SQL (part one)

Use Tutormanagement

--basic sql statement

/*
this is a multiline
comment
*/

Select StudentLastname, Studentfirstname from Student;

Select * From Student

Select StudentLastName as [Last Name], StudentFirstName as [First Name]
From Student
order by StudentLastName desc, StudentFirstname

Select StudentLastName [Last Name], StudentFirstName [First Name]
From Student

Select StudentLastName LastName, StudentFirstName FirstName
From Student

Select distinct TutorKey from [Session]

Select 5 + 14 * (3 /2 )


Use CommunityAssist

Select * from Donation

Select donationamount, donationAmount * .8 as charity, donationamount * .2 as organization
from donation

Use TutorManagement

Select * from Student

Select StudentLastName, StudentEmail from Student
Where StudentLastName= 'Bradbury'

Select StudentLastName, StudentAge
from Student
Where StudentAge > 24

-->,< >=, <=, !=, =,<>

Select StudentLastName, StudentAge
from Student
Where StudentAge > 24
And StudentLastName like 'C%'

Select StudentLastName, StudentAge
from Student
Where StudentAge > 24
or StudentLastName like 'C%'

-- % for any number of characters
-- _ for a single character

-- MIC 1_2  MIC 122, MIC 102,

Select * From Student

Select StudentLastName, StudentEmail
From Student
Where studentEmail is not null


Select StudentLastName, StudentEmail
From Student
Where studentEmail is null

--scalar only operates on one row at a time

Select * from Session
Select distinct Year(SessionDateKey) as [Year] from Session
order by year(SessionDateKey) Desc

Select distinct Month(SessionDateKey) as [Month] from Session
order by Month(SessionDateKey) Desc

Select  Year(SessionDateKey) as [Year], Month(SessionDateKey) as [Month]
From Session
order by year(SessionDateKey) Desc,Month(SessionDateKey) Desc

Select DATEDIFF(dd, '11/18/2013', '12/11/13')
Select DateAdd(yy,  20,'11/18/13')

Select GetDate()

--aggregate operate across multilple rows

Select * From [Session]
Select count(StudentKey) as NumberOfStudents from Student
Select count (Distinct SessionDateKey) as [Sessions] from [Session]
Select count (SessionStatus) as [Sessions] from [Session]

Select Sum(StudentAge) / Count(StudentKey) From Student

Select avg(StudentAge) From Student

Select Max(StudentAge) From Student

Select Min(StudentAge) From Student

Select * From Student

Select Year(SessionDateKey) as [Year], Count(StudentKey) as Students 
from Session
Group by Year(SessionDatekey)

Select TutorKey, Count(StudentKey) as students
from Session
Group by tutorkey