Thiết kế hệ quản trị CSDL - Quản lý sinh viên (create, alter, update, insert, select) - Lập trình SQL Server

-- tao bang class 
create TABLE Class (
  ClassId int NOT null,
  ClassCode nvarchar(50)
)

CREATE TABLE Student (
  StudentId int NOT null,
  StudentName nvarchar(50),
  BirthDate datetime,
  ClassId int
)

create table Subject (
  SubjectId int NOT null,
  SubjectName nvarchar(100),
  SessionCount int
)

create table Result (
  SubjectId int not null,
  StudentId int NOT null,
  Mark int
)

-- Change data type Mark of Result
ALTER table Result
Alter column Mark float

-- Constraint 
---- Primary Key
alter table Class
ADD CONSTRAINT PK_Class PRIMARY KEY (classid)

ALTER TABLE Student
ADD CONSTRAINT PK_Student PRIMARY KEY (studentid)

ALTER TABLE Subject
ADD CONSTRAINT PK_Subject PRIMARY KEY (subjectid)

ALTER TABLE Result
ADD CONSTRAINT PK_Result PRIMARY KEY (subjectid, studentid)

---- Foreign Key
ALTER TABLE Student
add CONSTRAINT FK_Student_Class FOREIGN KEY (classid) references Class (classid)

ALTER TABLE Result
ADD CONSTRAINT FK_Result_Student FOREIGN KEY (studentid) references Student (studentid)

ALTER TABLE Result
ADD CONSTRAINT FK_Result_Subject FOREIGN KEY (subjectid) references Subject (subjectid)

ALTER TABLE Subject
ADD CONSTRAINT CK_Subject_SessionCount check (sessioncount > 0)

-- insert data
INSERT INTO Class (classid, classcode)
VALUES
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E')

INSERT INTO Student (studentid, studentname, birthdate, classid)
VALUES
(1, 'SA', '1993-08-05', 1),
(2, 'SB', '1992-06-10', 1),
(3, 'SC', '1992-09-07', 2),
(4, 'SD', '1993-10-10', 2),
(5, 'SE', '1992-06-06', 3)

INSERT INTO Subject(subjectid, subjectname, sessioncount)
VALUES
(1, 'C Programming', 22),
(2, 'Web Design', 18),
(3, 'Database Management', 23)

INSERT INTO Result(studentid, subjectid, mark)
VALUES
(1, 1, 8),
(1, 2, 7),
(2, 3, 5),
(3, 2, 6),
(4, 3, 9),
(5, 2, 8)

-- TEST
SELECT * FROM Class
SELECT * FROM Student
SELECT * FROM Subject
SELECT * FROM Result

-- Tim kiem theo ngay sinh
SELECT studentid as 'Ma Sinh Vien', studentname as 'Ten Sinh Vien', birthdate as 'Ngay Sinh'
from Student
Where birthdate BETWEEN '1992-10-10' and '1993-10-10'

-- Dem so sinh vien trong tung lop hoc
SELECT Class.classid 'Ma Lop Hoc', Class.classcode 'Ten Lop Hoc', COUNT(Student.StudentId) TotalStudent
FROM Class, Student
WHERE Class.ClassId = Student.ClassId
GROUP BY Class.classid, Class.classcode

-- Hien thi thong tin tong diem cua sinh vien
SELECT Student.StudentId 'Ma Sinh Vien', Student.StudentName 'Ten Sinh Vien', SUM(Result.Mark) 'Tong Diem'
FROM Student, Result
WHERE Student.StudentId = Result.studentid
GROUP By Student.StudentId, Student.StudentName
HAVING SUM(Result.Mark) > 10
ORDER by 'Tong Diem' DESC

-- Where
SELECT Class.classid 'Ma Lop Hoc', Class.classcode 'Ten Lop Hoc', COUNT(Student.StudentId) TotalStudent
FROM Class, Student
WHERE Class.ClassId = Student.ClassId
GROUP BY Class.classid, Class.classcode

-- CHuyen sang cach viet khac su dung inner join
---- Class: left table, Student: right table
SELECT Class.classid 'Ma Lop Hoc', Class.classcode 'Ten Lop Hoc', COUNT(Student.StudentId) TotalStudent
FROM Class inner join Student on Class.ClassId = Student.ClassId
GROUP BY Class.classid, Class.classcode

SELECT Class.classid 'Ma Lop Hoc', Class.classcode 'Ten Lop Hoc', COUNT(Student.StudentId) TotalStudent
FROM Class left join Student on Class.ClassId = Student.ClassId
GROUP BY Class.classid, Class.classcode

SELECT Class.classid 'Ma Lop Hoc', Class.classcode 'Ten Lop Hoc', COUNT(Student.StudentId) TotalStudent
FROM Class right join Student on Class.ClassId = Student.ClassId
GROUP BY Class.classid, Class.classcode

SELECT Class.classid 'Ma Lop Hoc', Class.classcode 'Ten Lop Hoc', COUNT(Student.StudentId) TotalStudent
FROM Student right join Class on Class.ClassId = Student.ClassId
GROUP BY Class.classid, Class.classcode

SELECT Class.classid 'Ma Lop Hoc', Class.classcode 'Ten Lop Hoc', COUNT(Student.StudentId) TotalStudent
FROM Class full outer join Student on Class.ClassId = Student.ClassId
GROUP BY Class.classid, Class.classcode