Due date: TBD
In this lab, you'll get experience using SQL to perform basic join operations on a small dataset. This lab will show you how a relational data model, combined with a powerful query processing language, can greatly simplify finding answers to complex questions.
Instead of installing a full-fledged DBMS on your machine, we'll be using https://www.db-fiddle.com, an interactive and web-based SQL interface.
Setting up DB Fiddle: First, go to https://www.db-fiddle.com. In the top left of the screen, there is a small dropdown that currently has the value "MySQL: 5.7" selected. Change this value to SQLite 3.18 by selecting the "3.18" option from the SQLite section of the dropdown.
Getting the schema: the left hand side of the DB Fiddle site is used to store the schema and data of your database. We've defined a database schema for you, which you should copy/paste into this section. To get the schema, go to: http://rm.cab/sql. Afterwards, your session should look like this:
The schema represents a simple university. Each entity (student, professor, course, enrollment, teaching assignment) is represented by a relation (sometimes called a "table"), and most entities have an ID (for example, student's have a student ID field, sid).
Writing your first query: to get started, let's run a basic SQL query to list all the students in our university. On the right hand side of DB Fiddle, in the section labeled "Query SQL", enter the following:
SELECT * FROM student;... and then press the "Run" button at the top of DB Fiddle. The results pane should appear, and give you a table showing the result of your query -- in this case, listing all of the students in the student relation.
To get only students with a certain last name (say, "Lovelace"), we can add a WHERE clause to our query:
SELECT * FROM student
WHERE
slast = "Lovelace";Writing your first (few) join query: Let's expand our listing of students to also include their majors. In order to do this, we want to join together the student relation with the major relation, matching on the sid attributes (sometimes called "columns") of both relations. To do this, enter the following SQL query:
SELECT * FROM
student, major
WHERE
student.sid = major.sid;This query can be read as follows: take the Cartesian product of the student and major relations, and then return only the elements which have a matching student.sid and major.sid values. You'll notice that a few of our students (for example, Solomon Garber) disappeared, because they do not yet have majors. To remedy this, and to list all students including those who are undecided, we need to do "left outer join." The syntax for this is:
SELECT * FROM
(student LEFT JOIN major ON student.sid = major.sid);You'll notice that now, when you click the "Run" button, the listing of students is complete, and those who are undecided are listed with a "null" major. Suppose we wanted to clean up our results a little bit and only show the last name and major of each student. We can do this projection like so:
SELECT slast, major FROM
(student LEFT JOIN major ON student.sid = major.sid);Writing some more queries: For the rest of this assignment, you should write SQL queries to answer the following questions. Put all your answers into the same DB Fiddle. You can mark which SQL statement matches which question with a SQL comment, which is any line that starts with two dashes (--). There is a small bug in DB Fiddle that causes a SQLITE_MISUSE: not an error message to appear if you end your "Query SQL" section with a comment, so make sure not to do that.
- Find the email addresses of every computer science major.
- Find the first name, last name, and email address of everyone who is undeclared (has no major).
- Find the last name and email address of every student who is enrolled in CS132, sorted by their last name (see link for hint).
- Find the distinct email addresses for all the students who are currently enrolled in a class taught by Olga.
- Find the distinct first and last names of all professors who are teaching any student who is a mathematics major.
Submitting your work: Once you have finalized your work (and only at this point), click the "Save" button in DB Fiddle. The URL in your browser's address bar will be updated to give you a unique link to your work. Test that this URL works by opening a new window and entering the URL. Once you have verified that your URL works, submit it in a text file along with your SQL statements.
Here is a snapshot of the correct query output, which you can use to ensure you have the correct results. Note that we will test your code with the same schema, but with different data, so do not hard-code your answers.
