Lab 2 - Introduction to SQL

Objectives
  1. Get to know SQLite environment.
  2. Take a look at a dataset
  3. Selection Queries

1. Get to know the SQLite environment

For the purposes of our lab we will use the sqlite3 . In the lab we use windows. For Mac OS and Linux, you can dowload binaries here.

  1. Download here
  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. Do Start->Run
  8. Hit "cmd" - enter
  9. Write "z:" in order to change the active drive into z: (the prompt should show something beginning with z:\).
  10. Write "cd z:\db".
  11. Comment: cd stands for "change directory" and z:\db for the new directory location.
  12. Write "sqlite3 university.db"
  13. Comment: sqlite3 calls the db-program and university.db is the database that we will work with.
  14. You should now see the SQL prompt :
    sqlite>
  15. This is a program in the Command-Window. You are now ready to write sql commands!

But before that:

  1. .h : Help (saves time, very useful)
  2. .mode column : aligns query results
  3. .headers ON : puts attribute names as headers in query result table
  4. .nullvalue NULL : prints NULL wherever it finds a null value
  5. .width 20 20 20 20 20 20 : enforces a width of 20 in the first 6 columns of the results
  6. .exit : exits

These were non-SQL commands. Remember that SQL command ends with a semi-colon ";"

Detailed Online Documentation can be found at: http://www.sqlite.org/sqlite.html

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. Selection Queries

1. Retrieving 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 below query 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 major in comp sci. Now we should use WHERE clause in order to specify the property of rows, which we want to retrieve.

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

Exercise 2: Write a query listing the ID of the room that 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 who 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
CS105 Labs