# 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
Chapter13: Views
Section: Chapter Questions
Problem 3MC
Related questions
Question
# 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
AI-Generated Solution
AI-generated content may present inaccurate or offensive content that does not represent bartleby’s views.
Unlock instant AI solutions
Tap the button
to generate a solution
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning