target. We must get its manufacturer with a single-row select */ EXEC SQL SELECT maker INTO :manf FROM Product
WHERE model = :modelOfClosest;
printf(\ :speedOfClosest);
EXEC SQL CLOSE CURSOR pcCursor; }
Exercise 8.1.1(f)
To make sure that we don't change the price of ``new'' PC's, we have only to make the cursor insensitive.
Again, the macro NO_MORE_TUPLES is used to test for the end of the relation. void lowerPrices() {
EXEC SQL BEGIN DECLARE SECTION; char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE pcCursor INSENSITIVE CURSOR FOR PC;
EXEC SQL OPEN pcCursor;
while(1) {
EXEC SQL FETCH FROM pcCursor; if(NO_MORE_TUPLES) break; EXEC SQL UPDATE PC
SET price = price - 100
WHERE CURRENT OF execCursor; }
EXEC SQL CLOSE pcCursor; }
Exercise 8.1.3
void twoMoreExpensive() {
EXEC SQL BEGIN DECLARE SECTION; char SQLSTATE[6], cd[5], cd1[5];
int model, model1, speed, speed1, ram, ram1, price, price1; float hd, hd1; /* we use the variables cd1, model1, etc, to read the tuple 2 positions later, to see if the speed has not changed (in which case there are at least two PC's with the same speed and at least as high a price) */
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE pcCursor SCROLL CURSOR FOR SELECT * FROM PC ORDER BY speed, price;
EXEC SQL OPEN pcCursor;
while(1) {
EXEC SQL FETCH NEXT FROM pcCursor INTO :model, :speed, :ram, :hd, :cd, :price; if(NO_MORE_TUPLES) break; EXEC SQL FETCH RELATIVE +2 FROM pcCursor INTO :model1, :speed1, :ram1,
:hd1, :cd1, :price1; if(NO_MORE_TUPLES) break;
if(speed1 == speed) /* print the tuple :model, :speed, etc. */ ; EXEC SQL FETCH RELATIVE -2 FROM pcCursor INTO :model1, :speed1, :ram1,
:hd1, :cd1, :price1; /* line above just to reset the cursor to where it was */ }
EXEC SQL CLOSE pcCursor;
Solutions for Section 8.2
Exercise 8.2.1(a)
CREATE FUNCTION PresNetWorth(IN studioName CHAR[15])
DECLARE presNetWorth INT;
BEGIN
SELECT netWorth INTO presNetWorth
FROM Studio, MovieExec
WHERE Studio.name = studioName AND presC# = cert#; RETURN(presNetWorth); END;
Exercise 8.2.1(b)
CREATE FUNCTION status(IN person, IN addr)
DECLARE 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;
Exercise 8.2.1(c)
CREATE PROCEDURE twoLongest( IN studio CHAR(15),
OUT longest VARCHAR(255), OUT second VARCHAR(255) )
DECLARE longestLg INT; DECLARE secondLg INT; DECLARE t VARCHAR(255); DECLARE l INT;
DECLARE Not_Found CONDITION FOR SQLSTATE = '02000'; DECLARE MovieCursor CURSOR FOR
SELECT title, length FROM Movie WHERE studioName = studio;
BEGIN
SET longest = NULL; SET second = NULL; SET longestLg = -1; SET secondLg = -1; OPEN MovieCursor; mainLoop: LOOP
FETCH MovieCursor INTO t, l;
IF Not_Found THEN LEAVE mainLoop END IF; IF l > longestLg THEN
SET secondLg = longestLg; SET second = longest; SET longestLg = l; SET longest = t;
ELSIF l > secongLg THEN SET secondLg = l; SET second = t;
END IF; END LOOP;
CLOSE MovieCursor; END;
In explanation, as we run through movies, we need to remember not only the titles of the two longest movies seen so far, but their lengths. That way, when we see a new title and length, fetched into the pair of local variables (t, l), we can compare the length l with the two longest so far. The body of the loop first asks if l is longer than the longest; if so, the old longest becomes second, and the current movie becomes longest. If the current movie is not longest, then we next ask if it is longer than the second longest, and we replace the latter, if so.
Exercise 8.2.2(a)
One can actually write a tricky SQL query that will retrieve the model with the closest price, use this query in a single-row select, and return the selected model number. However, we can also scan the PC's and keep track of how close we have come, along with the number of the model whose price has so far been the closest. To simplify, we maintain the square of the difference between the closest price and the target price, so we don't have to worry about whether the closest price is above or below the target (you need that trick in the SQL SELECT-INTO statement as well).
Since we need to take the first model as ``closest'' regardless of its price, we use -1 as a value of the square of the price differences to indicate that there is no closest model yet selected.
CREATE FUNCTION closestMatchPC(IN targetPrice INT)
DECLARE closestModel INT; DECLARE diffSq INT; DECLARE currSq INT; DECLARE m INT; DECLARE p INT;
DECLARE Not_Found CONDITION FOR SQLSTATE '02000'; DECLARE PCCursor CURSOR FOR SELECT model, price FROM PC;
BEGIN
SET closestModel = -1; SET diffSq = -1; OPEN PCCursor; mainLoop: LOOP
FETCH PCCursor INTO m, p;
IF Not_Found THEN LEAVE mainLoop END IF; SET currSq = (p - targetPrice)*(p - targetPrice); IF diffSq = -1 OR diffSq > currSq THEN BEGIN
SET closestModel = m;
SET diffSq = currSq; END IF; END LOOP;
CLOSE PDCursor;
RETURN(closestModel); END;
Solutions for Section 8.4
Exercise 8.4.1(a)
#include sqlcli.h
int closestMatchPC(int targetPrice) {
int diffSq, currSq, closestModel; SQLHENV myEnv; SQLHDBC myCon; SQLHSTMT pcStat;
SQLINTEGER m, p, mInfo, pInfo;
diffSq = closestModel = -1;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &myEnv); SQLAllocHandle(SQL_HANDLE_DBC, myEnv, &myCon); SQLAllocHandle(SQL_HANDLE_STMT, myCon, &pcStat);
SQLPrepare(pcStat, \ SQLExecute(pcStat);
SQLBindCol(pcStat, 1, SQL_INTEGER, &m, size(m), &mInfo); SQLBindCol(pcStat, 2, SQL_INTEGER, &p, size(p), &pInfo); while(SQLFetch(pcStat) != SQL_NO_DATA) { currSq = (p - targetPrice)*(p - targetPrice); if(diffSq == -1 || diffSq > currSq) { diffSq = currSq; closestModel = m; } }
return(m); }
Exercise 8.4.1(f)
#include sqlcli.h void lowerPrices() { SQLHENV myEnv; SQLHDBC myCon; SQLHSTMT pcStat;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &myEnv); SQLAllocHandle(SQL_HANDLE_DBC, myEnv, &myCon); SQLAllocHandle(SQL_HANDLE_STMT, myCon, &pcStat);