Database System Concepts
- Avi Silberschatz
- Henry F. Korth
- S. Sudarshan
Using the university schema, write the following queries.
In some cases you need to insert extra data to show the effect of a particular feature -- this is
indicated with the question. You should then show not only the query, but also the insert statements to
add the required extra data.
- Find the maximum and minimum enrollment across all sections, considering only sections that had
some enrollment, don't worry about those that had no students taking that section
- Find all sections that had the maximum enrollment (along with the enrollment), using a subquery.
- As in in Q1, but now also include sections with no students taking them; the enrollment for such
sections should be treated as 0. Do this in two different ways (and create require data for testing)
- Using a scalar subquery
- Using aggregation on a left outer join (use the SQL natural left outer join syntax)
- Find all courses whose identifier starts with the string "CS-1"
- Find instructors who have taught all the above courses
- Using the "not exists ... except ..." structure
- Using matching of counts which we covered in class (don't forget the distinct clause!).
- Insert each instructor as a student, with tot_creds = 0, in the same department
- Now delete all the newly added "students" above (note: already existing students who happened to
have tot_creds = 0 should not get deleted)
- Some of you may have noticed that the tot_creds value for students did not match the credits
from courses they have taken. Write and execute query to update tot_creds based
on the credits passed, to bring the database back to consistency. (This query is provided in
- Update the salary of each instructor to 10000 times the number of course sections they have
- Create your own query: define what you want to do in English, then write the query in SQL. Make it
as difficult as you wish, the harder the better.