8.8 Sample SQL Statements for working with the database

1. Insert one family with one guardian and two children

2. Insert one instructor and two courses taught by the instructor

3. Enroll students to classes

4. Display all students who enrolled in a specific course

5. Assign Grades to students who enrolled in a specific course

6.Display total number of students enrolled in a specific course

Tables System Tables External Tables CUSTOMER.Enrollments Columns Enrollid (PK, int, not null) E Grade (nchar(1), null) Studentid (FK, int, not null) o CourseNum (FK, int, not null) DKeys Constraints Triggers Statistics Columns Indexes CUSTOMERFAMILIES Familyid (PK, int, not null) E LastNameCH (nvarchar(20), n E Phone (nvarchar(15), not null ull) 01 CellPhone (nvarchar(15), null) Email (nvarchar(50), not null HomeAddress (nvarchar(50), City (nvarchar(20), null) l) State (nvarchar(20), null) Zip (nvarchar(10), null) DKeys Constraints Triggers Indexes Statistics Tables System Tables External Tables CUSTOMER.Enrollments CUSTOMERFAMILIES EE CUSTOMER.Guardians Columns Guardianid (PK, int, not null) Name (nvarchar(20), not null) E Address (nvarchar(50), null) Phone (nvarchar(15), not null) E Cell (nvarchar(15), null) Email (nvarchar(50), not null) o Familyid (FK, int, not null) Keys Constraints Triggers Indexes Statistics CUSTOMER Students Columns Studentid (PK, int, not null) FirstName (nvarchar(20), not null) LastName (nvarchar(20), not null) Gender (nvarchar(10), null) Relation (nvarchar(10), not null) E Phone (nvarchar(15), null) Email (nvarchar(50), null) BirthDate (date, not null) -Familyid (FK, int, not null) DKeys Constraints Triggers Indexes Statistics E SCHOOLCourses Columns o CourseNum (PK, int, not null) Title (nvarchar(30), not null) B MeetDay (nvarchar(30), null MeetTime (nvarchar(35), null) StartDate (date, null) E EndDate (date, null) B SeatCap (int, null) e-Instructorid (FK, int, null) Keys Constraints D Triggers DIndexes Statistics SCHOOLInstructors Columns Instructorid (PK, int, not null) Name (nvarchar(20), not null) E Address (nvarchar(50), null) Phone (nvarchar(15), not null) E Email (nvarchar(50), not null) Contact (nvarchar(20), not null) E HireDate (date, not null) Type (nvarchar(10), null) DKeys Constraints Triggers Indexes