IT420: Database Management and Organization

IT420: Database Management and Organization SQL part 3 7 February 2006 Adina Crăiniceanu www.cs.usna.edu/~adina

Announcements • Exam next Tuesday • 2 hours • Closed book/closed notes • No computers • Covers all material • Labs returned on Friday

Previously • SQL DDL: Data Definition Language • CREATE, DROP, ALTER • DML: Data Manipulation Language • INSERT • DELETE • UPDATE • SELECT

Today • More about SELECT

The SQL SELECT Statement • Basic SQL Query: SELECT [DISTINCT] column_name(s) FROM table_name(s) [WHERE conditions] [ORDER BY some_column_names [ASC/DESC] ] *Terms between [ ] are optional

WHERE Clause Options • AND, OR • IN, NOT IN, BETWEEN • =, >, <, <>, >= SELECT SNb FROM Students S, Enrolled E WHERE S.SNb = E.Nb AND E.Cid NOT IN (‘ComSci’, ‘Math’)

Calculations in SQL • Simple arithmetic • Five SQL aggregate operators: • COUNT • SUM • AVG • MIN • MAX

Find the age of the youngest student • Students(Alpha, LName, FName, Class, Age) • SELECT MIN(Age) FROM Students

Aggregate Operators • So far, aggregate operations applied to all (qualifying) rows • We want to apply them to each of several groups of rows • Students(Alpha, LName, SName, Class, Age) • Find the age of the youngest student for each class

Example • Students(Alpha, LName, FName, Class, Age) • Find the age of the youngest student for each class • If class values go from 1 to 4 we can write 4 queries that look like this: • SELECT MIN (S.Age) FROM Students S WHERE S.Class = i

GROUP-BY Clause • SELECT grouping_columns(s), aggregates FROM table_name(s) [WHERE conditions] GROUP BY grouping_columns • SELECT Class, MIN(Age) FROM Students GROUP BY Class

Conceptual Evaluation • SQL query semantics: • Compute the cross-product of table_names • Discard resulting rows if they fail conditions • Delete columns that are not specified in SELECT • Remaining rows are partitioned into groups by the value of the columns in grouping-columns • One answer row is generated per group • Note: Does not imply query will actually be evaluated this way!

GROUP BY Exercise • Students(Alpha, LName, FName, Class, Age) • For each last name, find the number of students with same last name

HAVING Clause • SELECT [grouping_columns(s), aggregates FROM table_name(s) [WHERE conditions] GROUP BY grouping_columns HAVING group_conditions • HAVING restricts the groups presented in the result

Example • SELECT Class, MIN(Age) FROM Students WHERE MajDeptName = ‘ComSci’ GROUP BY Class HAVING Class > 2 What does the query compute?

Another GROUP BY Example • SKU_DATA(SKU, SKU_description, Buyer, Department) • SELECT Department, COUNT(*) AS Dept_SKU_Count FROM SKU_DATA WHERE SKU <> 302000 GROUP BY Department HAVING COUNT (*) > 1 ORDER BY Dept_SKU_Count;

Select students with age higher than average • SELECT * FROM Students WHERE Age > AVG(Age) Illegal!

Subqueries • SELECT * FROM Students WHERE Age > (SELECT AVG(Age) FROM Students) • Second select is a subquery (or nested query) • You can have subqueries in FROM or HAVING clause also

Subqueries Exercise • Students(Alpha, LName, FName, Class, Age) • Enroll(Alpha, CourseID, Semester, Grade) • Find alpha for students enrolled in both ‘IT420’ and ‘IT334’ • Find name of students enrolled in both ‘IT420’ and ‘IT334’

Class Exercise • Students(Alpha, LName, FName, Class, Age) • Enroll(Alpha, CourseID, Semester, Grade) • Find the name of students enrolled in ‘IT420’ • Usual way • Use subqueries

SELECT FName, LName FROM Students S WHERE S.Alpha IN (SELECT Alpha FROM Enroll E WHERE E.CourseID = ‘IT420’)

Correlated Subqueries • SELECT FName, LName FROM Students S WHERE EXISTS (SELECT * FROM Enroll E WHERE E.CourseID = ‘IT420’ AND E.Alpha = S.Alpha)

Subqueries versus Joins • Subqueries and joins both process multiple tables. • Subquery can only be used to retrieve data from the top table. • Join can be used to obtain data from any number of tables • Correlated subquery can do work that is not possible with joins.

Class Exercise • What does this query compute: • SELECT FName, LName FROM Students S, Enroll E1, Enroll E2 WHERE S.Alpha = E1.Alpha AND S.Alpha = E2.Alpha AND E1.CourseID = ‘IT420’ AND E2.CourseID = ‘IT344’

JOIN ON Syntax • Students(Alpha, LName, FName, Class, Age) • Courses(CourseID, Description, Textbook) • Enroll(Alpha, CourseID, Semester, Grade) • Find the names of students enrolled in ‘IT420’ SELECT LName, FName FROM Students S JOIN Enroll C ON S.Alpha = C.Alpha WHERE CourseID = ‘IT420’ • Find the names of students enrolled in ‘Database Management’

Multiple JOIN ON • Find the names of students enrolled in ‘Database Management’ • SELECT LName, FName FROM Enroll E JOIN Courses C ON E.CourseID = C.CourseID JOIN Students ON E.Alpha = S.Alpha WHERE C.Description = ‘Database Management’

Outer Joins • Find all students and courses in which they are enrolled • SELECT S.Alpha, S.LName, S.FName, E.CourseID FROM Students S LEFT JOIN Enrolled E ON S.Alpha = E.Alpha

Class Exercise • Students(Alpha, LName, FName, Class, Age) • Courses(CourseID, Description, Textbook) • Enroll(Alpha, CourseID, Semester, Grade) • Find the age of youngest student older than 18, for each course with at least one such student enrolled in it

IT420: Database Management and Organization