Lab 3 - Queries

1. Open Database with Sqlite3

1. Click the start button and then run cmd

2. Go to z:\ drive, then your database folder z:\db.

3. Download this database file to z:\db, overwriting the one you previously had

4. Type in sqlite3 university.db to open the database with sqlite3.


2. Database Schema

For your reference, the database has the following schema:

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

 logo

3. Review of Aggregate Functions and Subqueries

Here we have a query:

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

How could we change the above query to only show credit status with at least two enrollments?

SELECT credit_status,COUNT(*)
FROM Enrolled
WHERE course = (SELECT course
                FROM Enrolled
                GROUP BY course
                Having COUNT(*)>1)
GROUP BY credit_status;


4. Joins

What is the meaning of the result of the following query? The second query includes two join conditions. In general, if you have N tables in the FROM clause, you need N - 1 join conditions.

SELECT *
FROM Department, Student, MajorsIn;

SELECT *
FROM Department, Student, MajorsIn
WHERE Department.name = MajorsIn.dept and Student.id = MajorsIn.student;


5. Writing Queries: Rule of Thumb

1. Start with the FROM clause

2. If you need more than one table, determine the necessary join conditions

3. Determine if a GROUP BY clause is needed

4. Determine any other conditions that are needed

5. Fill in the rest of the query: SELECT, ORDER BY



6. Practice

1. Write a query to select the room with the maximum capacity.

2. Write a query that counts the students for every major (in the MajorsIn relation) and prints the count and the department name.

3. Write the name and the capacity of the room that course CS 105 is taught.

4. Write a query that lists all departments whose names consist of more than one word.

5. Write a query that selects all the names of the rooms with 50<capacity<250 or with a name that does not contain the letter 'A'.

6. Write a query that retrieves all departments that are assigned to some office.

7. Write a query that counts the students majoring in computer science.

8. Write a query that outputs the names, the courses, the room, the start_times and end_times of all undergraduate students(join 4 tables!)

9. Create a list of all students who are not enrolled in 'CS 105'

CS105
CS105 Labs