Consider the following SUPPLIER-PARTS database
Supplier (SupplierId, SupplierName,city,Rating)
Part (PartId,SupplierId,PartName,Part Desc,PartWeight,Price)
Order (OrderId,OrderDate,SupplierId,PartId,OrderQty,SalesmanId,OrderCost)
Salesman (SalesmanId, SalesmanName,city,commission)
Note :
1.Create appropriate #P.K and #F.K referential integrity constraints.
2.City should be from ahmd,Bombay,delhi.
3.PartWeight should in decimal with 2 digit.
4.Commission should be less than 1.
Create supplier table
create table supp
(
suppid number(2) PRIMARY KEY,
suppname varchar(10),
city varchar(5)
)
Alter city field of supplier table
alter table supp add constraint city check(city in('abd','bom','delhi'));
Alert part table add foreign key constrain
alter table part add constraint suppid foreign key(suppid)references supp;
Create part table
create table part
(
partid number(2) PRIMARY KEY,
suppid number(2),
partname varchar2(10),
partdesc varchar2(8) ,
partweight number(5,2),
price number(5,2)
)
Create order table
create table ord_tab
(
ordid number(2) primary key,
odate date,
suppid number(2) references supp on delete cascade,
partid number(2) references part on delete cascade,
orderqty number(5,2),
salesmid number(2) references salesman on delete cascade,
ordcost number(5,2)
)
Create salesman table
CREATE TABLE SALESMAN
(
SALESID NUMBER(2) PRIMARY KEY,
SALESNAME VARCHAR2(10),
CITY VARCHAR(5),
COMM NUMBER(3,2) CHECK (COMM < 1)
)
Insert statements
INSERT STATEMENT FOR SUPPLIER TABLE
INSERT INTO SUPP VALUES(&SUPPID,'&SUPPNAME','&CITY',&RATING)
Output :
SUPPID SUPPNAME CITY RATING
1 daps abd 10
2 bhikho bom 20
3 vidhdo delhi 2
4 swetu bom 1
INSERT STATEMENT FOR SALESMAN TABLE
INSERT INTO SALESMAN VALUES(&SALESID,'&SALESNAME','&CITY','&COMM')
Output :
SALESID SALESNAME CITY COMM
12 DAPS ABD .1
23 SHALU ASD .2
34 DOLLY VASNA .8
45 DANSY KER .9
INSERT STATEMENT FOR PART TABLE
INSERT INTO PART VALUES(&PARTID,&SUPPID,'&PARTNAME','&PARTDESC',&PARTWEIGHT,&PRICE)
PARTID SUPPID PARTNAME PARTDESC PARTWEIGHT PRICE
11 1 BUTTON WHITE 99 123
22 2 JEANS RED 88 234
33 3 SHOES BLACK 77 345
44 4 JACKET BROWN 66 456
Design the queries for the following and show the result for your sample data.
List all suppliers who are from “ahmedabad” city using select statement
select * from supp where city='abd';
Output :
SUPPID SUPPNAME CITY RATING
1 daps abd 10
List all suppliers who have supplied partA
select suppname from supp,part where supp.suppid=part.suppid and partid=22;
Output :
SUPPNAME
bhikho
List all suppliers with their total order amount
select suppid,sum(ordcost) from ord_tab group by(suppid);
Output :
SUPPID SUM(ORDCOST)
1 112
2 77.7
3 56
4 55
List all suppliers with their maximum order amount
select suppid,max(ordcost) from ord_tab group by(suppid) ;
Output :
SUPPID MAX(ORDCOST)
1 112
2 77.7
3 56
4 55
List all suppliers having rating=1 and who are from ahmedabad city
select * from supp where rating=1 and city='bom';
SUPPID SUPPNAME CITY RATING
4 swetu bom 1
List down all orders with all part names and highest cost order for that part name
select part.partname,max(ord_tab.ordcost) from part,ord_tab where part.partid=ord_tab.partid group by(part.partname);
Output :
PARTNAME MAX(ORD_TAB.ORDCOST)
BUTTON 112
JACKET 55
JEANS 77.7
SHOES 56
List all orders having salesman named “sanjay”
select ord_tab.* from ord_tab,salesman where salesman.salesid = ord_tab.salesmid and salesman.salesname='DAPS'
Output :
ORDID ODATE SUPPID PARTID ORDERQTY SALESMID ORDCOST
66 10-JAN-84 1 11 111 12 112
List the total cost of orders which have salesman named “chirag”
select part.partname,sum(ord_tab.ordcost) from part,ord_tab,salesman where part.partid=ord_tab.partid
and salesman.salesid=ord_tab.salesmid and salesman.salesname='DANSY' group by(part.partname)
Output :
PARTNAME SUM(ORD_TAB.ORDCOST)
JACKET 55
Delete all orders whose ordercost is below 500 AND orderdate is less than 1/1/1998
delete from ord_tab where ordcost > 70 and odate < '24-dec-89'
SQL> select * from ord_tab;
ORDID ODATE SUPPID PARTID ORDERQTY SALESMID ORDCOST
34 12-NOV-00 3 33 44 34 56
33 23-DEC-89 4 44 66 45 55
Update the commission of all salesman who belong to city “Bombay” and having less than 10% commission to 20%.
SQL> select * from salesman;
SALESID SALESNAME CITY COMM
---------- ---------- ----- ----------
12 DAPS ABD .1
23 SHALU ASD .2
34 DOLLY VASNA .8
45 DANSY KER .9
SQL> update salesman set comm=comm*0.1 where city='KER' and comm between 0.2 and 2;
1 row updated.
SQL> select * from salesman;
SALESID SALESNAME CITY COMM
12 DAPS ABD .1
23 SHALU ASD .2
34 DOLLY VASNA .8
45 DANSY KER .09
Append all the Bombay supplier records from Supplier table to AhmdSuppliers table.(use Append-Query)
create table ahmdsupp(suppid,suppname,city,rating)
as select suppid,suppname,city,rating from supp
SQL> insert into ahmdsupp select suppid,suppname,city,rating from supp where city='abd';
1 row created.
SQL> select * from ahmdsupp;
SUPPID SUPPNAME CITY RATING
1 daps abd 10
2 bhikho bom 20
3 vidhdo delhi 2
4 swetu bom 1
1 daps abd 10
SQL> delete from ahmdsupp where city<>'abd';
3 rows deleted.