SQLize
Online
/
PHPize Online
/
SQLtest Online
A
A
A
Share
Donate
Blog
Popular
Donate
A
A
A
Share
Blog
Popular
SQLize.online is a free online SQL environment for quickly running, experimenting with and sharing code.
You can run your SQL code on top of the most popular RDBMS including MySQL, MariaDB, SQLite, PostgreSQL, Oracle and Microsoft SQL Server.
SQL code:
Upload
Copy
Format
Clear
/****************** 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, c.CourseID, x.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. */ create view StudentSchedule as select StudentName, CourseName, c.CourseID, x.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 ; /* 10) Query the schedule view for the courses Elliot *or* James are registered for. */
SQL
Server:
MariaDB 11.4
MariaDB 11.5
MariaDB 10
MariaDB 10 Sakila (ReadOnly)
MySQL 5.7
MySQL 5.7 Sakila (ReadOnly)
MySQL 8.0
MySQL 8.0 Sakila (ReadOnly)
SQLite 3
PostgreSQL 10 Bookings (ReadOnly)
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
MS SQL Server 2017
MS SQL Server 2019
MS SQL Server 2022
MS SQL Server 2022 AdventureWorks (ReadOnly)
Firebird 4.0
Firebird 4.0 (Employee)
Oracle Database 19c (HR)
Oracle Database 21c
Oracle Database 23c Free
SOQOL
Version
ER Diagram
Preserve result
Stuck with a problem?
Got Error?
Ask ChatGPT!
Result:
Copy
Clear