Within each department terms sorted in descending

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Within each department, terms sorted in descending order. • What if DISTINCT omitted? What if term omitted from SELECT clause? W hat if dept omitted from GROUP BY clause? W hat if dept omitted from ORDER BY clause? 10 Course Example dept cnum instructor term SELECT dept, term, COUNT(DISTINCT instructor) AS num_instructors FROM Course GROUP BY dept, term; ORDER BY dept, term DESC; SUMMARY OF SQL QUERIES 1. Assemble all tables according to From clause (“,” means to use ). 2. Keep only tuples matching W here clause. 3. Group into blocks based on Group By clause. 4. Keep only blocks matching Having clause. 6. Order resulting tuples according to Order By clause. 11 5. Create one tuple for each block using Select clause. NESTED QUERIES Any table can be used in FROM clause. select-from-where produces a table. Thus can nest one query within another. Example: Give the biographical information for directors of profitable movies . Film title genre year director minutes budget gross Person name birth city 12 SELECT name, birth, city FROM ( SELECT director FROM Film WHERE gross > budget) AS Profitable, Person WHERE director = name NESTED QUERIES (CONT’D.) Any colum...
View Full Document

Ask a homework question - tutors are online