Chapter 3

Query Formulation
with SQL

McGraw-Hill/Irwin Copyright © 2007 by The McGraw-Hill Companies, Inc. All rights reserved.
Query Formulation
▪ Query formulation is an important skill in application
development
▪ Everyone involved in the application dev. must be
competent in query formulation
▪ Need lots of practice with query formulation and SQL

4-2
What is SQL?
▪ SQL = Structured Query Language
→ Pronunciation : sequel due to its original name

▪ Language for database definition, manipulation,

and control
→ Definition : create & modify database structure
→ Manipulation : retrieval and modification of rows
→ Control : integrity and security constrains

▪ International standard
→ ANSI, ISO, SQL standards

▪ Standalone and embedded usage

→ Standalone : write and execute statements using a specialized editor
→ Embeded : embed statements into a procedural language

4-3
DML * DDL * DCL

▪ Data Manipulation Language (DML) :

INSERT, UPDATE, DELETE, SELECT

▪ Data Definition Language (DDL) :

CREATE DATABASE, DROP DATABASE,
CREATE TABLE, DROP TABLE, ALTER TABLE

▪ Data Control Language (DCL) :

GRANT, REVOKE, COMMIT, ROLLBACK, ETC

4-4
SELECT Statement Overview
SELECT <list of column expressions>
FROM <list of tables and join operations>
WHERE <list of logical expressions for rows>
GROUP BY <list of grouping columns>
HAVING <list of logical expressions for groups>
ORDER BY <list of sorting specifications>

▪ Expression: combination of columns, constants,

operators, and functions

4-5
University Database

4-6
First SELECT Examples
Example 1
SELECT * FROM Faculty

Example 2
SELECT *
FROM Faculty
WHERE FacSSN = '543210987'

Example 3
SELECT FacFirstName, FacLastName, FacSalary
FROM Faculty

Example 4
SELECT FacFirstName, FacLastName, FacSalary
FROM Faculty
WHERE FacSalary > 65000 AND FacRank = 'PROF'
4-7
Using Expressions
Example 5 (Access)
SELECT FacFirstName, FacLastName, FacCity,
FacSalary*1.1 AS IncreasedSalary,
FacHireDate
FROM Faculty
WHERE year(FacHireDate) > 1996

Example 5 (Oracle)
SELECT FacFirstName, FacLastName, FacCity,
FacSalary*1.1 AS IncreasedSalary,
FacHireDate
FROM Faculty
WHERE to_number(to_char(FacHireDate, 'YYYY'))
> 1996

4-8
Inexact Matching
• Match against a pattern: LIKE operator
• Use meta characters to specify patterns
– Wildcard (* or %)
– Any single character (? or _)
Example 6 (Access)
SELECT *
FROM Offering
WHERE CourseNo LIKE 'IS*'

Example 6 (Oracle)
SELECT *
FROM Offering
WHERE CourseNo LIKE 'IS%'
4-9
Using Dates
• Dates are numbers
• Date constants and functions are not standard

Example 7 (Access)
SELECT FacFirstName, FacLastName, FacHireDate
FROM Faculty
WHERE FacHireDate BETWEEN #1/1/1999#
AND #12/31/2000#

Example 7 (Oracle)
SELECT FacFirstName, FacLastName, FacHireDate
FROM Faculty
WHERE FacHireDate BETWEEN '1-Jan-1999'
AND '31-Dec-2000'
4-10
Other Single Table Examples
Example 8: Testing for null values
SELECT OfferNo, CourseNo
FROM Offering
WHERE FacSSN IS NULL AND OffTerm = 'SUMMER'
AND OffYear = 2006

Example 9: Mixing AND and OR

SELECT OfferNo, CourseNo, FacSSN
FROM Offering
WHERE (OffTerm = 'FALL' AND OffYear = 2005)
OR (OffTerm = 'WINTER' AND OffYear = 2006)

4-11
Inner Join
(Cross Product Style)

SELECT R.NoBon, M.Nama, M.Alamat, R.NoMobil, C.MerkTipe,

R.LamaRental
FROM Member M, Rental R, Mobil C
WHERE (M.NoMember = R.NoMember) AND (R.NoMobil = C.NoMobil)
SELECT NoBon, Nama, Alamat, R.NoMobil, MerkTipe, LamaRental
FROM Member M, Rental R, Mobil C
WHERE (M.NoMember = R.NoMember) AND (R.NoMobil = C.NoMobil)

4-12
Inner Join
(Inner Join Operator Style)

SELECT R.NoBon, M.Nama, M.Alamat, R.NoMobil, C.MerkTipe,

R.LamaRental
FROM Member M INNER JOIN Rental R ON M.NoMember = R.NoMember
INNER JOIN Mobil C ON R.NoMobil = C.NoMobil
SELECT NoBon, Nama, Alamat, R.NoMobil, MerkTipe, LamaRental
FROM Member M INNER JOIN Rental R ON M.NoMember = R.NoMember
INNER JOIN Mobil C ON R.NoMobil = C.NoMobil

4-13
Self-Join
▪ Join a table to itself
▪ Usually involve a self-referencing relationship
▪ Useful to find relationships among rows of the
same table
▪ Find subordinates within a preset number of levels
▪ Find subordinates within any number of levels
requires embedded SQL

4-14
Self-Join Example
Example : List faculty members who have a higher salary
than their supervisor. List the social security number,
name, and salary of the faculty and supervisor.

SELECT Subr.FacSSN, Subr.FacLastName,

Subr.FacSalary, Supr.FacSSN,
Supr.FacLastName, Supr.FacSalary
FROM Faculty Subr, Faculty Supr
WHERE Subr.FacSupervisor = Supr.FacSSN
AND Subr.FacSalary > Supr.FacSalary

4-15
Fungsi Agregat

SELECT SUM(UnitTerjual) AS JumlahTerjual

FROM DETILJUAL
SELECT AVG(NilaiUas) AS RataRataNilaiUas
FROM NILAI
SELECT MAX(Harga) AS HargaProdukTermahal
FROM PRODUK
SELECT MIN(Harga) AS HargaProdukTermurah
FROM PRODUK
SELECT COUNT(*) AS JumlahPelanggan
FROM PELANGGAN
SELECT STD(NilaiTugas) AS StandarDeviasiNilaiTugas
FROM NILAI

4-16
Group By (1)

[a] Menampilkan jumlah mahasiswa tiap fakultas :

SELECT Fakultas, COUNT (*) AS JumlahMhs
FROM MAHASISWA
GROUP BY Fakultas

[b] Menampilkan jumlah mahasiswa per fakultas untuk

tiap jenis kelamin :
SELECT Fakultas, JenisKelamin, COUNT (*) AS
JumlahMhs
FROM MAHASISWA
GROUP BY Fakultas, JenisKelamin
4-17
Group By (2)

[c] Menampilkan total stok masing-masing kategori produk :

SELECT Kategori, SUM (Stok) AS TotalStok
FROM PRODUK
GROUP BY Kategori
[d] Menampilkan harga tertinggi tiap merk produk :
SELECT Merk, MAX (Harga) AS HargaTertinggi
FROM PRODUK
GROUP BY Merk

4-18
Group By & Having

[e] Menampilkan total stok masing-masing kategori selain

produk Flash Disk :
SELECT Kategori, SUM (Stok) AS TotalStok
FROM Produk GROUP BY Kategori
HAVING Kategori <> ‘Flash Disk’
[f] Menampilkan harga tertinggi untuk merk Apacer,
Diamond & Genius :
SELECT Merk, MAX (Harga) AS HargaTertinggi
FROM Produk GROUP BY Merk
HAVING Merk IN (‘Apacer’, ‘Diamond’, ‘Genius’)

4-19
Nested Query
Example : List finance faculty who teach IS courses.
SELECT FacSSN, FacLastName, FacDept
FROM Faculty
WHERE FacDept = 'FIN' AND FacSSN IN
(SELECT FacSSN FROM Offering
WHERE CourseNo LIKE 'IS*’)

4-20
Traditional Set Operators

A UNION B

A INTERSECT B

A MINUS B

4-21
Union Compatibility
▪ Strong requirement
▪ Same number of columns
▪ Each corresponding column is compatible
▪ Positional correspondence

4-22
SQL UNION Example
Example : Retrieve basic data about all university people

SELECT FacSSN AS SSN, FacFirstName AS FirstName,

FacLastName AS LastName, FacCity AS City,
FacState AS State
FROM Faculty
UNION
SELECT StdSSN AS SSN, StdFirstName AS FirstName,
StdLastName AS LastName, StdCity AS City,
StdState AS State FROM Student

4-23
Oracle INTERSECT Example
Example : Show teaching assistants, faculty who are students. Only
show the common columns in the result.

SELECT FacSSN AS SSN, FacFirstName AS

FirstName, FacLastName AS LastName,
FacCity AS City, FacState AS State
FROM Faculty
INTERSECT
SELECT StdSSN AS SSN, StdFirstName AS
FirstName, StdLastName AS LastName,
StdCity AS City, StdState AS State
FROM Student

4-24
Oracle MINUS Example
Example : Show faculty who are not students (pure faculty). Only show
the common columns in the result.

SELECT FacSSN AS SSN, FacFirstName AS

FirstName, FacLastName AS LastName,
FacCity AS City, FacState AS State
FROM Faculty
MINUS
SELECT StdSSN AS SSN, StdFirstName AS
FirstName, StdLastName AS LastName,
StdCity AS City, StdState AS State
FROM Student

4-25
Data Manipulation Statements
▪ INSERT: adds one or more rows
▪ UPDATE: modifies one or more rows
▪ DELETE: removes one or more rows
▪ UPDATE and DELETE can use a WHERE
clause
▪ Not as widely used as SELECT statement

4-26
INSERT Example
Example : Insert a row into the Student table supplying
values for all columns.

INSERT INTO Student

(StdSSN, StdFirstName, StdLastName,
StdCity, StdState, StdZip, StdClass,
StdMajor, StdGPA)
VALUES
('999999999','JOE','STUDENT','SEATAC',
'WA','98042-1121','FR','IS', 0.0)

4-27
UPDATE Example
Example : Change the major and class of Homer Wells.

UPDATE Student
SET StdMajor = 'ACCT',
StdClass = 'SO'
WHERE StdFirstName = 'HOMER'
AND StdLastName = 'WELLS'

4-28
DELETE Example
Example : Delete all IS majors who are seniors.

DELETE FROM Student

WHERE StdMajor = 'IS'
AND StdClass = 'SR'

4-29