Database Management/SQL

This lesson introduces Structured Query Language (SQL). SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS).

Objectives and Skills
Objectives and skills for this lesson include:
 * Understand SQL concepts
 * Create single-table SQL queries using a database application
 * Create multi-table SQL queries using a database application

Readings

 * 1)  SQL
 * 2)  Structured Query Language/Data Query Language

Multimedia

 * 1) YouTube: Learn Basic SQL
 * 2) YouTube: The Structured Query Language (SQL)
 * 3) YouTube: Introduction to SQL with LibreOffice Base
 * 4) YouTube: Microsoft Access SQL Basics

Activities

 * 1) Select a database application to use for this lesson. LibreOffice Base and Microsoft Access are recommended.
 * 2) Review Database Applications and Database Software for background information and tutorials.
 * 3) Complete the activities below using your selected database application and SQL view.

LibreOffice Base

 * 1) To view SQL for a query, open the query and then select   and.

Microsoft Access

 * 1) To view SQL for a query, open the query and then select   and.

Northwind

 * 1) Download a copy of the Database Examples/Northwind database for your selected database application.
 * 2) Review the E-R diagram in your database application to verify that it matches the Database Examples/Northwind example. Note the primary keys, foreign keys, and relationships. Then write SQL queries to determine results for each of the following.
 * 3) Select all fields and all records in the Categories table.
 * 4) Select the CustomerName for all customers.
 * 5) Select the CustomerName for all customers in the country 'Italy'.
 * 6) Select the ProductName for all products in Category 1 with a price less than $5.00.
 * 7) Select the SupplierName for all suppliers from English-speaking countries (Australia, Canada, UK, USA)
 * 8) Select the first and last names of all employees having a birthday in September.
 * 9) Select the CustomerName and OrderID for all orders placed on 9 September 1996.
 * 10) Select the ProductName for all seafood products with a supplier from 'Boston'.

Pubs

 * 1) Download a copy of the Database Examples/Pubs database for your selected database application.
 * 2) Review the E-R diagram in your database application to verify that it matches the Database Examples/Pubs example. Note the primary keys, foreign keys, and relationships. Then create QBE queries to determine results for each of the following. View the SQL generated for each query.
 * 3) Select all fields and all records in the Authors table.
 * 4) Select the emp_id and lname for all employees.
 * 5) Select the au_firstname and au_lastname for all authors in the state 'UT'.
 * 6) Select the orderID for all orders in storeID 7131 with a quantity greater than 20.
 * 7) Select the publishername for all publishers in the European-speaking countries (Germany, France).
 * 8) Select the first and last names of all employees having a hire date in June.
 * 9) Select the orderID, ord_detail and title for all orders placed on September 13, 2024.
 * 10) Select the first and last employee name for all job descriptions with a publisher name from 'NY'.

Lesson Summary

 * SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS).
 * SQL statements may be classified into either data query language (DQL), data definition language (DDL), data control language (DCL), or data manipulation language (DML).
 * Data query language (DQL) includes the SELECT statement.
 * The structure of the SELECT statement is:
 * The  clause, which indicates the table(s) to retrieve data from.
 * The  clause includes a comparison predicate, which restricts the rows returned by the query.
 * The  clause includes a comparison predicate, which restricts the rows returned by the query.

Key Terms

 * inner join
 * An inner join requires each row in the two joined tables to have matching column values. Inner join creates a new result table by combining column values of two tables. An inner join is one in which Access only includes data from a table if there is corresponding data in the related table, and vice versa. Inner joins are useful because they let you combine data from two sources based on shared values – so you only see data when there’s a complete picture. []


 * join
 * A join combines columns from one or more tables in a relational database.


 * LIKE operator
 * The LIKE operator is a logical operator that determines if a character string matches a specified pattern.


 * OR operator
 * The value of an OR expression is when at least one of the input values is true.


 * SELECT (SQL)
 * The SELECT command retrieves data from one or more tables or views.


 * Structured Query Language (SQL)
 * Structured Query Language (SQL) is a widely-used programming language for working with relational databases.


 * wildcard character
 * A wildcard character is a kind of placeholder represented by a single character, such as an asterisk (*), question mark (?), bracket ([]).
 * ANSI-89 wildcard characters also include hashtag (#), exclamation point (!), and dash (-).