Quản lý điểm danh Aptech

2020-12-10 10:39:03

create database qldiemdanh
use qldiemdanh

----1. Bảng học viên -> Student
create table Student(
RollNo nvarchar (20) primary key,
Ten_hoc_vien nvarchar (100),
Gioi_tinh nvarchar (20),
Ngay_sinh date,
Que_quan nvarchar (100),
email nvarchar (100),
SDT nvarchar (20),
)
------2. Bảng giáo viên -> Teacher
create table Teacher (
Ma_giao_vien nvarchar(20)  primary key,
Ten_giao_vien nvarchar(100),
Gioi_tinh nvarchar (20),
Ngay_sinh date
)
-----3. Bảng môn học -> Subject
create table Subject_s (
Ma_mon_hoc nvarchar(20)  primary key,
Ten_mon_hoc nvarchar(100),
Tong_so_buoi int
)
------4. Bảng lớp học -> Class
create table Class (
Ma_lop_hoc nvarchar(20)  primary key,
Ten_lop_hoc nvarchar(100),
Ghi_chu text
)
------Thành viên lớp học -> ClassMember
create table ClassMember (
RollNo nvarchar(20) ,
ClassNo nvarchar(20),
JoinedDate date,
OutedDate date
)
----6. Phân công công viêc -> Schedule
create table Schedule(
id int identity(1,1)  primary key,
Ma_giao_vien nvarchar(20),
Ma_mon_hoc nvarchar(20),
Ma_lop_hoc nvarchar(20),
Ngay_bat_dau date,
Ngay_ket_thuc date
)
------7. Bảng điểm danh -> Attendance
create table Attendance(
id int identity(1,1)  primary key,
ScheduleId int,
RollNo nvarchar(20),
Ngay_diem_danh date,
Diem_danh_lan_1 NVARCHAR(50),
Diem_danh_lan_2 NVARCHAR(50),
Ghi_chu text
)
alter table ClassMember
add constraint _fk_ClassMember_ClassNo
foreign key (ClassNo)
references Class(Ma_lop_hoc)

alter table ClassMember
add constraint _fk_ClassMember_rollno
foreign key (RollNo)
references Student(RollNo)

alter table Schedule
add constraint _fk_Schedule_Ma_giao_vien
foreign key (Ma_giao_vien)
references Teacher(Ma_giao_vien)

alter table Schedule
add constraint _fk_Schedule_Subject
foreign key (Ma_mon_hoc)
references Subject_s(Ma_mon_hoc)

alter table Schedule
add constraint _fk_Schedule_Ma_lop_hoc
foreign key (Ma_lop_hoc)
references Class(Ma_lop_hoc)

alter table Attendance
add constraint _fk_ScheduleId
foreign key (ScheduleId)
references Schedule(id)


insert into Student(RollNo,Ten_hoc_vien,Gioi_tinh,Ngay_sinh,Que_quan,email,SDT)
values
('HS01','A','Nam','2002-09-18','Ha Noi','A@gmail.com','01234567899'),
('HS02','E','Nam','2002-09-01','Hai Duong','fdf@gmail.com','4594859847'),
('HS03','D','Nu','2002-01-28','Ha Nam','adfg@gmail.com','0120897688'),
('HS04','C','Nam','2002-07-10','Ha Noi','vc@gmail.com','58748588488'),
('HS05','B','Nam','2002-12-28','Tuyen Quang','jkj@gmail.com','01234564583')

insert into Teacher(Ma_giao_vien,Ten_giao_vien,Gioi_tinh,Ngay_sinh)
values
('GV01','ooo','Nu','1997-02-16'),
('GV02','ttt','Nu','1998-07-29'),
('GV03','hhh','Nam','1995-02-20'),
('GV04','yyy','Nu','1991-09-19'),
('GV06','opp','Nu','1997-01-06')

insert into Subject_s(Ma_mon_hoc,Ten_mon_hoc,Tong_so_buoi)
values
('MH01','Toan','40'),
('MH02','Van','40'),
('MH03','Tieng Anh','30'),
('MH04','Lich su','25'),
('MH05','Dia li','25')

insert into Class(Ma_lop_hoc,Ten_lop_hoc,Ghi_chu)
values 
('Cl01','Lop A1','Tang 1'),
('Cl02','Lop A2','Tang 1'),
('Cl03','Lop A3','Tang 2'),
('Cl04','Lop A4','Tang 2'),
('Cl05','Lop A5','Tang 3')

insert into ClassMember (RollNo,ClassNo,JoinedDate,OutedDate)
values
('HS01','Cl01','1-7-2018','1-7-2022'),
('HS02','Cl02','1-7-2018','1-7-2022'),
('HS03','Cl03','1-7-2018','1-7-2022'),
('HS04','Cl04','1-7-2018','1-7-2022'),
('HS05','Cl05','1-7-2018','1-7-2022')

insert into Schedule (Ma_giao_vien,Ma_mon_hoc,Ma_lop_hoc,Ngay_bat_dau,Ngay_ket_thuc)
values
('GV01','MH01','Cl01','2018-7-1','2018-7-30'),
('GV02','MH02','Cl02','2018-7-1','2018-7-30'),
('GV03','MH03','Cl03','2018-7-1','2018-7-30'),
('GV04','MH04','Cl04','2018-7-1','2018-7-30'),
('GV06','MH05','Cl05','2018-7-1','2018-7-30')

insert into Attendance(ScheduleId,RollNo,Ngay_diem_danh,Diem_danh_lan_1,Diem_danh_lan_2,Ghi_chu)
values
('1','HS01','2020-01-01','ok','ok','ok'),
('2','HS02','2020-01-01','ok','ok','ok'),
('3','HS03','2020-01-01','ok','ok','ok'),
('4','HS04','2020-01-01','ok','ok','ok'),
('5','HS05','2020-01-01','ok','ok','ok')

create proc xem_sh_the_lop
@ten_lop nvarchar(100) 
as
select Student.RollNo,Student.Ten_hoc_vien,Student.Gioi_tinh,Student.Que_quan,Student.email,Student.SDT,Class.Ma_lop_hoc,Class.Ten_lop_hoc
from ClassMember,Schedule,Attendance,Class,Subject_s,Teacher,Student
where ClassMember.ClassNo = Class.Ma_lop_hoc and Schedule.Ma_giao_vien = Teacher.Ma_giao_vien and Schedule.Ma_mon_hoc = Subject_s.Ma_mon_hoc
and Schedule .Ma_lop_hoc =Class.Ma_lop_hoc and Schedule .id = Attendance.ScheduleId and ClassMember.RollNo =Student.RollNo
and Class.Ten_lop_hoc = @ten_lop


exec xem_sh_the_lop 'Lop A3'
exec xem_sh_the_lop 'Lop A1'

select *from Attendance
select * from Schedule

create proc xem_diem_danh
@Ma_lop nvarchar(100),
@ma_mon_hoc nvarchar(100)
as
select Class.Ten_lop_hoc,Student.RollNo,Student.Ten_hoc_vien,Attendance.Ngay_diem_danh,Attendance.Diem_danh_lan_1,Attendance.Diem_danh_lan_2,Subject_s.Ten_mon_hoc
from ClassMember,Schedule,Attendance,Class,Subject_s,Teacher,Student
where ClassMember.ClassNo = Class.Ma_lop_hoc and Schedule.Ma_giao_vien = Teacher.Ma_giao_vien and Schedule.Ma_mon_hoc = Subject_s.Ma_mon_hoc
and Schedule .Ma_lop_hoc =Class.Ma_lop_hoc and Schedule .id = Attendance.ScheduleId and ClassMember.RollNo =Student.RollNo
and Class.Ma_lop_hoc = @Ma_lop and Subject_s.Ma_mon_hoc = @ma_mon_hoc

exec xem_diem_danh 'Cl03' , 'MH03'

create trigger xoa_hoc_vien_sd
on Student
instead of delete
as
begin
delete from Attendance where RollNo in ( select RollNo from deleted)
delete from ClassMember where RollNo in ( select RollNo from deleted)
delete from Student where RollNo in ( select RollNo from deleted)
end

delete student where RollNo = 'HS01'
select* from Student 

2020-12-10 10:07:31

create database Aptech_attendance_management
use Aptech_attendance_management

--- Bảng học viên -> Student ---
create table Student(
RollNo nvarchar (10) primary key ,
name_student nvarchar (100),
gender nvarchar (50),
Birthday date ,
address nvarchar (200),
email nvarchar (200),
phonenumber nvarchar(20)
)
go

insert into Student(RollNo,name_student,gender,Birthday,address,email,phonenumber)
values
('R001','ABC','Nam','2002-01-01','Ha Noi','abc@gmail.com','1212121212'),
('R002','BBC','Nu','2002-02-02','Thai Binh','bbc@gmail.com','2323232323'),
('R003','CAC','Nam','2002-03-03','Lai Chau','cbc@gmail.com','3434343434'),
('R004','DBC','Nu','2002-04-04','Bac Giang','dbc@gmail.com','4545454545'),
('R005','EBC','Nam','2002-05-05','Lao Cai ','ebc@gmail.com','5656565656')

---  Bảng giáo viên -> Teacher ---
create table teacher(
TeacherID nvarchar(100) primary key ,
teacher_name nvarchar(100),
gender nvarchar(50),
birthday date
)
go

insert into teacher(TeacherID,teacher_name,gender,birthday)
values
('1','Tran Van Diep','Nam','2002-06-06'),
('2','Tran Van Diep','nu','2002-07-07'),
('3','Tran Van Diep','Nam','2002-08-08'),
('4','Tran Van Diep','nu','2002-09-09'),
('5','Tran Van Diep','Nam','2002-10-10')

 --- Bảng môn học -> Subject ---
 create table Subject(
 SubjectID nvarchar(100) primary key ,
 Subject_name nvarchar(100),
 total int
 )
 go

 insert into Subject(SubjectID,Subject_name,total)
 values 
 ('T1','Toan','1'),
 ('V1','Van','2'),
 ('LS1','Lich Su','3'),
 ('VL1','Vat Li','4'),
 ('DL1','Dia Li','5')


 --- Bảng lớp học -> Class ---
 create table  Class(
 ClassID nvarchar(100) primary key ,
 class_name nvarchar(100),
 note nvarchar (200)
 )
 go
 insert into Class(ClassID,class_name,note)
 values
 ('1','T2008A','good class'),
 ('2','T2008A','good class'),
 ('3','T2008A','good class'),
 ('4','T2008A','good class'),
 ('5','T2008A','good class')
 
 --- Thành viên lớp học -> ClassMember ---
 create table ClassMember(
 RollNo nvarchar(10) primary key ,
 ClassNo nvarchar (100),
 JoinedDate date,
 OutedDate date
 )
 go 
 insert into ClassMember(RollNo,ClassNo,JoinedDate,OutedDate)
 values
 ('R001','T2008A','2020-08-08','2022-08-08'),
 ('R002','T2008A','2020-08-08','2022-08-08'),
 ('R003','T2008A','2020-08-08','2022-08-08'),
 ('R004','T2008A','2020-08-08','2022-08-08'),
 ('R005','T2008A','2020-08-08','2022-08-08')

 --- 6. Phân công công viêc -> Schedule ---
 create table Schedule(
ID int identity(1,1) primary key,
TeacherID nvarchar(100),
SubjectID nvarchar(100),
ClassID nvarchar(100) ,
Start_day date,
end_day date
 )
 go 
 insert into Schedule(TeacherID,SubjectID,ClassID,Start_day,end_day)
 values
 ('Tran Van Diep','HTML','T2008A','2018-07-01','2018-07-30'),
 ('Tran Van Diep','C program','T2008A','2018-07-01','2018-07-30'),
 ('Tran Van Diep','PHP','T2008A','2018-07-01','2018-07-30'),
 ('Tran Van Diep','JAVA','T2008A','2018-07-01','2018-07-30'),
 ('Tran Van Diep','JS','T2008A','2018-7-01','2018-07-30')

 ---7. Bảng điểm danh -> Attendance---
 create table Attendance_1(
 ScheduleId int,
 ID int identity (1,1) primary key ,
 Rollno nvarchar(20),
 Attendance_date date,
 Roll_call_1 date,
 Roll_call_2 date,
 note text
 )
 go
 insert into Attendance(ScheduleId,Rollno,Attendance_date,Roll_call_1,Roll_call_2,note)
 values
 ('1','R001','2020-12-09','2020-12-09','2020-12-09','Present'),
 ('2','R002','2020-12-09','2020-12-09','2020-12-09','Present'),
 ('3','R003','2020-12-09','2020-12-09','2020-12-09','Present'),
 ('4','R004','2020-12-09','2020-12-09','2020-12-09','Present'),
 ('5','R005','2020-12-09','2020-12-09','2020-12-09','Present')

 --- inserting data ---
 select *from Student
 select *from teacher
 select *from Subject
 select *from Class
 select *from ClassMember
 select *from Schedule
 select *from Attendance

 --- Tạo procedure để xem thông tin học viên trong một lớp học - đầu vào là tên lớp---

create proc proc_view_student
@tenlop nvarchar(100)
as
begin
	select  student.rollno, name_student as 'Ten HS',student.gender, student.phonenumber, student.email, student.address, class_name as 'Ten Lop'
	FROM student, class, ClassMember
	WHERE classmember.rollno = student.rollno and classmember.classno=ClassID and class_name =@tenlop
	GROUP BY student.rollno, name_student, student.gender, student.phonenumber, student.email,address,class_name
end

EXEC proc_view_student 'T2008A'

---Tạo procedure để xem danh sách điểm danh của lớp học, của môn môn học cụ thể. - đầu vào là mã lớp học và môn học---

 create proc proc_view_student3
 @ClassID nvarchar(100),
 @SubjectID nvarchar(100)
 as
 Begin
    Select  Attendance.rollno, Subject_name as 'Ten mon hoc ',Subject.total
	FROM Attendance,Subject,student, class,Schedule
	 where student.rollno=Attendance.rollno and Schedule.ClassID=class.ClassID and Schedule.ScheduleId = Attendance.ScheduleId 
	GROUP BY student.rollno, name_student, student.gender, student.phonenumber, student.email,address,class_name
 End

 Exec proc_view_student3 'T2008A', 'toan'


 ---- - tạo trigger để xóa dữ liệu học viên ---
 create trigger delete_student
 instead of delete
 as
 begin
	delete from  ClassMember where Rollno in (select Rollno from deleted)
	delete from Attendance where Rollno in (select Rollno from deleted)
	delete from Student where Rollno in (select Rollno from deleted)
 end
 delete student where rollno = 'R001'

 select *from Student

2020-12-10 09:24:47

create database QLDiemDanhAptech
use QLDiemDanhAptech

create table student (
	rollno nvarchar(10) primary key,
	student_name nvarchar(20),
	birthday date,
	gender nvarchar(10),
	address nvarchar(100),
	email nvarchar(50),
	phoneNumber nvarchar(16)
)

create table teacher (
	teacher_code nvarchar(10) primary key,
	teacher_name nvarchar(20),
	gender nvarchar(10),
	birthday date
)

create table subject (
	subject_code nvarchar(10) primary key,
	subject_name nvarchar(50),
	totalNumber  int
)

create table class (
	class_code nvarchar(10) primary key,
	class_name nvarchar(10),
	note ntext
) 


create table classMember (
	rollno nvarchar(10) foreign key references student(rollno),
	class_code nvarchar(10) foreign key references class(class_code),
	joinedDate date,
	outedDate date
)

create table Schedule (
	ScheduleId int primary key identity(1, 1),
	taecher_code nvarchar(10) foreign key references teacher(teacher_code),
	subject_code nvarchar(10) foreign key references subject(subject_code),
	startDate date,
	finishDate date
)

 alter table Schedule
 add class_code nvarchar(10) foreign key references class (class_code)

create table  Attendance (
	 ScheduleId int primary key foreign key references Schedule(ScheduleId),
	 rollno nvarchar(10),
	 AttendanceDate date,
	 Attendance1 nvarchar(20),
	 Attendence2 nvarchar(20),
	 note ntext
)

insert into student(rollno, student_name, birthday, gender, address, email, phoneNumber)
values
('R001', 'TRAN VAN A', '2001-01-01', 'nam', 'Ha noi', 'R001@gmail.com', '09876543211'),
('R002', 'TRAN VAN B', '2001-02-01', 'nam', 'Lang son', 'R002@gmail.com', '09876543212'),
('R003', 'TRAN VAN C', '2001-03-01', 'nam', 'Hai phong', 'R003@gmail.com', '09876543213'),
('R004', 'TRAN VAN D', '2001-04-01', 'nam', 'Nam dinh', 'R004@gmail.com', '09876543214'),
('R005', 'TRAN VAN E', '2001-05-01', 'nam', 'Ha noi', 'R005@gmail.com', '09876543215')

insert into teacher(teacher_code, teacher_name, gender, birthday)
values
('T001', 'NGUYEN VAN A', 'nam', '1985-12-12'),
('T002', 'NGUYEN VAN B', 'nam', '1986-09-12'),
('T003', 'NGUYEN VAN C', 'nam', '1987-10-12'),
('T004', 'NGUYEN VAN D', 'nam', '1988-02-12'),
('T005', 'NGUYEN VAN E', 'nam', '1985-06-12')

insert into subject(subject_code, subject_name, totalNumber)
values
('S001', 'LAP TRINH C', 12),
('S002', 'LAP TRINH HTML/CSS', 18),
('S003', 'LAP TRINH JS', 12),
('S004', 'SQL SERVER', 10),
('S005', 'LAP TRINH PHP', 20)

insert into class(class_code, class_name, note)
values
('P205', 'T2007A', 'NOTE1'),
('P206', 'T2007M', 'NOTE2'),
('P207', 'T2007N', 'NOTE3'),
('P208', 'T2008A', 'NOTE4'),
('P209', 'T2008M', 'NOTE5')

insert into classMember(rollno, class_code, joinedDate, outedDate)
values
('R001', 'P205', '2020-07-20', '2022-07-20'),
('R002', 'P206', '2020-07-20', '2022-07-20'),
('R003', 'P207', '2020-07-20', '2022-07-20'),
('R004', 'P208', '2020-08-20', '2022-08-20'),
('R005', 'P209', '2020-08-20', '2022-08-20')

insert into Schedule(taecher_code, subject_code, class_code, startDate, finishDate)
values
('T001', 'S002', 'P205', '2020-07-01', '2020-07-30'),
('T002', 'S003', 'P206', '2020-07-01', '2020-07-30'),
('T003', 'S001', 'P207', '2020-07-01', '2020-07-30'),
('T004', 'S004', 'P208', '2020-07-01', '2020-07-30'),
('T005', 'S005', 'P209', '2020-07-01', '2020-07-30')

insert into Attendance(ScheduleId, rollno, AttendanceDate, Attendance1, Attendence2)
values
(1, 'R001', '2020-07-10', 'P', 'P'),
(2, 'R002', '2020-07-10', 'P', 'KP'),
(3, 'R003', '2020-07-10', 'KP', 'KP'),
(4, 'R004', '2020-07-10', 'P', 'KP'),
(5, 'R005', '2020-07-10', 'KP', 'P')

select * from student
select * from teacher
select * from subject
select * from class
select * from classMember
select * from Schedule
select * from Attendance

create proc vw_infomation_student 
	@className nvarchar(10)
as
begin
	select student.rollno, student.student_name, student.birthday, student.address, 
	student.gender, student.email, student.phoneNumber,  classMember.rollno, class.class_name
	from classMember right join student on student.rollno=classMember.rollno
	      inner join class on classMember.class_code=class.class_code
	where class_name=@className
end

exec vw_infomation_student 'T2007A'

create proc vw_Attendence_subject
	@class_code nvarchar(10),
	@subject_name nvarchar(20)
as
begin
	select student.rollno, student.student_name, subject.subject_name, class.class_code, Attendance.Attendance1, Attendance.Attendence2
	from student, class, subject, Attendance, Schedule
	where student.rollno=Attendance.rollno and Schedule.class_code=class.class_code and Schedule.ScheduleId=Attendance.ScheduleId
	and Schedule.subject_code=subject.subject_code and class.class_code= @class_code and subject.subject_name= @subject_name
end

exec vw_Attendence_subject 'P205', 'LAP TRINH C'

create trigger TG_delete_student on student
instead of delete
as
begin
	delete from classMember where rollno in (select rollno from deleted)
	delete from Attendance where rollno in (select rollno from deleted)
	delete from student where rollno in (select rollno from deleted)
end

delete student where rollno='R001'

2020-12-09 16:10:25

create database quan_ly_diem_danh_app
use quan_ly_diem_danh_app

create table studentss(
id int identity (1,1) primary key,
rollno nvarchar (100),
fullname nvarchar (100),
sex nvarchar (50),
birthday date,
country nvarchar (100),
email nvarchar (100),
sdt nvarchar (50),)

create table teachers(
id int identity (1,1) primary key,
code_teacher nvarchar (100),
fullname_teacher nvarchar (100),
sex_teacher nvarchar (100),
birthday_teacher date ,
)

create table subss(
id int identity (1,1) primary key,
code_sub nvarchar (50),
name_sub nvarchar (50),
total nvarchar (100),
)

create table classs(
id int identity (1,1) primary key,
code_class nvarchar (100),
name_class nvarchar (100),
note nvarchar (100),)

create table classmembers(
id int identity (1,1) primary key,
rollno_member nvarchar (100),
classno nvarchar (100),
joindate date,
outdate date,
)

create table Schedules(
id int identity (1,1) primary key,
code_teach nvarchar (50),
code_sub nvarchar (50),
start_sub date,
finish_sub date,)

create table  Attendances(
ScheduleId int,
id int identity (1,1) primary key,
rollno_Attendance nvarchar (100),
date_Attendance date,
Attendance_1 nvarchar (100),
Attendance_2 nvarchar (100),
note nvarchar (100),)


delete from studentss
delete from Teachers
delete from subss
delete from classs
delete from classmembers
delete from Schedules
delete from Attendances

insert into studentss
values
('roo1','do minh quan','nam','2002-09-26','hai phong','dominhquan260902@gmail.com','0586543018'),
('roo2','do minh quan','nam','2002-09-25','hai phong','dominhquan260902@gmail.com','0586543018'),
('roo3','do minh quan','nam','2002-09-24','hai phong','dominhquan260902@gmail.com','0586543018'),
('roo4','do minh quan','nam','2002-09-23','hai phong','dominhquan260902@gmail.com','0586543018'),
('roo5','do minh quan','nam','2002-09-22','hai phong','dominhquan260902@gmail.com','0586543018')

insert into Teachers
values
('1','tran van diep','nam','1929-08-10'),
('2','tran van a','nam','1929-08-9'),
('3','tran van b','nam','1929-08-8'),
('4','tran van c','nam','1929-08-7'),
('5','tran van d','nam','1929-08-6')

insert into subss
values
('c001','toan','10'),
('c001','toan','10'),
('c001','toan','10'),
('c001','toan','10'),
('c001','toan','10')

insert into classs
values
('c001','lop hoc adc','khong co hoc sinh'),
('c001','lop hoc adc','khong co hoc sinh'),
('c001','lop hoc adc','khong co hoc sinh'),
('c001','lop hoc adc','khong co hoc sinh'),
('c001','lop hoc adc','khong co hoc sinh')

insert into classmembers
values
('roo1','lop1','2020-01-2','2020-05-2'),
('roo2','lop1','2020-01-2','2020-05-2'),
('roo3','lop1','2020-01-2','2020-05-2'),
('roo4','lop1','2020-01-2','2020-05-2'),
('roo5','lop1','2020-01-2','2020-05-2')

insert into  Schedules
values
('t001','e001','2020-09-10','2021-09-10'),
('t001','e001','2020-09-10','2021-09-10'),
('t001','e001','2020-09-10','2021-09-10'),
('t001','e001','2020-09-10','2021-09-10'),
('t001','e001','2020-09-10','2021-09-10')

insert into Attendances
values
('1','d1','2020-12-9','vang','du','rat la muon hoc'),
('2','d1','2020-12-9','vang','du','rat la muon hoc'),
('3','d1','2020-12-9','vang','du','rat la muon hoc'),
('4','d1','2020-12-9','vang','du','rat la muon hoc'),
('5','d1','2020-12-9','vang','du','rat la muon hoc')




--yeu cau select thong tin hoc vien trong moi lop hoc - dau vao la ten lop

create proc proc_yeu_cau_de_bai_1_1
@idclass nvarchar (100)
as
begin
	select studentss.rollno, studentss.fullname , studentss.sex ,studentss.sdt , studentss.country ,studentss.email ,studentss.birthday
	from studentss left join classs on studentss.rollno = classmembers.rollno_member
	where classs.name_class = @idclass
end

exec proc_yeu_cau_de_bai_1_1 


select Attendances.ScheduleId , Attendances.rollno_Attendance , Attendances.date_Attendance, Attendances.Attendance_1 , Attendances.Attendance_2 , Attendances.note ,
subss.code_sub , subss.name_sub , subss.total 
from Attendances , subss left join Schedules on Attendances.id=Schedules.id
where classs.code_class=@codeclass
      classs.name_class=@nameclass

create proc proc_yeu_cau_de_bai_1_2
@codeclass nvarchar (100)
@nameclass nvarchar (100)
as
begin
	select Attendances.ScheduleId , Attendances.rollno_Attendance , Attendances.date_Attendance, Attendances.Attendance_1 , Attendances.Attendance_2 , Attendances.note ,
subss.code_sub , subss.name_sub , subss.total 
from Attendances , subss left join Schedules on Attendances.id=Schedules.id
where classs.code_class=@codeclass
      classs.name_class=@nameclass

end

exec  proc_yeu_cau_de_bai_1_2

create trigger trigger_delete_stu on studentss
for delete
as
begin
delete from studentss 
where rollno=1
print N'ban da nhap sai du lieu xin hay nhap du lieu dung'
rollback transaction 
end 

2020-12-09 09:15:24

create database diemdanh
use diemdanh

create table student(
	rollno nvarchar(10) primary key,
	name nvarchar(100),
	gender nvarchar(10),
	dob date, 
	email nvarchar(100),
	phone_no int,
	quequan nvarchar(50)
)

insert into student
values
('R01', 'Pham A', 'Nam', '2003-01-10', 'abc@gmail.com', '0123456789', 'Hanoi'),
('R02', 'Le B', 'Nu', '2003-03-07', 'def@gmail.com', '0987654321', 'Hanoi'),
('R03', 'Hoang C', 'Nam', '2003-11-10', 'ghi@gmail.com', '0678954321', 'TP HCM'),
('R04', 'Nguyen D', 'Nam', '2000-01-29', 'wer@gmail.com', '0123459876', 'Danang'),
('R05', 'Tran E', 'Nu', '2001-08-24', 'jyt@gmail.com', '0123442785', 'TP HCM')

select * from student

create table teacher(
	id int primary key identity(1,1),
	name nvarchar(100),
	gender nvarchar(10),
	dob date
)

insert into teacher(name, gender, dob)
values
('Tran A', 'Nam', '1990-01-15'),
('Hoang B', 'Nu', '1989-03-01'),
('Le C', 'Nam', '1994-11-17'),
('Pham D', 'Nam', '1993-01-24'),
('Nguyen E', 'Nu', '1974-08-28')

select * from teacher

create table subject(
	id int primary key identity(1,1),
	name nvarchar(100),
	lesson_no int
)

insert into subject(name, lesson_no)
values
('Toan', '30'),
('Van', '32'),
('Anh', '32'),
('Phap', '25'),
('Ly', '25')

select * from subject

create table class(
	id int primary key identity(1,1),
	name nvarchar(100),
	note text
)

insert into class(name, note)
values
('12T1', 'Lop chuyen Toan'),
('12V', 'Lop chuyen Van'),
('12A2', 'Lop chuyen Anh'),
('12P1', 'Lop chuyen Phap'),
('12L2', 'Lop chuyen Ly')

select * from class

create table classmember(
	id int primary key identity(1,1),
	rollno nvarchar(10) references student(rollno),
	classno int references class(id),
	joined_date date,
	outed_date date
)

insert into classmember(rollno, classno, joined_date, outed_date)
values
('R02', 5, '2020-09-15', '2021-06-15'),
('R05', 3, '2020-09-16', '2021-06-16'),
('R03', 4, '2020-09-17', '2021-06-17'),
('R04', 1, '2020-09-14', '2021-06-14'),
('R01', 2, '2020-09-13', '2021-06-13')

select * from classmember

create table schedule(
	id int primary key identity(1,1),
	teacher_id int references teacher(id),
	subject_id int references subject(id),
	class_id int references class(id),
	startdate date,
	enddate date
)

insert into schedule(teacher_id, subject_id, class_id, startdate, enddate)
values
(3, 5, 5, '2020-09-20', '2021-06-12'),
(5, 3, 3, '2020-09-21', '2021-06-13'),
(4, 4, 4, '2020-09-20', '2021-06-13'),
(1, 1, 1, '2020-09-21', '2021-06-12'),
(2, 2, 2, '2020-09-22', '2021-06-13')

select * from schedule

create table attendance(
	id int references schedule(id),
	rollno nvarchar(10) references student(rollno),
	attendance_date date,
	first_time nvarchar(20),
	second_time nvarchar(20),
	note text
)

insert into attendance
values
(1, 'R02', '2020-12-09', 'Co', 'Khong', 'Nghi ko phep'),
(2, 'R05', '2020-12-08', 'Co', 'Co', 'Di hoc du'),
(3, 'R03', '2020-12-07', 'Khong', 'Khong', 'Nghi co phep'),
(4, 'R04', '2020-12-06', 'Co', 'Co', 'Di hoc du'),
(5, 'R02', '2020-12-05', 'Khong', 'Khong', 'Nghi ko phep')

select * from attendance

CREATE PROC proc_view_class_info
	@classname nvarchar(50)
AS
BEGIN
	SELECT student.rollno, student.name as 'Ten HS', student.dob, student.gender, student.phone_no, student.email, student.quequan, class.name as 'Ten Lop'
	FROM student, class, classmember
	WHERE classmember.rollno = student.rollno and classmember.classno=class.id and class.name = @classname
	GROUP BY student.rollno, student.name, student.dob, student.gender, student.phone_no, student.email, student.quequan, class.name
END

EXEC proc_view_class_info '12A1'

CREATE PROC proc_view_attendance_info
	@classid int, @subject nvarchar(100)
AS
BEGIN
	SELECT student.name as 'Ten HS', attendance.*, class.name as 'Ten Lop', subject.name as 'Ten Mon Hoc'
	FROM student, attendance, class, subject, schedule
	WHERE attendance.rollno = student.rollno and schedule.class_id=class.id and schedule.id=attendance.id
	and schedule.subject_id = subject.id and class.id = @classid and subject.name = @subject
END

EXEC proc_view_attendance_info '2','Anh'

create trigger TG_delete_student on student
instead of delete
as
begin
	delete from classmember where rollno in (select rollno from deleted)
	delete from attendance where rollno in (select rollno from deleted)
	delete from student where rollno in (select rollno from deleted)
end

delete student where rollno=1