Thread Rating:
  • 1 Votes - 4 Average
  • 1
  • 2
  • 3
  • 4
  • 5
DBMS lab program 5th sem cse/ise
Author Message
Sandeep Offline
Sleepy Admin
*******

Posts: 660
Joined: Sep 2012
REPUTATION 14
Post: #1
DBMS lab program 5th sem cse/ise
1.INSURANCE DATABASE

Table creation

(i)Sql> create table person(driverid varchar2(8) primary key, name varchar2(10), address varchar2(14));

Sql>create table car(regno varchar2(4) primary key, model varchar2(6), year number(4));

Sql>create table accident(repno number(4) primary key, accddate date, location varchar2(10));

Sql>create table owns(driverid varchar2(8), regno varchar2(4), foreign key (driverid) references person, foreign key (regno) references car);

Sql>create table participated(driverid varchar2(8), regno varchar2(4), repno number(4), damage amount number (6), foreign key (driverid) references person, foreign key (regno) references car, foreign key (repno) references accident);

[b]Insert Data[/b]

Sql>insert into person values(‘&driverid’,’&name’,’&address’);
Enter value for driveridBig Grin1
Enter value for name:sunay
Enter value for address:dahod

Sql>insert into car values(‘&regno,’&model,’&year);
Enter values for regno:2345
Enter values for model:m4
Enter values for year:2008

Sql>insert into accident values(‘&repno’,’&accddate’,’&location’);
Enter values for repno:1102
Enter values for accddate:01-feb-2009
Enter values for location:banglore

Sql>insert into owns values(‘&driverid’,’&regno’);
Enter values for driveridBig Grin1
Enter values for regno:2345

Sql>insert into participated values (‘&driverid,’&regno’,’&repno’,’&damageamount’);
Enter values for driveridBig Grin1
Enter values for regno:2345
Enter values for repno:1102
Enter values for damageamount:5000

No of people own the car
Sql>select count (*) as “no of person”
From owns o, accident a, participated p
Where to char (accddate,’yy’) = ’09’ and
o.driverid = p.driverid and p.repno = a.repno;

No of accidents
Sql>select count (*) as “no of accidents”
From participated p, car c
Where p.regno = c.regno and c.model = ’i10’;
10-05-2012 09:29 PM
WWW FIND
Sandeep Offline
Sleepy Admin
*******

Posts: 660
Joined: Sep 2012
REPUTATION 14
Post: #2
RE: DBMS lab program 5th sem cse/ise
2.ORDER PROCESSING DATABASE

Table Creation

Sql>create table customer(custno number(4) primary key, name varchar2(8), city varchar2(8));

Sql>create table ordertab(ordno number(5) primary key,orddate date, custno number(4), orderamount number(6), foreign key (custno) references customer);

Sql>create table item(itemno number(5) primary key, unitprice number(6));

Sql> create table orderitem(ordno number(5) , itemno number(5), quantity number(5), foreign key (ordno) references ordertab, foreign key (itemno) references item);

Sql>create table warehouse(warehouseno number(8) primary key, city varchar2(8));

Sql>create table shipmenttab(ordno number(5), warehouseno number(8), shipdate date, foreign key (ordno) references ordertab, foreign key (warehouseno) references warehouse);

INsert data

Sql>insert into customer values(‘&custno’,’&name’,’&city’);
Enter value for custno: 12
Enter value for name: david
Enter value for city: bijapur

Sql> insert into ordertab values(‘&ordno’, ’&orddate’, ’&custno’, ’&orderamount’);
Enter value for ordno: 1011
Enter value for orddate:01-feb-2009
Enter value for custno:12
Enter value for orderamount:2000

Sql> insert into item values(‘&itemno’,’&unitprice’);
Enter value for itemno: 1001
Enter value for unitprice: 2000

Sql> insert into orderitem values(‘&ordno’,’&itemno’,’&quantity’);
Enter value for ordno: 1011
Enter value for itemno: 1001
Enter value for quantity: 25

Sql> insert into shipmenttab values(‘&ordno’,’&warehouseno’,’&shipdate’);
Enter value for ordno: 1011
Enter value for warehouseno: 1234
Enter value for shipdate: 05-feb09

3) Sql>select c.name as ”customer name”,
Count (o.ordno) as ”orders placed” ,
Avg (o.orderamount) as “average amount”
From customer c, ordertab o
Where c.custno = o.custno
Group by c.name;

4) Sql>select ordno
From ordertab o
Where not exists
(select *
From shipmenttab S1
Where (S1.warehouseno in (select warehouseno
From warehouse
Where city = ‘dahod’))
And
Not exists (select *
From shipmenttab S2
Where S2.ordno = o.ordno
And
S1.warehouseno = S1.warehouseno));


5) Sql>update orderitem set itemno = NULL
where itemno = 1005;


Sql>delete from item where itemno = 1005;
10-05-2012 09:55 PM
WWW FIND
Sandeep Offline
Sleepy Admin
*******

Posts: 660
Joined: Sep 2012
REPUTATION 14
Post: #3
RE: DBMS lab program 5th sem cse/ise
3.STUDENT ENROLLMENT DATABASE

Table creation

Sql>create table student(regno vrchar2(8) primary key, name varchar2(8), major varchar2(8), bdate date);

Sql>create table coursetab(courseno number(4) primary key,cname varchar2(8) ,dept varchar2(8));

Sql> create table enroll (regno varchr2(8) , courseno number(4), sem number(4),marks number(4), foreign key (regno) references student, foreign key (courseno) references coursetab);

Sql>create table text(bookisbn number(8) primary key, booktitle varchar2(8), publisher varchar2(8),author varchar2(8));

Sql>create table bookadopt(courseno number(4), sem number(4), bookisbn number(8), foreign key (courseno) references coursetab, foreign key (bookisbn) references text);

Insert data

Sql>insert into student values(‘&regno’,’&name’,’&major’,’&bdate’);
Enter value for regno: 1011
Enter value for name: nandy
Enter value for major: Cs
Enter value for bdate: 12-aug-88

Sql>insert into coursetab values(‘&courseno’,’&cname’,’&dept’);
Enter value for courseno: 1
Enter value for name: be
Enter value for city: cse

Sql>insert into text values (‘&bookisbn’,’&booktitle’,’&publisher’,’&author’);
Enter value for bookisbn: 89012
Enter value for booktitle: dbms
Enter value for publisher: mcgraw
Enter value for author: raghu

Sql>insert into bookadopt values(‘&courseno’,’&sem’,’&bookisbn’);
Enter value for courseno: 1
Enter value for sem: 5
Enter value for bookisbn: 89012

Sql>insert into text values (‘&bookisbn’,’&booktitle’,’&publisher’,’&author’);
Enter value for bookisbn: 34567
Enter value for booktitle: c
Enter value for publisher: phi
Enter value for author: henry

Sql>insert into bookadopt values(‘&courseno’,’&sem’,’&bookisbn’);
Enter value for courseno: 1
Enter value for sem: 2
Enter value for bookisbn: 34567

4)Sql>Select c.name, t.booktitle, t.bookisbn
From text t, bookadopt b, coursetab c
Where t.bookisbn=b.bookisbn and b.courseno in (select b1.courseno
From bookadopt b1
Where b1.courseno in (select c.courseno from coursetab
Where dept=’ISE’) gropu by b1.courseno
Having count (b1.courseno)>2);

5)Sql>Select distinct (dept)
From coursetab c
Where not exists ((select bookisbn
From text
Where publisher=’pearson’)
Minus
(Select bookisbn
From bookadopt b1
Where b1.courseno =c.courseno);
10-05-2012 10:01 PM
WWW FIND
Sandeep Offline
Sleepy Admin
*******

Posts: 660
Joined: Sep 2012
REPUTATION 14
Post: #4
RE: DBMS lab program 5th sem cse/ise
4. BOOKS DEALER DATABASE
create table
Sql>create table author (authored number(8) primary key, name varchar2(8), city varchar2(8), country varchar2(8));

Sql>create table publisher (publisherid number(8) primary key, name varchar2(8) , city varchar2(8),country varchar2(8));

Sql> create table category (categoryid number(8) primary key, description varchar2(8));

Sql>create table catalog (bookid number(8) primary key, authorid number(8), publisherid number(8),categoryid number(8).year number(5),price number(8),foreign key (authorid) references author, foreign key (publisherid) references publisher,foreign key (categoryid) references category);

Sql>create table orderdetails(orderno number(8) primary key, bookid number(8), quantity number(8), foreign key (bookid) references catalog);

INsert data

Sql>insert into author values(‘&authorid’,’&name’,’&city’,’&country’);
Enter value for authorid: 1
Enter value for name: a1
Enter value for city: c1
Enter value for country: con1

Sql>insert into publisher values (‘&publisherid’,’&name’,’&city’,’&country’);
Enter value for publisherid: 11
Enter value for name: p1
Enter value for city: c1
Enter value for country: con1

Sql>insert into category values(‘&categoryid’,’&description’);
Enter value for categoryid: 111
Enter value for description: d1

Sql>insert into catalog values (‘&bookid’,’&title’,’&autherid’,’&publisherid’,’&catelogid’,’&year’, ’&price’);

Sql>insert into orderdetails values(‘&orderno’,’&bookid’,’&quantity’);
Enter value for orderno: 11111
Enter value for bookid: 1111
Enter value for quantity: 20


3)Sql>select * from author a
Where a.authorid in(select authored
From catalog c
Where year > 2000 and price > (select avg(price) from catalog)
Group by c.authorid
Having count (c.authorid)>=2);

4) Sql>select name
From author
Where authorid in (select authorid
From catalog
Where bookid in (select bookid
From orderdetails
Group by bookid
Having sum (quantity)>=all(select max(sum(quantity))
From orderdetails
Group by bookid )));

5) Sql>update catalog
Set price=price+price*0.1
Where publisherid = 22;
(This post was last modified: 10-05-2012 10:09 PM by Sandeep.)
10-05-2012 10:08 PM
WWW FIND
Sandeep Offline
Sleepy Admin
*******

Posts: 660
Joined: Sep 2012
REPUTATION 14
Post: #5
RE: DBMS lab program 5th sem cse/ise
5.BANKING ENTERPRISE DATABASE

Sql>create table branch (branchname varchar2(10) primary key, branchcity varchar2(8), assets number (8,2));

Sql>create table acount (accno number(10) primary key, branchname varchar2(10) , balance number(8,2), foreign key (branchname) references branch);

Sql> create table customer (customername varchar2(20) primary key, customerstreet varchar2(20), customercity varchar2(20));

Sql>create table depositor (customername varchar2(20), accno number(10), foreign key (customername) references customer, foreign key (accno) references acount);

Sql>create table loan (loannumber number(10) primary key, branchname varchar2(10), amount number(8,2), foreign key (branchname) references branch);

Sql>create table borrower (customername varchar2(20), loannumber number(10), foreign key (customername) references customer, foreign key (loannumber) references loan);

INsert data

Sql>insert into branch values(‘&branchname’,’&branchcity’,’&assets’);
Enter value for branchname: b1
Enter value for branchcity: c1
Enter value for assets: 100.1

Sql>insert into acount values (‘&accno’,’&branchname’,’&balance’);
Enter value for accno: 11
Enter value for branchname: b1
Enter value for balance: 10

Sql>insert into customer values (‘&customername’,’&customerstreet’,’&customercity’);
Enter value for customername: cust1
Enter value for customerstreet: s1
Enter value for customercity: c1

Sql>insert into depositor values(‘&customername’,’&accno’);
Enter value for customername: cust1
Enter value for accno: 11

Sql>insert into loan values (‘&loannumber’,’&branchname’,’&amount’);
Enter value for loannumber: 1
Enter value for branchname: b1
Enter value for amount: 1000

Sql>insert into borrower values (‘&customername‘,‘&loannumber’);
Enter value for customername: cust1
Enter value for loannumber: 1

3)Sql>Select customername, count (*)
From depositor d, acount a
Where branchname=’b1’
and
d.accno =a.accno
Group by customername
Having count (*)>1;

4)Sql>select distinct (customername)
From depositor
Where not rxists
(Select *
From acount a1
Where (branch name is (select branchname
From branch
Where branch city=’c1’)) and
Not exits (select *
From account a2
Where a2.accno=d.accno and a2.branchname=a1.branchname));

5) Sql>Delete from depositor
Where accno in (Select From account Where branchname in
(Select branchname From branch Where branchcity=’c1’));

Sql>Delete from acount
Where branchname in
(Select branchname From branch Where branchcity=’c1’));
10-05-2012 10:14 PM
WWW FIND
 


Possibly Related Threads...
Thread: Author Replies: Views: Last Post
  Download DBMS notes for 5th sem Sandeep 0 3,908 08-21-2013 11:00 PM
Last Post: Sandeep
  C program to convert String from Lower Case to Upper case Sandeep 0 561 02-08-2013 05:01 PM
Last Post: Sandeep
  Program to sort the Linked list Sandeep 0 466 01-13-2013 10:45 PM
Last Post: Sandeep
  Program to find the frequency of the given term in sentence Sandeep 0 476 12-14-2012 11:43 PM
Last Post: Sandeep
  M.Tech DBMS 1st lab program Sandeep 0 2,013 10-13-2012 11:28 PM
Last Post: Sandeep

Forum Jump:


User(s) browsing this thread: 1 Guest(s)