This lecture covers basic queries on the student enrollment system.
Our Student Enrollment System is fully set up and populated. Now, let’s start extracting meaningful information from it. We’ll begin with some basic queries, using `SELECT`, `WHERE`, and simple `JOIN`s.
Query 1: List All Students and Their Majors
A simple query to get an overview of our student body.
SELECT StudentName, Major FROM Students;Query 2: List All Courses and Their Instructors
This requires joining the `Courses` table with the `Instructors` table.
SELECT
c.CourseName,
c.Department,
i.InstructorName
FROM Courses AS c
INNER JOIN Instructors AS i ON c.Instructor_ID = i.InstructorID;Query 3: Find All Courses Taught by Dr. Smith
We can combine a `JOIN` with a `WHERE` clause to filter results.
SELECT
c.CourseName,
c.Department
FROM Courses AS c
INNER JOIN Instructors AS i ON c.Instructor_ID = i.InstructorID
WHERE i.InstructorName = 'Dr. Smith';Query 4: List All Students Enrolled in ‘Introduction to SQL’
This query requires joining `Students`, `Enrollments`, and `Courses`.
SELECT
s.StudentName,
e.Grade
FROM Students AS s
INNER JOIN Enrollments AS e ON s.StudentID = e.Student_ID
INNER JOIN Courses AS c ON e.Course_ID = c.CourseID
WHERE c.CourseName = 'Introduction to SQL';Query 5: Find All Courses a Specific Student is Taking
Let’s find all courses that ‘Alice Brown’ is enrolled in.
SELECT
c.CourseName,
e.Grade
FROM Students AS s
INNER JOIN Enrollments AS e ON s.StudentID = e.Student_ID
INNER JOIN Courses AS c ON e.Course_ID = c.CourseID
WHERE s.StudentName = 'Alice Brown';In the Next Lecture…
We will move on to more advanced queries, incorporating aggregate functions and `GROUP BY` to perform analytics on our student data, such as calculating average grades for courses and students.
Lecture 42: Final Project – Populating the Data
