Question

QUESTIONS Q01: Use the comment feature of SQL to print your full name, AUID and username in three separate lines at the top of the script file. From this point onwards, you should use the comment feature to separate your answers for each question. (0.2 mark) Q02: Write a SQL statement to retrieve all the rows from the Product table. All the columns should be renamed with proper spacing using the alias feature. For example, the UnitPrice column should be renamed as "Unit Price". (0.2 mark) Q03: Write a SQL statement to retrieve all the rows from the Product table with ProductName, UnitPrice and UnitslnStock columns only. The rows should be sorted by UnitPrice in descending order. (0.2 mark) Q04: You need to locate the phone number of a shipper company named United Package. Write a SQL statement to retrieve only that piece of information from the appropriate table. Your SQL statement should cater for different casing scenarios. (0.2 mark) Q05: You are going to send a product list to all the customers by fax. Write a SQL statement to retrieve rows from the Customer table only if those customers have a fax number. (0.2 mark) Q06: You want to study some particular orders. Write a SQL statement to retrieve rows from the Order table when their OrderDate values are within the month of July in 1996. (0.2 mark)

Answer 1

You should have given table structure, but i am giving you sample answers please implement for all the columns.

1.

SET SERVEROUTPUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE("Name");
   DBMS_OUTPUT.PUT_LINE("Auid");
   DBMS_OUTPUT.PUT_LINE("Username");
END;

2.

select Product_Id as "Product_Id", UnitPrice as "UnitPrice" from Product;

3.

select ProductName as "ProductName", UnitPrice as "UnitPrice", UnitInStock as "UnitInStock" from Product order by UnitPrice;

4.

select phonenumber from shipper where company_name like 'United Package';

5.

select * from customer where faxnumber is not null;

6.

select * from order where orderdate>=1/7/1996 and orderdate<=31/7/1996;