b)
SELECT DISTINCT C.class
FROM Classes C,
Ships S ,
c)
SELECT S.name
FROM Ships S,
d)
SELECT O.battle
e)
SELECT S.name
FROM Classes C1
6.4.5
Yes, duplicates are possible. If a person produced more than one movie of
of Prod and MovieExec will also repeat the name.
6.4.6
(a)
SELECT AVG(speed) AS Avg_Speed
FROM PC ;
(b)
SELECT AVG(speed) AS Avg_Speed
(c)
SELECT AVG(P.price) AS Avg_Price
FROM Product R,
PC P
(d)
SELECT AVG(M.price) AS Avg_Price
FROM
(SELECT P.price
FROM Product R,
PC P
(e)
SELECT SPEED,
AVG(price) AS AVG_PRICE
FROM PC
(f)
SELECT R.maker,
AVG(L.screen) AS Avg_Screen_Size
MAKER AVG_SCREEN_SIZE
—– ———————————
A 15.233333333333333333333333333
(g)
SELECT R.maker
FROM Product R,
MAKER
—–
A
4 record(s) selected.
(h)
SELECT R.maker,
MAX(P.price) AS Max_Price
FROM Product R,
PC P
(i)
SELECT speed,
AVG(price) AS Avg_Price
FROM PC
WHERE speed > 2.0
GROUP BY speed ;
SPEED AVG_PRICE
———- ———–
2.10 995
(j)
SELECT AVG(P.hd) AS Avg_HD_Size
FROM Product R,
PC P
6.4.7
(a)
SELECT COUNT(C.type) AS NO_Classes
FROM Classes
WHERE type =’bb’ ;
NO_CLASSES
(c)
We weight by the number of ships and the answer could be different.
(d)
Even though the book mentions that the first ship has the same name as class, we
can also calculate answer differently.
SELECT C.class,
MIN(S.launched) AS First_Launched
CLASS FIRST_LAUNCHED
—————— ————–
Iowa 1943
(e)
SELECT C.class,
COUNT(O.ship) AS No_Sunk
FROM Classes C ,
(f)
SELECT M.class,
COUNT(O.ship) AS No_Sunk
FROM Outcomes O,
Ships S ,
(SELECT C.class
(g)
SELECT C.country,
AVG(C.bore*C.bore*C.bore*0.5) Avg_Shell_Wt
FROM Classes C,
Ships S
6.4.8
SELECT starName,
MIN(YEAR) AS minYear
6.4.9
Yes, it is possible. We can include in gamma operator the aggregation for HAVING
6.5.1
(a)
INSERT
INTO Product VALUES
(
‘C’ ,
(b)
INSERT
INTO Product
SELECT make ,
model+1100,
‘laptop’
Or if model is character data type
INSERT
INTO Product
SELECT make ,
CHAR(INT(model)+1100),
(c)
DELETE
FROM PC
(d)
DELETE
FROM Laptop L
WHERE L.model IN
(SELECT R2.model
)
) ;
DELETE
FROM PRODUCT R3
WHERE R3.model IN
(e)
UPDATE Product
SET maker = ‘A’
WHERE maker = ‘B’ ;
(f)
UPDATE PC
(g)
UPDATE Laptop L
SET L.screen = L.screen+1,
6.5.2
(a)
INSERT
INTO Classes VALUES
(
‘Nelson’ ,
‘bb’ ,
);
(b)
INSERT
INTO Classes VALUES
(
‘Vittorio Veneto’,
‘bb’ ,
‘Italy’ ,
9,15,41000
) ;
INSERT
INTO Ships VALUES
(
‘Vittorio Veneto’,
‘Vittorio Veneto’,
1940
(c)
DELETE
FROM Ships S
(d)
UPDATE Classes
(e)
DELETE
FROM Classes C
6.6.1
(a)
EXEC SQL BEGIN DECLARE SECTION;
int modelNo;
int pcPrice;
int pcRAM;
pcSpeed = iSpeed;
pcRAM = fRAM;
EXEC SQL OPEN pcCursor;
EXEC SQL FETCH pcCursor
INTO :modelNo, :pcPrice;
while (SQLCODE == 0)
{
(b)
EXEC SQL BEGIN DECLARE SECTION;
int modelNo;
EXEC SQL END DECLARE SECTION;
void deleteModel(int iModel) {
The ISOLATION LEVEL is set to SERIALIZABLE but it could be anything since there
is no risk of dirty read (no select statement).
(c)
EXEC SQL BEGIN DECLARE SECTION;
(d)
EXEC SQL BEGIN DECLARE SECTION;
char maker[1];
int exists = 0;
int modelNo;
maker = cMaker;
modelNo = iModel;
pcSpeed = iSpeed;
pcRAM = fRAM;
pcHDD = iHDD;
pcPrice = iPrice;
}
6.6.2
(a) It is a READ ONLY transaction. Thus there is no write or update atomicity
problem. However, a system crash can cause truncated result and application may
need to rerun on system restart.
(b) If the system crash occurs after the model was deleted from Product but
before deletion from PC, an atomicity problem occurs. Databases keep a log of
6.6.3
(a)
T is the READ ONLY transaction from 6.6.1 (a). Another READ ONLY transaction can
run concurrently without any difference (i.e. As if all transactions ran in
SERIALIZABLE isolation).
If deleteModel from 6.6.1 (b) was running concurrently with T, T may not return
(b)
T is the deleteModel from 6.6.1 (b). If running insertPC concurrently with T,
(c)
T is updatePCPrice from 6.6.1 (c). When running concurrently with another
updatePCPrice for same model, T could read the updated price (dirty data) and
(d)
T is insertPC from 6.6.1 (d).
6.6.4
Serializable: T will never see changes to the database and keep printing the
same list of PCs. This does not serve any useful purpose. Application may need
to periodically stop T and then restart it to see data committed in the
meantime.
Repeatable Read: T will continue to see the list of PCs it saw once. However, T
will also see any new PCs that are inserted in the database. Locking issues can