Database Management/SQL

From Wikiversity
Jump to navigation Jump to search

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[edit | edit source]

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
  • Create database manipulation language SQL queries using a database application

Readings[edit | edit source]

  1. Wikipedia: SQL
  2. Wikibooks: Structured Query Language/Data Query Language
  3. Wikibooks: Structured Query Language/Data Manipulation Language

Multimedia[edit | edit source]

  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[edit | edit source]

  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[edit | edit source]

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

Microsoft Access[edit | edit source]

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

Northwind[edit | edit source]

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

Lesson Summary[edit | edit source]

  • 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]
  • Data manipulation language (DML) includes the INSERT, UPDATE, and DELETE statements.[5]
  • The structure of the SELECT statement is:[6]
    • SELECT ... FROM ... WHERE ...
  • The structure of the INSERT, UPDATE, and DELETE statements is:[7]
    • INSERT INTO ... VALUES ...
    • UPDATE ... SET ... WHERE ...
    • DELETE FROM ... WHERE ...
  • The FROM clause, which indicates the table(s) to retrieve data from.[8]
  • The WHERE clause includes a comparison predicate, which restricts the rows returned by the query.[9]
  • 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.[10]
  • Aggregate functions include COUNT, SUM, MIN, MAX, and AVG.[11]
  • The HAVING clause includes a predicate used to filter rows resulting from the GROUP BY clause.[12]
  • 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).[13]
  • Without an ORDER BY clause, the order of rows returned by an SQL query is undefined.[14]
  • The DISTINCT keyword eliminates duplicate data.[15]

Key Terms[edit | edit source]

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

See Also[edit | edit source]

References[edit | edit source]