single_entity.sql

SET
	search_path TO classicmodels;

-- =====================================================
-- Prepare a list of offices sorted by country, state, city.
-- =====================================================
SELECT
	*
FROM
	offices
ORDER BY
	country,
	state,
	city;

-- =====================================================
-- How many employees are there in the company?
-- =====================================================
SELECT
	COUNT(DISTINCT employeenumber)
FROM
	employees;

-- =====================================================
-- What is the total of payments received?
-- =====================================================
SELECT
	SUM(amount) AS total_payments
FROM
	payments;

-- =====================================================
-- List the product lines that contain 'Cars'
-- =====================================================
SELECT
	productline
FROM
	productlines
WHERE
	productline LIKE '%Cars%';

-- =====================================================
-- Report total payments for October 28, 2004.
-- =====================================================
SELECT
	SUM(amount) AS october_28_2004_payments
FROM
	payments
WHERE
	paymentdate = '2004-10-28';

-- =====================================================
-- Report those payments greater than $100,000.
-- =====================================================
SELECT
	checknumber,
	amount
FROM
	payments
WHERE
	amount > 100000
ORDER BY
	amount DESC;

-- =====================================================
-- List the products in each product line.
-- =====================================================
SELECT
	productname,
	productline
FROM
	products
ORDER BY
	productline;

-- =====================================================
-- How many products in each product line?
-- =====================================================
SELECT
	productline,
	COUNT(productname) AS products_count
FROM
	products
GROUP BY
	productline
ORDER BY
	products_count DESC;

-- =====================================================
-- What is the minimum payment received?
-- =====================================================
SELECT
	*
FROM
	payments
WHERE
	amount = (
		SELECT
			MIN(amount)
		FROM
			payments
	);

-- =====================================================
-- List all payments greater than twice the average payment.
-- =====================================================
SELECT
	*
FROM
	payments
WHERE
	amount > (
		SELECT
			2 * AVG(amount)
		FROM
			payments
	);

-- =====================================================
-- What is the average percentage markup of the MSRP on buyPrice?
-- =====================================================
SELECT
	ROUND(AVG(100.0 * (msrp - buyprice) / buyprice), 2) AS avg_percentage_markup
FROM
	products;

-- =====================================================
-- How many distinct products does ClassicModels sell?
-- =====================================================
SELECT
	COUNT(productcode) AS num_products
FROM
	products;

-- =====================================================
-- Report the name and city of customers who don't have sales representatives?
-- =====================================================
SELECT
	customername,
	city
FROM
	customers
WHERE
	salesrepemployeenumber IS NULL;

-- =====================================================
-- What are the names of executives with VP or Manager in their title?
-- =====================================================
SELECT
	firstname || ' ' || lastname AS full_name,
	jobtitle
FROM
	employees
WHERE
	jobtitle ~* '\mVP\M'
	OR jobtitle ~* '\mManager\M';

-- =====================================================
-- Which orders have a value greater than $5,000?
-- =====================================================
SELECT
	ordernumber,
	SUM(quantityordered * priceeach) AS order_amount
FROM
	orderdetails
GROUP BY
	ordernumber
HAVING
	SUM(quantityordered * priceeach) > 5000
ORDER BY
	order_amount DESC;