数据库系统基础教程第一版数据库习题答案ch9.docx

《数据库系统基础教程第一版数据库习题答案ch9.docx》由会员分享，可在线阅读，更多相关《数据库系统基础教程第一版数据库习题答案ch9.docx（60页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、a)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 jus

2、t 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

3、price.If PC is empty, we cannot answer the question, and so abort. /EXEC SQL FETCH FROM pcCursor INTO :modelOfClosestz :priceOfClosest, :speedOfClosest;if(NOT_FOUND) /* print message and exit */ ;while(1) EXEC SQL FETCH pcCursor INTO :tempModel, :tempPricez:tempSpeed;if (NOT FOUND) break;strcpy(maxF

4、irepowerClass, cclass);:%snH, maxFirepowerClass);printf ( nClass of maximum firpowrEXEC SQL CLOSE CURSOR cursorl;b)void getCountry() EXEC SQLEXEC SQLBEGIN DECLARE SECTION;charibattle20,iresult10,ocountry20;charstmtl200,stmt2200;EXEC SQLENDDECLARESECTION;strcpy(stmtl,SELECT COUNTRY FROMClasses CWHERE

5、 C.class IN (SELECT S.class FROM Ships SWHERE S.name IN (SELECT ship FROM OutcomesWHERE battle = ?) );Strcpy (stm2,SELECT country FROM ClassesWHERE class = ( SELECT MAX(COUNT(class)FROM Ships s, Outcomes oWHERE o.name = s.ship ANDEXECEXECSQLSQLPREPAREPREPAREquerylquery2FROM stmtl;FROM stmt2;EXECSQLD

6、ECLAREcursorlCURSOR FOR queryl;EXECSQLDECLAREcursor2CURSOR FOR query2;/* ask user for battle */* get countries of the ships involved in the battle */ EXEC SQL OPEN cursorl USING :ibattle;while(!NOT_FOUND) EXEC SQL FETCH cursorl INTO :ocountry;if (FOUND) printf (contry:%sn, ocoutry); )EXEC SQL CLOSE

7、CURSOR cursorl;/* 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

8、);)/* 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

9、);)c)void addShips() EXEC SQL BEGIN DECLARE SECTION;char iclass20z itype3z icontry20, iship20; int inumGuns, iborez idisplacement, ilaunched;char stmtl100, stmt2100;EXEC SQL END DECLARE SECTION;strcpy (stmtl, INSERT INTO Classes VALUES (?, ?, ?, ?, ?, ?)；strcpy(stmt2, vINSERT INTO Ships VALUES (?, ?

10、, ?)；/* ask user for a class and other info for Classes table */EXEC SQL EXECUTE IMMEDATE :stmtl 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

11、for a ship and launched */)d)void findError () EXEC SQLBEGIN DECLARE SECTION;char bname20, bdate8, newbdate8;char sname20, 【year4, newlyear4; char stmtl100, stmt2100;EXEC SQL END DECLARE SECTION;strcpy (stmtl, UPDATE Battls SET date = ? WHERE name = ?)；strcpy (stmt2, UPDATE Ships SET launched = ? WH

12、ERE name = ?)EXEC SQL DECLARE Cl CURSOR FORSelect b.name, b.date, s.name, s.launchedFROM Battles b, Outcomes o, Ships sWHERE b.name = o.battle ANDo.ship = s.name ANDYEAR(b.date) s.launched;EXEC SQL OPEN Cl;while(!NOT_FOUND) EXEC SQL FETCH Cl INTO :bname, :bdate, :sname, :lyear;/ prompt user and ask

13、if a change is needed */if(change battle)/ get a new battle date to newbdate /EXEC SQL EXECUTE IMMEDATE :stmtlUSING :bname, :newbdate; )if (change_ship) (/* get a new launched year to newlyear /EXEC SQL EXECUTE IMMEDATE :stmt2USING :sname, :newlyear; )a)CREATE FUNCTION PresNetWorth(studioName CHAR15

14、) RETURNS INTEGER DECLARE presNetWorth 工NT;BEGINSELECT netWorthINTO presNetWorthFROM Studio, MovieExecWHERE Studio.name = studioName AND presC# = cert#;RETURN(presNetWorth);END;b)CREATE FUNCTION status(person CHAR(30)z add工 CHAR(255) RETURNS INTEGERDECLARE isStar INT;DECLARE isExec INT;BEGINSELECT C

15、OUNT(*)INTO isStarFROM MovieStarWHERE MovieStar.name = person AND MovieStar.address = addr;SELECT COUNT(*)INTO isExecFROM MovieExecWHERE 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( I

16、N studio CHAR(15),OUT longest VARCHAR(255),OUT second VARCHAR(255)DECLARE t VARCHAR(255);DECLARE i INT;DECLARE Not_Found CONDITION FOR SQLSTATE = 1 02000 1 ;DECLARE MovieCursor CURSOR FORSELECT title FROM Movies WHERE studioName = studio ORDER BY length DESC;BEGINSET longest = NULL;SET second = NULL

17、;OPEN MovieCursor;SET i = 0;mainLoop: WHILE (i 120 ANDtitle IN (SELECT movieTitle FROM StarslnWHERE starName = star); BEGINSET earliestYear = 0;OPEN MovieCursor;FETCH MovieCursor INTO earliestYear;CLOSE MovieCursor; END;CREATE PROCEDURE uniqueStar(IN addr CHAR(255),OUT star CHAR(30) )BEGINSET star =

18、 NULL;IF 1 = (SELECT COUNT(*) FROM MovieStar WHERE address = addr) THENSELECT name INTO star FROM MovieStar WHERE address = addr; END;CREATE PROCEDURE removeStar(IN star CHAR(30) )BEGINDELETE FROM Movies WHERE title INstar);(SELECT movieTitle FROM Starsln WHERE starName = DELETE FROM Starsln WHERE s

19、tarName = star;DELETE FROM MovieStar WHERE name = star;END;a)CREATE FUNCTION closestMatchPC(targetPrice 工NT) RETURNS CHARDECLARE closestModel CHAR(4);DECLARE diffSq 工NT;DECLARE currSq INT;DECLARE m CHAR(4);DECLARE p INT;DECLARE Not_Found CONDITION FOR SQLSTATE 1 020001;DECLARE PCCursor CURSOR FORSEL

20、ECT model, price FROM PC;BEGINSET closestModel = NULL;SET diffSq = -1;OPEN PCCursor;mainLoop: LOOPFETCH PCCursor INTO m, p;IF Not_Found THEN LEAVE mainLoop END IF;SET currSq = (p - targetPrice)*(p - targetPrice);IF diffSq = -1 OR diffSq currSqTHEN BEGINSET closestModel = m;SET diffSq = currSq;END IF

21、;END LOOP;CLOSE PDCursor;RETURN(closestModel);END;b)CREATE FUNCTION getPrice(imaker CHAR(l), imodel CHAR(4)RETURNS INTEGERIF ptype = pcTHENSELECT priceINTO pprice FROM PCWHERE model = imodel;ELSE IF ptypelaptop THENSELECT priceINTO pprice FROM LaptopWHERE model = imodel;ELSE IF ptypeprinter7 THENSEL

22、ECT priceINTO pprice FROM PrinterDECLARE ptype VARCHAR(IO);DECLARE pprice INT;DECLARE Not_Found CONDITION FOR SQLSTATE ,02000,BEGINSELECT type INTO ptype FROM Productimodel;imodel;WHERE maker = imaker AND modelWHERE model = imodel;ELSEpprice = NULL;END IF;RETURN (pprice);END;c)CREATE PROCEDURE addPC

23、(IN imodel 工NT,IN ispeed DECIMAL(3,2),IN iram工NT,IN ihd 工NT, IN iprice INTDECLARE Already_Exist CONDITION FOR SQLSTATE ,02300,BEGINif(/*tempPrice closer to targetPrice than is priceOfClosest */)modelOfClosest = tempModel;priceOfClosest = tempPrice;speedOfClosest = tempSpeed; )/* Now, modelOfClosest

24、is the model whose price is closest to target. We must get its manufacturer with a single-row select *EXEC SQL SELECT makerINTO :manf FROM Product WHERE model = :modelOfClosest;printf(nmanf=%s, model=%d, speed=%dnn, manf, modlOfClosst, speedOfClosest);EXEC SQL CLOSE CURSOR pcCursor; )b)void acceptab

25、leLaptop() 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 queryl FROMSELECT model, speed, ram, hd, screen, price, makerFROM Laptop 1, Product pWHE

26、RE speed = ? ANDINSERT INTO PC VALUES(imodel, ispeed, iram, ihd, iprice);WHILE (Already_Exist) DOSET imodel = imodel + 1;INSERT INTO PC VALUES(imodel, ispeed, iram, ihdf iprice);END WHILE;END;d)CREATE PROCEDURE getNumOfHigherPrice( IN iprice 工NT,OUT NumOfPCs 工NT,OUT NumOfLaptops 工NT, OUT NumOfPrinte

27、rs INT )BEGINSET NumOfPCs = 0;SET NumOfLaptops = 0;SET NumOfPrinters = 0;SELECT COUNT(*) INTO NumOfPCs FROM PC WHERE price iprice;SELECT COUNT(*) INTO NumOfLaptops FROM Laptop WHERE price iprice;SELECT COUNT (*) INTO NumOfPrinters FROM Printer WHERE price iprice;END;CREATE FUNCTION getFirepower(icla

28、ss VARCHAR(IO) RETURNS INTEGERDECLARE firepower INT;DECLARE nguns 工NT;DECLARE nbore INT;BEGINSELECT numGuns, bore INTO nguns, nbore FROM ClassesWHERE class = iclass;SET firepower = nguns * (nbore * nbore * nbore);RETURN(firepower);END;a) CREATE PROCEDURE twoCountriesInBattle(IN ibattle VARCHAR(20),O

29、UT firstCountry VARCHAR(20),OUT secondCountry VARCHAR(20) )DECLARE i INT;DECLARE ocountry VARCHAR(20);DECLARE classCursor CURSOR FORSELECT country FROM ClassesWHERE class IN(SELECT class FROM ShipsWHERE name IN(SELECT ship FROM Outcomes WHERE battle = ibattle )；BEGINSET firstCountry = NULL;SET secon

30、dCountry = NULL;SET i = 0;IF 2 = (SELECT COUNT(*) count FROM ClassesWHERE class IN (SELECT class FROM ShipsWHERE name IN(OutcomesibattleSELECT ship FROM WHERE battle = )THENOPEN classCursor;WHILE (i 2) DOFETCH classCursor INTO ocountry;IF (i = 0) THENSET firstCountry = ocountry;ELSESET secoundCountr

31、y = ocountry;END IF;END WHILE;END IF;CLOSE calssCursor;END;b) CREATE PROCEDURE addClass(IN iship VARCHAR(20) fIN iclass VARCHAR(20),IN itype CHAR(2),IN icountry VARCHAR(20),IN inumGuns 工NT,IN ibore INT,IN idisplacement INTBEGININSERT INTO Classes VALUES(iclass, itype, icountry, inumGuns, ibore, idis

32、placement);INSERT INTO Ships VALUES(iship, iclass, NULL); END;CREATE PROCEDURE checkLaunched( IN ship VARCHAR(20) ) DECLARE bname VARCHAR(20);BEGINIF EXIST (SELECT b.name INTO bnameFROM Battles b, Outcomes o, Ships sWHERE b.name = o.battle AND o.ship = s.name AND YEAR(b.date) =ia) #include sqlcli.hS

33、QLHENV myEnv;SQLHDBC mycon;SQLHSTMT execStat;SQLRETURN errCodel, errCode2, errCode3;SQLCHAR manf, tempModel4;SQLFLOAT tempSpeed;SQLINTEGER tempPrice;SQLINTEGER collnfo;Int targetPrice;char mode10fClosest4;float speedOfClosest;int priceOfClosest;/ ask user for target price and read the answer into va

34、riable targetPrice */errCodel = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &myEnv); if (errCodel) printf(Error for SQL_HANDLE_ENV.n);exit(1);)errCode2 = SQLAllocHandle(SQL_HANDLE_DBC, myEnv, &myCon);if (errCode2) printf (,zError for SQL_ HANDLE_DBC . n,z); exit (1);) errCode3 = SQLAllocHandle(S

35、QL HANDLE STMT, myCon, &execStat);SQLBindCol(execStat,1,SQL CHAR, tempModel,sizeof(tempModel),&collnfo);SQLBindCol(execStat,2,SQL INTEGER, tempPrice,sizeof(tempPrice),Scollnfo);SQLBindCol(execStat,3,SQL FLOAT, tempSpeed,sizeof(tempSpeed),&collnfo);if(errCode3) printf (,zError for SQL_ HANDLE_STMT .n

36、)； exit(1);)SQLExecDirect(execStatzSELECT model, price, speed FROM PC，SQL NTS);priceOfClosest = NULL;while(SQLFetch(execStat)while(SQLFetch(execStat)!= SQL_NO_DATA) if ( /* the 1st fetchif ( /* the 1st fetchor tempPrice closer to targetPrice *modelOfClosestmodelOfClosesttempModel;priceOfClosest=temp

37、Price;speedOfClosest=tmpSpd;)/ Now, modelOfClosest is the model whose price is closest totarget. We must get its manufacturer with a single-row selectif (priceOfClosest = NULL ) /* no data fetched */ /* print error message and exit */SQLPrepare (execStat,SELECT maker FROM Product WHERE model = ?，SQL

38、_NTS);SQLBindParameter (execStat 1 z .z modelOfClosest, .);SQLExecute(execStat);SQLBindCol(execStat, 1r SQLCHAR, &manf, sizeof(manf)r &collnfo);/* print manf */b) #include sqlcli.hSQLHENV myEnv;SQLHDBC mycon;SQLHSTMT execStat;SQLRETURN errCodel, errCode2r errCode3;SQLCHAR model4z maker;SQLFLOAT minS

39、pd;SQLINTEGER minRam, minHd, minScreen;SQLFLOAT speed;SQLINTEGER ram, hd, screen;SQLINTEGER collnfo;/* ask user for minimum speed, ram, hd size, and screen size */errCodel = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &myEnv); if (errCodel) printf (Error for SQL_HANDLE_ENV.n,z);exit (1);)errCode

40、2 = SQLAllocHandle(SQL_HANDLE_DBC, myEnv, &myCon);if (errCode2) printf (z,Error for SQL_ HANDLE_DBC .n)；exit (1);errCode3 = SQLAllocHandle(SQL_HANDLE_STMT, myCon, &execStat);if (rrCode3) printf (Error for SQL_ HANDLE_STMT . n,z); exit (1);SQLBindCol(execStat, 1,SQLBindCol(execStat, 2 rSQLBindCol(xcS

41、tat, 3,SQLBindCol(execStat, 4,SQLBindCol(execStat, 5,SQLBindCol(execStat, 6,SQLBindCol(execStat, 7 rwhile(SQLFetch(execStat)!= SQL_NO_DATA) SQLPrepare (execStat,SELECT model, speed, ram, hdz screen, price, maker FROM Laptop 1, Product p I IWHERE speed = ? AND | |am = ? AND | | hd = ? AND | | screen

42、= ? AND V1.model = p.modal”, SQL_NTS);SQLBindParameter (execStat, 1, SQL_FLOATZ ，minSpeed, .);SQLBindParameter (execStat, 2, SQL_INTEGERf minRam, .);SQLBindParameter(execStat,3,SQL_ INTEGER,minHd, .);SQLBindParameter(execStat,4,SQL_ INTEGER,minScreenA.);SQLExecute(execStat);SQL_CHAR, model, sizof(mo

43、d1), Scollnfo); SQL_FLOAT, speed, sizeof (speed), &collnfo);SQL_工NTEGER, ram, sizeof(ram), &collnfo);SQL_工NTEGER, hd, sizeof(hd), &collnfo);SQL_工NTEGER, screen, sizeof (screen), &collnfo);SQL_工NTEGER, price, sizeof (price), &collnfo);SQL_CHAR, maker,sizeof(maker), Scollnfo);if ( FOUND )/* print fetc

44、hed info /#include sqlcli.hSQLHENV myEnv;SQLHDBC mycon;SQLHSTMT execStat;SQLRETURN errCodel, errCod2, errCode3;SQLCHAR maker, model4z type10, color6;SQLFLOAT speed;SQLINTEGER ram, hd, screen, price;SQLINTEGER collnfo;/ ask user for minimum speed, ram, hd size, and screen size /errCodel = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &myEnv); if (errCodel) printf (z,Error for SQL_HANDLE_ENV.n)；exit (1);errCode2 = SQLAllocHandle(S