DBMS LAB1 SOLUTION
SQL> create table customer1 (cust_id number(5)
primary key, cust_name varchar2(15));
Output:
Table created.
SQL> desc customer1;
Output:
Name Null? Type
-----------------------------------------
-------- ----------------
CUST_ID NOT NULL NUMBER(5)
CUST_NAME VARCHAR2(15)
Valid Test Data
b) SQL> insert into customer1
values(&custid,'&custname');
SQL> select * from customer1;
Output:
CUST_ID CUST_NAME
---------- ---------------
100
ramu
101
kamal
102
raju
103
raju sundaram
104
lawrence
SQL> create table item(item_id number(4) primary key,
item_name varchar2(15),price number(6,2));
item_name varchar2(15),price number(6,2));
SQL> dsec item
Output:
Name
Null? Type
……………………………………………………………………………………………………
Cust_id
NOT NULL NUMBER(4)
Item_name
VARCHAR2(15)
PRICE
NUMBER(6,2)
SQL>insert into item
values(&item_id,’&item_name’,&price);
SQL>
select * from item;
Output:
ITEM_ID ITEM_NAME PRICE
……………………………………………………………………………………..
2334 geera 6.25
4532 corn soup 34.65
2124 lays chips 20
4531 setwet 99.99
2319 duracell 45.5
SQL>create
table sale(bill_no number(5) primary key,bill_date date, cust_id number(5)
references customer(cust_id), item_id number(4) references
item(item_id),qty_sold number(4));
Out put: Table Created.
SQL>dsec sale
Output:
Name Null? Type
………………………………………………………………………………………..
BILL_NO NOT
NULL NUMBER(4)
BILL_DATE DATE
CUST_ID NUMBER(5)
ITEM_ID NUMBER(4)
QTY_SOLD NUMBER(4)
SQL>insert into
Sale
&cust_id, &item_id, &qty_sold);
SQL>select *
from sale;
Output:
BILL_NO BILL_DATE
CUST_ID ITEM_ID QTY_SOLD
………………………………………………………………………………………………………...
1450 04-JAN-06 100 2124 2
1451 04-JAN-06 101 2319 1
1452 04-JAN-06 103 4531 2
1453 04-JAN-06 102 2334 3
1454 04-JAN-06 104 4532 3
c) List all the bills for the current date with
the customer names and item numbers
SQL>
select c.custname, i.itemid, s.billno from customer c, item I, sale s
where
c.custid=s.custid and
s.billdate=to_char(sysdate);
CUSTNAME ITEMID BILLNO
------------- --------- ---------
John 5001 332
d) List the total Bill details
with the quantity sold, price of the item and the final amount
SQL>
select i.price, s.qty,(i.price*s.qty) total from item I, sale s where
i.itemid=s.itemid;
PRICE QTY TOTAL
------- ----- --------
120 2 240
20 3 60
5 2 10
10 1 10
350 4 1400
e) List the details of the
customer who have bought a product which has a price>200
SQL>
select c.custid, c.custname from customer c, sale s, item i where
i.price>200 and
c.custid=s.custid
and i.itemid=s.itemid;
CUSTID CUSTNAME
--------- --------------
4
duffy
f) Give a count of how many
products have been bought by each customer
SQL>
select custid, count(itemid) from sale group by custid;
CUSTID COUNT(ITEMID)
---------- ---------------------
1 2
3 1
4 1
5 1
g) Give a list of products
bought by a customer having cust_id as 5
SQL>
select i.itemname from item i, sale s where s.custid=5 and i.itemid-s.itemid;
ITEMNAME
--------------
Pens
h) List the item details which are sold as of
today
SQL>
select i.itemid, i.itemname from item I, sale s where i.itemid=s.itemid
and
s.billdate=to_char(sysdate);
ITEMID ITEMNAME
--------- -------------
1234
pencil
i) Create a view which lists
out the bill_no, bill_date, cust_id, item_id, price, qty_sold, amount
SQL>create
view cust as (select s.billno, s.billdate, c.custid, i. iitemid, i.price, s.qty
from customer c,sale s item I where c.custid=s.custid and i.iemid=s.itemid);
view created.
SQL>select
* from cust;
BILLNO
BILLDATE CUSTID ITEMID PRICE
QTY
……………………………………………………………………………………………
3432 12-JAN-06 3 3244 120 2
4424 20-FEB-06 1 3456 20 3
332 13-MAR-06 1 1234 5 2
2343 10-MAR 5 5001 10 1
1331 11-MAR-06 4 76776 350 4
j) Create a view which lists the
daily sales date wise for the last one week
Comments
Post a Comment