Database Management System Laboratory 16MCA28



Overview of DatabaseA?Database?is a collection of related data organized in a way that data can be easily accessed, managed and updated. Any piece of information can be a data, for example name of your school. Database is actually a place where related piece of information is stored and various operations can be performed on it.A?DBMS?is software that allows creation, definition and manipulation of database. Dbms is a tool used to perform any kind of operation on data in database. Dbms also provides protection and security to database. It maintains data consistency in case of multiple users. Here are some examples of popular dbms, MySql, Oracle, Sybase, Microsoft Access and IBM DB2 etc.SQL is a programming language for Relational Databases. It is designed over relational algebra and tuple relational calculus. SQL comes as a package with all major distributions of RDBMS.SQL comprises both data definition and data manipulation languages. Using the data definition properties of SQL, one can design and modify database schema, whereas data manipulation properties allows SQL to store and retrieve data from database.Data Definition LanguageSQL uses the following set of commands to define database schema ?CREATECreates new databases, tables and views from RDBMS.For example ?Create database tutorialspoint;Create table article;Create view for_students;DROPDrops commands, views, tables, and databases from RDBMS.For example?Drop object_type object_name;Drop database tutorialspoint;Drop table article;Drop view for_students;ALTERModifies database schema.Alter object_type object_name parameters;For example?Alter table article add subject varchar;This command adds an attribute in the relation article with the name subject of string type.Data Manipulation LanguageSQL is equipped with data manipulation language DML. DML modifies the database instance by inserting, updating and deleting its data. DML is responsible for all from data modification in a database. SQL contains the following set of commands in its DML section ?SELECT/FROM/WHEREINSERT INTO/VALUESUPDATE/SET/WHEREDELETE FROM/WHEREThese basic constructs allow database programmers and users to enter data and information intothe database and retrieve efficiently using a number of filter options.SELECT/FROM/WHERESELECT ? this is one of the fundamental query command of SQL. It is similar to the projection operation of relational algebra. It selects the attributes based on the condition described by WHERE clause.FROM ? this clause takes a relation name as an argument from which attributes are to be selected/projected. In case more than one relation names are given, this clause corresponds to Cartesian product.WHERE ? this clause defines predicate or conditions, which must match in order to qualify the attributes to be projected.For example ?Select author_nameFrom book_authorWhere age > 50;This command will yield the names of authors from the relation book_author whose age is greater than 50.INSERT INTO/VALUESThis command is used for inserting values into the rows of a table relation.Syntax?INSERT INTO table (column1 [, column2, column3 ... ]) VALUES (value1 [, value2, value3... ])OrINSERT INTO table VALUES (value1, [value2, ... ])For example ?INSERT INTO tutorialspoint (Author, Subject) VALUES ("anonymous", "computers");UPDATE/SET/WHEREThis command is used for updating or modifying the values of columns in a table relation.Syntax ?UPDATE table_name SET column_name = value [, column_name = value ...] [WHERE condition]For example ?UPDATE tutorialspoint SET Author="webmaster" WHERE Author="anonymous";DELETE/FROM/WHEREThis command is used for removing one or more rows from a table relation.Syntax ?DELETE FROM table_name [WHERE condition];For example ?DELETE FROM tutorialspointsWHERE Author="unknown";1) Create the following tables with properly specifying Primary keys, foreign keys and solve the following queries. i. BRANCH(Branchid, Branchname, HOD) ii. STUDENT(USN, Name, Address, Branchid, sem)iii. BOOK(Bookid, Bookname, Authorid, Publisher, Branchid)iv. AUTHOR(Authorid, Authorname, Country, age) v. BORROW(USN ,Bookid, Borrowed_Date) Queries:1 List the details of Students who are all studying in 2ndsem MCA.2 List the students who are not borrowed any books. 3 Display the USN, Student name, Branch_name, Book_name, Author_name, Books_Borrowed_Date of 2nd sem MCA Students who borrowed books. 4 Display the number of books written by each Author. 5 Display the student details who borrowed more than two books. 6 Display the student details who borrowed books of more than one Author. 7 Display the Book names in descending order of their names. 8 List the details of students who borrowed the books which are all published by the same Publisher.CREATE TABLE BRANCH ( BRANCHID NUMBER PRIMARY KEY, BRANCHNAME VARCHAR(20), HOD VARCHAR(20) ); INSERT INTO BRANCH VALUES (1,'MCA','SRINIVAS RAO');INSERT INTO BRANCH VALUES (2,'BE','TIPPESWAMY');INSERT INTO BRANCH VALUES (3,'BE_EC','RAVISHANKAR'); INSERT INTO BRANCH VALUES (4,'BE_ISE','JOHN'); CREATE TABLE STUDENT ( USN VARCHAR(30) PRIMARY KEY, NAME VARCHAR(40), ADDRESS VARCHAR(60), BRANCHID NUMBER REFERENCES BRANCH, SEM NUMBER );INSERT INTO STUDENT VALUES ( '1ST16MCA01', 'ADARSH','KERALA',1,2);INSERT INTO STUDENT VALUES ( '1ST16CS02', 'ANIS','KERALA',2,2);INSERT INTO STUDENT VALUES ( '1ST16EC03', 'ARCHANA','CHANDIGARH',3,3);INSERT INTO STUDENT VALUES ( '1ST16IS01', 'NAYON','WEST BENGAL',4,4);INSERT INTO STUDENT VALUES ( '1ST16MCA02', 'RAHUK','BIHAR',1,2);CREATE TABLE AUTHOR ( AUTHORID NUMBER PRIMARY KEY, AUTHORNAME VARCHAR(30), COUNTRY VARCHAR(30), AGE NUMBER ); INSERT INTO AUTHOR VALUES (11,'WILLIAM STALLING','AMERICA',30);INSERT INTO AUTHOR VALUES (12,'PETERSON','CANADA', 40);INSERT INTO AUTHOR VALUES (13,'PADMAREDDY','INDIA',35);INSERT INTO AUTHOR VALUES (14,'NAMRATHA','INDIA',27); CREATE TABLE BOOK ( BOOKID NUMBER PRIMARY KEY, BOOKNAME VARCHAR(30), AUTHORID NUMBER REFERENCES AUTHOR, PUBLISHER VARCHAR(30), BRANCHID NUMBER REFERENCES BRANCH );INSERT INTO BOOK VALUES (111,'MCADBMS',11,'WILLIAM PUBLISHER',1);INSERT INTO BOOK VALUES (222,'CSDBMS',12,'PETERSON PUBLISHER',2);INSERT INTO BOOK VALUES (333,'ISEDBMS',13,'PADMAREDDY PUBLISHER',3);INSERT INTO BOOK VALUES (444,'ECDBMS',14,'NAMRATHA PUBLISHER',4);CREATE TABLE BORROW ( USN VARCHAR(30) REFERENCES STUDENT, BOOKID NUMBER REFERENCES BOOK, BORROWED_DATE DATE );INSERT INTO BORROW VALUES( '1ST16MCA01',111,date '2010-11-02');INSERT INTO BORROW VALUES( '1ST16CS02',222,date '2014-1-02');INSERT INTO BORROW VALUES( '1ST16IS01',333,date '2014-4-05');INSERT INTO BORROW VALUES( '1ST16IS01',333,date '2015-5-07');INSERT INTO BORROW VALUES( '1ST16IS01',333,date '2017-4-05'); 1. List the details of Students who are all studying in 2ndsem MCA.SQL> SELECT * FROM STUDENT WHERE SEM=2;USN NAME ADDRESS BRANCHID SEM1ST16MCA01 ADARSH KERALA 1 21ST16CS02 ANIS KERALA 2 21ST16MCA02 RAHUK BIHAR 1 22) List the students who are not borrowed any books. SQL> SELECT * FROM STUDENT WHERE USN NOT IN ( SELECT USN FROM BORROW);USN NAME ADDRESS BRANCHID SEM1ST16EC03 ARCHANA CHANDIGARH 3 31ST16MCA02 RAHUK BIHAR 1 23) Display the USN, Student name, Branch_name, Book_name, Author_name, Books_Borrowed_Date of 2nd sem MCA Students who borrowed books.\select distinct a1.USN,a1.NAME,c.BOOKNAME,d.AUTHORNAME,a2.BORROWED_DATE from STUDENT a1 join branch br on a1.BRANCHID=br.BRANCHID join BORROW a2 on a1.USN=a2.usn join book c on a2.BOOKID=c.BOOKID join author d on c.AUTHORID=d.AUTHORID where a1.SEM=2 and br.BRANCHNAME='MCA';USN NAME BOOKNAME AUTHORNAME BORROWED_1ST16MCA01 ADARSH MCADBMS WILLIAM STALLING 02-NOV-104) Display the number of books written by each AuthorSQL> SELECT AUTHORNAME, COUNT(*) FROM AUTHOR, BOOK WHERE AUTHOR.AUTHORID=BOOK.AUTHORID GROUP BY AUTHOR.AUTHORNAME;AUTHORNAME COUNT(*)------------------------------ ----------PETERSON 1WILLIAM STALLING 1PADMAREDDY 1NAMRATHA 15) Display the student details who borrowed more than two books.select * from STUDENT where USN in (select USN from BORROW group by USN having COUNT(*)>2); select * from STUDENT where USN in (select USN from BORROW group by USN having COUNT(*)>2);USN NAME ADDRESS BRANCHID SEM1ST16IS01 NAYON WEST BENGAL 4 46) Display the student details who borrowed books of more than one Author.select * from student where usn in (select usn from borrow a join book b on a.BOOKID=b.BOOKID group by usn, b.AUTHORID having count(b.AUTHORID)>1);USN NAME ADDRESS BRANCHID SEM1ST16IS01 NAYON WEST BENGAL 4 47) Display the Book names in descending order of their names. SQL> SELECT BOOKNAME FROM BOOK ORDER BY BOOKNAME DESC;BOOKNAME------------------------------MCADBMSISEDBMSECDBMSCSDBMS8 List the details of students who borrowed the books which are all published by the same Publisher. select * from student where usn in(select a.USN from student a join borrow b on b.USN=a.USN joinbook c on b.BOOKID=c.BOOKID group by a.USN having count(c.PUBLISHER)=1);USNNAMEADDRESSBRANCHIDSEM1ST16MCA01ANISH49/5/1C121ST16MCA03DIPAK49/8/1C33E-R DIAGRAM :-552450174625-552450563245RELATION DIAGRAM : 2) Design an ER-diagram for the following scenario, Convert the same into a relational model and then solve the following queries. Consider a Cricket Tournament “ABC CUP” organized by an organization. In the tournament there are many teams are contesting each having a Teamid,Team_Name, City, a coach. Each team is uniquely identified by using Teamid. A team can have many Players and a captain. Each player is uniquely identified by Playerid, having a Name, and multiple phone numbers,age. A player represents only one team. There are many Stadiums to conduct matches. Each stadium is identified using Stadiumid, having a stadium_name,Address ( involves city,area_name,pincode).A team can play many matches. Each match played between the two teams in the scheduled date and time in the predefined Stadium. Each match is identified uniquely by using Matchid. Each match won by any of the one team that also wants to record in the database. For each match man_of_the match award given to a player. Queries: 1 Display the youngest player (in terms of age) Name, Team name , age in which he belongs of the tournament. 2 List the details of the stadium where the maximum number of matches were played. 3 List the details of the player who is not a captain but got the man_of _match award at least in two matches. 4 Display the Team details who won the maximum matches. 5 Display the team name where all its won matches played in the same stadium. SQL> connectEnter user-name: SYSTEMEnter password:Connected.SQL> create table team ( tid int primary key, tname varchar(20), coach varchar(20), captain_pid int, city varchar(20));Table created.SQL> create table player ( pid int primary key, pname varchar(2), age int, tid int references team(tid));Table created.SQL> create table stadium (sid int primary key, sname varchar(20), picode number(8), city varchar(20), area varchar(20));Table created.SQL> create table match (mid int primary key, mdate date, time varchar(6), sid int references stadium(sid), team1_id int references team(tid), team2_id int references team(tid), winning_team_id int references team(tid), man_of_match int references player(pid));Table created.SQL> create table player_phone ( pid int references player(pid), phone int , primary key(pid,phone));Table created.insert into team values(1,'India','Virat',11,'Delhi'); insert into team values(2,'Australia','Smith',22,'Sydney'); insert into team values(3,'WestIndies','Sammy',33,'Kingstown'); insert into team values(4,'Srilanka','Sangakara',44,'Colombo'); select * from team; TID TNAME COACH CAPTAIN_PID CITY 1 India Virat 11 Delhi 2 Australia Smith 22 Sydney 3 WestIndies Sammy 33 Kingstown 4 Srilanka Sangakara 44 Colombo insert into player values(101,'MS',35,1); insert into player values(102,'ps',40,2); insert into player values(103,'MM',26,3); insert into player values(104,'NO',36,4); select * from player; PID PN AGE TID---------- -- ---------- ---------- 101 MS 35 1 102 ps 40 2 103 MM 26 3 104 NO 36 4insert into stadium values(201,'chinnaswamy',560010,'bangalore','karnataka');insert into stadium values(202,'Eadengarden',200010,'Kolkata','Westbengal');insert into stadium values(203,'Melbourne',122010,'Melbourne','Australia');SQL> select * from stadium; SID SNAME PICODE CITY AREA 201 chinnaswamy 560010 bangalore karnataka 202 Eadengarden 200010 Kolkata Westbengal 203 Melbourne 122010 Melbourne Australia insert into match values(301,date'2010-5-10','10am',201,1,2,1,101); insert into match values(302,date'2016-3-15','11am',202,2,3,3,103); select * from match; MID MDATE TIME SID TEAM1_ID TEAM2_ID WINNING_TEAM_ID MAN_OF_MATCH 301 10-MAY-10 10am 201 1 2 1 101 302 15-MAR-16 11am 202 2 3 3 103insert into player_phone values(101,100000); insert into player_phone values(102,2100000);insert into player_phone values(103,23330000);Display the youngest player (in terms of age) Name, Team name , age in which he belongs of the tournament. SELECT B.NAME, A.TEAMNAME, B.AGE FROM TEAM A , PLAYER B WHERE A.TEAMID = B.TEAMID AND B.AGE=(SELECT MIN(AGE) FROM PLAYER ;List the details of the stadium where the maximum number of matches were played. SQL> select * from stadium where sid in (select sid from match group by sid having count(sid) = (select max(count(sid)) from match group by sid)) ; SID SNAME PICODE CITY---------- -------------------- ---------- --------------------AREA-------------------- 201 chinnaswamy 560010 bangalorekarnataka 202 Eadengarden 200010 KolkataWestbengalList the details of the player who is not a captain but got the man_of _match award at least in two matches. SQL> select * from player where pid not in ( select captain_pid from team wherecaptain_pid not in (select man_of_match from match group by man_of_match havingcount(man_of_match)=2)); PID PN AGE TID---------- -- ---------- ---------- 101 MS 35 1 102 ps 40 2 103 MM 26 3 104 NO 36 4Display the Team details who won the maximum matches. SQL> select * from team where tid in 2 (select winning_team_id from match group by winning_team_id 3 having count(winning_team_id)= 4 (select max(count(winning_team_id)) 5 from match group by winning_team_id)) 6 ; TID TNAME COACH CAPTAIN_PID---------- -------------------- -------------------- -----------CITY-------------------- 1 India Virat 11Delhi 3 WestIndies Sammy 33Kingstown Display the team name where all its won matches played in the same stadium. SQL> SELECT MAX(winning_team_id)FROM ( SELECT DISTINCT winning_team_id, sid FROM match )GROUP BY sid HAVING COUNT(*) = 1;MAX(WINNING_TEAM_ID)-------------------- 1 33) Consider the following Scenario and design an ER-Diagram, map the designed ER-diagram into a Relational model. Consider an organization “ABC” having many employees. An employee works for one department. Each employee identified by using Empid, having Name, address ( described as House_no, city, district, state, pin code) and more than one phone numbers. Department identified by using Dno, having Dname, Dlocation. Each Department having a manager . Each department having many employees. There are many Projects , each project is controlled by the department. Each Project uniquely identified by Pno, having Project_name,Project_location. An employee works on many Projects. Number of hours per week worked on each project by an Employee also needs to be recorded in the database . A project is worked by many employees. Each employee supervised by the supervisor. Employee having many dependents. Dependents having the dependent_name, gender, age, address. Dependents are identified by Empid. T1(Empid, Emp_Name,city, district, state, pin_code, phoneno, Dno,Dname,Dlocation, Dept_mgr_id, Pno, Project_name, Project_location, Number_of_Hours,Supervisor_Empid, Dependent_name, gender, address) Queries: 1. Display the details of the employees who are working on both the projects having project_no 5 and 10. 2. Display the details of employees having atleast two dependents. 3. Display the project name on which more number of employees are working. 4. Retrieve the employees who do not have any dependents. 5. Display the Employee details whose total number of hours per week working on various projects is maximum than all other employees. 6. create a view to display the number of employees working in each department. CREATE TABLE EMPLOYEE (EMPID INTEGER PRIMARY KEY, EMPNAME VARCHAR(20), HOUSENUM NUMBER, CITY VARCHAR(20),DISTRICT VARCHAR(20),STATE VARCHAR(20),PINCODE NUMBER, PHONENUM1 NUMBER, PHONENUM2 NUMBER); DESC EMPLOYEE; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPID NOT NULL NUMBER(38) EMPNAME VARCHAR2(20) HOUSENUM NUMBER CITY VARCHAR2(20) DISTRICT VARCHAR2(20) STATE VARCHAR2(20) PINCODE NUMBER PHONENUM1 NUMBER PHONENUM2 NUMBER INSERT INTO EMPLOYEE VALUES (1,'NAMRATHA',200,'DHARWAD','DHARWAD','KARNATAKA',560010,9632223445,9474344434);INSERT INTO EMPLOYEE VALUES (2,'SNEHA',300,'BANGALORE','BANGALORE','KARNATAKA',580070,9635273845,9478328434);INSERT INTO EMPLOYEE VALUES (3,'NETRA',400,'BELGAUM','BELGAUM','KARNATAKA',540030,9335273945,9276380434);INSERT INTO EMPLOYEE VALUES (4,'MANJU',500,'HYDERABAD','HYDERABAD','ANDRAPRADESH',340034,9335263845,726684444);INSERT INTO EMPLOYEE VALUES (5,'ADARSH',600,'ALPHE','TRISSUR','KERALA',340038,9442334855,5266844433);INSERT INTO EMPLOYEE VALUES (6,'ABC',600,'RAJAJINA','BELGAUM','KARNATAKA',540030,9335273945,9276380434);INSERT INTO EMPLOYEE VALUES(7,'DEFC',700,'ABCDEFGH','BELGAUM','KARNATAKA',543334,53463,56277676);SQL> CREATE TABLE DEPARTMENT (DEPTNO NUMBER PRIMARY KEY, DEPTNAME VARCHAR(20),DEPTLOCATION VARCHAR(20), DEPTMANAGERID REFERENCES EMPLOYEE(EMPID)); DESC DEPARTMENT; Name Null? Type ----------------------------------------- -------- ---------------------------- DEPTNO NOT NULL NUMBER DEPTNAME VARCHAR2(20) DEPTLOCATION VARCHAR2(20) DEPTMANAGERID NUMBERINSERT INTO DEPARTMENT VALUES(11,'DEVELOPER','BANGALORE',1);INSERT INTO DEPARTMENT VALUES(12,'TESTER','TRISSUR',5);INSERT INTO DEPARTMENT VALUES(13,'NETWORKENGINEER','CHENNAI',2);INSERT INTO DEPARTMENT VALUES(14,'MAINTAINANCE','HYDERABAD',3); INSERT INTO DEPARTMENT VALUES(15,'SECURITY','BANGALORE',4); SELECT * FROM DEPARTMENT; DEPTNO DEPTNAME DEPTLOCATION DEPTMANAGERID---------- -------------------- -------------------- ------------- 11 DEVELOPER BANGALORE 1 12 TESTER TRISSUR 5 13 NETWORKENGINEER CHENNAI 2 14 MAINTAINANCE HYDERABAD 3 15 SECURITY BANGALORE 4 CREATE TABLE PROJECT (PNO NUMBER PRIMARY KEY, PNAME VARCHAR(20),PLOCATION VARCHAR(20),NUMBEROFHOURS NUMBER, SUPERVISOREMPID REFERENCES EMPLOYEE(EMPID));SQL> DESC PROJECT; Name Null? Type ----------------------------------------- -------- ---------------------------- PNO NOT NULL NUMBER PNAME VARCHAR2(20) PLOCATION VARCHAR2(20) NUMBEROFHOURS NUMBER SUPERVISOREMPID NUMBERSQL> INSERT INTO PROJECT VALUES(5,'ANDROID APPLICATION','BANGALORE',20,1);1 row created.SQL> INSERT INTO PROJECT VALUES(6,'MOBILE APPLICATION','HYDERABAD',30,2);1 row created.SQL> INSERT INTO PROJECT VALUES(7,'WEBSITE','CHENNAI',40,3);1 row created.SQL> INSERT INTO PROJECT VALUES(8,'SMP','BANGALORE',50,4);1 row created.SQL> INSERT INTO PROJECT VALUES(10,'WIRELESS COMMU','BANGALORE',60,5);1 row created.SQL> CREATE TABLE DEPENDENT (DEPENDENTNAME VARCHAR(20), GENDER VARCHAR(20), ADDRESS VARCHAR(20), AGE NUMBER, DEPENDENTEMPID REFERENCES EMPLOYEE(EMPID));Table created.SQL> DESC DEPENDENT; Name Null? Type ----------------------------------------- -------- ---------------------------- DEPENDENTNAME VARCHAR2(20) GENDER VARCHAR2(20) ADDRESS VARCHAR2(20) AGE NUMBER DEPENDENTEMPID NUMBERSQL>SQL> INSERT INTO DEPENDENT VALUES( 'MANJUNATH','MALE','DHARWAD',30,1);1 row created.SQL> INSERT INTO DEPENDENT VALUES( 'SANTOSH','MALE','BANGALORE',28,2);1 row created.SQL> INSERT INTO DEPENDENT VALUES( 'VIJAY','MALE','BELGAUM',34,3);1 row created.SQL> INSERT INTO DEPENDENT VALUES( 'AKSHATHA','FEMALE','TRISSUR',24,4);1 row created.SQL> INSERT INTO DEPENDENT VALUES( 'ANU','FEMALE','HYDERABAD',36,5);1 row created.SQL> INSERT INTO DEPENDENT VALUES( 'SANVI','FEMALE','DHARWAD',6,1);1 row created.SQL> SELECT * FROM DEPENDENT;DEPENDENTNAME GENDER ADDRESS AGE-------------------- -------------------- -------------------- ----------DEPENDENTEMPID--------------MANJUNATH MALE DHARWAD 30 1SANTOSH MALE BANGALORE 28 2VIJAY MALE BELGAUM 34 3DEPENDENTNAME GENDER ADDRESS AGE-------------------- -------------------- -------------------- ----------DEPENDENTEMPID--------------AKSHATHA FEMALE TRISSUR 24 4ANU FEMALE HYDERABAD 36 5SANVI FEMALE DHARWAD 6 1 QUERIES:2) Display the details of employees having at least two dependentsSQL> SELECT * FROM EMPLOYEE WHERE( SELECT COUNT(*) FROM DEPENDENT WHERE DEPENDENTEMPID=EMPID)>=2; EMPID ENAME HOUSENO CITY DISTRICT STATE PINCODE PHONENUM1 PHONENUM2 1 NAMRATHA 200 DHARWAD DHARWAD KARNATAKA 560010 9632223445 94743444343) Display the project name on which more number of employees are workingSQL> SELECT EMPID, PNO FROM EMPLOYEE INNER JOIN PROJECT ON EMPLOYEE.EMPID =PROJECT.SUPERVISOREMPID WHERE EMPLOYEE.EMPID IN (SELECT EMPID FROM PROJECT GROUP BY EMPID HAVING COUNT (EMPID)>2); EMPID PNO---------- ---------- 1 5 2 6 3 7 4 8 5 10 5 114) Retrieve the employees who do not have any dependents.SQL> SELECT * FROM EMPLOYEE WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE EMPID=DEPENDENTEMPID); EMPID ENAME HOUSENO CITY---------- -------------------- ---------- --------------------DISTRICT STATE PINCODE PHONENUM1 PHONENUM2-------------------- -------------------- ---------- ---------- ---------- 7 DEFC 700 ABCDEFGHBELGAUM KARNATAKA 543334 53463 56277676 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download