SQL Part Two: Joins, Insert, Update, Delete

use TutorManagement

Select * From TutorCourse

--here is a simple inner join
--joins are used to "join" two or more tables
--Here the tutor and tutorCourse tables
--to find the names of all the tutors who
--teach Web110
Select Tutor.TutorKey, TutorFirstName, TutorLastName, CourseKey
From Tutor
inner join TutorCourse
on Tutor.TutorKey=TutorCourse.TutorKey
Where CourseKey = 'Web110'

--same query but with the tables aliased.
--it just saves time
Select t.TutorKey, TutorFirstName, TutorLastName, CourseKey
From Tutor t
inner join TutorCourse tc
on t.TutorKey=tc.TutorKey
Where CourseKey = 'Web110'

--an old way to do a join. Shorter but more dangerous
--especially as you start joining more tables
Select t.TutorKey, TutorFirstName, TutorLastName, CourseKey
From Tutor t, TutorCourse tc
Where t.TutorKey=tc.TutorKey
And CourseKey='Web110'

--creates a cross join
--a cross join is where each record in the first table
--is matched to every record in the second table
--this can happen on purpose or by accident
--if you forget to add a realtionshio
Select t.TutorKey, TutorFirstName, TutorLastName, CourseKey
From Tutor t, TutorCourse tc
Where CourseKey='Web110'

Select * From Session

--A join with three tables
Select TutorLastName, SessionDateKey, SessionTimeKey,
Coursekey, StudentLastName
From Tutor
Inner Join [Session] 
on Tutor.TutorKey=[Session].TutorKey
Inner Join Student
on Student.StudentKey=[Session].StudentKey

Select * From tutor
order by TutorLastName

--Insert new records into the tutor table
--if you have an auto increment (identity) column, do not include it in the 
--fields for the insert
Insert into Tutor(TutorKey, TutorLastName, 
TutorFirstName, TutorPhone, 
TutorEmail, TutorHireDate, TutorStatus)
Values('980000000', 'Smith', 'John', '2065552356','js@gmail.com',GetDate(),'Active'),
('980000001', 'Jones', 'Jason', '2065552956','jj@gmail.com',GetDate(),'Active')


--this begins a manual transaction
--this allows for the possiblity of an undo
Begin tran

--updates the tutor table
--essential that this has a where clause
--unless you wish to update all the rows
--in the table
Update Tutor
Set TutorFirstName = 'Jonathan'
Where TutorKey='980000000' 

Select * from Tutor

rollback tran --cancels the transaction, your undo
Commit tran--commits the transaction to the database
--once commited there is no undo

--Deletes a row from the table Tutor
--this is best done in a transaction also
--you cannot delete a parent that has children
--unless you delete the children first
Delete from Tutor
Where TutorKey='980000000'