# 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

* Wikipedia: SQL
* Wikibooks: Structured Query Language/Data Query Language

## Multimedia

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

## 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).
* 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:
** SELECT ... FROM ... WHERE ...
* The FROM clause, which indicates the table(s) to retrieve data from.
* The WHERE 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 (-).

## See Also

* SQL
* Wikibooks: Database Design/Fundamental Concepts
* W3Schools: SQL Tutorial
* SQL Course: Introduction
* Codecademy: SQL Commands
* McFadyen: Relational Databases and Microsoft Access - Chapter 9 (Data Definitional Language (DDL)]
* Watt: Database Design - Chapter 15 (SQL)
* Mode: SQL Aggregate Functions