Logo 
Search:

Oracle Articles

Submit Article
Home » Articles » Oracle » DatabaseRSS Feeds

SUPPLIER-ORDERS-CUSTOMERS database

Posted By: Adam Evans     Category: Oracle     Views: 4460

Article that create SUPPLIER-ORDERS-CUSTOMERS database with alter, insert, select, update, delete etc...

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
  
Share: 


Didn't find what you were looking for? Find more on SUPPLIER-ORDERS-CUSTOMERS database Or get search suggestion and latest updates.

Adam Evans
Adam Evans author of SUPPLIER-ORDERS-CUSTOMERS database is from London, United Kingdom.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!