1. Open SQLite Database Browser
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. Open university.db in SQLite Database Browser.
2. Database Schema
For your reference, the database has the following schema:
3. Review of Aggregate Functions and Subqueries
Here we have a query:
How could we change the above query to only show credit status of students enrolled to courses with at least two enrollments? That is, do the above query for the students that are enrolled in popular courses.
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.
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
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'