Inner and Outer Joins : Inner Joins « Table Joins « Oracle PL / SQL

1. Oracle PL / SQL
2. Table Joins
3. Inner Joins

Inner and Outer Joins

  
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> 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>
SQL> SELECT Class.ClassID,
  2         Class.CourseID,
  3         Class.Time,
  4         Room.Comments AS RoomName
  5  FROM Class INNER JOIN Room
  6  ON Class.RoomID = Room.RoomID
  7  ORDER BY ClassID;

   CLASSID   COURSEID TIME
---------- ---------- --------------------------------------------------
ROOMNAME
--------------------------------------------------
         2          2 Mon 11:00-12:00, Thu 09:00-11:00
Languages Room 1

         3          3 Mon 14:00-16:00
Science Room 1

         4          4 Tue 10:00-12:00, Thu 14:00-15:00
Science Department


3 rows selected.

SQL>
SQL>
SQL> drop table Room;

Table dropped.

SQL> drop table Class;

Table dropped.

SQL>

   
    
  

Related examples in the same category