b)
SELECT DISTINCT C.class
FROM Classes C,
Ships S
WHERE C.class = S.class
AND EXISTS
c)
SELECT S.name
FROM Ships S
WHERE S.class IN
);
d)
SELECT O.battle
FROM Outcomes O
WHERE O.ship IN
(SELECT name
e)
SELECT S.name
FROM Ships S,
Classes C
WHERE S.Class = C.Class
AND numGuns >= ALL
Better answer;
SELECT S.name
FROM Ships S,
Classes C
WHERE S.Class = C.Class
AND numGuns >= ALL
(SELECT numGuns
6.3.3
SELECT title
FROM Movies
GROUP BY title
6.3.4
SELECT S.name
FROM Ships S,
Classes C
WHERE S.Class = C.Class ;
6.3.5
(a)
SELECT S.name,
S.address
FROM MovieStar S,
MovieExec E
WHERE S.gender =’F’
AND E.netWorth > 10000000
(b)
SELECT name,
6.3.6
By replacing the column in subquery with a constant and using IN subquery for
the constant, statement equivalent to EXISTS can be found.
i.e. replace “WHERE EXISTS (SELECT C1 FROM R1..)” by “WHERE 1 IN (SELECT 1 FROM
R1…)”
Example:
6.3.7
(a)
n*m tuples are returned where there are n studios and m executives. Each studio
(b)
There are no common attributes between StarsIn and MovieStar; hence no tuples
are returned.
(c)
There will be at least one tuple corresponding to each star in MovieStar. The
unemployed stars will appear once with null values for StarsIn. All employed
6.3.8
Since model numbers are unique, a full natural outer join of PC, Laptop and
Printer will return one row for each model. We want all information about PCs,
Laptops and Printers even if the model does not appear in Product but vice versa
is not true. Thus a left natural outer join between Product and result above is
Alternately, the Product relation can be joined individually with each of
PC,Laptop and Printer and the three results can be Unioned together. For
attributes that do not exist in one relation, a constant such as ‘NA’ or 0.0 can
be used. Below is an example of this approach using PC and Laptop.
SELECT R.MAKER ,
R.MODEL ,
R.TYPE ,
UNION
SELECT R.MAKER ,
R.MODEL ,
R.TYPE ,
6.3.9
SELECT *
6.3.10
SELECT *
UNION
(SELECT C2.class ,
C2.type ,
C2.country ,
6.3.11
(a)
SELECT *
FROM R,
S ;
(b)
Let Attr consist of
AttrR = attributes unique to R
AttrS = attributes unique to S
(c)
SELECT *
6.4.1
(a)
(b)
SELECT DISTINCT R.maker
FROM Product R,
(c)
SELECT R.model,
P.price
FROM Product R,
PC P
WHERE R.model = P.model
AND R.maker = ‘B’
(d)
SELECT model
(e)
SELECT DISTINCT R.maker
FROM Product R,
Laptop L
WHERE R.model = L.model
(f)
With GROUP BY hd, DISTINCT keyword is not required.
(g)
SELECT P1.model,
P2.model
(h)
SELECT R.maker
FROM Product R
WHERE R.model IN
(i)
After finding the maximum speed, an IN subquery can provide the manufacturer
name.
SELECT R.maker
FROM Product R,
PC P
WHERE R.model = P.model
AND P.speed IN
(SELECT MAX(M.speed)
FROM
(SELECT speed
FROM PC
FROM
(SELECT speed
FROM PC
UNION
SELECT speed
FROM Laptop
) N
) ;
Alternately,
SELECT COALESCE(MAX(P2.speed),MAX(L2.speed),0) SPEED
FROM PC P2
UNION
SELECT R2.maker
FROM Product R2,
Laptop L
(j)
SELECT R.maker
(k)
SELECT R.maker
FROM Product R,
PC P
6.4.2
(a)
We can assume that class is unique in Classes and DISTINCT keyword is not
required.
(b)
Ship names are not unique (In absence of hull codes, year of launch can help
(c)
SELECT DISTINCT ship AS Ship_Name
(d)
SELECT DISTINCT S.name AS Ship_Name
FROM Ships S,
(e)
SELECT DISTINCT O.ship AS Ship_Name,
C.displacement ,
C.numGuns
SHIP_NAME DISPLACEMENT NUMGUNS
—————— ———— ——-
Kirishima 32000 8
Washington 37000 9
2 record(s) selected.
Note: South Dakota was also in Guadalcanal but its class information is not
available. Below query will return name of all ships that were in Guadalcanal
SHIP_NAME DISPLACEMENT NUMGUNS
—————— ———— ——-
Kirishima 32000 8
(f)
The Set opearator UNION guarantees unique results.
SELECT ship AS Ship_Name
(g)
SELECT C.class
FROM Classes C,
Ships S
WHERE C.class = S.class
GROUP BY C.class
INTERSECT
SELECT C2.country
FROM Classes C2
WHERE C2.type=’bc’ ;
However, above query does not account for classes without any ships belonging to
AND C2.type =’bc’ ;
(i)
SELECT O2.ship AS Ship_Name
FROM Outcomes O2,
6.4.3
a)
SELECT DISTINCT R.maker
FROM Product R,
PC P
WHERE R.model = P.model
d)
Due to set operator UNION, unique results are returned.
It is difficult to completely avoid a subquery here. One option is to use Views.
CREATE VIEW AllProduct AS
SELECT model,
price
FROM PC
But if we replace the View, the query contains a FROM subquery.
SELECT A1.model
FROM
(SELECT model,
price
FROM PC
UNION
SELECT model,
price
FROM Laptop
e)
SELECT DISTINCT R.maker
FROM Product R,
f)
SELECT DISTINCT R1.maker
FROM Product R1,
PC P1
WHERE R1.model=P1.model
6.4.4
a)
SELECT DISTINCT C1.country
FROM Classes C1