A particular query can have several formulations, and a query optimizer may produce di?erent query plans with di?erent costs for each.

Question

a. Assume that the Computer Science Department teaches only three 100-level courses:
CS110, CS113, and CS114. Write an SQL statement whose result set contains the course codes of all courses that have these as prerequisites in three ways: using OR, UNION, and a nested subquery involving LIKE.
b. Write an SQL statement whose result set contains the names of all computer science courses that are prerequisites to other courses in three ways: using a join, a nested subquery involving EXISTS, and a nested subquery involving IN.

Answer 1

a. ```
SELECT R.CrsCode
FROM Requires R
WHERE R.PrereqCrsCode = ’CS110’ OR
R.PrereqCrsCode = ’CS113’
R.PrereqCrsCode = ’CS114’
```

```
SELECT R.CrsCode
FROM Requires R
WHERE R.PrereqCrsCode = ’CS110’
UNION
SELECT R.CrsCode
FROM Requires R
WHERE R.PrereqCrsCode = ’CS113’
UNION
SELECT R.CrsCode
FROM Requires R
WHERE R.PrereqCrsCode = ’CS114’
```

```
SELECT R.CrsCode
FROM Requires R
WHERE R.PrereqCrsCode LIKE ’CS1%’
```

b. ```
SELECT C.CrsName
FROM Course C, Requires R
WHERE C.CrsCode = R.PrereqCrsCode
```

```
SELECT C.CrsName
FROM Course C
WHERE EXISTS (
SELECT *
FROM Requires R
WHERE C.CrsCode = R.PrereqCrsCode)
```

```
SELECT C.CrsName
FROM Course C
WHERE C.CrsCode IN (
SELECT R.PrereqCrsCode
FROM Requires R)
```