Nested Sub Queries ex :5 DBMS Lab

Nested Sub Queries

SQL> create table books1(isbn number(5)primary key,title varchar(25),author varchar(25),price number(7,2),pubyear number(4)); 

Table created. 

SQL> create table customers(custid number(5)primary key,custname varchar(25),address varchar(30)); 

Table created. 

SQL> create table orders(orderno number(5)primary key,custid number(5)references customers(custid),orderdate date); 

Table created 

SQL> create table orderlist(orderno number(5)references orders(orderno),isbn number(5)references books(isbn),qtty number(5),totprice number(5),shipdate date); 

Table created. 

SQL> insert into books values(&isbn,'&title','&author',&price,&pubyear);

Enter value for isbn: 4251

Enter value for title: The Scarlet Pimpernal

Enter value for author: Thomas Odissey

Enter value for price: 560

Enter value for pubyear: 1995

old   1: insert into books values(&isbn,'&title','&author',&price,&pubyear)

new   1: insert into books values(4251,'The Scarlet Pimpernal','Thomas Odissey',560,1995) 

1 row created. 

SQL> /

Enter value for isbn: 17854

Enter value for title: The Tempest

Enter value for author: Shakespeare

Enter value for price: 129

Enter value for pubyear: 1886

old   1: insert into books values(&isbn,'&title','&author',&price,&pubyear)

new   1: insert into books values(17854,'The Tempest','Shakespeare',129,1886) 

1 row created. 

SQL> /

Enter value for isbn: 19546

Enter value for title: Harry Potter Series

Enter value for author: J.K.Rowling

Enter value for price: 985

Enter value for pubyear: 2004

old   1: insert into books values(&isbn,'&title','&author',&price,&pubyear)

new   1: insert into books values(19546,'Harry Potter Series','J.K.Rowling',985,2004) 

1 row created. 

SQL> /

Enter value for isbn: 7546

Enter value for title: The Arabian Nights

Enter value for author: Arab philosophers

Enter value for price: 1876

Enter value for pubyear: 1885

old   1: insert into books values(&isbn,'&title','&author',&price,&pubyear)

new   1: insert into books values(7546,'The Arabian Nights','Arab philosophers',1876,1885) 

1 row created. 

SQL> /

Enter value for isbn: 5248

Enter value for title: Julius Caesar

Enter value for author: Shakespeare

Enter value for price: 768

Enter value for pubyear: 1779

old   1: insert into books values(&isbn,'&title','&author',&price,&pubyear)

new   1: insert into books values(5248,'Julius Caesar','Shakespeare',768,1779) 

1 row created. 

SQL> insert into customers values(&custid,'&custname','&address');

Enter value for custid: 11000

Enter value for custname: Sunitha

Enter value for address: 12, Nethaji Street, Madurai.

old   1: insert into customers values(&custid,'&custname','&address')

new   1: insert into customers values(11000,'Sunitha','12, Nethaji Street, Madurai.') 

1 row created. 

SQL> /

Enter value for custid: 11005

Enter value for custname: Ashok

Enter value for address: 154, K.R.Nagar, Coimbatore.

old   1: insert into customers values(&custid,'&custname','&address')

new   1: insert into customers values(11005,'Ashok','154, K.R.Nagar, Coimbatore.') 

1 row created. 

SQL> /

Enter value for custid: 11501

Enter value for custname: Ashwin

Enter value for address: 176, East street, Erode.

old   1: insert into customers values(&custid,'&custname','&address')

new   1: insert into customers values(11501,'Ashwin','176, East street, Erode.') 

1 row created.
 SQL> /

Enter value for custid: 12540

Enter value for custname: Sunil

Enter value for address: 90, Rajaji Street, Chidambaram

old   1: insert into customers values(&custid,'&custname','&address')

new   1: insert into customers values(12540,'Sunil','90, Rajaji Street, Chidambaram') 

1 row created. 

SQL> /

Enter value for custid: 14260

Enter value for custname: Uday

Enter value for address: 59, Gandhi Street, Tutucorin.

old   1: insert into customers values(&custid,'&custname','&address')

new   1: insert into customers values(14260,'Uday','59, Gandhi Street, Tutucorin.') 

1 row created. 

SQL> insert into orders values(&orderno,&custid,'&orderdate');

Enter value for orderno: 1701

Enter value for custid: 11000

Enter value for orderdate: 23-FEB-1992

old   1: insert into orders values(&orderno,&custid,'&orderdate')

new   1: insert into orders values(1701,11000,'23-FEB-1992') 

1 row created.

SQL> /

Enter value for orderno: 1654

Enter value for custid: 11005

Enter value for orderdate: 24-MAR-94

old   1: insert into orders values(&orderno,&custid,'&orderdate')

new   1: insert into orders values(1654,11005,'24-MAR-94') 

1 row created. 

SQL> /

Enter value for orderno: 17845

Enter value for custid: 11501

Enter value for orderdate: 12-APR-89

old   1: insert into orders values(&orderno,&custid,'&orderdate')

new   1: insert into orders values(17845,11501,'12-APR-89') 

1 row created. 

SQL> /

Enter value for orderno: 7842

Enter value for custid: 12540

Enter value for orderdate: 9-SEP-96

old   1: insert into orders values(&orderno,&custid,'&orderdate')

new   1: insert into orders values(7842,12540,'9-SEP-96') 

1 row created. 

SQL> /

Enter value for orderno: 5640

Enter value for custid: 14260

Enter value for orderdate: 6-OCT-98

old   1: insert into orders values(&orderno,&custid,'&orderdate')

new   1: insert into orders values(5640,14260,'6-OCT-98') 

1 row created.

SQL> insert into orderlist values(&orderno,&isbn,&qtty,&totprice,'&shipdate');

Enter value for orderno: 1701

Enter value for isbn: 4251

Enter value for qtty: 36100

Enter value for totprice: 6900

Enter value for shipdate: 09-APR-92

old   1: insert into orderlist values(&orderno,&isbn,&qtty,&totprice,'&shipdate')

new   1: insert into orderlist values(1701,4251,36100,6900,'09-APR-92') 

1 row created.

SQL> /

Enter value for orderno: 1654

Enter value for isbn: 17854

Enter value for qtty: 8795

Enter value for totprice: 520

Enter value for shipdate: 19-JUL-97

old   1: insert into orderlist values(&orderno,&isbn,&qtty,&totprice,'&shipdate')

new   1: insert into orderlist values(1654,17854,8795,520,'19-JUL-97') 

1 row created. 

SQL> /

Enter value for orderno: 7842

Enter value for isbn: 7546

Enter value for qtty: 4520

Enter value for totprice: 320

Enter value for shipdate: 27-SEP-68

old   1: insert into orderlist values(&orderno,&isbn,&qtty,&totprice,'&shipdate')

new   1: insert into orderlist values(7842,7546,4520,320,'27-SEP-68') 

1 row created. 

SQL> /

Enter value for orderno: 5640

Enter value for isbn: 5248

Enter value for qtty: 790

Enter value for totprice: 653

Enter value for shipdate: 12-DEC-96

old   1: insert into orderlist values(&orderno,&isbn,&qtty,&totprice,'&shipdate')

new   1: insert into orderlist values(5640,5248,790,653,'12-DEC-96') 

1 row created. 

SQL> /

Enter value for orderno: 1654

Enter value for isbn: 17854

Enter value for qtty: 17000

Enter value for totprice: 2360

Enter value for shipdate: 18-MAR-94

old   1:  insert into orderlist values(&orderno,&isbn,&qtty,&totprice,'&shipdate')

new   1:  insert into orderlist values(1654,17854,17000,2360,'18-MAR-94') 

1 row created.
 

SQL> select * from orderlist; 

   ORDERNO       ISBN       QTTY   TOTPRICE SHIPDATE

---------- ---------- ---------- ---------- ---------          -----------

      7842       7546       7200        620                            10-FEB-98

      5640       5248        790        760                              19-OCT-97

     17845      19546       9000        680         18-SEP-99

      1654      17854      17000       2360       18-MAR-94 

SQL> select * from books; 

      ISBN TITLE                     AUTHOR                         PRICE   PUBYEAR

---------- ------------------------- ------------------------- ---------- ----------

      4251 The Scarlet Pimpernal     Thomas Odissey                   560              1995 

     17854 The Tempest               Shakespeare                      129                          1886 

     19546 Harry Potter Series       J.K.Rowling                      985                        2004
 

      ISBN TITLE                     AUTHOR                         PRICE   PUBYEAR

---------- ------------------------- ------------------------- ----------        ----------

      7546 The Arabian Nights        Arab philosophers               1876                               1885 

      5248 Julius Caesar             Shakespeare                      768                               1779

SQL> select * from customers; 

    CUSTID CUSTNAME                  ADDRESS

---------- ------------------------- ------------------------------

     11000 Sunitha                   12, Nethaji Street, Madurai.

     11005 Ashok                     154, K.R.Nagar, Coimbatore.

     11501 Ashwin                    176, East street, Erode.

     12540 Sunil                     90, Rajaji Street, Chidambaram

     14260 Uday                      59, Gandhi Street, Tutucorin. 

SQL> select * from orders; 

   ORDERNO     CUSTID  ORDERDATE

---------- ---------- ----------

      1701     11000          23-FEB-1992

      1702      11005          24-MAR-94

     11501      11501          12-APR-89

      1705      12540          9-SEP-96

     11706      14260          6-OCT-98 

SQL> select * from books1 where price between 300 and 400; 

      ISBN TITLE                     AUTHOR                         PRICE    PUBYEAR

---------- ------------------------- ------------------------- -----------    ---------

     19546 The Arabian Nights        Arab Philosophers         320      1956 

SQL> select * from books1 b where exists(select * from orderlist o where b.isbn=o.isbn); 

      ISBN TITLE                     AUTHOR                         PRICE    PUBYEAR

---------- ------------------------- -------------------------           ----------        ----------

     17854 The Tempest               William Shakespeare              870      1877 

      7546 Harry Potter Series       J.K.Rowling                      600      2000 

      5248 Julius Caesar             William Shakespeare              650      1756

      ISBN TITLE                     AUTHOR                         PRICE    PUBYEAR

---------- ------------------------- ------------------------- ---------                   -----------

     19546 The Arabian Nights        Arab Philosophers                320      1956

SQL> select sum(price) from books1 group by pubyear; 

SUM(PRICE)

----------

       650

       870

       320

       900

       600 

SQL> select sum(price),pubyear from books1 group by pubyear having sum(price)>500; 

SUM(PRICE)    PUBYEAR

---------- ----------

       650       1756

       870       1877

       900       1998

       600       2000 

SQL> (select isbn from books1)union(select isbn from orderlist); 

      ISBN

----------

      4251

      5248

      7546

     17854

     19546 

SQL> (select isbn from books1)intersect(select isbn from orderlist); 

      ISBN

----------

      5248

      7546

     17854

     19546 

SQL> select c.custname,c.address,o.orderdate,o.orderno from customers c,orders o where c.custid=o.custid; 

CUSTNAME                  ADDRESS                        ORDERDATE    ORDERNO

------------------------- ------------------------------ --------- ----------

Ashok                     154, K.R.Nagar, Coimbatore.    24-MAR-94       1654

Ashwin                    176, East street, Erode.       12-APR-89      17845

Sunil                     90, Rajaji Street, Chidambaram 09-SEP-96       7842

Uday                      59, Gandhi Street, Tutucorin.  06-OCT-98       5640 

SQL> select c.custname,o.orderno from customers c,orders o where c.custid=o.custid(+); 

CUSTNAME                     ORDERNO

------------------------- ----------

Sunitha

Ashok                           1654

Ashwin                         17845

Sunil                           7842

Uday                            5640 

SQL> select c.custname,o.orderno from customers c,orders o where c.custid(+)=o.custid; 

CUSTNAME                     ORDERNO

------------------------- ----------

Ashok                           1654

Ashwin                         17845

Sunil                           7842

Uday                            5640