2
Overview
Using Structured Query Language (SQL) to get the data you want from relational databases
Learning basic syntax and simple queries
All examples can be used with the “Northwind” database
3
SQL: Introduction
SQL is a standard command language use by relational DBMS to perform database operations
Some facts about SQL English-like Case insensitive An ANSI standard Not considered to be a programming language Most venders have their own proprietary extensions,
making SQL slightly different in different DBMS products
What Does SQL Do?
DML - data manipulation language Retrieving data: database queries Manipulating data: insert, update, delete
DDL - data definition language Defining and modifying data structures
(schema): databases, tables, views, etc.
DCL - data control language Control data access: permissions, etc.
4
5
Database Queries
A query is a request for information from a database.
“SELECT” statements are used to retrieve data The result of a query is generally a table (but not
necessarily a relation)
SQL SELECT basics Defining selection criteria
Sorting
Table join
SELECT: General Syntax
SELECT [Column(s), or other expressions]
FROM [Table(s)]
[WHERE …]
[ORDER BY …]
6
7
1.1 Select Columns
Syntax SELECT * (or a list of columns)
FROM TableName
Example
SELECT * FROM Products;
SELECT ProductName, UnitPriceFROM Products;
“*” represents all columns in the table
Provide a list of columns separated by comma
Column Qualifier and Alias
Qualifiers are used to distinguish column names when there is ambiguity
SELECT Products.ProductName, Products.UnitPriceFROM Products
Alias is used to give column another name
SELECT UnitPrice*Quantity AS Total
FROM [Order Details];Use “AS” operator to give an alias.
A qualifier of table name
Use square brackets if there is blank spaces in table or column names.
8
2. Select Rows (Selection Criteria)
Use WHERE clause to specify selection criteria
Common comparison operators =, >, <, >=, <=, <>, !=
Examples
SELECT * FROM Products WHERE UnitPrice = 18;
SELECT BookTitle, ListPrice FROM ProductsWHERE UnitPrice < 20;
9
2.1 Comparing with ConstantsNumbers (int, float, decimal, etc.) … WHERE UnitPrice > 20.99
Text (char, varchar, nvarchar, etc.): use '…' (single quote) for values … WHERE ProductName = 'Tofu' // exact match … WHERE ProductName > 'Chang'
Data/Time: SQL Server uses '...' … WHERE OrderDate < '08/30/2008'
Boolean (bit, yes/no, etc.): 'TURE' or 1, 'FALSE' or 0 … WHERE Discontinued = 1 … WHERE Discontinued = 'true'
10
compared by alphabetical order, “>” means after
Before the date, 12:00AM
11
2.2 IN and BETWEEN
IN (a value list): equals to any value in the list
SELECT * FROM ProductsWHERE UnitPrice IN (18, 19, 21)
BETWEEN min AND max: any value falls in the range, inclusive
SELECT * FROM ProductsWHERE UnitPrice BETWEEN 10 AND 20
12
2.3 String Pattern Match: LIKE
LIKE: keyword match, or fuzzy query _ (underscore): matching one single character
% (percentage) wildcard: matching any multiple characters
Comparison pattern is case insensitive
Example Can I get all tofu related products?
SELECT * FROM Products
WHERE ProductName LIKE '%tofu%'
Matching any characters after “tofu”
Matching any characters before “tofu”
13
2.4 IS NULL
NULL means missing value
IS NULL, IS NOT NULL
SELECT * FROM Orders
WHERE ShipPostalCode IS NULL
SELECT * FROM Orders
WHERE ShipPostalCode IS NOT NULL
2.5 NOTReversal criteria NOT (expression)
Examples
… WHERE NOT UnitPrice > 20;
… WHERE NOT ProductName LIKE '%tofu%';
… WHERE ProductName NOT LIKE '%tofu%';
… WHERE NOT ShipPostalCode IS NULL;
14
15
2.6 Compound ConditionsUse logical operators to connect multiple criteria AND: satisfy both conditions OR: satisfy either criterion Comparison precedence: AND has a higher precedence over OR Best practice: use parentheses () to explicitly define comparison
order
Examples
… WHERE UnitPrice <= 20 AND UnitPrice >= 10;
… WHERE ShippedDate ='8/2/1996' OR ShippedDate = '8/9/1996';
… WHERE (City = 'London' OR City = 'Seattle') AND HireDate > '1/1/1993';
If () are not used, “ City = 'Seattle' AND HireDate > '1/1/1993' ” will be evaluated first, which is wrong.
16
3. SortingSorting query results ORDER BY Column(s) [ASC/DESC] ASC (ascending) is the default order
Examples
SELECT * FROM Products ORDER BY UnitPrice;SELECT * FROM Products ORDER BY 6;
Multiple sorting
SELECT * FROM ProductsORDER BY CategoryID, UnitPrice DESC
Column position can also be used (starting from 1)
Sort by category first, then UnitPrice
4. Table Join
Retrieving data from multiple tables The query result consists of columns from
more than one table
How do rows match from different tables? Cross Join: no need to match.
Inner Join: use the foreign key constraint as the matching criteria
17
Table Join Effect
Product Category
A 1
B 2
Category Name
1 Canned
2 Drink
3 Fresh
Product Category
A Canned
B Drink
Product Category
A Canned
A Drink
A Fresh
B Canned
B Drink
B Fresh
18
Cross Join: no row matching
Inner Join: row matching based on foreign key
FK
Join
Table Join SQL Example
What is the category name for each product?
SELECT ProductName, CategoryName
FROM Products, Categories
Where
Products.CategoryID = Categories.CategoryID
Joining/matching criteria: very important, don’t forget!
Use “table.column” format to avoid ambiguity – CategoryID in which table?
Primary keyForeign key
19
3. Table Join Syntax Alternative
SELECT ProductName, CategoryNameFROM Products, Categories
Where Products.CategoryID = Categories.CategoryIDAND Discontinued = 0;
VS.
SELECT ProductName, CategoryNameFROM Products INNER JOIN Categories ON
Products.CategoryID = Categories.CategoryIDWHERE Discontinued = 0;
1. Joining/matching criteria: very important, don’t forget!
2. Table.Cloumn format is used to avoid ambiguity.
20
Joining More Tables (1)
For each order after April 1, 1998, I want to see what products (product name) were ordered, and how many.
SELECT Orders.OrderID, OrderDate, ProductName, QuantityFROM Orders, [Order Details], ProductsWHERE
Orders.OrderID = [Order Details].OrderIDAND [Order Details].ProductID = Products.ProductIDAND OrderDate > '1998-04-01'
21
Joining More Tables (2)
For each order after April 1, 1998, I want to see what products (product name) were ordered, and how many.
SELECT Orders.OrderID, OrderDate, ProductName, Quantity
FROM
Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID
INNER JOIN Products ON
[Order Details].ProductID = Products.ProductID
WHERE OrderDate > '1998-04-01'
22
SummaryKey concepts Database query SQL: DML, DDL, DCL Join, cross join, inner join
Key skills: write SQL SELECT statement to retrieve data; and know the result of a given SQL SELECT statement. Select columns, * Selection criteria in WHERE: comparison operators
=, <, >, >=, <=, <>, !=, IN, BETWEEN, LIKE, IS NULL, NOT %, _ AND, OR
ORDER BY Table join
23
More SQL ResourcesW3Schools SQL Tutorial http://www.w3schools.com/sql/
SQL Course http://sqlcourse.com/ http://sqlcourse2.com/
A gentle introduction to SQL http://sqlzoo.net/
Other http://www.youtube.com/watch?v=Rpp28U_K9Lk http://www.1keydata.com/sql
24