Question

Deliverable 1. Simple SQL Statements

Caution: Read the instructions carefully! Each question is based on a single SQL statement, and the single SQL statement might contain sub-queries (additional SELECT statements) within the statement.

1. Provide a list all of the Customer ID, Customer Names, and States and sort the list in alphabetical order by Customer Name.

2. Provide a list of all of the Customer ID, Customer Names, and States, and sort the list by state with the Customer Names in alphabetical order within each state.

3. What is the full address of the customer named Kaneohe Homes? That includes the street, city, state, and zip code

4. Provide a list of the product name, product ID, and unit price for all of the products in the Product table, sorted in alphabetical order by product name

5. What is the standard price and standard price if increased by 15% for every product?

6. What is the average standard price for the products with a product finish of natural ash?

7. Provide a listing of all of the products that includes the difference between its standard price and the overall average standard price of all products.

8. What is the product name, product ID and price of most expensive product in the product table?

9. Which orders have been placed since October 20, 2005?

10. List the product name, standard price, and finish for all tables that cost more than $300 and all desks that cost more than $300.

11. Provide a listing of the unique product IDs for the products in the Order Line table.

12. Which states have more than one customer? List the states and the number of customers for those states

Answer 1

Provide a list all of the Customer ID, Customer Names, and States and sort the list in alphabetical order by Customer Name.

SELECT customerID, CustomerName, CustomerState
FROM customer_t
ORDER BY CustomerName;

---

Provide a list of all of the Customer ID, Customer Names, and States, and sort the list by state with the Customer Names in alphabetical order within each state.

SELECT customerID, CustomerName, CustomerState
FROM customer_t
ORDER BY CustomerState LIKE '%[a-z]%';

---

What is the full address of the customer named Kaneohe Homes? That includes the street, city, state, and zip code

SELECT CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode
FROM customer_t
WHERE CustomerName = 'Kaneohe Homes';

---

Provide a list of the product name, product ID, and unit price for all of the products in the Product table, sorted in alphabetical order by product name

SELECT ProductID, ProductStandardPrice, ProductDescription
FROM product_t
WHERE ProductDescription LIKE '%[a-z]%';

---

What is the standard price and standard price if increased by 15% for every product?

UPDATE product_t
SET ProductStandardPrice = ProductStandardPrice * 1.15;

---

What is the average standard price for the products with a product finish of natural ash?

SELECT AVG(ProductStandardPrice) as ProductStandardPrice
FROM Product_t
WHERE ProductFInish = 'Natural Ash';

---

Provide a listing of all of the products that includes the difference between its standard price and the overall average standard price of all products.

SELECT ProductDescription, ProductStandardPrice – (SELECT AVG(ProductStandarPrice) FROM product_t) AS Difference
FROM product_t;

---

What is the product name, product ID and price of most expensive product in the product table?

SELECT ProductDescription, ProductId, ProductStandardPrice
FROM product_t
WHERE ProductStandardPrice >=800;

---

Which orders have been placed since October 20, 2005?

SELECT * FROM order_t
WHERE OrderDate <= 2005-10-20;

---

List the product name, standard price, and finish for all tables that cost more than $300 and all desks that cost more than $300.

SELECT ProductDescription, ProductStandardPrice, ProductFinish
FROM product_t
WHERE ProductDescription
LIKE '%_T%' '_D%'
AND ProductStandardPrice > 300;

---

Provide a listing of the unique product IDs for the products in the Order Line table

SELECT DISTINCT ProductID
FROM order_t;

--

Solved all the questions except last question, really sorry for that

If you have any doubt, please mention, love to help

all the best

please upvote