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

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

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

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

Lesson Summary

 * The  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,  ,  ,  , and.
 * The  clause includes a predicate used to filter rows resulting from the   clause.
 * The  clause identifies which column[s] to use to sort the resulting data, and in which direction to sort them (ascending or descending).
 * Without an  clause, the order of rows returned by an SQL query is undefined.
 * The  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:

Key Terms

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