2020-11-30 15:43:24

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

2020-11-30 14:11:19

create database StudentManagementSystem
use StudentManagementSystem 

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
)

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)

alter table subject 
add constraint ck_subject_sessioncount check (sessioncount > 0)

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)

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', '1993-08-05', 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)

select * from class
select * from student
select * from subject
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 'Ma lop', class.classCode 'Ten lop', count(student.studentId) 'Si so lop'
from class, student
where class.classId=student.studentId
group by class.classId, class.classCode

select student.studentId 'Ma sinh vien', 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

create view view_studentsubjcetmark 
as
select student.studentId, student.studentName, subject.subjectName, mark 
from student, subject, result
where student.studentId=subject.subjectId and subject.subjectId=result.subjectId

select * from view_studentsubjcetmark

create proc up_IncreaseMark 
	@subjectId int
as
begin
	select student.studentId, student.studentName, result.studentId, mark
	from student, result
	where student.studentId=result.studentId
	update result set mark=mark+1
	where result.studentId=@subjectId
end

exec up_IncreaseMark 2

2020-11-30 12:19:23

create database StudentManagementSystem

use StudentManagementSystem

create table class (
  class_id int not null primary key,
  class_code nvarchar(50),
)
go

create table student(
 student_id int not null  primary key,
 StudentName nvarchar(50),
 BirthDate datetime,
 class_id int references class(class_id)
)
go

create table subject(
 subject_id int not null primary key,
 subject_name nvarchar(100),
 sessionCount int ,
)
go

create table result(
 subject_id int not null references subject(subject_id),
 student_id int not null references student(student_id),
 mark int
 constraint abc primary key(student_id,subject_id)
)
go


alter table subject 
add check (sessionCount >0)

insert into class(class_id,class_code)
values
('1','C1106KV'),
('2','C1108GV'),
('3','C1108IV'),
('4','C1108HV'),
('5','C1109GV')

insert into student(student_id,StudentName,BirthDate,class_id)
values
('1','Phạm Tuấn Anh','1993-08-05 ','1'),
('2','Phan Văn Huy','1992-06-10  ','1'),
('3','Nguyễn Hoàng Minh','1992-09-07 ','2'),
('4','Trần Tuấn Tú','1993-10-10','2'),
('5','Đỗ Anh Tài','1992-06-06','3')


insert into subject(subject_id,subject_name,sessionCount)
values
('1','C Programming','22'),
('2',' Web Design','18'),
('3',' Database Management','23')

insert into result(student_id,subject_id,mark)
values
('1','1',8),
('1','2',7),
('2','3',5),
('3','2',6),
('4','3',9),
('5','2',8)


select student_id 'Ma Sinh Vien', StudentName 'Ten Sinh Vien', BirthDate 'Ngay Sinh'
from student
where BirthDate between '1992-10-10' and '1993-10-10'

select Class.class_id, Class.class_code, COUNT(Student.student_id) TotalStudent
from Class, Student
where Class.class_id = Student.student_id
group by Class.class_id, Class.class_code 

select Student.student_id 'Ma Sinh Vien', Student.StudentName 'Ten Sinh Vien', SUM(Result.Mark) TotalMark
from Student left join Result on Student.student_id = Result.student_id
group by Student.student_id, Student.StudentName
having SUM(Result.Mark) > 10
order by TotalMark desc

create view view_studentsubjectmark
AS
SELECT student.student_id, student.studentname, subject.subject_name, mark
FROM student, subject, result
WHERE student.student_id = result.student_id and subject.subject_id = result.subject_id

select * from view_studentsubjectmark

CREATE PROC up_increasemark
	@subjectid int output
AS
BEGIN
	select student.studentid, studentname, mark, result.subjectid
	from student, result
	where student.studentid=result.studentid 
	update result set mark = mark+1 
	where result.studentid=@subjectid
END

EXEC up_increasemark 2

2020-11-30 10:11:28

create database StudentDatabaseManagement
use StudentDatabaseManagement

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

insert into class
values
(1,'C1106KV'),
(2,'C1108GV'),
(3,'C1108IV'),
(4,'C1108HV'),
(5,'C1109GV')

select * from class

create table student(
	 studentid int primary key not null,
	 studentname nvarchar(50), 
	 birthday datetime,
	 classid int references class(classid)
)

insert into student(studentid,studentname,birthday,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 Tai Anh','1992/06/06',3)

select * from student

create table subject(
	subjectid int primary key not null,
	subjectname nvarchar(100),
	sessioncount int,
	check (sessioncount>0)
)

insert into subject
values
(1,'C Programming',22),
(2,'Web Design',18),
(3,'Database Management',23)

select * from subject

create table result(
	studentid int not null references student(studentid),
	subjectid int not null references subject(subjectid),
	mark int
)

insert into result
values
(1,1,8),
(1,2,7),
(2,3,5),
(3,2,6),
(4,3,9),
(5,2,8)

select * from result

select studentid as 'Ma Sinh Vien', studentname as 'Ten Sinh Vien', birthday as 'Ngay Sinh' from student
where birthday between '1992/10/10' and '1993/10/10'

SELECT class.classid as 'Ma Lop', class.classcode as 'Ten Lop', COUNT(student.studentname) as 'Si So Lop'
FROM class
left outer join student on student.classid = class.classid
GROUP BY class.classid, class.classcode

SELECT student.studentid as 'Ma Sinh Vien', studentname as 'Ten Sinh Vien', sum(result.mark) as 'Tong Diem'
FROM student, result
where student.studentid = result.studentid 
GROUP BY student.studentid, studentname
having sum(result.mark)>10

create view view_studentsubjectmark
AS
SELECT student.studentid, student.studentname, subject.subjectname, mark
FROM student, subject, result
WHERE student.studentid = result.studentid and subject.subjectid = result.subjectid

select * from view_studentsubjectmark

CREATE PROC up_increasemark
	@subjectid int output
AS
BEGIN
	select student.studentid, studentname, mark, result.subjectid
	from student, result
	where student.studentid=result.studentid 
	update result set mark = mark+1 
	where result.studentid=@subjectid
END

EXEC up_increasemark 2 

2020-11-30 09:26:27

--tạo cơ sở dữ liệu--
create database studentManagementSystem
go
use studentManagementSystem 
go

create table classs(
     classID int not null,
	 classCode nvarchar(50)
)
go

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,
   subjectId int not null,
   Mark float
)
go

alter table Result
alter colum Mark float
alter table class
add cóntraint 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, SubjectId)

alter table Student
add constraint FK_Student_Class foreign key (ClassId) references Class (SubjectId, SubjectId)

alter table Result
add constraint FK_Result_Student foreign key (StudentId) 

alter table Subject
add constraint CH_Subject_SessionCount check (SessionCount > 0)


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-08-05', 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)

select * from Class
select * from Student
select * from Result
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'

n
select Class.ClassId, Class.ClassCode, COUNT(Student.StudentId) TotalStudent
from Class, Student
where Class.ClassId = Student.ClassId
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