Advanced Concepts in SQL

1
Advanced Concepts in SQL

• Advanced Queries
• Views

2
Running Example
3
Advanced Queries

• Substring operators
• Nested queries

4
Substring Operators
Type the query directly into postgresql!

• We can select tuples which attribute value
  resembles some string
• We can use LIKE for that
• Select all employees whose address contains
  Houston, TX

Wildcards
SELECT FNAME, LNAME FROM EMPLOYEE
WHERE ADDRESS LIKE
Houston,TX
5
Another Wildcard
Type the query directly into postgresql!

• Find All Employees whose SSN has 1 2 3 4 5 on ODD
  places
• _ replaces one character
• replaces arbitrary number of characters

SELECT FNAME, LNAME FROM EMPLOYEE WHERE SSN
LIKE 1_2_3_4_5
6
Between Operator

• Can be used when the attribute values are ordered
• Retrieve all employees in department 5 with
  salary between 30,000 and 40,000 (Query 14)

SELECT FROM EMPLOYEE WHERE (SALARY BETWEEN
30000 AND 40000) AND DNO 5
7
Result of Query 14
8
Nested Queries

• Nested queries contain another query (usually in
  WHERE statement)
• Can be
• Correlated when the attribute from outer query
  participates in inner query
• Non-correlated, otherwise

9
Uncorrelated Queries

• Return the names of employees with salary larger
  than salaries of ALL employees from Department 5
  (Query101)

SELECT EMPLOYEE.LNAME, EMPLOYEE.FNAME FROM
EMPLOYEE WHERE SALARY gt All (SELECT SALARY
FROM EMPLOYEE WHERE DNO5)
Operator
Inner query
Outer query
10
Observe

• The inner query is executed only once and does
  not dependent on the outer query
• This is the reason we call this
  uncorrelatedquery
• Note The query can be reformulated as the
  following uncorrelated query (Query101a)
• SELECT EMPLOYEE.LNAME, EMPLOYEE.FNAME
• FROM EMPLOYEE
• WHERE SALARY gt
• (SELECT MAX(SALARY)
• FROM EMPLOYEE
• WHERE DNO5)

11
Result of Query 101
12
Another Example of Uncorrelated Query
Type the query directly into postgresql!

• Select SSN of all employees who works the same
  number of hours on some project where employee
  with ssn 123456789 works (Query102 that does
  not work in MSAccess)

  SELECT DISTINCT ESSN FROM WORKS_ON WHERE
(PNO, HOURS) IN
(SELECT PNO, HOURS
FROM WORKS_ON
WHERE
ESSN123456789)
Operator
Inner query
13
Operators

• Comparison Operators
• Include
• lt
• lt
• gt
• gt
• ltgt
• Can be combined with ANY and SOME
• Set Operators
• IN, NOT IN

14
Correlated Queries

• In correlated queries, the inner query is
  executed FOR EACH tuple examined in outer query
• Hence, correlated queries may be expensive to
  execute
• Sometimes, correlated queries can be replaced by
  simpler, unnested queries

15
Example of Correlated Query

• List names of employees who do not have any
  dependents (Query 6, Ch7)

SELECT FNAME, LNAME FROM EMPLOYEE E WHERE NOT
EXISTS (SELECT FROM DEPENDENT
WHERE E.SSNESSN)
We select a tuple only if it does not
have dependents
In inner query, for each tuple from outer query
we search for dependents Inner query should be
repeated for EACH Tuple in EMPLOYEE (from outer
query)
16
Note

• This query could be performed using outer join

SELECT FNAME, LNAME FROM EMPLOYEE LEFT OUTER JOIN
DEPENDENT ON SSNESSN WHERE ESSN IS NULL
17
Results of Query6
Query6
18
Operators

• Here, in addition to operators for uncorrelated
  queries, operators may be
• EXIST
• NOT EXIST

19
Another Example of Correlated Query

• Retrieve the name of each employee who has a
  dependent with the same first name and the same
  sex (Query16B)

SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E
WHERE EXISTS (SELECT
FROM DEPENDENT WHERE
E.SSNESSN AND E.SEXSEX
AND E.FNAMEDEPENDENT_NAME)
20
Results

• This query results with empty table

21
Note 1

• This query is equivalent to nested correlated
  query (Query16)

SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE
E.SSN IN (SELECT ESSN FROM
DEPENDENT WHERE E.FNAMEDEPENDENT_NAME
AND E.SEXSEX)
22
Note 2

• Both queries can be represented as following
  unnested query (Query16A)
• Generally, correlated nested queries with SELECT,
  FROM, WHERE and IN operator can ALWAYS be
  represented as UNNESTED queries

SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E,
DEPENDENT AS D WHERE E.SSND.ESSN AND
E.SEXD.SEX AND E.FNAMED.DEPENDENT_NAME
23
Another Example of Correlated Query

• List the names of managers who have at least one
  dependent (Query7, Ch7)

SELECT FNAME, LNAME FROM EMPLOYEE AS E WHERE
EXISTS (SELECT
FROM DEPENDENT WHERE
E.SSNESSN) AND EXISTS (SELECT
FROM DEPARTMENT
WHERE E.SSNMGRSSN)
List employee if s/he exists As manager
and if s/he exists As owner of the dependent
24
Result of Query 7
25

• SELECT FNAME, LNAME
• FROM EMPLOYEE AS E, DEPENDENT AS S, DEPARTMENT AS
  D
• WHERE E.SSNS.ESSN AND

26
Multiple Nesting

• If necessary, we can apply multiple nesting
• Remember however that such queries can be
  prohibitively slow!!!
• Example Retrieve the names of each employe who
  works on ALL projects controlled by department
  number 5

27
One of possible solution (Query3B)
SELECT LNAME, FNAME FROM EMPLOYEE AS E WHERE NOT
EXISTS (SELECT FROM
WORKS_ON B WHERE (B.PNO IN
(SELECT PNUMBER
FROM PROJECT
WHERE DNUM5) )
AND NOT EXISTS (SELECT
FROM
WORKS_ON C
WHERE C.ESSNE.SSN
AND C.PNOB.PNO) )
28
Result

• This query results with an empty table!
• This is example of division query!
• More to follow about this ?

Note