First SQL

Use Community_Assist

Select * from Person

Select Personfirstname, PersonlastName, PersonEmail
From Person
Order by PersonLastName;

Select Personfirstname, PersonlastName, PersonEmail
From Person
Order by PersonLastName desc;

Select * From Employee;
Select * from EmployeePosition;

--two table join
Select PersonLastName, PersonFirstName, 
EmployeeHireDate, EmployeeAnnualSalary
From Person
Inner Join Employee
On Person.PersonKey=Employee.PersonKey


Select * from Position
--four table join
Select PersonLastName, PersonFirstName, 
EmployeeHireDate, EmployeeAnnualSalary,
PositionName
From Person
Inner Join Employee
On Person.PersonKey=Employee.PersonKey
inner join EmployeePosition
on EmployeePosition.EmployeeKey=Employee.EmployeeKey
inner join Position
on Position.PositionKey=EmployeePosition.PositionKey

--Insert 
Insert into person(PersonLastName, PersonFirstName, 
PersonEmail, PersonEntryDate)
Values('Rose','Axel','arose@gmail.com', GetDate())

Insert into Contact( ContactNumber, ContactTypeKey, 
PersonKey)
Values('2065551209',1, 130),
('2065551210',2, 130)

Select * from Contact where personkey=130

Begin tran
Update Person 
Set PersonLastName='Smith'

Select * from Person
Rollback tran

Begin tran

Update Person
Set PersonFirstName='Jason'
Where PersonKey=1

Commit tran

--Delete
Delete from Person
where PersonKey=130

Delete from Contact
Where personkey=130