Subqueries in the WHERE Clause: equals : Single Row Subquery « Query Select « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Query Select
3. Single Row Subquery

SQL>
SQL>
SQL> CREATE TABLE emp (
  2     empID      INT NOT NULL PRIMARY KEY,
  3     CourseID    INT NOT NULL,
  4     ProfessorID INT NOT NULL,
  5     SustainedOn DATE,
  6     Comments    VARCHAR(255));

Table created.

SQL> INSERT INTO emp (empID,CourseID,ProfessorID,SustainedOn,Comments) VALUES (1,1,1,DATE '2003-03-12','Hard');

1 row created.

SQL> INSERT INTO emp (empID,CourseID,ProfessorID,SustainedOn,Comments) VALUES (2,2,1,DATE '2003-03-13','Simple');

1 row created.

SQL> INSERT INTO emp (empID,CourseID,ProfessorID,SustainedOn,Comments) VALUES (3,3,2,DATE '2003-03-11','1 hour long');

1 row created.

SQL> INSERT INTO emp (empID,CourseID,ProfessorID,SustainedOn) VALUES (4,4,3,DATE '2003-03-18');

1 row created.

SQL> INSERT INTO emp (empID,CourseID,ProfessorID,SustainedOn,Comments) VALUES (5,5,2,DATE '2003-03-19','2 hours long');

1 row created.

SQL>
SQL> CREATE TABLE Course (
  2     CourseID INT NOT NULL PRIMARY KEY,
  3     Name     VARCHAR(50),
  4     Credits  INT);

Table created.

SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (1,'Mediaeval Romanian',5);

1 row created.

SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (2,'Philosophy',5);

1 row created.

SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (3,'History of Computing',5);

1 row created.

SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (4,'Organic Computer',5);

1 row created.

SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (5,'Applied Mathmatics',5);

1 row created.

SQL>
SQL>
SQL>
SQL> SELECT Name FROM Course
  2  WHERE CourseID =
  3  (
  4  SELECT CourseID from emp
  5  WHERE SustainedOn='10-MAR-03'
  6  );

no rows selected

SQL> drop table Course;

Table dropped.

SQL> drop table emp;

Table dropped.