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).^[1]

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

Multimedia

Activities

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

LibreOffice Base

To view SQL for a query, open the query and then select View and Switch Design View On/Off.

Microsoft Access

To view SQL for a query, open the query and then select View and SQL View.

Northwind

Download a copy of the Database Examples/Northwind database for your selected database application.
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.
Select all fields and all records in the Categories table.
Select the CustomerName for all customers.
Select the CustomerName for all customers in the country 'Italy'.
Select the ProductName for all products in Category 1 with a price less than $5.00.
Select the SupplierName for all suppliers from English-speaking countries (Australia, Canada, UK, USA)
Select the first and last names of all employees having a birthday in September.
Select the CustomerName and OrderID for all orders placed on 9 September 1996.
Select the ProductName for all seafood products with a supplier from 'Boston'.

Pubs

Download a copy of the Database Examples/Pubs database for your selected database application.
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.
Select all fields and all records in the Authors table.
Select the emp_id and lname for all employees.
Select the au_firstname and au_lastname for all authors in the state 'UT'.
Select the orderID for all orders in storeID 7131 with a quantity greater than 20.
Select the publishername for all publishers in the European-speaking countries (Germany, France).
Select the first and last names of all employees having a hire date in June.
Select the orderID, ord_detail and title for all orders placed on September 13, 2024.
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).^[2]
SQL statements may be classified into either data query language (DQL), data definition language (DDL), data control language (DCL), or data manipulation language (DML).^[3]
Data query language (DQL) includes the SELECT statement.^[4]
The structure of the SELECT statement is:^[5]
- SELECT ... FROM ... WHERE ...
The FROM clause, which indicates the table(s) to retrieve data from.^[6]
The WHERE clause includes a comparison predicate, which restricts the rows returned by the query.^[7]

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.^[8] 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. [[2]]
join: A join combines columns from one or more tables in a relational database.^[9]
LIKE operator: The LIKE operator is a logical operator that determines if a character string matches a specified pattern. ^[10]
OR operator: The value of an OR expression is when at least one of the input values is true.^[11]
SELECT (SQL): The SELECT command retrieves data from one or more tables or views.^[12]
Structured Query Language (SQL): Structured Query Language (SQL) is a widely-used programming language for working with relational databases.^[13]
wildcard character: A wildcard character is a kind of placeholder represented by a single character, such as an asterisk (*), question mark (?), bracket ([]).^[14]; ANSI-89 wildcard characters also include hashtag (#), exclamation point (!), and dash (-).^[15]

References

[1] Wikipedia: SQL

[2] Wikipedia: SQL

[3] Wikipedia: SQL

[4] Wikipedia: Data query language

[5] Wikipedia: Data manipulation language

[6] Wikipedia: Select (SQL)

[7] Wikipedia: Select (SQL)

[8] Wikipedia: Join (SQL) Inner Join

[9] Wikipedia: Join (SQL)

[10] SQLServerTutorial: SQL Server Basics

[11] W3Schools.com SQL, AND, OR, NOT

[12] Wikibooks: Structured Query Language/SELECT: Fundamentals

[13] Wikibooks: Structured Query Language

[14] Wikipedia: Wildcard charter

[McFadyen:_Relational_Databases_and_Microsoft_Access-15] [1] McFadyen: Relational Databases and Microsoft Access

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]

[12]

[13]

[14]

[15]