Subqueries in the WHERE Clause: equals : Subquery Basics « Subquery « Oracle PL / SQL

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » Subquery » Subquery Basics 
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,CommentsVALUES (1,1,1,DATE '2003-03-12','Hard');

row created.

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

row created.

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

row created.

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

row created.

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

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,CreditsVALUES (1,'Mediaeval Romanian',5);

row created.

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

row created.

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

row created.

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

row created.

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

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
1.Use sub query as a virtual table
2.Compare with data from subquery
3.An example of a nested three-level subquery
4.If an inner query returns a NULL, the outer query also returns NULL
5.Working with multi-column subqueries
6.Use aggregate function in sub query
7.Sub query: 'SELECT 1 FROM dept d'
8.Using the EXISTS and NOT EXISTS operators
9.Writing Single Row Subqueries
10.Compare with the sub query result
11.Subqueries in a HAVING Clause: Uses a subquery in the HAVING clause of the outer query
12.Subqueries in a FROM Clause (Inline Views)
13.Single Row Subqueries May Return a Maximum of One Row
14.Subqueries May Not Contain an ORDER BY Clause
15.Sub query with table join
16.Writing Multiple Column Subqueries with table join
17.Subquery just returns a literal value: improve performance of your query
18.EXISTS typically offers better performance than IN with subqueries
19.NVL() is used to convert null in correlated query
20.Writing Nested Subqueries
21.subqueries in the SELECT column list (New Way)
22.subqueries in the SELECT column list (Old way)
23.Not equals and subquery
24.Greater than average salary
25.Greater than max(salary)
26.Larger than value from subquery
27.Subquery in select statement
28.Subquery in where clause
29.Subqueries That Return Multiple Results
30.Subqueries in the WHERE Clause: less than
31.Simple Subqueries in select statement
32.Single-row subqueries return only one row of result.
33.The parent query of a single-row subquery can return more than one row.
34.Born after employee 4 was born
35.inline view: What percentage of these items exist in each bin selected
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.