2020-12-02 09:10:07

create database StudentManagementSystem

use StudentManagementSystem

create table class (
	classid int primary key identity (1,1) not null,
	classcode nvarchar(50)
)

create table student (
	studentid int primary key identity (1,1) not null,
	studentname nvarchar(50),
	birthdate datetime,
	classid int
)

create table subject (
	subjectid int primary key identity (1,1) not null,
	subjectname nvarchar(100),
	sessioncount int
)

create table result (
	studentid int not null references student(studentid),
	subjectid int not null references subject(subjectid),
	mark int
	constraint pk_result primary key (studentid, subjectid)
)

alter table subject
add check (sessioncount > 0)

insert into class(classcode)
values
('C1106KV'),
('C1108GV'),
('C1108IV'),
('C1108HV'),
('C1109GV')

select * from class

insert into student(studentname, birthdate,classid)
values
('Pham Tuan Anh', '1993-08-05', 1),
('Phan Van Huy', '1992-06-10', 2),
('Nguyen Hoang Minh', '1992-09-07', 3),
('Tran Tuan Tu', '1993-10-10', 4),
('Do Anh Tai', '1992-06-06', 5)

select * from student

insert into subject(subjectname, sessioncount)
values
('C ProGramming', 22),
('Web Design', 18),
('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

select studentid 'Ma sinh vien', studentname 'Ten Sinh Vien', birthdate 'Ngay Sinh'
from student
where birthdate between '1992-10-10' and '1993-10-10'

select class.classid, class.classcode, COUNT(student.studentid) totalstudent
from class, student
where class.classid = student.studentid
group by class.classid, class.classcode

select student.studentid 'Ma Sinh Vien', student.studentname 'Ten Sinh Vien', 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
order by totalmark desc

create view view_studentsubjectmark
as
select student.studentid, student.studentname, subject.subjectname, result.mark
from student, subject, result
where student.studentid = result.studentid and subject.subjectid = result.subjectid

select * from view_studentsubjectmark

create proc up_increasemark
	@subject int output
as
begin
	select student.studentid, student.studentname, result.mark, result.subjectid
	from student, result
	where student.studentid = result.studentid
	update result set mark = mark + 1
	where result.studentid = @subject
end

exec up_increasemark 2