SQL Basics Practice Problems

Consider this schema from the textbook for a university database:

university-schema.png

Write the following queries in SQL, using this schema. You can test your answers at https://www.db-book.com/db7/university-lab-dir/sqljs.html

  1. Find the titles of courses in the Comp. Sci. department that have 3 credits.1
  2. Find the IDs of all students who were taught by an instructor named Einstein; make sure there are no duplicates in the result.2
  3. Find the ID and name of each student who has taken at least one Comp. Sci. course; make sure there are no duplicate names in the result.3
  4. Find the course id, section id, and building for each section of a Biology course.4
  5. Output instructor names sorted by the ratio of their salary to their department's budget (in ascending order).5
  6. Output instructor names and buildings for each building an instructor has taught in. Include instructor names who have not taught any classes (the building name should be NULL in this case).6

Footnotes:

1 
SELECT title
FROM course
WHERE dept_name = 'Comp. Sci' AND credits = 3;
2 
SELECT DISTINCT takes.ID
FROM takes, teaches, instructor
WHERE takes.course_id = teaches.course_id
  AND takes.sec_id = teaches.sec_id
  AND takes.semester = teaches.semester
  AND takes.year = teaches.year
  AND teaches.ID = instructor.ID
  AND instructor.name = 'Einstein';
3 
SELECT DISTINCT takes.ID, name
FROM takes, student, course
WHERE takes.ID = student.ID
  AND takes.course_id = course.course_id
  AND course.dept_name = 'Comp. Sci.'
4 
SELECT section.course_id, sec_id, building
FROM section, course
WHERE section.course_id = course.course_id
  AND course.dept_name = 'Biology';
5 
SELECT name
FROM instructor, department
WHERE instructor.dept_name = department.dept_name
ORDER BY CAST(salary AS FLOAT) / budget;
6 
SELECT DISTINCT name, building
FROM instructor LEFT OUTER JOIN teaches ON teaches.ID = instructor.ID
LEFT OUTER JOIN section ON teaches.course_id = section.course_id
AND teaches.sec_id = section.sec_id
AND teaches.semester = section.semester
AND teaches.year = section.year;