9.3.1
a)
In the following, we use macro NOT_FOUND as defined in the section.
void closestMatchPC() {
EXEC SQL BEGIN DECLARE SECTION;
char manf, SQLSTATE[6];
int targetPrice, /* holds price given by user */
/* 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 */)
}
b)
void acceptableLaptop() {
EXEC SQL BEGIN DECLARE SECTION;
int minRam, minHd, minScreen; /* given by user */
float minSpeed;
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;
c)
void productsByMaker() {
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;
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:%s\n”, maker);
/* get PCs made by the manufacturer */
EXEC SQL OPEN cursor1 USING :maker;
Printf(“product type: PC\n”);
}
/* get Laptops made by the manufacturer */
EXEC SQL OPEN cursor2 USING :maker;
Printf(“product type: Laptop\n”);
while(!NOT_FOUND) {
}
}
/* get Printers made by the manufacturer */
EXEC SQL OPEN cursor3 USING :maker;
Printf(“product type: Printer\n”);
}
d)
void withinBudget() {
EXEC SQL BEGIN DECLARE SECTION;
int total_budget, rest_budget, pc_price, printer_price;
char pc_model[4], printer_model[4], color[6];
float min_speed;
EXEC SQL FETCH cursor1 INTO :pc_model, :pc_price;
if (NOT_FOUND)
Printf(“no pc found within the budget\n”);
else
if(NOT_FOUND) {
EXEC SQL CLOSE CURSOR cursor2;
color = “false”;
EXEC SQL OPEN cursor2 USING :rest_budget, :color;
if(NOT_FOUND)
printf(“no printer found within the budget\n”);
else {
e)
void newPCproduct() {
EXEC SQL BEGIN DECLARE SECTION;
char pmaker, pmodel[4], ptype[6];
float pspeed;
EXEC SQL PREPARE stmt3 FROM
‘INSERT INTO PC VALUES(?, ?, ?, ?, ?)’;
}
}
9.3.2
a)
void largestFirepower() {
EXEC SQL BEGIN DECLARE SECTION;
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;
strcpy(maxFirepowerClass, cclass);
}
}
}
b)
void getCountry() {
EXEC SQL BEGIN DECLARE SECTION;
char ibattle[20], iresult[10], ocountry[20];
char stmt1[200], stmt2[200];
EXEC SQL END DECLARE SECTION;
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;
EXEC SQL OPEN cursor2 USING :iresult;
/* loop for the case there’s the same max# of ships sunk */
While(!NOT_FOUND) {
}
c)
void addShips() {
EXEC SQL BEGIN DECLARE SECTION;
char iclass[20], itype[3], icontry[20], iship[20];
int inumGuns, ibore, idisplacement, ilaunched;
char stmt1[100], stmt2[100];
EXEC SQL END DECLARE SECTION;
EXEC SQL EXECUTE IMMEDATE :stmt2
USING :iship, :iclass, ilaunched;
/* ask user for a ship and launched */
}
}
d)
void findError() {
EXEC SQL BEGIN DECLARE SECTION;
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)
9.4.1
a)
CREATE FUNCTION PresNetWorth(studioName CHAR[15]) RETURNS INTEGER
DECLARE presNetWorth INT;
BEGIN
b)
CREATE FUNCTION status(person CHAR(30), addr CHAR(255)) RETURNS INTEGER
DECLARE isStar INT;
DECLARE isExec INT;
BEGIN
SELECT COUNT(*)
INTO isStar
c)
CREATE PROCEDURE twoLongest(
IN studio CHAR(15),
OUT longest VARCHAR(255),
OUT second VARCHAR(255)
)
mainLoop: WHILE (i < 2) DO
FETCH MovieCursor INTO t;
IF Not_Found THEN LEAVE mainLoop END IF;
d)
CREATE PROCEDURE earliest120mMovie(
IN star CHAR(30),
OUT earliestYear INT
)
e)
CREATE PROCEDURE uniqueStar(
IN addr CHAR(255),
OUT star CHAR(30)
f)
CREATE PROCEDURE removeStar(
IN star CHAR(30)
)
9.4.2
a)
CREATE FUNCTION closestMatchPC(targetPrice INT) RETURNS CHAR
DECLARE closestModel CHAR(4);
DECLARE diffSq INT;
BEGIN
SET closestModel = NULL;
SET diffSq = –1;
OPEN PCCursor;
mainLoop: LOOP
FETCH PCCursor INTO m, p;
b)
CREATE FUNCTION getPrice(imaker CHAR(1), imodel CHAR(4))
RETURNS INTEGER
DECLARE ptype VARCHAR(10);
DECLARE pprice INT;
DECLARE Not_Found CONDITION FOR SQLSTATE ‘02000’;
RETURN (pprice);
END;
c)
CREATE PROCEDURE addPC(
IN imodel INT,
d)
CREATE PROCEDURE getNumOfHigherPrice(
IN iprice INT,
OUT NumOfPCs INT,
OUT NumOfLaptops INT,
OUT NumOfPrinters INT
)
9.4.3
a)
CREATE FUNCTION getFirepower(iclass VARCHAR(10)) RETURNS INTEGER
b)
CREATE PROCEDURE twoCountriesInBattle(
IN ibattle VARCHAR(20),
OUT firstCountry VARCHAR(20),
OUT secondCountry VARCHAR(20)
)
BEGIN
SET firstCountry = NULL;
WHERE battle = ibattle
)
)
)
THEN
OPEN classCursor;
WHILE (i < 2) DO
c)
CREATE PROCEDURE addClass(
IN iship VARCHAR(20),
BEGIN
INSERT INTO Classes VALUES(iclass, itype, icountry,
inumGuns, ibore, idisplacement);
INSERT INTO Ships VALUES(iship, iclass, NULL);
END;
d)
CREATE PROCEDURE checkLaunched(
IN ship VARCHAR(20)
)
9.4.4
9.5.1
a)
#include sqlcli.h
SQLHENV myEnv;
SQLHDBC mycon;
SQLHSTMT execStat;
SQLRETURN errCode1, errCode2, errCode3;
errCode1 = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &myEnv);
if(errCode1) {
printf(”Error for SQL_HANDLE_ENV.\n”);
exit(1);
}
errCode2 = SQLAllocHandle(SQL_HANDLE_DBC, myEnv, &myCon);
if(errCode2) {
printf(”Error for SQL_ HANDLE_DBC.\n”);
exit(1);
}
priceOfClosest = NULL;
while(SQLFetch(execStat) != SQL_NO_DATA) {
if( /* the 1st fetch or tempPrice closer to targetPrice */
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
*/
if (priceOfClosest == NULL ) /* no data fetched */
/* print error message and exit */
b)
#include sqlcli.h
SQLHENV myEnv;
SQLHDBC mycon;
}
errCode2 = SQLAllocHandle(SQL_HANDLE_DBC, myEnv, &myCon);
if(errCode2) {
printf(”Error for SQL_ HANDLE_DBC.\n”);
exit(1);
}
SQLPrepare(execStat,
“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”,
SQL_NTS);
SQLBindParameter(execStat, 1, SQL_FLOAT, …, minSpeed, …);
SQLBindParameter (execStat, 2, SQL_INTEGER, …, minRam, …);
while(SQLFetch(execStat) != SQL_NO_DATA) {
if( FOUND )
/* print fetched info */
}
c)
#include sqlcli.h
SQLHENV myEnv;
}
errCode2 = SQLAllocHandle(SQL_HANDLE_DBC, myEnv, &myCon);
}
/* get PCs made by the manufacturer */
SQLBindParameter(execStat, 1, SQL_CHAR, …, maker, …);
SQLExecute(execStat);
SQLBindCol(execStat, 1, SQL_CHAR, model, sizeof(model), &colInfo);
SQLBindCol(execStat, 2, SQL_FLOAT, speed,
sizeof(speed), &colInfo);
SQLBindCol(execStat, 3, SQL_INTEGER, ram,
sizeof(ram), &colInfo);
}
/* get Laptops made by the manufacturer */
SQLPrepare(execStat,
“SELECT * FROM Laptop WHERE model IN (” ||
“SELECT model FROM Product ” ||
“WHERE maker = ? AND ” ||
“type = ‘laptop’”,