CS105
LAB 3- Queries

Connect to your sqlite3 database

1. Click the start button and then run cmd

2. Change directory to your database folder i.e. cd z:\db

3. Connect to your database i.e. sqlite3 mydata.db

Review of Aggregate Functions and Subqueries

Now try the following query:

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

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

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');

Write a query to find the student with the smallest id number?

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;

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

Practice

1. Copy there the following files: course.in , department.in, enrolled.in, majorsin.in, student.in, room.in in z:\db

Issue the following commands:
> drop table student;
> drop table course;
> drop table room;
> drop table department;
> drop table enrolled;
> drop table majorsin;

Here is a script, save it to the z:\db directory and run it using
> .read "create.txt"
> .separator "\t"
> .import "student.in" student
> .import "course.in" course
> .import "room.in" room
> .import "department.in" department
> .import "enrolled.in" enrolled
> .import "majorsin.in" majorsin

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 a query to find all the rooms whose names start with the word 'cas' (case insensitive).

4. Write the name and the capacity of the room that course cs105 is taught.

5. Write a query that lists all departments whose names consists of more than one words.

6. 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'.

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

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

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

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

CS105