Practical of RDBMS


We want to create a data of a Company. So we decide to create a table named DEPT, EMP, CUSTOMER, ORD, ITEM and PRODUCT.

All the Query Command is given to Create and Add data in Particular table.

To Create DEPT Table:

create table dept
(deptno number(2) constraint pri_key primary key,
dname char(14),
loc char(13));

To Add data in DEPT Table:

insert into dept
values(10,'ACCOUNTING','NEW YORK');
insert into dept
values(20,'RESEARCH','DALLAS');
insert into dept
values(30,'SALES','CHICAGO');
insert into dept
values(40,'OPERATIONS','BOSTON');

To Create EMP Table:

create table emp
(empno number(4) constraint prm_key primary key,
ename varchar2(10),
job char(9),
mgr number(4) references emp,
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) references dept);


To Add data in EMP Table:

insert into emp
(empno,ename,job,hiredate,sal,deptno)
values(7839,'KING','PRESIDENT','17-nov-1981',5000,10);
insert into emp
(empno,ename,job,mgr,hiredate,sal,deptno)
values(7698,'BLAKE','MANAGER',7839,'1-may-1981',2850,30);
insert into emp
(empno,ename,job,mgr,hiredate,sal,deptno)
values(7782,'CLARK','MANAGER',7839,'9-jun-1981',2450,10);
insert into emp
(empno,ename,job,mgr,hiredate,sal,deptno)
values(7566,'JONES','MANAGER',7839,'2-apr-1981',2975,20);
insert into emp
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7654,'MARTIN','SALESMAN',7698,'28-sep-1981',1250,1400,30);
insert into emp
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7499,'ALLEN','SALESMAN',7698,'20-feb-1981',1600,300,30);
insert into emp
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7844,'TURNER','SALESMAN',7698,'8-sep-1981',1500,0,30);
insert into emp
(empno,ename,job,mgr,hiredate,sal,deptno)
values(7900,'JAMES','CLERK',7698,'3-dec-1981',950,30);
insert into emp
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7521,'WARD','SALESMAN',7698,'22-feb-1981',1250,500,30);
insert into emp
(empno,ename,job,mgr,hiredate,sal,deptno)
values(7902,'FORD','ANALYST',7566,'3-dec-1981',3000,20);
insert into emp
(empno,ename,job,mgr,hiredate,sal,deptno)
values(7369,'SMITH','CLERK',7902,'17-dec-1980',800,20);
insert into emp
(empno,ename,job,mgr,hiredate,sal,deptno)
values(7788,'SCOTT','ANALYST',7566,'9-dec-1982',3000,20);
insert into emp
(empno,ename,job,mgr,hiredate,sal,deptno)
values(7876,'ADAMS','CLERK',7788,'12-jan-1983',1100,20);
insert into emp
(empno,ename,job,mgr,hiredate,sal,deptno)


To Create CUSTOMER Table:

create table customer
(custid number(6) primary key,
name varchar2(45),
address char(40),
city char(30),
state char(2),
zip number(6),
area number(3),
phone char(9),
repid number(4) references emp,
creditlimit number(9,2),
comments long);


To Add data in CUSTOMER Table:

insert into customer
values(100,'Jocksports','345 Viewridge','Belmont','CA',96711,415,'598-6609',7844,5000,'Very friendly people to work with - sales re likes to be called Mike.');
insert into customer
values(101,'Tkb Sport Shop','490 Boli Rd.','Redwood City','CA',94061,415,'368-1223',7521,10000,'Rep called 5/8 about change in order - contact shipping.');
insert into customer
values(102,'Vollyrite','9722 Hamilton','Burlingame','CA',95133,415,'344-3341',7654,7000,'Company doing heavy promotion begining 10/89. Prepare for large orders during winter.');
insert into customer
values(103,'Just Tennis','Hillview Mall','Burlingame','CA',97544,415,'677-9312',7521,3000,'Contact rep about new line of tennis rackets.');
insert into customer
values(104,'Every Mountain','574 Surry Rd.','Cupertino','CA',93301,408,'996-2323',7499,10000,'Customer with high market share (23%) due to aggressive advertising.');
insert into customer
values(105,'K + T Sports','3476 El Paseo','Santa Clara','CA',91003,408,'376-9966',7844,5000,'Tends to order large amount of merchandise at once. Accounting is considering raising their credit limit. Usually pay on time.');
insert into customer
values(106,'Shape Up','908 Sequoia','Palo Alto','CA',94301,415,'364-9777',7521,6000,'Support intensive. Orders small amounts (<800) of merchandise at a time.');
insert into customer
values(107,'Womens Sports','Valco Village','Sunnyvale','CA',93301,408,'967-4398',7499,10000,'First sporting goods store geared exclusively towards women. Unusual promotional styl willing to take chances towards new products!');
insert into customer
values(108,'North Woods Health And Fitness Supply Center','98 Lone Pine Way','Hibbing','MN',55649,612,'566-9123',7844,8000,null);


To Create ORD Table:

create table ord
(ordid number(4) constraint priky_ord primary key,
orderdate date,
commplan char(1),
custid number(6) constraint ref_cust references customer(custid),
shipdate date,
total number(8,2));


To Add data in ORD Table:

insert into ord
values(610,'07-jan-1987','A',101,'8-jan-1987',101.40);
insert into ord
values(611,'11-jan-1987','B',102,'11-jan-1987',45);
insert into ord
values(612,'15-jan-1987','C',104,'20-jan-1987',5860);
insert into ord
values(601,'1-may-1986','A',106,'30-may-1986',2.40);
insert into ord
values(602,'05-jun-1986','B',102,'20-jun-1986',56);
insert into ord
values(604,'15-jun-1986','A',106,'30-jun-1986',698);
insert into ord
values(605,'14-jul-1986','A',106,'30-jul-1986',8324);
insert into ord
values(606,'14-jul-1986','A',100,'30-jul-1986',3.40);
insert into ord
values(609,'01-aug-1986','B',100,'15-aug-1986',97.50);
insert into ord
values(607,'18-jul-1986','C',104,'18-jul-1986',5.60);
insert into ord
values(608,'25-jul-1986','C',104,'25-jul-1986',35.20);
insert into ord
values(603,'05-jun-1986',null,102,'5-jun-1986',224);
insert into ord
values(620,'12-mar-1987',null,100,'12-mar-1987',4450);
insert into ord
values(613,'01-feb-1987',null,108,'1-feb-1987',6400);
insert into ord
values(614,'01-feb-1987',null,102,'5-feb-1987',23940);
insert into ord
values(616,'03-feb-1987',null,103,'10-feb-1987',764);
insert into ord
values(619,'22-feb-1987',null,104,'4-feb-1987',1260);
insert into ord
values(617,'05-feb-1987',null,105,'3-mar-1987',46370);
insert into ord
values(615,'01-feb-1987',null,107,'6-feb-1987',710);
insert into ord
values(618,'15-feb-1987','A',102,'6-mar-1987',3510.50);
insert into ord
values(621,'15-mar-1987','A',100,'1-jan-1987',730);


To Create PRODUCT Table:

create table product
(prodid number(6) constraint pkey_prod primary key,
descrip char(30));


To Add data in PRODUCT Table:

insert into product
values(100860,'ACE TENNIS RACKET I');
insert into product
values(100861,'ACE TENNIS RACKET II');
insert into product
values(100870,'ACE TENNIS BALLS-3 PACK');
insert into product
values(100871,'ACE TENNIS BALLS-6 PACK');
insert into product
values(100890,'ACE TENNIS NET');
insert into product
values(101860,'SP TENNIS RACKET');
insert into product
values(101863,'SP JUNIOR RACKET');
insert into product
values(102130,'RH: "GUIDE TO TENNIS"');
insert into product
values(200376,'SB ENERGY BAR-6 PACK');
insert into product
values(200380,'SB VITA SNACK-6 PACK');


To Create ITEM Table:

create table item
(ordid number(4) constraint ref_ordid references ord(ordid),
itemid number(4),
prodid number(6) constraint ref_prodid references product(prodid),
actualprice number(8,2),
qty number(8),
itemtot number(8,2),
constraint pkey_2cols primary key(ordid,itemid));


To Add data in ITEM Table:

insert into item
values(612,1,100860,30,100,3000);
insert into item
values(604,3,100860,44,10,440);
insert into item
values(603,2,100860,56,4,224);
insert into item
values(610,1,100860,35,1,35);
insert into item
values(614,1,100860,35,444,15540);
insert into item
values(620,1,100860,35,10,350);
insert into item
values(617,1,100860,35,50,1750);
insert into item
values(618,1,100860,35,23,805);
insert into item
values(611,1,100861,45,1,45);
insert into item
values(604,2,100861,42,2,84);
insert into item
values(612,2,100861,40.5,20,810);
insert into item
values(617,2,100861,45,100,4500);
insert into item
values(616,1,100861,45,10,450);
insert into item
values(615,1,100861,45,4,180);
insert into item
values(618,2,100861,45.11,50,2255.5);
insert into item
values(621,1,100861,45,10,450);
insert into item
values(609,1,100861,35,1,35);
insert into item
values(605,1,100861,45,100,4500);
insert into item
values(602,1,100870,2.8,20,56);
insert into item
values(610,2,100870,2.8,3,8.4);
insert into item
values(614,2,100870,2.8,1000,2800);
insert into item
values(616,2,100870,2.8,50,140);
insert into item
values(615,2,100870,2.8,100,280);
insert into item
values(617,3,100870,2.8,500,1400);
insert into item
values(609,2,100870,2.5,5,12.5);
insert into item
values(618,3,100870,45,10,450);
insert into item
values(621,2,100870,2.8,100,280);
insert into item
values(605,2,100870,2.8,500,1400);
insert into item
values(613,1,100871,5.6,100,560);
insert into item
values(614,3,100871,5.6,1000,5600);
insert into item
values(607,1,100871,5.6,1,5.6);
insert into item
values(617,4,100871,5.6,500,2800);
insert into item
values(615,3,100871,5,50,250);
insert into item
values(608,2,100871,5.6,2,11.2);
insert into item
values(612,4,100871,5.5,100,550);
insert into item
values(619,4,100871,5.6,50,280);
insert into item
values(610,3,100890,58,1,58);
insert into item
values(604,1,100890,58,3,174);
insert into item
values(616,3,100890,58,2,116);
insert into item
values(617,5,100890,58,500,29000);
insert into item
values(609,3,100890,50,1,50);
insert into item
values(605,3,100890,58,5,290);
insert into item
values(613,2,101860,24,200,4800);
insert into item
values(617,6,101860,24,100,2400);
insert into item
values(608,1,101860,24,1,24);
insert into item
values(605,4,101860,24,50,1200);
insert into item
values(612,3,101863,10,150,1500);
insert into item
values(617,7,101863,12.5,200,2500);
insert into item
values(605,5,101863,9,100,900);
insert into item
values(620,3,102130,3.4,500,1700);
insert into item
values(619,3,102130,3.4,100,340);
insert into item
values(616,4,102130,3.4,10,34);
insert into item
values(617,8,102130,3.4,100,340);
insert into item
values(606,1,102130,3.4,1,3.4);
insert into item
values(605,6,102130,3.4,10,34);
insert into item
values(601,1,200376,2.4,1,2.4);
insert into item
values(613,4,200376,2.2,200,440);
insert into item
values(620,2,200376,2.4,1000,2400);
insert into item
values(616,5,200376,2.4,10,24);
insert into item
values(619,2,200376,2.4,100,240);
insert into item
values(617,9,200376,2.4,200,480);
insert into item
values(613,3,200380,4,150,600);
insert into item
values(619,1,200380,4,100,400);
insert into item
values(617,10,200380,4,300,1200);


નોંધ :- પીજીડીસીએ ના વિદ્યાર્થીઓ ઘરે સમજી શકે અને પ્રેક્ટિસ કરી શકે એ હેતુ માટે જ આ માહિતિ મુકવામાં આવી છે. તેના અયોગ્ય ઉપયોગ બદલ લેખક કે વેબ માસ્ટર ની કોઇપણ પ્રકારની જવાબદારી રહેશે નહિ.

Make a free website with Yola