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 [Link]
From sailors s, reserves r
Where [Link] = [Link]
3. Find the colors of boats reserved by dustin
Select distinct [Link]
From boats b, reserves r, sailors s
Where [Link] = ‘dustin’
and [Link] = [Link]
and [Link] = [Link]
4. Find the sailor IDs of all sailors who have reserved red boats but not green boats
(Select distinct [Link]
From boats b, reserves r
Where [Link] = [Link]
and [Link] = ‘red’)
MINUS
(Select distinct [Link]
From boats b, reserves r
Where [Link] = [Link]
and [Link] = ‘green’)
5. Find the names of sailors who have reserved at least two boats
Select distinct [Link]
From sailors s, reserves r1, reserves r2
Where [Link] = [Link]
and [Link] = [Link]
and [Link] <> [Link]
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 [Link]
From student s, enrolled e
Where [Link] = [Link]
and [Link] = ‘Database Systems’
8. Find all the courses which are taught by David Anderson
Select distinct [Link]
From faculty f, class c
Where [Link] = [Link]
and [Link] = ‘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 [Link]
From student s, enrolled e
Where [Link] = [Link]
and [Link] in
(Select [Link]
From class c, faculty f
Where [Link] = [Link]
and [Link] = ‘James Smith’)
