-- John Aromando -- SQL Views --

use ogcPubLev2

--create view of one table
Create View MediumPriceTitles
as select partnum, bktitle, slprice
from titles
where slprice between 20 and 40


select top 5 * from MediumPriceTitles
order by slprice desc

--remove
drop view MediumPriceTitles

--select Top N based on all tables
create view Top50_Orders
as 
select top 50 
	c.*, 
	s.ordnum, sldate, qty, 
	t.*,
	r.lname, fname, commrate,
	qty*slprice as OrderTotal 
FROM Customers as c 
		JOIN Sales as s ON s.custnum = c.custnum 
		JOIN Titles as t ON t.partnum = s.partnum 
		JOIN Slspers as r ON r.repid = c.repid
order by OrderTotal desc --'order by' is not possible in views unless 'top' clause is used

select * from Top50_Orders order by Summary desc

--select Bottom N based on all tables
create view Bot50_Orders
as 
select top 50 
	c.*, 
	s.ordnum, sldate, qty, 
	t.*,
	r.lname, fname, commrate,
	qty*slprice as OrderTotal 
FROM Customers as c 
		JOIN Sales as s ON s.custnum = c.custnum 
		JOIN Titles as t ON t.partnum = s.partnum 
		JOIN Slspers as r ON r.repid = c.repid
order by OrderTotal asc 

select * from Bot50_Orders order by OrderTotal desc

sp_help top50_Orders --does not show criteria

/*
Create Base view of main tables (Customers, Sales, Titles, Slspers)
	Show all fields but (no dupelicate fields)
	Show a total value per order calculation
	Show all records from all tables referenced in the view
Create a Grand Total View
Create a Detail Summary View
Create Select qry to show percent of total per order
*/
Create View ALLTablesBase
as SELECT
	c.*,
	s.ordnum, sldate, qty,
	t.*,
	r.lname, fname, commrate,
	qty*slprice as Summary
FROM Customers as c
	JOIN Sales as s ON s.custnum = c.custnum
	JOIN Titles as t ON t.partnum = s.partnum
	JOIN Slspers as r ON r.repid = c.repid

drop view ALLTablesBase

select * from ALLTablesBase

EXEC sp_rename 'ALLTablesBase.Summary', 'OrderTotal', 'column'

Create View GrandTotalOrders as
select SUM(OrderTotal) as GrandTotal
from ALLTablesBase

drop view GrandTotalOrders

select * from GrandTotalOrders

Create View CustomerSummaries as
select CustName, SUM(Ordertotal) as CustomerTotals
from AllTablesBase
group by CustName

drop view CustomerSummaries

select * from CustomerSummaries

select
	CustName
	CustomerTotals,
	GrandTotal,
	CustomerTotals/GrandTotal as '% Of Total'
From
	CustomerSummaries as c
	cross join GrandTotalOrders
Where
	CustomerTotals is not null
Order By
	'% of Total' desc

--prevent alteration of table properties with schema binding
Create View CustomerNames_View
with schemabinding
as select Custname
from dbo.Customers

drop view CustomerNames_View

alter table customers
alter column custname varchar (30) not null -- can't do because of schemabinding

--insert records and update values with views
insert  MediumPriceTitles values
('40256', 'How to Play Violoin (Intermediate)', 35),
('40257', 'How to Play Violin (Advanced)', 39)

--verify base table
select * from titles

--verify view
select*
from MediumPriceTitles 
where partnum in ('40256', '40257')

--update records with views
update MediumPriceTitles
set slprice = 40
where partnum ='40256'

--verify
select *
from MediumPriceTitles
where partnum = '40256'

--verify base table
select* from titles
order by partnum

--delete records with views
delete MediumPriceTitles
where partnum = '40321'

--verify
select* from MediumPriceTitles
order by partnum