1.
Consider the following relation schema
Works(Pname,Cname,salary)
Lives(Pname,Street,City)
located_in (Cname, city)
Manager(Pname,Mgrname)
Write the SQL queries for the following
(i)Find the names of all persons who live in the city Bangalore.
SELECT Pname
FROM Lives
WHERE City = ‘Bangalore’;
(ii)Retrieve the names of all person of "Infosys" whose salary is between Rs .50000
SELECT Pname
FROM Works
WHERE Cname = ‘Infosys’
AND salary BETWEEN 50000;
iii)Find the names of all persons who lives and work in the same city
SELECT [Link]
FROM Works W
JOIN Lives L ON [Link] = [Link]
JOIN located_in LI ON [Link] = [Link]
WHERE [Link] = [Link];
iv)List the names of the people who work for “Tech M” along with the cities they live in.
SELECT [Link], [Link]
FROM Works W
JOIN Lives L ON [Link] = [Link]
WHERE [Link] = ‘Tech M’;
v)Find the average salary of “Infosys” persons
SELECT AVG(salary) AS average_salary
FROM Works
WHERE Cname = ‘Infosys’;
2. Consider the following COMPANY database (10 marks)
EMP(Name,SSN,Salary,SuperSSN,Dno)
DEPT(DNum,Dname,MgrSSN,Dno)
DEPT_LOC(Dnum,Dlocation)
DEPENDENT(ESSN,Dep_name,Sex)
WORKS_ON(ESSN,Pno,Hours)
PROJECT(Pname,Pnumber,Plocation,Dnum)
Write the SQL queries for the following
(i)Retrieve the name of the employee who works with same department as ravi
SELECT [Link]
FROM EMP E1
JOIN EMP E2 ON [Link] = [Link]
WHERE [Link] = 'Ravi' AND [Link] != 'Ravi';
ii)Retrieve the number of dependents for an employee “Ravi”
SELECT COUNT(*) AS NumberOfDependents
FROM EMP E
JOIN DEPENDENT D ON [Link] = [Link]
WHERE [Link] = ‘Ravi’;
iii)Retrieve the name of the managers working in location “DELHI”who has no female dependents
SELECT [Link]
FROM EMP E
JOIN DEPT D ON [Link] = [Link]
JOIN DEPT_LOC DL ON [Link] = [Link]
WHERE [Link] = ‘DELHI’
AND NOT EXISTS (
SELECT 1
FROM DEPENDENT DEP
WHERE [Link] = [Link] AND [Link] = ‘F’
);
iv)List female employees from Dno=20 earning more than 50000
SELECT Name
FROM EMP
WHERE Dno = 20 AND Salary > 50000 AND Sex = ‘F’;
v)List “CSE” department details
SELECT *
FROM DEPT
WHERE Dname = ‘CSE’;
3. Consider the Movie database:
MOVIE(Title,Director,Movieyear,Rating)
ACTORS(Actor,Age)
ACTS(Actor,Title)
DIRECTORS(Director,DirectorsAge)
Write the following queries using SQL
(i)List the movies directed by “STEVEN ” after 2018
SELECT Title
FROM MOVIE
WHERE Director = ‘STEVEN’ AND Movieyear > 2018;
ii) List all the actors who are over 40 year old and worked in all movies directed by “JOHN”.
SELECT [Link]
FROM ACTORS A
WHERE [Link] > 40
AND NOT EXISTS (
SELECT [Link]
FROM MOVIE M
WHERE [Link] = ‘JOHN’
AND NOT EXISTS (
SELECT [Link]
FROM ACTS AC
WHERE [Link] = [Link] AND [Link] = [Link]
);
iii) Find an actor who was casted in “maxim” movie.
SELECT Actor
FROM ACTS
WHERE Title = ‘maxim’;
iv) Find director and actor of the movie whose title starts with letter “A”.
SELECT [Link], [Link]
FROM MOVIE M
JOIN ACTS A ON [Link] = [Link]
WHERE [Link] LIKE ‘A%’;
v) List all female actresses.
SELECT Actor
FROM ACTRESSES;
Or can write as below
SELECT Actor
FROM ACTORS
WHERE Gender = ‘F’;
[Link] the sql queries for the following database schema:
Student(usn,name,branch,percentage)
Faculty(fid,fname,department,designation,salary)
Course(cid,cname,fid)
Enroll(cid,usn,grade)
(i)retrieve the names of all students enrolled for the course’CS_54”
SELECT [Link]
FROM Student S
JOIN Enroll E ON [Link] = [Link]
WHERE [Link] = ‘CS_54’;
(ii) list all the departments having an average salary of the faculties above Rs.10,000
SELECT [Link]
FROM Faculty F
GROUP BY [Link]
HAVING AVG([Link]) > 10000;
(iii) list the names of the students enrolled for the course’CS_51’ and having ‘B’ grade.
SELECT [Link]
FROM Student S
JOIN Enroll E ON [Link] = [Link]
WHERE [Link] = ‘CS_51’ AND [Link] = ‘B’;
5. Consider the following schemas:
sailor(sid,sname,rating,age)
Boat(bid,bname,colour)
Reserve(sid,bid,day)
(i) retrieve the sailor names that have reserved red and green boats.
SELECT [Link]
FROM sailor S
JOIN Reserve R1 ON [Link] = [Link]
JOIN Boat B1 ON [Link] = [Link] AND [Link] = ‘red’
JOIN Reserve R2 ON [Link] = [Link]
JOIN Boat B2 ON [Link] = [Link] AND [Link] = ‘green’;
(ii) retrieve the colors of boats reserved by raj
SELECT DISTINCT [Link]
FROM sailor S
JOIN Reserve R ON [Link] = [Link]
JOIN Boat B ON [Link] = [Link]
WHERE [Link] = ‘Raj’;
(iii) retrieve the sid’s of sailors with age over 20,who have not reserved a red boat.
SELECT [Link]
FROM sailor S
WHERE [Link] > 20
AND NOT EXISTS (
SELECT 1
FROM Reserve R
JOIN Boat B ON [Link] = [Link]
WHERE [Link] = [Link] AND [Link] = ‘red’
);
(iv) retrieve the names of sailors who have reserved all boats.
SELECT [Link]
FROM sailor S
WHERE NOT EXISTS (
SELECT [Link]
FROM Boat B
WHERE NOT EXISTS (
SELECT [Link]
FROM Reserve R
WHERE [Link] = [Link] AND [Link] = [Link]
);
(i) SELECT e.employee_name, [Link], [Link]
FROM Employee e
JOIN Works w ON e.employee_name = w.employee_name
WHERE w.company_name = 'First Bank Corporation'
AND [Link] > 10000;
(ii) SELECT e.employee_name
FROM Employee e
JOIN Works w ON e.employee_name = w.employee_name
WHERE w.company_name != 'First Bank Corporation';
(iii) SELECT e.employee_name
FROM Employee e
JOIN Works w ON e.employee_name = w.employee_name
WHERE [Link] > (
SELECT MAX([Link])
FROM Works w2
WHERE w2.company_name = 'Small Bank Corporation'
);
(iv) SELECT c.company_name
FROM Company c
JOIN Works w ON c.company_name = w.company_name
GROUP BY c.company_name
ORDER BY SUM([Link]) ASC
LIMIT 1;
(v) SELECT e.employee_name
FROM Employee e
JOIN Manages m ON e.employee_name = m.employee_name
JOIN Employee mgr ON m.manager_name = mgr.employee_name
WHERE [Link] = [Link]
AND [Link] = [Link];
(i) SELECT Dname, fname, Lname
FROM dept
JOIN emp ON [Link] = [Link];
(ii) SELECT [Link], [Link], COUNT(works_on.ESSN) AS NumOfEmployees
FROM project
JOIN works_on ON [Link] = works_on.Pno
GROUP BY [Link], [Link];
(iii) SELECT fname, Lname
FROM emp
WHERE NOT EXISTS (
SELECT [Link]
FROM project
WHERE [Link] = 5
EXCEPT
SELECT works_on.Pno
FROM works_on
WHERE works_on.ESSN = [Link]
);
(iv) SELECT fname, Lname
FROM emp
WHERE SSN NOT IN (
SELECT ESSN
FROM dependent
);
(v) SELECT gender, COUNT(*) AS NumOfEmployees
FROM emp
GROUP BY gender;
(i) SELECT [Link], [Link], [Link]
FROM Emp E
JOIN dept D ON [Link] = [Link]
WHERE [Link] = 'sports';
(ii) SELECT [Link], COUNT([Link]) AS num_employees, AVG([Link]) AS avg_salary
FROM Emp E
GROUP BY [Link];
(iii) SELECT [Link], [Link], [Link], [Link], [Link]
FROM project P
JOIN dept D ON [Link] = [Link]
JOIN Emp M ON [Link] = [Link];
(iv) SELECT [Link], [Link]
FROM Emp E
JOIN dependent D ON [Link] = [Link]
GROUP BY [Link], [Link], [Link]
HAVING COUNT(D.dependent_name) >= 2;
(v) SELECT [Link], [Link]
FROM Emp E
WHERE [Link] = 20 AND [Link] = 'F' AND [Link] > 50000;
(i) SELECT age
FROM Sailors
WHERE Sname LIKE 'A%A'
AND LENGTH(Sname) >= 3;
(ii) SELECT rating, MIN(age) AS youngest_age
FROM Sailors
WHERE age >= 18
GROUP BY rating
HAVING COUNT(Sid) >= 2;
(iii) SELECT Sname
FROM Sailors
WHERE Sid NOT IN (
SELECT DISTINCT Sid
FROM Reservers R
JOIN Boats B ON [Link] = [Link]
WHERE [Link] = 'red'
);
(iv) SELECT [Link], [Link], [Link], ([Link] + 1) AS incremented_rating
FROM Sailors S
JOIN (
SELECT Sid, day
FROM Reservers
GROUP BY Sid, day
HAVING COUNT(DISTINCT bid) >= 2
) R ON [Link] = [Link];
(i) SELECT
[Link],
[Link],
[Link],
[Link],
[Link]
FROM
PROJECT P
JOIN
DEPARTMENT D ON [Link] = [Link]
JOIN
EMPLOYEE E ON D.Mgr_ssn = [Link]
WHERE
[Link] = 'Stafford';
(ii) SELECT
[Link],
[Link]
FROM
EMPLOYEE E
JOIN
DEPENDENT D ON [Link] = [Link]
WHERE
[Link] = D.Dependent_name;
(iii) SELECT
[Link],
SUM([Link]) AS Total_Hours
FROM
PROJECT P
JOIN
WORKS_ON W ON [Link] = [Link]
GROUP BY
[Link];
(iv) SELECT
[Link],
[Link]
FROM
EMPLOYEE E
WHERE NOT EXISTS (
SELECT
[Link]
FROM
PROJECT P
JOIN
DEPARTMENT D ON [Link] = [Link]
WHERE
[Link] = 'Research'
AND NOT EXISTS (
SELECT
[Link]
FROM
WORKS_ON W
WHERE
[Link] = [Link]
AND
[Link] = [Link]
);
(i) SELECT [Link], [Link]
FROM works w
JOIN lives l ON [Link] = [Link]
WHERE [Link] = 'Wipro';
(ii) SELECT DISTINCT [Link]
FROM works w
WHERE [Link] != 'Infosys';
(iii) SELECT [Link]
FROM works w1
WHERE [Link] > ALL (SELECT [Link]
FROM works w2
WHERE [Link] = 'Oracle');
(iv) SELECT [Link]
FROM works w
JOIN lives l ON [Link] = [Link]
JOIN located_In li ON [Link] = [Link] AND [Link] = [Link];
(i) SELECT DISTINCT I.ITEM_NAME
FROM ITEM I
JOIN SALES S ON I.ITEM_NO = S.ITEM_NO
JOIN CUSTOMER C ON [Link] = [Link]
WHERE [Link] = 'Prasanth';
(ii) SELECT DISTINCT I.ITEM_NAME
FROM ITEM I
JOIN SUPPLY SP ON I.ITEM_NO = SP.ITEM_NO
WHERE SP.SUPPLY_DATE BETWEEN '2019-01-01' AND '2019-01-30';
(iii) SELECT [Link], [Link], [Link], [Link], [Link]
FROM CUSTOMER C
JOIN SALES S ON [Link] = [Link]
GROUP BY [Link], [Link], [Link], [Link], [Link]
HAVING SUM([Link]) > 5000;
(iv) SELECT
SUM([Link]) AS Total_Sales_Amount,
SUM(S.#ITEMS) AS Total_Items,
AVG([Link]) AS Average_Sale_Amount
FROM SALES S;
(v) SELECT [Link], [Link], [Link], [Link], [Link]
FROM CUSTOMER C
LEFT JOIN SALES S ON [Link] = [Link]
WHERE [Link] IS NULL;
(i) SELECT *
FROM RESORT
WHERE resortcity = 'Los Angeles';
(ii) SELECT *
FROM RESORT
WHERE numsuite > 30;
(iii) SELECT *
FROM VISITOR
ORDER BY firstname ASC;
(i) SELECT [Link], COUNT([Link]) AS NumberOfCourses
FROM STUDENT S
JOIN ENROLL E ON [Link] = [Link]
WHERE [Link] = 'John Smith' AND [Link] = 'WO9'
GROUP BY [Link];
(ii) SELECT [Link], B.book_isbn, T.book_title
FROM BOOK_ADOPTION B
JOIN TEXT T ON B.book_isbn = T.book_isbn
JOIN COURSE C ON [Link] = [Link]
WHERE [Link] = 'CS'
GROUP BY [Link], B.book_isbn, T.book_title
HAVING COUNT(B.book_isbn) > 2;
(iii) SELECT DISTINCT [Link]
FROM COURSE C
JOIN BOOK_ADOPTION B ON [Link] = [Link]
JOIN TEXT T ON B.book_isbn = T.book_isbn
WHERE [Link] = 'Pearson'
GROUP BY [Link]
HAVING COUNT(DISTINCT B.book_isbn) = (SELECT COUNT(DISTINCT B2.book_isbn)
FROM BOOK_ADOPTION B2
JOIN TEXT T2 ON B2.book_isbn = T2.book_isbn
WHERE [Link] IN (SELECT [Link]
FROM COURSE C2
WHERE [Link] = [Link])
AND [Link] = 'Pearson');
(i) SELECT [Link]
FROM EMP E
JOIN DEPT D ON [Link] = [Link]
WHERE [Link] = (
SELECT dno
FROM EMP
WHERE Salary = (SELECT MAX(Salary) FROM EMP)
);
(ii) SELECT [Link]
FROM EMP E1
WHERE [Link] >= (
SELECT MIN(Salary) + 10000
FROM EMP
);
(iii) CREATE VIEW ResearchEmployees AS
SELECT [Link] AS EmployeeName, [Link] AS SupervisorName, [Link]
FROM EMP E
JOIN EMP S ON [Link] = [Link]
JOIN DEPT D ON [Link] = [Link]
WHERE [Link] = 'Research';
(iv) CREATE VIEW ProjectSummary AS
SELECT [Link] AS ProjectName, [Link] AS ControllingDeptName,
COUNT([Link]) AS NumberOfEmployees,
SUM([Link]) AS TotalHoursWorked
FROM PROJECT P
JOIN DEPT D ON [Link] = [Link]
JOIN WORKS_ON W ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT([Link]) > 1;
(i) SELECT Title
FROM Movie
WHERE director = 'Hanson' AND Myear > 1997;
(ii) -- Find all actors
SELECT Actor AS Name, Aage AS Age
FROM Actors
UNION
-- Find all directors
SELECT Director AS Name, dage AS Age
FROM Directors;
(iii) SELECT [Link]
FROM Movie M
JOIN Acts A ON [Link] = [Link]
JOIN Actors AC ON [Link] = [Link]
WHERE [Link] = 'Coen' AND [Link] = 'Mc Dormand';
(iv) SELECT [Link], [Link]
FROM Directors D
JOIN Movie M ON [Link] = [Link]
JOIN Acts A ON [Link] = [Link]
JOIN Actors A ON [Link] = [Link]
WHERE [Link] < [Link];
(i) SELECT DISTINCT [Link]
FROM Sailors S1
JOIN RESERVE R1 ON [Link] = [Link]
JOIN BOATS B1 ON [Link] = [Link]
JOIN RESERVE R2 ON [Link] = [Link]
JOIN BOATS B2 ON [Link] = [Link]
WHERE [Link] = 'Red' AND [Link] = 'Green';
(ii) SELECT DISTINCT [Link]
FROM Sailors S
JOIN RESERVE R ON [Link] = [Link]
JOIN BOATS B ON [Link] = [Link]
WHERE [Link] > 20 AND [Link] = 'Black';
(iii) SELECT DISTINCT [Link]
FROM Sailors S
JOIN RESERVE R ON [Link] = [Link]
JOIN BOATS B ON [Link] = [Link]
WHERE [Link] = 'Green' AND [Link] = 'Monday';
(iv) SELECT COUNT(*)
FROM BOATS B
LEFT JOIN RESERVE R ON [Link] = [Link]
WHERE [Link] IS NULL;
(v) SELECT Sname
FROM Sailors
WHERE Rating = 10
AND Age = (SELECT MAX(Age) FROM Sailors WHERE Rating = 10);
(i) SELECT DISTINCT [Link]
FROM PROJECT p
JOIN EMPLOYEE e ON [Link] = [Link]
WHERE [Link] LIKE '%Scott%'
UNION
SELECT DISTINCT [Link]
FROM PROJECT p
JOIN DEPARTMENT d ON [Link] = [Link]
JOIN EMPLOYEE e ON [Link] = [Link]
WHERE [Link] LIKE '%Scott%';
(ii) SELECT [Link], [Link], [Link] * 1.10 AS NewSalary
FROM EMPLOYEE e
JOIN WORK_ON w ON [Link] = [Link]
JOIN PROJECT p ON [Link] = [Link]
WHERE [Link] = 'IOT';
(iii) SELECT
SUM([Link]) AS TotalSalary,
MAX([Link]) AS MaxSalary,
MIN([Link]) AS MinSalary,
AVG([Link]) AS AvgSalary
FROM EMPLOYEE e
JOIN DEPARTMENT d ON [Link] = [Link]
WHERE [Link] = 'accounts';
(iv) SELECT [Link]
FROM EMPLOYEE e
WHERE NOT EXISTS (
SELECT [Link]
FROM PROJECT p
WHERE [Link] = 5
AND NOT EXISTS (
SELECT [Link]
FROM WORK_ON w
WHERE [Link] = [Link]
AND [Link] = [Link]
);
(v) SELECT [Link], COUNT(*) AS NumberOfHighEarners
FROM EMPLOYEE e
WHERE [Link] > 600000
GROUP BY [Link]
HAVING COUNT(*) > 5;
(i) UPDATE FACULTY
SET Salary = Salary * 1.15;
(ii) SELECT Dept
FROM FACULTY
GROUP BY Dept
HAVING AVG(Salary) > 20000;
(iii) SELECT Fname
FROM FACULTY
WHERE Fname LIKE 'R%U';
(iv) SELECT Sname
FROM STUDENTS
JOIN ENROL ON [Link] = [Link]
JOIN COURSE ON [Link] = [Link]
WHERE [Link] = 'GS-53' AND [Link] = 'A';
(i) SELECT [Link], [Link]
FROM EMPLOYEE E
JOIN DEPARTMENT D ON [Link] = [Link]
WHERE [Link] = 'Research';
(ii) SELECT [Link]
FROM EMPLOYEE E
WHERE NOT EXISTS (
SELECT [Link]
FROM PROJECT P
WHERE [Link] = 5
AND NOT EXISTS (
SELECT [Link]
FROM WORKS-ON W
WHERE [Link] = [Link]
AND [Link] = [Link]
);
(iii) SELECT [Link]
FROM PROJECT P
JOIN WORKS-ON W ON [Link] = [Link]
JOIN EMPLOYEE E ON [Link] = [Link]
WHERE [Link] = 'Smith';
(iv) SELECT [Link]
FROM EMPLOYEE E
LEFT JOIN DEPENDENT D ON [Link] = [Link]
WHERE [Link] IS NULL;
(i) SELECT DISTINCT [Link]
FROM SAILORS S
JOIN RESERVES R ON [Link] = [Link]
JOIN BOATS B ON [Link] = [Link]
WHERE [Link] IN ('red', 'green');
(ii) SELECT [Link]
FROM SAILORS S1
WHERE [Link] > (SELECT MAX([Link]) FROM SAILORS S2 WHERE [Link] = 10);
(iii) SELECT [Link], [Link], [Link]
FROM SAILORS S1
WHERE [Link] > (SELECT [Link] FROM SAILORS S2 WHERE [Link] = 'Ramesh');
(i) SELECT Name
FROM MEMBERS
WHERE Designation = 'Professor' AND Age > 45;
(ii) SELECT DISTINCT B.Book_Title
FROM BOOKS B
JOIN RESERVES R ON [Link] = [Link]-id
JOIN MEMBERS M ON [Link]-id = [Link]-id
WHERE [Link] = 'Professor';
(iii) SELECT [Link]-id
FROM MEMBERS M
WHERE NOT EXISTS (
SELECT 1
FROM RESERVES R
JOIN BOOKS B ON [Link]-id = [Link]
WHERE [Link]-id = [Link]-id AND [Link]-price > 500
);
(iv) SELECT [Link]-Author, B.Book_Title
FROM BOOKS B
JOIN RESERVES R ON [Link] = [Link]-id
WHERE [Link] = '2017-05-27';
(v) SELECT [Link]
FROM MEMBERS M
WHERE NOT EXISTS (
SELECT [Link]
FROM BOOKS B
WHERE NOT EXISTS (
SELECT 1
FROM RESERVES R
WHERE [Link]-id = [Link]-id AND [Link]-id = [Link]
);
(i) SELECT DISTINCT [Link]
FROM Sailors S1
JOIN Reserve R1 ON [Link] = [Link]
JOIN Boats B1 ON [Link] = [Link]
JOIN Reserve R2 ON [Link] = [Link]
JOIN Boats B2 ON [Link] = [Link]
WHERE [Link] = 'red' AND [Link] = 'green';
(ii) SELECT COUNT(*)
FROM Boats B
WHERE [Link] NOT IN (SELECT DISTINCT Bid FROM Reserve);
(iii) SELECT [Link]
FROM Sailors S
JOIN Reserve R ON [Link] = [Link]
WHERE [Link] = 103;
(iv) SELECT [Link]
FROM Sailors S
WHERE NOT EXISTS (
SELECT [Link]
FROM Boats B
WHERE NOT EXISTS (
SELECT [Link]
FROM Reserve R
WHERE [Link] = [Link] AND [Link] = [Link]
);