SQL

--SQL Ansi ISO

Use VincentVinyl;
Select * from Album
Select albumtitle, albumstudion from album
--first generation binary machine language
--2nd assembler
--3rd generation python, c++, C#, Java, C, Fortran, Cobal procedural
--4th generation what you to do not how

Select * from Person
order by personlastname desc;

Select * from employee;

Select * from Album where AlbumID=3

Select * from Album where AlbumStudion='columbia'

Select * from purchase where purchasedate='12/20/2018'

Select purchaseID, year(purchasedate) AS "YEAR"
From purchase

Select * from Saledetail where SaleDetailDiscount > 0

--joins
Select * from Employee

Select EmployeeID, [PersonLastName],[PersonFirstName]
From Employee
Inner Join Person
on employee.PersonID=person.PersonID

Select * from inventory

Select InventoryID, purchasedetail.purchasedetailid, 
Albumtitle, conditionName, purchaseprice,InventorySalePrice,
Inventorysaleprice - purchaseprice AS "Difference"
From Inventory
inner join condition
on Inventory.ConditionID=Condition.ConditionID 
inner join purchaseDetail
on purchaseDetail.PurchaseDetailID=inventory.PurchaseDetailID
inner join Album
on Album.AlbumID=PurchaseDetail.AlbumID

--insert update delete

Insert into album(AlbumTitle, AlbumYear, AlbumStudion)
Values('American Idiot',2004, '80080')

Select * from Album

Insert into purchase(PurchaseDate, PersonID)
values(GetDate(), 2)
Select * from purchase

Insert into PurchaseDetail(purchaseID, AlbumID, PurchasePrice)
Values(4, 6, 10.00)
select * from purchaseDetail

--updates are dangerous

Select * from Person



Insert into inventory(PurchaseDetailID, ConditionID, InventorySalePrice)
Values(8, 1, 20.00)

Select * from Inventory

Update person
Set PersonFirstName='Debby',
personemail='debbybrown@gmail.com'
Where personid=2

begin tran

Update Person
Set personlastname='Smith'

rollback tran

Delete from Person 
Where personid=1