| A First Course in Database Systems |
|
| A First Course in Database Systems |
Product the declaration
lowerPrice(inout real);
Product the declaration
real getSpeed() raises notComputer;
SELECT p.model
FROM PCs p
WHERE p.price < 2000
SELECT DISTINCT lp1.manufacturer
FROM Printers lp1, Printers lp2
WHERE lp1.manufacturer = lp2.manufacturer AND
lp1.printerType = "laser" AND
lp2.printerType = "laser" AND
lp1.model != lp2.model
SELECT p.model
FROM (SELECT x FROM PCs x WHERE x.price < 2000) p
SELECT DISTINCT lp1.manufacturer
FROM (SELECT x FROM Printers x WHERE x.printerType = "laser") lp1,
(SELECT y FROM Printers y WHERE y.manufacturer = lp1.manufacturer
AND y.printerType = "laser"
AND y.model != lp1.model) lp2
Notice that the second collection in the FROM clause is
empty unless there is a second laser printer by the manufacturer of
lp1.
SELECT pc.manufacturer
FROM PCs pc
WHERE EXISTS pr IN Printers :
pr.manufacturer = pc.manufacturer
SELECT pc1.manufacturer
FROM PCs pc1
WHERE FOR ALL pc2 IN PCs :
pc2.hd >= 2.0 OR
pc2.manufacturer != pc1.manufacturer
Notice that by requiring of every PC pc2 that it
either be by a different manufacturer or have the requisite hard disk,
we are saying that every PC by the manufacturer of pc1 has
enough hard disk.
The fact that pc1 exists guarantees that its manufacturer
manufactures at least one PC.
AVG(SELECT p.speed FROM PCs p)
SELECT cdSpeed, AVG(SELECT part.pc.ram
FROM partition part)
FROM PCs pc
GROUP BY cdSpeed: pc.cd
SELECT class, COUNT(SELECT part.s
FROM partition part
WHERE EXISTS outcome IN Outcomes :
outcome.theShip = part.s AND
outcome.status = sunk)
FROM Ships s
GROUP BY class: s.classOf
HAVING MIN(SELECT part.s.launched FROM partition part) < 1919
Starting from the FROM clause, we look at the ships s, and
group them by class.
The HAVING clause eliminates those groups (which are the
classes) whose minimum launch date is 1919 or later.
The result, expressed by the SELECT clause is a table
giving the class and a count of a certain set.
This set is obtained by looking at a group of ships and determining for
each ship s whether that ship appears in an Outcome object with the
status "sunk."
We do not bother with DISTINCT when counting these ships,
because it should be impossible for a ship to be sunk twice and
therefore no ship s appears in two Outcome objects.
x = Set(1,2,3);
x = SELECT p
FROM PCs p
WHERE p.model = 1000;
colorado = Ship(name: "Colorado",
launched: 1923,
classOf: marylandClass
)
Here, we assume that marylandClass is a variable holding the
class object for the ship-class "Maryland".Note: the question is actually wrong, and should have read ``ship Maryland of the Colorado class.'' We have, however, left the question in the book as is and changed the answer on 12/2/97.
CREATE ROW TYPE PersonType (
name NameType,
mother REF(PersonType),
father REF(PersonType)
);
The type NameType is not defined here.
Its definition is Exercise 8.5.1(a).
CREATE ROW TYPE ClassType (
class CHAR(20),
type CHAR(2),
country CHAR(20),
numGuns INT,
bore REAL,
disp INT
);
CREATE ROW TYPE ShipType (
name CHAR(20),
class REF(ClassType),
launch INT
);
CREATE ROW TYPE BattleType (
name CHAR(20),
theDate DATE
);
CREATE ROW TYPE OutcomeType (
ship REF(ShipType),
battle REF(BattleType),
result CHAR(10)
);
CREATE TABLE Classes OF TYPE ClassType;
CREATE TABLE Ships OF TYPE ShipType
SCOPE FOR class IS Classes;
CREATE TABLE Battles OF TYPE BattleType;
CREATE TABLE Outcomes OF TYPE OutcomeType
SCOPE FOR ship IS Ships
SCOPE FOR battle IS Battles;
SELECT name
FROM Ships
WHERE class->disp > 35000;
CREATE TYPE PCType (
speed INT,
ram INT,
hd REAL,
cd CHAR(5),
price REAL
EQUALS DEFAULT,
LESS THAN NONE
);
FUNCTION newPC(:s INT, :r INT, :h REAL, :c CHAR(5), :p REAL)
RETURNS PCType;
:pc PCType;
BEGIN
:pc = PC();
:pc.speed = :s;
:pc.ram = :r;
:pc.hd = :h;
:pc.cd = :c;
:pc.price = :p;
RETURN(:pc);
END;
FUNCTION value(:pc PCType) RETURNS REAL;
RETURN(:pc.speed + 5*:pc.ram + 50*:pc.hd + 10*:pc.cd);