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