CSC 453 Database Systems Lecture

1. CSC 453 Database SystemsLecture Tanu Malik College of CDM DePaul University

2. Mid-Term • Two topics Relational Model and SQL • Multiple-choice and multiple-select question • Explain your reasoning. In case you have chosen the wrong choice you may get partial points. • 2 hours 15 mins • One page of notes. • No class after the mid-term.

3. Last time • SQL • Basic SQL on two tables • Subqueries • Nested subqueries • Correlated subqueries

4. SQL Queries • General form of a query: SELECT list of attributes to reportFROM list of tables [CROSS JOIN| [LEFT|REIGHT] OUTER JOIN][WHERE tuple condition][GROUP BY list of grouping attributes] [HAVING group condition] [ORDER BY list of ordering attributes] ; [UNION|INTERSECT|EXCEPT] • Result is an ordered set of ordered tuples

5. Last time • SQL • Basic SQL on two tables • Subqueries • Nested subqueries • Correlated subqueries

6. Subqueries • The result of one query may be needed by another to compute its result. • There are two kinds of subqueries • Nested subqueries in which result of a inner subquery is used by outer subquery to compute result • Correlated query in which each tuple of outer query runs aa loop for the inner subqery.

7. Nested Query Example • Find the student who started earliest from ‘Chicago’

8. Example • Find the student who started earliest from ‘Chicago’ SELECT Student.* FROM Student WHERE started = (SELECT min(started) FROM student WHERE city = ‘Chicago’)

9. Correlated Query Example • Find the student who started earliest in Naperville, Chicago, Evanston

10. Example SELECT Student.* FROM Student WHERE started = (SELECT min(started) FROM student WHERE city = ‘Chicago’) AND city = ‘Chicago’ UNION SELECT Student.* FROM Student WHERE started = (SELECT min(started) FROM student WHERE city = ‘Naperville’) AND city = ‘Naperville’ UNION (SELECT Student.* FROM Student WHERE started = (SELECT min(started) FROM student WHERE city = ‘Evanston’) AND city = ‘Evanston’))

11. Example SELECT Student.* FROM Student S1 WHERE started = (SELECT min(started) FROM student S2 WHERE S2.city = S1.city)

12. Subquery check • Different ways of checking: • Within the inner query set • Not within the inner query set • Against all members of the inner query set • Against any one member of the inner query set • Does the set exists

13. Returning a Single Value Result • On the LHS of a WHERE clause SELECT LastName, FirstName, SID FROM student WHERE (SELECT count(*) FROM enrolled WHERE SID = StudentID) >= 2; • As part of SELECT clause SELECT LastName, FirstName, SID, (SELECT count(*) FROM enrolled WHERE SID = StudentID) AS EnrCrs FROM student; Correlation

14. Subquery Practice

15. Practice List members of HerCTI that are not enrolled in courses. Courses not offered in 2013 (i.e. no record of anybody being enrolled).

16. Practice • List the oldest studentgroup • List students belonging to the first studentgroup

17. Practice • List courses that have a unique number • For all departments list the highest course number used by that department • List student groups that have both graduate and undergraduate members.

18. Mixed Practice • List students who are members of all studentgroups • List students who have taken courses in all departments • List students who have enrolled in courses every year that courses were offered

19. Update/Delete Statements • UPDATE table_nameSET column1 = value1, column2 = value2, ...WHERE condition; • DELETE FROM table_nameWHERE condition; • INSERT INTO table2SELECT * FROM table1WHERE condition;

20. A Database View • A database view is a searchable object in a database that is defined by a query. • As opposed to a CREATE TABLE definition. • Views may or may not store data • Materalized vs non-materialized tables (“virtual tables,”) • Views are queryable like a table. • A view can combine data from two or more table, using joins, and also just contain a subset of information.

21. Reasons for Using A Database View • To avoid repeated querying • Hide information • grant access to relevant information • Simplify queries by improving readability • not necessarily a good reason to create a view in general, if temporary table is sufficient

22. To avoid repeated querying CREATE OR REPLACE VIEW CSstudents AS SELECT * FROM student WHERE Program = 'COMP-SCI‘; • Usage: SELECT * FROM CSstudents; • base tables (CREATE TABLE) stored in database • views (CREATE VIEW) • dependent on base tables or other views, may or may not be stored (virtual vs materialized)

23. To hide information CREATE VIEW studentview AS SELECT LastName, FirstName, SID, Career, Program FROM student; • Usage SELECT name FROM studentgroup WHERE name NOT IN (SELECT groupname FROM CSstudents, memberof WHERE StudentID = SID);

24. Improving readability CREATE VIEW enrollment(SID, LName, CID, CNR, Dpt) AS SELECT SID, LastName, CID, CourseNr, Department FROM student, enrolled, course WHERE SID = studentID AND CourseID = CID; • Usage SELECT count(*) FROM enrollment WHERE CNR = 440 AND Dpt = 'CSC';

25. Temporary Tables create global temporary table gradstudent( LASTNAME VARCHAR2(40), SID NUMBER(5,0), PROGRAM VARCHAR2(10), primary key(sid) ) on commit delete rows; • lifetime of temporary data is limited to session insert into gradstudent select lastname, sid, program from student where career = ‘GRD’; • or “on commit preserve rows” • table exists beyond session

26. Common Table Expression (CTE) WITH GradStudents AS (SELECT SID, LastName, SSN FROM student WHERE Career = ‘GRD') SELECT * FROM enrolled WHERE StudentID NOT IN (SELECT SID FROM GradStudents); • Temporary table, exists only for lifetime of query, • cannot be used in other queries • can create multiple such tables

27. CTE Example WITH StudentEnrollment(SID, Quarter, Year, crs_nbr) AS (SELECT StudentID, Quarter, Year, count(CourseID) FROM enrolled GROUP BY StudentID, Quarter, Year), StudentMax(SID, maxcrs) AS (SELECT SID, max(crs_nbr) FROM StudentEnrollment GROUP BY SID) SELECT * FROM student S, StudentMax SM WHERE S.SID = SM.SID; • temporary table can refer to previous temporary table • mutual recursion not allowed (in Oracle)

28. Modifying Views • DROP VIEW Csstudents; • What about other objects that depend on it (e.g other views)? • How is/are the underlying base table(s) affected? INSERT INTO CSstudents(LastName, FirstName, SID) VALUES ('Crackenden', 'Gloria', 123); • What do INSERT, DELETE, UPDATE mean for a view?

29. Updateable Views • “An updatable view is one you can use to insert, update, or delete base table rows.” • Roughly: • FROM contains only a single relation • no DISTINCT, aggregation, set, calculated value • WHERE clause may not contain a sub-query involving the relation the view is based on • Statement can still fail (e.g. if primary key is missing in INSERT or non-null attributes are not included)