9
Advanced SQL Query
(PART 1)
CSF2600700 - BASIS DATA
Advanced
Acknowledgements
This slide is a modification to supplementary slide of
“Database System”, 7th edition, Elmasri/Navathe, 2015: Chapter 7 More SQL: Complex Queries,
Triggers, Views, and Schema Modification used in “Basis Data” course in academic years 2018/2019 in
the Faculty of Computer Science, Universitas Indonesia.
2
Review: SQL yang Sudah Di Pelajari
DDL: Data Definition Language
Basic SQL Query
Cartesian Product
3
Outline
1. Join SQL
2. More Complex SQL Queries
3. Grouping and Aggregate Functions
4. Views (Virtual Tables) in SQL PART 2
5. Schema Change Statements in SQL
4
1. Join SQL
Meanings of NULL values
Unknown value
ex: A person’s date of birth is not known
Unavailable
ex: A person has a home phone but does not want it to be listed
Not applicable attribute
ex. Passport number
SQL does not distinguish between the different meanings of NULL
5
1. Join SQL
...
Operations on NULL value
6
... Join SQL
1.
Operations on NULL value (Cntd.)
SQL allows queries that check whether an attribute value is NULL
IS or IS NOT NULL
SQL uses IS or IS NOT to compare NULLs because it considers each
NULL value distinct from other NULL values, so equality comparison is
not appropriate.
Query 18. Retrieve the names of all employees who do not have supervisors.
SELECT Fname, Lname
FROM EMPLOYEE
WHERE Super_ssn IS NULL;
Note: If a join condition is specified, tuples with NULL values for the join
attributes are not included in the result
7
... Join SQL
1.
Arithmetic Operations
The standard arithmetic operators '+', '-'. '*', and '/' (for addition, subtraction,
multiplication, and division, respectively) can be applied to numeric values
in an SQL query result
Query 13: Show the effect of giving all employees who work on the
'ProductX' project a 10% raise.
Q13:
SELECT FNAME, LNAME, 1.1*SALARY AS INCREASED_SAL
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProductX';
8
... Join SQL
1.
Arithmetic Operations (Cntd.)
Query 14: Retrieve all employees in department 5 whose salary is between $30,000 and
$40,000
Q14:
SELECT *
FROM EMPLOYEE
WHERE (SALARY BETWEEN 30000 AND 40000) AND DNO=5;
Q14A:
SELECT *
FROM EMPLOYEE
WHERE (SALARY >= 30000 AND SALARY <=40000) AND DNO=5;
9
... Join SQL
1.
The EXCEPT Function
Equal to minus operation
A except B means set of data in A without data that appears in B
(SELECT … FROM … WHERE … ) EXCEPT (SELECT … FROM … WHERE …)
10
... Join SQL
1.
Joined Relations Feature in SQL
Can specify a “joined relation” in the FROM-clause
Looks like any other relation but is the result of a join
Allows the user to specify different types of joins
(regular “theta” JOIN, NATURAL JOIN, LEFT OUTER JOIN,
RIGHT OUTER JOIN, CROSS JOIN, etc)
Illustration: Takahashi & Azuma (2014)
11
1. Join SQL
...
Example: CROSS-JOIN
12
1. Join SQL
...
Example: THETA JOIN
13
1. Join SQL
...
Example: OUTER JOIN
14
1. Join SQL
...
Example: FULL OUTER JOIN
15
1. Join SQL
...
Example: NATURAL JOIN
16
Outline
1. Join SQL
2. More Complex SQL Queries
3. Grouping and Aggregate Functions
4. Views (Virtual Tables) in SQL PART 2
5. Schema Change Statements in SQL
17
... More Complex SQL Queries
2.
Nested Queries
Some queries require that existing values in the database be fetched and then used in
a comparison condition -> using nested query
A nested query is a complete SELECT-FROM-WHERE block, within in the
WHERE-clause of another query
That other query is called the outer query
Comparison operator IN
➔ Compares value v with a set (or multiset) of values V
➔ Evaluates to TRUE if v is one of the elements in V
18
... More Complex SQL Queries
2.
Nested Queries (Cntd.)
Outer Query
Nested Query
19
... More Complex SQL Queries
2.
Nested Queries (Cntd.)
Use tuples of values in comparisons:
➔ Place them within parentheses
Query:
Retrieve the SSN from all employees who work the same (project,hours)
combination on same project that employee ‘John Smith’ (ESSN = ‘123456789’ )
works on.
20
... More Complex SQL Queries
2.
Nested Queries (Cntd.)
Use other comparison operators to compare a single value v
➔ = ANY (or = SOME) operator
Returns TRUE if the value v is equal to some value in the set V and is hence equivalent
to IN
➔ Other operators that can be combined with ANY (or SOME): >, >=, <, <=, and <>
21
... More Complex SQL Queries
2.
Correlated Nested Queries
If a condition in the WHERE-clause of a nested query references an attribute of a
relation declared in the outer query, the two queries are said to be correlated.
The result of a correlated nested query is different for each tuple (or combination of
tuples) of the relation(s) the outer query
22
... More Complex SQL Queries
2.
Correlated Nested Queries (Cntd.)
A query written with nested SELECT... FROM... WHERE...blocks and
using the = or IN comparison operators can always be expressed as a
single block query.
For example, Q16 may be written as in Q12A
23
... More Complex SQL Queries
2.
The EXISTS Functions
Check whether the result of a correlated nested query is empty (contains no
tuples) or not
EXISTS and NOT EXISTS are usually used in conjunction with a correlated
nested query
24
... More Complex SQL Queries
2.
The EXISTS Functions (Cntd.)
Query 12: Retrieve the name of each employee who has a dependent with the same
first name and same sex as the employee.
SELECT Fname, Lname
FROM EMPLOYEE E
WHERE EXISTS (SELECT * FROM DEPENDENT WHERE SSN = ESSN AND
Fname = DEPENDENT_NAME AND E.Sex = Sex);
25
... More Complex SQL Queries
2.
The EXISTS Functions (Cntd.)
Query 6: Retrieve the names of employees who have no dependents
SELECT Fname, Lname
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT * FROM DEPENDENT WHERE SSN = ESSN);
The correlated nested query retrieves all DEPENDENT tuples related to an
EMPLOYEE tuple. If none exist, the EMPLOYEE tuple is selected
26
... More Complex SQL Queries
2.
The EXISTS Functions (Cntd.)
Query 7: List the names of managers who have at least one dependent.
SELECT Fname, Lname
FROM EMPLOYEE
WHERE EXISTS ( SELECT * FROM DEPENDENT WHERE SSN = ESSN) AND
EXISTS ( SELECT * FROM DEPARTMENT WHERE SSN = MGR_SSN);
➔ The first nested query select all DEPENDENT tuples related to an EMPLOYEE
➔ The second nested query select all DEPARTMENT tuples managed by the EMPLOYEE
➔ If at least one of the first and at least one of the second exists, we select the EMPLOYEE tuple.
Can you rewrite that query using only on a nested query or no nested query?
27
... More Complex SQL Queries
2.
Alternative of Sample Query 7
List the names of managers who have at least one dependent without nested.
SELECT e.Fname, e.Lname
FROM EMPLOYEE e
JOIN DEPENDENT d ON e.ssn = d.essn
JOIN DEPARTMENT dp ON e.ssn = dp.mgr_ssn;
28
... More Complex SQL Queries
2.
The EXISTS Functions (Cntd.)
Query 3: Retrieve the name of each employee who works on all the projects controlled by department number 5
Can be used: (S1 CONTAINS S2) that logically equivalent to (S2 EXCEPT S1) is empty.
SELECT Fname, Lname
FROM EMPLOYEE
WHERE NOT EXISTS (
(SELECT Pnumber FROM PROJECT WHERE DNUM = 5)
EXCEPT
(SELECT Pno FROM WORKS_ON WHERE SSN = ESSN)
);
➔ The first subquery select all projects controlled by dept 5
➔ The second subquery select all projects that particular employee being considered works on.
➔ If the set difference of the first subquery MINUS (EXCEPT) the second subquery is empty, it means that the employee
works on all the projects and is hence selected
29
... More Complex SQL Queries
2.
Exercise
Gunakan data state COMPANY untuk menuliskan query berdasarkan
permintaan berikut.
1. Tampilkan nama depan dan gaji employee yang terlibat pada
project namun memiliki jam kerja null.
2. Tampilkan nama depan manager dan nama department manager
tersebut bekerja dimana project pada departemen tersebut
dikerjakan terdapat karyawan yang memiliki jam kerja null.
3. Tampilkan nama depan dan ssn employee yang mempunyai
departemen dan jenis kelamin yang sama dengan Franklin Wong.
4. Tampilkan nama employee dan nama departmentnya dimana
employee tersebut minimal terlibat pada satu project.
5. Tampilkan nama belakang dan alamat employee yang tidak
memiliki tanggungan anak (Son atau Daughter)
6. Tampilkan nama belakang department manager yang tidak
mempunyai tanggungan.
7. Tampilkan nama depan dan ssn employee dimana project yang
employee tersebut kerjakan selalu sama dengan yang dikerjakan
oleh James Borg.
30
?
Q&A
