Oracle PL/SQL Tutorial/Query Select/Multi column subquery

inline view: What percentage of these items exist in each bin selected

SQL>
SQL> create table t
  2  (item number,
  3   bin number,
  4   primary key (bin , item) );
Table created.
SQL>
SQL> insert into t values(2, 34);
1 row created.
SQL> insert into t values(45,34);
1 row created.
SQL> insert into t values(76,34);
1 row created.
SQL> insert into t values(76,35);
1 row created.
SQL> insert into t values(89,35);
1 row created.
SQL> insert into t values(45,35);
1 row created.
SQL> insert into t values(45,36);
1 row created.
SQL>
SQL> select * from t
  2  order by 1,2;
         2         34
        45         34
        45         35
        45         36
        76         34
        76         35
        89         35
7 rows selected.
SQL>
SQL> 
SQL> select bin, count(*), count(*)/cnt
  2  from (
  3         select bin, count(distinct item) over () AS cnt
  4         from t
  5         where item in (2,45,76,89) )
  6  group by bin, cnt
  7  /
        34          3          .75
        35          3          .75
        36          1          .25
3 rows selected.
SQL> drop table t;
Table dropped.
SQL>

Multi-column subquery

SQL>
SQL> CREATE TABLE product_order (
  2       product_name  VARCHAR2(25),
  3       salesperson   VARCHAR2(3),
  4       order_date DATE,
  5       quantity      NUMBER(4,2)
  6  );
Table created.
SQL>
SQL>
SQL> INSERT INTO product_order VALUES ("Product 1", "CA", "14-JUL-03", 1);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 2", "BB", "14-JUL-03", 75);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 3", "GA", "14-JUL-03", 2);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 4", "GA", "15-JUL-03", 8);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 5", "LB", "15-JUL-03", 20);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 6", "CA", "16-JUL-03", 5);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 7", "CA", "17-JUL-03", 1);
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT * FROM product_order ORDER BY product_name, order_date;
PRODUCT_NAME              SAL ORDER_DAT   QUANTITY
------------------------- --- --------- ----------
Product 1                 CA  14-JUL-03          1
Product 2                 BB  14-JUL-03         75
Product 3                 GA  14-JUL-03          2
Product 4                 GA  15-JUL-03          8
Product 5                 LB  15-JUL-03         20
Product 6                 CA  16-JUL-03          5
Product 7                 CA  17-JUL-03          1
7 rows selected.
SQL>
SQL> SELECT * FROM   product_order
  2  WHERE  (product_name, order_date)
  3  IN     (SELECT product_name, MAX(order_date) FROM product_order GROUP BY product_name);
PRODUCT_NAME              SAL ORDER_DAT   QUANTITY
------------------------- --- --------- ----------
Product 1                 CA  14-JUL-03          1
Product 2                 BB  14-JUL-03         75
Product 3                 GA  14-JUL-03          2
Product 4                 GA  15-JUL-03          8
Product 5                 LB  15-JUL-03         20
Product 6                 CA  16-JUL-03          5
Product 7                 CA  17-JUL-03          1
7 rows selected.
SQL>
SQL> drop table product_order;
Table dropped.
SQL>
SQL>

Subquery in from clause

SQL>
SQL> create table myTable
  2  as
  3  select rownum id, a.*
  4    from all_objects a
  5   where 1=0
  6  /
Table created.
SQL>
SQL>
SQL> select *
  2    from myTable myTable1, ( select owner, max(last_ddl_time) max_time from myTable group by owner ) myTable2
  3   where myTable1.owner = myTable2.owner
  4  /
no rows selected
SQL> drop table myTable;
Table dropped.