SQLize Online / PHPize Online  /  SQLtest Online

/****************** Registration Database INFO 1620 Written by Lisa Thoendel Last Updated Summer 2022 ******************/ /**** The below code will create a database for us to work with. Copy and paste this code to the DDL pane of SQL Fiddle. --<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>-- Creation Code. DO NOT EDIT! ****/ CREATE TABLE Student (StudentID integer NOT NULL, StudentName VARCHAR(25), CONSTRAINT Student_PK PRIMARY KEY (StudentID)); CREATE TABLE Course (CourseID CHAR(8) NOT NULL, CourseName VARCHAR(35), CONSTRAINT Course_PK PRIMARY KEY (CourseID)); CREATE TABLE Section (SectionNo integer NOT NULL, Semester CHAR(7) NOT NULL, CourseID CHAR(8), CONSTRAINT Section_PK PRIMARY KEY(SectionNo), CONSTRAINT Section_FK FOREIGN KEY (CourseID) REFERENCES Course (CourseID)); CREATE TABLE Registration (StudentID integer NOT NULL, SectionNo integer NOT NULL, CONSTRAINT IsRegistered_PK PRIMARY KEY (StudentID, SectionNo), CONSTRAINT StudentIsRegistered_FK FOREIGN KEY(StudentID) REFERENCES Student(StudentID), CONSTRAINT CourseIsRegistered_FK FOREIGN KEY (SectionNo) REFERENCES Section(SectionNo)); /*** Insert statements to give us data to work with. DO NOT EDIT! ***/ insert into Student (StudentID, StudentName) values (3, "Beth"), (4, "Elliot"), (5, "James"), (6, "Clare"), (7, "Sophie"); insert into Course (CourseID, CourseName) values ('INFO1620', 'Introduction to Database'), ('INFO1003', 'Problem Solving'), ('INFO1002', 'Introduction to IT'), ('INFO2630', 'SQL'), ('MATH1410', 'Statistics'); insert into Section (SectionNo, Semester, CourseID) values (1,'FA22','INFO1620'), (2,'FA22','INFO1620'), (3,'FA22','INFO1003'), (4,'FA22','INFO1002'), (5,'FA22','INFO1002'), (6,'FA22','MATH1410'); insert into Registration (StudentID, SectionNo) values (3,1), (3,3), (3,5), (4,1), (4,3), (5,4), (5,3), (6,6); /* Test Code: Execute in query pane */ select * from Student; select * from Course; select * from Section; select * from Registration; /****************** Module 6: Advanced SQL INFO 1620 Written by Lisa Thoendel Last Updated Summer 2022 Savannah Paulson --<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>-- Use the Registration create script to answer these questions. We will use the below area to write our test SQL. ******************/ /* 1) Join student and course together with a natural join and select all rows. Note that this produces every possible combination of students and courses. There is not a PK/FK relationship between student and course - so this is the only possible join between the 2 tables. */ select * from Student, Course ; /* 2) Now let's look at an inner join between students and registration. This will make use of the PK/FK relationship between these tables. */ select s.StudentID, s.StudentName, r.StudentID, r.SectionNo from Student s Join Registration r on s.StudentID = r.StudentID ; /* 3) Next, let's add sections to the join - giving us a 3 way inner join. This means we'll see students that are registered for a section and sections that have students registered. */ select * from Student s Join Registration r on s.StudentID = r.StudentID join Section x on x.SectionNo = r.SectionNo ; /* 4) Suppose we want to see students that aren't registered yet though also. We'll need an outer join to accomplish that. */ select * from Student s left outer join Registration r on s.StudentID = r.StudentID ; /* 5) By using the NOT IN operator, we can target a list of students that are not registered only. */ select * from Student where StudentID NOT IN (select StudentID from Registration) ; /* 6) Or we can use IN to create a kind of join by subquery. Select the student names and IDs that are in INFO 1003 */ select StudentName from Student where StudentID IN ( select StudentID from Registration where SectionNo IN ( select SectionNo from Section where CourseID = 'INFO1003' ) ) ; /* 7) Another way to get this same result is by using NOT EXISTS. */ select * from Student where NOT EXISTS ( Select * from Registration where Registration.StudentID = Student.StudentID ) ; /* 8) Build a master schedule showing every student's names with the class names, numbers and sections they are registered for. Include the semester. This brings our whole schema together! */ select StudentName, CourseName, CourseID, SectionNo, Semester from Student s join Registration r on r.StudentID = s.StudentID join Section x on x.SectionNo = r.SectionNo join Course c on c.CourseID = x.CourseID ; /* 9) Over time, you may find that you use a query very frequently or that you have a subset of users that need access to a particular query, but that shouldn't see the full tables behind that query. These are excellent use cases for a view! Build a view based on #8 called schedule. */ /* 10) Query the schedule view for the courses Elliot *or* James are registered for. */