# For this assignment, use the university database use university; # This assignment involves more than one table. # It would be very helpful to review the content in each table so you know how they are related. select * from instructor; select * from teaches; select * from course; select * from department; select * from student; select * from takes; # Write SQL statement to complete the following tasks # 1. Find the lowest salary of any instructor # 2. Find the names of all instructors earning the lowest salary (there may be more than one with the same salary). # 3. Find all instructors whose salary information is not null # 4. List the instructor names and the course title that an instructor has taught. # Remove duplicate. Sort by instructor name alphabetically. # Hint: you need the data from three tables: instructor, teaches, course. Expect 14 rows. # 5. Using the IN keyword, list all course titles that are taught by Mozart and Srinivasan. # Hint: you need the data from three tables: instructor, teaches, course. Expect 4 rows. # 6. For each department, find the maximum salary of instructors in that department. # You may assume that every department has at least one instructor. # 7. Find the lowest, across all departments, of the per-department maximum salary computed by the preceding query. # Give the resulting column a meaningful name, like "lowest_dept_cap". # Hint: MySQL asks each derived table to have its own alias. That is, you need to give alias to subqueries. # 8. Write an SQL query to find the names and budgets of those departments # whose budget is higher than that of Music. List them by budget from lowest to highest. # Hint: department budget is stored in department table, expect 4 rows. # 9. Find the number of department that is located in any building. # In result list only building names and the number of department in that bulding. # Hint: query the department table. Expect 4 rows # 10. Based on previous query, list only buildings that have at least 2 departments in it # 11. For each student who has taken a course at least twice, # show the student's ID and the course ID. # Hint: use takes table, think first how you can list how many times a student has taken a course

# For this assignment, use the university database use university; # This assignment involves more than one table. # It would be very helpful to review the content in each table so you know how they are related. select * from instructor; select * from teaches; select * from course; select * from department; select * from student; select * from takes; # Write SQL statement to complete the following tasks # 1. Find the lowest salary of any instructor # 2. Find the names of all instructors earning the lowest salary (there may be more than one with the same salary). # 3. Find all instructors whose salary information is not null # 4. List the instructor names and the course title that an instructor has taught. # Remove duplicate. Sort by instructor name alphabetically. # Hint: you need the data from three tables: instructor, teaches, course. Expect 14 rows. # 5. Using the IN keyword, list all course titles that are taught by Mozart and Srinivasan. # Hint: you need the data from three tables: instructor, teaches, course. Expect 4 rows. # 6. For each department, find the maximum salary of instructors in that department. # You may assume that every department has at least one instructor. # 7. Find the lowest, across all departments, of the per-department maximum salary computed by the preceding query. # Give the resulting column a meaningful name, like "lowest_dept_cap". # Hint: MySQL asks each derived table to have its own alias. That is, you need to give alias to subqueries. # 8. Write an SQL query to find the names and budgets of those departments # whose budget is higher than that of Music. List them by budget from lowest to highest. # Hint: department budget is stored in department table, expect 4 rows. # 9. Find the number of department that is located in any building. # In result list only building names and the number of department in that bulding. # Hint: query the department table. Expect 4 rows # 10. Based on previous query, list only buildings that have at least 2 departments in it # 11. For each student who has taken a course at least twice, # show the student's ID and the course ID. # Hint: use takes table, think first how you can list how many times a student has taken a course