IMG-LOGO
×

Giáo Trình Môn Học

Khoá học lập trình SQL

Kiến Thức Core SQL Server

[Video] Hướng dẫn cách xác định kiểu dữ liệu, primary key, foreign key trong SQL - My SQL - SQL Server [Video] Hướng dẫn thao tác nhiểu bảng - select - multi table - join, inner join trong SQL Server [Video] Hướng dẫn phân tích CSDL - phân tích bảng, column trong bản - Học lập trình SQL, SQL Server [Video] Hướng dẫn tìm hiểu create, alter , insert table và bản chất primary key, foreign key - SQL Server [Video] Hướng dẫn tìm hiểu select, and, in, like, not in,group by .. having, order by asc, desc - SQL Server [Video] Quản lý sinh viên (create, alter, update, insert, select) - Lập trình SQL Server

View SQL Server

[Video] Hướng dẫn tìm hiểu view trong SQL Server Bài Giảng: Hướng dẫn tìm hiểu view + proc trong SQL - Lập trình SQL Server

Store & Procedure

[Video] Hướng dẫn tìm hiểu proc procedure trong SQL Server - Lập trình SQL Server, MySQL Bài Giảng: Xây dự hệ thống đưa đón học sinh bằng xe bus của Trường Học - Lập trình SQL Server [Video] Xây dựng CSDL quản lý điểm danh aptech - Lập trình SQL Server/MySQL

Trigger

[Video] Học Trigger qua ví dụ - quản lý nhà trên phố - Lập trình SQL Server [Video] Hướng dẫn học trigger qua bài quản lý điểm danh Aptech - Lập trình SQL Server [Video] Bài tập - Quản lý nhân khẩu - Lập trình SQL Server - MySQL




Trang Chủ SQL Server/MySQL Xây dựng CSDL quản lý điểm danh aptech - Lập trình SQL Server/MySQL

Xây dựng CSDL quản lý điểm danh aptech - Lập trình SQL Server/MySQL

by GokiSoft.com - 20:11 12/01/2022 9,171 Lượt Xem

Quản lý điểm danh Aptech

1. Bảng học viên -> Student

- RollNo :  tên trường trong bảng

- tên học viên : tên trường trong bảng

- giới tinh, ngày sinh, quê quán, email, sđt : tên trường trong bảng

2. Bảng giáo viên -> Teacher

- mã giáo viên : tên trường trong bảng

- ten giáo viên, giới tính, ngày sinh : tên trường trong bảng

3. Bảng môn học -> Subject

- mã môn học : tên trường trong bảng

- tên môn học : tên trường trong bảng

- tổng số buổi : tên trường trong bảng

4. Bảng lớp học -> Class

- mã lớp học : tên trường trong bảng

- tên lớp học : tên trường trong bảng

- ghi chú : tên trường trong bảng

5. Thành viên lớp học -> ClassMember

- RollNo

- ClassNo

- JoinedDate

- OutedDate

6. Phân công công viêc -> Schedule

- ID : tự tăng

- Mã giáo viên

- Mã môn học

- Mã lớp học

- ngày bắt đầu - 1/7/2018

- ngày kết thúc - 30/7/2018

7. Bảng điểm danh -> Attendance

- ID: tự tăng

-  ScheduleId -> liên kết vs Schedule

- RollNo : tên trường trong bảng

- ngày điểm danh : tên trường trong bảng

- điểm danh lần 1 : tên trường trong bảng

- điểm danh lần 2 : tên trường trong bảng

- ghi chú : thông tin bổ sung -> tên trường trong bảng

Yêu cầu :

- Thiết kế database trên

- Thêm dữ liệu cho từng bảng, mỗi bảng 5 bản nghi

- 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

- 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

- tạo trigger để xóa dữ liệu học viên

Bình luận



Chia sẻ từ lớp học

Nguyễn Anh Vũ [T2008A]

Ngày viết: 15:11 11/12/2020



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', '[email protected]', '0964657860'),
('TH2007035', 'Tran Van Lam', 'Nam', '2002-04-28', '[email protected]', '0953547678'),
('TH2007036', 'Vu Quang Dong', 'Nu', '2002-04-28', '[email protected]', '0123648860'),
('TH2007037', 'Bui Van Manh', 'Nu', '2002-03-28', '[email protected]', '0352629766'),
('TH2007038', 'Do Mac Nam' , 'Gay', '2002-03-28', '[email protected]', '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'




Do Trung Duc [T2008A]

Ngày viết: 13:51 11/12/2020



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','[email protected]','0985764267'),
('Nam','Nam','1992-05-21','Hanoi','[email protected]','0985764267'),
('Quang','Nam','1991-06-02','Hanoi','[email protected]','0985764267'),
('Ngoc','Nu','1991-09-07','Hanoi','[email protected]','0985764267'),
('Van','Nu','1991-01-04','Hanoi','[email protected]','0985764267'),
('Hieu','Nam','1991-12-25','Hanoi','[email protected]','0985764267'),
('Linh','Nam','1991-12-24','Hanoi','[email protected]','0985764267'),
('Trung','Nam','1990-11-02','Hanoi','[email protected]','0985764267'),
('Mai','Nam','1991-06-08','Hanoi','[email protected]','0985764267'),
('Diep','Nam','1991-05-04','Hanoi','[email protected]','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','[email protected]',''),
('Teacher2','Nam','','Hanoi','[email protected]',''),
('Teacher3','Nam','','Hanoi','[email protected]',''),
('Teacher4','Nam','','Hanoi','[email protected]',''),
('Teacher5','Nam','','Hanoi','[email protected]','')

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


Trần Văn Lâm [T2008A]

Ngày viết: 12:38 11/12/2020



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','[email protected]','11312312'),
('R002','Nguyen Anh Vu','nam','2002-05-01','Thanh Hoa','[email protected]','11312312'),
('R003','Nguyen Dong Phan','nam','2002-05-01','Thanh Hoa','[email protected]','11312312'),
('R004','Bui Van Manh','nam','2002-05-01','Thanh Hoa','[email protected]','11312312'),
('R005','Nguyen Ba Hai','nam','2002-05-01','Thanh Hoa','[email protected]','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'


Nguyên Phấn Đông [T2008A]

Ngày viết: 21:23 10/12/2020



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', '[email protected]', 0585076514),
('MS02', 'Tran Van A', 'Nam', '1991-6-11', 'Ha Noi', '[email protected]', 0585076515),
('MS03', 'Tran Van B', 'Nu', '2000-12-1', 'Ha Noi', '[email protected]', 0585076516),
('MS04', 'Tran Van C', 'Nu', '1998-1-19', 'Hai Phong', '[email protected]', 0585076517),
('MS05', 'Tran Van E', 'Nam', '1998-12-1', 'Sai Gon', '[email protected]', 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


hainguyen [T2008A]

Ngày viết: 17:46 10/12/2020



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', '[email protected]', 012345567),
('MS02', 'Tran Van A', 'Nam', '1999-6-11', 'Ha Noi', '[email protected]', 013456563),
('MS03', 'Tran Van B', 'Nu', '1999-12-1', 'Ha Noi', '[email protected]', 013564573),
('MS04', 'Tran Van C', 'Nu', '1998-1-10', 'Hai Phong', '[email protected]', 013475634),
('MS05', 'Tran Van E', 'Nam', '1998-12-21', 'Si Gon', '[email protected]', 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


Đã sao chép!!!