Quản lý điểm danh Aptech

2021-05-10 08:10:22

create database QuanLyDiemDanhAptech

use QuanLyDiemDanhAptech

create table student (
	rollno nvarchar(20) primary key,
	name nvarchar(100),
	gender nvarchar(10),
	birthday date,
	que_quan nvarchar(100),
	email nvarchar(100),
	sdt int
)
go

create table teacher (
	ma_teacher nvarchar(20) primary key,
	name nvarchar(100),
	gender nvarchar(10),
	birthday date
)
go

create table subject (
	ma_monhoc nvarchar(20) primary key,
	name_subject nvarchar(50),
	tongsobuoi int
)
go

create table class (
	ma_class nvarchar(20) primary key,
	name_class nvarchar(20),
	note nvarchar(500)
)
go

create table classMember (
	rollno nvarchar(20) primary key references student(rollno),
	classno nvarchar(20) references class(ma_class),
	joinedDate date,
	outedDate date
)
go

create table schedule (
	id int primary key identity (1,1),
	ma_teacher nvarchar(20),
	ma_monhoc nvarchar(20),
	ma_class nvarchar(20),
	indate date,
	outdate date
)
go

create table attendance (
	scheduleId int references schedule(id),
	rollno nvarchar(20),
	ngaydiemdanh date,
	ddlan_1 nvarchar(20),
	ddlan_2 nvarchar(20),
	note nvarchar(500)
)
go

insert into student(rollno, name, gender, birthday, que_quan, email, sdt)
values
('MS01', 'Tran Van D', 'Nam', '1998-5-10', 'Nam Dinh', 'a@gmail.com', 0585076514),
('MS02', 'Tran Van A', 'Nam', '1991-6-11', 'Ha Noi', 'b@gmail.com', 0585076515),
('MS03', 'Tran Van B', 'Nu', '2000-12-1', 'Ha Noi', 'c@gmail.com', 0585076516),
('MS04', 'Tran Van C', 'Nu', '1998-1-19', 'Hai Phong', 'd@gmail.com', 0585076517),
('MS05', 'Tran Van E', 'Nam', '1998-12-1', 'Sai Gon', 'e@gmail.com', 0585076518)

insert into teacher(ma_teacher, name, gender, birthday)
values
('GV01', 'Tran Van A', 'Nam', '1989-06-30'),
('GV02', 'Tran Thi A', 'Nu', '1989-05-20'),
('GV03', 'Tran Van B', 'Nu', '1990-12-30'),
('GV04', 'Tran Van C', 'Nam', '1989-03-10'),
('GV05', 'Tran Van D', 'Nu', '1989-11-11')

insert into subject(ma_monhoc, name_subject, tongsobuoi)
values
('MH01', 'Lap Trinh C', 10),
('MH02', 'HTML/CSS/JS', 11),
('MH03', 'SQL Sever', 12),
('MH04', 'Boostrap', 13),
('MH05', 'Jquery', 10)

insert into class(ma_class, name_class, note)
values
('LH01', 'T2008A', ''),
('LH02', 'T2005A', ''),
('LH03', 'T2006A', ''),
('LH04', 'T2007A', ''),
('LH05', 'T2009A', '')

insert into classMember(rollno, classno, joinedDate, outedDate)
values
('MS01', 'LH01', '2020-01-12', '2020-02-12'),
('MS02', 'LH02', '2020-02-12', '2020-03-12'),
('MS03', 'LH03', '2020-04-12', '2020-05-12'),
('MS04', 'LH04', '2020-02-12', '2020-03-12'),
('MS05', 'LH05', '2020-03-12', '2020-04-12')

insert into schedule(ma_teacher, ma_monhoc, ma_class, indate, outdate)
values
('GV01', 'MH01', 'LH01', '2018-07-01', '2018-07-30'),
('GV02', 'MH02', 'LH02', '2018-07-01', '2018-07-30'),
('GV03', 'MH03', 'LH03', '2018-07-01', '2018-07-30'),
('GV04', 'MH04', 'LH04', '2018-07-01', '2018-07-30'),
('GV05', 'MH05', 'LH05', '2018-07-01', '2018-07-30')

insert into attendance(scheduleId, rollno, ngaydiemdanh, ddlan_1, ddlan_2, note)
values
(1, 'MS01', '2020-12-09', 'Co', 'Co', ''),
(2, 'MS02', '2020-12-09', 'Khong', 'Co', ''),
(3, 'MS03', '2020-12-09', 'Co', 'Co', ''),
(4, 'MS04', '2020-12-09', 'Co', 'Khong', ''),
(5, 'MS05', '2020-12-09', 'Co', 'Co', '')

select * from student
select * from teacher
select * from subject
select * from class
select * from classMember
select * from schedule
select * from attendance

create proc thong_tin_hoc_vien
 @class  nvarchar(50)
as
begin
	select class.name_class, classMember.classno, student.name, student.gender, student.birthday
	from classMember,studentc,class class 
	where classMember.rollno = student.rollno and classMember.classno = class.ma_class
end

exec thong_tin_hoc_vien 


alter table attendance
add constraint LK_at_st foreign key (rollno) references student(rollno)

alter table schedule
add constraint LK_sch_class foreign key (ma_class) references class(ma_class)

alter table schedule
add constraint LK_sch_sb foreign key (ma_monhoc) references subject(ma_monhoc)

create proc danh_sach_diem_danh
	@class nvarchar(20), @subject nvarchar(50)
as
begin
	select student.name, attendance.*, class.name_class, subject.name_subject
	from student, attendance, class, schedule, subject
	where attendance.rollno = student.rollno and schedule.ma_class = class.ma_class and schedule.id = attendance.scheduleId
	and schedule.ma_monhoc = subject.ma_monhoc and class.ma_class = @class and subject.name_subject = @subject
end

exec danh_sach_diem_danh 'LH01', 'Jquery'

create trigger detele_su_lieu 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 = 'MS01'

select * from student

2021-04-22 14:16:21

create database Aptech
go
use Aptech
go

--drop table Student
--drop table Teacher
--drop table ClassMember
--drop table Attendance
--drop table Class
--drop table Schedule
--drop table Subject
create table Student (
RollNo int primary key ,
name nvarchar(30),
gender nvarchar(5),
Bdate date,
native nvarchar(30),
email nvarchar(30),
sdt nvarchar(20)
)
go
create table Teacher(
id int primary key  ,
name nvarchar(30),
gender nvarchar(5),
Bdate date
)
go
create table Subject(
id int primary key ,

  name nvarchar(30),

  noB int

)
go
create table Class(
 ClassNo int primary key  ,

 name nvarchar(30),

 note nvarchar(50)
)
go
create table ClassMember(
 RollNo int references Student(RollNo),

 ClassNo int references Class(ClassNo),

 JoinedDate date,

 OutedDate date
)
go
create table Schedule(
id int primary key identity (1,1),

  idt int references Teacher(id),

  ids int references Subject(id),

  ClassNo int references Class(ClassNo),

  dateStart date,

  dateEnd date
)
go
create table Attendance(
id int primary key identity (1,1),

ScheduleId int references Schedule(id),

RollNo int references Student (RollNo),

	ngayDiemDannh date,
	diemDanhLan1 int,
	diemDanhLan2 int,
	ghiChu nvarchar(90)
)
go
insert into Teacher
values
	(11, 'Tran Van A', 'Nam', '1980-1-4'),
	(12, 'Tran Van B', 'Nam', '1989-1-4'),
	(13, 'Tran Thi A', 'Nu', '1987-1-4'),
	(14, 'Tran Thi B', 'Nu', '1986-1-4'),
	(15, 'Tran Van C', 'Nam', '1985-1-4')
go
insert into Subject
values
	(21, 'SQL Server', 10),
	(22, 'HTML', 10),
	(23, 'Bootstrap', 5),
	(24, 'Lorem Ipsum', 12),
	(25, 'Javascript', 7)
go
insert into Student
values
	(31, 'Nguyen Van A', 'Nam', '2000-4-1', 'Ha Noi', 'nguyenvana@gmail.com', 123456789),
	(32, 'Nguyen Van B', 'Nam', '2001-4-1', 'Ha Tay', 'nguyenvanb@gmail.com', 127246235),
	(33, 'Nguyen Van C', 'Nam', '1999-4-1', 'Ha Nam', 'nguyenvanc@gmail.com', 12451345),
	(34, 'Nguyen Thi A', 'Nu', '1994-4-1', 'Ha Noi', 'nguyenthia@gmail.com', 21353544),
	(35, 'Nguyen Thi B', 'Nu', '1995-4-1', 'Ha Tay', 'nguyenthib@gmail.com', 1234434)
go
insert into Class
values
	(41, 'Room001', ''),
	(42, 'Room002', ''),
	(43, 'Room003', ''),
	(44, 'Room004', 'thieu 1 may'),
	(45, 'Room005', 'dang sua chua')
go
insert into Schedule
values
	(13, 24, 41, '2021-03-22', '2021-04-22'),
	(12, 23, 42, '2021-03-25', '2021-04-25'),
	(11, 22, 43, '2021-03-27', '2021-04-27'),
	(15, 21, 44, '2021-03-21', '2021-04-21'),
	(14, 25, 45, '2021-03-30', '2021-04-30')
go
insert into Attendance
values
	(3, 33, '2021-4-9', 1, 0, 'Xin nghi nua buoi'),
	(2, 32, '2021-4-9', 1, 1, ''),
	(1, 31, '2021-4-9', 1, 1, ''),
	(4, 35, '2021-4-9', 1, 0, 'Xin nghi nua buoi'),
	(5, 34, '2021-4-9', 0, 0, 'Vang co phep')
go
insert into ClassMember
values
	(34, 43, '2021-03-22', '2021-04-22'),
	(33, 42, '2021-03-25', '2021-04-25'),
	(32, 41, '2021-03-27', '2021-04-27'),
	(31, 44, '2021-03-21', '2021-04-21'),
	(35, 45, '2021-03-30', '2021-04-30')
go



 



 create proc lop 
 @className nvarchar(30)
 as
 begin
		
		select Student.* from Student,ClassMember,Class                                                

			where ClassMember.RollNo=Student.RollNo and ClassMember.ClassNo=Class.ClassNo and Class.name=@className




 end

	exec lop 'Room004'




create proc Attendancing 
@classid int,
@subjectid int
 as
 begin
		
		select Student.* from Student,Subject,Schedule,Attendance,Class
			

			where Class.ClassNo=Schedule.ClassNo

			and
			 

				Attendance.ScheduleId=Schedule.id

			and 
			
				Attendance.RollNo=Student.RollNo

			and

				Subject.id=Schedule.ids


			and Subject.id=@subjectid and Class.ClassNo = @classid




 end


 exec Attendancing 3,2



 create trigger xoadulieu on Student
   instead of delete
   as
   begin
     delete from ClassMember where Rollno in (select deleted.Rollno from deleted)
	 delete from Attendance where Rollno in (select deleted.Rollno from deleted)
   end

 
 

2021-04-22 13:55:47

create database QuanLy_DiemDanh_Aptech
go
use QuanLy_DiemDanh_Aptech
go
-- tao bang Hoc Vien--
create table Student(
	RollNo int primary key identity(1,1),
	Name nvarchar(50),
	Gender nvarchar(10),
	Birth_Day date,
	HomeTown nvarchar(20),
	Email nvarchar(50),
	MobilePhone nvarchar(11)
)
go
_
-- tao bang Giao Vien--
create table Teacher(
	Id_Teacher int primary key identity(1,1),
	Name_Teacher nvarchar(50),
	Gender nvarchar(10),
	Birth_Day date
)
go
-- tao bang Mon Hoc--
create table Subject(
	Id_Subject int primary key identity(1,1),
	Name_Sj nvarchar(20),
	Sum_Sj int,
)
go
-- tao bang Lop Hoc--
create table Class(
	Id_Class int primary key identity(1,1),
	Name_Class nvarchar(10),
	Note nvarchar(200)
)
go
-- tao bang Thanh Vien Lop Hoc--
create table ClassMember(
	RollNo int,
	ClassNo int,
	JoinedDate date,
	OutedDate date
)
go
drop table ClassMember
-- tao bang Phan Cong Cong Viec--
create table Schedule(
	ID int primary key identity(1,1),
	Id_Teacher int,
	Id_Subject int,
	Id_Class int,
	StartDate date,
	StopDate date
)
go

--tao bang Diem Danh--
create table Atlendance(
	Id int primary key identity(1,1),
	Schedule_Id int, 
	RollNo int,
	Date_At date,
	Atlendance_1 nvarchar(10),
	Atlendance_2 nvarchar(10),
	note nvarchar(200)
)
go

--tao Foreign key--
alter table Schedule
add constraint FK_ID_Teacher foreign key (Id_Teacher) references Teacher(Id_Teacher)
go

alter table Schedule
add constraint FK_ID_Subject foreign key (Id_Subject) references Subject(Id_Subject)
go

alter table Schedule
add constraint FK_ID_Class foreign key (Id_Class) references Class(Id_Class)
go

alter table Atlendance
add constraint FK_ID_Schedule foreign key (Schedule_Id) references Schedule(Id)
go

alter table ClassMember
add constraint FK_ID_ClassMember foreign key (ClassNo) references Class(Id_Class)
go

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

alter table Atlendance
add constraint FK_ID_Student_At foreign key (RollNo) references Student(RollNo)
go

-- tao thong tin du lieu 5 ban ghi vao moi Table--
insert into Student(Name,Gender,Birth_Day,HomeTown,Email,MobilePhone)
values
('Tran Van A','Nam','2000-10-20','HaNoi','TVA@gmail.com','012345678'),
('Tran Van B','Nu','2000-1-2','HaNam','TVA@gmail.com','012345678'),
('Tran Van C','Nam','2000-5-6','NamDinh','TVA@gmail.com','012345678'),
('Tran Van D','Nu','2000-3-7','HaTay','TVA@gmail.com','012345678'),
('Tran Van E','Nam','2000-2-10','HaDong','TVA@gmail.com','012345678')
go

select * from Student

insert into Teacher(Name_Teacher,Gender,Birth_Day)
values
('Nguyen Tran A','Nam','1989-5-10'),
('Nguyen Tran B','Nam','1990-6-12'),
('Nguyen Tran C','Nu','1988-4-15'),
('Nguyen Tran D','Nam','1988-2-1'),
('Nguyen Tran E','Nu','1990-6-11')
go

select * from Teacher

insert into Subject(Name_Sj,Sum_Sj)
values
('CSS/Javscrip',5),
('PHP',6),
('SQL Server',10),
('C Sharp',8),
('HTML',7)
go

select * from Subject

insert into Class(Name_Class,Note)
values
('Class_1','Lop tot'),
('Class_2','Lop day hay'),
('Class_3','Lop chuan'),
('Class_4','Lop chat luong'),
('Class_5','Lop hay qua')
go

select * from Class

insert into ClassMember(RollNo,ClassNo,JoinedDate,OutedDate)
values
(1,1,'2020-1-1','2021-1-1'),
(2,2,'2020-2-2','2021-2-2'),
(3,3,'2020-3-3','2021-3-3'),
(4,4,'2020-4-4','2021-4-4'),
(5,5,'2020-5-5','2021-5-5')
go

select * from ClassMember

insert into Schedule(Id_Teacher,Id_Subject,Id_Class,StartDate,StopDate)
values
(1,2,3,'2020-1-2','2021-1-2'),
(2,4,2,'2020-2-3','2021-2-3'),
(3,5,4,'2020-3-4','2021-3-4'),
(4,1,5,'2020-4-5','2021-4-5'),
(5,3,1,'2020-5-6','2021-5-6')
go

select * from Schedule

insert into Atlendance(Schedule_Id,RollNo,Date_At,Atlendance_1,Atlendance_2,note)
values
(1,1,'2020-1-2','Co','Co','Nhanh'),
(2,2,'2020-2-3','Khong','Co','Muon'),
(3,3,'2020-3-4','Khong','Khong','Vang'),
(4,4,'2020-4-5','Co','Khong','Ve Som'),
(5,5,'2020-5-6','Co','Co','Day Du')
go

Select * from Atlendance

-- tao Pro de xem thong tin hoc vien trong 1 lop hoc va Dau vao la ten lop
create proc proc_view_Info_Student_Class 
	@NameClass nvarchar(10)
as
begin
	select Student.*,Class.Name_Class
	from Student,Class,ClassMember
	where Student.RollNo = ClassMember.RollNo
		and ClassMember.ClassNo = Class.Id_Class
		and Class.Name_Class = @NameClass
end
go

exec proc_view_Info_Student_Class 'Class_1' 
go
-- tao Pro de xem danh sach diem danh cua lop hoc,cua mon hoc cu the -- Dau vao la Ma lop hoc va Mon hoc
alter proc proc_view_Info_Attendance_Subject 
	@Id_Class int,
	@SubjectName nvarchar(20)
as
begin
	select Atlendance.*,Class.Name_Class,Subject.Name_Sj,Class.Id_Class
	from Atlendance,Class,Subject,Schedule
	where Atlendance.Schedule_Id = Schedule.ID
		and Schedule.Id_Class = Class.Id_Class
		and Schedule.Id_Subject = Subject.Id_Subject
		and Subject.Name_Sj = @SubjectName
		and Class.Id_Class = @Id_Class
end
go

exec proc_view_Info_Attendance_Subject 3,'PHP' 
go

select * from Atlendance
select * from ClassMember
select * from Student
select * from Subject

--tao trigger --
select * from Atlendance
select * from ClassMember
select * from Student


create trigger trigger_delete_Id_Student on Student
instead of delete
as
begin
	delete from Atlendance 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 from Student where RollNo = 1

2021-04-22 13:19:22

create database Ape

use Ape

create table Student
(
rollno int primary key identity(1,1),
name nvarchar(25),
gender nvarchar(10),
birth date,
home nvarchar(50),
email nvarchar(50),
phone nvarchar(20)
)

create table Teacher
(
id int primary key identity(1,1),
name nvarchar(25),
gender nvarchar(10),
birth date
)

create table Subject
(
id int primary key identity(1,1),
subject nvarchar(25),
count int
)

create table Class
(
id int primary key identity(1,1),
class nvarchar(25) not null,
note nvarchar(50)
)

create table ClassMember
(
rollno int references Student(rollno),
classid int references Class(id),
strt date,
emd date
)

create table Schedule 
(
id int primary key identity(1,1),
teacherid int references Teacher(id),
subjectid int references Subject(id),
classid int references Class(id),
strt date,
emd date
)

create table Attendance
(
id int primary key identity(1,1),
scheduleid int references Schedule(id),
rollno int references Student(rollno),
di date,
lan1 date,
lan2 date,
note nvarchar(50) 
)

insert into Student(name,gender,birth,home,email,phone)
values
('Vu Minh Hieu','Nam','2000-12-05','Ha Noi','vuiveheiu@gmail.com','037.713.2000'),
('Vu Minh Hiu','Nam','2000-12-15','Ha Nam','vuivehreiu@gmail.com','037.713.2052'),
('Vu Minh Hi','Nu','2000-12-25','Ha Cau','vuivehqwu@gmail.com','037.713.2054'),
('Vu Minh Hu','Nam','2000-12-07','Ha Tri','vuivehefgu@gmail.com','037.713.2021'),
('Vu Minh Hie','Nu','2000-12-19','Ha Tay','vuiveheqw@gmail.com','037.713.2023')

insert into Teacher(name,gender,birth)
values
('Tran Van Diep','Nam','1980-06-15'),
('Tran Van iep','Nu','1980-06-15'),
('Tran Van Dep','Nam','1980-06-15'),
('Tran Van Dip','Nu','1980-06-15'),
('Tran Van Di','Nam','1980-06-15')

insert into Subject(subject,count)
values
('Toan',12),
('Van',10),
('Anh',8),
('Ly',12),
('Hoa',12)
go

insert into Class(class,note)
values
('Lop Toan 1','Rat hay'),
('Lop Toan 2','Hay'),
('Lop Van','Rat Hayy'),
('Lop Van 2','Rat te'),
('Lop Anh 1','DC'),
('Lop Anh 2','Brilliant'),
('Lop Ly','nah'),
('Lop Hoa','hannah')
go

insert into ClassMember(rollno,classid,strt,emd)
values
(1,1,'2018-07-01','2018-07-15'),
(1,3,'2018-07-01','2018-07-15'),
(2,2,'2018-07-02','2018-07-16'),
(2,5,'2018-07-03','2018-07-17'),
(3,7,'2018-07-05','2018-07-20'),
(3,8,'2018-07-05','2018-07-20'),
(4,4,'2018-07-09','2018-07-24'),
(5,6,'2018-07-10','2018-07-25')
go

insert into Schedule(teacherid,subjectid,classid,strt,emd)
values
(1,1,1,'2018-07-01','2018-07-15'),
(2,1,2,'2018-07-02','2018-07-16'),
(3,2,3,'2018-07-01','2018-07-15'),
(1,2,4,'2018-07-09','2018-07-24'),
(2,3,5,'2018-07-03','2018-07-17'),
(3,3,6,'2018-07-10','2018-07-25'),
(4,4,7,'2018-07-05','2018-07-20'),
(5,5,8,'2018-07-05','2018-07-20')
go

insert into Attendance(scheduleid,rollno,di,lan1,lan2,note)
values
(1,1,'2018-07-01','2018-07-02','2018-07-04','Du'),
(3,1,'2018-07-01','2018-07-02','2018-07-04','Du'),
(2,2,'2018-07-02','2018-07-03','2018-07-05','Du'),
(4,4,'2018-07-09','2018-07-10','2018-07-13','thieu 1'),
(5,2,'2018-07-03','2018-07-05','2018-07-07','Du'),
(6,5,'2018-07-10','2018-07-10','2018-07-12','Du'),
(7,3,'2018-07-05','2018-07-07','2018-07-09','Rat du'),
(8,3,'2018-07-05','2018-07-07','2018-07-09','Dz')
go

create procedure SV 
@classid int
as
begin
select Student.rollno 'MSV', Student.name 'Ten SV',Class.class 'Ten Lop',ClassMember.strt 'Bat dau',ClassMember.emd 'Ket thuc'
from Student,Class,ClassMember
where Student.rollno=ClassMember.rollno
and ClassMember.classid = Class.id
and
classid = @classid 
end

drop procedure SV
exec SV 8

create procedure Attendancing
@classid int,
@subjectid int
as
begin
select Attendance.rollno 'MSV' ,Student.name 'Ten', Attendance.di 'Start',Attendance.lan1 'Lan dau', Attendance.lan2 'Lan 2', Schedule.classid 'Ma lop', Class.class 'Ten lop', Subject.subject 'Mon hoc'
from Attendance,Class,Schedule,Subject,Student
where Attendance.rollno=Student.rollno
and Schedule.id = Attendance.id
and Schedule.classid = Class.id
and Schedule.subjectid=Subject.id
and @classid = Schedule.classid
and @subjectid = Schedule.subjectid
end

drop procedure Attendancing
exec Attendancing 3,2

select*from Class
select*from Subject

create trigger XHV on Student
instead of delete
as
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)

delete from Student
where rollno=5


select*from Student
select*from Attendance
select*from ClassMember

2021-04-22 08:18:06

create database db0409_quanLyDiemDanhAptech

use db0409_quanLyDiemDanhAptech

create table Student (
	rollno int primary key,
	tenHocVien nvarchar(50),
	gioiTinh nvarchar(10),
	ngaySinh date,
	queQuan nvarchar(20),
	email nvarchar(20),
	soDienThoai int
)

create table Teacher (
	maGiaoVien int primary key,
	tenGiaoVien nvarchar(50),
	gioiTinh nvarchar(10),
	ngaySinh date
)

create table Subject (
	maMonHoc int primary key,
	tenMonHoc nvarchar(50),
	tongSoBuoi int,
)

create table Class (
	maLopHoc int primary key,
	tenLopHoc nvarchar(50),
	ghiChu nvarchar(700)
)

create table ClassMember (
	rollno int,
	maLopHoc int,
	joinedDate date,
	outedDate date
)

create table Schedule (
	id int identity(1,1) primary key,
	maGiaoVien int,
	maMonHoc int,
	maLopHoc int,
	ngayBatDau date,
	ngayKetThuc date
)

create table Attendance (
	id int identity(1,1) primary key,
	Schedule_id int foreign key references Schedule(id),
	rollno int,
	ngayDiemDannh date,
	diemDanhLan1 int,
	diemDanhLan2 int,
	ghiChu nvarchar(700)
)


alter table ClassMember
add foreign key (rollno) references Student(rollno)
alter table ClassMember
add foreign key (maLopHoc) references Class(maLopHoc)

alter table Schedule
add foreign key (maGiaoVien) references Teacher(maGiaoVien)
alter table Schedule
add foreign key (maMonHoc) references Subject(maMonHoc)
alter table Schedule
add foreign key (maLopHoc) references Class(maLopHoc)

alter table Attendance
add foreign key (rollno) references Student(rollno)


insert into Teacher
values
	(11, 'Tran Van A', 'Nam', '1980-1-4'),
	(12, 'Tran Van B', 'Nam', '1989-1-4'),
	(13, 'Tran Thi A', 'Nu', '1987-1-4'),
	(14, 'Tran Thi B', 'Nu', '1986-1-4'),
	(15, 'Tran Van C', 'Nam', '1985-1-4')

insert into Subject
values
	(21, 'SQL Server', 10),
	(22, 'HTML', 10),
	(23, 'Bootstrap', 5),
	(24, 'Lorem Ipsum', 12),
	(25, 'Javascript', 7)

insert into Student
values
	(31, 'Nguyen Van A', 'Nam', '2000-4-1', 'Ha Noi', 'nguyenvana@gmail.com', 123456789),
	(32, 'Nguyen Van B', 'Nam', '2001-4-1', 'Ha Tay', 'nguyenvanb@gmail.com', 127246235),
	(33, 'Nguyen Van C', 'Nam', '1999-4-1', 'Ha Nam', 'nguyenvanc@gmail.com', 12451345),
	(34, 'Nguyen Thi A', 'Nu', '1994-4-1', 'Ha Noi', 'nguyenthia@gmail.com', 21353544),
	(35, 'Nguyen Thi B', 'Nu', '1995-4-1', 'Ha Tay', 'nguyenthib@gmail.com', 1234434)

insert into Class
values
	(41, 'Room001', ''),
	(42, 'Room002', ''),
	(43, 'Room003', ''),
	(44, 'Room004', 'thieu 1 may'),
	(45, 'Room005', 'dang sua chua')

insert into Schedule
values
	(13, 24, 41, '2021-03-22', '2021-04-22'),
	(12, 23, 42, '2021-03-25', '2021-04-25'),
	(11, 22, 43, '2021-03-27', '2021-04-27'),
	(15, 21, 44, '2021-03-21', '2021-04-21'),
	(14, 25, 45, '2021-03-30', '2021-04-30')

insert into Attendance
values
	(3, 33, '2021-4-9', 1, 0, 'Xin nghi nua buoi'),
	(2, 32, '2021-4-9', 1, 1, ''),
	(1, 31, '2021-4-9', 1, 1, ''),
	(4, 35, '2021-4-9', 1, 0, 'Xin nghi nua buoi'),
	(5, 34, '2021-4-9', 0, 0, 'Vang co phep')

insert into ClassMember
values
	(34, 43, '2021-03-22', '2021-04-22'),
	(33, 42, '2021-03-25', '2021-04-25'),
	(32, 41, '2021-03-27', '2021-04-27'),
	(31, 44, '2021-03-21', '2021-04-21'),
	(35, 45, '2021-03-30', '2021-04-30')

create proc proc_view_Student_in_ClassName @tenLopHoc nvarchar(50)
as
	select Class.maLopHoc, Class.tenLopHoc, Student.*
	from Class join ClassMember on Class.maLopHoc = ClassMember.maLopHoc 
		join Student on ClassMember.rollno = Student.rollno
	where Class.tenLopHoc = @tenLopHoc
go

exec proc_view_Student_in_ClassName 'Room005'


create proc proc_view_attendance_by_classNo_and_subjectName @maLopHoc int, @tenMonHoc nvarchar(50)
as
	select Student.tenHocVien, Class.maLopHoc, Subject.tenMonHoc, Attendance.ngayDiemDannh, Attendance.diemDanhLan1, Attendance.diemDanhLan2, Attendance.ghiChu
	from Subject join Schedule on Subject.maMonHoc = Schedule.maMonHoc
		join Attendance on Schedule.id = Attendance.Schedule_id
		join Student on Student.rollno = Attendance.rollno
		join Class on Class.maLopHoc = Schedule.maLopHoc
	where Class.maLopHoc = @maLopHoc and Subject.tenMonHoc = @tenMonHoc
	order by Student.tenHocVien asc
go

exec proc_view_attendance_by_classNo_and_subjectName 43, 'HTML'


create trigger delete_Student on Student
instead of delete
as
	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)
go

delete from Student where rollno = 34

2020-12-11 08:11:37

create database Quan_Ly_Diem_Danh_Aptech
use Quan_Ly_Diem_Danh_Aptech

create table Student (
       Id int primary key identity(1,1),
       RollNo nvarchar(200),
	   Fullname nvarchar(50),
	   Gender nvarchar(10),
	   Birthday date,
	   Email nvarchar(100),
       Phone_number nvarchar(20)
)

create table Teacher (
       Id int primary key identity(1,1),
       Teachercode nvarchar(20),
	   Fullname nvarchar(50),
	   Gender nvarchar(10),
	   Birthday date
)

create table Subject (
       Id int primary key identity(1,1),
       Subjectcode nvarchar(20),
	   Name nvarchar(50),
       Total_sesionn int
)

create table Class (
       Id int primary key identity(1,1),
       Classcode nvarchar(50),
	   NameClass nvarchar(50),
	   note text
)

create table ClassMember (
       Id int primary key identity(1,1),
       RollNo nvarchar(200),
	   ClassNo nvarchar(50),
	   JoinedDate datetime,
	   OutedDate datetime
)

create table Schedule (
       Id int primary key identity(1,1),
	   TeacherCode nvarchar(30),
	   SubjectCode nvarchar(20),
	   ClassCode nvarchar(30),
	   StartDate datetime,
	   EndDate datetime
)

create table Attendance (
       Id int primary key identity(1,1),
	   Rollno nvarchar(200),
	   Attendance1 nvarchar(20),
	   Attendance2 nvarchar(20),
	   Note text
)
alter table ClassMember
	add constraint fk_Rollno foreign key (Rollno) references Student(Rollno)
alter table Schedule
	add constraint fk_TeacherCode foreign key (TeacherCode) references Teacher(TeacherCode)
alter table Schedule
	add constraint fk_SubjectCode foreign key (SubjectCode) references Subject(SubjectCode)
alter table Schedule
	add constraint fk_ClassCode foreign key (ClassCode) references Class(ClassCode)
alter table ClassMember
	add constraint fk_Classno foreign key (ClassNo) references Class(Rollno)

insert into Student(RollNo,Fullname,Gender,Birthday,Email,Phone_number)
values
('TH2007034', 'Nguyen Ba Hai', 'Nam', '2002-04-28', 'nguyenbahai280402@gmail.com', '0964657860'),
('TH2007035', 'Tran Van Lam', 'Nam', '2002-04-28', 'tranvanlam280402@gmail.com', '0953547678'),
('TH2007036', 'Vu Quang Dong', 'Nu', '2002-04-28', 'nguyenphandong280402@gmail.com', '0123648860'),
('TH2007037', 'Bui Van Manh', 'Nu', '2002-03-28', 'buivanmanh2803202@gmail.com', '0352629766'),
('TH2007038', 'Do Mac Nam' , 'Gay', '2002-03-28', 'domacnam2803202@gmail.com', '0352629766')

insert into Teacher(Teachercode,Fullname,Gender,Birthday)
values
('R001', 'Nguyen Anh Vu', 'Nam', '2002-04-28'),
('R002', 'Nguyen Anh Vu', 'Nam', '2002-04-28'),
('R003', 'Nguyen Anh Vu', 'Nam', '2002-04-28'),
('R004', 'Nguyen Anh Vu', 'Nam', '2002-04-28'),
('R005', 'Nguyen Anh Vu', 'Nam', '2002-04-28')

insert into Subject(Subjectcode,Name,Total_sesionn)
values
('T001', 'Lap Trinh C', 10),
('T002', 'HTML', 11),
('T003', 'SQL', 12),
('T004', 'JAVA', 13),
('T005', 'PHP', 11)

insert into Class(Classcode,NameClass,note)
values
('T2008A', 'Thay Diep', '12345'),
('T2008A', 'Thay Diep', '12345'),
('T2008A', 'Thay Diep', '12345'),
('T2008A', 'Thay Diep', '12345'),
('T2008A', 'Thay Diep', '12345')

insert into ClassMember(RollNo,ClassNo,JoinedDate,OutedDate)
values
('C001', 'T2008A', '2020-12-9', '2020-12-9'),
('C001', 'T2008A', '2020-12-9', '2020-12-9'),
('C001', 'T2008A', '2020-12-9', '2020-12-9'),
('C001', 'T2008A', '2020-12-9', '2020-12-9'),
('C001', 'T2008A', '2020-12-9', '2020-12-9')

insert into Schedule(TeacherCode,SubjectCode,ClassCode,StartDate,EndDate)
values
('R001','T001','T2008A', '2020-01-01', '2020-12-31'),
('R001','T001','T2008A', '2020-01-01', '2020-12-31'),
('R001','T001','T2008A', '2020-01-01', '2020-12-31'),
('R001','T001','T2008A', '2020-01-01', '2020-12-31'),
('R001','T001','T2008A', '2020-01-01', '2020-12-31')

insert into Attendance(Rollno,Attendance1,Attendance2,Note)
values
('TH2001034', 'co mat', 'co mat', 'tot'),
('TH2001035', 'co mat', 'co mat', 'tot'),
('TH2001036', 'co mat', 'co mat', 'tot'),
('TH2001037', 'co mat', 'co mat', 'tot'),
('TH2001038', 'co mat', 'co mat', 'tot')

select * from Student
select * from Class
select * from ClassMember
select Class.nameClass,ClassMember.ClassNo,Student.NameStudent,Student.gender,Student.birthday,Student.address,Student.email,Student.PhoneNumber
from Class,ClassMember,Student
where Student.rollno = ClassMember.rollno
      and ClassMember.ClassNo = Class.rollnoClass
	  and Class.nameClass = '10C'
create proc proc_view_thong_tin_hs
	@nameclass nvarchar(20)
as
begin
	select Class.nameClass,ClassMember.ClassNo,Student.NameStudent,Student.gender,Student.birthday,Student.address,Student.email,Student.PhoneNumber
from Class,ClassMember,Student
where Student.rollno = ClassMember.rollno
      and ClassMember.ClassNo = Class.rollnoClass
	  and Class.nameClass = @nameclass
end
exec proc_view_thong_tin_hs '10D'
select * from Subject
select * from Schedule
select * from Attendance
select Student.NameStudent,Class.nameClass,Subject.NameSubject,Schedule.rollnoClass,Schedule.rollnoSubject,Attendance.AttendanceDate,Attendance.Attendance1,Attendance.Attendance2,Attendance.note
from Student,Subject,Schedule,Attendance,Class
where Student.rollno = Attendance.rollno and Schedule.rollnoClass = Class.rollnoClass and Schedule.rollnoSubject = Subject.rollnoSubject
	and Schedule.rollnoClass = 'X005'
	and Schedule.rollnoSubject= 'A002'
create proc proc_view_diem_danh
	@rollnoClass nvarchar(20),
	@rollnoSub nvarchar(20)
as
begin
	select Student.NameStudent,Class.nameClass,Subject.NameSubject,Schedule.rollnoClass,Schedule.rollnoSubject,Attendance.AttendanceDate,Attendance.Attendance1,Attendance.Attendance2,Attendance.note
from Student,Subject,Schedule,Attendance,Class
where Student.rollno = Attendance.rollno and Schedule.rollnoClass = Class.rollnoClass and Schedule.rollnoSubject = Subject.rollnoSubject
	and Schedule.rollnoClass = @rollnoClass
	and Schedule.rollnoSubject= @rollnoSub
end
exec proc_view_diem_danh 'X005','A002'

2020-12-11 06:51:35

create database QuanLyDiemDanhAptech
use QuanLyDiemDanhAptech

create table Student(
Rollno int identity (1,1),
Name nvarchar (200),
Gender nvarchar (10), 
Birthday date , 
Address nvarchar (200), 
Email nvarchar (200), 
Phone nvarchar (50)
)

alter table Student
add constraint PK_Student primary key (Rollno)

create table Teacher(
TeacherID int identity (1,1),
TeacherName nvarchar (200), 
TeacherGender nvarchar (10), 
TeacherBirthday date , 
Address nvarchar (200), 
Email nvarchar (200), 
Phone varchar (50),
 constraint PK_Teacher primary key (TeacherID)
)


create table Subject(
SubjectID  int identity (1,1),
SujectName nvarchar (200), 
TotalDay int,
constraint PK_Subject primary key (SubjectID)
)

create table Class(
ClassID  int identity (1,1),
ClassName nvarchar (200), 
Note nvarchar (200), 
constraint PK_Class primary key (ClassID )
)

create table ClassMember(
ID int identity (1,1),
Rollno int,
ClassID int,
JoinDate date,
OutDate date,
constraint PK_ClassMember primary key (ID),
constraint FK_ClassMember_Rollno foreign key (Rollno) references Student(Rollno),
constraint FK_ClassMember_ClassID foreign key (ClassID) references Class(ClassID)
)

create table Schedule(
ID int identity(1,1),
TeacherID int,
ClassID int,
SubjectID int,
StartDay date,
EndDay date,
constraint PK_Schedule primary key (ID),
constraint FK_Schedule_TeacherID foreign key (TeacherID) references Teacher(TeacherID),
constraint FK_Schedule_ClassID foreign key (ClassID) references Class(ClassID),
constraint FK_Schedule_SubjectID foreign key (SubjectID) references Subject(SubjectID)
)

create table  Attendance(
ID int identity(1,1),
Rollno int,
ClassID int,
SubjectID int,
Ngaydiemdanh date,
Timeone nvarchar(10),
Timetwo nvarchar(10),
Ghichu nvarchar(500),
constraint PK_Attendance primary key (ID),
constraint FK_Attendance_Rollno foreign key (Rollno) references Student(Rollno),
constraint FK_Attendance_ClassID foreign key (ClassID) references Class(ClassID),
constraint FK_Attendance_SubjectID foreign key (SubjectID) references Subject(SubjectID)
)

select * from Student
select * from Class
select * from ClassMember
select * from Subject
select * from Schedule
select * from Attendance

INSERT INTO Student(Name,Gender,Birthday,Address,Email,Phone)
values
('Duc','Nam','1991-12-20','Hanoi','duc@gmail.com','0985764267'),
('Nam','Nam','1992-05-21','Hanoi','nam@gmail.com','0985764267'),
('Quang','Nam','1991-06-02','Hanoi','quang@gmail.com','0985764267'),
('Ngoc','Nu','1991-09-07','Hanoi','ngoc@gmail.com','0985764267'),
('Van','Nu','1991-01-04','Hanoi','van@gmail.com','0985764267'),
('Hieu','Nam','1991-12-25','Hanoi','hieu@gmail.com','0985764267'),
('Linh','Nam','1991-12-24','Hanoi','linh@gmail.com','0985764267'),
('Trung','Nam','1990-11-02','Hanoi','trung@gmail.com','0985764267'),
('Mai','Nam','1991-06-08','Hanoi','mai@gmail.com','0985764267'),
('Diep','Nam','1991-05-04','Hanoi','duc@gmail.com','0985764267')

insert into Class(ClassName,Note)
values
('T2008A','Hoc sang'),
('T2008B','Hoc sang'),
('T2008C','Hoc sang'),
('T2008D','Hoc chieu'), 
('T2008E','Hoc chieu')



drop table Class

Insert into ClassMember(Rollno,ClassID,JoinDate,OutDate)
values
(17,1,'2020-09-12','2020-08-12'),
(17,1,'2020-09-12','2020-08-12'),
(18,2,'2020-09-12','2020-08-12'),
(19,4,'2020-09-12','2020-08-12'),
(20,3,'2020-09-12','2020-08-12'),
(21,1,'2020-09-12','2020-08-12'),
(22,5,'2020-09-12','2020-08-12'),
(23,4,'2020-09-12','2020-08-12'),
(24,5,'2020-09-12','2020-08-12'),
(25,2,'2020-09-12','2020-08-12'),
(26,1,'2020-09-12','2020-08-12'),
(18,2,'2020-09-12','2020-08-12')

Insert into Subject (SujectName,TotalDay)
values
('C',15),
('HTML',20),
('SQL',10),
('PHP',12),
('Java',15)


select *from Teacher
insert into Teacher(TeacherName,TeacherGender,TeacherBirthday,Address,Email,Phone)
values
('Teacher1','Nam','','Hanoi','teacher1@gmail.com',''),
('Teacher2','Nam','','Hanoi','teacher2@gmail.com',''),
('Teacher3','Nam','','Hanoi','teacher3@gmail.com',''),
('Teacher4','Nam','','Hanoi','teacher4@gmail.com',''),
('Teacher5','Nam','','Hanoi','teacher5@gmail.com','')

select *from Schedule
insert into Schedule (TeacherID,ClassID,SubjectID,StartDay,EndDay)
values
('1','6',1,'',''),
('1','7',1,'',''),
('2','8',1,'',''),
('4','9',1,'',''),
('3','10',1,'','')


insert into Attendance (Rollno,ClassID,SubjectID,Ngaydiemdanh,Timeone,Timetwo,Ghichu)
values
('17','1','1','2020-12-08','co','co',''),
('18','2','2','2020-12-08','co','co',''),
('19','4','5','2020-12-08','co','co',''),
('26','1','1','2020-12-08','co','co',''),
('20','3','3','2020-12-08','co','co','')

select * from Student
select * from Class
select * from ClassMember
select * from Teacher
select * from Subject
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 xemthongtinlophoc 1
	@ClassName nvarchar(100)
as
BEGIN
	select ClassMember.ClassID, Student.Name, Student.Rollno from ClassMember left join Student on ClassMember.Rollno = Student.Rollno
	where ClassMember.ClassID = 1
end

EXEC xemthongtinlophoc 1

--- 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 xemthongtindiemdanh
	@ClassID int, @SubjectID int
as
begin
select Attendance.* , Student.Name
from Attendance left join Student on Attendance.Rollno=Student.Rollno
where Attendance.ClassID = @ClassID and Attendance.SubjectID = @SubjectID
end

exec xemthongtindiemdanh 1, 1

--Tao trigger xoa du lieu hoc vien

create trigger xoadulieuhocvien on Student
   instead of delete
   as
   begin
     delete from ClassMember where Rollno in (select deleted.Rollno from deleted)
	 delete from Attendance where Rollno in (select deleted.Rollno from deleted)
   end

     select * from Student
   select * from Attendance
   delete from Student  where Rollno = 26

2020-12-11 05:38:24

create database DiemDanhAptech
use DiemDanhAptech
create table Student(
	rollno nvarchar(20) primary key,
	NameStudent nvarchar(100),
	gender nvarchar(20),
	birthday date,
	address nvarchar(200),
	email nvarchar(100),
	PhoneNumber nvarchar(30)
)
create table Teacher(
	rollnoTeacher nvarchar(20) primary key,
	NameTeacher nvarchar(100),
	gender nvarchar(20),
	birthday date
)
create table Subject(
	rollnoSubject nvarchar(20) primary key,
	NameSubject nvarchar(100),
	total_session int
)
create table Class(
	rollnoClass nvarchar(20) primary key,
	nameClass nvarchar(100),
	note text
)
create table ClassMember(
	rollno nvarchar(20) primary key,
	ClassNo nvarchar(20),
	JoinedDate datetime,
	OutedDate datetime
)
create table Schedule(
	id int primary key identity(1,1),
	rollnoTeacher nvarchar(20),
	rollnoSubject nvarchar(20),
	rollnoClass nvarchar(20),
	startDate datetime,
	endDate datetime
)
create table Attendance(
	Schedule int primary key references Schedule(id),
	rollno nvarchar(20),
	AttendanceDate datetime,
	Attendance1 nvarchar(20),
	Attendance2 nvarchar(20),
	note text
)
alter table ClassMember
	add constraint fk_rollno foreign key (rollno) references Student(rollno)
alter table Schedule
	add constraint fk_rollnoTeacher foreign key (rollnoTeacher) references Teacher(rollnoTeacher)
alter table Schedule
	add constraint fk_rollnoSubject foreign key (rollnoSubject) references Subject(rollnoSubject)
alter table Schedule
	add constraint fk_rollnoClass foreign key (rollnoClass) references Class(rollnoClass)
alter table ClassMember
	add constraint fk_classno foreign key (ClassNo) references Class(rollnoClass)
select * from Student
insert into Student(rollno,NameStudent,gender,birthday,address,email,PhoneNumber)
values
('R001','Do Mac Nam','nam','2002-05-01','Thanh Hoa','namdbrr@gmail.com','11312312'),
('R002','Nguyen Anh Vu','nam','2002-05-01','Thanh Hoa','namdbrr@gmail.com','11312312'),
('R003','Nguyen Dong Phan','nam','2002-05-01','Thanh Hoa','namdbrr@gmail.com','11312312'),
('R004','Bui Van Manh','nam','2002-05-01','Thanh Hoa','namdbrr@gmail.com','11312312'),
('R005','Nguyen Ba Hai','nam','2002-05-01','Thanh Hoa','namdbrr@gmail.com','11312312')
select * from Teacher
insert into Teacher(rollnoTeacher,NameTeacher,gender,birthday)
values
('T001','Tran Van Diep','nam','1998-05-01'),
('T002','Tran Van Lam','nam','1998-05-01'),
('T003','Son Tung MTP','nam','1998-05-01'),
('T004','Jack','nam','1998-05-01'),
('T005','Hoa Vinh','nam','1998-05-01')
select * from Subject
insert into Subject(rollnoSubject,NameSubject,total_session)
values
('A001','Math',6),
('A002','Vietnamese',6),
('A003','History',8),
('A004','Art',5),
('A005','science',7)
select * from Class
insert into Class(rollnoClass,nameClass,note) 
values
('X001','10A','abcxyz'),
('X002','10B','abcxyz'),
('X003','10C','abcxyz'),
('X004','10D','abcxyz'),
('X005','10E','abcxyz')
select * from ClassMember
insert into ClassMember(rollno,ClassNo,JoinedDate,OutedDate)
values
('R001','X001','2018-07-01','2018-07-30'),
('R002','X002','2018-07-01','2018-07-30'),
('R003','X003','2018-07-01','2018-07-30'),
('R004','X004','2018-07-01','2018-07-30'),
('R005','X005','2018-07-01','2018-07-30')
select * from Schedule
insert into Schedule(rollnoTeacher,rollnoSubject,rollnoClass,startDate,endDate)
values
('T001','A002','X005','2018-07-01','2018-07-30'),
('T001','A003','X002','2018-07-01','2018-07-30'),
('T004','A002','X003','2018-07-01','2018-07-30'),
('T003','A004','X001','2018-07-01','2018-07-30'),
('T002','A001','X003','2018-07-01','2018-07-30')
select * from Attendance
insert into Attendance(Schedule,rollno,AttendanceDate,Attendance1,Attendance2,note)
values
(1,'R001','2018-07-07','co','co','abcxyz'),
(2,'R002','2018-07-07','co','co','abcxyz'),
(3,'R003','2018-07-07','ko','co','abcxyz'),
(4,'R004','2018-07-07','co','ko','abcxyz'),
(5,'R005','2018-07-07','co','ko','abcxyz')
select * from Student
select * from Class
select * from ClassMember
select Class.nameClass,ClassMember.ClassNo,Student.NameStudent,Student.gender,Student.birthday,Student.address,Student.email,Student.PhoneNumber
from Class,ClassMember,Student
where Student.rollno = ClassMember.rollno
      and ClassMember.ClassNo = Class.rollnoClass
	  and Class.nameClass = '10C'
create proc proc_view_thong_tin_hs
	@nameclass nvarchar(20)
as
begin
	select Class.nameClass,ClassMember.ClassNo,Student.NameStudent,Student.gender,Student.birthday,Student.address,Student.email,Student.PhoneNumber
from Class,ClassMember,Student
where Student.rollno = ClassMember.rollno
      and ClassMember.ClassNo = Class.rollnoClass
	  and Class.nameClass = @nameclass
end
exec proc_view_thong_tin_hs '10D'
select * from Subject
select * from Schedule
select * from Attendance
select Student.NameStudent,Class.nameClass,Subject.NameSubject,Schedule.rollnoClass,Schedule.rollnoSubject,Attendance.AttendanceDate,Attendance.Attendance1,Attendance.Attendance2,Attendance.note
from Student,Subject,Schedule,Attendance,Class
where Student.rollno = Attendance.rollno and Schedule.rollnoClass = Class.rollnoClass and Schedule.rollnoSubject = Subject.rollnoSubject
	and Schedule.rollnoClass = 'X005'
	and Schedule.rollnoSubject= 'A002'
create proc proc_view_diem_danh
	@rollnoClass nvarchar(20),
	@rollnoSub nvarchar(20)
as
begin
	select Student.NameStudent,Class.nameClass,Subject.NameSubject,Schedule.rollnoClass,Schedule.rollnoSubject,Attendance.AttendanceDate,Attendance.Attendance1,Attendance.Attendance2,Attendance.note
from Student,Subject,Schedule,Attendance,Class
where Student.rollno = Attendance.rollno and Schedule.rollnoClass = Class.rollnoClass and Schedule.rollnoSubject = Subject.rollnoSubject
	and Schedule.rollnoClass = @rollnoClass
	and Schedule.rollnoSubject= @rollnoSub
end
exec proc_view_diem_danh 'X005','A002'

2020-12-10 14:23:59

create database QuanLyDiemDanhAptech

use QuanLyDiemDanhAptech

create table student (
	rollno nvarchar(20) primary key,
	name nvarchar(100),
	gender nvarchar(10),
	birthday date,
	que_quan nvarchar(100),
	email nvarchar(100),
	sdt int
)
go

create table teacher (
	ma_teacher nvarchar(20) primary key,
	name nvarchar(100),
	gender nvarchar(10),
	birthday date
)
go

create table subject (
	ma_monhoc nvarchar(20) primary key,
	name_subject nvarchar(50),
	tongsobuoi int
)
go

create table class (
	ma_class nvarchar(20) primary key,
	name_class nvarchar(20),
	note nvarchar(500)
)
go

create table classMember (
	rollno nvarchar(20) primary key references student(rollno),
	classno nvarchar(20) references class(ma_class),
	joinedDate date,
	outedDate date
)
go

create table schedule (
	id int primary key identity (1,1),
	ma_teacher nvarchar(20),
	ma_monhoc nvarchar(20),
	ma_class nvarchar(20),
	indate date,
	outdate date
)
go

create table attendance (
	scheduleId int references schedule(id),
	rollno nvarchar(20),
	ngaydiemdanh date,
	ddlan_1 nvarchar(20),
	ddlan_2 nvarchar(20),
	note nvarchar(500)
)
go

insert into student(rollno, name, gender, birthday, que_quan, email, sdt)
values
('MS01', 'Tran Van D', 'Nam', '1998-5-10', 'Nam Dinh', 'a@gmail.com', 0585076514),
('MS02', 'Tran Van A', 'Nam', '1991-6-11', 'Ha Noi', 'b@gmail.com', 0585076515),
('MS03', 'Tran Van B', 'Nu', '2000-12-1', 'Ha Noi', 'c@gmail.com', 0585076516),
('MS04', 'Tran Van C', 'Nu', '1998-1-19', 'Hai Phong', 'd@gmail.com', 0585076517),
('MS05', 'Tran Van E', 'Nam', '1998-12-1', 'Sai Gon', 'e@gmail.com', 0585076518)

insert into teacher(ma_teacher, name, gender, birthday)
values
('GV01', 'Tran Van Diep1', 'Nam', '1989-06-30'),
('GV02', 'Tran Thi Diep2', 'Nu', '1989-05-20'),
('GV03', 'Tran Van Diep3', 'Nu', '1990-12-30'),
('GV04', 'Tran Van Diep4', 'Nam', '1989-03-10'),
('GV05', 'Tran Van Diep5', 'Nu', '1989-11-11')

insert into subject(ma_monhoc, name_subject, tongsobuoi)
values
('MH01', 'Lap Trinh C', 10),
('MH02', 'HTML/CSS/JS', 11),
('MH03', 'SQL Sever', 12),
('MH04', 'Boostrap', 13),
('MH05', 'Jquery', 10)

insert into class(ma_class, name_class, note)
values
('LH01', 'T2008A', ''),
('LH02', 'T2005A', ''),
('LH03', 'T2006A', ''),
('LH04', 'T2007A', ''),
('LH05', 'T2009A', '')

insert into classMember(rollno, classno, joinedDate, outedDate)
values
('MS01', 'LH01', '2020-01-12', '2020-02-12'),
('MS02', 'LH02', '2020-02-12', '2020-03-12'),
('MS03', 'LH03', '2020-04-12', '2020-05-12'),
('MS04', 'LH04', '2020-02-12', '2020-03-12'),
('MS05', 'LH05', '2020-03-12', '2020-04-12')

insert into schedule(ma_teacher, ma_monhoc, ma_class, indate, outdate)
values
('GV01', 'MH01', 'LH01', '2018-07-01', '2018-07-30'),
('GV02', 'MH02', 'LH02', '2018-07-01', '2018-07-30'),
('GV03', 'MH03', 'LH03', '2018-07-01', '2018-07-30'),
('GV04', 'MH04', 'LH04', '2018-07-01', '2018-07-30'),
('GV05', 'MH05', 'LH05', '2018-07-01', '2018-07-30')

insert into attendance(scheduleId, rollno, ngaydiemdanh, ddlan_1, ddlan_2, note)
values
(1, 'MS01', '2020-12-09', 'Co', 'Co', ''),
(2, 'MS02', '2020-12-09', 'Khong', 'Co', ''),
(3, 'MS03', '2020-12-09', 'Co', 'Co', ''),
(4, 'MS04', '2020-12-09', 'Co', 'Khong', ''),
(5, 'MS05', '2020-12-09', 'Co', 'Co', '')

select * from student
select * from teacher
select * from subject
select * from class
select * from classMember
select * from schedule
select * from attendance

create proc thong_tin_hoc_vien
 @class  nvarchar(50)
as
begin
	select class.name_class, classMember.classno, student.name, student.gender, student.birthday
	from classMember,studentc,class class 
	where classMember.rollno = student.rollno and classMember.classno = class.ma_class
end

exec thong_tin_hoc_vien 


alter table attendance
add constraint LK_at_st foreign key (rollno) references student(rollno)

alter table schedule
add constraint LK_sch_class foreign key (ma_class) references class(ma_class)

alter table schedule
add constraint LK_sch_sb foreign key (ma_monhoc) references subject(ma_monhoc)

create proc danh_sach_diem_danh
	@class nvarchar(20), @subject nvarchar(50)
as
begin
	select student.name, attendance.*, class.name_class, subject.name_subject
	from student, attendance, class, schedule, subject
	where attendance.rollno = student.rollno and schedule.ma_class = class.ma_class and schedule.id = attendance.scheduleId
	and schedule.ma_monhoc = subject.ma_monhoc and class.ma_class = @class and subject.name_subject = @subject
end

exec danh_sach_diem_danh 'LH01', 'Jquery'

create trigger detele_su_lieu 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 = 'MS01'

select * from student

2020-12-10 10:46:20

create database QuanLyDiemDanhAptech

use QuanLyDiemDanhAptech

create table student (
	rollno nvarchar(20) primary key,
	name nvarchar(100),
	gender nvarchar(10),
	birthday date,
	que_quan nvarchar(100),
	email nvarchar(100),
	sdt int
)

create table teacher (
	ma_teacher nvarchar(20) primary key,
	name nvarchar(100),
	gender nvarchar(10),
	birthday date
)

create table subject (
	ma_monhoc nvarchar(20) primary key,
	name_subject nvarchar(50),
	tongsobuoi int
)

create table class (
	ma_class nvarchar(20) primary key,
	name_class nvarchar(20),
	note nvarchar(500)
)

create table classMember (
	rollno nvarchar(20) primary key references student(rollno),
	classno nvarchar(20) references class(ma_class),
	joinedDate date,
	outedDate date
)

create table schedule (
	id int primary key identity (1,1),
	ma_teacher nvarchar(20),
	ma_monhoc nvarchar(20),
	ma_class nvarchar(20),
	indate date,
	outdate date
)

create table attendance (
	scheduleId int references schedule(id),
	rollno nvarchar(20),
	ngaydiemdanh date,
	ddlan_1 nvarchar(20),
	ddlan_2 nvarchar(20),
	note nvarchar(500)
)

insert into student(rollno, name, gender, birthday, que_quan, email, sdt)
values
('MS01', 'Tran Van D', 'Nam', '1999-5-10', 'Nam Dinh', 'a@gmail.com', 012345567),
('MS02', 'Tran Van A', 'Nam', '1999-6-11', 'Ha Noi', 'b@gmail.com', 013456563),
('MS03', 'Tran Van B', 'Nu', '1999-12-1', 'Ha Noi', 'c@gmail.com', 013564573),
('MS04', 'Tran Van C', 'Nu', '1998-1-10', 'Hai Phong', 'd@gmail.com', 013475634),
('MS05', 'Tran Van E', 'Nam', '1998-12-21', 'Si Gon', 'e@gmail.com', 01934534)

insert into teacher(ma_teacher, name, gender, birthday)
values
('GV01', 'Tran Van Diep', 'Nam', '1989-06-30'),
('GV02', 'Tran Thi Diep', 'Nu', '1989-05-20'),
('GV03', 'Tran Van U', 'Nu', '1990-12-30'),
('GV04', 'Tran Van H', 'Nam', '1989-03-10'),
('GV05', 'Tran Van L', 'Nu', '1989-11-11')

insert into subject(ma_monhoc, name_subject, tongsobuoi)
values
('MH01', 'Lap Trinh C', 10),
('MH02', 'HTML/CSS/JS', 11),
('MH03', 'SQL Sever', 12),
('MH04', 'Boostrap', 13),
('MH05', 'Jquery', 10)

insert into class(ma_class, name_class, note)
values
('LH01', 'T2008A', ''),
('LH02', 'T2005A', ''),
('LH03', 'T2006A', ''),
('LH04', 'T2007A', ''),
('LH05', 'T2009A', '')

insert into classMember(rollno, classno, joinedDate, outedDate)
values
('MS01', 'LH01', '2020-01-12', '2020-02-12'),
('MS02', 'LH02', '2020-02-12', '2020-03-12'),
('MS03', 'LH03', '2020-04-12', '2020-05-12'),
('MS04', 'LH04', '2020-02-12', '2020-03-12'),
('MS05', 'LH05', '2020-03-12', '2020-04-12')

insert into schedule(ma_teacher, ma_monhoc, ma_class, indate, outdate)
values
('GV01', 'MH01', 'LH01', '2018-07-01', '2018-07-30'),
('GV02', 'MH02', 'LH02', '2018-07-01', '2018-07-30'),
('GV03', 'MH03', 'LH03', '2018-07-01', '2018-07-30'),
('GV04', 'MH04', 'LH04', '2018-07-01', '2018-07-30'),
('GV05', 'MH05', 'LH05', '2018-07-01', '2018-07-30')

insert into attendance(scheduleId, rollno, ngaydiemdanh, ddlan_1, ddlan_2, note)
values
(1, 'MS01', '2020-12-09', 'Co', 'Co', ''),
(2, 'MS02', '2020-12-09', 'Khong', 'Co', ''),
(3, 'MS03', '2020-12-09', 'Co', 'Co', ''),
(4, 'MS04', '2020-12-09', 'Co', 'Khong', ''),
(5, 'MS05', '2020-12-09', 'Co', 'Co', '')

select * from student
select * from teacher
select * from subject
select * from class
select * from classMember
select * from schedule
select * from attendance

create proc thong_tin_hoc_vien
as
begin
	select class.name_class, classMember.classno, student.name, student.gender, student.birthday
	from student, class, classMember
	where classMember.rollno = student.rollno and classMember.classno = class.ma_class
end

exec thong_tin_hoc_vien 

alter table attendance
add constraint LK_at_st foreign key (rollno) references student(rollno)

alter table schedule
add constraint LK_sch_class foreign key (ma_class) references class(ma_class)

alter table schedule
add constraint LK_sch_sb foreign key (ma_monhoc) references subject(ma_monhoc)

create proc danh_sach_diem_danh
	@class nvarchar(20), @subject nvarchar(50)
as
begin
	select student.name, attendance.*, class.name_class, subject.name_subject
	from student, attendance, class, schedule, subject
	where attendance.rollno = student.rollno and schedule.ma_class = class.ma_class and schedule.id = attendance.scheduleId
	and schedule.ma_monhoc = subject.ma_monhoc and class.ma_class = @class and subject.name_subject = @subject
end

exec danh_sach_diem_danh 'LH01', 'Jquery'

create trigger detele_su_lieu 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 = 'MS01'

select * from student