Database Query 1: SQL Basics

Database Query 1: SQL Basics

J.G. Zheng Fall 2010

CIS 3730

Designing and Managing Data

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

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.

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 …]

1.1 Select Columns

Syntax



SELECT * ( or a list of columns ) FROM TableName

Example

SELECT * FROM Products;

SELECT ProductName, UnitPrice FROM 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.UnitPrice FROM Products

Alias is used to give column another name

SELECT UnitPrice*Quantity AS Total

FROM [Order Details];

A qualifier of table name

Use square brackets if there is blank spaces in table or column names.

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 Products

WHERE UnitPrice < 20;

2.1 Comparing with Constants

Numbers (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'

compared by alphabetical order, “>” means after

Before the date, 12:00AM

2.2 IN and BETWEEN

IN (a value list): equals to any value in the list

SELECT * FROM Products

WHERE UnitPrice IN (18, 19, 21)

BETWEEN min AND max : any value falls in the range, inclusive

SELECT * FROM Products

WHERE UnitPrice BETWEEN 10 AND 20

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”

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 NOT

Reversal criteria

 NOT (expression)

Examples

… WHERE NOT UnitPrice > 20;

… WHERE NOT ProductName LIKE '%tofu%';

… WHERE ProductName NOT LIKE '%tofu%';

… WHERE NOT ShipPostalCode IS NULL;

2.6 Compound Conditions

Use 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.

3. Sorting

Sorting 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 Products

ORDER 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

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 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 key Foreign key

3. Table Join Syntax Alternative

SELECT ProductName, CategoryName FROM Products, Categories

Where Products.CategoryID = Categories.CategoryID AND Discontinued = 0;

VS.

SELECT ProductName, CategoryName

FROM Products INNER JOIN Categories ON

Products.CategoryID = Categories.CategoryID WHERE Discontinued = 0;

1. Joining/matching criteria:

very important, don’t forget!

2. Table.Cloumn format is used to avoid ambiguity.

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, Quantity FROM Orders, [Order Details], Products

WHERE

Orders.OrderID = [Order Details].OrderID AND

[Order Details].ProductID = Products.ProductID AND

OrderDate > '1998-04-01'

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'

Summary

Key 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

More SQL Resources

W3Schools 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