Right Outer Join, room table and class table

   
SQL>
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>
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>
SQL>
SQL>
SQL> SELECT Room.RoomID, Class.Time
  2  FROM Room
  3     RIGHT OUTER JOIN Class
  4     ON Room.RoomID = Class.RoomID
  5  ORDER BY Room.RoomID;

    ROOMID TIME
---------- --------------------------------------------------
         2 Tue 10:00-12:00, Thu 14:00-15:00
         3 Mon 14:00-16:00
           Mon 09:00-11:00
           Mon 11:00-12:00, Thu 09:00-11:00

4 rows selected.

SQL>
SQL> drop table Room;

Table dropped.

SQL> drop table Class;

Table dropped.

Related examples in the same category

1.	right outer join between department table and employee table
2.	Right outer joins:return all the rows from the table on the right instead of the table on the left
3.	Group by and right join department id
4.	Right Outer Join(room vs class)
5.	Right join with where in clause

Right Outer Join, room table and class table : Right Outer Joins « Table Joins « Oracle PL / SQL

Related examples in the same category