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!
If you haven’t already created a folder named cs460
for your
work in this course, follow these
instructions to do so.
Then create a subfolder called lab2
within your cs460
folder,
and put all of the files for this lab in that folder.
If you haven’t already done so, download and install DB Browser for SQLite following the instructions in Problem Set 1.
To download the database we will be using in this lab, click on the following link:
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.
Launch DB Browser for SQLite.
Click the Open Database button, and find and open the
university.sqlite
database file that you downloaded above.
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.
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:
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.)
Why is DISTINCT needed in the previous query?
In this task, you will practice writing SQL queries for the database that you opened in Task 1. Here is its schema:
Schema of the database
Student(id, name)
Department(name, office)
Room(id, name, capacity)
Course(name, start_time, end_time, room_id)
MajorsIn(student_id, dept_name)
Enrolled(student_id, course_name, credit_status)
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.
Important
In your work for this course, you may only use aspects of SQL that we have discussed in lecture. See our course policies for more detail.
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';
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';
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;
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;
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?
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%';
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');
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.
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.
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;
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);
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.
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';
On paper, write a relational-algebra query for each of the following problems from Task 2:
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))
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.
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.
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.
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:
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.
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:
At the bottom of the DB Browser window, you should see a message indicating that the command executed successfully.
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.
INSERT INTO WorksFor VALUES ('U00000001', 'computer science', 'assistant'); INSERT INTO WorksFor VALUES ('U00000010', 'mathematics', 'grader');
Try entering your INSERT
command in the Execute SQL tab.
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.