/* HW4 */
use dbstu4;

/* problem 1
select CHAR_TRIP, CHAR_DATE, AC_NUMBER, CHAR_DESTINATION
from dbo.CHARTER;
*/

/* problem 2
select CHAR_DATE, AC_NUMBER, CHAR_DESTINATION,
CHAR_DISTANCE, CHAR_HOURS_FLOWN
from dbo.CHARTER
where AC_NUMBER='2778V';
*/

/* problem 3
drop view AC2778V;
create view AC2778V as
select CHAR_DATE, AC_NUMBER, CHAR_DESTINATION,
CHAR_DISTANCE, CHAR_HOURS_FLOWN
from dbo.CHARTER
where AC_NUMBER='2778V';
*/

/* problem 4
select distinct ch.CHAR_DATE, ch.AC_NUMBER, ch.CHAR_DESTINATION,
		c.CUS_LNAME, c.CUS_AREACODE, c.CUS_PHONE
from AC2778V ac
inner join dbo.CHARTER ch on ch.AC_NUMBER = ac.AC_NUMBER
inner join dbo.AC_CUSTOMER c on c.CUS_CODE = ch.CUS_CODE
*/

select * from dbo.CHARTER;

/* problem 5
select ch.CHAR_DATE, ch.CHAR_DESTINATION, ch.AC_NUMBER,
		m.MOD_NAME, m.MOD_CHG_MILE
from dbo.CHARTER ch
inner join dbo.AIRCRAFT a on a.AC_NUMBER = ch.AC_NUMBER
inner join dbo.MODEL m on m.MOD_CODE = a.MOD_CODE
*/

/* problem 6
select ch.CHAR_DATE, ch.CHAR_DESTINATION, ch.AC_NUMBER,
		m.MOD_NAME, m.MOD_CHG_MILE, c.CUS_LNAME
from dbo.CHARTER ch
inner join dbo.AIRCRAFT a on a.AC_NUMBER = ch.AC_NUMBER
inner join dbo.MODEL m on m.MOD_CODE = a.MOD_CODE
inner join dbo.AC_CUSTOMER c on c.CUS_CODE = ch.CUS_CODE
where ch.CHAR_DATE > '2008-02-09 00:00:00.000';
*/

/* problem 7
select ch.CHAR_DATE, ch.CHAR_DESTINATION, ch.AC_NUMBER,
		m.MOD_CHG_MILE, ch.CHAR_DISTANCE,
		e.EMP_NUM, cr.CREW_JOB, e.EMP_LNAME
from dbo.CHARTER ch
inner join dbo.AIRCRAFT a on a.AC_NUMBER = ch.AC_NUMBER
inner join dbo.MODEL m on m.MOD_CODE = a.MOD_CODE
inner join dbo.AC_CUSTOMER c on c.CUS_CODE = ch.CUS_CODE
inner join dbo.CREW cr on cr.CHAR_TRIP = ch.CHAR_TRIP
inner join dbo.AC_EMPLOYEE e on e.EMP_NUM = cr.EMP_NUM
where ch.CHAR_DATE > '2008-02-09 00:00:00.000';
*/
/* problem 7 explanation:
more rows because AC_NUM 4278Y has two crew members
*/

/* problem 8
select ch.CHAR_DATE, ch.AC_NUMBER,
		ch.CHAR_HOURS_FLOWN as HOURS,
		ch.CHAR_FUEL_GALLONS as GALLONS,
		ch.CHAR_FUEL_GALLONS / ch.CHAR_HOURS_FLOWN as [galperhr]
from dbo.CHARTER ch
*/

/* problem 9
select ch.CHAR_DATE, c.CUS_LNAME, ch.CHAR_DISTANCE,
		m.MOD_CHG_MILE,
		ch.CHAR_DISTANCE * m.MOD_CHG_MILE as [Mileage Charge]
from dbo.CHARTER ch
inner join dbo.AIRCRAFT a on a.AC_NUMBER = ch.AC_NUMBER
inner join dbo.MODEL m on m.MOD_CODE = a.MOD_CODE
inner join dbo.AC_CUSTOMER c on c.CUS_CODE = ch.CUS_CODE
where ch.CHAR_DATE between '2008-02-09 00:00:00.000'
and '2008-12-31 23:59:59.999';
*/

/* problem 10
select ch.CHAR_DATE, c.CUS_LNAME,
		ch.CHAR_DISTANCE * m.MOD_CHG_MILE as [Mileage Charge],
		ch.CHAR_HOURS_WAIT * 50 as [Wait Charge],
		(ch.CHAR_DISTANCE * m.MOD_CHG_MILE)
		+ (ch.CHAR_HOURS_WAIT * 50) as [Total$]
from dbo.CHARTER ch
inner join dbo.AIRCRAFT a on a.AC_NUMBER = ch.AC_NUMBER
inner join dbo.MODEL m on m.MOD_CODE = a.MOD_CODE
inner join dbo.AC_CUSTOMER c on c.CUS_CODE = ch.CUS_CODE
order by reverse(ch.CHAR_DATE);
*/

/* problem 11
select CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_BALANCE
from dbo.AC_CUSTOMER
where CUS_BALANCE!=0;
*/

/* problem 12
select avg(CUS_BALANCE) as AVG_BALANCE,
		min(CUS_BALANCE) as MIN_BALANCE,
		max(CUS_BALANCE) as MAX_BALANCE,
		sum(CUS_BALANCE) as TOTAL_UNPAID_BALANCE
from dbo.AC_CUSTOMER
*/

/* problem 13
select AC_NUMBER, count(AC_NUMBER) as NUM_TRIPS,
		sum(CHAR_DISTANCE) as TOTAL_DIST,
		avg(CHAR_DISTANCE) as AVG_DIST,
		sum(CHAR_HOURS_FLOWN + CHAR_HOURS_WAIT) as TOTAL_HRS,
		avg(CHAR_HOURS_FLOWN + CHAR_HOURS_WAIT) as AVG_HRS
from dbo.CHARTER
group by AC_NUMBER;
*/

/* still redoing #11 and above */