Logo 
Search:

Oracle Articles

Submit Article
Home » Articles » Oracle » DatabaseRSS Feeds

SUPPLIER-PARTS database

Posted By: Sienna Hughes     Category: Oracle     Views: 7300

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

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.
 


  
Share: 


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

Sienna Hughes
Sienna Hughes author of SUPPLIER-PARTS 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!