DBMS Manual - Odt
DBMS Manual - Odt
DBMS Manual - Odt
A. LABORATORY OVERVIEW
Degree:
BE
Semester:
5
Laboratory Title:
Database Applications Laboratory
SEE Marks:
50 Marks
Total Contact Hours:
42 Hrs
Lab Manual Author:
Shruthi H R
Checked By:
Programme:
CSE
Academic Year: 2015-16
Laboratory Code: 10CSL57
Duration of SEE: 180 Minutes
IA Marks:
25
Sign
Dt :
Sign
Dt :
B. DESCRIPTION
1. PREREQUISITES
Computer concepts and C Programming languages(10PCD13)
Data structure with C(10CS35)
2. BASE COURE
Database management system(10CS54)
3. LEARNING OBJECTIVES(LO)
During the course the student should learn
To understand the fundamental concepts of Data Base Design, Data Models, different Data
Base Languages(SQL/ORACLE)
To analyze and implement Database design methodology,OLTP,OLAP,DB connectivity
To query the Database using SQL commands
To generate reports
4. LEARNING OUTCOMES
At the end of the course the student should be able to
Create Database with different kinds of integrity constraints and use the SQL commands such
as DDL, DML,DCL to access the data using SQL commands.
Use Database security and authorization in order to access database for the different kinds of
the user
Access and manipulate data using SQL commands
5. RESOURCE REQUIRED
HARDWARE REQUIREMENTS
Pentium 4 or above
Memory : 1 GB ram
Speed: 1GH
SOFTWARE REQUIRED
Ubuntu , Linux
Oracle
6. RELEVANCE OF THE COURSE
Web programming lab(10CSL78)
7. GENERAL INSTRUCTIONS
Students are required to carry their observation / programs book with
completed exercises while entering the lab.
Lab can be used in free time / lunch hours by the students who need to use
the systems should take prior permission from the lab in-charge.
Lab records need to be submitted on or before date of submission.
Students are not supposed to use cds and pen drives.
College Database
Airline Database
Student Database
Bank Database
Date Planned
B1:
B2:
B1:
B2:
B1:
B2:
B1:
B2:
B1:
B2:
Date Conducted
B1:
B2:
B1:
B2:
B1:
B2:
B1:
B2:
B1:
B2:
C. EVALUATION SCHEME
Record writing: 10 Marks - 10 marks to each experiment to be awarded and average of all
experiments is taken.
Laboratory Activities: 5 Marks
Laboratory IA tests: 10 Marks IAs should follow in the succeeding week of theory IA.
Minimum 2 IAs are mandatory. For final Lab IA, average of best 2 of total number of IAs.
Internal Assessment (IAMarks) = 1 + 2 + 3 = 25 Marks
SEE : 50 Marks
D. EXPERIMENTS
EXPERIMENT NO 1-TITLE: COLLEGE DATABASE
LEARNING OBJECTIVES:
Application of DDL, DML, DCL.
Implementation of normalization and ER Diagrams.
AIM : Consider the following relations:
Student(snum: integer, sname: string, major: string, level: string, age: integer)
Class (name: string,meets at: string, room: string, fid: integer)
Enrolled(snum: integer, cname: string)
Faculty(fid: integer, fname: string, deptid: integer)
The meaning of these relations is straightforward; for example, Enrolled has one record per student
-classpair such that the student is enrolled in the class. Level is a two character code with 4
different values (example: Junior: JR etc)
Write the following queries in SQL. No duplicates should be printed in any of th e answers.
i. Find the names of all juniors (level = JR) who are enrolled in a class taught by Prof. Harshith
ii. Find the names of all classes that either meet in room R128 or have five or more Students
enrolled.
iii. Find the names of all students who are enrolled in two classes that meet at the same time.
iv. Find the names of faculty members who teach in every roomin which some class is taught.
v. Find the names of faculty members for whom the combined enrollment of the courses that they
teach is less than five.
THEORY:
RDBMS stands for Relational Database Management System. RDBMS is the basis for
SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and
Microsoft Access.A Relational database management system (RDBMS) is a database management
system (DBMS) that is based on the relational model as introduced by E. F. Codd.
Table: The data in RDBMS is stored in database objects called tables. The table is a collection of
related data entries and it consists of columns and rows.
Field : A field is a column in a table that is designed to maintain specific information about every
record in the table.
Row/Record : A record, also called a row of data, is each individual entry that exists in a table.
PROCEDURE:
STEP 1: Start
STEP 2: Create the table with its essential attributes.
STEP 3: Insert the values for the created tables.
STEP 4: View the description and the values of the table.
STEP 5: Execute different SQL Commands and extract information from the
table.
STEP 6: Stop
PROGRAMME:
Create the above tables by properly specifying the primary keys and the foreign keys.
CREATING THE TABLES:
SQL>create table student
(
snum int,
sname varchar(20),
major varchar(15),
slevel varchar(15),
age int,
primary key(snum)
);
SQL>create table class
(
name varchar(15),
meetat varchar(8),
room varchar(10),
fid int,
primary key(name),
foreign key(fid) references faculty(fid) on delete cascade
);
SQL>create table enrolled
(
snum int,
cname varchar(15),
primary key(snum,cname),
foreign key(snum) references student(snum),
foreign key(cname) references class(name)
);
SQL>create table faculty
(
fid int,
fname varchar(20),
deptid int,
primary key (fid)
);
Enter at least five tuples for each relation.
INSERTING VALUES INTO THE RELATIONS/TABLES:
SQL>insert into student values('&snum','&sname','&major','&slevel','&age');
SQL>insert into faculty values('&fid','&fname','&deptid');
SQL>insert into class values('&name','&meetat','&room','&fid');
SQL>insert into enrolled values('&snum','&cname');
SQL QUERIES:
QUERY 01:Find the names of all juniors(level=jr) who are enrolled in a class taught by prof.
Harshith.
SQL> select distinct sname from
student s, enrolled e,class c,faculty f
where s.snum=e.snum and cname=name and c.fid=f.fid and slevel='jr'
and fname='Harshith'
QUERY 02:Find the names of all classes that either meet in room R128 or have 5 or more
students enrolled.
SQL> select name from
class c
where room='R128'or
(select count(*) from enrolled where cname=c.name)>=5
QUERY 03:Find the names of all students who are enrolled in two classes that meet at the
same time.
SQL> select distinct sname from
student s, enrolled e,class c
where s.snum=e.snum and cname=name and
(select count(*) from student x,enrolled y,class z
where x.snum=y.snum and cname=name and x.snum=s.snum and meetat=c.meetat)=2;
QUERY 04:Find the names of the faculty members for who teach in every room in which
some class is taught.
SQL> select fname
from faculty f
where not exists
( (select room from class)
minus
(select room
from class c
where c.fid=f.fid));
QUERY 05:Find the names of the faculty for whom the combined enrollment of the courses
that they teach is less than five.
SQL> select fname from
faculty f
where fid in
( select fid
from enrolled,class
where cname=name
group by fid
having count(*)<5);
RESULTS &ANALYSIS:
SQL>desc student;
Name
Null?
Type
----------------------------------------- ----------------------------------SNUM
NOT NULL
NUMBER(5)
SNAME
MAJOR
SLEVEL
AGE
VARCHAR2(20)
VARCHAR2(15)
VARCHAR2(15)
SQL>desc faculty;
Name
Null?
----------------------------------------- -------FID
FNAME
DEPTID
Type
---------------------------NOT NULL
NUMBER(5)
VARCHAR2(20)
NUMBER(5)
SQL>desc class;
Name
Null?
Type
----------------------------------------- -------NAME
NOT NULL
MEETAT
ROOM
FID
SQL>desc enrolled;
Name
Null?
Type
----------------------------------------- -------SNUM
NOT NULL
CNAME
NOT NULL
---------------------------VARCHAR2(15)
VARCHAR2(8)
VARCHAR2(10)
---------------------------NUMBER(5)
VARCHAR2(15)
SNAME
-----------Raja
Ravi
Kishore
Kiran
Kumar
MAJOR
-----------CS
CS
CS
IS
IS
SLEVEL
------------jr
jr
sr
jr
sr
FNAME
-----------Vinod
Harshith
Shubha
Jeevan
Sunil
DEPTID
----------1
2
2
1
1
MEETAT
-------fn
an
fn
fn
an
an
an
CNMAE
----------
ROOM
--------128
127
128
126
127
126
127
FID
-------122
121
123
124
125
122
122
AGE
--------19
19
21
19
21
111
111
111
111
222
222
222
333
444
555
555
ADA
DBMS
DS
SS
DBMS
DS
SS
SS
SS
ADA
SS
QUERY 02:
NAME
--------------SS
QUERY 03:
SNAME
-------------------Ravi
QUERY 04:
FNAME
-------------------Harshith
QUERY 05:
FNAME
-------------------Harshith
Shubha
Jeevan
OUTCOMES &CONCLUSIONS:
At the end of the program the student should be able to,
Understand the concepts of aggregate, group by functions.
APPLICATION AREAS:
Database designing and implementation.
REMARKS:
AIM:
The following relations keep track of airline flight information:
Flights(no: integer, from: string, to: string, distance: integer, Departs: time, arrives: time, price:
real)
Aircraft(aid: integer, aname: string, cruisingrange: integer)
Certified(eid:integer,aid:integer)
Employees(eid: integer, ename: string, salary: integer)
Note that the Employees relation describes pilots and other kinds of employees as well; Every pilot
is certified for some aircraft, and only pilots are certified to fly. Write each of the following queries
in SQL.
i. Find the names of aircraft such that all pilots certified to operate them have salaries more than
Rs.80,000.
ii. For each pilot who is certified for more than three aircraft s, find the eid and the maximum
cruisingrange of the aircraft for which she or he is certified.
iii. Find the names of pilots whose salary is less than the price of the cheapest route from Bengaluru
to Frankfurt.
iv. For all aircraft with cruisingrange over 1000 Kms, .find the name of the aircraft and the average
salary of all pilots certified for this aircraft.
v. Find the names of pilots certified for some Boeing aircraft.
vi. Find the aids of all aircraft that can be used on routes from Bengaluru to New Delhi.
THEORY:
MySQL:
MySQL is an open source SQL database, which is developed by Swedish company MySQL AB.
MySQL is pronounced "my ess-que-ell,"in contrast with SQL, pronounced "sequel."
MySQL is supporting many different platforms including Microsoft Windows, the major Linux
distributions, UNIX, and Mac OS X.
MySQL has free and paid versions, depending on its usage (non-commercial/commercial) and
features. MySQL comes with a very fast, multi-threaded, multi-user, and robust SQL database
server.
SQL data type is an attribute that specifies type of data of any object. Each column, variable and
expression has related data type in SQL.
You would use these data types while creating your tables. You would choose a particular data type
for a table column based on your requirement.
Data type
bigint
int
smallint
tinyint
bit
decimal
numeric
From
To
-9,223,372,036,854,775,808 9,223,372,036,854,775,80
7
-2,147,483,648
2,147,483,647
-2,147,483,648
32,767
0
255
0
1
-10^38 +1
10^38 -1
-10^38 +1
10^38 -1
PROCEDURE:
STEP 1: Start
STEP 2: Create the table with its essential attributes.
primary key(eid,aid),
foreign key(eid) references employees(eid),
foreign key(aid) references aircraft(aid)
);
Enter at least five tuples for each relation.
INSERTING VALUES INTO THE TABLES/RELATIONS:
SQL>Insert into flight values(&fNo, &from1, &to1,
&arrives,&price);
SQL>Insert into aircraft values (&aid,&aname,&crange);
SQL>Insert into employees values (&eid,&ename,&salary);
SQL>Insert into certified values(&eid,&aid);
&distance,
&departs,
SQL QUERIES:
QUERY 01:Find the names of aircraft such that all pilots certified to operate them have
salaries more than Rs.80,000.
SQL>select distinct aname from
employees e,certified c,aircraft a
where e.eid=c.eid and c.aid=a.aid
group by aname
having min(salary)>80000
QUERY 02:For each pilot who is certified for more than three aircrafts, find the eid and the
maximum cruisingrange of the aircraft for which she or he is certified.
SQL>select c.eid,max(crange)
from certified c,aircraft a
where c.aid=a.aid
group by c.eid
having count(*)>3 ;
QUERY 03:Find the names of pilots whose salary is less than the price of the cheapest route
from Bangaluru to Frankfurt.
SQL>select ename
from employees
where salary<(select min(price) from flights where
from1='Bangalore'and to1='Frankfurt');
QUERY 04:For all aircraft with cruisingrange over 1000kms, find the name of the aircraft
and the average salary of all pilots certified for this aircraft.
SQL>select a.aname,avg(salary)
from aircraft a,certified c,employees e
Null?
-------NOT NULL
NUMBER(10,2)
SQL>desc aircraft;
Name
Null?
-------------------------------- -------AID
NOT NULL
ANAME
CRANGE
SQL>desc employees;
Name
-------------------------------EID
Null?
-------NOT NULL
ENAME
Type
---------------------------NUMBER(10)
VARCHAR2(15)
NUMBER(10)
Type
---------------------------NUMBER(10)
VARCHAR2(15)
SALARY
NUMBER(10)
SQL>desc certified
Name
Null?
-------------------------------- -------EID
NOT NULL
AID
NOT NULL
SQL >SELECT * from AIRCRAFT ;
AID
------
Type
---------------------------NUMBER(10)
VARCHAR2(15)
VARCHAR2(15)
NUMBER(5)
DATE
DATE
ANAME
------------
CRANGE
------------
Type
---------------------------NUMBER(10)
NUMBER(10)
685
686
687
688
689
690
Boeing 15
Boeing 10
Boeing 20
Indian air
Go air
Avenger
1000
2000
3000
4000
5000
200
ENAME
----------asha
arun
anand
ramya
karthik
SALARY
-------------90000
85000
30000
2000
1500
FROM1
---------Bangalore
Bangalore
Bangalore
Bangalore
Bangalore
SQL >SELECT *
EID
-----101
101
101
101
101
102
103
103
104
105
TO1
-----Frankfurt
Frankfurt
Delhi
Delhi
Frankfurt
DISTANCE
--------------1500
200
400
350
500
from CERTIFIED ;
AID
-------685
686
687
688
689
685
686
687
686
689
QUERY 02:
EID MAX(CRANGE)
----- ----------101
5000
QUERY 03:
ENAME
--------------ramya
karthik
QUERY 04:
DEPARTS
-------------12-aug-15 10:00
13-oct-15 09:00
12-sep-15 02:00
13-feb-15 06:00
12-aug-15 10:00
ARRIVES
------------12-aug-15 12:00
13-oct-15 12:00
12-sep-15 10:00
13-feb-15 11:00
12-aug-15 12:00
PRICE
---------6000
2500
3000
3500
4000
ANAME
AVG(SALARY)
--------------- ----------Go air
45750
Boeing 10
60000
Indian air
46000
Boeing 20
60000
QUERY 05:
ENAME
--------------anand
arun
asha
QUERY 06:
AID ANAME
------
----------
685
686
687
688
689
Boeing 15
Boeing 10
Boeing 20
Indian air
Go air
OUTCOMES &CONCLUSIONS:
At the end of the program the student should be able to,
Understand the concepts of aggregate, group by functions and constraints on the tables.
APPLICATION AREAS:
Database designing and implementation.
REMARKS:
EXPERIMENT NO 3-TITLE: STUDENT DATABASE
LEARNING OBJECTIVES:
Application of DDL, DML, DCL.
Generation of reports.
AIM:
Consider the following database of student enrollment in courses &books adopted for each course.
STUDENT(regno: string, name: string,major: string, bdate:date)
COURSE(course #:int, cname:string, dept:string)
ENROLL ( regno:string, course#:int, sem:int, marks:int)
BOOK _ ADOPTION(course# :int, sem:int, book-ISBN:int)
TEXT (book-ISBN:int, book-title:string, publisher:string, author:string)
.Create the above tables by properly specifying the primary keys and the foreign keys.
ii. Enter at least five tuples for each relation.
iii. Demonstrate how you add a new text book to the database and make this book be adopted by
some department.
iv. Produce a list of text books (include Course #, Book -ISBN, Book-title) in the alphabetical order
for courses offered by the CS department that use more than two books.
v. List any department that has all its adopted books published by a specific publisher.
vi. Generate suitable reports.
vii. Create suitable front end for querying and displaying the results.
THEORY:
SQL Create table
Creating a basic table involves naming the table and defining its columns and each column's data
type.
The SQL CREATE TABLE statement is used to create a new table.
Syntax:
Basic syntax of CREATE TABLE statement is as follows:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY(one ormore columns ));
The SQL DROP TABLE statement is used to remove a table definition and all data, indexes,
triggers, constraints, and permission specifications for that table.
NOTE:You have to be careful while using this command because once a table is deleted then all the
information available in the table would also be lost forever.
Syntax:
Basic syntax of DROP TABLE statement is as follows:
DROP TABLE table_name;
PROCEDURE:
STEP 1: Start
STEP 2: Create the table with its essential attributes.
STEP 3: Insert the values for the created tables.
STEP 4: View the description and the values of the table.
STEP 5: Execute different SQL Commands and extract information from the
table.
STEP 6: Generate the reports.
STEP 7: Stop
PROGRAMME:
Create the above tables by properly specifying the primary keys and the foreign keys.
CREATING THE TABLES:
SQL>create table student
(
regno varchar(15),
name varchar(15),
major varchar(15),
bdate date,
primary key(regno)
);
SQL>create table course
(
courseno int,
cname varchar(15),
dept varchar(15),
primary key(courseno)
);
SQL>create table text
(
bookisbn int,
booktitle varchar(20),
publisher varchar(15),
author varchar(20),
primary key(bookisbn)
);
SQL>create table enroll
(
regno varchar(15),
courseno int,
sem int,
marks number(10),
primary key(regno,courseno),
foreign key(regno) references student(regno),
foreign key(courseno) references course(courseno)
);
SQL>create table book_adoption
(
courseno int,
sem int,
bookisbn int,
primary key(courseno,bookisbn),
foreign key(bookisbn) references text(bookisbn)
);
Enter at least five tuples for each relation.
INSERTING VALUES INTO THE TABLES/RELATIONS:
SQL>insert into student values('®no','&name','&major','&bdate');
SQL>insert into course values(&courseno,'&cname','&dept');
SQL>insert into enroll values('®no',&courseno,&sem,&marks);
SQL>insert into text values(&bookisbn,'&booktitle','&publisher','&author');
SQL>insert into bookadoption values(&couseno,&sem,&bookisbn);
Demonstrate how you add a new text book to the database and make this book be adopted by some
department.
SQL>insert into text1 values(600700,'Storage Networks','syham series','ulf troppens');
SQL>insert into bookadoption1 values(100,7,600700);
SQL QUERIES:
QUERY 01:Produce a list of text books (include Course #, Book-ISBN, Book-title) in the
alphabetical order for courses offered by the CS department that use more than two books.
SQL> select c.courseno,t.bookisbn,t.booktitle
from course c, book_adoption b,text t
where c.courseno=b.courseno and b.bookisbn=t.bookisbn and c.dept='cs'and
(select count(*)
from book_adoption
where courseno=c.courseno)>2
order by t.booktitle
QUERY 02:List any department that has all its adopted books published by a specific
publisher.
SQL> select distinct c.dept,t.publisher
from course c,book_adoption b,text t
where c.courseno=b.courseno and b.bookisbn=t.bookisbn
and
not exists (
(select y.bookisbn
from course x
where x.courseno=y.courseno and x.dept=c.dept)
minus
(select bookisbn
from text
where publisher=t.publisher)
);
QUERY 03:Generate suitable reports.
SQL> ED BOOKS
ttitle 'BOOKS ADOPTED BY PARTICULAR DEPARTMENT'
btitle 'BOOK DATABASE'
column BOOKISBN heading 'BOOK_ISBN
column BOOKTITAL heading 'BOOK_TITAL
column CNAME heading 'COURSE_NAME'
column DEPT heading 'DEPARTMENT_NAME'
break on dept skip 2
compute count of bookisbn on dept
set linesize 100
set pagesize 40
select t.bookisbn,t.booktitle,c.cname,c.dept from course c, book_adoption a, text t where
c.courseno=a.courseno and a.bookisbn=t.bookisbn order by dept;
btitle off
ttitle off
clear breaks
clear columns
RESULTS &ANALYSIS:
SQL>desc student;
Name
-----------------------
Null?
Type
---------- ----------------------------
REGNO
NAME
MAJOR
BDATE
NOT NULL
VARCHAR2(15)
VARCHAR2(15)
VARCHAR2(15)
DATE
SQL>desc course;
Name
Null?
Type
----------------------------------------- -------- ---------------------------COURSENO
NOT NULL
NUMBER(10)
CNAME
VARCHAR2(15)
DEPT
VARCHAR2(15)
SQL>desc text;
Name
--------------------------BOOKISBN
BOOKTITLE
PUBLISHER
AUTHOR
Null?
-------NOT NULL
Type
---------------------------NUMBER(10)
VARCHAR2(20)
VARCHAR2(15)
VARCHAR2(20)
SQL>desc enroll1;
Name
Null?
------------------------------- -------REGNO
NOT NULL
COURSENO
NOT NULL
SEM
MARKS
SQL>desc book_adoption;
Type
Name
Null?
----------------------------------COURSENO
NOT NULL
SEM
BOOKISBN
NOT NULL
Type
---------------------------NUMBER(10)
NUMBER(2)
NUMBER(10)
---------------------------VARCHAR2(15)
NUMBER(10)
NUMBER(2)
NUMBER(10)
NAME
-----------
MAJOR
-----------------
BDATE
------------
CS
IS
EE
ME
EC
12/01/90
01/07/89
22/09/91
30/12/92
11/03/90
Aarthi
Bala
Chitra
Deepak
Eniya
CNAME
-----------DBMS
SS
LD
CN-1
DEPT
------------CS
IS
EC
CS
500
PCD
IS
COURSENO
-----------100
200
300
400
500
SEM
--------1
2
3
4
5
MARKS
------------95
85
80
75
98
SEM
-------1
2
3
4
5
7
BOOKISBN
----------------100200
200300
300400
400500
500600
600700
BOOKTITLE
-------------------DBMS
System software
Logic design
Computer networks
C fundamentals
Database system
PUBLISHER
----------------phi
pearson
tata mcrawhill
phi
tata mcrawhill
syham series
AUTHOR
-------------tenanbaum
elmasri
coreman
balagurusamy
william stallings
ulf troppens
QUERY 03:
SQL>start books
Wed Aug 08
page 1
BOOKS ADOPTED BY PARTICULAR DEPARTMENT
BOOK_ISBN
BOOK_TITLE
COURSE_NAME
-----------------------------------------------300400
Logic design
LD
---------********************
1
count
DEPARTMENT_NAME
-------------------EC
100200
400500
600700
---------3
500600
200300
---------3
DBMS
DBMS
Computer networks
CN-1
Database system
DBMS
********************
count
C fundamentals
PCD
System software
SS
********************
count
CS
IS
BOOK DATABASE
6 rows selected.
OUTCOMES &CONCLUSIONS:
At the end of the program the student should be able to,
Understand concepts of report generation, normalization.
APPLICATION AREAS:
Database designing and implementation.
REMARKS:
country varchar(20),
primary key(publisherid)
);
SQL> create table category
(
categoryid number(10),
description varchar (20),
primary key(categoryid)
);
SQL> create table catalog
(
bookid number(10),
title varchar(20),
authorid number(10),
publisherid number(10),
categoryid number(10),
year number(10),
price number(10),
primary key(bookid),
foreign key(authorid) references author(authorid),
foreign key(publisherid)references publisher(publisherid),
foreign key(categoryid)references category(categoryid)
);
SQL> create table orderdetails
(
orderno number(10),
bookid number(10),
qty number(10),
primary key(orderno,bookid),
foreign key(bookid)references catalog(bookid)
);
Enter at least five tuples for each relation.
INSERTING VALUES INTO THE RELATIONS/TABLES:
SQL>insert into author values(&authorid,'&name','&city','&country');
SQL>insert into publisher values(&publisherid,'&name','&city','&country');
SQL>insert into category values(&categoryid,'&description');
SQL>insert into catalog values(&bookid, '&title', &authorid, &publisherid, &categoryid, &year,
&price);
SQL>insert into orderdetails values(&ordno,&bookid,&qty);
SQL QUERIES:
QUERY 01:Give the details of the authors who have 2 or more books in the catalog and the
price of the books is greater than the average price of the books in the catalog and the year of
publication is after 2000.
SQL> select *from author a
where a.authorid in (select c.authorid
from catalog c
where c.year>2002 and c.price >(select avg(price)from catalog)
group by c.authorid
having count(c.authorid)>=2);
QUERY 02:Find the author of the book which has maximum sales.
SQL> select a.name
from author a,catalog c,orderdetails o
where a.authorid=c.authorid and c.bookid=o.bookid and o.qty= (select max(qty) from
orderdetails);
QUERY 03:Demonstrate how you increase the price of books published by a specific
publisher by 10%.
SQL> update catalog
set price=1.1*price
where publisherid =(select publisherid from publisher where name='phi');
Generate suitable reports.
SQL>START BOOK
ttitle 'STOCK DETAILS'
btitle 'BOOK DEALER DATABASE'
column bookid heading 'BOOK_ID'
column title heading 'BOOK_NAME'
column name heading 'AUTHOR_NAME'
column publisherid heading 'PUBLISHER_NAME'
column price heading 'PRICE'
break on name skip 2
compute count of bookid on name
set linesize 100
set pagesize 40
select c.bookid,c.title,a.name,p.name,c.price from author a,publisher p,catalog c where
a.authorid=c.authorid and p.publisherid=c.publisherid order by p.name;
btitle off
ttitle off
clear breaks
clear columns
RESULTS &ANALYSIS:
SQL>desc author;
Name
-----------------------AUTHORID
NAME
CITY
COUNTRY
Null?
-------NOT NULL
Type
---------------------------NUMBER (10)
VARCHAR2 (20)
VARCHAR2 (20)
VARCHAR2 (20)
Null?
-------NOT NULL
Type
---------------------------NUMBER (10)
VARCHAR2 (20)
VARCHAR2 (20)
SQL>desc publisher;
Name
--------------------------PUBLISHERID
NAME
CITY
COUNTRY
VARCHAR2 (20)
SQL>desc category;
Name
-----------------------CATEGORYID
DESCRIPTION
Null?
Type
----------------------------------NOT NULL
NUMBER(10)
VARCHAR2(20)
SQL>desc catalog;
Name
----------------------BOOKID
TITLE
AUTHORID
PUBLISHERID
CATEGORYID
YEAR
PRICE
Null?
Type
-------- ---------------------------NOT NULL
NUMBER (10)
VARCHAR2 (20)
NUMBER (10)
NUMBER (10)
NUMBER (10)
NUMBER (10)
NUMBER (10)
SQL>desc orderdetails;
Name
------------------------ORDERNO
BOOKID
QTY
Null?
Type
-------- -----------------------NOT NULL
NUMBER (10)
NOT NULL
NUMBER (10)
NUMBER (10)
NAME
------------tenanbaum
coreman
balagurusamy
ulf troppens
robert spalding
CITY
---------california
new york
chennai
rome
vienna
COUNTRY
----------------usa
usa
india
italy
austria
;
CITY
--------newyork
indiana
delhi
boston
canberra
COUNTRY
--------------usa
russia
india
australia
Moscow
from CATALOG;
TITLE
--------DATA STRUCTURES
ALGORITHMS
C++
C STRUCTURES
OPERATING
SYSTEMS
AUTHORID
----------------10
20
30
40
50
PUBLISHERID
------------------100
200
300
400
500
CATEGORYID
---------------1000
2000
3000
4000
5000
YEAR
---------2003
2002
2005
2003
2003
PRICE
--------250
450
380
200
310
666
777
DATABASE
SYSTEMS
STORAGE
NETWORKS
SQL >SELECT *
10
100
6000
2010
550
10
200
7000
2012
750
from category;
CATEGORYID
1000
2000
3000
4000
5000
6000
7000
SQL>Select * from
ORDERNO
11
22
33
44
55
DESCRIPTION
cse
ise
mech
ece
eie
mca
msc
orderdetails;
BOOKID
111
222
333
444
555
QTY
100
80
130
120
75
COUNTRY
-------------------usa
QUERY 02:
NAME
-------------------balagurusamy
QUERY 03:
1 row updated.
SQL>select * from catalog;
BOOKID
------------111
222
333
444
555
TITLE
--------DATA
STRUCTURES
ALGORITHMS
C++
C
STRUCTURES
OPERATING
SYSTEMS
AUTHORID
----------------10
PUBLISHERID
------------------100
CATEGORYID
---------------1000
YEAR
---------2003
PRICE
--------275
20
30
40
200
300
400
2000
3000
4000
2002
2005
2003
450
380
200
50
500
5000
2003
310
QUERY 04:
Tue Aug 14
page 1
STOCK DETAILS
BOOK_ID BOOK_NAME
---------- --------------------
AUTHOR_NAME
--------------------
PUBLISHER_NAME
---------------------
PRICE
----------
444
---------1
C STRUCTURES
555
---------1
310
ALGORITHMS
storage networks
495
222
777
ulf troppens
addison
********************
count
coreman
********************
count
DATA STRUCTURES
database systems
550
---------2
333
---------1
pearson
tenanbaum
750
---------2
111
666
200
tenanbaum
tenanbaum
phi
275
tata macrawhill
380
********************
count
C++
balagurusamy
********************
count
BOOK DEALER DATABASE
7 rows selected
OUTCOMES &CONCLUSIONS:
At the end of the program the student should be able to,
Understand concepts of report generation, DML.
APPLICATION AREAS:
Database designing and implementation.
REMARKS:
i. Create the above tables by properly specifying the primary keys and the foreign keys
ii. Enter at least five tuples for each relation
iii. Find all the customers who have at least two accounts at the Main branch.
iv. Find all the customers who have an account at all the branches located in a specific city.
v. Demonstrate how you delete all account tuples at every branch located in a specific city.
vi. Generate suitable reports.
vii. Create suitable front end for querying and displaying the results.
nges.
THEORY:
The SQL WHERE clause is used to specify a condition while fetching the data from single table or
joining with multiple tables.
If the given condition is satisfied then only it returns specific value from the table. You would use
WHERE clause to filter the records and fetching only necessary records.
The WHERE clause is not only used in SELECT statement, but it is also used in UPDATE,
DELETE statement, etc., which we would examine in subsequent chapters.
Syntax:
The basic syntax of SELECT statement with WHERE clause is as follows:
SELECT column1,column2,columnN
FROM table_name
WHERE [condition]
The SQL AND and OR operators are used to combine multiple conditions to narrow data in an SQL
statement. These two operators are called conjunctive operators.
These operators provide a means to make multiple comparisons with different operators in the
sameSQL statement.
The AND Operator:
The AND operator allows the existence of multiple conditions in an SQL statement's WHERE
clause.
Syntax:
The basic syntax of AND operator with WHERE clause is as follows:
SELECT column1,column2,columnN
FROM table_name
WHERE [condition1]AND [condition2]...AND [conditionN];
You can combine N number of conditions using AND operator. For an action to be taken by the
SQL statement, whether it be a transaction or query, all conditions separated by the AND must be
TRUE.
PROCEDURE:
STEP 1: Start
STEP 2: Create the table with its essential attributes.
STEP 3: Insert the values for the created tables.
STEP 4: View the description and the values of the table.
STEP 5: Execute different SQL Commands and extract information from the
table.
STEP 6: Generate the reports
STEP 7:Stop
PROGRAMME:
Create the above tables by properly specifying the primary keys and the foreign keys.
CREATING THE TABLES:
SQL>create table branch
(
bname varchar(20),
city varchar(20),
assets number(10,2),
primary key(bname)
);
SQL> create table account
(
accno number(10),
bname varchar(20),
balance number(10,2),
primary key(accno),
foreign key(bname)references branch(bname) on delete cascade
);
SQL> create table customers
(
cname varchar(20),
street varchar(20),
city varchar(20),
primary key(cname)
);
SQL> create table depositor
(
cname varchar(20),
accno number(10),
primary key(cname,accno),
foreign key(cname)references customers(cname),
foreign key(accno)references account(accno) on delete cascade
);
SQL> create table loan
(
lnum number(10),
bname varchar(20),
amt number(10,2),
primary key(lnum)
);
SQL> create table borrower
(
cname varchar(20),
lnum number(10),
primary key(cname,lnum),
foreign key(cname)references customers(cname),
foreign key(lnum)references loan(lnum) on delete cascade
);
Enter at least five tuples for each relation
INSERTING VALUES INTO THE TABLES/RELATIONS:
SQL>insert into branch values(&bname,&city,&assets);
SQL>insert into account values(&accno,&bname,&balance);
SQL>insert into customers values(&cname,&cstreet,&ccity);
SQL>insert into depositor values(&cname,&accno);
SQL>insert into loan values(&lnum,&bname,&amt);
SQL>insert into borrower values(&cname,&lnum);
SQL QUERIES:
QUERY 01:Find all the customers who have at least two accounts at the Main branch.
SQL> select c.cname
from account a,customers c,branch b,depositor d
where c.cname=d.cname and d.accno=a.accno and a.bname=b.bname
group by c.cname
having count(*)>=2;
QUERY 02:Find all the customers who have an account at all the branches located in a
specific city.
SQL> select count(d.cname),d.cname
from account a,depositor d
where a.bname in (select bname from branch
where city='BLORE')and a.accno=d.accno
group by d.cname;
QUERY 03:Demonstrate how you delete tuples in ACCOUNT relation at every branch
located in a specific city.
SQL>delete from account a
where a.bname in(select b.bname
from branch b
where b.city=UDUPI);
QUERY 04:Generate suitable reports.
SQL>start bank
ttitle 'LOAN DETAILS'
btitle 'BANK DATABASE'
column cname heading 'CUSTOMER NAME
column lnum heading 'LOAN NUMBER'
column bname heading 'BRANCH NAME'
column amt heading 'LOAN AMOUNT'
break on bname skip 2
compute count of lnum on bname
set linesize 100
set pagesize 50
select
c.cname,b.lnum,l.bname,l.amt from customers c,loan l,borrower b where
c.cname=b.cname and l.lnum=b.lnum order by l.bname;
btitle off
ttitle off
clear breaks
clear columns
RESULTS &ANALYSIS:
SQL>desc branch;
Name
Null?
----------------------------------------- -------BNAME
NOT NULL
CITY
ASSETS
Type
---------------------------VARCHAR2(20)
VARCHAR2(20)
NUMBER(10,2)
SQL>desc account;
Name
Null?
----------------------------------------- -------ACCNO
NOT NULL
BNAME
BALANCE
Type
---------------------------NUMBER (10)
VARCHAR2 (20)
NUMBER (10,2)
SQL>desc customers;
Name
Null?
Type
----------------------------------------- ----------------------------------CNAME
NOT NULL VARCHAR2(20)
STREET
VARCHAR2(20)
CITY
VARCHAR2(20)
SQL>desc depositor;
Name
Null?
----------------------------------------- -------CNAME
NOT NULL
ACCNO
NOT NULL
Type
---------------------------VARCHAR2(20)
NUMBER (10)
SQL>desc loan;
Name
----------------------------------------- -------LNUM
NOT NULL
BNAME
AMT
Null?
Type
---------------------------NUMBER(10)
VARCHAR2(20)
NUMBER(10,2)
SQL>desc borrower;
Name
Null?
----------------------------------------- -------CNAME
NOT NULL
LNUM
NOT NULL
Type
---------------------------VARCHAR2(20)
NUMBER(10)
SQL>Select
* from branch;
BNAME
---------------------SBM KAR
SBM UDP
SBM BLR
SBM MUM
SBM KLR
CITY
---------------------KARKALA
UDUPI
BLORE
MUMBAI
KASARAGOD
BALANCE
----------------100000
110000
120000
90000
70000
130000
120000
CITY
------------------KARKALA
UDUPI
BLORE
MUMBAI
KASARAGOD
* fromDEPOSITOR;
CNAME
-----------------dheeraj
sooraj
kathik
abhijith
ullas
dheeraj
sooraj
SQL>Select *
LNUM
-------------100
200
300
400
500
600
700
ACCNO
---------------------10
20
30
40
50
60
70
from LOAN;
BNAME
----------------------SBM KAR
SBM UDP
SBM BLR
SBM MUM
SBM KLR
SBM KAR
SBM KLR
ASSETS
------------1000
1200
1100
5000
4000
AMT
-------------50000
45000
60000
55000
70000
21000
16000
abhijith
ullas
400
500
------------------1
CNAME
------------------kathik
QUERY 03:
Select * from ACCOUNT;
ACCNO
BNAME
-------------------- ------------------------10
SBM KAR
30
SBM BLR
40
SBM MUM
50
SBM KLR
60
BM KAR
BALANCE
----------------100000
120000
90000
70000
130000
QUERY 04:
Fri Aug 31
page 1
LOAN DETAILS
CUSTOMER NAME
LOAN NUMBER BRANCH NAME
LOAN AMOUNT
------------------------------------------------------------------------------kathik
300
SBM BLR
60000
----------- ********************
----------1
count
sum
60000
dheeraj
-----------
ullas
-----------
abhijith
vinay
-----------
100
********************
1
SBM KAR
500
********************
1
SBM KLR
400
800
********************
2
SBM MUM
count
count
count
50000
----------sum
50000
70000
----------sum
70000
55000
85000
----------sum
140000
sooraj
-----------
200
********************
1
BANK DATABASE
6 rows selected.
OUTCOMES &CONCLUSIONS:
Concepts of report generation, DML will be well understood.
APPLICATION AREAS:
Database designing and implementation.
REMARKS:
SBM UDP
count
45000
----------sum
45000