EXPERIMENT-6
6A. Querying (using ANY, ALL, IN, NOTIN, UNION, UNION ALL, INTERSECT,
JOIN, Constraints etc.)
6B. Nested, Correlated subqueries.
Aim: To practice SQL queries using operators (e.g., ANY, ALL, IN), set operations (e.g.,
UNION, INTERSECT), joins, and constraints to ensure data integrity. It also focuses on
understanding and implementing nested and correlated subqueries.
PROCEDURE:
Question: Consider the following schema with three tables:
Sailors (sid: integer, sname: string, rating: integer, age: real)
Boats (bid: integer, bname: string, color: string)
Reserves (sid: integer, bid: integer, day: date)
Sailors Table Boats Table Reserves Table
sid sname rating age bid bname color sid bid day
22 Dustin 7 45 101 Interlake blue 22 101 1998-10-10
29 Brutus 1 33 102 Interlake red 22 102 1998-10-10
31 Lubber 8 55.5 103 Clipper green 22 103 1998-10-08
32 Andy 8 25.5 104 Marine red 22 104 1998-10-07
58 Rusty 10 35 31 102 1998-11-10
64 Horatio 7 35 31 103 1998-11-06
71 Zorba 10 16 31 104 1998-11-12
74 Horatio 9 40 64 101 1998-09-05
85 Art 3 25.5 64 102 1998-09-08
95 Bob 3 63.5 74 103 1998-09-08
Instances of Sailors, Boats and Reserves
1. Create the Tables
Sailors Table
CREATE TABLE Sailors (sid INTEGER PRIMARY KEY, sname VARCHAR(50), rating INTEGER,
age REAL);
Boats Table
CREATE TABLE Boats (bid INTEGER PRIMARY KEY, bname VARCHAR(50), color
VARCHAR(20));
Reserves Table
CREATE TABLE Reserves (sid INTEGER, bid INTEGER, day DATE, PRIMARY KEY (sid, bid, day),
FOREIGN KEY (sid) REFERENCES Sailors(sid), FOREIGN KEY (bid) REFERENCES Boats(bid));
2. Insert the records into tables (based on above data)
Insert records into Sailors Table
CREATE TABLE Sailors (sid INTEGER PRIMARY KEY, sname VARCHAR(50), rating INTEGER,
age REAL);
Insert records into Boats Table
CREATE TABLE Boats (bid INTEGER PRIMARY KEY, bname VARCHAR(50), color
VARCHAR(20));
Insert records into Reserves Table
CREATE TABLE Reserves (sid INTEGER, bid INTEGER, day DATE, PRIMARY KEY (sid, bid, day),
FOREIGN KEY (sid) REFERENCES Sailors(sid), FOREIGN KEY (bid) REFERENCES Boats(bid));
The following are the queries to practice using:
Using ANY
1. Retrieve the names of sailors who have reserved any boat
SELECT sname
FROM sailors
WHERE sid = ANY (SELECT sid FROM reserves);
2. Retrieve the details of sailors whose age is greater than the age of any other
sailor
SELECT *
FROM sailors
WHERE age > ANY (SELECT age FROM sailors);
3. List the names of boats reserved on any date after 1998-10-07
SELECT bname
FROM boats
WHERE bid = ANY (
SELECT bid FROM reserves WHERE day > '1998-10-07'
);
4. Find sailors whose ratings are higher than any sailor with an age above 50
SELECT sname
FROM sailors
WHERE rating > ANY (
SELECT rating FROM sailors WHERE age > 50
);
5. Identify boats that were reserved by any sailor with a rating of 10
SELECT bname
FROM boats
WHERE bid = ANY (
SELECT bid FROM reserves WHERE sid = ANY (
SELECT sid FROM sailors WHERE rating = 10
)
);
Using ALL
6. Find sailors whose age is greater than all sailors with a rating of 5
SELECT sname
FROM sailors
WHERE age > ALL (SELECT age FROM sailors WHERE rating = 5);
7. Find boats reserved on the earliest date across all reservations
SELECT bname
FROM boats
WHERE bid = ALL (SELECT bid FROM reserves WHERE day = (SELECT MIN(day) FROM
reserves));
8. Identify sailors whose rating is less than all sailors above the age of 50
SELECT sname
FROM sailors
WHERE rating < ALL (SELECT rating FROM sailors WHERE age > 50);
Using IN
9. Find the names of sailors who have reserved a red boat
SELECT sname
FROM sailors
WHERE sid IN (SELECT sid FROM reserves WHERE bid IN (SELECT bid FROM boats
WHERE color = 'red'));
10. Retrieve the details of boats reserved by "Dustin"
SELECT bname, color
FROM boats
WHERE bid IN (SELECT bid FROM reserves WHERE sid IN (SELECT sid FROM sailors
WHERE sname = 'Dustin'));
11. List sailors who have made at least one reservation
SELECT sname
FROM sailors
WHERE sid IN (SELECT sid FROM reserves);
Using NOT IN
12. Find sailors who have never made a reservation
SELECT sname
FROM sailors
WHERE sid NOT IN (SELECT sid FROM reserves);
13. Identify boats that have never been reserved
SELECT bname
FROM boats
WHERE bid NOT IN (SELECT bid FROM reserves);
14. List sailors whose rating is not shared by any other sailor
SELECT sname
FROM sailors
WHERE rating NOT IN (SELECT rating FROM sailors WHERE sname != sailors.sname);
Using UNION
15. Retrieve the IDs of sailors and boats involved in reservations
SELECT sid FROM sailors
UNION
SELECT bid FROM boats;
16. Find all unique names of sailors and boats
SELECT sname FROM sailors
UNION
SELECT bname FROM boats;
Using UNION ALL
17. Retrieve all IDs of sailors and boats involved in reservations, including duplicates
SELECT sid FROM sailors
UNION ALL
SELECT bid FROM boats;
18. Find all names of sailors and boats, including duplicates
SELECT sname FROM sailors
UNION ALL
SELECT bname FROM boats;
Using INTERSECT
19. Find IDs common to both sailors and boats
SELECT sid FROM sailors
INTERSECT
SELECT bid FROM boats;
20. Find names common to sailors and boats
SELECT sname FROM sailors
INTERSECT
SELECT bname FROM boats;
Using JOIN
21. Retrieve the names of sailors who reserved a red boat
SELECT sailors.sname, boats.bname, boats.color
FROM sailors
INNER JOIN reserves ON sailors.sid = reserves.sid
INNER JOIN boats ON reserves.bid = boats.bid
WHERE boats.color = 'red';
22. List sailors along with the details of boats they reserved
SELECT sailors.sname, boats.bname, reserves.day
FROM sailors
JOIN reserves ON sailors.sid = reserves.sid
JOIN boats ON reserves.bid = boats.bid;
Using Constraints
23. Create a constraint to ensure that boat names are unique
ALTER TABLE boats
ADD CONSTRAINT unique_bname UNIQUE (bname);
24. Enforce non-negative ratings for sailors
ALTER TABLE sailors
ADD CONSTRAINT positive_rating CHECK (rating >= 0);
25. Ensure no duplicate reservations
ALTER TABLE reserves
ADD CONSTRAINT no_duplicate_reservations UNIQUE (sid, bid, day);
