603 Database Systems

1. 603 Database Systems Senior Lecturer: Laurie Webster II, M.S.S.E.,M.S.E.E., M.S.BME, Ph.D., P.E. Lecture 20 A First Course in Database Systems

2. SQL Selection in SQL: The selection operator of relational algebra, and much more, is available through the WHERE clause in SQL. SELECT L FROM R WHERE C  L( C (R))

3. SQL SELECT L FROM R WHERE C  L( C (R)) We start with the relation in the FROM clause, apply to each tuple whatever condition is indicated in the WHERE clause, and then project onto the list of attributes and / or expressions in the SELECT clause.

4. SQL The following query asks for all the movies made after 1970 that are in black-and-white. SELECT title FROM movie WHERE year > 1970 AND NOT inColor ; In this condition, we have the AND of two booleans. The first is an ordinary comparison, but the second is the attribute inColor, negated.

5. SQL Consider the Query: SELECT title FROM Movie WHERE (year . 1970 OR length < 90) AND studioName = ‘MGM’ ; The parentheses are needed here because the precedence of logical operators in SQL is the same as in most other languages: AND takes precedence over OR, and NOT takes precedence over both.

6. SQL Retrieve the name and address of all employees who work in the ‘Research’ department. SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME = ‘Research’ AND DNUMBER = DNO ; FNAME LNAME ADDRESS John Smith 731 Fondren, Houston, Tx Franklin Wong 638 Voss, Houston, Tx Ramesh Narayan 975 Fire Oak, Humble, Tx Joyce English 5631 Rice, Houston, Tx

7. SQL For every project located in ‘Stafford’, list the project number, the controlling department number, and the department managers’ last name , address, and birthdate. SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION=‘Stafford’ ;

8. SQL SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION=‘Stafford’ ; The JOIN condition DNUM=DNUMBER relates a project to its controlling department

9. SQL SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION=‘Stafford’ ; The JOIN condition MGRSSN=SSN relates the controlling department to the employee who manages that department

10. The Database Language SQL Dealing With Ambiguous Attribute Names and Aliasing: In SQL the same name can be used for two (or more) attributes ==> attributes must be in different relations Given that this happens and a query refers to two or more attributes with the same name ==> we must qualify the attribute with the relation name to prevent ambiguity

11. SQL Language Dealing With Ambiguous Attribute Names and Aliasing: To qualify the attribute name with the relation name prefix the relation name to the attribute name and separate the two by a period. Ex. COMPANY Schema - DNO and LNAME of EMPLOYEE relation were called DNUMBER and NAME and DNAME attribute of DEPARTMENT was called NAME.

12. SQL Language Ex. COMPANY Schema - DNO and LNAME of EMPLOYEE relation were called DNUMBER and NAME and DNAME attribute of DEPARTMENT was called NAME. SELECT FNAME, EMPLOYEE.NAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DEPARTMENT.NAME=‘Research’ AND DEPARTMENT.DNUMBER=EMPLOYEE.DNUMBER ;

13. SQL Ambiquity also arises when queries refer to the same relation twice!! Ex. For each employee, retrieve the employee’s first name and last name and the first and last name of his or her immediate supervisor. SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE E EMPLOYEE S WHERE E.SUPERSSN = S.SSN E & S ==> aliases for EMPLOYEE relation

14. SQL Unspecified WHERE clauses and use of ‘*’: A missing WHERE clause indicates no condition on tuple selection, hence, all tuples of the relation specified in the FROM clause qualify to be selected for query result A missing WHERE clause  condition WHERE TRUE  for every row in the tuple

15. SQL Query: Select all EMPLOYEE SSNs. SELECT SSN FROM EMPLOYEE SSN 123456789 333445555 .  for all tuples in EMPLOYEE . 88866555

16. SQL Select all combinations of an EMPLOYEE SSN and a Department DNAME. SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT result is the Cartesian Crossproduct

17. SQL Retrieve all attribute values of EMPLOYEE tuples who work in DEPARTMENT number 5. SELECT * FROM EMPLOYEE WHERE DNO=5 ; RESULT => all attributes for employees of DNO=5.

18. SQL Retrieve all the attributes of an EMPLOYEE and the attributes of the DEPARTMENT he or she works in for every employee of the ‘Research’ department. SELECT * FROM EMPLOYEE, DEPARTMENT WHERE DNAME=‘Research’ AND DNO=DNUMBER ;

19. SQL Specify the Cross Product of the EMPLOYEE and DEPARTMENT relations. SELECT * FROM EMPLOYEE , DEPARTMENT

20. SQL Tables as Sets in SQL: In general SQL does not treat a relation as a set ==> duplicate tuples can appear more than once or in the result of a query

21. SQL SQL does not automatically eliminate duplicate tuples in the results of queries for the following reasons: * Duplicate elimination is an expensive operation. One way to implement it is to sort the tuples first and then eliminate them. * The user may want to see duplicate tuples in the result of a query. * When an aggregate function is applied to tuples, in most cases we don’t want to eliminate duplicates.

22. SQL If you want to eliminate duplicates of tuples from the result of a SQL query use the keyword DISTINCT in the SELECT clause ==> distinct tuples should remain in the result ==> a relation that is a set of tuples

23. SQL If we are interested only in distinct salary values (each value appearing once) use keyword DISTINCT: SELECT DISTINCT SALARY FROM EMPLOYEE SALARY 30000 40000 50000

24. SQL Next Lecture MORE SQL