Lab 2: SQL

Task 0: Preliminaries

Policy reminders

• Feel free to ask questions at any point during the lab.

• We may not finish all of the lab exercises during the actual lab session. Every week, we will post solutions to the current week’s lab on the Labs page of the course website.

• We don’t grade the lab exercises. However, starting with today’s lab, your attendance at lab will count towards your participation grade. If you attend 85% of the lab sessions over the course of the semester, you will get full credit for lab participation. See the syllabus for more details.

• To get full credit for participation in lab, you must work productively throughout the lab session. You will not be penalized if you cannot finish all of the lab exercises.

• Feel free to work on the lab exercises with your classmates, and don’t hesitate to ask a staff member for help!

Create the necessary folder

1. If you haven’t already created a folder named cs460 for your work in this course, follow these instructions to do so.

2. Then create a subfolder called lab2 within your cs460 folder, and put all of the files for this lab in that folder.

Download the software and database

1. If you haven’t already done so, download and install DB Browser for SQLite following the instructions in Problem Set 1.

2. To download the database we will be using in this lab, click on the following link:

   university.sqlite

   and save the file in your lab2 folder.

Using the virtual desktop
If you encounter issues installing the DB Browser software, you can use the copy of it that is available on the virtual desktop. Instructions for doing so can be found here.

Task 1: Explore DB Browser for SQLite

1. Launch DB Browser for SQLite.

2. Click the Open Database button, and find and open the university.sqlite database file that you downloaded above.

3. Click on the Browse Data tab and explore the contents of the tables. Initially, you will see the Course table, but you should explore the other tables as well by choosing the appropriate table name from the drop-down menu.

4. Select the Execute SQL tab, which is where you will perform queries on the database.

   For example, try entering the following SQL command in the space provided:

   SELECT DISTINCT name
   FROM Student, Enrolled
   WHERE id = student_id
     AND course_name LIKE 'CS%';
   

   To run it, you can do 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.

   The resulting tuples represent the names of all students in the database who are enrolled in a CS course. (Note: We assume that student names are unique.)

5. Why is DISTINCT needed in the previous query?

   Because a student may be enrolled in more than one CS course, and SQL does not eliminate duplicates unless we explicitly ask it to do so.

Task 2: Write SQL queries

In this task, you will practice writing SQL queries for the database that you opened in Task 1. 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 names of all students majoring in 'computer science'. Hint: Don’t forget to include an appropriate join condition.

   SELECT name
   FROM Student, MajorsIn
   WHERE id = student_id
     AND dept_name = 'computer science';
   

2. Write a query that finds the name and 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.

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

3. 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).

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

4. 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.

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

5. 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.

   Once you have answered the questions regarding the new conditions, go ahead and revise the query.

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

6. Write a query that finds the names of all students who are not enrolled in CS 460. Hint: You will need a subquery.

   SELECT name
   FROM Student
   WHERE id NOT IN (SELECT student_id
                    FROM Enrolled
                    WHERE course_name = 'CS 460');
   

7. 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.

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

   Note: Because the name of the department appears in the MajorsIn table and we only care about departments that actually have students majoring in them, we could have solved this problem with a single-table query:

   SELECT name, COUNT(*)
   FROM MajorsIn
   GROUP BY name
   ORDER BY COUNT(*) DESC;
   

   However, we will need both tables in the next two problems, so we included them both in our original answer above.

8. 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.

   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.

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

   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;
   

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

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

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?

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

    A GROUP BY clause isn’t needed because we are computing a single count, rather than separate counts for different subgroups.

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).

    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';
    

Extra practice 1: More practice with relational-algebra queries

On paper, write a relational-algebra query for each of the following problems from Task 2:

1. problem 1 (names of students majoring in computer science)

   The most straightforward approach is to recall how a SELECT command is logically carried out:

   • When there are multiple tables in the FROM clause, we begin with the full Cartesian product of those tables.

   • Then we perform a selection (σ) based on the WHERE clause.

   • Finally, the we perform a projection (π) based on the SELECT clause.

   That gives you the following relational-algebra query:
   π _name (σ _{id = student_id AND dept_name = ‘computer science’} (Student x MajorsIn))

   One alternate approach would be to take advantage of the fact that a theta-join is equivalent to a Cartesian product followed by a selection. As a result, we can use a theta-join to replace the Cartesian product and selection in the query above:

   π _name (Student ⨝_{id = student_id AND dept_name = ‘computer science’} MajorsIn)

   Other possible alternatives include:

   π _name (σ _{dept_name = ‘computer science’} (Student ⨝_{id = student_id} MajorsIn)

   or

   π _name (Student ⨝_{id = student_id} (σ _{dept_name = ‘computer science’} MajorsIn))

2. problem 6 (names of students not enrolled in CS 460). Hint: Take advantage of the set difference operator (-). Also, in order to make this query easier to write, you may assume that all student names are unique.

   π _name (Student) − π _name (Student ⨝_{id = student_id AND course_name = ‘CS 460’} Enrolled)

   In this query, the left-hand side of the set difference produces the names of all students in the database, while the right-hand side produces the names of all students who are taking CS 460. Thus, taking the set difference of these two sets leaves us with the names of all students who are not taking CS 460.

Extra practice 2: 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.

Last updated on September 19, 2025.