QUESTION 1

Question 1

Customer Item schema queries which fall in all the categories mentioned above.

CUST(Custno, cname, state, phone)

ITEM(itemno, Itemname, Itemprice, Qty_hand)

INVOICE(Invno, invDate, Custno)

INVITEM(Invno, Itemno, Qty)

1. Create four table along with necessary constraints( PK,FK,notnull, Unique and Check constraints)
   
   
   CUST
   
   
   create table cust
   (custno varchar2(6) constraint pk_cust primary key,
   cname varchar2(20) not null,
   state varchar2(15),
   phone number(10));
   
   
   ITEM
   
   
   create table item
   (itemno varchar2(6)constraint pk_ino primary key,
   itemname varchar2(20) not null,
   itemprice number(8,2) constraint chk_price check(itemprice>0),
   qty_hand number(5) constraint chk_qty check(qty_hand>0),
   constraint chk_item check(itemno like 'i%'));
   
   
   INVOICE
   
   
   create table invoice
   (invno number(6) constraint pk_invno primary key,
   invdate date,
   custno varchar2(6) constraint fk_cust references cust(custno));
   
   
   INVITEM
   
   
   create table invitem
   (invno number(6) constraint fk_invno references invoice(invno),
   itemno varchar2(6) constraint fk_itemno references item,
   qty number(6));
   
   
2. Write a Insert script for insertion of rows with substitution variables.
   
   
   CUST :
   
   
   insert into cust values('c00001','pankaj','Gujarat',9999999999);
   insert into cust values('c00002','sanju','Maharashtra',8888888888);
   insert into cust values('c00003','dheru','Madras',7777777777);
   insert into cust values('c00004','jigee','Punjab',6666666666);
   
   
   ITEM :
   
   
   insert into item values('i00001','Mouse',150,10);
   insert into item values('i00002','Keyboard',250,20);
   insert into item values('i00003','Monitor',3500,5);
   insert into item values('i00004','Speaker',450,10);
   insert into item values('i00005','RAM',800,5);
   
   
   INVOICE :
   
   
   insert into invoice values(1001,'04-Jan-09','c00002');
   insert into invoice values(1002,'05-May-09','c00001');
   insert into invoice values(1003,'04-Apr-09','c00004');
   insert into invoice values(1004,'28-Jun-09','c00002');
   insert into invoice values(1005,'31-Aug-09','c00001');
   
   
   INVITEM :
   
   
   insert into invitem values(1003,'i00005',2);
   insert into invitem values(1001,'i00004',3);
   insert into invitem values(1005,'i00001',5);
   insert into invitem values(1002,'i00005',2);
   insert into invitem values(1002,'i00002',2);
   insert into invitem values(1003,'i00001',2);
   insert into invitem values(1004,'i00004',4);
   insert into invitem values(1002,'i00001',1);
   insert into invitem values(1001,'i00002',3);
   
   
3. Add a column to the Item table, which will allow us to store Item color field.
   
   
   alter table item add(itemcolor varchar2(15));
   
   
4. Write SELECT statement for the given queries.
1. Display Item name, Price in sentence form using concatenation
   
   select 'Itemname:' || itemname || ' And Itemprice:' || itemprice "Item-Price" from item;
   
   
2. Find total value of each item based on quantity on hand
   
   select sum(qty_hand) "Total" from item;
   
   
3. Find customers who are from state of Gujarat.
   
   select * from cust where state='Gujarat' ;
   
   
4. Display items with unit price of at least Rs. 100
   
   select * from ITEM where itemprice>=100;
   
5. List items whose range lies between Rs. 200 and Rs. 500
   
   select * from ITEM where itemprice BETWEEN 200 and 500;
   
6. Which customers are from lalbaug area of Ahmedabad, Baroda and Patan.
   
   select * from cust where area='lalbaug' and city IN ('Ahmedabad','Baroda','Patan')
   
7. Find all customers whose name start with Letter ‘P’.
   
   select * from cust where cname LIKE 'p%';
8. Find name of items with ‘W’ in their name.
   
   select * from item where itemname LIKE '%a%';
   
   
   
9. Sort all customers alphabetically
   
   select * from cust order by cname;
   
10. Sort all items in descending order by their prices.
    
    select * from item order by itemprice DESC;
    
11. Display all customers from M.P alphabetically
    
    select * from cust where state='Madras' order by cname;
    
12. Display invoices dates in ‘September 05, 2007’ format.
    
    select invno, custno, to_char(invdate,'Month DD,YYYY') "Date" from invoice;
    
    
    
13. Find total, average, highest and lowest unit price.
    
    select sum(itemprice) "TOTAL_PRICE", MAX(itemprice) "MAX_PRICE", AVG(itemprice) "AVG_PRICE", MIN(itemprice) "MINIMUM_PRICE" from item;
    
14. Count number of items ordered in each invoice
    
    select invno "INVOICE NO", COUNT(*) “TOTAL” from invitem GROUP BY invno;
    
15. Find invoices in which three or more items are ordered.
    
    select invno "INVOICE NO", sum(qty) "ORDERED" from invitem group by invno having sum(qty)>=3;
    
16. Find all possible combination of customers and items ( use Cartesian product)
    
    select cname, itemname from cust, invoice, invitem, item item where cust.custno=invoice.custno and invoice.invno=invitem.invno and invitem.itemno=item.itemno;
    
    
17. Display all item quantity and item price for invoices ( natural join)
    
    select * from invoice NATURAL JOIN invitem group by invitem.invno;
    
18. Find total price amount for each invoice.
    
    select invno, count(*) "QUANTITY", sum(qty*itemprice) “TOTAL” from invitem,item where item.itemno=invitem.itemno group by invitem.invno;
19. Use outer join to display items ordered as well as not ordered so far.
    
    select *  from item,invitem where item.itemno=invitem.itemno(+);
    
20. Find invoices with ‘Gear’ in their item name.
    
    select invno from item, invitem where invitem.itemno=item.itemno and item.itemn ame LIKE '%o%';
    
21. Display name of items ordered in invoice number 1001
    
    select itemname, invno from item, invitem where item.itemno= invitem.itemno and invno=1001;
    
22. Find the items that are cheaper than ‘Bullet’.
    
    select itemno, itemname from item where itemprice<(select itemprice from item where itemname='Speaker');
    
23. Create a table ( namely guj_cust) for all Gujarat customer based on existing customer table
    
    create table cust_adi as select * from cust where state=’Ahmedabad';
    
24. Copy all M.P customers to the table with Gujarat customers
    
    insert into cust_adi (select * from cust where state='Punjab')
    
25. Rename Guj_cust table to MP_cust table.
    
    rename cust_adi to cust_punjab
    
26. Find the customers who are not in Gujarat or M.P
    
    Select * from cust where state NOT IN('Ahmedabad','Punjab');
    
27. Delete rows from customer table that are also in MP_cust table
    
    delete from cust where exists(select custno from cust_punjab where cust_punjab.custno =cust.custno );
    
28. Find the items with top three prices
    
    select *  from
    
    (
         select itemname,
         row_number()  over (order by itemprice) rank
         from item
    )
    where rank<=3;
    
29. Find two items with lowest quantity on hand
    
    select *  from
    
    (
         select itemname, qty_hand,
         row_number()  over (order by qty_hand) rank
         from item
    )
    where rank between 1 and 2;
    
30. Create a simple view with item names and item price only
    
    create view view1 as select itemname , itemprice from item
    
31. Create a sequence that can be used to enter new items into item table
    
    create sequence seq_item increment by 1 start with 6;
    
    
    
32. Add a new item into item table with sequence just created.
    
    insert into item values(seq_item.nextval,'Modem',450,2);
    
    
    
33. Create a index file to speed up a search based on customer name
    
    create index idx_item on item(itemno);
    
    
    
34. Lock customer Mr. Shah record to update the state and phone no.
    
    select state,phone from cust where cname='pankaj' for update;
    
35. Give everybody select and insert rights on your item table
    
    grant select,insert on item to ALL;
    
36. Revoke the insert option on item table from user ‘Roshi
    
    revoke insert on item from rahul;