A Guide to SQL, Ninth Edition Solutions 8-1
Chapter 8
SQL Functions and Procedures
Solutions
Answers to Review Questions
1. Use the UPPER function to convert letters to uppercase (Oracle and SQL Server). In
2. Use the ROUND function to round a number to a specific number of decimal places in
3. To add months to a date, use the ADD_MONTHS function (Oracle), or the DATEADD
4. Use the SYSDATE function in Oracle, the GETDATE() function in SQL Server, and the
5. In Oracle, separate the column names with two vertical lines (||) in the SELECT clause.
7. A stored procedure is a special file that contains commands that can be used over and
15. FETCH
17. To use SQL commands in Access, create the command in a string variable. To run the
21. The INSERTED and DELETED tables are system tables created by SQL Server. The
22. [Critical Thinking]
SELECT ITEM_NUM, DESCRIPTION, PRICE
1.
2.
3.
4.
5. a.
CREATE OR REPLACE PROCEDURE DISP_CUST_CRED
(I_CUSTOMER_NUM IN CUSTOMER.CUSTOMER_NUM%TYPE) AS
I_CUSTOMER_NAME CUSTOMER.CUSTOMER_NAME%TYPE;
I_CREDIT_LIMIT CUSTOMER.CREDIT_LIMIT%TYPE;
CREATE PROCEDURE usp_DISP_CUST_CRED
@custnum char(3)
b.
CREATE OR REPLACE PROCEDURE DISP_ORDERS (I_ORDER_NUM ORDERS.ORDER_NUM%TYPE) AS
I_ORDER_DATE ORDERS.ORDER_DATE%TYPE;
I_CUSTOMER_NUM CUSTOMER.CUSTOMER_NUM%TYPE;
I_CUSTOMER_NAME CUSTOMER.CUSTOMER_NAME%TYPE;
In SQL Server, the command is:
c.
CREATE OR REPLACE PROCEDURE ADD_ORDER
(I_ORDER_NUM IN ORDERS.ORDER_NUM%TYPE,
In SQL Server, the command is:
CREATE PROCEDURE usp_ADD_ORDERS
d.
CREATE OR REPLACE PROCEDURE UPDATE_ORDER_DATE
A Guide to SQL, Ninth Edition Solutions 8-4
In SQL Server, the command is:
CREATE PROCEDURE usp_UPD_ORDER_DATE
@ordernum char(5),
e.
CREATE OR REPLACE PROCEDURE DELETE_ORDERS
In SQL Server, the command is:
CREATE PROCEDURE usp_DEL_ORDERS
@ordernum char(5)
6.
CREATE OR REPLACE PROCEDURE DISP_ITEM_CATEGORY
(I_CATEGORY IN ITEM.CATEGORY%TYPE) AS
I_ITEM_NUM ITEM.ITEM_NUM%TYPE;
I_DESCRIPTION ITEM.DESCRIPTION%TYPE;
FETCH ITEMGROUP INTO I_ITEM_NUM, I_DESCRIPTION, I_STOREHOUSE, I_PRICE;
EXIT WHEN ITEMGROUP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I_ITEM_NUM);
In SQL Server, the command is:
CREATE PROCEDURE usp_DISP_ITEM_CATEGORY
@CATEGORY char(2)
A Guide to SQL, Ninth Edition Solutions 8-5
AS
DECLARE @itemnum char(4)
FOR
SELECT ITEM_NUM, DESCRIPTION, STOREHOUSE, PRICE
FROM ITEM
WHERE CATEGORY = @category
OPEN mycursor
END
CLOSE mycursor
DEALLOCATE mycursor
7. a.
Public Function OrderDelete(I_ORDER_NUM)
b.
Public Function OrderUpdate(I_ORDER_NUM, I_ORDER_DATE)
Dim strSQL As String
strSQL = “UPDATE ORDERS SET ORDER_DATE = ‘”
c.
Public Function Finditems(I_CATEGORY)
Dim rs As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSQL As String
Set cnn = CurrentProject.Connection
A Guide to SQL, Ninth Edition Solutions 8-6
8.
CREATE OR REPLACE PROCEDURE CHG_ITEM_PRICE (I_ITEM_NUM IN ITEM.ITEM_NUM%TYPE,
I_PRICE IN ITEM.PRICE%TYPE) AS
BEGIN
UPDATE ITEM
SET PRICE = I_PRICE
In SQL Server, the command is:
CREATE PROCEDURE usp_CHG_ITEM_PRICE
9.
a.
CREATE OR REPLACE TRIGGER ADD_CUSTOMER
AFTER INSERT ON CUSTOMER FOR EACH ROW
BEGIN
In SQL Server, the command is:
b.
CREATE OR REPLACE TRIGGER UPD_CUSTOMER
AFTER UPDATE ON CUSTOMER FOR EACH ROW
BEGIN
A Guide to SQL, Ninth Edition Solutions 8-7
UPDATE REP
SET COMMISSION = COMMISSION + ((@newbalance –@oldbalance) * RATE)
c.
CREATE OR REPLACE TRIGGER DEL_CUSTOMER
In SQL Server, the command is:
CREATE TRIGGER DEL_REP_COMM
ON CUSTOMER
AFTER DELETE
AS
10. [Critical Thinking] MONTHS_BETWEEN returns number of months between dates date1 and date2. If date1
is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. SYSDATE
returns the current date and time set for the operating system on which the database resides. CURRENT_DATE
b.
Answers to Colonial Adventure Tours Exercises
1.
2.
3.
4. a.
CREATE OR REPLACE PROCEDURE DISP_GUIDE (I_GUIDE_NUM IN GUIDE.GUIDE_NUM%TYPE) AS
I_LAST_NAME GUIDE.LAST_NAME%TYPE;
I_FIRST_NAME GUIDE.FIRST_NAME%TYPE;
BEGIN
@guidenum char(4)
AS
SELECT GUIDE_NUM, RTRIM(FIRST_NAME)+’ ‘+RTRIM(LAST_NAME)
FROM GUIDE
WHERE GUIDE_NUM = @guidenum
b.
AND RESERVATION_ID = I_RESERVATION_ID;
DBMS_OUTPUT.PUT_LINE(I_NUM_PERSONS);
DBMS_OUTPUT.PUT_LINE(I_CUSTOMER_NUM);
DBMS_OUTPUT.PUT_LINE(I_LAST_NAME);
END;
/
In SQL Server, the command is:
c.
CREATE OR REPLACE PROCEDURE ADD_GUIDE
(I_GUIDE_NUM IN GUIDE.GUIDE_NUM%TYPE,
I_LAST_NAME IN GUIDE.LAST_NAME%TYPE,
I_FIRST_NAME IN GUIDE.FIRST_NAME%TYPE) AS
BEGIN
INSERT INTO GUIDE (GUIDE_NUM, LAST_NAME, FIRST_NAME)
VALUES
AS
(@guidenum, @last, @first)
d.
CREATE OR REPLACE PROCEDURE CHG_GUIDE
(I_GUIDE_NUM IN GUIDE.GUIDE_NUM%TYPE,
I_LAST_NAME GUIDE.LAST_NAME%TYPE) AS
SET LAST_NAME = @last
WHERE GUIDE_NUM = @guidenum
e.
CREATE OR REPLACE PROCEDURE DEL_GUIDE
(I_GUIDE_NUM IN GUIDE.GUIDE_NUM%TYPE) AS
5.
CREATE OR REPLACE PROCEDURE DISP_CUST_RESERVATION
(I_CUSTOMER_NUM IN CUSTOMER.CUSTOMER_NUM%TYPE) AS
I_RESERVATION_ID RESERVATION.RESERVATION_CODE%TYPE;
I_TRIP_ID RESERVATION.TRIP_ID%TYPE;
EXIT WHEN RESERVATIONGROUP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I_RESERVATION_ID);
DBMS_OUTPUT.PUT_LINE(I_TRIP_ID);
DBMS_OUTPUT.PUT_LINE(I_NUM_PERSONS);
DBMS_OUTPUT.PUT_LINE(I_TRIP_PRICE);
DECLARE @tripprice decimal(6,2)
DECLARE mycursor CURSOR READ_ONLY
FOR
SELECT RESERVATION_ID, TRIP_ID, NUM_PERSONS, TRIP_PRICE
FROM RESERVATION
WHERE CUSTOMER_NUM = @customernum
6. a.
Public Function GuideDelete(I_GUIDE_NUM)
b.
Public Function GuideUpdate(I_GUIDE_NUM, I_ LAST_NAME)
Dim strSQL As String
strSQL = “UPDATE GUIDE SET LAST_NAME = ‘”
c.
Public Function FindReservations(I_CUSTOMER_NUM)
Do Until rs.EOF
Debug.Print (rs!RESERVATION_ID)
Debug.Print (rs!TRIP_ID)
Debug.Print (rs!NUM_PERSONS)
Debug.Print (rs!TRIP_PRICE)
7.
CREATE OR REPLACE PROCEDURE CHG_MAX_GRP_SIZE
(I_TRIP_ID IN TRIP.TRIP_ID%TYPE,
I_MAX_GRP_SIZE IN TRIP.MAX_GRP_SIZE%TYPE) AS
BEGIN
8.
a.
CREATE OR REPLACE TRIGGER ADD_TOTAL_PERSONS
AFTER INSERT ON RESERVATION FOR EACH ROW
BEGIN
SET TOTAL_PERSONS = TOTAL_PERSONS + @numpersons
b.
CREATE OR REPLACE TRIGGER UPD_TOTAL_PERSONS
AFTER UPDATE ON RESERVATION FOR EACH ROW
BEGIN
SELECT @newnumpersons = (SELECT NUM_PERSONS FROM INSERTED)
SELECT @oldnumpersons = (SELECT NUM_PERSONS FROM DELETED)
UPDATE TRIP
SET TOTAL_ PERSONS = TOTAL_ PERSONS + @newpersons – @oldpersons
c.
CREATE OR REPLACE TRIGGER DEL_ TOTAL_PERSONS
In SQL Server, the command is:
CREATE TRIGGER DEL_ TOTAL_PERSONS
ON RESERVATION
AFTER DELETE
AS
9. [Critical Thinking] The LENGTH function returns the number of characters in the string. Leading spaces are
included; trailing spaces are not included. The SUBSTR function returns the specified number of characters from the
SELECT SUBSTR(TYPE,0,3)
FROM TRIP;
Answers to Solmaris Condominium Group Exercises
1.
2.
3.
SELECT LOCATION_NUM, UNIT_NUM, CONDO_UNIT.OWNER_NUM, LAST_NAME, CONDO_FEE,
4. a.
CREATE OR REPLACE PROCEDURE DISP_OWNER (I_OWNER_NUM IN OWNER.OWNER_NUM%TYPE) AS
I_FIRST_NAME OWNER.FIRST_NAME%TYPE;
I_LAST_NAME OWNER.LAST_NAME%TYPE;
In SQL Server, the command is:
CREATE PROCEDURE usp_DISP_OWNER
@ownernum char(5)
b.
FROM CONDO_UNIT, OWNER
WHERE CONDO_UNIT.OWNER_NUM = OWNER.OWNER_NUM
AND CONDO_ID = I_CONDO_ID;
DBMS_OUTPUT.PUT_LINE(I_LOCATION_NUM);
In SQL Server, the command is:
CREATE PROCEDURE usp_DISP_CONDO_UNIT
WHERE CONDO_UNIT.OWNER_NUM = OWNER.OWNER_NUM
AND CONDO_ID = @condoid
c.
CREATE OR REPLACE PROCEDURE ADD_OWNER
(I_OWNER_NUM IN OWNER.OWNER_NUM%TYPE,
I_POSTAL_CODE);
END;
/
In SQL Server, the command is:
CREATE PROCEDURE ADD_OWNER
@ownernum char(5),
CREATE OR REPLACE PROCEDURE CHG_OWNER
(I_OWNER_NUM IN OWNER.OWNER_NUM%TYPE,
I_LAST_NAME IN OWNER.LAST_NAME%TYPE) AS
BEGIN
UPDATE OWNER
e.
CREATE OR REPLACE PROCEDURE DEL_OWNER
A Guide to SQL, Ninth Edition Solutions 8–15
(I_OWNER_NUM IN OWNER.OWNER_NUM%TYPE) AS
BEGIN
5.
CREATE OR REPLACE PROCEDURE DISP_CONDO
(I_SQR_FT IN CONDO_UNIT.SQR_FT%TYPE) AS
I_LOCATION_NUM CONDO_UNIT.LOCATION_NUM%TYPE;
I_UNIT_NUM CONDO_UNIT.UNIT_NUM%TYPE;
EXIT WHEN CONDOGROUP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I_LOCATION_NUM);
DBMS_OUTPUT.PUT_LINE(I_UNIT_NUM);
DBMS_OUTPUT.PUT_LINE(I_CONDO_FEE);
DBMS_OUTPUT.PUT_LINE(I_OWNER_NUM);
DECLARE mycursor CURSOR READ_ONLY
FOR
SELECT LOCATION_NUM, UNIT_NUM, CONDO_FEE, OWNER_NUM
FROM CONDO_UNIT
WHERE SQR_FT = @sqrft
OPEN mycursor
6. a.
Public Function OwnerDelete(I_OWNER_NUM)
Dim strSQL As String
strSQL = “DELETE FROM OWNER WHERE OWNER_NUM = ‘”
strSQL = strSQL & I_OWNER_NUM
strSQL = strSQL & “‘”
DoCmd.RunSQL strSQL
End Function
c.
Public Function FindCondos(I_SQR_FT)
Debug.Print (rs!LOCATION_NUM)
Debug.Print (rs!UNIT_NUM)
Debug.Print (rs!CONDO_FEE)
Debug.Print (rs!OWNER_NUM)
rs.MoveNext
7.
CREATE OR REPLACE PROCEDURE CHG_CONDO_FEE (I_CONDO_ID IN CONDO_UNIT.CONDO_ID%TYPE,
I_LOCATION_NUM IN CONDO_UNIT.LOCATION_NUM%TYPE, I_CONDO_FEE IN CONDO_UNIT.CONDO_FEE%TYPE) AS
BEGIN
UPDATE CONDO_UNIT
SET CONDO_FEE = I_CONDO_FEE
/
In SQL Server, the command is:
8.
a.
CREATE OR REPLACE TRIGGER ADD_CONDO_UNIT
AFTER INSERT ON CONDO_UNIT FOR EACH ROW
BEGIN
SET TOTAL_FEES = TOTAL_FEES + @condofee
b.
CREATE OR REPLACE TRIGGER UPD_CONDO_UNIT
AFTER UPDATE ON CONDO_UNIT FOR EACH ROW
BEGIN
SELECT @oldcondofee = (SELECT CONDO_FEE FROM DELETED)
UPDATE OWNER
SET TOTAL_FEES = TOTAL_FEES + @newcondofee + @oldcondofee
c.
CREATE OR REPLACE TRIGGER DEL_CONDO_UNIT
9. [Critical Thinking] The CEIL function returns the smallest integer that is greater than or equal to the number. The
FLOOR function returns the largest integer that is less than or equal to the number. In SQL Server, the functions are
CEILING and FLOOR. The commands are not available in Access.
In SQL Server, the command is
Whether the values vary or not depends on the basic discounted fee. Note that for the condo with ID of 14, all three
values are the same.