Structured Query Language (SQL)

1
Structured Query Language(SQL)
Ask and ye shall receive. The Bible
2
Class Outline

• What is SQL?
• What are the basic SQL select statements and what
  is the order in which they are to be used? Which
  are optional?
• Create SQL statements that
• use expressions and functions to convert data
  into information
• use multiple criteria for searching
• produce output in a specific order
• provide summary data from groups of records
• display data from two tables
• show data that uses the results of another query
  as criteria

3
SQL

• SQL is a non-procedural data access language that
  is used primarily in programming by embedding it
  in other languages
• SQL is not a user-level language
• SQL accepts one or more relations as input and
  produces a single relation as output
• SQL provides functions for data definition
  (creates database and table structures), data
  management (enter, edit, delete data), and data
  query (convert data into information)
• developed in mid 1970s by IBM endorsed by ANSI
  (American National Standards Institute) as the
  language of choice for manipulating relational
  databases
• language used by DB2, SQL/DS, ORACLE, INGRES,
  SYBASE, dBase, Paradox, Access (each with its own
  dialect)
• computer systems are able to exchange data by
  passing SQL requests and responses to one another

our focus
4
General SQL Query Syntax

• SELECT columns to extract
• FROM tables containing columns
• WHERE search criteria to restricts rows that are
  returned
• GROUP BY summarizes query results by groups
• HAVING search criteria to restrict groups that
  are returned
• ORDER BY sorts results by one or more columns

required
optional, must be in this order if any or all are
used

• Preceding is the order in which clauses should
  appear
• Order of processing is as follows From, Where,
  Group by, Having, Order by, Select

5
Projections

• SELECT Name, Salary
• FROM Employee

SELECT Office, EmpID, Name FROM Employee
6
Unique Projections

• SELECT DISTINCT Name, Salary
• FROM Employee

SELECT DISTINCT Office, Salary FROM Employee
appears only once
7
Selections
SELECT EmpID, Name, Office, Salary FROM
Employee WHERE Office Brussels
SELECT FROM Employee WHERE Office Brussels
8
Combining Selections and Projections

• SELECT Name, Salary
• FROM Employee
• WHERE Office Brussels

SELECT Name, Office, Salary FROM Employee WHERE
EmpID 35
Single quotes necessary around text and dates
(but not values) in criteria.
9
Comparison Search Conditions

• SELECT Name, Salary
• FROM Employee
• WHERE Salary gt 2500

SELECT EmpID, Name, Office FROM Employee WHERE
Name ltgt Anne Abel
10
Comparison Search Conditions
11
Examples of Search Conditions
SELECT Name, Office, Salary FROM Employee WHERE
Name Like am
SELECT Name, Office, Salary FROM Employee WHERE
Office is Null
SELECT Name, Office, Salary FROM Employee WHERE
Name Like Ab_l
SELECT Name, Office, Salary FROM Employee WHERE
Office NOT IN (Toronto)

• SELECT Name, Salary
• FROM Employee
• WHERE Office IN (Brussels, Tokyo)

SELECT Name, Office, Salary FROM Employee WHERE
Salary between 2000 and 3000
12
Compound Comparison Search Conditions
AND means that ALL conditions must be met
SELECT Name, Office, Salary FROM Employee WHERE
Name NOT Like Abel and Salary gt 3100
SELECT Name, Office, Salary FROM Employee WHERE
Office IN (Toronto) or Name Anne Abel
OR means that ANY condition may be met
when operators are combined, brackets are
evaluated first (inner to outer)

• SELECT Name, Salary
• FROM Employee
• WHERE (Office IN (Brussels, Tokyo) or Salary
  is Null)and HireDate lt 7/15/99

SELECT Name, Office, Salary FROM Employee WHERE
(Salary between 2000 and 3000 and Office ltgt
Tokyo) or (Name like Gor and EmpID gt 20)
13
Sorting

• SELECT Name, Salary
• FROM Employee
• WHERE Office Tokyo
• ORDER BY Salary, Name

SELECT Name, Office, Salary FROM Employee WHERE
Salary gt 2000 ORDER BY EmpID DESC
14
Expressions using Arithmetic
SELECT ItemName, Price1.15 FROM Product

• SELECT Name, Salary, Commission/Salary
• FROM Salesperson
• WHERE Commission gt .05Salary

SELECT ContactName, CompanyName FROM
Customer WHERE Paid is null and OrderDate gt
1/1/99 ORDER BY SysDate-InvoiceDate
current system date
SELECT Name, (Sysdate-Birthdate)/365 FROM
Employee ORDER BY Name
SELECT EmpID, Hiredate90 FROM Employee ORDER BY
Name
15
SQL Built-in Functions
SELECT Count() FROM Employee
SELECT Count(Distinct Office) FROM Employee
SELECT Sum(Salary) FROM Employee
SELECT Max(HireDate) FROM Employee
SELECT Min(Name) FROM Employee
SELECT Avg(Salary) FROM Employee
16
Aggregate Functions and Grouping
SELECT Office, Count() FROM Employee GROUP BY
Office
SELECT Office, Status, Max(Salary) FROM
Employee GROUP BY Office, Status
To view the groupings, you must also select them!
17
More Grouping Functions
SELECT Category, Avg(Price), Min(Quantity),
Sum(PriceQuantity) FROM Product WHERE SupplierID
in (1, 2) or SupplierID is null GROUP BY
Category ORDER BY Avg(Price)
18
Restrict Groups with Having
SELECT Category, Avg(Price), Min(Quantity),
Sum(PriceQuantity) FROM Product WHERE SupplierID
in (1, 2) or SupplierID is null GROUP BY
Category HAVING Min(Quantity) lt 5
The WHERE clause is always evaluated before the
HAVING clause.
19
Another Having example
SELECT Office, Max(Salary) FROM Employee WHERE
Status full-time GROUP BY Office HAVING
Count() gt 1 ORDER BY Office
20
Subqueries
Subquery is always evaluated before the main
query.
SELECT ProdID, Price, Qty FROM Product WHERE
SupID IN (SELECT SupplierID FROM
Supplier WHERE City London)
SELECT ProdID FROM Product WHERE Qty gt (SELECT
Avg(Qty) FROM Product WHERE SupID IN (1,
2))
21
Nested Subqueries
Determine the number of policies sold in area 100
of type 45
SELECT Count(Unique PolicyNum) FROM
PolicyPlan WHERE PlanCode 45 and PolicyNum
IN (SELECT PolicyNum FROM Commission WHERE
AgentNum IN (SELECT AgentNum FROM
Agent WHERE Area 100))
22
Join (Natural Join)
SELECT Product? ProdID, Product? ProdDesc,
Supplier? SupplierName FROM Product,
Supplier WHERE Product? SupID Supplier?
SupplierID
Table names required in Select statement only if
theres a possibility of ambiguity
23
Renaming Attributes and Relations with an Alias
SELECT SupplierName, avg(price) Average Price,
count() of Items FROM Product P, Supplier
S WHERE P? SupID S? SupplierIDand Category
Accessories GROUP BY SupplierName ORDER BY
SupplierID
alias
24
Exists and not exists in Subqueries
Which employees have no dependents?
Which employees have dependents of the same sex
as themselves?
SELECT Name FROM Employee E WHERE EXISTS (SELECT
FROM Dependent D WHERE E.EmpID D.EmpID and
E.Sex D.DepSex)
SELECT Name FROM Employee E WHERE NOT
EXISTS (SELECT FROM Dependent D WHERE
E.EmpID D.EmpID)