EX.
NO : 04 Nested Queries
DATE:
[Link] the following relations
Department (dept_id, dept_name)
Student (rollno, name, gender, mark1, mark2, mar3, total, average, dept_id)
Staff (staff_id, name, designation, qualification, dept_id)
Tutor (rollno, staff_id)
1. Display the staff details who work in AI&DS department.
SELECT * FROM Staff
WHERE dept_id = (
SELECT dept_id FROM Department WHERE dept_name = 'AI&DS');
2. Display the student details who come under the tutor ship of the given staff name ‘X’.
SELECT * FROM Student
WHERE rollno IN (
SELECT rollno FROM Tutor WHERE staff_id = (
SELECT staff_id FROM Staff WHERE name = 'X'));
3. Display tutor name of the student who got the highest total marks.
SELECT name FROM Staff
WHERE staff_id = (
SELECT staff_id FROM Tutor WHERE rollno = ( SELECT rollno
FROM Student WHERE total = (SELECT MAX(total) FROM Student)));
4. Display dept name where students of the respective dept scores highest total than other
depts.
SELECT dept_name FROM Department
WHERE dept_id = (SELECT dept_id FROM Student
WHERE total = (SELECT MAX(total) FROM Student));
II. Consider the following relations
Employee (ssn, first_name, last_name, gender, designation, doj, address)
Employee-salary (ssn, basic_pay, DA, TA, pay)
Department (deptid, dname,mgr_name)
Employee-department (ssn, deptid)
1. Retrieve the doj, address of employees who work for ‘Research’ department
SELECT doj, address FROM Employee
WHERE ssn IN (
SELECT ssn FROM Employee_department
WHERE deptid = (
SELECT deptid FROM Department WHERE dname = 'Research'));
2. Retrieve all the information about employees working in ‘Research’ department
SELECT *
FROM Employee
WHERE ssn IN (
SELECT ssn
FROM Employee_department
WHERE deptid = (
SELECT deptid
FROM Department
WHERE dname = 'Research'
)
);
3. Retrieve the manager name of employee who receives salary less than 10000
SELECT mgr_name
FROM Department
WHERE deptid IN (
SELECT deptid
FROM Employee_department
WHERE ssn IN (
SELECT ssn
FROM Employee_salary
WHERE pay < 10000
)
);
III. Consider the following relation
Student(sid, stu_name, aadhaar, mobile, city)
Subject(scode, sub_name, credits)
Enroll(sid, scode)
Stu_activity(sid, event, prize)
1. Display names of the students who enrolled in 4 credit subjects.
SELECT stu_name
FROM Student
WHERE sid IN (
SELECT sid FROM Enroll
WHERE scode IN (
SELECT scode FROM Subject
WHERE credits = 4
)
);
2. Retrieve aadhaar details of the student who participated in any of the events.
SELECT aadhaar
FROM Student
WHERE sid IN (
SELECT sid
FROM Stu_activity);
3. Display the subject code for enrolled subject by the student who won prize in any of the
activity
SELECT scode
FROM Enroll
WHERE sid IN (
SELECT sid
FROM Stu_activity
WHERE prize IS NOT NULL);
4. Display student name and mobile number of the students who located in ‘Erode’ city and
enrolled 3 credit subject
SELECT stu_name, mobile
FROM Student
WHERE city = 'Erode'
AND sid IN (
SELECT sid FROM Enroll
WHERE scode IN (
SELECT scode FROM Subject WHERE credits = 3));
IV.
[Link]
SUM(Salary) AS Total_Salary,
MAX(Salary) AS Maximum_Salary,
MIN(Salary) AS Minimum_Salary,
AVG(Salary) AS Average_Salary
FROM Employee
WHERE DeptNo = (
SELECT DeptNo
FROM Department
WHERE DName = 'Accounts'
);
[Link] FName, Middle, LName
FROM Employee
WHERE DeptNo = 5;
[Link]
DName,
(SELECT COUNT(*) FROM Employee E
WHERE [Link] = [Link]) AS Employee_Count FROM Department D
WHERE (SELECT COUNT(*)
FROM Employee E
WHERE [Link] = [Link]) >= 2;
[Link] FName, Middle, LName
FROM Employee
WHERE BDate BETWEEN '1990-01-01' AND '1999-12-31';
[Link]
[Link], [Link], [Link],
(SELECT DName
FROM Department
WHERE DeptNo = [Link]) AS Department_Name
FROM Employee E;