EASY

Getting Started
How can I access the schemas, tables, and data used in the practice problems? Use livesql.oracle.com. All schemas and tables utilized in the current practice problem sets are readily available without the headache of software installation. Simply create an account and let the SQL mastering begin! For a subset of the problems, a script is required to be executed to create and subsequently populate the table referenced within the problem. Simply copy and paste the corresponding script provided in the Tables Requiring Script Execution section of the SQL PRACTICE PROBLEMS page.

Schema and Table Information
Information on the schemas, corresponding tables, and data used in the example problems can be found within the SQL PRACTICE PROBLEMS page. I recommend opening another browser window with this page pulled up for quick reference.

SELECT AND FROM CLAUSES

Problem 1
Retrieve all employee’s first names from the employees table.

SELECT
    hr.employees.first_name
FROM
    hr.employees;

Problem 2
Retrieve the employee ID, hire date, and the job ID for each employee.

SELECT
    hr.employees.employee_id,
    hr.employees.hire_date,
    hr.employees.job_id
FROM
    hr.employees;

Problem 3
Retrieve the department ID, department name, manager ID, and location ID for each department.

SELECT
    hr.departments.department_id,
    hr.departments.department_name,
    hr.departments.manager_id,
    hr.departments.location_id
FROM
    hr.departments;

Problem 4
Retrieve all employee IDs. Rename this column to read as “worker_id.”

SELECT
    hr.employees.employee_id AS worker_id
FROM
    hr.employees;

Problem 5
For each job, retrieve the job code, job title, minimum salary, and maximum salary. Rename these columns to read as job_code, title, minimum_salary, and maximum_salary, respectively.

SELECT
    hr.jobs.job_id AS job_code,
    hr.jobs.job_title AS title,
    hr.jobs.min_salary AS minimum_salary,
    hr.jobs.max_salary AS maximum_salary
FROM
    hr.jobs;

Problem 6
Retrieve the job ID, job title, maximum salary, minimum salary, and salary range for each job. Salary range is defined as the difference between the maximum salary and minimum salary. Alias the difference as “salary_range.”

SELECT
    hr.jobs.job_id,
    hr.jobs.job_title,
    hr.jobs.max_salary,
    hr.jobs.min_salary,
    hr.jobs.max_salary - hr.jobs.min_salary AS salary_range
FROM
    hr.jobs;

Problem 7
Retrieve the country id and country name for each country.

SELECT
    hr.countries.country_id,
    hr.countries.country_name
FROM
    hr.countries;

Problem 8
Retrieve the employee ID, email, and phone number for each employee.

SELECT
    hr.employees.employee_id,
    hr.employees.email,
    hr.employees.phone_number
FROM
    hr.employees;

Problem 9
Retrieve the job ID, job title, and minimum salary for each job at the company.

SELECT
    hr.jobs.job_id,
    hr.jobs.job_title,
    hr.jobs.min_salary
FROM
    hr.jobs;

Problem 10
Retrieve the location ID, city, and state/province for all warehouse/department locations.

SELECT
    hr.locations.location_id,
    hr.locations.city,
    hr.locations.state_province
FROM
    hr.locations;

WHERE CLAUSE WITH COMPARISON OPERATORS

Problem 11
Retrieve the location ID, street address, city, and state/province for departments or warehouses located in Texas.
Solution

SELECT
    hr.locations.location_id,
    hr.locations.street_address,
    hr.locations.city,
    hr.locations.state_province
FROM
    hr.locations
WHERE
    hr.locations.state_province = 'Texas';

Problem 12
Retrieve the employee ID, job ID, and hire date for employees hired in year 2007 or later.
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.job_id,
    hr.employees.hire_date
FROM
    hr.employees
WHERE
    hr.employees.hire_date >= '01-JAN-07';

Problem 13
Retrieve the department ID, department name, and location ID for departments/warehouses with location IDs other than 1700.
Solution

SELECT
    hr.departments.department_id,
    hr.departments.department_name,
    hr.departments.location_id
FROM
    hr.departments
WHERE
    hr.departments.location_id != 1700;

Problem 14
Retrieve the employee ID, first name, last name, and job ID for each programmer (i.e., those employees with a job ID of “IT_PROG.”
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.first_name,
    hr.employees.last_name,
    hr.employees.job_id
FROM
    hr.employees
WHERE
    hr.employees.job_id = 'IT_PROG';

Problem 15
Retrieve the location ID, city, and state/province, and country ID for department/warehouse locations outside of the United States.
Solution

SELECT
    hr.locations.location_id,
    hr.locations.city,
    hr.locations.state_province,
    hr.locations.country_id
FROM
    hr.locations
WHERE
    hr.locations.country_id != 'US';

Problem 16
Retrieve the employee ID, job ID, and salary for employees with a salary of greater than or equal to $10,000.
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.job_id,
    hr.employees.salary
FROM
    hr.employees
WHERE
    hr.employees.salary >= 10000;

WHERE CLAUSE WITH IN, NOT IN, AND BETWEEN LOGICAL OPERATORS

Problem 17
Retrieve the location ID, city, state/province, and country ID for department/warehouse locations in the United States or Canada.
Solution

SELECT
    hr.locations.location_id,
    hr.locations.city,
    hr.locations.state_province,
    hr.locations.country_id
FROM
    hr.locations
WHERE
    hr.locations.country_id IN ('US', 'CA');

Problem 18
Retrieve the location ID, city, state/province, and country ID for department/warehouse locations outside the United States and Canada.
Solution

SELECT
    hr.locations.location_id,
    hr.locations.city,
    hr.locations.state_province,
    hr.locations.country_id
FROM
    hr.locations
WHERE
    hr.locations.country_id NOT IN ('US', 'CA');

Problem 19
Retrieve the employee ID and salary for all associates with a salary between $6,000 and $10,000, inclusive.
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.salary
FROM
    hr.employees
WHERE
    hr.employees.salary BETWEEN 6000 AND 10000;

Problem 20
Retrieve the employee ID, first name, last name, job ID, hire date, and salary for all employees hired between 2004 and 2006, inclusive.
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.first_name,
    hr.employees.last_name,
    hr.employees.job_id,
    hr.employees.hire_date,
    hr.employees.salary
FROM
    hr.employees
WHERE
    hr.employees.hire_date BETWEEN '01-JAN-04' AND '31-DEC-06';

Problem 21
Retrieve the employee ID, job ID, and salary for employees with job IDs other than IT_PROG, SA_MAN, and PU_CLERK.
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.job_id,
    hr.employees.salary
FROM
    hr.employees
WHERE
    hr.employees.job_id NOT IN ('IT_PROG', 'SA_MAN', 'PU_CLERK');

Problem 22
Retrieve the employee ID, job ID, hire date, and salary for employees with salaries between $15,000 and $30,000, inclusive.
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.job_id,
    hr.employees.hire_date,
    hr.employees.salary
FROM
    hr.employees
WHERE
    hr.employees.salary BETWEEN 15000 AND 30000;

WHERE CLAUSE WITH LIKE AND NOT LIKE LOGICAL OPERATORS

Problem 23
Retrieve the employee ID and first names of employees with a first name starting with the letter ‘A.’
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.first_name
FROM
    hr.employees
WHERE
    hr.employees.first_name LIKE 'A%';

Problem 24
Retrieve the employee ID and first names of employees with a first name starting with the letter ‘C’ followed by any sequence of characters and ending with the letter ‘s.’
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.first_name
FROM
    hr.employees
WHERE
    hr.employees.first_name LIKE 'C%s';

Problem 25
Retrieve employee phone numbers where the first digit is a 5, 3rd to last digit is a 1, second to last digit is any number, and the final digit is a 9.
Solution

SELECT
    hr.employees.phone_number
FROM
    hr.employees
WHERE
    hr.employees.phone_number LIKE '5%1_9';

Problem 26
Retrieve the employee ID and last name of employees with a last name not starting with the letter ‘B.’
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.last_name
FROM
    hr.employees
WHERE
    hr.employees.last_name NOT LIKE 'B%';

Problem 27
Retrieve all employee IDs for employees with a phone number containing the sequence of digits, 423.
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.phone_number
FROM
    hr.employees
WHERE
    hr.employees.phone_number LIKE '%423%';

Problem 28
Retrieve the employee ID and job ID for those employees with job IDs starting with the letters ‘AD.’
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.job_id
FROM
    hr.employees
WHERE
    hr.employees.job_id LIKE 'AD%';

Problem 29
Retrieve the employee ID and salary for employees with a salary starting with the 1 digit followed by a single digit of any value and ending with 3 zeros (e.g., 14000).
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.salary
FROM
    hr.employees
WHERE
    hr.employees.salary LIKE '1_000';

Problem 30
Retrieve the country ID and country name for country names starting with ‘A’ and ending in ‘a.’
Solution

SELECT
    hr.countries.country_id,
    hr.countries.country_name
FROM
    hr.countries
WHERE
    hr.countries.country_name LIKE 'A%a';

Problem 31
Retrieve the employee ID and phone number of employees where the phone number doesn’t begin with ‘650.’
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.phone_number
FROM
    hr.employees
WHERE
    hr.employees.phone_number NOT LIKE '650%';

WHERE CLAUSE WITH IS NULL AND IS NOT NULL

Problem 32
Retrieve the department ID, department name, and manager ID for departments without a manager ID assigned.
Solution

SELECT
    hr.departments.department_id,
    hr.departments.department_name,
    hr.departments.manager_id
FROM
    hr.departments
WHERE
    hr.departments.manager_id IS NULL;

Problem 33
Retrieve the department ID, department name, and manager ID for departments with a manager ID assigned.
Solution

SELECT
    hr.departments.department_id,
    hr.departments.department_name,
    hr.departments.manager_id
FROM
    hr.departments
WHERE
    hr.departments.manager_id IS NOT NULL;

Problem 34
Retrieve the employee ID and commission percentage for those employees with existing commission percentages
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.commission_pct
FROM
    hr.employees
WHERE
    hr.employees.commission_pct IS NOT NULL;

Problem 35
Retrieve the employee ID and commission percentage for those employees with non-existing commission percentages.
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.commission_pct
FROM
    hr.employees
WHERE
    hr.employees.commission_pct IS NULL;

WHERE CLAUSE WITH MULTIPLE AND AND/OR LOGICAL OPERATORS

Problem 36
Retrieve the employee ID, job ID, salary, and manager ID for all employees with a manager ID of 100 or 108 and a salary greater than $12,000.
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.job_id,
    hr.employees.salary,
    hr.employees.manager_id
FROM
    hr.employees
WHERE
    hr.employees.manager_id IN (100, 108)
    AND hr.employees.salary > 12000;

Problem 37
Retrieve the job ID, job title, minimum salary, and maximum salary for management jobs or those jobs with a minimum salary greater than 20,000 and a maximum salary greater than 25,000.
Solution

SELECT
    hr.jobs.job_id,
    hr.jobs.job_title,
    hr.jobs.min_salary,
    hr.jobs.max_salary
FROM
    hr.jobs
WHERE
    hr.jobs.job_title LIKE '%Manager'
    OR (hr.jobs.min_salary > 20000
        AND hr.jobs.max_salary > 25000);

Problem 38
Retrieve the employee ID, job ID, and department ID employees for employees that meet one or more of the following conditions:
Department ID is equal to 90 and the job ID is equal to ‘AD_PRES’
Department ID is equal to 100 and the job ID is equal to ‘FI_ACCOUNT’
Employee ID is equal to 115
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.job_id,
    hr.employees.department_id
FROM
    hr.employees
WHERE
    (hr.employees.department_id = 90
    	AND hr.employees.job_id = 'AD_PRES')
    OR (hr.employees.department_id = 100
    	AND hr.employees.job_id = 'FI_ACCOUNT')
    OR hr.employees.employee_id = '115';

Problem 39
Retrieve the employee ID, hire date, job ID, and salary for employees hired on as programmers in year 2007.
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.hire_date,
    hr.employees.job_id,
    hr.employees.salary
FROM
    hr.employees
WHERE
    hr.employees.hire_date >= '01-JAN-07'
    AND hr.employees.hire_date < '01-JAN-08'
    AND hr.employees.job_id = 'IT_PROG';

Problem 40
Retrieve the employee ID, hire date, job ID, and salary for employees that were hired on as programmers or in the year 2007.
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.hire_date,
    hr.employees.job_id,
    hr.employees.salary
FROM
    hr.employees
WHERE
    (hr.employees.hire_date >= '01-JAN-07'
    	AND hr.employees.hire_date < '01-JAN-08')
    OR hr.employees.job_id = 'IT_PROG';

Problem 41
Retrieve the employee ID, hire date, job ID, and salary for employees that were hired in year 2007 or were hired on as programmers with a salary greater than 5000.
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.hire_date,
    hr.employees.job_id,
    hr.employees.salary
FROM
    hr.employees
WHERE
    (hr.employees.hire_date >= '01-JAN-07'
    	AND hr.employees.hire_date < '01-JAN-08')
    OR (hr.employees.job_id = 'IT_PROG'
    	AND hr.employees.salary > 5000);

Problem 42
Retrieve the employee ID, hire date, job ID, manager ID, and salary for employees that were hired in year 2007 or were hired on as programmers with a salary greater than 5000 or employees without a manager ID assigned and with a salary greater than 5000.
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.hire_date,
    hr.employees.job_id,
    hr.employees.manager_id,
    hr.employees.salary
FROM
    hr.employees
WHERE
    (hr.employees.hire_date >= '01-JAN-07'
    	AND hr.employees.hire_date < '01-JAN-08')
    OR ((hr.employees.job_id = 'IT_PROG'
    	OR hr.employees.manager_id IS NULL)
    	AND hr.employees.salary > 5000);

GROUP BY CLAUSE AND COMPUTING AGGREGATES

Problem 43
Retrieve the minimum salary, maximum salary, average salary, and total sum of salaries. Provide appropriate aliases for the aggregations.
Solution

SELECT
    MIN(hr.employees.salary) AS minimum_salary,
    MAX(hr.employees.salary) AS maximum_salary,
    AVG(hr.employees.salary) AS average_salary,
    SUM(hr.employees.salary) AS salary_summation
FROM
    hr.employees;

Problem 44
For each job ID, compute the average employee salary with respect to the job ID. Provide an appropriate alias for the aggregation.
Solution

SELECT
    hr.employees.job_id,
    AVG(hr.employees.salary) AS average_salary
FROM
    hr.employees
GROUP BY
    hr.employees.job_id;

Problem 45
For each job ID, compute the minimum and maximum salary with respect to the job ID. Provide appropriate aliases for the aggregations.
Solution

SELECT
    hr.employees.job_id,
    MIN(hr.employees.salary) AS minimum_salary,
    MAX(hr.employees.salary) AS maximum_salary
FROM
    hr.employees
GROUP BY
    hr.employees.job_id;

Problem 46
For each existing job ID and manager ID combination, compute the number of employees and the average salary for those employees. Provide appropriate aliases for the aggregations.
Solution

SELECT
    hr.employees.job_id,
    hr.employees.manager_id,
    COUNT(hr.employees.salary) AS employee_count,
    AVG(hr.employees.salary) AS average_salary
FROM
    hr.employees
GROUP BY
    hr.employees.job_id,
    hr.employees.manager_id;

Problem 47
For each region ID, compute the number of countries assigned the region ID. Provide an appropriate alias for the aggregation.
Solution

SELECT
    hr.countries.region_id,
    COUNT(hr.countries.region_id) AS country_count
FROM
    hr.countries
GROUP BY
    hr.countries.region_id;

Problem 48
For each manager ID, compute the number of employees assigned the manager ID. Provide an appropriate alias for the aggregation.
Solution

SELECT
    hr.employees.manager_id,
    COUNT(hr.employees.employee_id) AS employee_count
FROM
    hr.employees
GROUP BY
    hr.employees.manager_id;

Problem 49
For each manager ID, compute the maximum salary for employees assigned the manager ID. Provide an appropriate alias for the aggregation.
Solution

SELECT
    hr.employees.manager_id,
    MAX(hr.employees.salary) AS maximum_salary
FROM
    hr.employees
GROUP BY
    hr.employees.manager_id;

Problem 50
For each country ID and state/province combination, compute the number of department/warehouse locations. Provide an appropriate alias for the aggregation.
Solution

SELECT
    hr.locations.country_id,
    hr.locations.state_province,
    COUNT(hr.locations.state_province) AS warehouse_count
FROM
    hr.locations
GROUP BY
    hr.locations.country_id,
    hr.locations.state_province;

HAVING CLAUSE

Problem 51
For each job ID, compute the minimum and maximum salary with respect to the job ID. Return only job IDs with an range (MAX Salary – MIN Salary for each job ID) of salaries greater than or equal to $3,000. Provide appropriate aliases for the aggregations.
Solution

SELECT
    hr.employees.job_id,
    MIN(hr.employees.salary) AS minimum_salary,
    MAX(hr.employees.salary) AS maximum_salary
FROM
    hr.employees
GROUP BY
    hr.employees.job_id
HAVING
    MAX(hr.employees.salary) - MIN(hr.employees.salary) >= 3000;

Problem 52
For each existing job ID and manager ID combination, compute the number of employees and the average salary for those employees. Return only those job ID and manager ID combinations for which at least two employees are employed under the respective job ID and manager ID combination. Provide appropriate aliases for the aggregations.
Solution

SELECT
    hr.employees.job_id,
    hr.employees.manager_id,
    COUNT(hr.employees.salary) AS employee_count,
    AVG(hr.employees.salary) AS average_salary
FROM
    hr.employees
GROUP BY
    hr.employees.job_id,
    hr.employees.manager_id
HAVING
    COUNT(hr.employees.salary) > 1;

Problem 53
Retrieve the department ID(s) and the sum of the employees’ salaries for each department ID for departments with a total salary of greater than $100,000. Provide an appropriate alias for the aggregation.
Solution

SELECT
    hr.employees.department_id,
    SUM(hr.employees.salary) AS total_salary
FROM
    hr.employees
GROUP BY
    hr.employees.department_id
HAVING
    SUM(hr.employees.salary) > 100000;

Problem 54
Retrieve the department IDs and the number of employees where more than 5 employees are assigned/work in. Provide an appropriate alias for the aggregation.
Solution

SELECT
    hr.employees.department_id,
    COUNT(hr.employees.employee_id) AS employee_count
FROM
    hr.employees
GROUP BY
    hr.employees.department_id
HAVING
    COUNT(hr.employees.employee_id) > 5;

Problem 55
Retrieve the employee ID(s) and number of jobs each employee has held for employees that have held more than 2 jobs. Provide an appropriate alias for the aggregation.
Solution

SELECT
    hr.job_history.employee_id,
    COUNT(*) AS job_count
FROM
    hr.job_history
GROUP BY
    hr.job_history.employee_id
HAVING
    COUNT(*) > 1;

ORDER BY CLAUSE

Problem 56
Retrieve employee IDs, job IDs, hire dates, and salaries for all employees. Sort/order the result set by hire date in ascending order and by salary in descending order.
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.job_id,
    hr.employees.hire_date,
    hr.employees.salary
FROM
    hr.employees
ORDER BY
    hr.employees.hire_date,
    hr.employees.salary DESC

Problem 57
Retrieve country IDs, country names, and region IDs for all countries. Sort/order the result set by region ID in ascending order and by country name in ascending order.
Solution

SELECT
    hr.countries.country_id,
    hr.countries.country_name,
    hr.countries.region_id
FROM
    hr.countries
ORDER BY
    hr.countries.region_id,
    hr.countries.country_name;

Problem 58
Retrieve the employee ID and hire date for each employee. Sort/order the result set by hire date in ascending order (i.e., oldest to newest).
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.job_id,
    hr.employees.hire_date
FROM
    hr.employees
ORDER BY
    hr.employees.hire_date;

Problem 59
Retrieve the employee ID, job ID, and salary for each employee. Sort/order the result set by salary in descending order (i.e., largest salary to smallest salary).
Solution

SELECT
    hr.employees.employee_id,
    hr.employees.job_id,
    hr.employees.salary
FROM
    hr.employees
ORDER BY
    hr.employees.salary DESC;

Problem 60
Retrieve the manager ID, employee ID, and salary for each employee. Sort/order the result set by manager ID (ascending) and within each manager, sort employee salaries in descending order.
Solution

SELECT
    hr.employees.manager_id,
    hr.employees.employee_id,
    hr.employees.salary
FROM
    hr.employees
ORDER BY
    hr.employees.manager_id,
    hr.employees.salary DESC;

Problem 61
For each country ID, return the number of departments/warehouses. Sort/order the result set by the number of departments/warehouses located in each country. Provide an appropriate alias for the aggregation.
Solution

SELECT
    hr.locations.country_id,
    COUNT(hr.locations.country_id) AS warehouse_count
FROM
    hr.locations
GROUP BY
    hr.locations.country_id
ORDER BY
    warehouse_count DESC;

ASSORTED PROBLEMS

Problem 62
Write a query that returns a result set containing, for each product, the product ID, product name, product description, list price, and minimum price. In addition, include a derived column containing the difference between the listing price and the minimum price. Alias this column as “amount_above_min.”
Solution

SELECT
	oe.product_information.product_id,
	oe.product_information.product_name,
	oe.product_information.product_description,
	oe.product_information.list_price,
	oe.product_information.min_price,
	oe.product_information.list_price - oe.product_information.min_price AS amount_above_min
FROM
	oe.product_information;

Problem 63
Write a query that returns a result set containing, for each customer, the customer ID, customer first name, customer last name, and credit limit. Only include customers with a last name starting with ‘A’ that also have a credit limit less than or equal to 1200. Sort the result set by credit limit, ascending.
Solution

SELECT
	oe.customers.customer_id,
	oe.customers.cust_first_name,
	oe.customers.cust_last_name,
	oe.customers.credit_limit
FROM
	oe.customers
WHERE
	oe.customers.cust_last_name LIKE 'A%'
	AND oe.customers.credit_limit <= 1200
ORDER BY
	oe.customers.credit_limit;

Problem 64
Write a query that returns a result set containing, for each order, the order ID, order date, and order total. Only include orders placed in year 2008 and later with an order total greater than $50,000 or for those orders placed in year 2007 with an order total greater than $25,000. Sort the result set by order date (ascending) and by order total (descending).
Solution

SELECT
	oe.orders.order_id,
	oe.orders.order_date,
	oe.orders.order_total
FROM
	oe.orders
WHERE
	(oe.orders.order_date >= '01-JAN-08'
		AND oe.orders.order_total > 50000)
	OR (oe.orders.order_date BETWEEN '01-JAN-07' AND '31-DEC-07'
		AND oe.orders.order_total > 25000)
ORDER BY
	oe.orders.order_date,
	oe.orders.order_total DESC;

Problem 65
Write a query that returns a result set containing the number of customers for each gender and credit limit combination. Gender and credit limit data should be included in the result set. Sort the result set by credit limit (ascending) and gender (ascending).
Solution

SELECT
	oe.customers.gender,
	oe.customers.credit_limit,
	COUNT(oe.customers.gender)
FROM
	oe.customers
GROUP BY
	oe.customers.gender,
	oe.customers.credit_limit
ORDER BY
	oe.customers.credit_limit,
	oe.customers.gender;

Problem 66
Write a query that returns a result set containing the number of customers for each natural language support territory (nls_territory), gender, and credit limit combination. NLS territory, gender, and credit limit data should be included in the result set. Only include those combinations for which a single customer is aligned/belongs. Sort the result set by NLS territory (ascending), credit limit (ascending), and gender (ascending).
Solution

SELECT
	oe.customers.nls_territory,
	oe.customers.gender,
	oe.customers.credit_limit,
	COUNT(*)
FROM
	oe.customers
GROUP BY
	oe.customers.nls_territory,
	oe.customers.gender,
	oe.customers.credit_limit
HAVING
	COUNT(*) = 1
ORDER BY
	oe.customers.nls_territory,
	oe.customers.credit_limit,
	oe.customers.gender;

Problem 67
For each order, retrieve the order ID, order date, customer ID, order total, and sales rep ID. Sort the result set by customer ID (ascending) and order date (ascending).
Solution

SELECT
	oe.orders.order_id,
	oe.orders.order_date,
	oe.orders.customer_id,
	oe.orders.order_total,
	oe.orders.sales_rep_id
FROM
	oe.orders
ORDER BY
	oe.orders.customer_id,
	oe.orders.order_date;

Problem 68
Retrieve only those orders with an order total greater than $100,000. Include the order ID, order date, customer ID, order total, and sales rep ID for each order. Sort the result set by customer ID (ascending) and order date (ascending).
Solution

SELECT
	oe.orders.order_id,
	oe.orders.order_date,
	oe.orders.customer_id,
	oe.orders.order_total,
	oe.orders.sales_rep_id
FROM
	oe.orders
WHERE
	oe.orders.order_total > 100000
ORDER BY
	oe.orders.customer_id,
	oe.orders.order_date;

Problem 69
For each order, retrieve the order ID, line-item ID, product ID, unit price, and quantity of the first line item.
Solution

SELECT
	oe.order_items.order_id,
	oe.order_items.line_item_id,
	oe.order_items.product_id,
	oe.order_items.unit_price,
	oe.order_items.quantity
FROM
	oe.order_items
WHERE
	oe.order_items.line_item_id = 1;

Problem 70
For each order, retrieve the order ID, order date, and order total. Only include those orders with an order total between $75,000 and $100,000, inclusive. Sort the result set by order total, descending.
Solution

SELECT
	oe.orders.order_id,
	oe.orders.order_date,
	oe.orders.order_total
FROM
	oe.orders
WHERE
	oe.orders.order_total BETWEEN 75000 AND 100000
ORDER BY
	oe.orders.order_total DESC;

Problem 71
Retrieve all customers with a national language support territory (nls_territory) of America, India, or China. Include the customer ID, customer first name, customer last name, and NLS territory for each customer. Sort the result set by NLS territory (ascending) and customer ID (ascending).
Solution

SELECT
	oe.customers.customer_id,
	oe.customers.cust_first_name,
	oe.customers.cust_last_name,
	oe.customers.nls_territory
FROM
	oe.customers
WHERE
	oe.customers.nls_territory IN ('AMERICA', 'INDIA', 'CHINA')
ORDER BY
	oe.customers.nls_territory,
	oe.customers.customer_id;

Problem 72
Retrieve product information for all products with product names starting with ‘Inkjet.’ Include the product ID, product name, and product description for each product.
Solution

SELECT
	oe.product_information.product_id,
	oe.product_information.product_name,
	oe.product_information.product_description
FROM
	oe.product_information
WHERE
	oe.product_information.product_name LIKE 'Inkjet%';

Problem 73
Retrieve the product ID, supplier ID, and catalog URL for products with a catalog URL containing the string, 102094.
Solution

SELECT
	oe.product_information.product_id,
	oe.product_information.supplier_id,
	oe.product_information.catalog_url
FROM
	oe.product_information
WHERE
	oe.product_information.catalog_url LIKE '%102094%';

Problem 74
Retrieve the order ID, order date, order total, customer ID, and sales rep ID. Only include online orders in your result set. Assume all orders without a sales rep ID are online orders.
Solution

SELECT
	oe.orders.order_id,
	oe.orders.order_date,
	oe.orders.order_total,
	oe.orders.customer_id,
	oe.orders.sales_rep_id
FROM
	oe.orders
WHERE
	oe.orders.sales_rep_id IS NULL;

Problem 75
Retrieve online orders. Only include online orders placed in year 2007 or online orders with an order total greater than or equal to $100,000. Include the order ID, order date, order total, and sales rep ID for each online order. Assume all orders without a sales rep ID are online orders. Sort the result set by order date, ascending.
Solution

SELECT
	oe.orders.order_id,
	oe.orders.order_date,
	oe.orders.order_total,
	oe.orders.sales_rep_id
FROM
	oe.orders
WHERE
    oe.orders.sales_rep_id IS NULL
	AND (oe.orders.order_date BETWEEN '01-JAN-07' AND '31-DEC-07'
		OR oe.orders.order_total >= 100000)
ORDER BY
	oe.orders.order_date;

Problem 76
Retrieve product information for products that have a minimum price greater than or equal to $1,000 or less than or equal to $10. In addition, only include products with a category ID of 19. Include the product ID, product name, category ID, and minimum price for each product. Sort the result set by minimum price, descending.
Solution

SELECT
	oe.product_information.product_id,
	oe.product_information.product_name,
	oe.product_information.category_id,
	oe.product_information.min_price
FROM
	oe.product_information
WHERE
	(oe.product_information.min_price >= 1000
		OR oe.product_information.min_price <= 10)
	AND oe.product_information.category_id = 19
ORDER BY
	oe.product_information.min_price DESC;

Problem 77
Compute/retrieve the minimum order total, maximum order total, sum of all order totals, the number of orders placed, and the average of all order totals. Provide meaningful aliases/names to the derived columns.
Solution

SELECT
	MIN(oe.orders.order_total) AS min_order_total,
	MAX(oe.orders.order_total) AS max_order_total,
	SUM(oe.orders.order_total) AS order_total,
	COUNT(oe.orders.order_total) AS order_count,
	AVG(oe.orders.order_total) AS average_order_total
FROM
	oe.orders;

Problem 78
For each customer, compute/retrieve the minimum order total, maximum order total, sum of all order totals, the number of orders placed, and the average of all order totals. Provide meaningful aliases/names to the derived columns. The customer ID should be the first column in your result set. Sort the result set by the sum of order totals, descending.
Solution

SELECT
	oe.orders.customer_id,
	MIN(oe.orders.order_total) AS min_order_total,
	MAX(oe.orders.order_total) AS max_order_total,
	SUM(oe.orders.order_total) AS order_total,
	COUNT(oe.orders.order_total) AS order_count,
	AVG(oe.orders.order_total) AS average_order_total
FROM
	oe.orders
GROUP BY
	oe.orders.customer_id
ORDER BY
	SUM(oe.orders.order_total) DESC;

Problem 79
For each product ID, compute/retrieve the number orders placed for that item. The product ID should be the first column in your result set. Provide a meaningful alias/name to the derived column. Sort the result set by the number of times a product was ordered, descending.
Solution

SELECT
	oe.order_items.product_id,
	COUNT(oe.order_items.product_id) AS order_count
FROM
	oe.order_items
GROUP BY
	oe.order_items.product_id
ORDER BY
	order_count DESC;

Problem 80
For each existing account manager ID and credit limit combination, compute/retrieve the number of customers aligned to the manager ID and credit limit combination. Account manager ID and credit limit should be included in your result set. Provide a meaningful alias/name to the derived column. Sort the result set by account manager ID (ascending) and by credit limit (descending).
Solution

SELECT
	oe.customers.account_mgr_id,
	oe.customers.credit_limit,
	COUNT(oe.customers.credit_limit) AS customer_count
FROM
	oe.customers
GROUP BY
	oe.customers.account_mgr_id,
	oe.customers.credit_limit
ORDER BY
	oe.customers.account_mgr_id,
	oe.customers.credit_limit DESC;

Problem 81
Retrieve the number of products containing product information for each product category ID. Only include those product category IDs with more than 25 products aligned to the product category ID. The product category ID should be the first column in your result set. Provide a meaningful alias/name to the derived column. Sort the result set by the number of products in each product category ID, descending.
Solution

SELECT
	oe.product_information.category_id,
	COUNT(oe.product_information.category_id) AS product_count
FROM
	oe.product_information
GROUP BY
	oe.product_information.category_id
HAVING
	COUNT(oe.product_information.category_id) > 25
ORDER BY
	product_count DESC;

Problem 82
For each customer that has placed an order, compute/retrieve the date the customer made their first order and the sum of all order totals. Only include customers that made their first order before year 2007 and have a sum of all order totals greater than $150,000. The customer ID should be the first column in your result set. Provide meaningful aliases/names to the derived columns. Sort the result set by the first order’s order date.
Solution

SELECT
	oe.orders.customer_id,
	MIN(oe.orders.order_date) AS first_order_date,
	SUM(oe.orders.order_total) AS order_total
FROM
	oe.orders
GROUP BY
	oe.orders.customer_id
HAVING
	MIN(oe.orders.order_date) <= '31-DEC-06'
	AND SUM(oe.orders.order_total) > 150000
ORDER BY
	MIN(oe.orders.order_date);

Problem 83
Retrieve the daily product category sales for April 2023. In the output, include the report date, product category, and total sales. Sort the result set in ascending order by report date and product category.

SELECT
	north_america_sale.report_date,
    north_america_sale.product_category,
    north_america_sale.total_sale
FROM
	north_america_sale
WHERE
	north_america_sale.report_date >= '01-APR-23'
	AND north_america_sale.report_date < '01-MAY-23'
ORDER BY
    north_america_sale.report_date,
    north_america_sale.product_category;

Problem 84
Building onto your solution from problem 83, retrieve only those daily sales for the Software product category. Remove the sorting based on product category.

SELECT
	north_america_sale.report_date,
    north_america_sale.product_category,
    north_america_sale.total_sale
FROM
	north_america_sale
WHERE
	north_america_sale.report_date >= '01-APR-23'
	AND north_america_sale.report_date < '01-MAY-23'
	AND north_america_sale.product_category = 'Software'
ORDER BY
    north_america_sale.report_date;

Problem 85
Compute the total sales across all product categories for each day occurring in the months of July, August, and September of 2023. Alias the computed sale amount column as ‘daily_total_sale’. In the output, include the report date and daily total sales. Sort the result set in ascending order by report date.

SELECT
	north_america_sale.report_date,
    SUM(north_america_sale.total_sale) AS daily_total_sale
FROM
	north_america_sale
WHERE
	north_america_sale.report_date >= '01-JUL-23'
	AND north_america_sale.report_date < '01-SEP-23'
GROUP BY
    north_america_sale.report_date
ORDER BY
	north_america_sale.report_date;

Problem 86
Building onto your previous solution from problem 85, only include those days where the daily total sales amount is greater than 1,000,000. Sort the result set in ascending order by report date.

SELECT
	north_america_sale.report_date,
    SUM(north_america_sale.total_sale) AS daily_total_sale
FROM
	north_america_sale
WHERE
	north_america_sale.report_date >= '01-JUL-23'
	AND north_america_sale.report_date < '01-SEP-23'
GROUP BY
    north_america_sale.report_date
HAVING
    SUM(north_america_sale.total_sale) > 1000000
ORDER BY
	north_america_sale.report_date;

Problem 87
For each member type, compute the total number of members. Alias the total number of members of each member type as “member_count.” In the output, include the member type ID and member count. Sort the result set in descending order by member_count.

SELECT
    member.member_type_id,
	COUNT(member.member_type_id) AS member_count
FROM
	member
GROUP BY
    member.member_type_id
ORDER BY
    COUNT(member.member_type_id) DESC;

Problem 88
For each employee, compute the total number of members enrolled. Alias the total number of members of each member type as “enrollment_count.” In the output, include the employee ID and enrollment count. Sort the result set in descending order by enrollment_count.

SELECT
    member.employee_id,
	COUNT(member.member_id) AS enrollment_count
FROM
	member
GROUP BY
    member.employee_id
ORDER BY
    COUNT(member.member_id) DESC;

Problem 89
Compute the average, minimum, maximum, and range (i.e., maximum – minimum) of using all the prices of all products. Provide meaningful aliases/names to the derived columns.

SELECT
    AVG(product.price) AS average_price,
	MAX(product.price) AS maximum_price,
	MIN(product.price) AS minimum_price,
	MAX(product.price) - MIN(product.price) AS price_range
FROM
	product;

Problem 90
Write a query that returns a result set containing the product ID, description, and price for those products with a description containing the sequence of characters, “Chocolate.”

SELECT
	product.product_id,
	product.description,
    product.price
FROM
	product
WHERE
    product.description LIKE '%Chocolate%';

Problem 91
Compute the number of members enrolled each day based on the member enrollment date values. Alias the number of members enrolled as “enrollment_count.” In the output, include the enrollment date and enrollment count. Sort the result set in ascending order by enrollment_date.

SELECT
	member.enrollment_date,
    COUNT(member.member_id) AS enrollment_count
FROM
	member
GROUP BY
    member.enrollment_date
ORDER BY
    member.enrollment_date;

Problem 92
Building onto your previous solution from problem 91, only include those days where the number of members enrolled is at least 3.

SELECT
	member.enrollment_date,
    COUNT(member.member_id) AS enrollment_count
FROM
	member
GROUP BY
    member.enrollment_date
HAVING
	COUNT(member.member_id) >= 3
ORDER BY
    member.enrollment_date;

Problem 93
Retrieve member details for those members enrolled by the employee with an ID value of 1 or with an enrollment date occurring within the month of March 2024. In the output, include the member ID, member type ID, enrollment date, email address, and employee ID.

SELECT
    member.member_id,
	member.member_type_id,
	member.enrollment_date,
	member.email_address,
	member.employee_id
FROM
	member
WHERE
	member.employee_id = 1
	OR (
    	enrollment_date >= '01-MAR-24'
    	AND enrollment_date < '01-APR-24'
    );

Problem 94
Compute the number of members associated with each member type. Alias the number of members as “member_count.” In the output, include the member type ID and member count. Sort the result set in ascending order by member type ID.

SELECT
	member.member_type_id,
    COUNT(member.member_id) AS member_count
FROM
	member
GROUP BY
	member.member_type_id
ORDER BY
	member_type_id;

Problem 95
Write a query that returns a result set containing the employee ID, last name, first name, hire date, job ID, and salary for those employees hired prior to the ice cream shop’s opening date (i.e., January 1, 2024).

SELECT
	employee.employee_id,
    employee.last_name,
    employee.first_name,
    employee.hire_date,
    employee.job_id,
    employee.salary
FROM
	employee
WHERE
    employee.hire_date < '01-JAN-24';

Problem 96
Adjust your solution to problem 96 to compute the number of employees hired prior to the ice cream shop’s opening date (i.e., January 1, 2024). Alias the number of employees hired as “employee_count.”

SELECT
	COUNT(employee.employee_id) AS employee_count
FROM
	employee
WHERE
    employee.hire_date < '01-JAN-24';

Problem 97
Building onto your solution to the previous problem, break out the total number of employees hired by job ID value. There should be one row per job ID value and the corresponding employee count.

SELECT
	employee.job_id,
	COUNT(employee.employee_id) AS employee_count
FROM
	employee
WHERE
    employee.hire_date < '01-JAN-24'
GROUP BY
	employee.job_id;

Problem 98
Compute the average salary for each job. Only include those job ID values where more than one employee holds the corresponding job. Alias the average salary column as “average_salary.” In the output, include the job ID corresponding average salary.

SELECT
	employee.job_id,
    AVG(employee.salary) AS average_salary
FROM
	employee
GROUP BY
	employee.job_id
HAVING
	COUNT(employee.employee_id) > 1;

Problem 99
Compute the ice cream shop’s daily sales. Alias the daily sales column as “daily_sales.” In the output, include the purchase date and daily sales. Sort the result set in ascending order by purchase date.

SELECT
    purchase_date,
	SUM(purchase_total) AS daily_sales
FROM
	purchase
GROUP BY
    purchase_date
ORDER BY
    purchase_date;

Problem 100
Retrieve the purchase details for purchases where the total purchase value exceeds $10, and the purchase date is in January 2024. In the output, include the purchase ID, member ID, purchase date, employee ID, and purchase total. Sort the result set in ascending order by purchase date.

SELECT
	purchase.purchase_id,
    purchase.member_id,
    purchase.purchase_date,
    purchase.employee_id,
    purchase.purchase_total
FROM
	purchase
WHERE
	purchase.purchase_total >= 10
    AND purchase.purchase_date >= '01-JAN-24'
    AND purchase.purchase_date < '01-FEB-24'
ORDER BY
    purchase_date;

Problem 101
For each member, compute the total amount spent. Alias this computed amount as “total_sales.” In the output, include the member ID and total sales. Sort the result set in descending order by total sales.

SELECT
    purchase.member_id,
    SUM(purchase.purchase_total) AS total_sales
FROM
	purchase
GROUP BY
    purchase.member_id
ORDER BY
    SUM(purchase.purchase_total) DESC;

Problem 102
Building onto your solution to the previous problem, only include those members with a total sales value greater than or equal to $50 and more than three purchases.

SELECT
    purchase.member_id,
    SUM(purchase.purchase_total) AS total_sales
FROM
	purchase
GROUP BY
    purchase.member_id
HAVING
    SUM(purchase.purchase_total) >= 50
    AND COUNT(purchase.purchase_id) > 3
ORDER BY
    SUM(purchase.purchase_total) DESC;

Problem 103
Compute the distinct number of times each product has been purchased. Alias this computed value as “purchase_count.” In the output, include the product ID and purchase count. Sort the result set in descending order by purchase count.

SELECT
	purchase_item.product_id,
    COUNT(purchase_item.purchase_id) AS purchase_count
FROM
	purchase_item
GROUP BY
	purchase_item.product_id
ORDER BY
    COUNT(purchase_item.purchase_id) DESC;

Problem 104
For each purchase, compute the total number of items purchased based on quantity. Alias this computed value as “item_count.” In the output, include the purchase ID and item count. Sort the result set in descending order by item count.

SELECT
	purchase_item.purchase_id,
    SUM(purchase_item.quantity) AS total_quantity
FROM
	purchase_item
GROUP BY
	purchase_item.purchase_id
ORDER BY
	SUM(purchase_item.quantity) DESC;

Problem 105
Retrieve the purchase ID values for those purchases containing product ID values 1, 4, or 8. In the output, include the purchase ID.

SELECT
	DISTINCT purchase_item.purchase_id
FROM
	purchase_item
WHERE
	purchase_item.product_id IN (1, 4, 8);