Write Solutions to Questions Regarding SQL Assignment Solution

Instructions

Objective

Write solutions of questions regarding SQL language.

Requirements and Specifications

SQL Requirements

To compete the SQL assignment statement below, include a comment with the item number. For example:

--1.

--2.

1. List the id, name, and price for all products with price greater than the average product price.
2. For each product, list its id and total quantity ordered. Products are listed in ascending order of product_id.
3. For each product, list its id and total quantity ordered. Products are listed in ascending order of total quantity ordered.
4. For each product, list its id, name and total quantity ordered. Products are listed in ascending order of product_id.
5. 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.
6. 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.
7. 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.
8. Implement the query from #7 using NATURAL JOIN.
9. Implement the query from #7 using IN along with a subquery.
10. 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.
11. 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.
12. 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";