Tutorial 5 – SQL practice 1
Basic Queries
Feb.16, 2007
Solutions
Schema1:
Sailors (sid, sname, rating, age);
Boats (bid, bname, colour);
Reserves (sid, bid, day);
Schema2:
Student (snum, sname, major, standing, age);
Faculty (fid, fname, deptid);
Class (name, meets_at, room, fid);
Enrolled (snum, cname);
/% Not used for now
Schema3:
Emp (eid, ename, age, salary);
Dept (did, dname, budget, managerid);
Works (eid, did, pct_time);
Schema4:
Flights (flno, origin, destination, distance, departs, arrives, price);
Aircraft (aid, aname, cruisingrange);
Employees (eid, ename, salary);
Certified (eid, aid);
Schema5:
Suppliers (sid, sname, address);
Parts (pid, pname, colour);
Catalog (sid, pid, cost);
%/
Queries:
1. Find all sailors who are teens (show all information)
Select *
From sailors
Where age >= 13
and age <= 19
2. Find the names of sailors who have reserved at least one boat
Select distinct s.sname
From sailors s, reserves r
Where s.sid = r.sid
3. Find the colors of boats reserved by dustin
Select distinct b.colour
From boats b, reserves r, sailors s
Where s.sname = ‘dustin’
and s.sid = r.sid
and r.bid = b.bid
4. Find the sailor IDs of all sailors who have reserved red boats but not green boats
(Select distinct r.sid
From boats b, reserves r
Where b.bid = r.bid
and b.colour = ‘red’)
MINUS
(Select distinct r.sid
From boats b, reserves r
Where b.bid = r.bid
and b.colour = ‘green’)
5. Find the names of sailors who have reserved at least two boats
Select distinct s.sname
From sailors s, reserves r1, reserves r2
Where s.sid = r1.sid
and r1.sid = r2.sid
and r1.bid <> r2.bid
6. Find the names of the student whose major is in Computer Science.
Select distinct sname
From student
Where major = ‘Computer Science’
7. Find the name s of the student who has enrolled in course Database Systems.
Select distinct s.sname
From student s, enrolled e
Where s.snum = e.snum
and e.cname = ‘Database Systems’
8. Find all the courses which are taught by David Anderson
Select distinct c.name
From faculty f, class c
Where c.fid = f.fid
and f.fname = ‘David Anderson’
9. Find all the courses which are NOT using room R128
Select distinct name
From class
Where room not in
(Select room
From class
Where room = ‘R128’)
10. Find all the students who are in any one of the classes that professor James Smith teaches.
Select distinct s.sname
From student s, enrolled e
Where s.snum = e.snum
and e.cname in
(Select c.name
From class c, faculty f
Where c.fid = f.fid
and f.fname = ‘James Smith’)
