Dbms Assignment.docx

DATABASE MANAGEMENT SYSTEM ASSIGNMENT

Name- Shreya Roll No- 17001 Class- BMS 1A

Question1 To computerize the library system of a college. IssuedBooks( Accessionnumber, Borrower) LibraryBooks( Accessionnumber, Title, Author, Department,price, PurchaseDate) Solution: Creation of LibraryBooks table: Create table librarybooks17001 (accessionnumber int primary key not null, title Varchar(10), Author varchar(20), Department varchar(15), Price int, PurchaseDate date,);

Inserting values Example- insert into librarybooks17001(2,’ xyz’, ‘bayross’, ‘cs’, 190, ‘05-03-03’);

Creation of IssuedBooks table: Create table issuedbooks17001 (accessionnumber int primary key not null, Borrower varchar(20), foreign key(accessionnumber) references librarybooks17001(accessionnumber));

Inserting values: Example- insert into IssuedBooks17001 values(1,’a’);

a) Delete the record of book titled “DatabaseSystemConcep”Delete from LibraryBooks17001 where title='DatabaseSystemConcep';

b) Change the department of the book titled “DiscreteMaths” to “CS” Update librarybooks17001 set department=’cs’ where title=’DiscreteMaths”;

c) List all the books belonging to ‘cs’ department. Select title from librarybooks17001 where department='cs';

d) List all the books that belong to ‘CS’ department and are written by ‘Bayross’. Select title from librarybooks17001 where department='cs' AND author='bayross';

e) List all books (Department=’CS’) that have been issued. Select * from librarybooks_17001, issuedbooks17001 where librarybooks17001.accessionnumber=issuedbooks17001.accessionnumber AND department='cs';

f)

List all books which have a price less than 500 AND PurchaseDate between ’01-01-1999’ and ‘01’01’2004’. Select * from librarybooks17001 where Price<500 AND purchasedate between '01-Jan-1999' and '01-Jan-2004';

QUESTION 2 To store the details of students of Computer Department in your College StudentInfo( Roll no, Name of Student, Date of Birth, Address, Marks, Phone Number) PaperDetails(Paper code, Name of the paper) Academic details(Roll no, Paper code, Attendance, Marks in home examination)

Solution: Creation of Studentinfo table Create table Studentinfo_17001 (Rollno int primary key not null, Name varchar(15), DOB, Address varchar(30), Phoneno int);

Inserting data into StudentInfo_17001: insert into StudentInfo_17001 values (17001, 'shreya', '04-Sep-1999', 'delhi', 9827879);

Creation of PaperDetails Table: Create table Paperdetails_17001 (papercode int primary key not null, nameofpaper varchar(15));

Inserting values into PaperDetails_17001: insert into PaperDetails_17001 values(1, ‘dbms’);

Creation of table Academicdetails table:

Create table Academicdetails_17001 ( PaperCode int, Rollno int, Attendance int, MarksInHomeExam int, primary key(Rollno, Papercode), foreign key(Rollno) references StudentInfo_17001(Rollno), foreign key(Papercode) references PaperDetails_17001(Papercode));

Inserting values into AcademicDetails_17001: insert into AcademicDetails_17016 values(2,17001, 50, 95);

a) Design a query that will return the records (from the second table) along with the name of student from the first table, related to students who have more than 75% attendance and more than 60% marks in paper 2.

select a.rollno, name, a.papercode from studentinfo_17001 s, academicdetails_17001 a, paperdetails_17001 p where s.rollno=a.rollno AND p.papercode=a.papercode AND a.papercode=2 AND marksinhomeexam>60 AND attendance>75;

b) List all the students who live in Delhi and have marks greater than 60 in paper 1. Select Name from studentinfo_17001 s, academicdetails_17001 a where s.Rollno=a.Rollno AND address like '%delhi%' AND marksinhomeexam>60 AND papercode=1;

c) Find the total attendance and total marks obtained by each student.

Select name, sum(Attendance), sum(marksinhomeexam) from studentinfo_17001 s, academicdetails_17001 a where s.rollno=a.rollno group by name;

d) List the name of student who has got highest marks in paper 1.

select name from studentinfo_17001 s, academicdetails_17001 a where s.RollNo=a.RollNo AND Papercode=2 AND marksinhomeexam=(Select max(marksinhomeexam) from Academicdetails_17001 where Papercode=1);