Download
1 / 28
E N D
1. Advanced Concepts in SQL Advanced Queries
Views
2. Running Example
3. Advanced Queries Substring operators
Nested queries
4. Substring Operators We can select tuples which attribute value resembles some string
We can use LIKE for that
Select all employees whose address contains Houston, TX
5. Another Wildcard Find All Employees whose SSN has 1 2 3 4 5 on ODD places
_ replaces one character
% replaces arbitrary number of characters
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)
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)
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 >
(SELECT MAX(SALARY)
FROM EMPLOYEE
WHERE DNO=5);
11. Result of Query 101
12. Another Example of Uncorrelated Query 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)
13. Operators Comparison Operators
Include:
<
<=
>
>=
<>
=
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)
16. Note This query could be performed using outer join
17. Results of 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)
20. Results This query results with empty table
21. Note 1 This query is equivalent to nested correlated query (Query16)
22. 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 Note 2
23. Another Example of Correlated Query List the names of managers who have at least one dependent (Query7, Ch7)
24. Result of Query 7
25. SELECT FNAME, LNAME
FROM EMPLOYEE AS E, DEPENDENT AS S, DEPARTMENT AS D
WHERE E.SSN=S.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)
28. Result This query results with an empty table!
This is example of division query!
More to follow about this ?
