Instructions
Requirements and Specifications
- List the id, name, and price for all products with price greater than the average product price.
- For each product, list its id and total quantity ordered. Products are listed in ascending order of product_id.
- For each product, list its id and total quantity ordered. Products are listed in ascending order of total quantity ordered.
- For each product, list its id, name and total quantity ordered. Products are listed in ascending order of product_id.
- List the name for all customers who have placed order(s). Each customer name appears exactly once. Customer names are sorted in ascending alphabetical order. Use equijoin for this query.
- Implement the query from #5 using IN along with a subquery. Add the requirement that the customers’ orders have been placed after 23-OCT-2008.
- For each city, list the number of customers from the city, who have placed order(s). Cities are listed in ascending alphabetical order. Use equijoin for this query.
- Implement the query from #7 using NATURAL JOIN.
- Implement the query from #7 using IN along with a subquery.
- List the id for all products, which have NOT been ordered since after the date 28-OCT-2008. Sort the results by product_id in descending order. Use MINUS for this query.
- List the id for all Arizona customers, who have placed order(s) since on or after 27-OCT-2008. Sort the results by the customer id in ascending order. Use INTERSECT for this query.
- Implement the query from #11 using IN along with a subquery.
- 13. List the ids for all California customers along with all customers who have placed order(s) since on or after 23-OCT-2008. Sort the results by the customer id in descending order. Use UNION for this query.
- 14. List the id, name and total quantity ordered for all products with total quantity ordered greater than 8.
- 15. List the id, name and total quantity ordered for all products with total quantity ordered greater than 4 and were ordered by Utah customers.
Source Code
-- Question 1
SELECT id, name, price FROM products WHERE price > AVG(price);
-- Question 2
SELECT id, quantity FROM products ORDER BY id ASC;
-- Question 3
SELECT id, quantity FROM products ORDER BY quantity ASC;
-- Question 4
SELECT id, name, quantity FROM products ORDER BY id ASC
-- Question 5
SELECT A.name FROM customers A JOIN orders B ON A.id = orders.customer_id ORDER BY A.name ASC;
-- Question 6
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders) ORDER BY name ASC;
-- Question 7
SELECT A.cityName, count(B.cityID) AS Users FROM customers B INNER JOIN city A ON A.id = B.cityID GROUP BY A.cityName;
-- QUESTION 8
SELECT A.cityName, count(B.cityID) AS Users FROM customers B NATURAL JOIN city A ON A.id = B.cityID GROUP BY A.cityName;
-- Question 9
SELECT cityName, count(cityID) AS Users FROM customers IN(SELECT cityName FROM city) GROUP BY cityName;
-- Question 10
SELECT A.product_id FROM products A INNER JOIN orders B ON A.product_id = B.product_id MINUS SELECT A.product_id FROM products A INNER JOIN orders B ON A.product_id = B.product_id WHERE B.order_date >= Convert(datetime, '23-10-2008') ORDER BY A.product_id DESC;
-- Question 11
SEELCT id FROM customers WHERE where city LIKE "Arizona" INTERSECT SELECT customer_id from orders where order_date < Convert(datetime, '27-10-2008');
-- Question 12
SELECT product_id FROM products IN (SELECT product_id FROM orders WHERE id = products.id AND order_date < Convert(datetime, '23-10-2008'));
-- Question 13
SELECT id FROM customers UNION SELECT customer_id FROM orders WHERE order_date < Convert(datetime, '23-10-2008') ORDER BY customer_id DESC;
-- QUestion 14
SELECT id, name, quantity FROM products WHERE quantity > 8;
-- Question 15
SELECT A.id, A.name, A.quantity FROM orders A INNER JOIN customers B ON A.quantity > 4 AND A.customer_id = B.id AND B.city LIKE "Utah";