SQLize Online / PHPize Online  /  SQLtest Online

/****************** Module 6: Advanced SQL INFO 1620 Written by Lisa Thoendel Last Updated Summer 2022 ******************/ /**** The below code will create a database for us to work with. We will add our code where noted below. --<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>-- 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"); select * from Student; insert into Course (CourseID, CourseName) values ('INFO1620', 'Introduction to Database'), ('INFO1003', 'Problem Solving'), ('INFO1002', 'Introduction to IT'), ('INFO2630', 'SQL'), ('MATH1410', 'Statistics'); select * from Course; 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'); select * from Section; insert into Registration (StudentID, SectionNo) values (3,1), (3,3), (3,5), (4,1), (4,3), (5,4), (5,3), (6,6); select * from Registration; /****************** --<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>-- We will use the below area to write our test SQL. Answer the questions in the comments. ******************/ /* 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; /* 3) 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 * from Student s inner join Registration r on s.StudentID = r.StudentID; /* 4) 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 inner join Registration r on s.StudentID = r.StudentID join Section x on x.SectionNo = r.SectionNo; /* 5) 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 left outer join Section x on x.SectionNo = r.SectionNo; /* 6) By using the NOT IN operator, we can target a list of students that are not registered only. */ /* 7) Another way to get this same result is by using NOT EXISTS. */ /* 8) Build a master schedule showing every student's names with the class names, numbers and sections they are registered for. This brings our whole schema together! */ /* 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 Fall 2022 session. */