See Our team
Wondering how we keep quality?
Got unsolved questions? Ask Questions
GATE
GMAT
CBSE
NCERT
Career
Interview
Railway
UPSC
NID
NIFT-UG
NIFT-PG
PHP
AJAX
JavaScript
Node Js
Shell Script
Research
DATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-1
1. Consider the following relations:
Student (snum: integer, sname: string, major: string, level: string, age: integer)
Class (name: string, meets at: string, room: string, fid: integer)
Enrolled (snum: integer, cname: string)
Faculty (fid: integer, fname: string, deptid: integer)
The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class. Level is a two character code with 4 different values (example: Junior: JR etc)
Write the following queries in SQL. No duplicates should be printed in any of the answers.
i. Find the names of all Juniors (level = JR) who are enrolled in a class taught by Prof. Harshith
ii. Find the names of all classes that either meet in room R128 or have five or more Students enrolled.
iii. Find the names of all students who are enrolled in two classes that meet at the same time.
iv. Find the names of faculty members who teach in every room in which some class is taught.
v. Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five.
1. Consider the following relations:
Student (snum: integer, sname: string, major: string, level: string, age: integer)
Class (name: string, meets_at: string, room: string, fid: integer)
Enrolled (snum: integer, cname: string)
Faculty (fid: integer, fname: string, deptid: integer)
The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class. Level is a two character code with 4 different values (Junior: JR, Senior:SR, Super Senior: SS, Fresher: FR)
CREATE TABLE STUDENT
(SNUM INTEGER NOT NULL,
SNAME VARCHAR (15),
MAJOR VARCHAR (10),
SLEVEL CHAR (2) NOT NULL,
AGE NUMBER (3),
PRIMARY KEY (SNUM));
CREATE TABLE FACULTY
(FID INTEGER NOT NULL,
FNAME VARCHAR (12) NOT NULL,
DEPTID NUMBER (3),
PRIMARY KEY (FID));
CREATE TABLE CLASS
(NAME VARCHAR (12) NOT NULL,
MEETS_AT VARCHAR (10),
ROOM VARCHAR (5),
FID INTEGER,
PRIMARY KEY (NAME),
FOREIGN KEY (FID) REFERENCES FACULTY (FID));
CREATE TABLE ENROLLED
(SNUM INTEGER NOT NULL,
CNAME VARCHAR (12) NOT NULL,
PRIMARY KEY (SNUM, CNAME),
FOREIGN KEY (SNUM) REFERENCES STUDENT (SNUM),
FOREIGN KEY (CNAME) REFERENCES CLASS (NAME));
INSERT INTO STUDENT VALUES (&SNUM, ‘&SNAME’, ‘&MAJOR’, ‘&SLEVEL’, &AGE);
INSERT INTO FACULTY VALUES (&FID, ‘&FNAME’, &DEPTID);
INSERT INTO CLASS VALUES (‘&CNAME’, ‘&MEETS_AT’, ‘&ROOM’, &FID);
INSERT INTO ENROLLED VALUES (&SNUM, ‘&CNAME’);
Write the following queries in SQL. No duplicates should be printed in any of the answers.
i) Find the names of all Juniors (level = JR) who are enrolled in a class taught by Prof. Harshith
SELECT DISTINCT S.SNAME
FROM STUDENT S, CLASS C, ENROLLED E, FACULTY F
WHERE S.SNUM = E.SNUM AND E.CNAME = C.NAME AND C.FID = F.FID AND
F.FNAME = ‘Prof. Harshith’ AND S.LEVEL = ‘JR’;
ii) Find the names of all classes that either meet in room R128 or have five or more Students enrolled.
SELECT C.NAME
FROM CLASS C
WHERE C.ROOM = ‘R128’
OR C.NAME IN (SELECT E.CNAME
FROM ENROLLED E
GROUP BY E.CNAME
HAVING COUNT (*) >= 5);
iii) Find the names of all students who are enrolled in two classes that meet at the same time.
SELECT DISTINCT S.SNAME
FROM STUDENT S
WHERE S.SNUM IN (SELECT E1.SNUM
FROM ENROLLED E1, ENROLLED E2, CLASS C1, CLASS C2
WHERE E1.SNUM = E2.SNUM AND E1.CNAME <> E2.CNAME
AND E1.CNAME = C1.NAME
AND E2.CNAME = C2.NAME AND C1.MEETS_AT = C2.MEETS_AT);
iv) Find the names of faculty members who teach in every room in which some class is taught.
SELECT DISTINCT F.FNAME
FROM FACULTY F
WHERE NOT EXISTS (( SELECT *
FROM CLASS C
EXCEPT
(SELECT C1.ROOM
FROM CLASS C1
WHERE C1.FID = F.FID));
v) Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five.
SELECT DISTINCT F.FNAME
FROM FACULTY F
WHERE 5 > (SELECT COUNT (E.SNUM)
FROM CLASS C, ENROLLED E
WHERE C.CNAME = E.CNAME
AND C.FID = F.FID);
Editors
Checkout our YouTube channel for video tutorials and learn more about Eduladder.
New updates
Similar Questions
Notes
Management And Entrepreneurship Unit-1DATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-4DATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-3ADVANCED DBMS 10CS751Intelligent Management Of Electrical Systems in IndustriDATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-5TeradataDATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-206is65 information system and management vtu notes unit-1DATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-1