# 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