Reference Datatypes and Functions in Queries

CS 560 Advanced Database

Lab 3-1

Reference datatypes and functions in queries

Use the S_EMP and S_PRODUCT tables to complete the following exercises

1.Initiate the SQL*Plus session

2.Display the product names with all letters capitalized

3.For each employee, calculate the number of months between today and the date the employee was hired. Order your result by the number of months employed. Round the number of months upward to the closest whole number.

4.Display the full employee name, annual salary, and start date for all employees whose annual salary is $15,000 or more. Format the start date to look like the "3rd of December, 1982".

5.Display the employee last name in lowercase and salary rounded to the hundreds for all employees.

6.Display the number of years that have passed since Carmen Valasquez, employee ID 1, started the company.

7.For the products whose name includes the word "Ski", display the product name in uppercase, followed by a dash, followed by the first 15 characters of the short description.

8.Display the employee name and commission percent of all employees who are eligible to make a commission.

9.Write a query which produces the following for each employee:
employee name> earns <salary> but wants <3 times salary>.
For example:
ALLEN earns 1100 monthly but wants 3300.

10.Log off the Oracle server.

Lab 3-2

Return rows based upon groups of rows.

Use the S_ORD, S_ITEM, S_CUSTOMER, S_DEPT, and S_PRODUCT tables to complete the following exercises.

1.Initiate a SQL*Plus session.

2.Display the highest and lowest order totals in the system. Label the columns "Highest" and "Lowest".

3.Display the number of products listed in the S_PRODUCT table labeled "Number of Products".

4.Display the number of line items in each order under each order ID, labeled "Number of Items".

5.Display the customer count for each sales representative in the S_CUSTOMER table, labeled "Number of Customers".
Does every customer displayed have a sales representative? ______

6.Display the region ID and the number of departments within each region. Label the number of departments.

7.Display the product ID and the number of times it was ordered labeled "TIMES ORDERED."

8.Display the products from step 7, except display only the products that have been ordered at least 3 times labeled "TIMES ORDERED". Order the data by the number of products ordered.

9.Display the country name and the number of customers in the USA, labeled "CUSTOMERS".

10.Display the region ID and the number of customers for each region. Label the number of customers.

11.Display the order ID and total item count for each order of 100 or more items. For example, if order number 99 contains quantity 30 of one item, and quantity 75 of another item, then the total item count for that order is 105.

12.Display the average quantity of each order in the S_ITEM table. Only display the order ID if the average is higher than 70. Round the average quantity to the nearest whole number.

13.Display the average price of an item in the S_ITEM table. Label the column "Average Price". Round the average price to two decimal places.

14.Log off the Oracle Server.

Lab 3-3

Display data from multiple tables.

Use the S_EMP, S_DEPT, S_CUSTOMER, S_REGION, S_ORD, S_ITEM, and S_PRODUCR tables to complete the following exercises.

1.Initiate a SQL*Plus session.

2.Display each employee's last name, department ID, and the name of their department.

3.Display the employee name and department name for Smith.

4.Display the product name, product ID and quantity ordered of all items in order number 101. Label the quantity column "ORDERED".

5.Display the ID and name of each department with the total number of employees in the department.

6.Display the customer ID and the last name of their sales representative. Order the list by last name.

7.Display the sales representative last name and the customer name. Suppress the repetition of the sales representative name.

8.Display the department name and region name of all departments that have more than 2 employees.

9.Display the region ID and name and the number of departments within each region.

10.Display the customer name and the number of orders for each customer. Label the number of orders.

11.Display the last name, region name and commission percent of all employees who earn a commission.

12.Log off the Oracle Server.