Extract a Subset of the Results : Correlated Subquery « Query Select « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Query Select
  3. Correlated Subquery
SQL>
SQL> CREATE TABLE myTable (username VARCHAR(20),score INT);

Table created.

SQL> INSERT INTO myTable VALUES ('gordon',10);

1 row created.

SQL> INSERT INTO myTable VALUES ('user01',20);

1 row created.

SQL> INSERT INTO myTable VALUES ('user02',30);

1 row created.

SQL> INSERT INTO myTable VALUES ('user03',40);

1 row created.

SQL> INSERT INTO myTable VALUES ('user04',50);

1 row created.

SQL> INSERT INTO myTable VALUES ('user05',60);

1 row created.

SQL> INSERT INTO myTable VALUES ('user06',70);

1 row created.

SQL> INSERT INTO myTable VALUES ('user07',80);

1 row created.

SQL> INSERT INTO myTable VALUES ('user08',90);

1 row created.

SQL> INSERT INTO myTable VALUES ('user09',100);

1 row created.

SQL> INSERT INTO myTable VALUES ('user10',110);

1 row created.

SQL> INSERT INTO myTable VALUES ('user11',120);

1 row created.

SQL>
SQL> SELECT username,score FROM
  2  (SELECT username,score FROM myTable ORDER BY score DESC) t
  3    WHERE rownum <= 10;

USERNAME                  SCORE
-------------------- ----------
user11                      120
user10                      110
user09                      100
user08                       90
user07                       80
user06                       70
user05                       60
user04                       50
user03                       40
user02                       30

10 rows selected.

SQL> DROP TABLE myTable;

Table dropped.

SQL>
SQL>








2.37.Correlated Subquery
2.37.1.Writing Correlated Subqueries
2.37.2.Using EXISTS and NOT EXISTS with a Correlated Subquery
2.37.3.A correlated subquery: the subquery references a column from a table referred to in the parent statement.
2.37.4.The subquery returning the literal value 1
2.37.5.Using NOT EXISTS with a Correlated Subquery
2.37.6.list all employees who is younger than Joe
2.37.7.List all employees who attended XML course and Java course
2.37.8.Extract a Subset of the Results
2.37.9.How Many Products By Department with correlated subqueries 1
2.37.10.How Many Products By Department with correlated subqueries 2
2.37.11.Correlated subquery using the EXISTS operator