#Written by Jen Chen
#Copyrighted by Jen Chen
Lecture: Aggregate functions and subqueries.

Database in used:   Student.mdb
Tables: Imported tables: Orders and Shipping.


/* Task: TO show the number of shipping from each state. */

select ShipStateOrProvince,  count(ShipSTateOrProvince) AS [Most Shipped State]
from
   Orders
group by ShipStateOrProvince;


=============================================================
/* Task: To display the state's name and sum of each state's shipping then arrange the result
set in descending order.
	Here we use the result of a query as a table to query from.
*/

select  ShipStateOrProvince, MAX(MostShippedState) AS [MostShipped]
from
(
   select ShipSTateOrProvince, count(ShipSTateOrProvince) AS [MostShippedState]
	 from
       Orders O
   group by ShipStateOrProvince
)
GROUP BY ShipStateOrProvince
ORDER BY MAX(MostShippedState) DESC;

=============================================================
/* Select the state with the most shipping.
	- Here we use the result of a query as table to query from, and to use the result of
	another query as a query condition in the WHERE-clause.
*/
select *
from
  (
     select  ShipStateOrProvince, MAX(MostShippedState) AS [MostShipped]
 	from
          (
	     select ShipSTateOrProvince, count(ShipSTateOrProvince) AS [MostShippedState]
		from
   		   Orders O
		   group by ShipStateOrProvince
	  )
	GROUP BY ShipStateOrProvince
	ORDER BY MAX(MostShippedState) DESC
   )
where
   MostShipped = (
		   select  MAX(MostShippedState) AS [MostShipped]
		   from
		     (
		       select ShipSTateOrProvince,
			count(ShipSTateOrProvince) AS [MostShippedState]
		       from
   			  Orders O
			  group by ShipStateOrProvince
                  ))

=============================================================
/* Task: To find out the minimal value of the shipping from each state. */

select MIN(MostShipped)
from
(
	select  ShipStateOrProvince, MAX(MostShippedState) AS [MostShipped]
	    from
	(
	select ShipSTateOrProvince, count(ShipSTateOrProvince) AS [MostShippedState]
		from
   		   Orders O
		group by ShipStateOrProvince
	)
GROUP BY ShipStateOrProvince
ORDER BY MAX(MostShippedState) DESC
)

=============================================================
/* Task: TO find all states where the shipping value is minimal. */

select *
from
  (
     select  ShipStateOrProvince, MIN(MostShippedState) AS [MostShipped]
 from
(
select ShipSTateOrProvince, count(ShipSTateOrProvince) AS [MostShippedState]
from
   Orders O
group by ShipStateOrProvince
)
GROUP BY ShipStateOrProvince
ORDER BY MAX(MostShippedState) DESC
)
where MostShipped = (
select  MIN(MostShippedState) AS [MostShipped]
from
(
select ShipSTateOrProvince, count(ShipSTateOrProvince) AS [MostShippedState]
from
   Orders O
group by ShipStateOrProvince
))

============================================================

select ShipLastName, ShipFirstName, count(ShipLastName), count(ShipFirstName)
from
Orders
GROUP by ShipLastName, ShipFirstName
Order by ShipLastName, SHipFirstName

=============================================================