|
SQL>
SQL> CREATE TABLE Room (
2 RoomID INT NOT NULL PRIMARY KEY,
3 Comments VARCHAR(50),
4 Capacity INT);
Table created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (1,'Main hall',500);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (2,'Science Department',200);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (3,'Science Room 1',100);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (4,'Languages Block',300);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (5,'Languages Room 1',75);
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE TABLE Class (
2 ClassID INT NOT NULL PRIMARY KEY,
3 CourseID INT NOT NULL,
4 InstructorID INT NOT NULL,
5 RoomID INT NOT NULL,
6 Time VARCHAR(50));
Table created.
SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (1,1,1,6,'Mon 09:00-11:00');
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (2,2,1,5,'Mon 11:00-12:00, Thu 09:00-11:00');
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (3,3,2,3,'Mon 14:00-16:00');
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (4,4,3,2,'Tue 10:00-12:00, Thu 14:00-15:00');
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (5,5,2,9,'Tue 14:00-16:00');
1 row created.
SQL>
SQL>
SQL> SELECT Room.RoomID, Class.Time
2 FROM Room JOIN Class ON Room.RoomID <> Class.RoomID;
ROOMID TIME
---------- --------------------------------------------------
1 Mon 09:00-11:00
2 Mon 09:00-11:00
3 Mon 09:00-11:00
4 Mon 09:00-11:00
5 Mon 09:00-11:00
1 Mon 11:00-12:00, Thu 09:00-11:00
2 Mon 11:00-12:00, Thu 09:00-11:00
3 Mon 11:00-12:00, Thu 09:00-11:00
4 Mon 11:00-12:00, Thu 09:00-11:00
1 Mon 14:00-16:00
2 Mon 14:00-16:00
4 Mon 14:00-16:00
5 Mon 14:00-16:00
1 Tue 10:00-12:00, Thu 14:00-15:00
3 Tue 10:00-12:00, Thu 14:00-15:00
4 Tue 10:00-12:00, Thu 14:00-15:00
5 Tue 10:00-12:00, Thu 14:00-15:00
1 Tue 14:00-16:00
2 Tue 14:00-16:00
3 Tue 14:00-16:00
4 Tue 14:00-16:00
5 Tue 14:00-16:00
22 rows selected.
SQL>
SQL>
SQL> drop table room;
Table dropped.
SQL> drop table class;
Table dropped.
|