Database Management Systems

1
Database Management Systems Programming
LIS 558 - Week 6 Structured Query Language

• Faculty of Information Media Studies
• Summer 2000

2
Class Outline

• SQL -- What is it?
• Basic SQL select statements
• Using expressions and functions to convert data
  into information
• Using multiple criteria for searching
• Producing output in a specific order
• Providing summary data from groups of records
• Displaying data from more than one table
• Showing data that uses the results of another
  query as criteria

3
SQL - Structured Query Language

• 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)

4
SQL

• 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

5
General SQL Query Syntax

• SELECT columns to extract
• FROM tables containing columns
• WHERE search criteria to restrict
• 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

6
Projections

• SELECT Name, Salary
• FROM Employee

SELECT Office, EmpID, Name FROM Employee
7
Unique Projections

• SELECT DISTINCT Name, Salary
• FROM Employee

SELECT DISTINCT Office, Salary FROM Employee
appears only once
8
Selections
SELECT EmpID, Name, Office, Salary FROM
Employee WHERE Office Brussels
SELECT FROM Employee WHERE Office Brussels
9
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.
10
Comparison Search Conditions

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

SELECT EmpID, Name, Office FROM Employee WHERE
Name ltgt Anne Abel
11
Comparison Search Conditions
12
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?
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
13
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

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

when operators are combined, brackets are
evaluated first (inner to outer)
SELECT Name, Office, Salary FROM Employee WHERE
(Salary between 2000 and 3000 and Office ltgt
Tokyo) or (Name like Gor and EmpID gt 20)
14
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
15
Expressions using Arithmetic
SELECT ItemName, Price1.15 FROM Product

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

current system date
SELECT ContactName, CompanyName FROM
Customer WHERE Paid is null and OrderDate gt
1/1/99 ORDER BY Date-InvoiceDate
SELECT Name, (Date-Birthdate)/365 FROM
Employee ORDER BY Name
SELECT EmpID, Hiredate90 FROM Employee ORDER BY
Name
16
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
17
Aggregate Functions and Grouping
SELECT Office, Status, Max(Salary) FROM
Employee GROUP BY Office, Status
SELECT Office, Count() FROM Employee GROUP BY
Office
To view the groupings, you must also select them!
18
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)
19
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.
20
Another Having example
SELECT Office, Max(Salary) FROM Employee WHERE
Status full-time GROUP BY Office HAVING
Count() gt 1 ORDER BY Office
21
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))
22
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))
23
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
24
Renaming Attributes and Relations with an Alias
SELECT SupplierName, avg(price) As Average
Price, count() As of Items FROM Product P,
Supplier S WHERE P? SupID S? SupplierIDand
Category Accessories GROUP BY
SupplierName ORDER BY SupplierID
alias
25
Exists and not exists in Subqueries
Which employees have dependents of the same sex
as themselves?
Which employees have no dependents?
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)