Continue working on your Order database on the following queries using SQL state

Question

Continue working on your Order database on the following queries using SQL statements. Copy and past 1) query question, 2) SQL statement and 3) SQL output to a word file, and print it out.

1. List all the desks in the company that cost between $400 and $700 inclusive.

2. List customer names by their state, city and names in all in the ascending order.

3. Retrieve the names of customers who are in Texas and California.

4. Display the total cost for each order that has been placed and rename the output columns.

5. Retrieve the average, highest, and lowest price of each product finish.

6. Display customer names who have made 2 orders.

7. Display the order IDs and the number of products placed for each order.

8. Create a view for customer in Texas and New York.

Example for three output: Query, SQL and output Display the SKU description for the buyer “Pete Hansan”. SELECT SKU_DESCRIPTION FROM SKU_DATA WHERE Buyer = 'Pete Hansen';

Explanation / Answer

Answer)

Table names and attributes are missing and should have been provided. Using table name as in the questions.

1. List all the desks in the company that cost between $400 and $700 inclusive.
Assuming here that company is a table and product is an attribute which has desk.

select * COMPANY where product='desk' and cost between 400 AND 700;
This outputs all the entries in the table which has a desk as a product in the mentioned price range.

2. List customer names by their state, city and names in all in the ascending order.

SELECT CustomerName FROM Customers ORDER BY State, City, CustomerName ASC;

This outputs all customer names ordered by in ascending order their state, city and names.

3. Retrieve the names of customers who are in Texas and California.

SELECT CustomerName FROM Customers where State in ('Texas','California');

This outputs all customers who are in Texas or California.

4. Display the total cost for each order that has been placed and rename the output columns.
Assuming that Order table will be consisting of the orders. A separate table will have to contain the OrderDetails, let this table be as OrderDetails where all the price and cost of different products in the order are stored.

SELECT OrderID, SUM(cost) as TOTALCOST
FROM OrderDetails group by OrderID;

This prints out that for each Order we are printing the TOTAL COST that have been placed.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.