set 7 dbms gtu

SET – 7

Create the database SHOPPING and create given tables with all necessary constraints such as primary key, foreign key, unique key, not null and check constraints.

CUSTOMER (cno, cust_name, cust_phone, location,gender)

create table customer

(

cno int primary key,

cust_namevarchar(30) not null,

cust_phonenumber(10),

location varchar(30),

gender varchar(6) ,

constraint ck_gencheck(gender in('male', 'female'))

);

ITEM (itemno, itemname, color, weight, expire_date, price, shop_name)

create table item

(

itemno int primary key,

itemnamevarchar(30) not null,

colorvarchar(10),

weight int,

expire_date date,

price int check(price > 0),

shop_namevarchar(30)

);

CUST_ITEM (cno, itemno, quantity_purchased, date_purchase)

create table cust_item

(cno ,

itemno,

quantity_purchased int constraint ck_qulcheck(quantity_purchased> 0),

date_purchase date,

constraint fk_cno foreign key(cno) references customer(cno),

constraint fk_itemno foreign key(itemno) references item(itemno)

);

1.     Delete the items whose price is more than 50000.

delete from cust_item  whereitemno in(select itemno from item where price > 50000);

delete from item where price > 50000;

2.     Find the names of the customer who is located in same location as that of other customer.

3. Display the names of items which is black, white & brown in color.

            select * from item where color in ('black','white');

4. Display the names of all the items whose names lies between ‘p’ and ‘s’.

            select * from item where itemname between 'P' and 'S';

5.  Find the item which is having less weight.

            select * from item where weight = (select min(weight) from item);

6. Add one month more to those items whose item no =40.

            select ADD_MONTHS(expire_date,1) from item where itemno = 4;

7. Count total number of items which is going to expire in next month

select count(itemno) from item where to_char(expire_date, 'MM') = (to_char(sysdate, 'MM') );

8. List all customers whose phone number starts with ‘99’.

            select * from customer where cust_phone like '99%';

9. Display total value (qty*price) for all items.

            select (QUA_P * PRICE) as value from item a ,cust_item b

            where a.itemno = b.itemno;

10. List customer details who has purchased maximum number of items

11. Display total price item wise.

            select itemname, price from item;

12. List name of items, customer details and qty purchased.

            select i.itemname, c.*, t.qua_p from customer c, item i, cust_item t

            where c.cno = t.c_no and i.itemno = t.itemno;

13. Write a PL/SQL procedure which will display records in the following format

14. Write a trigger which do not allow insertion / updation / deletion of Item details on Sunday.