数据库系统基础教程第九章答案.doc

9.3.1a)In the following, we use macro NOT_FOUND as defined in the section. void closestMatchPC() EXEC SQL BEGIN DECLARE SECTION; char manf, SQLSTATE6; int targetPrice, /* holds price given by user */ float tempSpeed, speedOfClosest; char tempModel4, modelOfClosest4; int tempPrice, priceOfClosest; /* for tuple just read from PC & closest price found so far */ EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE pcCursor CURSOR FOR SELECT model, price, speed FROM PC; EXEC SQL OPEN pcCursor; /* ask user for target price and read the answer into variable targetPrice */ /* Initially, the first PC is the closest to the target price. If PC is empty, we cannot answer the question, and so abort. */ EXEC SQL FETCH FROM pcCursor INTO :modelOfClosest, :priceOfClosest, :speedOfClosest; if(NOT_FOUND) /* print message and exit */ ; while(1) EXEC SQL FETCH pcCursor INTO :tempModel, :tempPrice, :tempSpeed; if(NOT_FOUND) break; if(/*tempPrice closer to targetPrice than is priceOfClosest */) modelOfClosest = tempModel; priceOfClosest = tempPrice; speedOfClosest = tempSpeed; /* Now, modelOfClosest is the model whose price is closest to target. We must get its manufacturer with a single-row select */ EXEC SQL SELECT maker INTO :manf FROM Product WHERE model = :modelOfClosest; printf(manf=%s, model=%d, speed=%dn, manf, modelOfClosest, speedOfClosest); EXEC SQL CLOSE CURSOR pcCursor;b) void acceptableLaptop() EXEC SQL BEGIN DECLARE SECTION; int minRam, minHd, minScreen; /* given by user */ float minSpeed; char model4, maker, float speed; int ram, hd, screen, price; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE query1 FROM SELECT model, speed, ram, hd, screen, price, maker FROM Laptop l, Product p WHERE speed = ? AND ram = ? AND hd = ? AND screen = ? AND l.model = p.model EXEC SQL DECLARE cursor1 CURSOR FOR query1; /* ask user for minimum speed, ram, hd size, and screen size */ EXEC SQL OPEN cursor1 USING :minSpeed, :minRam, :minHd, :minScreen; while(!NOT_FOUND) EXEC SQL FETCH cursor1 INTO :model, :speed, :ram, :hd, :screen, :price, :maker; if(FOUND) printf(maker:%s, model:%d, n speed:%.2f, ram:%d, hd:%d, screen:%d, price:%dn, maker, model, speed, ram, hd, screen, price); EXEC SQL CLOSE CURSOR cursor1;c)void productsByMaker() EXEC SQL BEGIN DECLARE SECTION; char maker, model4, type10, color6; float speed; int ram, hd, screen, price; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE query1 FROM SELECT * FROM PC WHERE model IN (SELECT model FROM Product WHERE maker = ? AND type = pc); EXEC SQL PREPARE query2 FROM SELECT * FROM Laptop WHERE model IN (SELECT model FROM Product WHERE maker = ? AND type = laptop); EXEC SQL PREPARE query3 FROM SELECT * FROM Printer WHERE model IN (SELECT model FROM Product WHERE maker = ? AND type = printer); EXEC SQL DECLARE cursor1 CURSOR FOR query1; EXEC SQL DECLARE cursor2 CURSOR FOR query2; EXEC SQL DECLARE cursor3 CURSOR FOR query3; /* ask user for manufacturer */ Printf(“maker:%sn”, maker); /* get PCs made by the manufacturer */ EXEC SQL OPEN cursor1 USING :maker; Printf(“product type: PCn”); while(!NOT_FOUND) EXEC SQL FETCH cursor1 INTO :model, :speed, :ram, :hd, :price; if(FOUND) printf(model:%d,speed:%.2f, ram:%d, hd:%d, price:%dn”, model, speed, ram, hd, price); /* get Laptops made by the manufacturer */ EXEC SQL OPEN cursor2 USING :maker; Printf(“product type: Laptopn”); while(!NOT_FOUND) EXEC SQL FETCH cursor2 INTO :model, :speed, :ram, :hd, :screen, :price; if(FOUND) printf(model:%d, speed:%.2f, ram:%d, hd:%d, screen:%d, price:%dn, model, speed, ram, hd, screen, price); /* get Printers made by the manufacturer */ EXEC SQL OPEN cursor3 USING :maker; Printf(“product type: Printern”); while(!NOT_FOUND) EXEC SQL FETCH cursor3 INTO :model, :color, :type, :price; if(FOUND) printf(model:%d, color:%s, type:%s, price:%dn, model, color, type, price); EXEC SQL CLOSE CURSOR cursor1; EXEC SQL CLOSE CURSOR cursor2; EXEC SQL CLOSE CURSOR cursor3;d)void withinBudget() EXEC SQL BEGIN DECLARE SECTION; int total_budget, rest_budget, pc_price, printer_price;char pc_model4, printer_model4, color6; float min_speed; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE query1 FROM SELECT model, price FROM PC WHERE speed = ? AND price = ?ORDER BY price; EXEC SQL PREPARE query2 FROM SELECT model, price FROM Printer WHERE price 0) Printf(“Warnning: The PC model already existsn”); ELSE EXEC SQL EXECUTE stmt2 USING :pmaker, :pmodel, :ptype; EXEC SQL EXECUTE stmt3 UINGNG :pmodel, :pspeed, :pram, :phd, :pprice 9.3.2a)void largestFirepower() EXEC SQL BEGIN DECLARE SECTION; char cclass20, maxFirepowerClass20; int cnumGuns, cbore; float firepower, maxFirepower; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE cursor1 CURSOR FOR SELECT class, numGuns, bore FROM Classes; EXEC SQL OPEN cursor1; EXEC SQL FETCH FROM cursor1 INTO :cclass, :cnumGuns, :cbore; if(NOT_FOUND) /* print message and exit */ ; maxFirepower = cnumGuns * (power (cbore, 3); strcpy(maxFirepowerClass, cclass); while(1) EXEC SQL FETCH cursor1 INTO :cclass, :cnumGuns, :cbore; if(NOT_FOUND) break; firepower = cnumGuns * (power (cbore, 3); if( firepower maxFirepower ) maxFirepower = firepower; strcpy(maxFirepowerClass, cclass); printf(Class of maximum firepower :%sn, maxFirepowerClass); EXEC SQL CLOSE CURSOR cursor1;b) void getCountry() EXEC SQL BEGIN DECLARE SECTION; char ibattle20, iresult10, ocountry20; char stmt1200, stmt2200; EXEC SQL END DECLARE SECTION; strcpy(stmt1, “SELECT COUNTRY FROM Classes C WHERE C.class IN ( SELECT S.class FROM Ships S WHERE S.name IN ( SELECT ship FROM Outcomes WHERE battle = ?)” ); Strcpy(stm2, “SELECT country FROM Classes WHERE class = ( SELECT MAX(COUNT(class) FROM Ships s, Outcomes o WHERE o.name = s.ship AND s.result = ?)” ); EXEC SQL PREPARE query1 FROM stmt1; EXEC SQL PREPARE query2 FROM stmt2; EXEC SQL DECLARE cursor1 CURSOR FOR query1; EXEC SQL DECLARE cursor2 CURSOR FOR query2; /* ask user for battle */ /* get countries of the ships involved in the battle */ EXEC SQL OPEN cursor1 USING :ibattle; while(!NOT_FOUND) EXEC SQL FETCH cursor1 INTO :ocountry; if(FOUND) printf(contry:%sn”, ocoutry); EXEC SQL CLOSE CURSOR cursor1; /* get the country with the most ships sunk */ strcpy(iresult, “sunk”); EXEC SQL OPEN cursor2 USING :iresult; /* loop for the case theres the same max# of ships sunk */ While(!NOT_FOUND) EXEC SQL FETCH cursor2 INTO :ocountry;If(FOUND) Printf(“country with the most ships sunk: %s, ocountry); /* get the country with the most ships damaged */ strcpy(iresult, “damaged”); EXEC SQL OPEN cursor2 USING :iresult; /* loop for the case theres the same max# of ships damaged */ While(!NOT_FOUND) EXEC SQL FETCH cursor2 INTO :ocountry;If(FOUND) Printf(“country with the most ships damaged: %s, ocountry); c)void addShips() EXEC SQL BEGIN DECLARE SECTION; char iclass20, itype3, icontry20, iship20; int inumGuns, ibore, idisplacement, ilaunched; char stmt1100, stmt2100; EXEC SQL END DECLARE SECTION; strcpy(stmt1, “INSERT INTO Classes VALUES (?, ?, ?, ?, ?, ?)”); strcpy(stmt2, “INSERT INTO Ships VALUES (?, ?, ?)”); /* ask user for a class and other info for Classes table */ EXEC SQL EXECUTE IMMEDATE :stmt1 USING :iclass, :itype, :icontry, :inumGuns, :ibore, :idisplacement; /* ask user for a ship and launched */ WHILE(there_is_input) EXEC SQL EXECUTE IMMEDATE :stmt2 USING :iship, :iclass, ilaunched; /* ask user for a ship and launched */ d)void findError() EXEC SQL BEGIN DECLARE SECTION; char bname20, bdate8, newbdate8; char sname20, lyear4, newlyear4; char stmt1100, stmt2100; EXEC SQL END DECLARE SECTION; strcpy(stmt1, “UPDATE Battles SET date = ? WHERE name = ?”); strcpy(stmt2, “UPDATE Ships SET launched = ? WHERE name = ?”); EXEC SQL DECLARE C1 CURSOR FOR Select b.name, b.date, s.name, s.launched FROM Battles b, Outcomes o, Ships s WHERE b.name = o.battle AND o.ship = s.name AND YEAR(b.date) s.launched; EXEC SQL OPEN C1; while(!NOT_FOUND) EXEC SQL FETCH C1 INTO :bname, :bdate, :sname, :lyear;/* prompt user and ask if a change is needed */ if(change_battle) /* get a new battle date to newbdate */ EXEC SQL EXECUTE IMMEDATE :stmt1 USING :bname, :newbdate; if(change_ship) /* get a new launched year to newlyear */ EXEC SQL EXECUTE IMMEDATE :stmt2 USING :sname, :newlyear; 9.4.1a)CREATE FUNCTION PresNetWorth(studioName CHAR15) RETURNS INTEGERDECLARE presNetWorth INT;BEGIN SELECT netWorth INTO presNetWorth FROM Studio, MovieExec WHERE Studio.name = studioName AND presC# = cert#; RETURN(presNetWorth);END;b)CREATE FUNCTION status(person CHAR(30), addr CHAR(255) RETURNS INTEGERDECLARE isStar INT;DECLARE isExec INT;BEGIN SELECT COUNT(*) INTO isStar FROM MovieStar WHERE MovieStar.name = person AND MovieStar.address = addr; SELECT COUNT(*) INTO isExec FROM MovieExec WHERE MovieExec.name = person AND MovieExec.address = addr; IF isStar + isExec = 0 THEN RETURN(4) ELSE RETURN(isStar + 2*isExec) END IF;END;c)CREATE PROCEDURE twoLongest( IN studio CHAR(15), OUT longest VARCHAR(255), OUT second VARCHAR(255)DECLARE t VARCHAR(255);DECLARE i INT;DECLARE Not_Found CONDITION FOR SQLSTATE = 02000;DECLARE MovieCursor CURSOR FOR SELECT title FROM Movies WHERE studioName = studioORDER BY length DESC;BEGIN SET longest = NULL; SET second = NULL; OPEN MovieCursor; SET i = 0; mainLoop: WHILE (i 2) DO FETCH MovieCursor INTO t; IF Not_Found THEN LEAVE mainLoop END IF; SET i = i + 1; END WHILE; CLOSE MovieCursor;END;d)CREATE PROCEDU