SQL

Use TutorManagement

Select StudentfirstName, Studentlastname
From Student

Select *
From Student

Select Distinct Tutorkey from Session

Select 5 * (3 + 4) /(2 -3)

Select StudentGender,StudentAge, StudentAge * 2 As [Double Age]
From Student
Order by StudentGender, StudentAge DESC

Select StudentLastname, StudentFirstName
From Student
Where StudentGender='M'

Select StudentLastName,StudentFirstName,StudentAge
From Student
Where StudentAge <= 23

Select StudentLastName,StudentFirstName,StudentAge
From Student
Where StudentLastName like 'M%'

Select * From Session

Select *
from Session
Where SessionDateKey not between '11/1/2009' and '11/30/2009'

--scalar functions
Select * from Session
where MONTH(SessionDateKey) = 11

Select MONTH(SessionDateKey) From Session
Select Year(SessionDateKey) From Session
Select DAY(SessionDateKey) From Session
Select GETDATE()

Select DATEDIFF(yy, '3/3/2011', '9/11/2010')

--scalar functions

Select COUNT(Distinct StudentKey) as students from Session

Select AVG(StudentAge) From Student

Select SUM(StudentAge) From Student

Select MAX(StudentAge) From Student

Select MIN(StudentAge) From Student

Select * from Student

Begin tran
Update Student
Set StudentFirstName='Ray'
Where StudentKey='980001008'


Insert into Student(StudentKey, StudentLastName,
StudentFirstName, StudentPhone, StudentEmail,
StudentGender, StudentAge,
StudentCitizen, StudentWorkforceRetraining,
EthnicityKey)
Values('980002002', 'Smith', 'John', null, null, null, null, null, null,null)

Delete from Student
Where StudentKey='980002002'

Begin tran

Delete from Session

Select * from Session
Rollback tran

Commit tran

Create view vw_TutorsStudents
As
Select SessionDateKey, TutorLastName, CourseKey, StudentLastName
From [Session]
Inner Join Tutor
On [Session].TutorKey=Tutor.TutorKey
Inner join Student
On Student.StudentKey=[Session].StudentKey

Select * from vw_tutorsstudents

Select [name] from sys.views
Select [name] from sys.Tables