QuestionNovember 18, 2025

School Database Please create a database for a school with at least three tables.Create appropriate 1 to many relationships between tables.Suggested tables: instructors courses, classrooms. Create at least three queries and name each query appropriately. Example queries: summary query showing the total number of classes each instructor teaches; select query showing only business courses on Tuesdays and Thursdays, and where they are taught; select query showing courses starting after a certain date (8-week course v. semester-long course).

School Database Please create a database for a school with at least three tables.Create appropriate 1 to many relationships between tables.Suggested tables: instructors courses, classrooms. Create at least three queries and name each query appropriately. Example queries: summary query showing the total number of classes each instructor teaches; select query showing only business courses on Tuesdays and Thursdays, and where they are taught; select query showing courses starting after a certain date (8-week course v. semester-long course).
School Database Please create a database for a school with at least three tables.Create appropriate 1 to many relationships between tables.Suggested tables: instructors courses, classrooms. Create at least three queries and name each query appropriately. Example queries: summary query showing the total number of classes each instructor teaches; select query showing only business courses on Tuesdays and Thursdays, and where they are taught; select query showing courses starting after a certain date (8-week course v. semester-long course).

Solution
4.6(228 votes)

Answer

The solution includes a database schema with 3 one-to-many relationships, sample data, and 3 queries: 1. Total classes per instructor. 2. Business courses on Tue/Thu with room info. 3. Courses starting after a certain date. Explanation 1. Define tables and relationships Create 3 tables: `Instructors`, `Courses`, `Classrooms`. One instructor teaches many courses; one classroom hosts many courses. ```sql CREATE TABLE Instructors ( InstructorID INT PRIMARY KEY, Name VARCHAR(100), Department VARCHAR(50) ); CREATE TABLE Classrooms ( ClassroomID INT PRIMARY KEY, RoomNumber VARCHAR(10), Capacity INT ); CREATE TABLE Courses ( CourseID INT PRIMARY KEY, Title VARCHAR(100), Category VARCHAR(50), Days VARCHAR(20), StartDate DATE, DurationWeeks INT, InstructorID INT, ClassroomID INT, FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID), FOREIGN KEY (ClassroomID) REFERENCES Classrooms(ClassroomID) ); ``` 2. Insert sample data Minimal sample dataset for testing queries. ```sql INSERT INTO Instructors VALUES (1, 'John Smith', 'Business'), (2, 'Anna Lee', 'Math'); INSERT INTO Classrooms VALUES (1, 'A101', 30), (2, 'B205', 25); INSERT INTO Courses VALUES (1, 'Intro to Business', 'Business', 'Tuesday,Thursday', '2024-09-01', 16, 1, 1), (2, 'Advanced Marketing', 'Business', 'Tuesday,Thursday', '2024-09-10', 8, 1, 2), (3, 'Calculus I', 'Math', 'Monday,Wednesday', '2024-08-25', 16, 2, 1); ``` 3. Query1 - Total classes per instructor Count courses grouped by instructor. ```sql SELECT i.Name, COUNT(c.CourseID) AS TotalClasses FROM Instructors i JOIN Courses c ON i.InstructorID = c.InstructorID GROUP BY i.Name; ``` 4. Query2 - Business courses on Tue/Thu with location Filter by category and days, join classrooms. ```sql SELECT c.Title, cls.RoomNumber FROM Courses c JOIN Classrooms cls ON c.ClassroomID = cls.ClassroomID WHERE c.Category = 'Business' AND c.Days = 'Tuesday,Thursday'; ``` 5. Query3 - Courses after certain date Filter by start date. ```sql SELECT Title, StartDate, DurationWeeks FROM Courses WHERE StartDate > '2024-09-01'; ```

Explanation

1. Define tables and relationships <br /> Create 3 tables: `Instructors`, `Courses`, `Classrooms`. One instructor teaches many courses; one classroom hosts many courses. <br /><br />```sql<br />CREATE TABLE Instructors (<br /> InstructorID INT PRIMARY KEY,<br /> Name VARCHAR(100),<br /> Department VARCHAR(50)<br />);<br /><br />CREATE TABLE Classrooms (<br /> ClassroomID INT PRIMARY KEY,<br /> RoomNumber VARCHAR(10),<br /> Capacity INT<br />);<br /><br />CREATE TABLE Courses (<br /> CourseID INT PRIMARY KEY,<br /> Title VARCHAR(100),<br /> Category VARCHAR(50),<br /> Days VARCHAR(20),<br /> StartDate DATE,<br /> DurationWeeks INT,<br /> InstructorID INT,<br /> ClassroomID INT,<br /> FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID),<br /> FOREIGN KEY (ClassroomID) REFERENCES Classrooms(ClassroomID)<br />);<br />```<br /><br />2. Insert sample data <br /> Minimal sample dataset for testing queries. <br /><br />```sql<br />INSERT INTO Instructors VALUES<br />(1, 'John Smith', 'Business'),<br />(2, 'Anna Lee', 'Math');<br /><br />INSERT INTO Classrooms VALUES<br />(1, 'A101', 30),<br />(2, 'B205', 25);<br /><br />INSERT INTO Courses VALUES<br />(1, 'Intro to Business', 'Business', 'Tuesday,Thursday', '2024-09-01', 16, 1, 1),<br />(2, 'Advanced Marketing', 'Business', 'Tuesday,Thursday', '2024-09-10', 8, 1, 2),<br />(3, 'Calculus I', 'Math', 'Monday,Wednesday', '2024-08-25', 16, 2, 1);<br />```<br /><br />3. Query1 - Total classes per instructor <br /> Count courses grouped by instructor. <br /><br />```sql<br />SELECT i.Name, COUNT(c.CourseID) AS TotalClasses<br />FROM Instructors i<br />JOIN Courses c ON i.InstructorID = c.InstructorID<br />GROUP BY i.Name;<br />```<br /><br />4. Query2 - Business courses on Tue/Thu with location <br /> Filter by category and days, join classrooms. <br /><br />```sql<br />SELECT c.Title, cls.RoomNumber<br />FROM Courses c<br />JOIN Classrooms cls ON c.ClassroomID = cls.ClassroomID<br />WHERE c.Category = 'Business'<br /> AND c.Days = 'Tuesday,Thursday';<br />```<br /><br />5. Query3 - Courses after certain date <br /> Filter by start date. <br /><br />```sql<br />SELECT Title, StartDate, DurationWeeks<br />FROM Courses<br />WHERE StartDate > '2024-09-01';<br />```
Click to rate:

Similar Questions