This is a collection of 30 'worked examples' where I show the viewer how I go about writing SQL queries. SQL queries are rarely written left-to-right, top-to-bottom. Instead, a database developer typically builds their queries up from small and simple to more complex. These playbacks show how my queries grow as I get closer and closer to a correct solution. They also emphasize how to use an ER Diagram and schema when writing queries.
The database I am using describes courses taken by students in a university. It is a SQLite database called studentGrades.sqlite. Here are the entities and relationships:
Entites:
- Students
- Sections
- Courses
- Professors
- Departments
Relationships:
- Any one student takes zero or more sections (every student receives a grade).
- Any one section has zero or more students taking it.
- Any one section is an instance of one course.
- Any one course has zero or more instances of sections.
- Any one sections is taught by a single professor.
- Any one professor teaches zero or more sections.
- Any one professor belongs to zero or more departments.
- Any one department has zero or more professors.
- Any one department has zero or one professor who is the chairperson of the department.
- Any one professor chairs zero or one department.
- Any one course is offered by one department.
- Any one department offers zero or more courses.
The playbacks have several pictures of the ER Diagram and Schema for this database.
If you are brand new to SQL you might want to start with another one of my 'books' of code playbacks:
"Database Design and SQL for Beginners" on this site.
If you would like to learn how to write programs that use a database you can read:
"Programming with SQLite" on this site.
If you'd like to stay connected and get updates when I add new playbacks you can follow me on twitter:@markm208.
| 1.1 Q1: List every course name, section number, and professor name in chronological order for every section that has ever been offered. |
| 1.2 Q2: List every course name and section number for every course offered by the computer science department. |
| 1.3 Q3: Find the name of every professor who has ever taught CSCI111. |
| 1.4 Q4: List all of the professor names and their departments. |
| 1.5 Q5: List the names of the professors who have taught both CSCI111 and CSCI112. |
| 2.1 Q6: List the names of all of the students of professor Mark Mahoney who are greater than or equal to 21 years old. |
| 2.2 Q7: List the names of all of the students who are taught by a department chair. |
| 2.3 Q8: List all of the course names and section numbers of every course ever taught by a department chair. |
| 2.4 Q9: List all of the courses with the oldest student. |
| 2.5 Q10: List all of the courses and section numbers with the youngest average student age. |
| 3.1 Q11: List all of the course names and section numbers of courses with less than four credits. |
| 3.2 Q12: List all of the course names and section numbers with the smallest enrollment. |
| 3.3 Q13: List all of the student names who have taken more than one course with Mark Mahoney. |
| 3.4 Q14: List all of the student names who have taken a course with both Mark Mahoney and Eric Whendon. |
| 3.5 Q15: List all the course names and section numbers that had two or more students earn A's. |
| 4.1 Q16: Find the names of all the students who have taken CSCI111. |
| 4.2 Q17: Find the names of all professors in the computer science department who are not chairs of a department. |
| 4.3 Q18: Find the names of all professors who are the chair of a department. |
| 4.4 Q19: Find the ssn, first and last name, course name, and grade earned for all courses taken in spring 2007. |
| 4.5 Q20: Find the course name and section number of all the courses that have ever been offered in the fall. |
| 5.1 Q21: Find the names of all of the professors teaching in spring 2007. |
| 5.2 Q22: Find the names of all of the students who have received an A and a B in any course. |
| 5.3 Q23: Find out how many students have ever taken CSCI111. |
| 5.4 Q24: Find the average age of all students who ever had a course with Mark Mahoney. |
| 5.5 Q25: Find the names of all of the professors who have never taught a course. |
| 6.1 Q26: Find the names of all of the professors of who have taught May Jones. |
| 6.2 Q27: Find the names of the students who have had a course in Fall 2006 or Spring 2007. |
| 6.3 Q28: Find the names of the students who have taken a course from a professor who has more than one appointment to a department. |
| 6.4 Q29: Find the average age of students who took courses in Spring 2007. |
| 6.5 Q30: Find the sum of all of the credit hours offered by the computer science department in 2007 |