1. The company owner needs to know how many customers are from each state. Make a list of the customers by providing the state and the number of customers.

select state, COUNT(*)
from customer
group by state

2. Modify the above query to sort the answer by count.

select state, COUNT(*) as 'Name'
from customer
group by state
order by Name

select state, COUNT(*)
from customer
group by state
order by 2

3. Display the total number of catalogs of each manufacturer.

select manu_code, COUNT(*)
from catalog
group by manu_code
order by 2

4. Find the total shipping charge of every customer. Display the customer number and the amount. The report should display the highest amount first.

select customer_num, sum(ship_charge)
from orders
group by customer_num
order by 2 desc

5. List down the details of the customers who have placed more than 1 order.

select customer_num, COUNT(order_num)
from orders
group by customer_num

select customer_num, COUNT(order_num)
from orders
group by customer_num
having COUNT(order_num)>1

6. Modify the query of the question 3 to display the manufacturers who are having more than 1 catalog. The list should be sorted according to the manufacturer code.

select manu_code, COUNT(*)
from catalog
group by manu_code
having COUNT(*)>1

7. List down the total ship weights per customer order. The list should contain the order number, customer number and the total weight and sorted according to the customer number.

select customer_num, order_num, SUM(ship_weight)
from orders
group by customer_num
order by customer_num

^^ aggregate error

select customer_num, order_num, SUM(ship_weight)
from orders
group by customer_num, order_num
order by customer_num

select customer_num,  SUM(ship_weight)
from orders
group by customer_num
order by customer_num

^^ Correct one

8. Find how many customers are there in each state with ‘Sport’ word included in the company name. Display the state and the number of customers.

select state, COUNT(*)
from customer
where company like '%sport%'
group by state

select state, COUNT(*)
from customer
where company like '%sport%'
group by state
having COUNT(*)> 5

Having only works after grouping

9. Find the total number of calls of every customer.

select COUNT(*)
from cust_calls

10. Display the manufacturer details of the manufacturer who is manufactured highest quantity of items.

select manu_code,MAX(quantity)
from items
group by manu_code
order by 2 desc

11. Display the average lead time of each manufacturer.

select AVG(lead_time)
from manufact

12. Find the customer who spent more than Rs. 5000 for shipping charges.

select customer_num
from orders
where ship_charge > 5000.00

13. List down the order numbers of the orders which contain less than 200kg ship weight.

select *
from orders
where ship_weight > 200.00