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

2. Open Z:drive
3. Create a directory there named "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
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 EnrolledGROUP 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 nameFROM StudentWHERE id NOT IN (SELECT student                  FROM MajorsIn                  WHERE dept = 'comp sci');

Would this query give us the same results?

 SELECT nameFROM StudentWHERE 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.