Using Single-Row and Aggregate (Multi-Row) Functions */ --============================================== -- SECTION 1 --  -- -- Single-Row Functions --============================================== /* 1. Use the CONCAT function (not the || operator) to concatenate the customers’ last and first names into one column. Leave a ', ' between names. Give the column an alias: "Customer Full Name". Sort your results by the column alias. */ /* 2. Write a query using the SUBSTR function to display a list of distinct area codes (first 3 digits of phone number) for employees. Name the column “Area Codes”. Sort your results by the column alias. */ /* 3. Write a query using the LENGTH function to display a list of books with titles that are longer than 50 characters. Name the column “Long Titles”. Also, display the book titles of these books. Sort your results by longest title first and then by title. */ /* 4. Write a query using the MOD function to display a list of employees with salaries that are odd numbers. [You may need to look up Oracle's MOD() function.] For these employees, display Last name First name Current salary Using Oracle NVL() function [Look up the Oracle NVL() function to find out how it works.] Display Phone first and name the column as "Phone First" Display Email first and name the column as "Email First" Sort results by last and first names. */ /* 5. Write a query to calculate years of service for each employee.               Calculate years of service in two ways and display them as two separate columns. 1. SYSDATE-HIRE_DATE and name the column "Years of Service 1" 2. MONTHS_BETWEEN() function and name the column "Years of Service 2" BE SURE YOUR RESULTS ARE IN YEARS. Round each result to integers (whole numbers). Display Years of Service 1 Years of Service 2 Last Name First Name Sort results so the employee with most years of service displays first. */ /* 6. Write a SQL statement using CASE to classify employees into salary bands based on their salary: • LOW = 0 – 5000 • MID = 5000 – 10000 • HIGH = 10001 – 50000 Name the result of the CASE statement "Salary Band" Your results should display the employees’ name, salary, and salary band. Sort your results by salary descending and and salary band, then last and first names. */ --============================================== -- SECTION 2 -- -- Aggregate (Multi-Row) Functions --============================================== /* /* 1. Write a query using the COUNT function to display the number of employees in each department. These employees should have been hired between January 1, 1990 and December 31, 1995. Your results should be listed and sorted by department name (not Department_ID). Display Department Name Employee Count */ /* 2. Write a query using the MIN, MAX, and AVG functions to display the minimum, maximum, and average salaries of all employees. Your results should return 3 numbers in 1 row. */ /*               3. The company is performing a budget analysis. They need to determine how much they currently spend on employee salaries in each department. For each department, use the appropriate aggregate functions to display the following: Department Name Department Manager ID Minimum Salary Average Salary Maximum Salary Total Salary Number of Department Employees Sort your results to display the department with the highest total salary first. */ /* 4. Revise Q3 to select only those departments with 3 or more employees. */ /* 5. Write a query using the COUNT function to display the number of customers that placed orders. (NOT the number of Orders). Your results should return 1 number. */ /* 6. VERY DIFFICULT Write a query to calculate the order total for each order. There should be 14 Orders. IMPORTANT NOTE: You CANNOT use Order_Total in HOL_ORDERS because it contains NULL values. You will need to determine another way to calculate Order Total. Your results should display Order number Order date Customer last name Customer first name Order total Display the order total as “Order Total”. You will need to use SUM and GROUP BY. Sort your results so the highest order total displays first. */

Using Single-Row and Aggregate (Multi-Row) Functions
*/
--==============================================
-- SECTION 1
-- 
--
-- Single-Row Functions
--==============================================
/*
1. Use the CONCAT function (not the || operator) to concatenate the customers’
last and first names into one column.
Leave a ', ' between names.
Give the column an alias: "Customer Full Name".
Sort your results by the column alias.
*/
/*
2. Write a query using the SUBSTR function to display a list
of distinct area codes (first 3 digits of phone number) for employees.
Name the column “Area Codes”.
Sort your results by the column alias.
*/
/*
3. Write a query using the LENGTH function to display a list of books with
titles
that are longer than 50 characters.
Name the column “Long Titles”.
Also, display the book titles of these books.
Sort your results by longest title first and then by title.
*/
/*
4. Write a query using the MOD function to display a list of employees with
salaries that are odd numbers.
[You may need to look up Oracle's MOD() function.]
For these employees, display
Last name
First name
Current salary
Using Oracle NVL() function
[Look up the Oracle NVL() function to find out how it works.]
Display Phone first and name the column as "Phone First"
Display Email first and name the column as "Email First"
Sort results by last and first names.
*/
/*
5. Write a query to calculate years of service for each employee.

 

Calculate years of service in two ways and display them as two separate
columns.
1. SYSDATE-HIRE_DATE and name the column "Years of Service 1"
2. MONTHS_BETWEEN() function and name the column "Years of Service 2"
BE SURE YOUR RESULTS ARE IN YEARS.
Round each result to integers (whole numbers).

Display
Years of Service 1
Years of Service 2
Last Name
First Name
Sort results so the employee with most years of service displays first.
*/
/*
6. Write a SQL statement using CASE to classify employees into salary bands
based on their salary:
• LOW = 0 – 5000
• MID = 5000 – 10000
• HIGH = 10001 – 50000
Name the result of the CASE statement "Salary Band"
Your results should display the employees’ name, salary, and salary band.
Sort your results by salary descending and and salary band, then last and first
names.
*/
--==============================================
-- SECTION 2

--
-- Aggregate (Multi-Row) Functions
--==============================================
/*
/*
1. Write a query using the COUNT function to display the number of employees in
each department.
These employees should have been hired between
January 1, 1990 and December 31, 1995.
Your results should be listed and sorted by department name (not
Department_ID).
Display
Department Name
Employee Count
*/
/*
2. Write a query using the MIN, MAX, and AVG functions to display
the minimum, maximum, and average salaries of all employees.
Your results should return 3 numbers in 1 row.
*/
/*

 

3. The company is performing a budget analysis.
They need to determine how much they currently spend on employee salaries in
each department.
For each department, use the appropriate aggregate functions
to display the following:
Department Name
Department Manager ID
Minimum Salary
Average Salary
Maximum Salary
Total Salary
Number of Department Employees
Sort your results to display the department with the highest total salary
first.
*/
/*
4. Revise Q3 to select only those departments with 3 or more employees.
*/
/*
5. Write a query using the COUNT function to display the number of customers
that placed orders.
(NOT the number of Orders).
Your results should return 1 number.
*/
/*
6. VERY DIFFICULT
Write a query to calculate the order total for each order. There should be 14
Orders.
IMPORTANT NOTE: You CANNOT use Order_Total in HOL_ORDERS because it contains
NULL values.
You will need to determine another way to calculate Order Total.
Your results should display
Order number
Order date
Customer last name
Customer first name
Order total
Display the order total as “Order Total”.
You will need to use SUM and GROUP BY.
Sort your results so the highest order total displays first.
*/