New York CityCollege of Technology

of C.U.N.Y.

 

Lab 8 Exercise

TAL Distributors

1        List the item number and description for all items.  The description should appear in uppercase letters.

 

 

2.List the customer number and name for all customers located in the city of Grove.  Your query should ignore case.  For example, a customer with the city Grove should be included as should customers whose city is GROVE, grove, GrOvE, AND SO ON.

 

 

3.List the customer number, name, and balance for all customers.  The balance should be rounded to the nearest dollar.

 

 

4.TAL Distributors is running a promotion that is valid for up to 20 days after an order is placed. List the order number, customer number, customer name, and the promotion date for each order.  The promotion date is 20 days after the order was placed.

 

 

5.      Write PL/SQL procedure to accomplish the following tasks:

a.       Obtain the name and credit limit of the customer whose number currently is store in I_CUSTOMER_NUM.  Place these values in the variables I_CUSTOMER_NAME and  I_CREDIT_LIMIT, respectively.  Output the contents I_CUSTOMER_NAME and I_CREDIT_LIMIT.

b.      Obtain the order date, customer number, and name for orders whose number currently is stored in  I_ORDER_NUM.   Place these values in the variables I_ORDER_DATE, I_CUSTOMER_NUM, and I_CUSTOMER_NAME, respectively. Output the contents of  I_ORDER_DATE, I_CUSTOMER_NUM, and I_CUSTOMER_NAME.

 

CREATE PROCEDURE sOrderDetails
@I_ORDER_NUM int
AS
BEGIN
DECLARE @I_ORDER_DATE varchar(20)
DECLARE @I_CUSTOMER_NUM varchar(20)
DECLARE @I_CUSTOMER_NAME int
SET @I_ORDER_DATE = SELECT ORDER_DATE FROM ORDERS WHERE CUSTOMER_NUM =@I_ORDER_NUM
SET @I_CUSTOMER_NUM = SELECT CUSTOMER_NUM FROM ORDERS WHERE CUSTOMER_NUM =@I_ORDER_NUM
SET @I_CUSTOMER_NAME= SELECT CUSTOMER_NAME FROM ORDERS WHERE CUSTOMER_NUM =@I_ORDER_NUM
SELECT @I_ORDER_DATE,@I_CUSTOMER_NUM,@I_CUSTOMER_NAME
END
GO

c.       Add a row to the ORDERS table.

 

 

CREATE PROCEDURE InsertOrders
As
BEGIN
INSERT INTO ORDERS VALUES(5,'11/12/2012',12,'DEO')
END

 

 

 

 

d.      Change the date of the order whose number is stored in I_ORDER_NUM to the date currently found in I_ORDER_DATE.

UPDATE ORDERS
SET ORDER_DATE=@I_ORDER_DATE
WHERE ORDER_NUM=@I_ORDER_NUM

 

 

 

 

e.       Delete the order whose number is stored in I_ORDER_NUM.

 

DELETE FROM ORDERS WHERE ORDER_NUM=@I_ORDER_NUM

 

 

 

Colonial Adventure Tours

1  List the guide number, first name, and last name for all guides. The first name should appear in lowercase letters and the last name should appear in uppercase letters.

 

 

2.List the trip ID and the trip name for the trips that start in Bar Harbor. Your query should ignore case.  For example a trip with the start location of Bar Harbor should be included as should trips whose start location is BAR HARBOR, Bar harbor BaRHarBor and so on.

 

 

 

 

 

 

 

 

3. List the reservation ID, trip date, and price for all trips.  The price should be rounded to the nearest dollar.

 

4.      Write the PL/SQL procedure to accomplish the following tasks:

a.       Obtain the first name and last name(separated by a space), of the guide whose number currently is store in I_GUIDE_NUM.  Place these values in the variables I_FIRST_NAME and  I_LAST_NAME, respectively.  Output the contents I_GUIDE_NUM, I_FIRST_NAME and I_LAST_NAME.

 

b.      Obtain the number of persons, customer number, and customer last name for every reservation  whose current ID is stored in I_RESERVATION_ID.   Place these values in the variables I_NUM_PERSONS, I_CUSTOMER_NUM, and I_LAST_NAME, respectively. Output the contents of  I_NUM_PERSONS, I_CUSTOMER_NUM, and I_LAST_NAME.

 

 

Solmaris Condominium Group.

1. List the owner number, first name, and last name for all owners. The first name should appear in uppercase letters and the last name should appear in lowercase letters.

 

2.List the owner number and last name for owners located in city of Bowton. Your query should ignore case.  For example a customer with the city of Bowtonshould be included as should customer whose city is BOWTON, BowTon, BoWtOn and so on.

3.Solmaris is offering a monthly discount for owners who pay their condo fee on a quarterly basis. The discount is 1.75 percent of the monthly fee.  For each condo, list the location number, unit number, owner number, owner’s last name, condo fee and discount.  The discount should be rounded to the nearest dollar.

 

 

4.      Write the PL/SQL procedure to accomplish the following tasks:

a.       Obtain the first name and last name, of the owner whose number currently is store in I_OWNER_NUM.  Place these values in the variables I_FIRST_NAME and  I_LAST_NAME, respectively.  Output the contents I_OWNER_NUM,  I_FIRST_NAME and I_LAST_NAME.

 

 

b.      Obtain the location number, unit number, owner number, owner first name, and owner last name for the condo  whose condo ID is stored in I_CONDO_ID.   Place these values in the variables I_LOCATION_NUM, I_UNIT_NUM, I_OWNER_NUM, I_FIRST_NAME, I_LAST_NAME, respectively. Output the contents of  I_CONDO_ID,  I_LOCATION_NUM,  I_UNIT_NUM, I_OWNER_NUM , I_FIRST_NAME and I_LAST_NAME.