SQL Examples from the SPJ Database

1
SQL Examples from the SPJ Database

• Professor S. Kwan

2
Q01. FULL DETAILS OF ALL PROJECTS Project
Management

• SELECT FROM PROJECTS

3
Q01. FULL DETAILS OF ALL PROJECTS Better Example
- Use of External Names (aliases)

• SELECT PROJECTS.J AS PROJECT NUMBER,
  PROJECTS.JNAME AS NAME, PROJECTS.CITY
• FROM PROJECTS

4
Q02 SUPPLIERS WHO SUPPLIES PROJECT "J1"
Sourcing, supplier dependencyWrite your own
standard SQL

• SELECT SUPPLIER.S AS SUPPLIER NUMBER,
• SUPPLIER.SNAME AS NAME
• FROM SUPPLIER, SPJ
• WHERE SUPPLIER.S SPJ.S
• AND SPJ.J"J1"
• ORDER BY SUPPLIER.S

How?
5
Q02 SUPPLIERS WHO SUPPLIES PROJECT "J1"
Sourcing, supplier dependencyCreated with
Design View
6
Q02 SUPPLIERS WHO SUPPLIES PROJECT "J1"
Sourcing, supplier dependencyCreated with
Design View
SELECT SUPPLIER.S AS SUPPLIER NUMBER,
SUPPLIER.SNAME AS NAME FROM SUPPLIER
INNER JOIN SPJ ON SUPPLIER.S SPJ.S WHERE
((SPJ.J"J1")) ORDER BY SUPPLIER.S
7
Q02 SUPPLIERS WHO SUPPLIES PROJECT "J1"
Sourcing, supplier dependencyBetter Example -
Generalized with Parameter Value
PARAMETERS PROJECT NUMBER TEXT SELECT
SUPPLIER.S AS SUPPLIER NUMBER, SUPPLIER.SNAME
AS NAME, SPJ.J AS PROJECT NUMBER FROM
SUPPLIER, SPJ WHERE SUPPLIER.S SPJ.S AND
SPJ.JPROJECT NUMBER ORDER BY SUPPLIER.S
8
Q03 GET PARTS SUPPLIED BY A LONDON SUPPLIER
Foreign vs. local source of supply
SELECT DISTINCT PARTS.P AS PART NUMBER,
PARTS.PNAME AS PART, SUPPLIER.S AS SUPPLIER
NUMBER, SUPPLIER.CITY FROM PARTS INNER JOIN
(SUPPLIER INNER JOIN SPJ ON SUPPLIER.S
SPJ.S) ON PARTS.P
SPJ.P WHERE (SUPPLIER.CITY"LONDON")
9
Q03 GET PARTS SUPPLIED BY A LONDON SUPPLIER
10
Q03 GET PARTS SUPPLIED BY A LONDON SUPPLIER
Better Example - Generalized with Parameter Value
PARAMETERS SUPPLIER CITY LOCATION Text SELECT
DISTINCT PARTS.P AS PART NUMBER, PARTS.PNAME
AS PART, SUPPLIER.S AS SUPPLIER NUMBER,
SUPPLIER.CITY FROM PARTS, SUPPLIER, SPJ WHERE
PARTS.PSPJ.P AND SUPPLIER.SSPJ.S AND
SUPPLIER.CITYSUPPLIER CITY LOCATION
11
Q04 GET SUPPLIERS WHO SUPPLY GIVEN PART
Quality control, economy of scaleImplicit
Parameter
SELECT DISTINCT SUPPLIER.S AS SUPPLIER
NUMBER, SUPPLIER.SNAME AS NAME, SPJ.P AS
PART NUMBER FROM SUPPLIER INNER JOIN SPJ ON
SUPPLIER.S SPJ.S WHERE (SPJ.PPART
NUMBER)
12
Q05 GET ALL PAIRS OF SUPPLIERS LOCATED IN THE
SAME CITY Economy of scale, competition,
distribution
SELECT SX.S AS FIRST, SX.SNAME AS NAME of
FIRST, SY.S AS SECOND, SY.SNAME AS NAME of
SECOND, SX.CITY FROM SUPPLIER AS SX, SUPPLIER AS
SY WHERE SX.SltSY.S AND SX.CITYSY.CITY
13
Q06 LIST OF CITIES WITH AT LEAST ONE SUPPLIER
OR PROJECT Distribution, local source of
supply SQL SPECIFIC QUERY
SELECT CITY FROM SUPPLIER UNION SELECT CITY
FROM PROJECTS ORDER BY CITY
14
Q07 PROJECTS SUPPLIED ENTIRELY BY A GIVEN
SUPPLIER Sole source dependency SQL
SPECIFIC QUERY
PARAMETERS SUPPLIER NUMBER Text SELECT
DISTINCT SPJX.J AS PROJECT NUMBER,
JNAME AS PROJECT, SUPPLIER NUMBER FROM SPJ
AS SPJX, PROJECTS WHERE NOT EXISTS (SELECT
FROM SPJ SPJY WHERE SPJY.J SPJX.J AND
SPJY.S ltgt SUPPLIER NUMBER) AND SPJX.J
PROJECTS.J AND SPJX.S SUPPLIER
NUMBER
15
Q07 PROJECTS SUPPLIED ENTIRELY BY A GIVEN
SUPPLIER
For Supplier Number S1
For Supplier Number S2