MEDIUM

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.

Problem 1
For each department/warehouse location, retrieve the location ID, street address, postal code, city, state/province, and country name. Ensure your result set represents all locations even if the country ID for the location does not match an existing country ID in the countries table.

SELECT
    hr.locations.location_id,
    hr.locations.street_address,
    hr.locations.postal_code,
    hr.locations.city,
    hr.locations.state_province,
    hr.countries.country_name
FROM
    hr.locations
    LEFT JOIN
    hr.countries
    	ON hr.locations.country_id = hr.countries.country_id;

Problem 2
For each country ID, retrieve the location IDs, street addresses, postal codes, cities, states/provinces, and country names for department(s)/warehouse(s) that are tied to the respective country ID. Include country name data even if a department/warehouse is not currently located in the country.

SELECT
    hr.locations.location_id,
    hr.locations.street_address,
    hr.locations.postal_code,
    hr.locations.city,
    hr.locations.state_province,
    hr.countries.country_name
FROM
    hr.locations
    RIGHT JOIN
    hr.countries
    	ON hr.locations.country_id = hr.countries.country_id;

Problem 3
For each department, retrieve the department ID, department name, first name and last name of the manager heading the department, and the manager’s email address. In addition, include the street address, city, state/province, and country name for which the department is located. Sort/order the result set by department ID, ascending.

SELECT
    hr.departments.department_id,
    hr.departments.department_name,
    hr.departments.manager_id,
    hr.departments.location_id,
    hr.employees.employee_id,
    hr.employees.first_name,
    hr.employees.last_name,
    hr.employees.email,
    hr.locations.street_address,
    hr.locations.city,
    hr.locations.state_province,
    hr.locations.country_id,
    hr.countries.country_name
FROM
    hr.departments
	INNER JOIN
    hr.employees
    	ON hr.departments.manager_id = hr.employees.employee_id
	INNER JOIN
    hr.locations
	    ON hr.departments.location_id = hr.locations.location_id
	INNER JOIN
    hr.countries
        ON hr.locations.country_id = hr.countries.country_id
ORDER BY
    hr.departments.department_id;

Problem 4
Retrieve the customer ID, customer first name, customer last name, order ID, order date, and order total for all orders. Only include customers that have made an order. Sort the result set by customer ID (ascending) and order date (ascending).

SELECT
	oe.customers.customer_id,
	oe.customers.cust_first_name,
	oe.customers.cust_last_name,
	oe.orders.order_id,
	oe.orders.order_date,
	oe.orders.order_total
FROM
	oe.customers
	INNER JOIN
	oe.orders
		ON oe.customers.customer_id = oe.orders.customer_id
ORDER BY
	oe.customers.customer_id,
	oe.orders.order_date;

Problem 5
Retrieve the customer ID, customer first name, customer last name for customers that have not made an order. Sort the result set by customer ID, ascending. Use a join in conjunction with a WHERE clause to achieve this. Do not use a subquery.

SELECT
	oe.customers.customer_id,
	oe.customers.cust_first_name,
	oe.customers.cust_last_name
FROM
	oe.customers 
	LEFT JOIN
	oe.orders
		ON oe.customers.customer_id = oe.orders.customer_id
WHERE
	oe.orders.order_id IS NULL
ORDER BY
	oe.customers.customer_id;

Problem 6
For each order, retrieve the order ID, order date, and line-item IDs associated with the order ID. In addition, for each of the line-items associated with an order ID, include the unit price, quantity, and the sale amount (unit price * quantity) for the line item. Provide the derived sale amount column with an alias of “sale_amount.” Sort the result set by order date (ascending), order ID (ascending), and line-item ID (ascending).

SELECT
	oe.orders.order_id,
	oe.orders.order_date,
	oe.order_items.line_item_id,
	oe.order_items.unit_price,
	oe.order_items.quantity,
	oe.order_items.unit_price * oe.order_items.quantity AS sale_amount
FROM
	oe.orders
	INNER JOIN
	oe.order_items
		ON oe.orders.order_id = oe.order_items.order_id
ORDER BY
	oe.orders.order_date,
	oe.orders.order_id,
	oe.order_items.line_item_id;

Problem 7
Build onto your solution for problem 6. For each order, retrieve the order ID, order date, and line-item IDs associated with the order ID. In addition, for each of the line-items associated with an order ID, include the product ID, product name, product description, unit price, quantity, and the sale amount (unit price * quantity) for the line item. Only include those records with a category ID of 19. Provide the derived sale amount column with an alias of “sale_amount.” Sort the result set by order date (ascending), order ID (ascending), and line-item ID (ascending).