|
|
|
|
|
Solutions for Section 9.1
Solutions for Section 9.2
Solutions for Section 9.3
Solutions for Section 9.4
Solutions for Section 9.5
lowerPrice(inout real);
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".
CREATE TYPE PersonType AS (
name NameType,
mother REF(PersonType),
father REF(PersonType)
);
The type NameType is not defined here.
Its definition is Exercise 9.4.1(a).
CREATE TYPE ClassType AS (
class CHAR(20),
type CHAR(2),
country CHAR(20),
numGuns INT,
bore REAL,
disp INT
);
CREATE TYPE ShipType AS (
name CHAR(20),
class REF(ClassType),
launch INT
);
CREATE TYPE BattleType AS (
name CHAR(20),
theDate DATE
);
CREATE TYPE OutcomeType AS (
ship REF(ShipType),
battle REF(BattleType),
result CHAR(10)
);
CREATE TABLE Classes OF ClassType (
REF IS classID SYSTEM GENERATED
);
CREATE TABLE Ships OF ShipType
REF IS shipID SYSTEM GENERATED
);
CREATE TABLE Battles OF TYPE BattleType
REF IS battleID SYSTEM GENERATED
);
CREATE TABLE Outcomes OF TYPE OutcomeType;
SELECT star->name
FROM StarsIn
WHERE movie->title = 'Ishtar';
SELECT DISTINCT movie->title, movie->year
FROM StarsIn
WHERE star->address.city() = 'Malibu';
SELECT s.name()
FROM Ships s
WHERE s.class() -> disp > 35000;
CREATE PROCEDURE DeleteStar(IN n CHAR(30))
BEGIN
DELETE FROM StarsIn
WHERE star->name = n;
DELETE FROM MovieStar m
WHERE m.name() = n;
END;