# Write Solutions to Questions Regarding SQL Assignment Solution

July 09, 2024
Dr. Andrew
Python
Dr. Andrew Taylor, a renowned figure in the realm of Computer Science, earned his PhD from McGill University in Montreal, Canada. With 7 years of experience, he has tackled over 500 Python assignments, leveraging his extensive knowledge and skills to deliver outstanding results.
Key Topics
• Instructions
• Objective
• Requirements and Specifications
Tip of the day
News

## 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"; ```

## Similar Samples

Explore our curated samples at ProgrammingHomeworkHelp.com, offering solutions across diverse programming languages and topics. From Java and Python to SQL and beyond, each sample showcases our commitment to excellence in solving programming assignments. Dive into our samples to see how we can assist you in mastering programming concepts and achieving academic success.