Database Management/SQL
Appearance
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
Readings
[edit | edit source]Multimedia
[edit | edit source]- YouTube: Learn Basic SQL
- YouTube: The Structured Query Language (SQL)
- YouTube: Introduction to SQL with LibreOffice Base
- YouTube: Microsoft Access SQL Basics
Activities
[edit | edit source]- 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
[edit | edit source]- To view SQL for a query, open the query and then select
View
andSwitch Design View On/Off
.
Microsoft Access
[edit | edit source]- To view SQL for a query, open the query and then select
View
andSQL View
.
Northwind
[edit | edit source]- 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
[edit | edit source]- 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
[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]
- 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
[edit | edit source]- 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]
See Also
[edit | edit source]- 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
References
[edit | edit source]- ↑ Wikipedia: SQL
- ↑ Wikipedia: SQL
- ↑ Wikipedia: SQL
- ↑ Wikipedia: Data query language
- ↑ Wikipedia: Data manipulation language
- ↑ Wikipedia: Select (SQL)
- ↑ Wikipedia: Select (SQL)
- ↑ Wikipedia: Join (SQL) Inner Join
- ↑ Wikipedia: Join (SQL)
- ↑ SQLServerTutorial: SQL Server Basics
- ↑ W3Schools.com SQL, AND, OR, NOT
- ↑ Wikibooks: Structured Query Language/SELECT: Fundamentals
- ↑ Wikibooks: Structured Query Language
- ↑ Wikipedia: Wildcard charter
- ↑ [1] McFadyen: Relational Databases and Microsoft Access