LAB 4: INTERMEDIATE SQL COMMANDS and JOIN OPERATION
For join operation refer the document attached below :
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 the book/slides.)
- Update the salary of each instructor to 10000 times the number of course sections they have taught.
- 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.