IMG-LOGO
×

Giáo Trình Môn Học

Khoá học lập trình SQL

Kiến Thức Core SQL Server

[Video] Hướng dẫn cách xác định kiểu dữ liệu, primary key, foreign key trong SQL - My SQL - SQL Server [Video] Hướng dẫn thao tác nhiểu bảng - select - multi table - join, inner join trong SQL Server [Video] Hướng dẫn phân tích CSDL - phân tích bảng, column trong bản - Học lập trình SQL, SQL Server [Video] Hướng dẫn tìm hiểu create, alter , insert table và bản chất primary key, foreign key - SQL Server [Video] Hướng dẫn tìm hiểu select, and, in, like, not in,group by .. having, order by asc, desc - SQL Server [Video] Quản lý sinh viên (create, alter, update, insert, select) - Lập trình SQL Server

View SQL Server

[Video] Hướng dẫn tìm hiểu view trong SQL Server Bài Giảng: Hướng dẫn tìm hiểu view + proc trong SQL - Lập trình SQL Server

Store & Procedure

[Video] Hướng dẫn tìm hiểu proc procedure trong SQL Server - Lập trình SQL Server, MySQL Bài Giảng: Xây dự hệ thống đưa đón học sinh bằng xe bus của Trường Học - Lập trình SQL Server [Video] Xây dựng CSDL quản lý điểm danh aptech - Lập trình SQL Server/MySQL

Trigger

[Video] Học Trigger qua ví dụ - quản lý nhà trên phố - Lập trình SQL Server [Video] Hướng dẫn học trigger qua bài quản lý điểm danh Aptech - Lập trình SQL Server [Video] Bài tập - Quản lý nhân khẩu - Lập trình SQL Server - MySQL




Trang Chủ SQL Server/MySQL Bài Giảng: Hệ thống quản lý sinh viên - Lập trình SQL Server

Bài Giảng: Hệ thống quản lý sinh viên - Lập trình SQL Server

by GokiSoft.com - 17:39 30/10/2021 2,005 Lượt Xem

Tạo database & bảng (tables)


-- 1) Database
---- Tao database
create database StudentManagementSystem

---- Kich hoat database
use StudentManagementSystem

-- 2) Tables
---- Table 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(50),
	SessionCount int
)

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



-- 3) Index Creation and Table Alternation
create nonclustered index NCI_Student_StudentName on Student (StudentName)

alter table Result
alter column Mark float



-- 4) 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 (StudentId, SubjectId)

---- 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)

---- CHECK
alter table Subject
add constraint CK_Subject_SessionCount check (SessionCount >= 0)


-- 5) Insert Data
insert into Class(ClassId, ClassCode)
values
(1 ,'C1106KV'),
(2 ,'C1108GV'),
(3 ,'C1108IV'),
(4 ,'C1108HV'),
(5 ,'C1109GV')



insert into Student(StudentId, StudentName, BirthDate, ClassId)
values
(1 ,'Pham Tuan Anh', '1993-08-05', 1),
(2 ,'Phan Van Huy', '1992-06-10', 1),
(3 ,'Nguyen Hoang Minh', '1992-09-07', 2),
(4 ,'Tran Tuan Tu', '1993-10-10', 2),
(5 ,'Do Anh Tai', '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)


-- 6) Query
---- Search students whose BirthDate is between from ‘1992-10-10’ and ‘1993-10-10’. Display info
------ of StudentId, StudentName, DateOfBirth.
select StudentId, StudentName, BirthDate 'DateOfBirth'
from Student
where BirthDate between '1992-10-10' and '1993-10-10'



---- Count students in each class with information of ClassId, ClassCode, TotalStudent
select Class.ClassId, Class.ClassCode, count(Student.StudentId) 'TotalStudent'
from Class left join Student on Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode


---- Sum up mark of all subjects for each student, then display only students whose sum of mark is
------ greater than 10. Display info of: StudentId, StudentName, TotalMark
select Student.StudentId, Student.StudentName, sum(Result.Mark) 'TotalMark'
from Student left join Result on Student.StudentId = Result.StudentId
group by Student.StudentId, Student.StudentName
having sum(Result.Mark) > 10



-- 7) Views
---- Create a view named view_StudentSubjectMark which should show result of each student by
------ each subject, with information of: StudentId, StudentName, SubjectName, Mark.
create view view_StudentSubjectMark
as
select top(3) Student.StudentId, Student.StudentName, Subject.SubjectName, Result.Mark
from Student left join Result on Student.StudentId = Result.StudentId
	left join Subject on Result.SubjectId = Subject.SubjectId
order by Result.Mark desc

select * from view_StudentSubjectMark




-- 8) Proc
---- Create a procedure named up_IncreaseMark, with input parameters:
create proc up_IncreaseMark
	@subjectId int
as
begin
	update Result set Mark = Mark + 1 where SubjectId = @subjectId
end

select * from Result
exec up_IncreaseMark 2
select * from Result







Bình luận



Phản Hồi Bởi:

(1) TRẦN VĂN ĐIỆP [Teacher]

Chia sẻ từ lớp học

TRẦN VĂN ĐIỆP [Teacher]

Ngày viết: 20:06 13/04/2021



-- 1) Database
---- Tao database
create database StudentManagementSystem

---- Kich hoat database
use StudentManagementSystem

-- 2) Tables
---- Table 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(50),
	SessionCount int
)

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

-- 3) Index Creation and Table Alternation
create nonclustered index NCI_Student_StudentName on Student (StudentName)

alter table Result
alter column Mark float

-- 4) 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 (StudentId, SubjectId)

---- 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)

---- CHECK
alter table Subject
add constraint CK_Subject_SessionCount check (SessionCount >= 0)

-- 5) Insert Data
insert into Class(ClassId, ClassCode)
values
(1 ,'C1106KV'),
(2 ,'C1108GV'),
(3 ,'C1108IV'),
(4 ,'C1108HV'),
(5 ,'C1109GV')
select * from Class

insert into Student(StudentId, StudentName, BirthDate, ClassId)
values
(1 ,'Pham Tuan Anh', '1993-08-05', 1),
(2 ,'Phan Van Huy', '1992-06-10', 1),
(3 ,'Nguyen Hoang Minh', '1992-09-07', 2),
(4 ,'Tran Tuan Tu', '1993-10-10', 2),
(5 ,'Do Anh Tai', '1992-06-06', 3)
select * from Student

insert into Subject(SubjectId, SubjectName, SessionCount)
values
(1 ,'C Programming', 22),
(2 ,'Web Design', 18),
(3 ,'Database Management', 23)
select * from Subject

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)
select * from Result

-- 6) Query
---- Search students whose BirthDate is between from ‘1992-10-10’ and ‘1993-10-10’. Display info
------ of StudentId, StudentName, DateOfBirth.
select StudentId, StudentName, BirthDate 'DateOfBirth'
from Student
where BirthDate between '1992-10-10' and '1993-10-10'

---- Count students in each class with information of ClassId, ClassCode, TotalStudent
select Class.ClassId, Class.ClassCode, count(Student.StudentId) 'TotalStudent'
from Class left join Student on Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode

---- Sum up mark of all subjects for each student, then display only students whose sum of mark is
------ greater than 10. Display info of: StudentId, StudentName, TotalMark
select Student.StudentId, Student.StudentName, sum(Result.Mark) 'TotalMark'
from Student left join Result on Student.StudentId = Result.StudentId
group by Student.StudentId, Student.StudentName
having sum(Result.Mark) > 10

-- 7) Views
---- Create a view named view_StudentSubjectMark which should show result of each student by
------ each subject, with information of: StudentId, StudentName, SubjectName, Mark.
create view view_StudentSubjectMark
as
select top(3) Student.StudentId, Student.StudentName, Subject.SubjectName, Result.Mark
from Student left join Result on Student.StudentId = Result.StudentId
	left join Subject on Result.SubjectId = Subject.SubjectId
order by Result.Mark desc

select * from view_StudentSubjectMark

-- 8) Proc
---- Create a procedure named up_IncreaseMark, with input parameters:
create proc up_IncreaseMark
	@subjectId int
as
begin
	update Result set Mark = Mark + 1 where SubjectId = @subjectId
end

select * from Result
exec up_IncreaseMark 2
select * from Result


Đã sao chép!!!