ps0

Databases

Problem Set 0: SQL queries and the UU database

Attempt seriously by Tuesday, February 21, before class

Submit via MySLC by Friday, February 24 at 5pm

Instructions

Work entirely on your own. (Questions for the instructor are welcome!)
Read the entire assignment thoroughly before beginning to write your solutions.
Use only the SQL we have covered in class (unless otherwise indicated). If you are not sure whether you can or should use a particular SQL construct, just ask.
The problems relate to the UU schema we have begun using in class. (The schema is provided for your reference at the end of this document.)
You may wish to use SQLite (whether the GUI version or the command-line version) to test your queries. (Download an SQLite instance of the UU database.)
Type your solutions in a plain text (.txt) file.
Submit your work by uploading your file via MySLC.

For each of the following, indicate whether the statement is true, false, or unspecified according to the UU schema. Explain why, but keep your explanations very brief. (For example, you might write, “True because of the foreign key constraint between blah and blah.”)
1. Different students may have the same first and last name.
2. Every student must have a don.
3. Students can have more than one don.
4. No two members of the faculty can have the same last name, even if they have different first names.
5. Sometimes two or more professors may share the same phone number.
6. Professors may teach more than one course, though some slackers may not teach any.
7. Some courses taught in different departments may have the same title.
8. No two distinct courses taught by the same professor may have the same title.
Write SQL queries to answer the following questions. (Just write the SQL, do not include the results that the the given query would return for a snapshot of the UU database. I have provided a partial example of results to help clarify what is being asked.)
1. What courses are being offered by instructors who are also dons?
```
   title        teacher
   -----------  -----------
   Cyberpunk    Siff
     ...
```
2. What are the id numbers of students who have the same last name as their don?
```
    id
    --
    5
      ...
```
3. How many students are signed up for both math and lit courses? Solve two different ways: one without subqueries, one with subqueries.
```
    COUNT(*)
    --------
      ...
```
4. What are the identification numbers of students who either have the first name Ada or are registered for a class that allows the enrollment to exceed 100?
```
    id
    --
    2
      ...
```
Write SQL queries that correspond to the following descriptions. (Again, just write the SQL, do not include the results that the the given query would return for a snapshot of the UU database. I have provided a partial example of results to help clarify what is being asked.)
1. List the first names of pairs of students who have the same last name, but different first names.
```
    first_name  first_name
    ----------  ----------
    Dot         Nancy
      ...
```
2. List the first and last names of instructors who are dons but are not teaching any courses. The list should be in order of first name (with ties broken by last name).
```
    first_name  last_name
    ----------  ---------
    Abby        Straction
      ...
```
3. List the first and last names of students who have the same first name as an instructor for at least one of the courses for which they have registered.
```
    first_name  last_name
    ----------  ---------
    Dan         Data
      ...
```
4. For each student enrolled in at least one course, list their first and last names, ID#s, and the number of courses (n) for which they are enrolled in descending order of the the number of courses with ties broken by increasing ID#.
```
    first_name  last_name    id  n
    ----------  -----------  --  -
      ...
```
5. List, in alphabetical order without duplicates, the last names of teachers such that the maximum capacity of one (or more) of their courses is at least twice that of another of their courses.
```
    teacher
    -------
      ...
```
6. Find the most common first name among all students and faculty. Include ties, that is, if Jim and Mike are tied as the most common names, list both, in alphabetical order.
```
    first_name
    ----------
      ...
```
Consider this sequence of relational algebra expressions:
1. Translate it into a single equivalent SQL query. (Your translation should not be too literal, but it should capture the same meaning.)
2. In a clear and concise sentence, explain what the query achieves. (For example, “It finds all the professors who teach cosmology courses.” Example of what not to write: “It selects the foo attributes from the bar and buzz tables.”)
Consider this SQL query:
```
 SELECT id, first_name, last_name
   FROM Students NATURAL JOIN Registry NATURAL JOIN Professors
  WHERE department = 'econ'
```
Explain what you think is the intended goal of the query, why the query will fail to achieve that goal, and offer an SQL query that will achieve that goal.

In a clear and concise sentence, explain what the following query achieves:

 SELECT first_name, last_name
   FROM Students
  WHERE (SELECT COUNT(*)
           FROM Registry
          WHERE student_id = id) IN
                (SELECT MAX(Temp.n) 
                   FROM (SELECT student_id, COUNT(*) AS n
                           FROM Registry
                          GROUP BY student_id) AS Temp)

Write and SQL query to compute the average capacity of a UU course, assuming that the capacity is 15 if it is not explicitly specified in the database.
Read about SQL conditional expressions and the CASE operator. Given what you learn, rewrite your solution to the previous problem more compactly.

UU schema

CREATE TABLE Professors (
    PRIMARY KEY (last_name),
    last_name  CHARACTER VARYING(50) NOT NULL,
    first_name CHARACTER VARYING(50),
    department CHARACTER VARYING(50),
    phone      INTEGER
);

CREATE TABLE Courses (
    PRIMARY KEY (title, teacher),
    title   CHARACTER VARYING(50) NOT NULL,
    teacher CHARACTER VARYING(50) NOT NULL,
    cap     INTEGER,
    FOREIGN KEY (teacher) REFERENCES professors(last_name)
);

CREATE TABLE Students (
    PRIMARY KEY (id),
    id         INTEGER NOT NULL,
    first_name CHARACTER VARYING(50) NOT NULL,
    last_name  CHARACTER VARYING(50) NOT NULL,
    years_left INTEGER DEFAULT 4,
    don        CHARACTER VARYING(50),
    FOREIGN KEY (don) REFERENCES professors(last_name)
);

CREATE TABLE Registry (
    PRIMARY KEY (student_id, title, teacher),
    student_id INTEGER NOT NULL,
    title      CHARACTER VARYING(50) NOT NULL,
    teacher    CHARACTER VARYING(50) NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (title, teacher) REFERENCES courses(title, teacher)
);