Lab 2: SQL Fundamentals and Querying (10 points)

Overview

In this lab you will practice the fundamentals of SQL by working with a relational database for a fictional university called Lab2 University. The database tracks students, instructors, courses, sections, enrollments, and departments.

You will write Data Definition Language (DDL) statements to create tables, Data Manipulation Language (DML) statements to insert, update, and delete records, and a variety of queries of increasing complexity. By the end of this lab you should feel comfortable writing SQL against a multi-table relational schema.

You may work with one partner on this lab.

Database Schema

The Lab2 University database consists of the following six tables where field -> table represents a foreign key field referencing another table. Study the schema carefully before you begin — understanding the relationships between tables is critical for writing correct queries.

Departments(DeptID, DeptName, Building, Budget)

Instructors(InstructorID, FirstName, LastName, DeptID -> Departments, HireDate, Salary)

Courses(CourseID, CourseName, DeptID -> Departments, Credits)

Sections(SectionID, CourseID -> Course, InstructorID -> Instructors, Semester, Year, Room, Capacity)

Students(StudentID, FirstName, LastName, Email, MajorDeptID -> Departments, GPA, EnrollmentYear)

Enrollments(EnrollmentID, StudentID -> Students, SectionID -> Section, Grade)

Important relationships:

• Each Instructor belongs to one Department
• Each Course is offered by one Department
• Each Section is a specific offering of a Course, taught by one Instructor
• Each Student has a major in one Department
• Each Enrollment links a Student to a Section, with an optional letter grade. Grades should be A, A-, B+, B, B-, C+, C, C-, D, F or NULL.

Part 1 — Create tables (2 points)

Write CREATE TABLE statements for all six tables described in the schema above. Your statements must include:

1. Appropriate domain types for every column (e.g., INTEGER, VARCHAR(n), NUMERIC(10,2), DATE)
2. A primary key constraint on each table
3. Foreign key constraints for every relationship shown in the schema, with ON UPDATE and ON DELETE behavior specified. Create a constraint so that a department cannot be deleted if there are instructors assigned to it (Hint: use the RESTRICT constraint).
4. At least three of the following constraints spread across your tables:
   • NOT NULL
   • UNIQUE
   • CHECK (e.g., GPA BETWEEN 0.0 AND 4.0)
   • DEFAULT.

Note: Make sure your tables are created in the correct order so that foreign key references point to tables that already exist. Alternatively, you may use ALTER TABLE to add foreign keys after all tables are created.

Example (do not copy this verbatim — choose your own data types and constraints):

CREATE TABLE Departments (
    DeptID     INT AUTO_INCREMENT PRIMARY KEY,
    DeptName   VARCHAR(100) NOT NULL UNIQUE,
    Building   VARCHAR(100),
    Budget     DECIMAL(12,2) CHECK (budget >= 0)
    )

You may use MySQL Workbench to create your tables, but you should submit the SQL that MySQL Workbench uses to create these tables.

Part 2 — Manipulate data (2 points)

After creating your tables, populate them with sample data and then practice modifying records. Complete the following tasks:

Insert Data

Insert enough data to make your queries in Parts 3 and 4 meaningful. At minimum you need:

• 4 departments (one of which is Computer Science)
• 8 instructors (spread across departments)
• 10 courses
• 12 sections (across at least 2 semesters/years)
• 15 students
• 20 enrollment records (with a mix of grades including some NULL values representing in-progress courses, some should be a grade of 'F')

You may use an LLM to generate entities for your tables.

Update Records

1. Give every instructor in the "Computer Science" department a 10% salary raise.
2. Change the major of any student with a GPA above 3.8 to "Computer Science" (if they aren't already a CS major).

Delete Records

1. Delete all enrollment records where the grade is 'F'.
2. Attempt to delete a department that still has instructors assigned to it. In a comment, explain what happens and why, based on your choices from Part 1.

Part 3 — Query for database for insight (4 points)

Write a SQL query for each of the following questions. Above each query in your SQL file, include a comment with the question number and a brief description of your approach.

Basic Queries (1 point)

1. List all students majoring in "Computer Science," ordered by last name, then first name. Display StudentID, first name, last name, email, and GPA
2. Find all courses worth 4 or more credits. Display the course name, department name, and credits
3. List all sections offered in Fall 2025 along with the course name and the instructor's full name.

Joins (1 point)

4. (INNER JOIN) For each enrollment record, display the student's full name, the course name, the semester/year, and the grade received
5. (LEFT JOIN) List all students and any courses they are enrolled in. Students with no enrollments should still appear in the results with NULL values for course information
6. (RIGHT JOIN UNION LEFT JOIN equivalent to FULL OUTER JOIN) List all instructors and the sections they teach. Include instructors who are not teaching any sections this semester and sections that have no instructor assigned (if any exist).

Aggregation (1 point)

7. For each department, show the department name, the number of instructors, and the average instructor salary. Order by average salary descending
8. For each course, show the course name and the total number of students who have ever enrolled. Only include courses with more than 5 enrollments
9. Find the semester and year with the highest total enrollment across all sections
10. For each student, calculate their number of completed courses (grade is not NULL) and their grade point average. Map letter grades to numeric values as follows:

    Grade	A	A-	B+	B	B-	C+	C	C-	D	F
    Points	4.0	3.7	3.3	3.0	2.7	2.3	2.0	1.7	1.0	0.0

Subqueries & Set Operations (1 point)

11. (Non-correlated subquery) Find all instructors whose salary is above the average salary of their department
12. (Correlated subquery) For each department, find the student with the highest GPA (according to the GPA field in the Student table). Handle ties by listing all students who share the top GPA
13. (EXISTS IN or NOT IN) Find all courses that have never had any student enroll in any of their sections
14. (Set operations) Find students who are enrolled in sections taught by the "Computer Science" department but not in any sections taught by the "Mathematics" department. Use EXCEPT or NOT IN.

Part 4 — Views (2 points)

1. Create a view called student_transcript that displays:

   • Student ID, full name
   • Course name, credits
   • Semester, year
   • Grade
   • Instructor full name

   The view should include all enrollment records for all students.

2. Write a query using your view that retrieves the full transcript for a specific student (by student ID), ordered by year and semester.
3. Write a query using your view that calculates each student's total credits earned (counting only courses where a passing grade was received, i.e., grade ≠ 'F' and grade is not NULL).