Subqueries in the WHERE Clause: equals
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.
Related examples in the same category