Solutions
Chapter 6
6.1.1
Attributes must be separated by commas. Thus here B is an alias of A.
6.1.2
a)
SELECT address AS Studio_Address
FROM Studio
WHERE NAME = ‘MGM’;
c)
SELECT starName
FROM StarsIn
WHERE movieYear = 1980
OR movieTitle LIKE ‘%Love%’;
d)
SELECT name AS Exec_Name
FROM MovieExec
WHERE netWorth >= 10000000;
e)
SELECT name AS Star_Name
6.1.3
a)
SELECT model,
speed,
hd
FROM PC
WHERE price < 1000 ;
MODEL SPEED HD
—– ———- ——
1002 2.10 250
1003 1.42 80
b)
SELECT model ,
speed AS gigahertz,
hd AS gigabytes
FROM PC
WHERE price < 1000 ;
MODEL GIGAHERTZ GIGABYTES
—– ———- ———
1002 2.10 250
1003 1.42 80
c)
SELECT maker
FROM Product
WHERE TYPE = ‘printer’ ;
MAKER
—–
D
d)
SELECT model,
ram ,
screen
FROM Laptop
e)
SELECT *
FROM Printer
WHERE color ;
MODEL CASE TYPE PRICE
—– —– ——– ——
3001 TRUE ink-jet 99
Note: Implementation of Boolean type is optional in SQL standard (feature ID
T031). PostgreSQL has implementation similar to above example. Other DBMS
CREATE TABLE Printer
(
model CHAR(4) UNIQUE NOT NULL,
color SMALLINT ,
type VARCHAR(8) ,
price SMALLINT ,
CONSTRAINT Printer_ISCOLOR CHECK(color IN(0,1))
f)
SELECT model,
hd
FROM PC
WHERE speed = 3.2
AND price < 2000;
6.1.4
a)
SELECT class,
country
b)
SELECT name AS shipName
FROM Ships
WHERE launched < 1918 ;
SHIPNAME
——————
Haruna
11 record(s) selected.
c)
SELECT ship AS shipName,
battle
FROM Outcomes
WHERE result = ‘sunk’ ;
SHIPNAME BATTLE
—————— ——————
d)
SELECT name AS shipName
FROM Ships
WHERE name = class ;
SHIPNAME
——————
e)
SELECT name AS shipName
FROM Ships
WHERE name LIKE ‘R%’;
7 record(s) selected.
Note: As mentioned in exercise 2.4.3, there are some dangling pointers and to
retrieve all ships a UNION of Ships and Outcomes is required.
Below query returns 8 rows including ship named Rodney.
SELECT name AS shipName
f) Only using a filter like ‘% % %’ will incorrectly match name such as ‘ a b ‘
since % can match any sequence of 0 or more characters.
0 record(s) selected.
Note: As in (e), UNION with results from Outcomes.
SELECT name AS shipName
FROM Ships
WHERE name LIKE ‘_% _% _%’
SHIPNAME
——————
Duke of York
6.1.5
a)
The resulting expression is false when neither of (a=10) or (b=20) is TRUE.
a = 10 b = 20 a = 10 OR b = 20
NULL TRUE TRUE
b)
The resulting expression is only TRUE when both (a=10) and (b=20) are TRUE.
c)
The expression is always TRUE unless a is NULL.
d)
The expression is TRUE when a=b except when the values are NULL.
e)
Like in (d), the expression is TRUE when a<=b except when the values are NULL.
6.1.6
6.2.1
a)
SELECT M.name AS starName
FROM MovieStar M,
b)
SELECT S.starName
FROM Movies M ,
c)
SELECT X.name AS presidentName
d)
SELECT M1.title
e)
SELECT X1.name AS execName
6.2.2
a)
SELECT R.maker AS manufacturer,
L.speed AS gigahertz
MANUFACTURER GIGAHERTZ
———— ———-
A 2.00
A 2.16
A 2.00
b)
SELECT R.model,
P.price
UNION
SELECT R.model,
UNION
SELECT R.model,
T.price
MODEL PRICE
—– ——
1004 649
c)
SELECT R.maker
FROM Product R,
Laptop L
WHERE R.model = L.model
d)
SELECT DISTINCT P1.hd
FROM PC P1,
PC P2
e)
SELECT P1.model,
P2.model
FROM PC P1,
f)
SELECT M.maker
FROM
(SELECT maker,
R.model
FROM PC P,
Product R
—–
B
1 record(s) selected.
6.2.3
a)
SELECT S.name
FROM Ships S,
8 record(s) selected.
b)
SELECT S.name ,
C.displacement,
NAME DISPLACEMENT NUMGUNS
—————— ———— ——-
Kirishima 32000 8
c)
SELECT name shipName
FROM Ships
UNION
SHIPNAME
——————
Arizona
Bismark
California
Duke of York
Fuso
North Carolina
Prince of Wales
Ramillies
Renown
Repulse
Resolution
Revenge
Rodney
34 record(s) selected.
d)
SELECT C1.country
FROM Classes C1,
e)
SELECT O1.ship
FROM Outcomes O1,
Battles B1
SHIP
——————
0 record(s) selected.
f)
SELECT O.battle
FROM Outcomes O,
Ships S ,
Classes C
6.2.4
Since tuple variables are not guaranteed to be unique, every relation Ri should
be renamed using an alias. Every tuple variable should be qualified with the
6.2.5
Again, create a tuple variable for every Ri, i=1,2,…,n
That is, the FROM clause is
6.3.1
a)
SELECT DISTINCT maker
FROM Product
WHERE model IN
b)
SELECT P1.model
FROM Printer P1
) ;
c)
SELECT L.model
FROM Laptop L
WHERE L.speed < ANY
d)
SELECT model
FROM
(SELECT model,
price
FROM PC
UNION
) M1
WHERE M1.price >= ALL
(SELECT price
FROM PC
UNION
(d) – contd —
SELECT model
FROM
(SELECT model,
price
FROM PC
WHERE M1.price IN
(SELECT MAX(price)
FROM
(SELECT price
FROM PC
e)
SELECT R.maker
FROM Product R,
Printer T
WHERE R.model =T.model
AND T.price <= ALL
(SELECT MIN(price)
f)
SELECT R1.maker
FROM Product R1,
PC P1
);
SELECT R1.maker
FROM Product R1,
PC P1
WHERE R1.model=P1.model
AND P1.ram =
(SELECT MIN(ram)
6.3.2
a)
SELECT C.country
FROM Classes C
WHERE numGuns IN
(SELECT MAX(numGuns)