DBMS LAB -22MCAL27

1. COLLEGE LIBRARY DATABASE

Create the following tables with properly specifying Primary keys, foreign keys and solve

the following queries.

BRANCH (Branchid, Branchname, HOD)

STUDENT (USN, Name, Address, Branchid, sem)

BOOK (Bookid, Bookname, Authorid, Publisher, Branchid)

AUTHOR (Authorid, Authorname, Country, age)

BORROW (USN, Bookid, Borrowed_Date)

Execute the following Queries:

i. List the details of Students who are all studying in 2nd sem MCA.

ii. List the students who are not borrowed any books.

iii.Display the USN, Student name, Branch_name, Book_name, Author_name, Books_Borrowed_Date

of 2nd sem MCA Students who borrowed books.

iv. Display the number of books written by each Author.

v. Display the student details who borrowed more than two books.

vi. Display the student details who borrowed books of more than one Author.

vii. Display the Book names in descending order of their names.

viii. List the details of students who borrowed the books which are all published by the same

Publisher.

1
DBMS LAB -22MCAL27

SCHEMA DIAGRAM

ER DIAGRAM

2
DBMS LAB -22MCAL27

BRANCH (BRANCHID, BRANCHNAME, HOD)

CREATE TABLE BRANCH (BRANCHID INT PRIMARY KEY,

BRANCHNAME CHAR (15),
HOD CHAR (15));

INSERT INTO BRANCH VALUES (1, 'MCA', 'Dr. RAJU');

INSERT INTO BRANCH VALUES (2, 'CS', 'Prof. PRAVEEN');
INSERT INTO BRANCH VALUES (3, 'EC', 'Dr. KRUPA R');
INSERT INTO BRANCH VALUES (4, 'MCA', 'Dr. KSN');
INSERT INTO BRANCH VALUES (5, 'CV', 'Prof. Raj');

SELECT * FROM BRANCH;

AUTHOR (AUTHORID, AUTHORNAME, COUNTRY, AGE)

CREATE TABLE AUTHOR (AUTHORID INT PRIMARY KEY,

AUTHORNAME CHAR (15),
COUNTRY CHAR (10),
AGE INT);
INSERT INTO AUTHOR VALUES (444, 'RAMKRISHNAN', 'INDIA', 56);
INSERT INTO AUTHOR VALUES (555, 'ELMASRI', 'US', 75);
INSERT INTO AUTHOR VALUES (666, 'HERBERT', 'CANADA', 60);
INSERT INTO AUTHOR VALUES (777, 'KOTUR', 'INDIA', 58);

SELECT * FROM AUTHOR;

3
DBMS LAB -22MCAL27

STUDENT (USN, NAME, ADDRESS, BRANCHID, SEM)

CREATE TABLE STUDENT (USN VARCHAR (10) PRIMARY KEY,

NAME CHAR (15),
ADDRESS VARCHAR (15),
BRANCHID INT,
SEM INT,
FOREIGN KEY (BRANCHID) REFERENCES BRANCH (BRANCHID));

INSERT INTO STUDENT VALUES ('2JI20MC001', 'AKASH', 'KHANAPUR', 1, 2);

INSERT INTO STUDENT VALUES ('2JI20MC002', 'VINAY', 'BELAGAVI', 4, 2);
INSERT INTO STUDENT VALUES ('2JI20CS005', 'VIRAT', 'MUMBAI', 2, 6);
INSERT INTO STUDENT VALUES ('2JI20CV045', 'RAHUL', 'PUNE', 5, 7);
INSERT INTO STUDENT VALUES ('2JI19MC003', 'RAVI', 'HUBBALI', 2, 6);

SELECT * FROM STUDENT;

BOOK (BOOKID, BOOKNAME, AUTHORID, PUBLISHER, BRANCHID)

CREATE TABLE BOOK (BOOKID VARCHAR (5) PRIMARY KEY,

BOOKNAME CHAR (15),
AUTHORID INT,
PUBLISHER CHAR (15),
BRANCHID INT,
FOREIGN KEY (AUTHORID) REFERENCES AUTHOR (AUTHORID),
FOREIGN KEY (BRANCHID) REFERENCES BRANCH (BRANCHID));

INSERT INTO BOOK VALUES ('B01', 'JAVA', 666, 'TATA MCGRAW', 1);
INSERT INTO BOOK VALUES ('B02', 'DBMC', 555, 'TATA MCGRAW', 1);
INSERT INTO BOOK VALUES ('B03', 'DBMS', 555, 'TATA MCGRAW', 2);
INSERT INTO BOOK VALUES ('B04', 'C++', 777, 'PH', 4);
INSERT INTO BOOK VALUES ('B05', 'CONSTRUCT', 444, 'PH', 5);

4
DBMS LAB -22MCAL27

SELECT * FROM BOOK;

BORROW (USN, BOOKID, BORROWED_DATE)

CREATE TABLE BORROW (USN VARCHAR (10),

BOOKID VARCHAR (5),
BORROWDATE DATE,
FOREIGN KEY (USN) REFERENCES STUDENT (USN),
FOREIGN KEY (BOOKID) REFERENCES BOOK (BOOKID));

INSERT INTO BORROW VALUES ('2JI20MC001','B01','30-JAN-2021');

INSERT INTO BORROW VALUES ('2JI20MC001','B02','30-JAN-2021');
INSERT INTO BORROW VALUES ('2JI20MC002','B03','5-FEB-2020');
INSERT INTO BORROW VALUES ('2JI20CV045','B04','22-DEC-2019');
INSERT INTO BORROW VALUES ('2JI20CS005','B05','12-OCT-2019');
INSERT INTO BORROW VALUES ('2JI20MC001','B05','01-JUL-2020');

SELECT * FROM BORROW;

5
DBMS LAB -22MCAL27

QUERY 1: LIST THE DETAILS OF STUDENTS WHO ARE ALL STUDYING IN 2ND SEM MCA.

SELECT *
FROM STUDENT S, BRANCH B
WHERE S. BRANCHID=B.BRANCHID AND
S.SEM = 2 AND
B.BRANCHNAME = 'MCA';

QUERY 2: LIST THE STUDENTS WHO ARE NOT BORROWED ANY BOOKS.

SELECT *
FROM STUDENT S
WHERE S.USN NOT IN (SELECT B.USN
FROM BORROW B);

6
 DBMS LAB -22MCAL27

QUERY 3: DISPLAY THE USN, STUDENT NAME, BRANCH_NAME, BOOK_NAME, AUTHOR_NAME,

BOOKS_BORROWED_DATE OF 2ND SEM MCA STUDENTS WHO BORROWED BOOKS.

SELECT S.USN, S.NAME, BR.BRANCHNAME, BK.BOOKNAME, A.AUTHORNAME,BW.BORROWDATE

FROM STUDENT S, BRANCH BR, BOOK BK, AUTHOR A, BORROW BW
WHERE S.BRANCHID= BR.BRANCHID AND
S.BRANCHID = BK.BRANCHID AND
A.AUTHORID = BK.AUTHORID AND
BW.USN = S.USN AND
BK.BOOKID = BW.BOOKID AND
S.SEM = 2 AND
BR.BRANCHNAME = 'MCA';

QUERY 4: DISPLAY THE NUMBER OF BOOKS WRITTEN BY EACH AUTHOR.

SELECT A.AUTHORID, A.AUTHORNAME, COUNT (DISTINCT BK.BOOKID) AS NO_OF_BOOKS

FROM AUTHOR A, BOOK BK
WHERE A.AUTHORID= BK.AUTHORID
GROUP BY A.AUTHORID, A.AUTHORNAME;

7
 DBMS LAB -22MCAL27

QUERY 5: DISPLAY THE STUDENT DETAILS WHO BORROWED MORE THAN TWO BOOKS.

SELECT S.USN, S.NAME

FROM STUDENT S, BORROW BW
WHERE S.USN = BW.USN
GROUP BY S.USN, S.NAME
HAVING COUNT (BW.BOOKID)>2;

QUERY 6: DISPLAY THE STUDENT DETAILS WHO BORROWED BOOKS OF MORE THAN ONE AUTHOR.

SELECT S.USN, S.NAME, COUNT (BK.AUTHORID) AS NO_OF_AUTHOR

FROM STUDENT S, BORROW BW, BOOK BK
WHERE S.USN = BW.USN AND
BK.BOOKID = BW.BOOKID
GROUP BY S.USN, S.NAME
HAVING COUNT (BK.AUTHORID)>1;

QUERY 7: DISPLAY THE BOOK NAMES IN DESCENDING ORDER OF THEIR NAMES.

SELECT BOOKNAME
FROM BOOK
ORDER BY BOOKNAME DESC;

8
 DBMS LAB -22MCAL27

QUERY 8: LIST THE DETAILS OF STUDENTS WHO BORROWED THE BOOKS WHICH ARE ALL
PUBLISHED BY THE SAME PUBLISHER.

SELECT S.USN, S.NAME, COUNT (BK.PUBLISHER) AS SAME_PUBLISHER

FROM STUDENT S, BOOK BK, BORROW BW
WHERE S.USN=BW.USN AND
BK.BOOKID = BW.BOOKID
GROUP BY S.USN, S.NAME
HAVING COUNT (BK.PUBLISHER)>1;

9
DBMS LAB -22MCAL27

2. STUDENT DATABASE
Consider the following schema:

STUDENT (USN, NAME, DATE_OF_BIRTH, BRANCH, MARK1, MARK2, MARK3, TOTAL, GPA)

Execute the following queries:

i. Update the column total by adding the columns mark1, mark2, mark3.
ii. Find the GPA score of all the students.
iii. Find the students who born on a particular year of birth from the date_of_birth column.
iv. List the students who are studying in a particular branch of study.
v. Find the maximum GPA score of the student branch-wise.
vi. Find the students whose name starts with the alphabet “S”.
vii. Find the students whose name ends with the alphabets “AR”.
viii. Delete the student details whose USN is given as 1001.

SCHEMA DIAGRAM
STUDENT

USN NAME DOB BRANCH MARK1 MARK2 MARK3 TOTAL GPA

ER DIAGRAM:

1
DBMS LAB -22MCAL27

CREATE TABLE STUDENTLAB

(USN VARCHAR (10) PRIMARY KEY,
NAME VARCHAR (15),
DATE_OF_BIRTH DATE NULL,
BRANCH CHAR (15) NOT NULL,
MARKS1 INTEGER NOT NULL,
MARKS2 INTEGER NOT NULL,
MARKS3 INTEGER NOT NULL,
TOTAL INTEGER,
GPA REAL);

INSERT INTO STUDENTLAB VALUES ('2JI150MC01','GAURAV','12-JUL-1999','MCA',75,86,72,NULL,NULL);

INSERT INTO STUDENTLAB VALUES ('2JI150CC10','JAI','02-JAN-1998','COMM',75,86,70,NULL,NULL) ;

INSERT INTO STUDENTLAB VALUES ('2JI150SC09','DEEPAK','12-DEC-1999','SCIE',72,86,72,NULL,NULL) ;

INSERT INTO STUDENTLAB VALUES ('2JI180MC01','ABHI','27-FEB-1996','ARTS',80,86,72,NULL,NULL);

INSERT INTO STUDENTLAB VALUES ('2JI150MC45','SAI','15-AUG-1999','MCA',75,86,50, NULL,NULL);

INSERT INTO STUDENTLAB VALUES ('2JI150ME47','KEDAR','15-JUL-1998','ME',85,86,90, NULL,NULL);

SELECT * FROM STUDENTLAB;

2
DBMS LAB -22MCAL27

QUERY 1: UPDATE THE COLUMN TOTAL BY ADDING THE COLUMNS MARK1, MARK2, MARK3.

UPDATE STUDENTLAB
SET TOTAL = MARKS1 + MARKS2 + MARKS3;

SELECT * FROM STUDENTLAB;

QUERY 2: FIND THE GPA SCORE OF ALL THE STUDENTS.

UPDATE STUDENTLAB
SET GPA = (TOTAL/3.0);

SELECT * FROM STUDENTLAB;

3
DBMS LAB -22MCAL27

QUERY 3: FIND THE STUDENTS WHO BORN ON A PARTICULAR YEAR OF BIRTH FROM THE
DATE_OF_BIRTH COLUMN.
SELECT USN, NAME, DATE_OF_BIRTH
FROM STUDENTLAB
WHERE DATE_OF_BIRTH LIKE '%99';

QUERY 4: LIST THE STUDENTS WHO ARE STUDYING IN A PARTICULAR BRANCH OF STUDY.
SELECT USN, NAME, BRANCH
FROM STUDENTLAB
WHERE BRANCH = 'MCA';

QUERY 5: FIND THE MAXIMUM GPA SCORE OF THE STUDENT BRANCH-WISE.

SELECT USN, BRANCH, MAX (ROUND(GPA)) AS MAX_GPA
FROM STUDENTLAB
GROUP BY USN, BRANCH
ORDER BY MAX_GPA DESC ;

4
DBMS LAB -22MCAL27

QUERY 6: FIND THE STUDENTS WHOSE NAME STARTS WITH THE ALPHABET “S”.

SELECT USN, NAME

FROM STUDENTLAB
WHERE NAME LIKE 'S%';

QUERY 7: FIND THE STUDENTS WHOSE NAME ENDS WITH THE ALPHABETS “AR”.

SELECT NAME
FROM STUDENTLAB
WHERE NAME ='%AR';

QUERY 8: DELETE THE STUDENT DETAILS WHOSE USN IS GIVEN AS 1001.

DELETE
FROM STUDENTLAB
WHERE USN = '1001';

5
DBMS LAB -22MCAL27

3. ABC CUP DATABASE

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, pin code). 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.

EXECUTE THE FOLLOWING QUERIES:

I. Display the youngest player (in terms of age) name, team name, age in which he

belongs of the tournament.

II. List the details of the stadium where the maximum number of matches were played.

III. List the details of the player who is not a captain but got the man_of _match award at
least in two matches.

IV. Display the team details who won the maximum matches.

V. Display the team name where all its won matches played in the same stadium.

1
DBMS LAB -22MCAL27

SCHEMA DIAGRAM

ER DIAGRAM

2
DBMS LAB -22MCAL27

CREATE TABLE TEAM

(TID VARCHAR(10) PRIMARY KEY,
TNAME VARCHAR(10),
COACH VARCHAR(10),
CITY VARCHAR(10));

INSERT INTO TEAM VALUES ('T1','RCB','TOM','BANGALORE');

INSERT INTO TEAM VALUES ('T2','MI','JERRY','MUMBAI');
INSERT INTO TEAM VALUES ('T3','KKR','RAY','KOLKATA');
INSERT INTO TEAM VALUES ('T4','CSK','STEPHEN','CHENNAI');

CREATE TABLE PLAYER

(PID VARCHAR (4) PRIMARY KEY,
PNAME VARCHAR (10),
AGE INT,
TID VARCHAR (10) REFERENCES TEAM (TID) );

INSERT INTO PLAYER VALUES ('P1','VIRAT',35,'T1');

INSERT INTO PLAYER VALUES ('P2','GLENN',37,'T1');
INSERT INTO PLAYER VALUES ('P3','DINESH',33,'T1');
INSERT INTO PLAYER VALUES ('P4','SIRAJ',30,'T1');

INSERT INTO PLAYER VALUES ('P5','HARDIK',34,'T2');

INSERT INTO PLAYER VALUES ('P6','ROHIT',36,'T2');
INSERT INTO PLAYER VALUES ('P7','SURYA',38,'T2');
INSERT INTO PLAYER VALUES ('P8','BUMRAH',29,'T2');

3
DBMS LAB -22MCAL27

INSERT INTO PLAYER VALUES ('P9','SHREYAS',29,'T3');

INSERT INTO PLAYER VALUES ('P10','SUNIL',28,'T3');
INSERT INTO PLAYER VALUES ('P11','RINKU',32,'T3');
INSERT INTO PLAYER VALUES ('P12','PHIL',35,'T3');

INSERT INTO PLAYER VALUES ('P13','MS DHONI',40,'T4');

INSERT INTO PLAYER VALUES ('P14','JADEJA',25,'T4');
INSERT INTO PLAYER VALUES ('P15','RUTURAJ',38,'T4');
INSERT INTO PLAYER VALUES ('P16','SHIVAM',30,'T4');

4
DBMS LAB -22MCAL27

CREATE TABLE CAPTAIN

(CPID VARCHAR(4),
CTEAMID VARCHAR(4),
FOREIGN KEY (CPID)REFERENCES PLAYER(PID),
FOREIGN KEY (CTEAMID) REFERENCES TEAM(TID));

INSERT INTO CAPTAIN VALUES('P1','T1');

INSERT INTO CAPTAIN VALUES ('P5','T2');
INSERT INTO CAPTAIN VALUES ('P9', 'T3');
INSERT INTO CAPTAIN VALUES ('P13', 'T4');

CREATE TABLE STADIUM

( SID VARCHAR(4) PRIMARY KEY,
SNAME VARCHAR(4) ,
CITY VARCHAR(10),
AREA VARCHAR(10),
PINCODE INT);

INSERT INTO STADIUM VALUES ('S1','CS','BANGALORE', 'MG',55123);

INSERT INTO STADIUM VALUES ('S2','MODI','GUJURAT', 'GR',58564);
INSERT INTO STADIUM VALUES ('S3','ST','BELAGAVI', 'KANBARGI',59000);

5
DBMS LAB -22MCAL27

CREATE TABLE MATCHES

( MID VARCHAR(4) PRIMARY KEY,
TEAM_ID1 VARCHAR(4) REFERENCES TEAM(TID),
TEAM_ID2 VARCHAR(4) REFERENCES TEAM(TID),
MDATE DATE,
TIME VARCHAR(10),
SID VARCHAR(4) REFERENCES STADIUM(SID),
WINNING_TEAM_ID VARCHAR(4) REFERENCES TEAM(TID),
MAN_OF_MATCH VARCHAR(4) REFERENCES PLAYER(PID));

INSERT INTO MATCHES VALUES ('M01','T1','T2','12-JAN-2024','10AM','S1','T1','P1');

INSERT INTO MATCHES VALUES ('M02','T1','T3','13-JAN-2024','10AM','S1','T3','P9');
INSERT INTO MATCHES VALUES ('M03','T1','T4','14-JAN-2024','10AM','S1','T4','P16');
INSERT INTO MATCHES VALUES ('M04','T2','T3','15-JAN-2024','10AM','S2','T2','P5');
INSERT INTO MATCHES VALUES ('M05','T2','T4','16-JAN-2024','10AM','S3','T4','P12');
INSERT INTO MATCHES VALUES ('M06','T3','T4','17-JAN-2024','10AM','S3','T3','P10');
INSERT INTO MATCHES VALUES ('M02','T1','T3','13-JAN-2024','10AM','S1','T3','P9');
INSERT INTO MATCHES VALUES ('M07','T3','T1','17-JAN-2024','10AM','S3','T3','P10');

6
DBMS LAB -22MCAL27

QUERY 1: DISPLAY THE YOUNGEST PLAYER (IN TERMS OF AGE) NAME, TEAM NAME, AGE IN
WHICH HE BELONGS OF THE TOURNAMENT.

SOLUTION:

SELECT PNAME, TNAME, AGE

FROM PLAYER P, TEAM T
WHERE P.TID = T.TID AND P.AGE IN (SELECT MIN(AGE)
FROM PLAYER);

QUERY 2: LIST THE DETAILS OF THE STADIUM WHERE THE MAXIMUM NUMBER OF
MATCHES WERE PLAYED.
SOLUTION:

SELECT *
FROM (SELECT M.SID, S.SNAME , COUNT(M.MID) AS NO_OF_MATCHES
FROM MATCHES M, STADIUM S
WHERE M.SID = S.SID
GROUP BY M.SID, S.SNAME
ORDER BY NO_OF_MATCHES DESC)
WHERE ROWNUM=1;

7
DBMS LAB -22MCAL27

QUERY 3: LIST THE DETAILS OF THE PLAYER WHO IS NOT A CAPTAIN BUT GOT THE MAN_OF
_MATCH AWARD AT LEAST IN TWO MATCHES.

SOLUTION :

SELECT PID, PNAME

FROM PLAYER
WHERE PID NOT IN (SELECT CPID
FROM CAPTAIN)

AND PID IN (SELECT MAN_OF_MATCH

FROM MATCHES

GROUP BY MAN_OF_MATCH

HAVING COUNT (MAN_OF_MATCH)>=2);

QUERY 4: DISPLAY THE TEAM DETAILS WHO WON THE MAXIMUM MATCHES.

SOLUTION:

SELECT WINNING_TEAM_ID, COUNT (WINNING_TEAM_ID ) AS

NO_OF_MATCHES_WON

FROM MATCHES

GROUP BY WINNING_TEAM_ID

ORDER BY COUNT (WINNING_TEAM_ID );

8
DBMS LAB -22MCAL27

QUERY 5: DISPLAY THE TEAM NAME WHERE ALL ITS WON MATCHES PLAYED IN THE SAME
STADIUM.
SOLUTION:
SELECT *
FROM TEAM
WHERE TID IN
(SELECT WINNING_TEAM_ID
FROM MATCHES
GROUP BY WINNING_TEAM_ID
HAVING COUNT(*)=(SELECT MAX(COUNT(*))
FROM MATCHES
GROUP BY SID));

9
DBMS LAB -22MCAL27

4.ELECTION DATABASE

A country wants to conduct an election for the parliament. A country having many constituencies.
Each constituency is identified uniquely by Constituency_id, having the Name, belongs to a
state,Number_of_voters. A constituency can have many voters. Each voter is uniquely identified
by using Voter_id, having the Name, age, address (involves Houseno,city,state,pincode). Each
voter belongs to only one constituency. There are many candidates contesting in the election.
Each candidates are uniquely identified by using candidate_id, having Name, phone_no, age, state.
A candidate belongs to only one party.Thereare many parties. Each party is uniquely identified by
using Party_id, having Party_Name,Party_symbol. A candidate can contest from many
constituencies under a same party. A party can have many candidates contesting from different
constituencies. No constituency having the candidates from the same party. A constituency can
have many contesting candidates belongs to different parties. Each voter votes only one candidate
of his/her constituency.

Queries:
i. List the details of the candidates who are contesting from more than one constituencies which

are belongs to different states.

ii. Display the state name having maximum number of constituencies.

iii. Create a stored procedure to insert the tuple into the voter table by checking the voter age. If

voter‟s age is at least 18 years old, then insert the tuple into the voter else display the “Not an

eligible voter msg”.

iv. Create a stored procedure to display the number_of_voters in the specified constituency.

Where the constituency name is passed as an argument to the stored procedure.

v. Create a TRIGGER to UPDATE the count of “ Number_of_voters” of the respective constituency

in “CONSTITUENCY” table , AFTER inserting a tuple into the “VOTERS” table.

1
DBMS LAB -22MCAL27

CREATE TABLE CONSTITUENCY( CONSTID INT PRIMARY KEY,

NAME VARCHAR(10),

STATE VARCHAR(10),

NO_OF_VOTERS INT );

INSERT INTO CONSTITUENCY VALUES(1,'BANGALORE','KARNATAKA',2);

INSERT INTO CONSTITUENCY VALUES(2,'SHIMOGA','KARNATAKA',2);

INSERT INTO CONSTITUENCY VALUES(3,'HYDERABAD','TELANGANA',3);

SELECT * FROM CONSTITUENCY;

CREATE TABLE PARTY( PID INT PRIMARY KEY,

PNAME VARCHAR(10),

SYMBOL VARCHAR(10) );

INSERT INTO PARTY VALUES(1,'CONGRESS','PALM');

INSERT INTO PARTY VALUES(2,'BJP','LOTUS');

SELECT * FROM PARTY;

2
DBMS LAB -22MCAL27

CREATE TABLE CANDIDATE( CANDID INT PRIMARY KEY,

NAME VARCHAR(10),

PNO VARCHAR(10),

AGE INT,

STATE VARCHAR(10),

PID INT REFERENCES PARTY(PID) );

INSERT INTO CANDIDATE VALUES(1,'AMAR','1234567890',35,'KARNATAKA',1);

INSERT INTO CANDIDATE VALUES(2,'ANTONY','9874561230',37,'TELANGANA',2);

SELECT * FROM CANDIDATE;

CREATE TABLE VOTERS( VID INT PRIMARY KEY,

NAME VARCHAR(10),

AGE VARCHAR(10),

ADDRESS VARCHAR(20),

CONSTID INT REFERENCES CONSTITUENCY(CONSTID),

CANDID INT REFERENCES CANDIDATE(CANDID) );

INSERT INTO VOTERS VALUES(1,'ABHI',20,'KARNATAKA',1,1);

INSERT INTO VOTERS VALUES(2,'ARIF',20,'KARNATAKA',2,2);

INSERT INTO VOTERS VALUES(3,'MANOJ',18,'KARNATAKA',1,2);

3
DBMS LAB -22MCAL27

SELECT * FROM VOTERS;

CREATE TABLE CONTEST( CONSTID INT REFERENCES CONSTITUENCY(CONSTID),

CANDID INT REFERENCES CANDIDATE(CANDID) );

INSERT INTO CONTEST VALUES(1,1);

INSERT INTO CONTEST VALUES(2,2);

INSERT INTO CONTEST VALUES(3,1);

SELECT * FROM CONTEST;

4
DBMS LAB -22MCAL27

QUERY 1: LIST THE DETAILS OF THE CANDIDATES WHO ARE CONTESTING FROM MORE THAN
ONE CONSTITUENCIES WHICH ARE BELONGS TO DIFFERENT STATES.
SELECT *

FROM CANDIDATE

WHERE CANDID IN ( SELECT CANDID

FROM CONTEST CT, CONSTITUENCY CS

WHERE CT.CONSTID = CS.CONSTID

GROUP BY CANDID

HAVING COUNT(DISTINCT(STATE))>1 );

QUERY 2: DISPLAY THE STATE NAME HAVING MAXIMUM NUMBER OF CONSTITUENCIES.

SELECT STATE FROM CONSTITUENCY

GROUP BY STATE

HAVING COUNT(STATE) = ( SELECT MAX(COUNTS)

FROM ( SELECT COUNT(*) COUNTS

FROM CONSTITUENCY

GROUP BY STATE) COUNTER );

5
DBMS LAB -22MCAL27

QUERY 3: CREATE A STORED PROCEDURE TO INSERT THE TUPLE INTO THE VOTER TABLE BY
CHECKING THE VOTER AGE. IF VOTER'S AGE IS AT LEAST 18 YEARS OLD, THEN INSERT THE TUPLE
INTO THE VOTER ELSE DISPLAY THE "NOT AN ELIGIBLE VOTER MSG".

CREATE OR REPLACE PROCEDURE CHECKAGE(ID IN INT, AGE1 IN INT)

AS

BEGIN

IF AGE1>=18 THEN

INSERT INTO VOTERS (VID, AGE) VALUES (ID, AGE1);

ELSE

DBMS_OUTPUT.PUT_LINE ('NOT AN ELIGIBLE VOTER');

END IF;

END CHECKAGE;

1. EXECUTE CHECKAGE(12,18);

2. EXECUTE CHECKAGE(14,10);

6
DBMS LAB -22MCAL27

QUERY 4: CREATE A STORED PROCEDURE TO DISPLAY THE NUMBER_OF_VOTERS IN THE

SPECIFIED CONSTITUENCY WHERE THE CONSTITUENCY NAME IS PASSED AS AN ARGUMENT TO
THE STORED PROCEDURE.

CREATE OR REPLACE PROCEDURE GET_VOTER(CNAME IN VARCHAR) IS

VOTER_COUNT NUMBER;

BEGIN

SELECT NO_OF_VOTERS INTO VOTER_COUNT

FROM CONSTITUENCY

WHERE NAME = CNAME AND ROWNUM=1;

DBMS_OUTPUT.PUT_LINE('NUMBER OF VOTERS IN ' || CNAME || ' IS ' || VOTER_COUNT);

END;

7
DBMS LAB -22MCAL27

QUERY 5: CREATE A TRIGGER TO UPDATE THE COUNT OF "NUMBER_OF_VOTERS" OF THE

RESPECTIVE CONSTITUENCY IN "CONSTITUENCY" TABLE , AFTER INSERTING A TUPLE INTO THE
"VOTERS" TABLE.

CREATE OR REPLACE TRIGGER COUNT

AFTER INSERT ON VOTERS

FOR EACH ROW

BEGIN

UPDATE CONSTITUENCY

SET NO_OF_VOTERS = NO_OF_VOTERS + 1

WHERE CONSTID =:NEW.CONSTID;

END;

INSERT INTO VOTERS VALUES(4,'AKSHAY',22,'KAR',1,1);

SQL> 1 row inserted

SELECT * FROM CONSTITUENCY;

Note: Voter’s count will be updated.

8
DBMS LAB -22MCAL27

5. TOURISM

Design an ER-diagram for the following scenario, Convert the same into a relational
model, normalize Relations into a suitable Normal form and then solve the following
queries. A country can have many Tourist places . Each Tourist place is identified by using
tourist_place_id, having a name, belongs to a state, Number of kilometers away from the
02.03.2021 updated 52/ 104 capital city of that state,history. There are many Tourists
visits tourist places every year. Each tourist is identified uniquely by using Tourist_id,
having a Name, age, Country and multiple emailids. A tourist visits many Tourist places,
it is also required to record the visted_date in the database. A tourist can visit a Tourist
place many times at different dates. A Tourist place can be visited by many tourists either
in the same date or at different dates.

Queries:

1. List the state name which is having maximum number of tourist places.
2. List details of Tourist place where maximum number of tourists visited.
3. List the details of tourists visited all tourist places of the state “KARNATAKA”.
4. Display the details of the tourists visited at least one tourist place of the state, but
visited all states tourist places.
5. Display the details of the tourist place visited by the tourists of all country.

1
DBMS LAB -22MCAL27

SCHEMA DIAGRAM

ER DIAGRAM

2
DBMS LAB -22MCAL27

CREATE TABLE TOURIST (

TID INT PRIMARY KEY,
NAME VARCHAR(10),
AGE INT,
COUNTRY VARCHAR(10) );
INSERT INTO TOURIST VALUES (1,'AMAR',21,'INDIA');
INSERT INTO TOURIST VALUES (2,'AKBAR',22,'INDIA');
INSERT INTO TOURIST VALUES (3,'ANTONY',20,'UK');
INSERT INTO TOURIST VALUES (4,'RAM',25,'US');

SELECT * FROM TOURIST;

CREATE TABLE EMAIL(

TOURISTID INT REFERENCES TOURIST(TID),
EMAIL VARCHAR(20) );
INSERT INTO EMAIL VALUES(1,'AMAR@GMAIL.COM');
INSERT INTO EMAIL VALUES(1,'AMAR.P@GMAIL.COM');
INSERT INTO EMAIL VALUES(2,'AKBARAMAR@GMAIL.COM');
INSERT INTO EMAIL VALUES(3,'ANTONY@GMAIL.COM');
INSERT INTO EMAIL VALUES(4,'RAM@GMAIL.COM');

SELECT * FROM EMAIL;

3
DBMS LAB -22MCAL27

CREATE TABLE PLACE(

PID INT PRIMARY KEY,
NAME VARCHAR(10),
STATE VARCHAR(10),
KM INT,
HISTORY VARCHAR(15));

INSERT INTO PLACE VALUES(1,'JOGFALLS','KARNATAKA',400,'WATERFALLS');

INSERT INTO PLACE VALUES(2,'MYSORE','KARNATAKA',150,'PALACE');
INSERT INTO PLACE VALUES(3,'TAJMAHAL','DELHI',100,'BEAUTY');
INSERT INTO PLACE VALUES(4,'TIRUPATHI','AP',50,'TEMPLE');

SELECT * FROM PLACE;

CREATE TABLE VISIT(

TID INT REFERENCES TOURIST(TID),
PID INT REFERENCES PLACE(PID),
VISITDATE DATE );

INSERT INTO VISIT VALUES(1,1,'12-JAN-2022');

INSERT INTO VISIT VALUES(2,1,'12-JAN-2022');
INSERT INTO VISIT VALUES(3,1,'12-JAN-2022');
INSERT INTO VISIT VALUES(4,1,'12-JAN-2022');
INSERT INTO VISIT VALUES(2,2,'12-JAN-2022');
INSERT INTO VISIT VALUES(2,3,'12-JAN-2022');
INSERT INTO VISIT VALUES(2,4,'12-JAN-2022');

4
DBMS LAB -22MCAL27

SELECT * FROM VISIT;

QUERY 1: LIST THE STATE NAME WHICH IS HAVING THE MAXIMUM NUMBER OF TOURIST
PLACES.

SELECT STATE
FROM PLACE
GROUP BY STATE
HAVING COUNT(STATE) = ( SELECT MAX(COUNT(STATE))
FROM PLACE
GROUP BY STATE);

5
DBMS LAB -22MCAL27

QUERY 2:LIST DETAILS OF TOURIST PLACE WHERE MAXIMUM NUMBER OF TOURISTS VISITED.
SELECT *

FROM PLACE

WHERE PID IN (SELECT PID

FROM VISIT

GROUP BY PID

HAVING COUNT (PID) = (SELECT MAX (COUNT (PID))

FROM VISIT

GROUP BY PID));

QUERY 3: List the details of tourists visited all tourist places of the state “KARNATAKA”.
SELECT T.NAME,AGE, COUNTRY
FROM VISIT V, PLACE P, TOURIST T
WHERE V.PID = P.PID AND V.TID = T.TID AND STATE = 'KARNATAKA'
GROUP BY T.NAME, AGE, COUNTRY
HAVING COUNT(DISTINCT V.PID) = (
SELECT COUNT(*)
FROM PLACE
WHERE STATE = 'KARNATAKA' );

6
DBMS LAB -22MCAL27

QUERY 4: Display the details of the tourists visited at least one tourist place of the state, but
visited all states tourist places.
SELECT T.NAME, AGE, COUNTRY
FROM VISIT V, PLACE P, TOURIST T
WHERE V.PID = P.PID AND V.TID = T.TID
GROUP BY T.NAME, AGE, COUNTRY
HAVING COUNT(DISTINCT STATE) = (
SELECT COUNT(DISTINCT STATE)
FROM PLACE );

QUERY 5 : Display the details of the tourist place visited by the tourists of all country.

SELECT P.NAME, KM, HISTORY

FROM VISIT V, PLACE P, TOURIST T
WHERE V.PID = P.PID AND V.TID = T.TID
GROUP BY P.NAME, KM, HISTORY
HAVING COUNT(DISTINCT COUNTRY) = (
SELECT COUNT(DISTINCT COUNTRY)
FROM TOURIST );