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

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

   <source lang="sql">

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></source>

Multi-column subquery

   <source lang="sql">

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></source>

Subquery in from clause

   <source lang="sql">

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.</source>