MSSQL queries from database creation to queries and stored procedures.

create database MYDATABASE
on (name=MYDATABASE,
filename='c:\CCTB111\MYDATABASE.mdf',
size=1mb,
maxsize=2mb)
log on (name=MYDATABASE_log,
filename='c:\CCTB111\MYDATABASE.ldf',
size=1mb,
maxsize=2mb)

--create the tables according to your blueprints

use MyClassExamples
create table Student --always start with your parent table
(StudentID char(10) NOT NULL primary key,
FName varchar(50) not null,
Address varchar(50) null,
Postal_code varchar(7),
sex char(1),
Birth_date datetime,
GraduationDate datetime)

Create table Grade
(StudentID char(10) not null,
CourseID char(6) not null,
Grade int,
Primary key (StudentID , CourseID))

Create table Course (
CourseID char(6) Not null primary key,
CourseName char(20) not null,
Hours int null)

--create contraint(Validation) at the time of table creation
drop table Student
create table Student --always start with your parent table
(StudentID char(10) NOT NULL primary key,
FName varchar(50) not null,
Address varchar(50) null,
Postal_code varchar(7),
sex char(1) constraint G check (sex='f' or sex ='m'), --every constraint must have a unique name
Birth_date datetime constraint bd check (Birth_date < GetDate()),
GraduationDate datetime constraint gd default Dateadd(year,2,getDate()))
drop table Student
--to add constraint after the fact you must use Alter table command
--must first drop constraint and then add constraint

Alter table Student
drop constraint G
Alter table Student
add constraint G check(sex IN ('f','m','u'))

--add constraint after the fact for the grade table
--adding referential integrity
alter table grade
add constraint grade_const
Foreign key (StudentID)
references Student(studentID)

alter table grade
add constraint course_constr
foreign key (CourseID)
references Course(CourseID)

create table student
(studentID char(10) not null constraint StudID primary key,
Full_name char(50) not null,
Address varchar(50) null,
postal_code varchar(7),
sex_code char(1) constraint Sexcode check(sex_code in ('F','M')),
Birth_date datetime,
GraduationDate datetime constraint Grad_date Default getdate() + 2)

Create table grade
(StudentID char(10) not null,
CourseID char(6) not null,
Mark int constraint Mrk Check(Mark between 0 and 100),
constraint Primary_key Primary Key(StudentID, CourseID))

Create table Course
(CourseID char(6) not null primary key,
CourseName char(20) not null,
Hours int null constraint h check (Hours > 0))

alter table student
drop constraint Grad_date

alter table student
add constraint Grad_Date default DateAdd(year,2,getDate()) for Graduationdate

alter table grade
add constraint CrsID
foreign key(CourseID)
references Course (CourseID)

Alter table grade
add constraint stud_id
foreign key (StudentID)
references Student (StudentID)

Alter table grade
drop constraint stud_id
Alter table grade
drop constraint CrsID
Alter table student
drop constraint Grad_date

--Exercise 6 pre-Examples

--to create index on the name of the student
create unique index FullName on Student(FName)

--to remove index as follows:
drop index student.fullName

--to populate database tables use the insert command
--two formats of INSERT
--1.

INSERT into student
(studentID, Fname)
values
('6','lily')

--Format 2. Must provide value for all columns

insert into student
values(2,'Neli','abc','','f','','')

--you can import values from another database
--Tables must be EXACTLY the same

insert into student
select * from cctb111..STUDENT

insert into Course
values
('CCTB11','introduction SQL',14)

insert into Student
(StudentID,Full_Name,Address,Sex_code)
Values
('1357890','Arash Riahi','45 2020 105 st Edmonton','M')

insert into grade
(StudentID, CourseID)
values
('1357890','CCTB11')

delete from grade where courseid='cctb11' and studentid='1357890'
delete from student where studentid = '1357890'
delete from course where courseid = 'cctb11'

insert into MYDATABASE..Course
Select * from CCTB111..Course

Alter table student
add b varchar(10)

alter table student
drop column b

insert into MYDATABASE..Student
Select * from CCTB111..Student

insert into MYDATABASE..Grade
Select * from CCTB111..Grade

-- find all the students who has provided their birthdate
--include their name, id, addresses, and birthdates
Select Studentid,name,Address, birth_date
from Student
where birth_date is not null

Select Studentid,name,Address
from Student
where name like 'a%'

--Find all students whose third character is "a" in their name

Select Studentid,name,Address
from Student
where name like '__a%'

--find all the students who are over 40 years of age

Select Studentid,name,Address, left(birth_date,11), age= year(getdate())-year(Birth_date)
from Student
where year(getdate())-year(Birth_date) < 40
order by age

--Using datediff() function, find age
Select Studentid,name,Address, birthdate = left(birth_date,11), age= DateDiff(month,birth_date,getdate())
from Student
where DateDiff(month,birth_date,getdate()) < 480
order by age

Select *
from Student
where Year(birth_date) > 1961
order by birth_date

Select studentid, name, address
from student
where name Like 'Sm%'

Select name, address, birth_date
from Student
where address like '%edmonton%'
order by birth_date

--find all the students who are in their 40s
--include id, name, birthdate and age

Select studentid, name, short_birth_date = LEFT(birth_date,12), age = datediff(year,birth_date,getdate())
from student
where datediff(year,birth_date,getdate()) between 40 and 49

--find honours students
--average of 80 or higher is considered honour student
--include id,name,courseid, and mark
--there are two formats for joining tables
--1. Standard format as follows:

select student.studentid, name, courseid,mark
from student
join grade
on student.studentid = grade.studentid
where mark >=80

--can also joing using shorthand version
--Note this version may or may not work in all platforms

select student.studentid, name, courseid, mark
from student, grade
where student.studentid = grade.studentid and mark > 80

--7e.
Select *
from student
where datediff(year,birth_date,getdate()) > 40 and sex_code = 'f'
order by full_name

--7f.

select *
from student
where ((Month(birth_date)= '2' and day(birth_date)>14) OR
(Month(birth_date)= '3' and day(birth_date)<23)) AND sex_code = 'm'

--7g

select courseName
from Course
where Hours > 64

--7h
select *
from Course
where Courseid like '%2__'

--7i
select studentid, courseid, Adjusted_Mark = Mark + 10
from grade
where courseid = 'BCS370'

--7j
Select * from grade
where studentid = '9312345'

--7k
Select courseName,Mark
from student, course, grade
where full_name = 'Peter Codd' and student.studentid = grade.studentid and
grade.courseid = course.courseid

--7l
Select full_name, birth_date,Average = avg(MArk)
from student, grade
where sex_code = 'm' and student.studentid = grade.studentid
group by full_name,birth_date
order by avg(mark)

--exercise 7 Explore
--1.

Select TOP 3 *
from grade
where courseid = 'BCS130'
order by Mark Desc

--Clas Examples
--provide a list of all students reagrdless of whether they are taking courses

select student.studentid, grade.studentid,courseid Mark
from student
left join grade
on student.studentid = grade.studentid

select student.studentid, grade.studentid, Avgerage_MArk = avg(Mark)
from student,grade
where student.studentid *= grade.studentid
group by Student.studentid,grade.studentid
order by avg(Mark) Desc

--top values
select top 25 percent StudentID,avg(MArk)
from grade
group by StudentID
order by StudentID desc

--Aggregate queries are the type of query that uses the following functions
--Avg(), Max(), min(), count(),sum(),....Group by
--find course average and highest MArk

Select grade.CourseID,CourseName, 'Class Average' = avg(MArk), 'Highest Mark' = Max(Mark),
'Lowest Mark' = Min(Mark), 'Total Students' = count(Mark)
from grade,Course
where grade.CourseID = course.courseid
group by grade.CourseID,CourseName
having count(*)>1
order by count(*) desc

--Show each students average
Select Full_name, 'Class Average' = avg(MArk), 'Highest Mark' = Max(Mark),
'Lowest Mark' = Min(Mark), 'Total Courses Taken' = count(Mark)
from grade,Student
where Student.studentid = grade.studentid
group by Student.Full_name
having count(*) > 1
order by count(*) Desc

--find all students whose mark is below the class average.
--step 1. find the class average
select avg(mark) from grade
--step 2. find all the students whose marks are below the class average
select *
from grade
where mark = (Select avg(mark) from grade)
order by Mark desc

--Excersise 8
--8a.
Select grade.courseID,CourseName,Mark
from grade,course
where grade.StudentID = (Select StudentID from Student where full_name='Joe Cool') and
grade.courseID = course.courseID

--8b.
Select full_name, Mark
from grade,Student
where grade.studentid = Student.studentid and
Courseid = 'BCS240'

--8c.
Select TOP 3 full_name, Mark
from grade,Student
where grade.studentid = Student.studentid and
Courseid = 'BCS370'

--8d.
Select full_name,birth_date, Mark
from student,grade
where sex_code LIKE 'f' and student.studentid = grade.studentid

--8e.
Select full_name,CourseName,Mark
from student,grade,course
where student.studentid = grade.studentid and grade.courseid = Course.courseid
order by full_name, CourseName

--8f.
Select CourseName,avg(MArk)
from grade,course
where course.Courseid = grade.courseid
group by CourseName
having count(*) < 4

--8g.
Select courseID
from grade, student
where grade.studentid = student.studentID and sex_code LIKE 'm'
group by CourseID

--8h.
Select Studentid,CourseID,Mark
from grade
where Mark > (Select avg(Mark) from grade)

--8i.
Select full_name,CourseID,Mark
from grade,student
where Mark = (Select Max(Mark) from grade) and
grade.studentid = student.studentid
group by CourseID,full_name,mark

--8 Explore exercises
--1.

Select CourseName, Avg(Mark),MAx(Mark), Hours
from grade,Course
where grade.Courseid = course.courseid and hours > 64
group by CourseNAme,Hours

--2.

Select CourseName, Total_Students = count(*), Highest_Mark = Max(Mark),
Lowest_Mark = Min(Mark), Average_Mark = avg(Mark), Hours
from grade,Course
where grade.courseid = course.courseid
group by CourseName, Hours,grade.courseid

--3.
Select * from Student
where postal_code IN
(Select postal_code from student
group by postal_code
having count(*) > 1)
order by postal_code

--OR alternatively

Select *
from student S1
where postal_code in
(select postal_code from student S2 where s1.studentid <> s2.studentid and
s1.postal_code = s2.postal_code)
order by postal_code

--******** VIEWS
create view MyAddresses
as
select Studentid, name, address, Birth_date
from Student
where address is not null and birth_date is not null

--can select from a view once created

Select * from MyAddresses

--Views can be embedded...
Create view Teenagers
as
select * from MyAddresses where DateDiff(YEar,birth_date,getdate()) between 13 and 19

Select * from Teenagers

--can insert into vuews - just like a table

insert into Teenagers values ('123','Neli Riahi','abc','01/01/1991')

Select * from Teenagers

--can update a view (It updates the orignal tables as well)
update Teenagers
set address = 'xyz'
where studentid = '123'

--can delete from a view
delete from teenagers
where studentid = '123'

--can alter views
--In this case let's encrypt the view

alter view Teenagers
with encryption
as select * from MyAddresses
where DateDiff(YEar,birth_date,getdate()) between 13 and 19

--can use information schema to verify that your view is encrpyted
select * from Information_schema.views

--Can undo encryption by repeating alter command with encryption piece
--MUST remember exact defintion
alter view Teenagers
as select * from MyAddresses
where DateDiff(YEar,birth_date,getdate()) between 13 and 19

--can drop views
drop view Teenagers

Create view Teenagers
as
select * from MyAddresses where DateDiff(YEar,birth_date,getdate()) between 13 and 19

--Exercise 9
--9a.
Create view adults
as
select * from Student where DateDiff(YEar,birth_date,getdate()) > 17

--9b
Create view youth
as
select * from Student where DateDiff(YEar,birth_date,getdate()) < 18

--9c
select * from adults
select * from youth

--9d
create view adults_in_T6j5j2
as
select * from adults where postal_code = 'T6J5J2'

--9e
alter view adults_in_T6j5j2
as
select * from adults where postal_code <> 'T6J5J2'

select * from adults_in_T6j5j2

--9f
alter view adults_in_T6j5j2
with encryption
as
select * from adults where postal_code <> 'T6J5J2'

select * from information_schema.views

--9g
alter view adults_in_T6j5j2
as
select * from adults where postal_code <> 'T6J5J2'

select * from information_schema.views

--9h

create view GraduatedStudent
as
select * from Student where GraduationDate < getdate()

select * from graduatedstudent

--9i
insert into graduatedstudent
values ('987987987','Chad Moore','111-11st','','m','07/07/1982','04/01/2004')

--9j
update graduatedstudent
set full_name = 'Jord'
where full_name = 'Chad Moore'

--9k
delete from graduatedstudent
where full_name = 'Jord'

--stored procedures
--create a stored procedure to display students who are over 40
create proc MySP
as
select Age = datediff(year,birth_date,getdate()),*
from Student
where datediff(year,birth_date,getdate()) > 40
order by birth_date

--run stored procedure

exec MySp

drop proc MySP

--can use input and output variables wth stored procedure

create proc MySp @names varchar(50)
as
select Age = year(getdate()) - Year(birth_date),*
from Student
where name like @names and datediff(year,birth_date,getdate()) > 40
order by birth_date

-- to call procedure to provide list of students over 40 and name contains "A"

exec MySp @names='%a%'

drop proc MySP

--can create Sp with input and output parameters
create proc GetAge @names varchar(50), @age INT output
as
select @age = year(getdate()) - year(birth_date)
from student
where name like @names
and birth_date is not null

--To test this procedure you myust declare an output variable
declare @age as int
exec GetAge 'pe%', @age output
select 'the Age is ', @Age

--Exercise 10
--10a

create proc grads
as
select *
from student
where Year(getdate()) - YEAR(graduationDate) > 9

--10b
exec grads

--10f

drop proc grads

--10g

create proc grads @name varchar(50)
as
select * from Student
where graduationdate =
(Select graduationdate from student where full_name like @name)

--10h

exec grads 'Cardinal%'

--10i

alter proc grads @name varchar(50)
as
select * from Student
where graduationdate is not null and full_name like '%'+ @name + '%'

--10j

exec grads 'ara'

--10k

alter proc grads @id varchar(10), @avgmark int output
as
Select @avgmark = avg(Mark)
from grade
where studentid = @id

--10l

declare @avgmark int
exec grads '2222222',@avgmark output
select 'average mark is ',@avgmark