p7

“Join” Queries:
Q.1 Find the salary of Adam.
Ans- INPUT:
• SELECT Salary FROM Work WHERE Empname = 'Adam'; OUTPUT:

Q.2 Find the city where Brooks work.
Ans- INPUT:
• SELECT City FROM Employee JOIN Work ON Employee.Empname = Work.Empname WHERE Work.Empname = 'Brooks

Q.3 Display the sailor’s details whose boat is booked for 9th May, 98.
Ans- INPUT:
• SELECT S.* FROM Sailors S JOIN Reserves R ON S. Sid = R. Sid
WHERE R. Day = TO_DATE('09-May-1998', 'DD-MON-YYYY');
OUTPUT:

Q.3 Display the day of the ride and the sailor's name for boat 103.
Ans- INPUT:
• SELECT R. Day, S. Sname FROM Reserves R JOIN Sailors S ON R. Sid = S. Sid WHERE R. Bid = 103;
• OUTPUT:

Q.5 Display the sailor's name and age for the Red-colored 101 boat.
• SELECT S. Sname, S. Age
FROM Sailors S
JOIN Reserves R ON S. Sid = R. Sid
JOIN Boats B ON R. Bid = B. Bid
WHERE B. Color = 'Red' OR B. Bid = 101;
OUTPUT:

Q.6 Display the sailor details whose boat is never booked.
Ans- INPUT:
• SELECT S.* FROM Sailors S LEFT
JOIN Reserves R ON S. Sid = R. Sid WHERE
R. Sid IS NULL;
OUTPUT:

Q.7 Display the sailor's name that has a Red or Green Boat.
PAGE NO.41
Ans- INPUT:
• SELECT DISTINCT S. Sname
FROM Sailors S
JOIN Reserves R ON S. Sid = R. Sid JOIN Boats B ON R. Bid = B. Bid WHERE B. Color IN ('Red', 'Green');
OUTPUT:

Q.8 Display all sailor and boat details and who has an Interlake boat.
Ans- INPUT:
SELECT S., B. FROM Sailors S JOIN Reserves R ON S. Sid = R. Sid JOIN Boats B ON R. Bid = B. Bid WHERE B. B_name = 'Interlake';
OUTPUT:

Q.9 Display the sailor’s rating with boat details or the trip on 10th October, 98.
Ans- INPUT:
• SELECT S. Sid, S. Sname, S. Rating, B. Bid, B. B_name, B. Color
FROM Sailors S
JOIN Reserves R ON S. Sid = R. Sid
JOIN Boats B ON R. Bid = B. Bid
WHERE R. Day = TO_DATE ('10-Oct-1998', 'DD-MON-YYYY');
OUTPUT:

Q.10 Display the sailor's id and name with an age over 42 or who has Blue colored boat.
Ans- INPUT:
• SELECT S. Sid, S. Sname
FROM Sailors S
JOIN Reserves R ON S. Sid = R. Sid
JOIN Boats B ON R. Bid = B. Bid
WHERE S. Age > 42 OR B. Color = 'Blue';
OUTPUT:

Q.11 Display the name and rating of a sailor whose boat name is Clipper.
Ans- INPUT:
• SELECT S. Sname, S. Rating
FROM Sailors S
JOIN Reserves R ON S. Sid = R. Sid JOIN Boats B ON R. Bid = B. Bid WHERE B. B_name = 'Clipper';

OUTPUT:
PAGE NO.44

Q. 12 List products whose selling price is more than 500 and less than equal to 750.
Ans- INPUT:
• SELECT DSCR, Sell_Price FROM Product
WHERE Sell_Price > 500 AND Sell_Price <= 750;
OUTPUT:

Q.13 Describe the second-highest salary of a Work.
Ans- INPUT:

• SELECT MAX(Salary) AS Second_Highest_Salary PAGE NO.45
• PAGE NO.38PA
FROM Work WHERE Salary < (SELECT MAX(Salary) FROM Work);
OUTPUT:

Q.14 Display the date of travel and the sailor’s name, age between 35 and 65.
Ans- INPUT:
• SELECT R. Day AS Date_Of_Travel, S. Sname AS Sailor_Name
FROM Sailors S
JOIN Reserves R ON S. Sid = R. Sid
WHERE S. Age BETWEEN 35 AND 65;

OUTPUT:

Q.15 List all the employees working for “FBC”.
Ans- INPUT:
• SELECT E. Empname, E. Street, E. City
FROM Employee E
JOIN Work W ON E. Empname = W. Empname WHERE
W. Cmpname = 'FBC';
OUTPUT: