2020-12-02 09:49:05

--index 
create nonclustered index NCI_student_studentName on student(studentName)

--trigger

create trigger TG_result_insert on result 
for insert 
as
begin
	if (select mark from inserted ) < 0 
	begin 
		print N'Cannot insert mark less than zero.'
		rollback transaction
	end
end

insert result
values
(2, 1, -2)
---
create trigger TG_subject_update on subject
for update 
as
begin
	if update (subjectName)
	begin
		print N'You don’t update this column'
		rollback transaction
	end
end

update subject set subjectName = 'Toan' where subjectId=1

2020-12-02 09:35:32

create nonclustered index NCI_student_studentname on student (studentname)

create trigger TG_result_insert on result
for insert
as
begin
	if (select mark from inserted) <0 and (select mark from inserted) <> 0
	begin
		print N'Cannot insert mark less than zero'
		rollback transaction
	end
end

insert result
values
(1, 3, -2)

select * from result

create trigger TG_subject_update on subject
for update
as
begin
	if UPDATE(subjectname)
	begin
		print N'You don`t update this column'
		rollback transaction
	end
end

update subject set subjectname = 'colim1' where subjectid = 1

select * from subject

2020-12-02 09:02:51

create trigger TG_result_insert on result
for insert
as
begin
	if (select mark from inserted) < 0
	begin
		print N'Cannot insert mark < 0'
		rollback transaction
	end
end

insert result
values
(1,3,-2)

select * from result

create trigger TG_subject_update on subject
for update
as
begin
	if update(subjectname)
	begin
		print N'You do not update this column'
		rollback transaction
	end
end

update subject set subjectname = 'asdf' where subjectid = 1

select * from subject

2020-12-02 06:59:50

#lession5.sql

CREATE DATABASE StudentManagementSystem
USE StudentManagementSystem
CREATE TABLE class(
Classid INT NOT NULL primary key,
ClassCode NVARCHAR(50)
)
CREATE TABLE student(
Studentid INT NOT NULL primary key,
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 primary key,
Subjectid INT NOT NULL,
Mark INT
)
UPDATE result(
	SET Mark FLOAT
	WHERE Mark INT
)
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) 

select StudentId, StudentName, BirthDate
from Student
where BirthDate between '1992-10-10' and '1993-10-10'
select Class.ClassId, Class.ClassCode, COUNT(Student.StudentId) TotalStudent
from Class, Student
---5
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-19,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)
where Class.ClassId = Student.StudentId
group by Class.ClassId, Class.ClassCode
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
order by TotalMark desc

2020-12-02 06:27:11

create database StudentManagementSystem

use StudentManagementSystem

create table class (
  class_id int not null primary key,
  class_code nvarchar(100),
)
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','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','Đo Anh Tai','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-12-02 05:06:57

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 FK_Class primary key (ClassId)
alter table Student
	add constraint FK_Student primary key (StudentId)
alter table Subject
	add constraint FK_Subject primary key (SubjectId)
alter table Result
	add constraint FK_Result primary key (StudentId,SubjectId)

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 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','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(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 StudentId, StudentName, BirthDate
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, 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
order by TotalMark desc

2020-12-02 04:03:32

create DATABASE "QuanLySinhVien-gokisoft-buoi6"
use "QuanLySinhVien-gokisoft-buoi6"

create table Class(
ClassID int not null,
ClassCode nvarchar(50)
constraint PK_Class  primary key (ClassID)
)

create table Student(
StudentId int not null,
StudentName Nvarchar(50),
BirthDate datetime,
ClassID int 
constraint PK_Student primary key (StudentId)
constraint FK_Student_Class foreign key (ClassID) references Class(ClassID)
)

create table Subject(
SubjectId int not null,
SubjectName nvarchar(50),
SessionCount int,
constraint PK_Subject primary key (SubjectId)
)

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

alter table Result 
add constraint PK_Resault primary key (StudentId, SubjectId)

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


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)

select * from Class
select * from Student
select * from Subject
select * from Result

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',N'Phạm Tuấn Anh','1993-08-05',1),
('2',N'Phạm Văn Huy','1992-06-10',1),
('3',N'Nguyễn Hoàng Minh','1992-09-07',2),
('4',N'Trần Tuấn Tú','1993-10-10',2),
('5',N'Đỗ Anh Tài','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 Student.StudentId, Student.StudentName, Student.BirthDate from Student
where Student.BirthDate between '1992-10-10'and'1993-10-10'

select Class.ClassID, CLass.ClassCode, count(Student.StudentId) as N'Số học sinh trong lớp'
FROM Class inner join Student on Class.ClassID = Student.ClassID
group by Class.ClassID, CLass.ClassCode
 
select Result.StudentId, Student.StudentName, sum(Result.Mark) as 'Total Mark'
from Result inner join Student on Result.StudentId =  Student.StudentId 
group by Result.StudentId, Student.StudentName
having sum(Result.Mark) > 10

drop view view_StudentSubjectMark

--Hien thi tat ca diem cua moi sinh vien cach 1
create view view_StudentSubjectMark
AS
select Student.StudentId, Student.StudentName, Subject.SubjectName, Result.Mark 
FROM Result inner join Student on Student.StudentId = Result.StudentId 
inner join Subject on Subject.SubjectId = Result.SubjectId

--Hien thi tat ca diem cua moi sinh vien cach 2
create view view_StudentSubjectMark_test
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
select * from view_StudentSubjectMark_test

--Hien thi top 3 sinh vien theo diem giam dan tu view_StudentSubjectMark
select top(3) * from view_StudentSubjectMark
order by Mark desc

drop proc up_IncreaseMark

create PROC up_IncreaseMark
  @SubjectId int
  AS
  BEGIN
   update Result set Mark = Mark + 1 where SubjectId =  @SubjectId
  END

  EXEC up_IncreaseMark  2

  select * from Result

2020-12-01 10:06:25

create database StudentManagementSystem
use StudentManagementSystem

create table class (
classid int identity(1,1) primary key not null,
classcode nvarchar (50)
)
go
create table Student (
StudentId int identity(1,1) primary key not null,
StudentName nvarchar (50),
BirthDate datetime,
ClassId int
)
go
create table Subject (
SubjectId int identity(1,1) primary key not null,
SubjectName nvarchar (100),
SessionCount int 
)
go
create table Result (
StudentId int not null,
SubjectId int not null,
Mark int
constraint fk_sdid_sjid primary key (StudentId,SubjectId)
)
go

create index NCI_Student_StudentName
on Student (StudentName)

alter table Student
add constraint fk_cl_id foreign key (ClassId) references class(classId)

alter table Result
add constraint fk_sd_id foreign key (StudentId) references Student(StudentId)

alter table Result
add constraint fk_sj_id foreign key (SubjectId) references Subject(SubjectId)

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

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



insert into Student (StudentName,BirthDate,ClassId)
values
('Pham Tuan Anh',' 1993-08-05','1'),
('Phan Van Huy','1992-06-10','1'),
('Nguyen Hoang Minh','1992-09-07','2'),
('TranTuan Tu','1993-10-10','2'),
('Đo AnhTai','1992-06-06','3')


insert into Subject (SubjectName,SessionCount)
values
('C Programming','22'),
('Web Design','18'),
('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 as MaSinhVien ,StudentName as TenSinhVien,BirthDate as NgaySinh 
from Student 
where Student.BirthDate >='1992-10-10'and Student.BirthDate <='1993-10-10'

select class.classid as Malop,class.classcode as Tenlop, count(Student.ClassId) as sisolop
from class,Student
where class.classid = Student.ClassId
group by class.classid,class.classcode 

select Student.StudentId as Masinhvien,Student.StudentName as Tensinhvien, sum(Result.Mark) as TongĐiem
from Student left join Result on Student.StudentId = Result.StudentId
group by Student.StudentId,Student.StudentName
having sum(Result.Mark) >10

create view view_StudentSubjectMark
as
select Student.StudentId,Student.StudentName,Subject.SubjectName,Result.Mark
from Student,Subject,Result
where Student.StudentId = Subject.SubjectId and Subject.SubjectId = Result.SubjectId
select * from view_StudentSubjectMark

create proc up_IncreaseMark
 @SubjectId int 
as
begin
select Student.StudentId,Student.StudentName,Result.Mark,Subject.SubjectName,Subject.SubjectId
from Student,Result,Subject
where Student.StudentId = Result.StudentId and Result.SubjectId = Subject.SubjectId
update Result set Mark = Mark + 1
where SubjectId ='2'
end

exec up_IncreaseMark 2

2020-11-30 17:06:01

---database name StudentManagementSystem ---
create table class (
classID int NOT NULL,
ClassCode nvarchar (50)
)
go
insert into class(classID,ClassCode)
values
('1','C1106KV'),
('2','C1108GV'),
('3','C1108IV'),
('4','C1108HV'),
('5','C1109GV')
select *from class

create table student(
StudentID int NOT NULL,
StudentName nvarchar (50),
Birthdate datetime,
ClassID int
)
go
insert into student(StudentID,StudentName,Birthdate,ClassID)
values
('1','Phạm Tuấ nAnh','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','1993-06-06','3')

select *from student

create table subject(
SubjectID int NOT NULL,
SubjectName nvarchar (100),
sessionCount int
)
go
insert into subject(SubjectID,SubjectName,sessionCount)
values
('1','C Programming','22'),
('2','wed design','18'),
('3','Database management','23')

select *from subject
create table Result(
StudentID int NOT NULL,
SubjectID int NOT NULL,
Mark int
)
go
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

alter table student
add constraint fk_cl_id foreign key (StudentID) references class (classID)

alter table result 
add constraint fk_st_id foreign key (StudentID) references Student (StudentID)

alter table result 
add constraint fk_sb_id foreign key (SubjectID) references Subject (SubjectID ) 

--- query oparations ---

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)
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)
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_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 16:21:46

create database student mannage
use student mannage

create table class
(
classld int primary key, 
classCode nvarchar (50),
)

create table student(
studentld int primary key,
studntname nvarchar(50),
birthdate datetime,
classld int,)

create table subject(
subjectld int primary key,
subjectname nvarchar(100),
sessionCount int,
)

create table result(
studentld int ,
subjectld int ,
constraint fk_qwe primary key (studentld,subjectld),
mark int,)


alter table student
add constraint fk_student_class foreign key (classld) references class(classld)

alter table result
add constraint fk_result_student foreign key (studentld) references student(studentld)

alter table result
add constraint fk_result_sub foreign key (subjectld) references subject(subjectld)

alter table subject
add check (sessionCount > 0)

create proc proc_fake_class
as
begin
    --add du lieu va co the thay doi de hon 
	insert into class
	values
	('1','C1106KV'),
	('2','C1108GV'),
	('3','C1108IV'),
	('4','C1108HV'),
	('5','C1106GV')
    select * from class
end

exec proc_fake_class

create proc proc_fake_student
as
begin 
insert into student
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-05','3')
select *from Student
end

exec proc_fake_student

create proc proc_fake_sub
as
begin
insert into subject
values
('C ProGramming', 22),
('Web Design', 18),
('Database Management', 23)

select *from subject
end

exec proc_fake_sub

create proc proc_fake_result
as
begin
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
end

exec proc_fake_result

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

select class.classld, class.classcode, COUNT(student.studentld)
from class, student
where class.classld = student.studentld
group by class.classld, class.classcode

select student.studentld 'Ma Sinh Vien', student.studentname 'Ten Sinh Vien', SUM(result.mark)
from student left join result on student.studentid = result.studentld
group by student.studentld, student.studentname
having SUM(result.mark) > 10
order by totalmark desc

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

select * from view_studentsubjectmark

create proc up_mark
	@subject int 
as
begin
	select student.studentld, student.studentname, result.mark, result.subjectld
	from student, result
	where student.studentld = result.studentld
	update result set mark = mark + 1
	where result.studentld = @subject
end

exec up_mark