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.
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.
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).
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.
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.
Write a query that finds the names of all students who are not enrolled in CS 460. Hint: You will need a subquery.
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.
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.
Rewrite the previous query so that it only includes departments with fewer than two students majoring in them.
Write a query to find the name and capacity of the room with the smallest capacity. Hint: You will need a subquery.
Write a query that counts the number of students majoring in
computer science. Note: A GROUP BY clause is not needed here.
Why not?
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).
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)
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.
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.
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.
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 18, 2025.