DBMS LAB Part-B 4

Ex : 4
Create two tables Production_detail and Purchased_detail with the following fields:
Production_detail (Product_no, Product_name, Qty_available, Price Profit(%))
Purchased_detail (Cust_name, Product_no, Qty_sold)

Create table product_detail
SQL> create table product_detail(
    Prno varchar2(6) primary key,
    prname varchar2(15) not null,
    quantity number(3) not null,
    price number(6) not null,
    profit number(3) not null,
    check(prno like 'p%'));

Table created.

Display table fields
SQL> desc product_detail;

Name               Null?                  Type
-----------------------------------------------------
PRNO              NOT NULL        VARCHAR2(6)
PRNAME            NOT NULL        VARCHAR2(15)
QUANTITY     NOT NULL        NUMBER(3)
PRICE             NOT NULL        NUMBER(6)
PROFIT            NOT NULL        NUMBER(3)

Insert values into table
SQL> insert into product_detail values(
     'p001','monitor',10,4500,20);

1 row created.

SQL> select * from product_detail;

PRNO   PRNAME           QUANTITY     PRICE    PROFIT
---------------------------------------------------------------------------------
p001   monitor                10      4500        20
p002   pendrive               50       250          5
p003   CDdrive                 5       950          8
p004   keyboard                8       250         10

Create table purchased_detail
SQL> create table purchased_detail(
   Custname  varchar2(15) not null,
   prno      varchar2(6) references product_detail(prno),
   qty_sold  number(3) not null,
   primary key(custname,prno));

Table created.

Display table fields
SQL> desc purchased_detail;

Name               Null?                      Type
--------------------------------------------------------------------
CUSTNAME         NOT NULL          VARCHAR2(15)
PRNO             NOT NULL          VARCHAR2(6)
QTY_SOLD         NOT NULL          NUMBER(3)

Insert values into table
SQL> insert into purchased_detail values(
   'raman','p003',2);

1 row created.

SQL> select * from purchased_detail;

CUSTNAME        PRNO       QTY_SOLD
------------------------------------------------------
raman           p003           2
laxman          p002           5
bharath         p002          10
manish          p001           3
amith           p004           2

1. Query to display total amount spend by Mr. Laxman for purchasing any product. 

SQL> select (p.price*c.qty_sold) "Total amount"
    from product_detail p,purchased_detail c
    where p.prno=c.prno and
    c.custname='laxman';

Total amount
-----------------
1250

2. Query to display the name of product for which either quantity available is less than 10 or quantity sold is less than 4

SQL> select p.prname
    from product_detail p,purchased_detail c
    where p.prno=c.prno and
    (p.quantity<10 or c.qty_sold<4);

PRNAME
--------------
CDdrive
monitor
keyboard

3. Query to display the name of product and quantity taken by Mr. Bharath.

SQL> select p.prname,c.qty_sold
    from product_detail p,purchased_detail c
    where p.prno=c.prno and
    c.custname='bharath';

PRNAME           QTY_SOLD
-------------------------------------
pendrive               10

4. How much the profit does the shop keeper gets on Mr. Manish purchase?

SQL> select (p.price*c.qty_sold*profit/100) "Profit"
    from product_detail p,purchased_detail c
    where p.prno=c.prno and
    c.custname='manish';

   Profit
   --------
   2700

5. How many “Pen drives” have sold?
SQL> select sum(c.qty_sold) "Pendrives sold"
    from product_detail p,purchased_detail c
    where p.prno=c.prno and
    p.prname='pendrive';

Pendrives sold
------------------
15