/* Dana J Clark
-- CS362-1 SQL Midterm Exam scripts
-- February 25, 2009 */

/*--Item #1 Done */
 /*-- The following SQL statement will create a schema*/
	Create schema CS362Mdjc;
	
	
/*--Item #2 Done. --*/
	/*The following SQL creates tables vehicleV1 and vehicleV2*/
	create table CS362Mdjc.vehiclev1 
	(theVID	Char   ( 20 ),
	THEMAKE 	CHAR ( 25 ) , 
	THEMODEL 	CHAR ( 25 ) , 
	THECOLOR 	CHAR ( 20 )  , 
	theBasePrice	Dec    ( 9,2 ) ,
	theDelivery	Date not null default current_date,
	CONSTRAINT CS362Mdjc.v1PK Primary Key (theVID) );

	create table CS362Mdjc.vehicleV2 
	(theVID	Char ( 20 ) ,
	THEOPTION 	CHAR ( 30 ) ,
	theOptPrice	DEC   ( 9,2 ) ,
	CONSTRAINT CS362Mdjc.v2PK Primary Key (theVid, THEOPTION)  );


/*--Item #3  Done. */
	/*-- inserts data, based on existing data in other (like) tables from flagler.testV1 and flagler.testV2*/

insert into CS362Mdjc.vehiclev1 (select * from flaglera.testv1);

insert into CS362Mdjc.vehiclev2 (select * from flaglera.testv2);

/*--Item #4	Add Foreign Key contstraint between the two tables.   Done.*/
 
 
	
 alter table CS362Mdjc.vehicleV2 
	ADD CONSTRAINT CS362Mdjc.v2FK1
	FOREIGN KEY (theVID)
	References CS362Mdjc.vehicleV1(theVID)
	ON DELETE RESTRICT
	ON UPDATE RESTRICT;

	
/*--Item #5 Done. */
/*	The first SQL statement will create a view that displays all Red Audi TT's, the following SQL statement queries
that view formatting it summarily*/

	create view cs362Mdjc.RedTTs as
		select * from cs362Mdjc.vehiclev1 where
			themake ='Audi' and
			themodel like  '%TT%' and 
			THECOLOR= 'Red';

			
		Select 'There are ' concat Count(*) concat 'red TTs' as  REDTTS from CS362Mdjc.RedTTs;
	

/*--Item #6  The following statement will attempt to violate the FK constraint created in Item #4 Done.*/

Delete from CS362Mdjc.vehicleV1 where 
		THEMAKE = 'Audi' and THEMODEL like '%TT%';


/*--Item #7- Done. */
/*the following sql statements add labels to tables (Additionally added column labels)*/

Label on table CS362Mdjc.vehicleV1 is 'List of Automobiles in stock';

Label on column CS362Mdjc.vehicleV1
	(theVID is 'Vehicle ID number',
	THEMAKE is 'The manufacturer of the vehicle',
	THEMODEL is 'The model name of the vehicle',
	THECOLOR is 'The color of a particular vehicle',
	theBasePrice is 'Base price of the vehicle without additional options',
	theDelivery is 'Model Year');
	
Label on  table CS362Mdjc.vehicleV2 is 'List of options available for particular vehicles';

Label on column CS362Mdjc.vehicleV2
	(theVID is 'Vehicle ID number, refers to VID of table vehicleV1',
	THEOPTION is 'Vehicle optional feature',
	theOptPrice is 'Price of optional feature');

/*--Item#8  Done. */
/*	The following SQL counts number of records in vehiclev1 and vehiclev2 UNIONed together*/

Create view CS362Mdjc.TableCounts as
Select 'Number of cars : ' concat (count(*))as "Table Counts" from CS362Mdjc.vehicleV1    
UNION
Select 'Number of options : ' concat (count(*))as "Table Counts" from CS362Mdjc.vehicleV2 ;

Select * from cs362Mdjc.TableCounts;


/*--Item#9 Done. */
/*write the SQL statement to add a new column called SOLD which will be a single 
alphanumeric character with values Y for Yes and N for No. This field should have a 
default value of ‘N’.  (5 point)*/

Alter table CS362Mdjc.vehicleV1 ADD column SOLD CHAR (1) not null default'N';

Select distinct SOLD as "Distinct SOLD values" from CS362Mdjc.vehicleV1;


/*--Item #10   Done. */
/*SQL statement to create a check constraint named collection.testV1_SoldMustBeYesNoCC */

Alter table CS362Mdjc.vehicleV1
	Add constraint  CS362Mdjc.SOLDmustBeYesOrNoCC check(SOLD in ('Y', 'N' ));

	/*test constraint by attempting to violate it*/
	Update CS362Mdjc.vehicleV1 
		SET SOLD = 'V'
		where THEMAKE = 'Volvo';
		/*effective constraint*/
		
/*--Item #11  Done. */		
/*The following SQL statement creates a view called CarCount, which shows the number of cars of each model 
year currently available in the inventory.   */

create view CS362Mdjc.CarCount as
Select distinct char(year(thedelivery)) as "Model Year", count(year(thedelivery)) as "Number of cars"
from cs362mdjc.vehiclev1
group by char(year(thedelivery));


/*--Item #12  Done*/
/*Use the UNION clause to combine carcount view with a single line total of all cars. */

Create view CS362Mdjc.CarCountTotals as
Select "Model Year", "Number of cars" from CS362Mdjc.CarCount 
UNION
Select char('Totals: ') as "Model Year", sum("Number of cars") as "Number of cars" 
from CS362Mdjc.CarCount;


/* --Item #13-- Done.*/
/*The following SQL statement creates a VIEW named BIGBUCKS. The result set will contain fields 
VID, color, make, model, summarized OPTIONS PRICE (for all options on that vehicle), base price and 
total price. Total price is created by adding the total options and base price.  This involves 
joining a summarized options temporary table to the vehicles and can be done in a single SQL statement 
with a correlated subquery. The second SQL statement, retrieves the results obtained from the view and 
order this result set by the total price in DESCENDING sequence, including only for vehicles with 
total price (options + base) greater than $40,000.  .*/

	Create View CS362Mdjc.Bigbucks as
		Select
			 v1.thevid as THEVID, 
			v1.thecolor as THECOLOR,
			v1.themake as THEMAKE, 
			v1.themodel as THEMODEL,
			tmp.OptionTotal as OPTPRICE,
			v1.thebaseprice as THEBASEPRICE,
			v1.thebaseprice+tmp.OptionTotal as TOTALPRICE
		FROM  CS362Mdjc.vehicleV1 v1,
		table 
		(select 
			v2.theVid as thevid, 
			sum(v2.theOptPrice) as OptionTotal
		from CS362Mdjc.vehicleV2 v2
		 group by  theVID) as tmp
		WHERE
		v1.thevid = tmp.thevid  ;


select * from cs362Mdjc.bigbucks where TOTALPRICE >40000  order by TOTALPRICE desc;
		
		
/*--Item #14--  Done.*/
/*The following compound SQL statement changes the Sold field of the vehicle with the highest total price to YES, 
indicating that it is sold (ie. SOLD = “Y”)*/


update CS362Mdjc.vehicleV1 v1
set v1.sold = 'Y'
where v1.thevid = (Select a.thevid from (Select * from cs362Mdjc.bigbucks order by TOTALPRICE desc fetch first 1 rows only) a) ;


/*--Item #15-- Done.*/
/*The following SQL creates a view that will display the relative record number (rrn) of the vehicle that was marked sold*/
create view CS362Mdjc.theRRN as
select 
	rrn(CS362Mdjc.vehicleV1) as RRN, 
	thevid,  
	themake,
	themodel,
	thebaseprice,
	thedelivery,
	sold from CS362Mdjc.vehicleV1 where sold ='Y';

select * from cs362Mdjc.theRRN;

/*--Item #16-- Done. */
/*The following sql statement creates a view over the bigbucks view that displays total value of all cars*/

Create view CS362Mdjc.inventory as
select sum(TOTALPRICE) as "Total$" from cs362Mdjc.bigbucks;

Select * from cs362Mdjc.inventory;

/*--Item #17--  Done.*/

/* The following SQL creates a view that shows a result set containing
Make, Total value of all cars of that make, the total value of all cars, and the percentage each make
represents relative to the total value of all cars*/

Create view cs362Mdjc.MakeInv as
Select
	 tmp.Make as Make,
	tmp.MakeTotal as MakeTotal,
	tmp.GrandTotal as GrandTotal,
	Decimal(MakeTotal*100.0000/GrandTotal, 7,4)  as Percent

from  table
(select
	bb.themake as Make,
	sum(bb.TOTALPRICE ) as MakeTotal ,
	avg(i."Total$") as GrandTotal
from
	 cs362Mdjc.bigbucks bb,
	cs362Mdjc.inventory i

group by bb.themake) as tmp;

select * from cs362Mdjc.MakeInv;

/*--Item # 18-- Done.*/

/*the following sql creates a view called MYDATES that selects the delivery date 
from vehiclev1and converts it to USA, JIS, ISO, and EUR date formats*/

Create view CS362Mdjc.MYDATES as
Select
	thedelivery, 
	char(thedelivery, USA) as USA,
	char(thedelivery, JIS) as JIS,
	char(thedelivery, ISO) as ISO,
	char(thedelivery, EUR) as EUR

from cs362Mdjc.vehicleV1;

Select * from cs362Mdjc.mydates;



/*--Item #19-- Done*/

/* The following sql changes the delivery date of all the cars.*/

update CS362Mdjc.VehicleV1
set thedelivery = 
( date(thedelivery +7 years - 7 months+ 7 days   )  ); 

/*Next a view called AVG is created that shows the average time (in days) a car has been on the lot*/

Create view cs362Mdjc.AVGDAYS as
	Select
		v1.themake as "Make",
		avg((days(date('2009-03-01') ) ) - (days(v1.thedelivery))) as "Average time in stock"
	From cs362Mdjc.vehicleV1 v1 group by v1.themake;
	

/*--Item #20 -- Done*/
/*The following SQL creates a view called MYSTAMP */
create view  cs362Mdjc.mystamp as

select 
	tmp.thedelivery, 
	tmp."Begin", 
	tmp."End", 
	 ( (timestamp(tmp."End") - timestamp(tmp."Begin")) )as MYDUR
 from  
(select 
v1.thedelivery,
timestamp ( char(v1.thedelivery)   concat ('-') concat(   char(current_time))  )
	as "Begin",

timestamp ( char(v1.thedelivery + 100 days)   concat ('-') concat(   char(current_time  +7 hours + 30 minutes))  )
	as "End" 
from cs362Mdjc.vehiclev1 v1  ) as tmp ;


select 
	mydur,
	year(mydur) as YR,
	month(mydur) as MO,
	day(mydur) as DAY,
	hour(mydur) as HR,
	minute(mydur) as MIN
from cs362Mdjc.mystamp;


/*--Item #21-- Done. */

/*--JoinBoth view: Used LEFT JOIN to cover the case that there might be a car with no options, 
although the select statement: select * from cs362Mdjc.vehiclev1  where thevid not in 
(select thevid from cs362Mdjc.vehiclev2); reveals that there are none in this example. */

/*had to select by name in order to rename columns- including one pair that,
due to the assignment, were named alike(not allowed in Create View)- most likely 
intentionally so that I'd run into this issue. */

Create view CS362Mdjc.JOINBOTH as

Select 
	thevid as carvid,
	themake as make, 
	themodel as model,
	thecolor as color, 
	thebaseprice as BP,
	thedelivery as deldt,
	sold as sold,
	optvid as optvid,
	opt as opt,
	optp as optp
from cs362Mdjc.vehicleV1 v1
 left join 
(select
	thevid as optvid,
	theoption as opt,
	theoptprice as optp
from cs362Mdjc.vehicleV2 ) as tmp
on v1.thevid=tmp.optvid;



select * from cs362Mdjc.joinboth;





/*--Item 22--  Done*/


/*Created a view called pivotHelp to consolidate the information I need to pivot- basically reused BigBucks but added MakeYear*/
/*
Need a table that has:

VID  (really need or not? keep JIC need to key into orig tables again)
Make
MakeYear
baseprice
totaloptions
grandtotal

*/

/*The combined SQL statements below create a pivot view that displays summary columns (dollar values and counts) for Make(s)
unioned with a totals row. */
create view cs362Mdjc.pivotHelp as
Select
	 v1.thevid as VID, 
	v1.thedelivery as MAKEYEAR,
	v1.thecolor as COLOR,
	v1.themake as MAKE, 
	v1.themodel as MODEL,
	tmp.OptionTotal as TotalALLOPTIONS,
	v1.thebaseprice  as BASEPRICE,
	v1.thebaseprice+tmp.OptionTotal as GRANDTOTAL
FROM  	
CS362Mdjc.vehicleV1 v1,
table (select 
		v2.theVid as thevid, 
		sum(v2.theOptPrice) as OptionTotal
from CS362Mdjc.vehicleV2 v2
 		group by  theVID) as tmp
WHERE
v1.thevid = tmp.thevid  ;

	/*use the view created above as a consolidated source from which to pivot*/
	
create view cs362Mdjc.VPivot as	
select 
	distinct x."Year" as "Make Year",
	count(x.audi) as "Total Cars" ,
	sum (x.audi) as "No of Audis" ,
	sum (x.volvo) as "No of Volvos",
	sum (x.porsche) as "No of Porsches",
	sum (x.other) as "No of Other",
	sum (x.audicashvalue) as "Audi$",
	sum (x.volvocashvalue) as "Volvo$",
	sum (x.porschecashvalue) as "Porsche$",
	sum (x.othercashvalue) as "Other$",
	sum(x.grandtotalcashvalue) as "Total$"

from

(select 
	v1.vid,	
	v1.make as "Make",
	char(year(v1.makeyear)) as "Year",
	case
		when v1.make = 'Audi'   then 1
		else 0
		end as audi,
					
	
	case
		when v1.make = 'Audi' then v1.grandtotal
		else 0
		end as audicashvalue,

	case
		when v1.make = 'Volvo' then 1
		else 0
		end as volvo,
		
	case 
		when v1.make = 'Volvo' then v1.grandtotal
		else 0
		end as volvocashvalue,
		
	case
		when v1.make = 'Porche' or v1.make = 'Porsche' then 1
		else 0
		end as porsche,
		
	case when v1.make = 'Porche' or v1.make='Porsche' then v1.grandtotal
		else 0
		end as porschecashvalue,
		
	case	when v1.make <> 'Porche' and v1.make <> 'Porsche' and
		v1.make <> 'Volvo' and v1.make <> 'Audi' then 1
		else 0
		end as other,
		
	case	when v1.make <> 'Porche' and v1.make <> 'Porsche' and
		v1.make <> 'Volvo' and v1.make <> 'Audi' then v1.grandtotal
		else 0
		end as othercashvalue
,

	v1.grandtotal as grandtotalcashvalue
	
from cs362Mdjc.pivotHelp v1) as x 
group by x."Year"


UNION

select 
	'Totals: ' as "Make Year",
	count(x1.audi) as "Total Cars" ,
	sum (x1.audi) as "No of Audis" ,
	sum (x1.volvo) as "No of Volvos",
	sum (x1.porsche) as "No of Porsches",
	sum (x1.other) as "No of Other",
	sum (x1.audicashvalue) as "Audi$",
	sum (x1.volvocashvalue) as "Volvo$",
	sum (x1.porschecashvalue) as "Porsche$",
	sum (x1.othercashvalue) as "Other$",
	sum(x1.grandtotalcashvalue) as "Total$"

from

(select 
	v1.vid,	
	v1.make as "Make",
	char(year(v1.makeyear)) as "Year",
	case
		when v1.make = 'Audi'   then 1
		else 0
		end as audi,
					
	
	case
		when v1.make = 'Audi' then v1.grandtotal
		else 0
		end as audicashvalue,

	case
		when v1.make = 'Volvo' then 1
		else 0
		end as volvo,
		
	case 
		when v1.make = 'Volvo' then v1.grandtotal
		else 0
		end as volvocashvalue,
		
	case
		when v1.make = 'Porche' or v1.make = 'Porsche' then 1
		else 0
		end as porsche,
		
	case when v1.make = 'Porche' or v1.make='Porsche' then v1.grandtotal
		else 0
		end as porschecashvalue,
		
	case	when v1.make <> 'Porche' and v1.make <> 'Porsche' and
		v1.make <> 'Volvo' and v1.make <> 'Audi' then 1
		else 0
		end as other,
		
	case	when v1.make <> 'Porche' and v1.make <> 'Porsche' and
		v1.make <> 'Volvo' and v1.make <> 'Audi' then v1.grandtotal
		else 0
		end as othercashvalue
,

	v1.grandtotal as grandtotalcashvalue
	
from cs362Mdjc.pivotHelp v1) as x1 ;

select * from cs362Mdjc.vpivot;