Scribd
Upload
14 views

Dbms Lab Exp1

Uploaded by

sumitashetty19
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
14 views

Dbms Lab Exp1

Uploaded by

sumitashetty19
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 8

DBMS LAB with Mini Project(21CSL55) 2023

Program -1
Aim: Demonstrating creation of tables, applying the view concepts on the tables.
Program: Consider the following schema for a Library Database:
BOOK(Book_id, Title, Publisher_Name, Pub_Year)
BOOK_AUTHORS(Book_id, Author_Name)
PUBLISHER(Name, Address, Phone)
BOOK_COPIES(Book_id, Programme_id, No-of_Copies)
BOOK_LENDING(Book_id, Programme_id, Card_No, Date_Out, Due_Date)
LIBRARY_PROGRAMME(Programme_id, Programme_Name, Address)
Write SQL queries to
i. Retrieve details of all books in the library – id, title, name of PUBLISHER, authors,
number of copies in each branch, etc.
ii. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan
2017 to Jun 2017.
iii. Delete a book in BOOK table. Update the contents of other tables to reflect this data
manipulation operation.
iv. Partition the BOOK table based on year of publication. Demonstrate its working with
a simple query.
v. Create a view of all books and its number of copies that are currently available in the
Library.
Schema Diagram:

Artificial Intelligence and Data Science Engineering AY: 2023-24 Page 1


DBMS LAB with Mini Project(21CSL55) 2023

(i) Query for creating table PUBLISHER:

create table PUBLISHER ( name varchar(12),


address varchar(12), phone int,
primary key(name) );
Output

(ii) Query for creating table BOOK:


create table BOOK ( book_id varchar(5),
title varchar(15), PUBLISHER_name varchar(10),
pub_year int,
primary key(book_id),
foreign key(PUBLISHER_name) references PUBLISHER(name) on delete cascade );
Output

(iii) Query for creating table BOOK_AUTHORS:


create table BOOK_AUTHORS ( book_id varchar(5),
author_name varchar(15),
primary key(book_id),
foreign key(book_id) references Book(book_id) on delete cascade );
Output

(iv) Query for creating table LIBRARY_PROGRMME:


create table LIBRARY_PROGRMME ( programme_id varchar(5),
programme_name varchar(10), address varchar(15),

Artificial Intelligence and Data Science Engineering AY: 2023-24 Page 2


DBMS LAB with Mini Project(21CSL55) 2023

primary key(programme_id) );
Output

(v) Query for creating table BOOK_COPIES:


create table BOOK_COPIES ( book_id varchar(5),
Programme_id varchar(5), no_of_copies int,
primary key(book_id,Programme_id),
foreign key(book_id) references book(book_id) on delete cascade,
foreign key(Programme_id) references LIBRARY_PROGRMME(Programme_id) on delete
cascade );
Output

(vi) Query for creating table BOOK_LENDING:


create table BOOK_LENDING (Book_id varchar(5),
Programme_id varchar(5), Card_no varchar(5),
Date_out date, Due_dat e date,
primary key(Book_id,Programme_id,Card_no),
foreign key(Book_id) references BOOK(book_id),
foreign key(Programme_id) references LIBRARY_PROGRMME(Programme_id) on delete
cascade);
Output

Artificial Intelligence and Data Science Engineering AY: 2023-24 Page 3


DBMS LAB with Mini Project(21CSL55) 2023

(i)Queries for inserting table PUBLISHER:


insert into PUBLISHER values('Mcgrawhill','Bangalore','9480506312');
insert into PUBLISHER values('Pearson','Newdelhi','9785642365');
insert into PUBLISHER values('Random house','Hydrabad','8796452368');
insert into PUBLISHER values('Sapna','Chenai','8947589632');
insert into PUBLISHER values('Oxford','Bangalore','9785642315');
Output

(ii)Queries for inserting table BOOK:


insert into BOOK values('1','DBMS','Mcgrawhill','2017');
insert into BOOK values('2','ADBMS','Mcgrawhill','2016');
insert into BOOK values('3','CN','Pearson','2016');
insert into BOOK values('4','CG','Oxford','2015');
insert into BOOK values('5','OS','Pearson','2016');
Output

(iii) Queries for inserting table BOOK_AUTHORS:


insert into BOOK_AUTHORS values('1','navathe');
insert into BOOK_AUTHORS values('2','navathe');
insert into BOOK_AUTHORS values('3','tenenboum');
insert into BOOK_AUTHORS values('4','edward');
insert into BOOK_AUTHORS values('5','galvin');

Artificial Intelligence and Data Science Engineering AY: 2023-24 Page 4


DBMS LAB with Mini Project(21CSL55) 2023

Output

(iv) Queries for inserting table LIBRARY_PROGRMME:


insert into LIBRARY_PROGRMME values('10','RR nagar','Bangalore');
insert into LIBRARY_PROGRMME values('11','Manipal','Bangalore');
insert into LIBRARY_PROGRMME values('12','RNSIT','Bangalore');
insert into LIBRARY_PROGRMME values('13','Rajajnagar','Bangalore');
insert into LIBRARY_PROGRMME values('14','Nitte','Mangalore');
Output

(v) Queries for inserting table BOOK_COPIES:


insert into BOOK_COPIES values('1','10','10');
insert into BOOK_COPIES values('1','11','5');
insert into BOOK_COPIES values('2','12','2');
insert into BOOK_COPIES values('2','13','5');
insert into BOOK_COPIES values('3','14','7');
insert into BOOK_COPIES values('5','10','1');
insert into BOOK_COPIES values('4','11','3');
Output

Artificial Intelligence and Data Science Engineering AY: 2023-24 Page 5


DBMS LAB with Mini Project(21CSL55) 2023

(vi) Queries for inserting table BOOK_LENDING:


insert into BOOK_LENDING values('1','10','101','01-jan-17','01-jun-17');
insert into BOOK_LENDING values('3','14','101','11-jan-17','11-mar-17');
insert into BOOK_LENDING values('2','13','101','21-feb-17','21-apr-17');
insert into BOOK_LENDING values('4','11','101','15-mar-17','15-jul-17');
insert into BOOK_LENDING values('1','11','104','12-apr-17','12-may-17');
Output

(i)Write SQL queries to retrieve details of all books in the library – id, title, name of
PUBLISHER, authors, number of copies in each branch, etc.
Query: select b.book_id, b.title,b.publisher_name,a.author_name,c.no_of_copies,
c.progarmme_id from BOOK b, BOOK_AUTHORS a, BOOK_COPIES c
where b.book_id=a.book_id and b.book_id=c.book_id;
Output

(ii) Write SQL queries to get the particulars of borrowers who have borrowed more than 3
books, but from Jan 2017 to Jun 2017.
Query: select card_no from BOOK_LENDING
where date_out between '01-jan-17' and '30-jun- 17'
group by card_no
having count(*)>3;

Artificial Intelligence and Data Science Engineering AY: 2023-24 Page 6


DBMS LAB with Mini Project(21CSL55) 2023

Output

(iii) Write SQL queries to delete a book in BOOK table. Update the contents of other tables
to reflect this data manipulation operation.
Query: delete from BOOK where book_id=3;
Output

SQL> delete from book_authors where book_id=3;


1 row deleted.
SQL> delete from book_copies where book_id=3;
1 row deleted.
SQL> delete from book_lending where book_id=3;
1 row deleted.
SQL> delete from book where book_id=3;
1 row deleted.
(iv) Write SQL queries to partition the BOOK table based on year of publication.
Demonstrate its working with a simple query.
Query: create table bookp( book_id int,
title varchar(15), pub_name varchar(15),
pub_year int, primary key(book_id))
partition by range(pub_year) ( partition p0 values less than(2002),
partition p1 values less than(2010),
partition p2 values less than(2015));
insert into bookp values ('801','dbms','willey','2000');
insert into bookp values ('802','dbms','willey','2009');
insert into bookp values ('803','dbms','willey','2014');

Artificial Intelligence and Data Science Engineering AY: 2023-24 Page 7


DBMS LAB with Mini Project(21CSL55) 2023

Output

(v) Write SQL queries to create a view of all books and its number of copies that are
currently available in the Library.
Query: create view no_of_copies as( select book_id,sum(no_of_copies) as copies from
BOOK_COPIES group by book_id);
Output
select *from no_of_copies;

Artificial Intelligence and Data Science Engineering AY: 2023-24 Page 8

You might also like