SQL Practice Set 2
Sailors (sid, sname, rating, AGE)
Boats (bid, bname, colour )
Reserves (sid, bid, day)
1. Find the names of sailors who have reserved boat 103.
2. Find the names of sailors who have reserved a red boat.
3. Find the colour of boats reserved by Biswarup.
4. Find the names of sailors who have reserved at least one boat
5. Find the names of sailors who have reserved a red or a green boat.
6. Find the names of sailors who have reserved a red and a green boat.
7. Find the names of sailors who have reserved at least two boats.
8. Find the names of sailors with age over 20 who have not reserved a red boat.
9. Find the names of sailors who have reserved all boats.
[Link] the name of the sailors who have reserved all boats called interlake
[Link] the name of the sailors who have not reserved any boats.
branch (branch_name, branch_city, assets)
customer (customer_name, customer_street, customer_city)
loan (loan_number, branch_name, amount)
borrower (customer_name, loan_number)
account (account_number, branch_name, balance)
depositor (customer_name, account_number)
1. Find the average account balance at Perryridge Branch
2. Find the average aacount balance at each branch
3. Find the number of depositors for each branch
4. Find branches where the average account balance is more than 1200
5. Find average balance for each customer who lives in Harrison and has at least
three accounts
6. Find all customers who have at most one account at the perryridge branch
7. Find all customers who have at least two accounts at the perryridge branch
8. Find the names of all branches that have assets greater than those of at least
one branch located at Brooklyn
9. Find the names of all branches that have assets greater than those of all
branches located at Brooklyn
[Link] all customers who have both an account and a loan at the bank
[Link] all customers who have an account at all branches located at Brooklyn
EMP1 (eid, ename, age, salary)
Works (eid, did, pct_time)
Dept1 (did, budget, managerid)
1. Write SQL statements to delete all information about employees whose
salaries exceed that of the manager of one or more departments that they
work in.
2. Write SQL statements to find the name of employees who make second
highest salary.
Flights (flno, from, to, distance, departs, arrives)
Aircraft (aid, aname, cruisingrange)
Certified (eid, aid)
Employees (eid, ename, salary)
1. Find the names of pilots certified for some Boeing aircraft
2. Find the eids of employees who make the second highest salary
3. Find the name of pilots who can operate planes with a range greater than
3000 miles but not certified on any Boeing aircraft.
4. Find the Eids of employees who are certified for exactly three aircraft
book (book_id, Title)
book_author (book_id, authorname)
book_copies (book_id, branch_id, no_of_copy)
Library_branch(branch_id,branch_name)
Book_loan(book_id,branch_id,cardno)
borrower(name,city,cardno)
1. List the names of books borrowed by the student who belongs to "Kolkata".
2. Retrieve the name of students /borrowed by the students who does not
borrow any book.
3. Retrieve the name of library branch which has maximum number of books.
4. Retrieve the name of borrower who has borrowed at least 3 books and not
more than 10 books.
SHOP (shop_no, shop_name, owner_name)
CUSTOMER (cust_ no, cust_name, cust_addr)
SALE (cust_no, shop_no, item, Price)
1. List the name of the customers who purchase all items from one shop.
2. List the name of the customers who purchase items of maximum total value.
Hotel (Hno, Name, Address)
Room (Rno, Rtype, Hno, Price)
Booking (Hno, Gno, Rno, Dt_from, Dt_to)
Guest (Gno, GName, GAddress)
1. Find the names of all guests who are staying in hotels either in Kolkata or
Chennai
2. Find the total number of guests in Hotel Taj
3. List the number of rooms in each hotel
4. Find the room with the maximum price.
5. Find the hotel with maximum no. of rooms
6. Find the hotel with 2nd maximum no. of rooms