Lab 3: SQL revisited

Task 0: Prepare for lab

1. In last week’s lab, you should have downloaded:

   • DB Browser for SQLite
   • the university database

   If you didn’t, you should go ahead and do so now.

2. Launch DB Browser for SQLite.

3. Click the Open Database button, and find and open the university database, which you should have saved in your lab2 folder in a file named university.sqlite.

Task 1: Create a table and insert rows

1. Click on the Database Structure tab. You should see the list of tables that are currently in the database, along with the CREATE TABLE command for each table.

2. Some students have work-study jobs in which they work for a department at the university. Let’s assume that we want to create a table called WorksFor to capture the relationships between students and the departments for which they work. Each row in this new table should include:

   • the id number of a student
   • the name of a department
   • the title of the job that the student holds for that department

   Construct a CREATE TABLE command for this new table. Choose appropriate column names and types, and make sure to specify the primary key of the table and any foreign keys.

   You may assume that:

   • Students have at most one job for a department, and thus a given student will appear at most once in this table.

   • Job titles will have lengths that vary widely, but they will be no more than 30 characters in length.

   Use the CREATE TABLE commands for the existing tables as models. Among other things, you should consult them to ensure that your foreign key(s) have data types that are consistent with the types of the columns to which they refer.

   CREATE TABLE WorksFor(student_id CHAR(9) PRIMARY KEY, 
       dept_name VARCHAR(20), job_title VARCHAR(30), 
       FOREIGN KEY (student_id) REFERENCES Student(id),
       FOREIGN KEY (dept_name) REFERENCES Department(name));
   

   Note: We only need student_id for the primary key, because a given student appears at most once in this table.

3. Try entering your new CREATE TABLE command in the Execute SQL tab of DB Browser. Remember that you can run a SQL command by doing any of the following:

   • Press F5
   • Press Ctrl-R
   • Click the small button with a triangular shape that looks like the Play button of a music player.

   At the bottom of the DB Browser window, you should see a message indicating that the command executed successfully.

4. Construct the INSERT command needed to add one row to this table. You may use whatever values you want, as long as you observe the uniqueness and referential-integrity constraints on the table. You may find it helpful to consult the contents of the other tables, which you can do by using the Browse Data tab in DB Browser.

   Here are two possible commands:

   INSERT INTO WorksFor VALUES ('U00000001', 'computer science', 'assistant');
   INSERT INTO WorksFor VALUES ('U00000010', 'mathematics', 'grader');
   

5. Try entering your INSERT command in the Execute SQL tab.

6. Return to the Browse Data tab, and look at the contents of the WorksFor table, which should include the rows you just added.

Task 2: Practice SQL queries

Download the following file:

lab3_queries.py

and put it in your folder for this lab.

Open lab3_queries.py in a Python IDE or a text editor.

In this task, you will continue to work with the university database. Here is its schema:

When constructing each query, we recommend that you follow the rules of thumb given in the lecture notes.

Feel free to work with another student on these problems, and to ask the TA for help as needed.

1. Write a query that finds the major(s) of each student in the database. The results of your query should be tuples of the form (student name, dept name).

   Test the revised SQL command in DB Browser to make sure that it works. Once you have finalized the command, copy the command into your lab3_queries.py file, putting it between the triple quotes provided for the variable query1. We have included a sample query to show you what the format of your answers should look like.

   SELECT name, dept_name
   FROM Student, MajorsIn
   WHERE id = student_id;
   

2. Now revise the previous query so that its results will include students who don’t have a major. Hint: You will need to perform a LEFT OUTER JOIN, with the join condition moved to the ON clause.

   Test your query, revise it as needed, and copy the final version into your file, putting it between the triple quotes for query2.

   SELECT name, dept_name
   FROM Student LEFT OUTER JOIN MajorsIn 
     ON id = student_id;
   

3. Let’s say that we want to rewrite the previous query so that its results are limited to students whose names begin with either C or S. Should the new condition(s) go in the WHERE clause or the ON clause? Why?

   The new conditions should go in the WHERE clause, because they are not a join condition that is being used by the LEFT OUTER JOIN. If we put the new conditions in the ON clause, we would also get tuples for students whose names do not begin with C or S, because the LEFT OUTER JOIN includes results for tuples from the left table (Student) that do not have a match based on the condition in the ON clause.

   Go ahead and revise the query, and put the final version of the query between the triple quotes provided for query3.

   SELECT name, dept_name
   FROM Student LEFT OUTER JOIN MajorsIn 
     ON id = student_id
   WHERE name LIKE 'C%' OR name LIKE 'S%';
   

4. Write a query that determines the number of students majoring in each department. The result should be tuples of the form (department name, number of students majoring), and the departments should be listed in decreasing order based on how many students the department has. This query does not need to include departments with no majoring students.

   Put your final query between the triple quotes provided for query4.

   SELECT name, COUNT(*)
   FROM Department, MajorsIn
   WHERE name = dept_name
   GROUP BY name
   ORDER BY COUNT(*) DESC;
   

5. Now rewrite the previous query so that it includes departments with no majoring students.

   Hints:

   • You will need a LEFT OUTER JOIN.

   • You may need to revise the aggregate function in the SELECT clause. Make sure that you get a count of 0 for any department that did not appear in your results for the previous query.

   Put your final query between the triple quotes provided for query5.

   SELECT name, COUNT(student_id)
   FROM Department LEFT OUTER JOIN MajorsIn
     ON name = dept_name
   GROUP BY name
   ORDER BY COUNT(student_id) DESC;
   

   Note: We must use COUNT(student_id) or COUNT(dept_name), so that the department with no match in MajorsIn (basket weaving) will end up with a count of 0, rather than a count of 1.

6. Rewrite the previous query so that it only includes departments with fewer than two students majoring in them.

   Put your final query between the triple quotes provided for query6.

   SELECT name, COUNT(student_id)
   FROM Department LEFT OUTER JOIN MajorsIn
     ON name = dept_name
   GROUP BY name
   HAVING COUNT(student_id) < 2
   ORDER BY COUNT(student_id) DESC;
   

7. Write a query to find the name and capacity of the room with the smallest capacity. Hint: You will need a subquery.

   Put your final query between the triple quotes provided for query7.

   SELECT name, capacity
   FROM Room
   WHERE capacity = (SELECT MIN(capacity)
                     FROM Room);
   

8. Write a query that finds the name and the capacity of the room that CS 460 is taught in. Hint: You will need to prepend a table name or a table alias before the names of at least some of the columns in your query.

   Put your final query between the triple quotes provided for query8.

   SELECT R.name, capacity
   FROM Room R, Course C
   WHERE R.id = C.room_id
     AND C.name = 'CS 460';
   

9. Write a query that finds the names of all departments whose names consist of more than one word.

   Put your final query between the triple quotes provided for query9.

   SELECT name
   FROM Department
   WHERE name LIKE '% %';
   

10. Write a query that finds the names and capacities of all rooms that have a capacity between 50 and 250 inclusive or that have a name that does not begin with the letter ‘C’.

    Put your final query between the triple quotes provided for query10.

    SELECT name, capacity
    FROM Room
    WHERE (capacity >= 50 AND capacity <= 250) 
       OR name NOT LIKE 'C%';
    

11. Write a query that counts the number of students majoring in computer science. Note: A GROUP BY clause is not needed here. Why not?

    A GROUP BY clause is not needed, because we are only computing a single count, rather than separate counts for different subgroups.

    Put your final query between the triple quotes provided for query11.

    SELECT COUNT(*)
    FROM MajorsIn
    WHERE dept_name = 'computer science';
    

12. Write a query that finds the names of all students taking a course for undergraduate credit, along with the names and start times of those courses, and the names of the rooms in which the courses are offered. The result of the query should be tuples of the form (student name, course name, start_time, room_name).

    Put your final query between the triple quotes provided for query12.

    SELECT S.name, C.name, C.start_time, R.name
    FROM Student S, Course C, Enrolled E, Room R
    WHERE S.id = E.student_id
      AND C.name = E.course_name
      AND C.room_id = R.id
      AND E.credit_status = 'ugrad';
    

Task 3: Test your understanding of SQL

The TA will guide you through a quick problem-solving exercise that you will complete on paper. It is designed to help you see how well you’ve mastered the key concepts involved in writing SQL queries.

Note that the exams will also be paper-only, so it’s important that you are able to solve problems without relying on DB Browser, on online resources, or on too much help from others.

Last updated on September 26, 2024.