Aggregation Part of the Exercise . Select the maximum price of any item ordered
ID: 3906010 • Letter: A
Question
Aggregation Part of the Exercise
. Select the maximum price of any item ordered in the items_ordered table. Hint: Select the maximum price only.
. Select the average price of all of the items ordered that were purchased in the month of Dec.
. What is the total number of rows in the items_ordered table?
. For all of the tents that were ordered in the items_ordered table, what is the price of the lowest tent? Hint: Your query should return the price only.
Group By Exercises
. How many people are in each unique state in the customers table? Select the state and display the number of people in each. Hint: count is used to count rows in a column, sum works on numeric data only.
. From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table. Hint: The items will need to be broken up into separate groups.
. How many orders did each customer make? Use the items_ordered table. Select the customerid, number of orders they made, and the sum of their orders.
Having Clause Exercise
. How many people are in each unique state in the customers table that have more than one person in the state? Select the state and display the number of how many people are in each if it's greater than 1.
. From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table. Only display the results if the maximum price for one of the items is greater than 190.00.
. How many orders did each customer make? Use the items_ordered table. Select the customerid, number of orders they made, and the sum of their orders if they purchased more than 1 item.
Ordered By Exercises
. Select the lastname, firstname, and city for all customers in the customers table. Display the results in Ascending Order based on the lastname.
. Same thing as exercise #1, but display the results in Descending order.
. Select the item and price for all of the items in the items_ordered table that the price is greater than 10.00. Display the results in Ascending order based on the price.
IN an BETWEEN Exercises
Select the date, item, and price from the items_ordered table for all of the rows that have a price value ranging from 10.00 to 80.00.
Select the first name, city, and state from the customers table for all of the rows where the state value is either: Arizona, Washington, Oklahoma, Colorado, or Hawaii.
Submit your Queries in a Texfile
Explanation / Answer
Answer is as follows :
Aggregation Part of the Exercise
a) Select the maximum price of any item ordered in the items_ordered table. Hint: Select the maximum price only.
SELECT MAX(Price) FROM item_ordered ;
b) Select the average price of all of the items ordered that were purchased in the month of Dec.
SELECT AVG(Price) FROM items_ordered WHERE order_date LIKE '%Dec%
c) What is the total number of rows in the items_ordered table
SELECT COUNT(*) FROM items_ordered ;
d) For all of the tents that were ordered in the items_ordered table, what is the price of the lowest tent
SELECT MIN(Price) FROM items_ordered WHERE item = 'Tent' ;
Group By Exercises
a) How many people are in each unique state in the customers table? Select the state and display the number of people in each. Hint: count is used to count rows in a column, sum works on numeric data only.
SELECT state, COUNT(state) FROM customers GROUP BY state;
b) From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table. Hint: The items will need to be broken up into separate groups.
SELECT item, MAX(Price), MIN(Price) FROM items_ordered GROUP BY item;
c) How many orders did each customer make? Use the items_ordered table. Select the customerid, number of orders they made, and the sum of their orders.
SELECT customerid, COUNTcustomerid), SUM(price) FROM items_ordered GROUP BY customerid;
Having Clause Exercise
a) How many people are in each unique state in the customers table that have more than one person in the state? Select the state and display the number of how many people are in each if it's greater than 1.
SELECT state, COUNT(state) FROM customers GROUP BY state HAVING COUNT(state) > 1;
b) From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table. Only display the results if the maximum price for one of the items is greater than 190.00.
SELECT item, MAX(price), MIN(price) FROM items_ordered GROUP BY item HAVING MAX(Price) > 190.00 ;
c) How many orders did each customer make? Use the items_ordered table. Select the customerid, number of orders they made, and the sum of their orders if they purchased more than 1 item
SELECT customerid, COUNT(customerid), SUM(price) FROM items_ordered GROUP BY customerid HAVING COUNT(customerid) > 1 ;
Ordered By Exercises
a) Select the lastname, firstname, and city for all customers in the customers table. Display the results in Ascending Order based on the lastname.
SELECT lastname, firstname, city FROM customers ORDER BY lastname;
b) Same thing as exercise #1, but display the results in Descending order.
SELECT lastname, firstname, city FROM customers ORDER BY lastname DESC;
c) Select the item and price for all of the items in the items_ordered table that the price is greater than 10.00. Display the results in Ascending order based on the price.
SELECT item, price FROM items_ordered WHERE price > 10.00 ORDER BY price ASC;
IN an BETWEEN Exercises
a) Select the date, item, and price from the items_ordered table for all of the rows that have a price value ranging from 10.00 to 80.00.
SELECT order_date, item, price FROM items_ordered WHERE price BETWEEN 10.00 AND 80.00
b) Select the first name, city, and state from the customers table for all of the rows where the state value is either: Arizona, Washington, Oklahoma, Colorado, or Hawaii.
SELECT firstname, city, state FROM customers WHERE state IN ('Arizona', 'Washington', 'Oklahoma', 'Colorado', 'Hawaii') ;
if there is any query please ask in comments....