sYsfVfLg

1use master
2
3drop database if exists UniversityDb
4go
5create database UniversityDb
6go
7
8use UniversityDb
9
10CREATE TABLE Students (
11StudentID INT NOT NULL IDENTITY PRIMARY KEY,
12StudentName NVARCHAR(50) NULL,
13StudentDOB DATE NULL
14)
15
16create table Teachers(
17TeacherID Int not null identity primary key,
18TeacherName NVARCHAR(50) null,
19)
20
21
22CREATE TABLE Grades (
23GradeId int not null identity primary key,
24
25Grade int null ,
26IfPass bit null,
27GradeDate date null,
28StudentID INT NULL FOREIGN KEY REFERENCES Students(StudentID),
29TeacherID INT NULL FOREIGN KEY REFERENCES Teachers(TeacherID)
30)
31
32
33
34insert into Students values ('John','1985-01-15')
35insert into Students values ('Mike','1990-05-20')
36insert into Students values ('Peter','1995-10-30')
37
38insert into Teachers values ('Mr Johnson')
39insert into Teachers values ('Mrs Poppins')
40insert into Teachers values ('Mr Anderson')
41
42insert into Grades values (3,0,'2019-09-19',3,2)
43insert into Grades values (4,1,'2019-09-09',1,3)
44insert into Grades values (5,1,'2019-09-29',2,1)
45
46
47
48select * from Students
49select * from Teachers
50select * from Grades
51
52-- Joining 3 tables together
53select * from Students
54inner join Grades on Students.StudentID = Grades.StudentID
55inner join Teachers on Teachers.TeacherID = Grades.TeacherID
56
57--Comparing date of the grade with todays date and setting if statement for another table column
58select *, DATEDIFF(d,GradeDate,GETDATE()) as 'DaysAgo',
59Case
60When DATEDIFF(d,GradeDate,GETDATE()) < 10 then 'yes'
61else 'no'
62end
63as 'IsRepeatable' from Grades
64
65-- adding 7 days to the date
66select *, DATEADD(d,7,GradeDate) as 'NextGradeDate'
67from Grades
68
69insert into Students values ('Valentin','2000-05-05')
70
71select * from Students
72
73update Students
74Set StudentName='Rafael', StudentDOB='1999-05-05'
75WHERE StudentID = 4;
76
77select * from Students
78
79delete from Students where StudentID=4
80
81select * from Students