Consider the following SUPPLIER-ORDERS-CUSTOMERS database.
1.Customer (cnum,cname,city,rating,snum)
1.Rating should be >= 0
2.Default value for rating = 0
3.Snum is #F.K. referring to salespeople table
Create customer table
create table cust
(
cnum number(3) primary key,
cname varchar2(20),
city varchar2(20),
rating number(2) default 0 check(rating >= 0),
snum number(3) references salesppl on delete cascade
)
Order (onum,orderDate, cnum,snum)
1.cnum is #F.K. referring to Customer table
2.snum is #F.K. referring to Salespeople table
Create order table
create table ord
(
onum number(3) primary key,
odate date,
cnum number(3) references cust on delete cascade,
snum number(3) references salesppl on delete cascade
)
SalesPeople(snum,sname,city,rating,comm,ManagerNo)
Note : Manager of salesman itself is a manager with record in same table.
Salesman who is not having any manager has his salesmanNo as ManagerNo.
1.Default Value for commisson = 0
2.Decimal places for commission field = 2
3.ManagerNo is #F.K. referring to snum of same table.
4.City can be one of ahmd, bombay, chennai.
Create salespeople table
create table salesppl
(
snum number(3) primary key,
sname varchar2(20),
city varchar2(20) check(city in('ahmd','bom','che')),
rating number(2),
comm number(5,2) default 0,
manag_no number(2) references salesppl (snum)
)
Insert statements
INSERT STATEMENT FOR CUSTOMER TABLE
insert into cust values(&cnum,'&cname','&city',&rating,&snum);
INSERT STATEMENT FOR ORDER TABLE
insert into ord values(&onum,'&odate',&cnum,&snum);
INSERT STATEMENT FOR SALESPEOPLE TABLE
insert into salesppl values(&snum,'&sname','&city',&rating,&comm,&manag_no);
Display customer table details using select statement
select * from cust;
Output :
CNUM CNAME CITY RATING SNUM
44 shah ahmd 55 10
77 punjabi bom 88 12
88 mohmed bom 66 11
99 shah che 22 13
Display order table details using select statement
select * from ord;
Output :
ONUM ODATE CNUM SNUM
1 10-JAN-84 44 10
2 20-SEP-83 77 12
3 10-FEB-84 88 12
4 10-MAR-84 99 13
Display salespeople table details using select statement
select * from salesppl;
SNUM SNAME CITY RATING COMM MANAG_NO
10 abhishek bom 12 11 10
12 rani che 23 22 10
11 urmila bom 33 44 12
13 fardeen ahmd 9 8 10
Increase the commission of all salespeople by 20% who are from ‘ahmd’ city using update statement.
update salesppl set comm= comm*0.2 where city='ahmd';
1 row updated.
select * from salesppl;
Output :
SNUM SNAME CITY RATING COMM MANAG_NO
10 abhishek bom 12 11 10
12 rani che 23 22 10
11 urmila bom 33 44 12
13 fardeen ahmd 9 1.6 10
Select all customers whose rating is less than 10 and who belong to Mumbai.
select * from cust where rating < 90 and city='bom'
Output :
CNUM CNAME CITY RATING SNUM
77 punjabi bom 88 12
88 mohmed bom 66 11
Select all orders where orderDate should be greater than 10/02/2000(dd/mm/yyyy).
select * from ord where odate > '10-feb-83'
Output :
ONUM ODATE CNUM SNUM
1 10-JAN-84 44 10
2 20-SEP-83 77 12
3 10-FEB-84 88 12
4 10-MAR-84 99 13
Select all salespeople who are from ahmd, Mumbai or delhi city.
select * from salesppl where city in('bom','che');
Output :
SNUM SNAME CITY RATING COMM MANAG_NO
10 abhishek bom 12 11 10
12 rani che 23 22 10
11 urmila bom 33 44
Select the names of all salespeople whose rating is between 1 and 4.
select * from salesppl where rating between 10 and 20
Output :
SNUM SNAME CITY RATING COMM MANAG_NO
10 abhishek bom 12 11