Lab 2 - Introduction to SQL


Objectives
  1. Get to know SQLite environment.
  2. Take a look at a dataset
  3. Practice queries


1. Get to know the SQLite environment

For the purposes of our lab we will use the sqlitebrowser . If you are using Windows, use sqlitebrowser-1.3-win.zip. For Mac OS, use sqlitebrowser_200b1_osx.zip . For Linux, use sqlitebrowser-1.3-i386.tar.gz

  1. Download sqlitebrowser
  2. Open Z:drive
  3. Create a directory there named "db"
  4. Unzip the downloaded program in that directory (Z:/db)
  5. Download the lab's sample database from here and copy it to the same folder.
  6. Comment: We do this once: Z drive is accessible from all computers.
  7. Open sqlitebrowser, click File->Open Database
  8. Choose the database you just downloaded
  9. You can see the database structure in the first tab or browse data in the second tab. Use the third tab to type in SQL commands.
  10. Explore!

2. A List of the Tables in our Database

Student(id, name)
Department(name, office)
Room(id, name, capacity)
Course(name, start, end, room)
MajorsIn(student, dept)
Enrolled(student, course, credit_status)


3. Practice queries

1. Retrieving a whole table

A query result is always in the form of the table. Try the following query:

SELECT *
FROM Enrolled;

2. Retrieve specific columns

If you only need some of the attributes, you should specify them after SELECT. For example the query below retrieves credit_status column of Enrolled table.

SELECT credit_status
FROM Enrolled;

Exercise 1: List the names of all classrooms and their capacities.


3. Retrieve specific rows.

Write a query for retrieving all students that major in comp sci. Now we should use the WHERE clause in order to specify the properties of the rows we want to retrieve.

SELECT *
FROM MajorsIn
WHERE dept = 'comp sci';

Exercise 2: Write a query listing the ID of the room in which course CS 105 is taught.


4. Logical Operators.

Try the following query:

SELECT *
FROM MajorsIn
WHERE dept = 'comp sci' OR dept = 'mathematics';

What does this query do?

Try the following query:

SELECT student
FROM MajorsIn
WHERE dept = 'mathematics' AND dept = 'english'

What does this query do?

Exercise 3: Write a query listing the IDs of the rooms that are not in CAS building.


5. Aggregate queries

Now try the following query:

SELECT credit_status,COUNT(*)
FROM Enrolled
GROUP BY credit_status;

What does this query do?

Exercise 4: Write a query that calculates the average, the sum, the min and the max capacity of the rooms.


6. Subqueries

The following query lists the names of the students that do not major in comp sci:

SELECT name
FROM Student
WHERE id NOT IN (SELECT student
                 FROM MajorsIn
                 WHERE dept = 'comp sci');

Would this query give us the same results?

SELECT name
FROM Student
WHERE id IN (SELECT student
                 FROM MajorsIn
                 WHERE dept != 'comp sci');


7. More complex queries

Exercise 5: Write a query listing all the rooms in CAS that their capacity is 50 seats below the average of all the rooms or more ( capacity >= avg - 50 ).

Exercise 6: Write a query that returns the list of the departments and the number of students that major in each department.


CS105