# Database Management/Advanced SQL

This lesson introduces advanced SQL concepts, including grouping and sorting.

## Objectives and Skills

Objectives and skills for this lesson include:
* Understand advanced SQL concepts
* Create SQL queries using GROUP BY and HAVING
* Create SQL queries using ORDER BY
* Create database manipulation language SQL queries using a database application

## Readings

* Wikipedia: SQL
* Wikibooks: Structured Query Language/Data Query Language
* Wikibooks: Structured Query Language/Data Manipulation 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.
* To execute non-query SQL statements, use Tools / SQL. See LibreOffice: Executing SQL Commands for more information.

### 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 the CustomerName and OrderID for all customers who ordered 'Aniseed Syrup', sorted in alphabetical order.
* Select the ProductName, Quantity, Price, and ExtendedPrice (Quantity * Price) for orders 10344 and 10345.
* Select the first and last names and current age of all employees having a birthday in September.
* Select the OrderID, count of products ordered, and total order cost for orders 10344 and 10345.
* Select the total number of orders and total cost of orders shipped by 'Speedy Express' in February 1997.
* Insert a new shipper with ID 4, name 'On Time Delivery', and phone '(503) 555 0123'.
* Update products to increase prices on all products by 1 ($1.00).
* Update products to reduce prices on all products by 1 (-$1.00).
* Update the new shipper's name from 'On Time Delivery' to 'On-Time Delivery'.
* Delete the new shipper.

### 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.
* Select the storename and orderid for all stores that ordered 'The Busy Executive's Database Guide', sorted in alphabetical order.
* Select the title, quantity, price, and extendedprice (quantity * price) for sales orders P2121 and P723.
* Select the first and last names and current number of years for all employees having a hire date in November.
* Select the order number, sum of quantity sales ordered, and total extended cost for orders P2121 and P723.
* Select the total number of orders and total cost of orders shipped by 'Bookbeat' between January - May 2023.
* Insert a new job with ID 15, name 'Sr. Editor', min_lvl 75 and max_lvl 150.
* Update titles to increase prices on all titles by 1 ($1.00).
* Update titles to reduce prices on all titles by 1 (-$1.00).
* Update the new job name from 'Sr. Editor' to 'Sr Editor'.
* Delete the new job title.

## Lesson Summary

* The GROUP BY clause projects rows having common values into a smaller set of rows and is often used in conjunction with SQL aggregate functions or to eliminate duplicate rows from a result set.
* Aggregate functions include COUNT, SUM, MIN, MAX, and AVG.
* The HAVING clause includes a predicate used to filter rows resulting from the GROUP BY clause.
* The ORDER BY clause identifies which column[s] to use to sort the resulting data, and in which direction to sort them (ascending or descending).
* Without an ORDER BY clause, the order of rows returned by an SQL query is undefined.
* The DISTINCT keyword eliminates duplicate data.
* Data manipulation language (DML) includes the INSERT, UPDATE, and DELETE statements.
* The structure of the INSERT, UPDATE, and DELETE statements is:
** INSERT INTO ... VALUES ...
** UPDATE ... SET ... WHERE ...
** DELETE FROM ... WHERE ...

## Key Terms
### aggregate function

    A function where the values of multiple rows are grouped together to form a single summary value.

## 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)