Dbms Lab Manual Final
Dbms Lab Manual Final
Dbms Lab Manual Final
ENGINEERING
LABORATORY MANUAL
Semester: V
Prepared By
Ms.Anjana Kulkarni
VTU, Belagavi.
Program Educational Objectives (PEOs)
PEO4: Pursue a professional career adopting work values with a social concern to
bridge the digital divide and develop effective communication skills and
leadership qualities, and
PEO5: Understand the efficacy of life-long learning, professional ethics and practices,
so that they may emerge as global leaders.
Program Outcomes:
PO Number Title
PO 1 Engineering knowledge
PO 2 Problem analysis
PO 3 Design/development of solutions
PO 8 Ethics
PO 10 Communication
PO 12 Life-long learning
Course Outcomes:
CO Description
CO3 Implement, analyze and evaluate the project developed for an application.
MySql 8.1
SOFTWARE REQUIREMENT
Sl.no PART- A
1 Consider the following schema for a Library Database:
PART-B
For any problem selected
Make sure that the application should have five or more tables
Indicative areas include; health care
EXPERIMENT-1
Solution:
Entity-Relationship Diagram
SCHEMA DIAGRAM
Book
TABLE CREATION
DESC COMMANDS
DESC PUBLISHER;
DESC BOOK;
DESC LIBRARY_BRANCH;
DESC BOOK_COPIES;
DESC CARD;
DESC BOOK_LENDING;
QUERIES:
/*1. Retrieve details of all books in the library – id, title, name of publisher, authors, number
of copies in each branch, etc. */
SELECT B.BOOK_ID, B.TITLE, B.PUBLISHER_NAME, A.AUTHOR_NAME,
C.NO_OF_COPIES, L.BRANCH_ID
FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH L
WHERE B.BOOK_ID=A.BOOK_ID AND B.BOOK_ID=C.BOOK_ID AND
L.BRANCH_ID=C.BRANCH_ID;
/* 2. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017
to Jun 2017. */ select CARD_NO from book_lending where DATE_OUT between '01-JAN-17'
and '01- JUL17' group by(CARD_NO) having count(CARD_NO)>3 ;
/* 3. Delete a book in BOOK table. Update the contents of other tables to reflect this data
Manipulation operation. */
DELETE FROM BOOK WHERE BOOK_ID=3;
SELECT * FROM BOOK;
/* 4. Partition the BOOK table based on year of publication. Demonstrate its working with a
simple query. */
CREATE VIEW YEAR AS SELECT PUB_YEAR FROM BOOK;
SELECT * FROM YEAR;
/* 5. Create a view of all books and its number of copies that are currently available in the
Library. */
CREATE VIEW V_BOOKS AS SELECT B.BOOK_ID, B.TITLE, C.NO_OF_COPIES
FROM BOOK B, BOOK_COPIES C WHERE B.BOOK_ID=C.BOOK_ID;
SELECT * FROM V_BOOKS;
OUTPUT
| 4 | EDWARD ANGEL |
| 5 | GALVIN |
+ + +
5 rows in set (0.00 sec)
2- Queries
mysql> mysql> select CARD_NO from book_lending where DATE_OUT between '17-01-01'
and '1707-01' group by(CARD_NO) having count(CARD_NO)>3 ;
Empty set, 10 warnings (0.03 sec)
3- Queries
mysql> DELETE FROM BOOK WHERE BOOK_ID=3;
Query OK, 1 row affected (0.08 sec)
5- Queries
mysql> CREATE VIEW V_BOOKS AS SELECT B.BOOK_ID, B.TITLE, C.NO_OF_COPIES
-> FROM BOOK B, BOOK_COPIES C WHERE B.BOOK_ID=C.BOOK_ID;
Query OK, 0 rows affected (0.04 sec)
mysql> mysql> SELECT * FROM V_BOOKS;
+ + + +
| BOOK_ID | TITLE | NO_OF_COPIES |
+ + + +
| 1 | DBMS | 10 |
| 1 | DBMS | 5|
| 2 | ADBMS | 2|
| 2 | ADBMS | 5|
| 4 | CG | 3|
| 5 | OS | 1|
+ + + +
6 rows in set (0.00 sec)
LAB EXPERIMENTS-2
Description of Tables
DESC SALESMAN;
DESC CUSTOMER;
DESC ORDERS;
Queries
1. Count the customers with grades above Bangalore‟s average.
2. Find the name and numbers of all salesman who had more than one customer.
3. List all the salesman and indicate those who have and don‟t have customers in their cities (Use
UNION operation.)
DBMS Lab with Mini Project
4. Create a view that finds the salesman who has the customer with the highest order of a day.
5. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must
also be deleted.
DELETE FROM SALESMAN WHERE SALESMAN_ID=1000;
SELECT * FROM SALESMAN;
SELECT * FROM ORDERS;
OUTPUT
Q-1
mysql> SELECT COUNT(CUSTOMER_ID) FROM CUSTOMER WHERE GRADE >
(SELECT AVG(GRADE) FROM CUSTOMER WHERE CITY='BANGALORE');
+ +
| COUNT(CUSTOMER_ID) |
+ +
| 3|
+ +
1 row in set (0.04 sec)
Q-2
SELECT SALESMAN_ID,NAME FROM SALESMAN A WHERE (SELECT COUNT(*)
FROM CUSTOMER C WHERE C.SALESMAN_ID=A.SALESMAN_ID) > 1;
+ + +
| SALESMAN_ID | NAME |
+ + +
| 2000 | RAVI |
+ + +
1 row in set (0.03 sec)
Q-3
16
DBMS Lab with Mini Project
17
DBMS Lab with Mini Project
Q-4
CREATE VIEW HighestOrder2 AS SELECT s.SALESMAN_ID, s.NAME, o.ORD_DATE,
o.PURCHASE_AMT FROM SALESMAN s, ORDERS o WHERE (o.SALESMAN_ID =
s.SALESMAN_ID) and o.PURCHASE_AMT = (SELECT MAX(PURCHASE_AMT) FROM
ORDERS);
Query OK, 0 rows affected (0.08 sec)
EXPERIMENT - 3
Consider the schema for Movie Database:
ACTOR (Act_id, Act_Name, Act_Gender)
DIRECTOR (Dir_id, Dir_Name, Dir_Phone)
MOVIES (Mov_id, Mov_Title, Mov_Year, Mov_Lang, Dir_id)
MOVIE_CAST(Act_id, Mov_id, Role)
RATING (Mov_id, Rev_Stars)
Write SQL queries to
1. List the titles of all movies directed by „Hitchcock‟.
2. Find the movie names where one or more actors acted in two or more movies.
3. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN
operation).
4. Find the title of movies and number of stars for each movie that has at least one rating and
find the highest number of stars that movie received. Sort the result by movie title.
5. Update rating of all movies directed by „Steven Spielberg‟ to 5.
DBMS Lab with Mini Project
CREATE TABLE
CREATE TABLE ACTOR (
ACT_ID INT,
ACT_NAME VARCHAR (20),
ACT_GENDER CHAR (1),
PRIMARY KEY (ACT_ID));
MOV_ID INT,
DBMS Lab with Mini Project
DESC COMMAND
DESC ACTOR;
DESC DIRECTOR;
DESC MOVIES;
DESC MOVIE_CAST;
DESC RATING;
QUERIES:
1. List the titles of all movies directed by „Hitchcock‟
SELECT MOV_TITLE FROM MOVIES WHERE DIR_ID IN(SELECT DIR_ID FROM
DIRECTOR WHERE DIR_NAME = 'HITCHCOCK');
2. Find the movie names where one or more actors acted in two or more movies.
SELECT distinct MOV_TITLE FROM MOVIES M, MOVIE_CAST MV WHERE
M.MOV_ID=MV.MOV_ID AND ACT_ID IN(SELECT ACT_ID FROM MOVIE_CAST
GROUP BY ACT_ID HAVING COUNT(ACT_ID) > 1);
3. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN
operation)
SELECT ACT_NAME, MOV_TITLE, MOV_YEAR FROM ACTOR A JOIN MOVIE_CAST
C ON A.ACT_ID=C.ACT_ID JOIN MOVIES M ON C.MOV_ID=M.MOV_ID WHERE
M.MOV_YEAR NOT BETWEEN 2000 AND 2015;
4. Find the title of movies and number of stars for each movie that has at least one rating and find
the highest number of stars that movie received. Sort the result by movie title.
SELECT MOV_TITLE, COUNT(REV_STARS) FROM MOVIES INNER JOIN RATING
USING (MOV_ID) GROUP BY MOV_TITLE HAVING MAX(REV_STARS)>0 ORDER BY
MOV_TITLE;
5. Update rating of all movies directed by „Steven Spielberg‟ to 5.
UPDATE RATING SET REV_STARS=5 WHERE MOV_ID IN(SELECT MOV_ID FROM
MOVIES WHERE DIR_ID IN(SELECT DIR_ID FROM DIRECTOR WHERE DIR_NAME =
'STEVEN SPIELBERG'));
DBMS Lab with Mini Project
OUTPUT
Q1.
Q2.
SELECT distinct MOV_TITLE FROM MOVIES M, MOVIE_CAST MV WHERE
M.MOV_ID=MV.MOV_ID AND ACT_ID IN(SELECT ACT_ID FROM MOVIE_CAST GROUP BY
ACT_ID HAVING COUNT(ACT_ID) > 1);
+ +
| MOV_TITLE |
+ +
| BAHUBALI-2 |
| BAHUBALI-1 |
| AKASH |
+ +
Q3.
SELECT ACT_NAME, MOV_TITLE, MOV_YEAR FROM ACTOR A JOIN MOVIE_CAST CON
A.ACT_ID=C.ACT_ID JOIN MOVIES M ON C.MOV_ID=M.MOV_ID WHERE M.MOV_YEAR
NOT BETWEEN 2000 AND 2015;
+ + + +
| ACT_NAME | MOV_TITLE | MOV_YEAR |
+ + + +
| ANUSHKA | BAHUBALI-2 | 2017 |
+ + + +
DBMS Lab with Mini Project
Q4.
SELECT MOV_TITLE, COUNT(REV_STARS) FROM MOVIES INNER JOIN RATING USING
(MOV_ID) GROUP BY MOV_TITLE HAVING MAX(REV_STARS)>0 ORDER BY MOV_TITLE;
+ + +
| MOV_TITLE | COUNT(REV_STARS) |
+ + +
| AKASH | 1|
| BAHUBALI-1 | 1|
| BAHUBALI-2 | 1|
| WAR HORSE | 1|
+ + +
Q5. UPDATE RATING SET REV_STARS=5 WHERE MOV_ID IN(SELECT MOV_ID FROM
MOVIES WHERE DIR_ID IN(SELECT DIR_ID FROM DIRECTOR WHERE DIR_NAME =
'STEVEN SPIELBERG'));SELECT * FROM RATING;
+ + +
| MOV_ID | REV_STARS |
+ + +
| 1001 | 4|
| 1002 | 2|
| 1003 | 5|
| 1004 | 5|
+ + +
DBMS Lab with Mini Project
EXPERIMENT – 4
Consider the schema for College Database:
STUDENT (USN, SName, Address, Phone, Gender)
SEMSEC (SSID, Sem, Sec)
CLASS (USN, SSID)
COURSE (Subcode, Title, Sem, Credits)
IAMARKS (USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)
Write SQL queries to
1. List all the student details studying in fourth semester „C‟ section.
2. Compute the total number of male and female students in each semester and in each section.
3. Create a view of Test1 marks of student USN „1BI15CS101‟ in all Courses.
4. Calculate the FinalIA (average of best two test marks) and update the corresponding table for all
students.
5. Categorize students based on the following criterion:
If FinalIA = 17 to 20 then CAT = „Outstanding‟
If FinalIA = 12 to 16 then CAT = „Average‟
If FinalIA< 12 then CAT = „Weak‟ Give these details only for 8th semester A, B, and C section
students.
DBMS Lab with Mini Project
TABLE CREATION
CREATE TABLE STUDENT (
USN VARCHAR (10) PRIMARY KEY,
SNAME VARCHAR (20),
ADDRESS VARCHAR (20),
PHONE BIGINT,
GENDER CHAR (1));
(2AG16CS122','VINAYAKA','CHIKAMAGALUR',8800880011,'M');
DBMS Lab with Mini Project
QUERIES
Q1-List all the student details studying in fourth semester „C‟ section.
SELECT S.*, SS.SEM, SS.SEC FROM STUDENT S, SEMSEC SS, CLASS C WHERE S.USN
= C.USN AND SS.SSID = C.SSID AND SS.SEM = 4 AND SS.SEc='C';
Q2-Compute the total number of male and female students in each semester and in each section.
SELECT SS.SEM, SS.SEC, S.GENDER, COUNT(S.GENDER) AS COUNT FROM STUDENT S,
SEMSEC SS, CLASS C WHERE S.USN = C.USN AND SS.SSID = C.SSID GROUP BY SS.SEM,
SS.SEC, S.GENDER ORDER BY SEM;
DBMS Lab with Mini Project
Q3- Create a view of Test1 marks of student USN „1BI15CS101‟ in all Courses.
DBMS Lab with Mini Project
TO RUN
MYSQL> DELIMITER ;
MYSQL> CALL CALFA1();
END) AS CAT
DBMS Lab with Mini Project
Q2.
SELECT SS.SEM, SS.SEC, S.GENDER, COUNT(S.GENDER) AS COUNT FROM
STUDENT S, SEMSEC SS, CLASS C WHERE S.USN = C.USN AND SS.SSID = C.SSID
GROUP BY SS.SEM, SS.SEC, S.GENDER ORDER BY SEM;
+ + + + +
| SEM | SEC | GENDER | COUNT |
+ + + + +
| 3 |A |M | 1|
| 3|B |F | 1|
| 3 |C |M | 1|
| 4 |A |F | 1|
| 4 |A |M | 1|
| 4|B |M | 1|
| 4 |C |M | 1|
| 7 |A |F | 1|
| 7 |A |M | 2|
DBMS Lab with Mini Project
| 8|A |F | 1|
| 8|A |M | 1|
DBMS Lab with Mini Project
| 8|B |F | 1|
| 8 |C |F | 1|
+ + + + +
Q3.
mysql> CREATE VIEW STU_TEST1_MARKS_VIEW AS SELECT TEST1, SUBCODE
FROM IAMARKS WHERE USN = '1RN13CS091';
Query OK, 0 rows affected (0.06 sec)
Q4.
delimiter # create
procedure calfa1() begin
update IAMARKS
SET FINALIA = (CASE WHEN GREATEST(TEST1,TEST2)!=GREATEST(TEST1,TEST3)
THEN GREATEST(TEST1,TEST2)+GREATEST(TEST1,TEST3)
ELSE
GREATEST(TEST1,TEST2)+GREATEST(TEST2,TEST3)
END)/2
WHERE FINALIA IS NOT NULL;
END#
DBMS Lab with Mini Project
TO RUN
mysql> DELIMITER ;
mysql> CALL CALFA1();
Query OK, 5 rows affected (0.13 sec)
Q5.
SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER,
-> (CASE
-> WHEN IA.FINALIA BETWEEN 17 AND 20 THEN 'OUTSTANDING'
-> WHEN IA.FINALIA BETWEEN 12 AND 16 THEN 'AVERAGE'
-> ELSE 'WEAK'
-> END) AS CAT
-> FROM STUDENT S, SEMSEC SS, IAMARKS IA, SUBJECT SUB
-> WHERE S.USN = IA.USN AND
-> SS.SSID = IA.SSID AND
-> SUB.SUBCODE = IA.SUBCODE AND
-> SUB.SEM = 8;
DBMS Lab with Mini Project
+ + + + + + +
| USN | SNAME | ADDRESS | PHONE | GENDER | CAT |
+ + + + + + +
| 1RN13CS091 | TEESHA | BENGALURU | 7712312312 | F | WEAK |
| 1RN13CS091 | TEESHA | BENGALURU | 7712312312 | F | WEAK |
| 1RN13CS091 | TEESHA | BENGALURU | 7712312312 | F | WEAK |
| 1RN13CS091 | TEESHA | BENGALURU | 7712312312 | F | WEAK |
| 1RN13CS091 | TEESHA | BENGALURU | 7712312312 | F | WEAK |
+ + + + + + +
DBMS Lab with Mini Project
EXPERIMENT – 5
TABLE CREATION
NOTE: Once DEPARTMENT and EMPLOYEE tables are created we must alter department table to
add foreign constraint MGRSSN using sql command
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSCSE06‘,‘NEHA‘,‘SN‘,‘BANGALORE‘,‘F‘, 800000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSACC01‘,‘AHANA‘,‘K‘,‘MANGALORE‘,‘F‘, 350000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSACC02‘,‘SANTHOSH‘,‘KUMAR‘,‘MANGALORE‘,‘M‘, 300000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSISE01‘,‘VEENA‘,‘M‘,‘MYSORE‘,‘M‘, 600000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSIT01‘,‘NAGESH‘,‘HR‘,‘BANGALORE‘,‘M‘, 500000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSCSE06‘,‘NEHA‘,‘SN‘,‘BANGALORE‘,‘F‘, 800000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSACC01‘,‘AHANA‘,‘K‘,‘MANGALORE‘,‘F‘, 350000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSACC02‘,‘SANTHOSH‘,‘KUMAR‘,‘MANGALORE‘,‘M‘, 300000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSISE01‘,‘VEENA‘,‘M‘,‘MYSORE‘,‘M‘, 600000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSIT01‘,‘NAGESH‘,‘HR‘,‘BANGALORE‘,‘M‘, 500000);
UPDATE COMMAND
QUERIES:
1. Make a list of all project numbers for projects that involve an employee whose last name is ‘Scott’,
either as a worker or as a manager of the department that controls the project.
SELECT DISTINCT P.PNO
FROM PROJECT P,DEPARTMENT D,EMPLOYEE E
WHERE P.DNO=D.DNO AND
D.MGRSSN=E.SSN AND
E.LNAME='SCOTT'
UNION
SELECT DISTINCT W.PNO
FROM WORKS_ON W,EMPLOYEE E1
WHERE E1.SSN=W.SSN AND
E1.LNAME='SCOTT';
DBMS Lab with Mini Project
2. Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10 percent raise.
3.Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as the maximum
salary, the minimum salary, and the average salary in this department.
4. Retrieve the name of each employee who works on all the projects Controlled by department
number 5 (use NOT EXISTS operator).
SELECT E.FNAME,E.LNAME
FROM EMPLOYEE E
WHERE NOT EXISTS(SELECT PNO FROM PROJECT WHERE DNO=5
AND PNO NOT IN (SELECT PNO FROM WORKS_ON WHERE E.SSN=SSN));
5. For each department that has more than five employees, retrieve the department number and the
number of its employees who are making more than Rs. 6,00,000.
OUTPUT
1. Make a list of all project numbers for projects that involve an employee whose last name is
„Scott‟, either as a worker or as a manager of the department that controls the project. + ---- +
| PNO |
+ +
| 103 |
| 102 |
+-----+
2.Show the resulting salaries if every employee working on the „IoT‟ project is given a 10
percent raise
+ + + +
| FNAME | LNAME | INCR_SAL |
+ + + +
| JAMES | SMITH | 550000.0 |
| HEARN | BAKER | 770000.0 |
| PAVAN | HEGDE | 715000.0 |
+ + + +
3. Find the sum of the salaries of all employees of the „Accounts‟ department, as well as
the maximum salary, the minimum salary, and the average salary in this department
+ + + + +
| SUM(E.SALARY) | MAX(E.SALARY) | MIN(E.SALARY) | AVG(E.SALARY) |
+ + + + +
| 650000 | 350000 | 300000 | 325000.0000 | +---------------+---------------+------------
+ +
4. Retrieve the name of each employee who works on all the projects controlledby
department number 5 (use NOT EXISTS operator).
+ + +
| FNAME | LNAME |
+ + +
| JAMES | SMITH |
+ + +
For each department that has more than five employees, retrieve the department numberand the
number of its employees who are making more than Rs. 6,00,000.
+ + +
| DNO | No. oF Employees |
+ + +
|5 | 3|
+ + +