1 / 22

Fundamentals of Database Systems Fourth Edition El Masri & Navathe

University Of Palestine. Fundamentals of Database Systems Fourth Edition El Masri & Navathe. Chapter 8 (Cont.) SQL-99: Schema Definition, Basic Constraints, and Queries. Database Management CH 8. University Of Palestine. SQL: USE OF DISTINCT

cayla
Download Presentation

Fundamentals of Database Systems Fourth Edition El Masri & Navathe

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. University Of Palestine Fundamentals ofDatabase SystemsFourth EditionEl Masri & Navathe Chapter 8 (Cont.) SQL-99: Schema Definition, Basic Constraints, and Queries

  2. Database Management CH 8 University Of Palestine • SQL: USE OF DISTINCT • To eliminate duplicate tuples in a query result, the keyword DISTINCT is used • For example, the result of Q12 may have duplicate SALARY values whereas Q12A does not have any duplicate values • Q12: SELECT SALARY FROM EMPLOYEE • Q12A: SELECT DISTINCT SALARY FROM EMPLOYEE

  3. Database Management CH 8 University Of Palestine • SET OPERATIONS • SQL has directly incorporated some set operations • There is a union operation (UNION), and in some versions of SQL there are set difference (MINUS) and intersection (INTERSECT) operations • The resulting relations of these set operations are sets of tuples; duplicate tuples are eliminated from the result • The set operations apply only to union compatible relations ; the two relations must have the same attributes and the attributes must appear in the same order

  4. Database Management CH 8 University Of Palestine • SET OPERATIONS (Cont.) • Query 13: Make a list of all project numbers for projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project. • (SELECT PNAME FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith') • UNION • (SELECT PNAME FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER=PNO AND ESSN=SSN • AND LNAME='Smith')

  5. Database Management CH 8 University Of Palestine • SET OPERATIONS (Cont.) • Query 14: Retrieve the name of each employee who is either a manger or as some dependent. • (SELECT FAME, MINIT, LNAME FROM EMPLOYEE, DEARTMENT WHERE SSN=MGRSSN') • UNION • (SELECT FAME, MINIT, LNAME FROM EMPLOYEE, DEPENDENT WHERE SSN=ESSN)

  6. Database Management CH 8 University Of Palestine • SET OPERATIONS (Cont.) • Query 15: Retrieve the name of each manager who has dependents. • (SELECT FAME, MINIT, LNAME FROM EMPLOYEE, DEARTMENT WHERE SSN=MGRSSN') • INTERSECT • (SELECT FAME, MINIT, LNAME FROM EMPLOYEE, DEPENDENT WHERE SSN=ESSN)

  7. Database Management CH 8 University Of Palestine • SET OPERATIONS (Cont.) • Query 15: Retrieve the name of each manager who has dependents. • (SELECT FAME, MINIT, LNAME FROM EMPLOYEE, DEARTMENT WHERE SSN=MGRSSN') • MINUS • (SELECT FAME, MINIT, LNAME FROM EMPLOYEE, DEPENDENT WHERE SSN=ESSN)

  8. Database Management CH 8 University Of Palestine • SET OPERATIONS (Cont.) • Query 16: Retrieve the name of each employee who has some dependent and not a manger. • (SELECT FAME, MINIT, LNAME FROM EMPLOYEE, DEPENDENT WHERE SSN=ESSN) • MINUS • (SELECT FAME, MINIT, LNAME FROM EMPLOYEE, DEARTMENT WHERE SSN=MGRSSN')

  9. Database Management CH 8 University Of Palestine • SET OPERATIONS (Cont.) • Query 17: Retrieve the names of non manager employees. • (SELECT FAME, MINIT, LNAME FROM EMPLOYEE)MINUS • (SELECT FAME, MINIT, LNAME FROM EMPLOYEE, DEARTMENT WHERE SSN=MGRSSN')

  10. Database Management CH 8 University Of Palestine • SET OPERATIONS (Cont.) • Query 18: Retrieve the name of departments that don’t have any project located in “Huston”. • (SELECT DNAME FROM DEPARTMENT) • MINUS • (SELECT DNAME FROM DEPARTMENT, PROJECT • WHERE DNUMBER=DNUM • AND PLOCATION=“Huston”)

  11. Database Management CH 8 University Of Palestine • NESTING OF QUERIES • A complete SELECT query, called a nested query , can be specified within the WHERE-clause of another query, called the outer query • Many of the previous queries can be specified in an alternative form using nesting. • Query 19: Retrieve the name and address of all employees who work for the 'Research' department. • SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE WHERE DNO IN(SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research' )

  12. Database Management CH 8 University Of Palestine • NESTING OF QUERIES (Cont.) • The nested query selects the number of the 'Research' department • The outer query select an EMPLOYEE tuple if its DNO value is in the result of either nested query • The comparison operator IN compares a value v with a set (or multi-set) of values V, and evaluates to TRUE if v is one of the elements in V • In general, we can have several levels of nested queries • A reference to an unqualified attribute refers to the relation declared in the innermost nested query • In this example, the nested query is not correlated with the outer query

  13. Database Management CH 8 University Of Palestine • CORRELATED NESTING OF 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 • Query 20: Retrieve the name of each employee who has a dependent with the same first name as the employee. • SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE E SSN IN(SELECT ESSN FROM DEPENDENT WHERE ESSN=E.SSN AND E.FNAME=DEPENDENT_NAME)

  14. Database Management CH 8 University Of Palestine • NESTING OF QUERIES • Query 21: Retrieve the Names of Managers • SELECT E.FNAME, MINIT, LNAME FROM EMPLOYEE AS E WHERE ESSNIN • (SELECT MGRSSN FROM DEPARTMENT ) • Query 22: Retrieve the Names of Supervisors • SELECT E.FNAME, MINIT, LNAME FROM EMPLOYEE AS E WHERE ESSNIN • (SELECT SUPERSSN FROM EMPLOYEE )

  15. Database Management CH 8 University Of Palestine • THE EXISTS FUNCTION • EXISTS is used to check whether the result of a correlated nested query is empty (contains no tuples) or not • We can formulate Query 20in an alternative form that uses EXISTS as Q20A below • Query 20A: Retrieve the name of each employee who has a dependent with the same first name as the employee. • SELECT FNAME, LNAME FROM EMPLOYEE WHERE EXISTS(SELECT * FROM DEPENDENT WHERE SSN=ESSN AND FNAME=DEPENDENT_NAME)

  16. Database Management CH 8 University Of Palestine • EXPLICIT SETS • It is also possible to use an explicit (enumerated) set of values in the WHERE-clause rather than a nested query. • Query 23: Retrieve the social security numbers of all employees who work on project number 1, 2, or 3. • SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO IN(1, 2, 3)

  17. Database Management CH 8 University Of Palestine • NULLS IN SQL QUERIES • SQL allows queries that check if a value is NULL (missing or undefined or not applicable) • 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 24: Retrieve the names of all employees who do not have supervisors.SELECT FNAME, LNAME FROM EMPLOYEE WHERE SUPERSSN IS NULL

  18. Database Management CH 8 University Of Palestine • AGGREGATE FUNCTIONS • Include COUNT, SUM, MAX, MIN, and AVG • Query 25:Find the maximum salary, the minimum salary, and the average salary among all employees. • SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE • Some SQL implementations may not allow more than one function in the SELECT-clause

  19. Database Management CH 8 University Of Palestine • AGGREGATE FUNCTIONS • (Cont.) • Query 26: Find the maximum salary, the minimum salary, and the average salary among employees who work for the 'Research' department. • SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME='Research'

  20. Database Management CH 8 University Of Palestine • AGGREGATE FUNCTIONS • (Cont.) • Queries 27: Retrieve the total number of employees in the 'Research' department • SELECT COUNT (*) FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME='Research’

  21. Database Management CH 8 University Of Palestine • GROUPING • In many cases, we want to apply the aggregate functions to subgroups of tuples in a relation • Each subgroup of tuples consists of the set of tuples that have the same value for the grouping attribute(s) • The function is applied to each subgroup independently • SQL has a GROUP BY-clause for specifying the grouping attributes, which must also appear in the SELECT-clause

  22. Database Management CH 8 University Of Palestine • GROUPING (Cont.) • Query 28: For each department, retrieve the department number, the number of employees in the department, and their average salary. • SELECT DNO, COUNT (*), AVG (SALARY) FROM EMPLOYEEGROUP BY DNO • In 28, the EMPLOYEE tuples are divided into groups--each group having the same value for the grouping attribute DNO • The COUNT and AVG functions are applied to each such group of tuples separately • The SELECT-clause includes only the grouping attribute and the functions to be applied on each group of tuples

More Related