asda

CS306 - Assignment 1
March 15, 2011
1
Relational Algebra
TEAMS(tid, tname, budget )
PLAYERS(pid, pname, age, country )
PLAYS(pid, season, tid, position, value)
→ (pid references pid in PLAYERS table)
→ (tid references tid in TEAMS table)
WINS(wtid, ltid, season, wscore, lscore)
→ (wtid (winner) and ltid (loser) references tid in TEAMS table)
Consider an ’imaginary’ football league in which no match ends up in a draw
and all teams are in the league in all seasons. Also assume all contracts with
players last for one season only. Given the schema above, write the following
queries in relational algebra.
1. Find the names of the players that played in BJK during season 2002.
2. Find the pids of the players that played in both FB and GS.
3. Find the pids of players that played in TS during two consecutive seasons.
4. Find the names of the players that played in at least two different teams
of the same color.
5. Find the names of the teams that did not win any game during season
2006.
6. Find the pids of the players that played in at least three different teams.
7. Find the pids of the players that played in exactly one team and played
during only one season.
8. Find the tids of the teams that lost against all teams of color ’yellow- red’
during season 2008.
9. Find the tids of the teams that lost against a team with the highest budget.
10. Find the pids of the players that hold the record to be the most valuable
player of all times.
11. Find the pids of the players with the second highest value.
1

---

2
SQL
COURSES(cid, cname, capacity, location, term, areacode)
INSTRUCTORS(tid , tname, age, numOfPapers, academicDegree, area, salary)
INSTRUCTS(cid, tid, term, numOfExams)
→ (cid references cid in COURSES table)
→ (tid references tid in INSTRUCTORS table)
COMPETITION(wtid , ltid , wscore, lscore)
→ (wtid and ltid references tid in INSTRUCTORS table)
Students in SU Chess Club organize a Chess tournament among SU Instruc-
tors to attract attention on this mind game. In this competition, they keep
information about instructors, their courses and results of the competitions in
separate tables as it is shown above. Score is kept by classical piece rel- ative
value system (i.e. every piece in game has a value). Suppose in this system tie
is not possible. Considering above schema, state in English what the following
SQL Queries will compute:
1. SELECT IR.area, MAX(TEMP.wCount)
FROM (SELECT CT.wtid AS wtid, COUNT(*) AS wCount
FROM COMPETITION CT
GROUP BY CT.wtid) AS TEMP, INSTRUCTORS IR
WHERE TEMP.wtid = IR.tid
GROUP BY IR.area
2. SELECT I1.tid, COUNT(*)
FROM INSTRUCTORS I1, INSTRUCTORS I2, COMPETITION CT
WHERE I1.tid = CT.wtid AND I2.tid = CT.ltid AND I1.area <> I2.area
GROUP BY I1.tid
HAVING MIN(I2.numOfPapers) > (SELECT MIN(numOfPapers)
FROM INSTRUCTORS)
3. SELECT I.iname
FROM INSTRUCTORS I
WHERE NOT EXISTS ((SELECT I2.tid
FROM INSTRUCTORS I2
WHERE I2.area = ’CS’)
EXCEPT
(SELECT CT.wtid
FROM COMPETITION CT
WHERE CT.ltid = I.tid))
4. SELECT IR.tid
FROM INSTRUCTORS IR
WHERE IR.tid IN (SELECT IT.tid
FROM INSTRUCTS IT)
2

---

You must write the following queries in SQL. Note that your queries will
be run in Oracle and will be checked if they do what they are supposed
to do. In order to try your queries yourself, you can use commands in
data.txt (also see notes in guides.txt ) to install an example database with
the above schema in Oracle. Also, Oracle does not fully support Standard
SQL. You should consider notes near some questions and design your
queries accordingly to be able to test them in Oracle.
5. Find the names of instructors whose area is ‘CS’.
6. Find the names of instructors whose area is ‘MS’ and older than 40.
7. Find the names of the instructors that won against an instructor of area
‘MS’.
8. Find the names of the instructors that won against all instructors of area
‘MS’. (Don’t use Except. You can use minus operator instead of except in
Oracle)
9. Find the tids of the instructors who instructs more than one courses.
10. Find the names of the instructors who did not win any competition.
11. Find the names of courses that are in area ‘MS’ with the highest capacity.
12. Find the names of the courses which is instructed by the instructors who
has highest salary.
13. Find the number of papers of the instructors with the second highest
salary.
14. Find the tids of the instructors who did not get a score higher than 20 in
any competition
15. Find the average salary of the instructors whose ages are less than aver-
age age of all instructors.
16. Find the wtids and number of wins of the instructors who won more than
average of number of wins per instructors.
3

---