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 [Video] Xây dựng CSDL quản lý điểm danh aptech - Lập trình SQL Server/MySQL

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

by GokiSoft.com - 17:50 30/10/2021 2,966 Lượt Xem



--- Tao CSDL -> QuanLyDiemDanh
create database QuanLyDiemDanh
go

--- Active CSDL
use QuanLyDiemDanh
go

--- Phan I. Thiet ke tables
----- Tao bang Student
create table Student (
	rollno nvarchar(20) primary key,
	fullname nvarchar(50) not null,
	gender nvarchar(15),
	address nvarchar(150),
	birthday date
)
go

create table Teacher(
	id int primary key identity(1, 1),
	email nvarchar(150) unique,
	fullname nvarchar(150) not null,
	birthday date,
	gender nvarchar(15)
)
go

create table Subject (
	id int primary key identity(1,1),
	name nvarchar(50) not null,
	session int default 0
)
go

create table Class (
	id int primary key identity(1, 1),
	name nvarchar(50) not null,
	note nvarchar(200)
)
go

create table ClassMember (
	rollno nvarchar(20) not null,
	classno int not null,
	joined_date date,
	outed_date date,
	constraint pk_class_member primary key (rollno, classno)
)
go

alter table ClassMember
add constraint fk_class_member_rollno foreign key (rollno) references Student (rollno)

alter table ClassMember
add constraint fk_class_member_classno foreign key (classno) references Class (id)

create table Schedule (
	id int primary key identity(1, 1),
	teacherId int references Teacher(id),
	subjectNo int references Subject (id),
	classNo int references Class (id),
	startDate date,
	endDate date
)
go

------ attendence1, attendence2 : -1, 0, 1 : Vang, Nghi Co Phep, Di Hoc
------ attendence1, attendence2 : A, PA, P : Vang, Nghi Co Phep, Di Hoc
create table Attendences (
	id int primary key identity(1, 1),
	scheduleId int references Schedule(id),
	checkin datetime,
	attendence1 nvarchar(5) default 'P',
	attendence2 nvarchar(5) default 'P',
	note nvarchar(100)
)
go

------- TEST
select * from Student
select * from Class
select * from ClassMember
select * from Teacher
select * from Subject
select * from Schedule
select * from Attendences
-------- Phan II. Them du lieu
insert into Student (rollno, fullname, gender, birthday, address)
values
('R001', 'Tran Van A', 'Nam', '1999-03-06', 'Ha Noi'),
('R002', 'Tran Van B', 'Nam', '1997-05-19', 'Nam Dinh'),
('R003', 'Tran Van C', 'Nam', '1993-02-09', 'Ha Nam'),
('R004', 'Tran Van D', 'Nam', '1998-01-19', 'Thai Binh'),
('R005', 'Tran Van E', 'Nam', '1991-09-29', 'Hung Yen')
go

insert into Class (name)
values
('C1803L'),
('C1610I'),
('C1808G'),
('C2002L'),
('C1812L')
go

select * from Class

insert into ClassMember (rollno, classno, joined_date, outed_date)
values
('R001', 1, '2018-02-15', '2020-06-06'),
('R002', 1, '2018-02-15', '2020-06-06'),
('R003', 2, '2018-12-15', null),
('R004', 2, '2018-12-15', null),
('R005', 3, '2019-02-15', null)
go

insert into ClassMember (rollno, classno, joined_date, outed_date)
values
('R001', 4, '2018-02-15', null),
('R002', 4, '2018-02-15', null),
('R003', 4, '2018-12-15', null),
('R004', 5, '2018-12-15', null),
('R005', 5, '2019-02-15', null)
go

insert into Teacher (fullname, birthday, email, gender)
values
('Tran Van Diep', '1986-02-02', '[email protected]', 'Nam'),
('Tran Van OK', '1986-12-02', '[email protected]', 'Nam')
go

insert into Subject(name, session)
values
('Lap Trinh C', 10),
('Bootstrap/jQuery', 6),
('HTML/CSS/JS', 10),
('SQL Server', 10),
('PHP/Laravel', 10)
go

select * from Schedule

insert into Schedule(teacherId, subjectNo, classNo, startDate, endDate)
values
(1, 4, 4, '2020-07-16', '2020-09-16'),
(1, 5, 5, '2020-07-10', '2020-09-10')
go

select * from Attendences

--- FIX : checkin => checkin1 & checkin2
--- rollno 
alter table Attendences
drop column checkin

alter table Attendences
add checkin1 datetime

alter table Attendences
add checkin2 datetime

alter table Attendences
add rollno nvarchar(20) references Student (rollno)

select * from Attendences

insert into Attendences (scheduleId, rollno, checkin1, attendence1, checkin2, attendence2, note)
values
(1, 'R001', '2020-08-20 19:05:00', 'A', '2020-08-20 20:50:00', 'P', ''),
(1, 'R002', '2020-08-20 19:05:00', 'P', '2020-08-20 20:50:00', 'P', ''),
(1, 'R003', '2020-08-20 19:05:00', 'P', '2020-08-20 20:50:00', 'PA', '')

insert into Attendences (scheduleId, rollno, checkin1, attendence1, checkin2, attendence2, note)
values
(2, 'R004', '2020-08-20 19:05:00', 'P', '2020-08-20 20:50:00', 'P', ''),
(2, 'R005', '2020-08-20 19:05:00', 'P', '2020-08-20 20:50:00', 'P', '')
go

--- - 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
------- ClassName, RollNo, Fullname, Gender, Birthday, Address
------- ClassName => Class, Other => Student, ClassMember
create proc proc_view_students_in_class
	@ClassName nvarchar(50)
as
begin
	select Class.name ClassName, Student.rollno, Student.fullname, Student.gender, Student.birthday, Student.address
	from Class left join ClassMember on Class.id = ClassMember.classno
		left join Student on ClassMember.rollno = Student.rollno
	where Class.name = @ClassName
end

exec proc_view_students_in_class N'C2002L'

---- 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
---- ClassName, SubjectName, Fullname, RollNo, checkin1, attendence1, checkin2, attendence2
------- Class, Subject, Student, Attendences, Schedule
select Class.name ClassName, Subject.name SubjectName, Student.fullname, Student.rollno, Attendences.checkin1, Attendences.attendence1, Attendences.checkin2, Attendences.attendence2
from Class, Subject, Student, Attendences, Schedule
where Class.id = Schedule.classNo
	and Subject.id = Schedule.subjectNo
	and Student.rollno = Attendences.rollno
	and Schedule.id = Attendences.scheduleId
	and Class.name = 'C2002L'
	and Subject.name = 'PHP/Laravel'

create proc proc_xem_diem_danh
	@ClassName nvarchar(50),
	@SubjectName nvarchar(50)
as
begin
	select Class.name ClassName, Subject.name SubjectName, Student.fullname, Student.rollno, Attendences.checkin1, Attendences.attendence1, Attendences.checkin2, Attendences.attendence2
	from Class, Subject, Student, Attendences, Schedule
	where Class.id = Schedule.classNo
		and Subject.id = Schedule.subjectNo
		and Student.rollno = Attendences.rollno
		and Schedule.id = Attendences.scheduleId
		and Class.name = @ClassName
		and Subject.name = @SubjectName
end

create proc proc_xem_diem_danh_sinh_vien
	@ClassName nvarchar(50),
	@SubjectName nvarchar(50),
	@RollNo nvarchar(20)
as
begin
	select Class.name ClassName, Subject.name SubjectName, Student.fullname, Student.rollno, Attendences.checkin1, Attendences.attendence1, Attendences.checkin2, Attendences.attendence2
	from Class, Subject, Student, Attendences, Schedule
	where Class.id = Schedule.classNo
		and Subject.id = Schedule.subjectNo
		and Student.rollno = Attendences.rollno
		and Schedule.id = Attendences.scheduleId
		and Class.name = @ClassName
		and Subject.name = @SubjectName
		and Student.rollno = @RollNo
end

exec proc_xem_diem_danh N'C2002L', N'SQL Server'
exec proc_xem_diem_danh_sinh_vien N'C2002L', N'SQL Server', N'R001'
exec proc_xem_diem_danh_sinh_vien N'C2002L', N'SQL Server', N'R005'



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

Bình luận



Phản Hồi Bởi:

(1) TRẦN VĂN ĐIỆP [Teacher]

Chia sẻ từ lớp học

TRẦN VĂN ĐIỆP [Teacher]

Ngày viết: 20:11 01/09/2020

Trigger

TRẦN VĂN ĐIỆP [Teacher]

Ngày viết: 19:49 01/09/2020

Trigger


-- TEST
select * from Student

-- Trigger khong cho phep sua RollNo
create trigger trigger_no_update_rollno_student on Student
for update
as
begin
	if update(rollno)
	begin
		print N'Khong cho phep update rollno'
		rollback transaction
	end
end

insert into Student(rollno, fullname, gender, address, birthday)
values
('R006', 'ABC', 'Nam', 'Ha Noi', '1999-09-02')

update Student set rollno = 'R006' where rollno = 'R007'

-- Trigger check Gender
select * from Student
update Student set gender = 'Khac', address = 'Nam Dinh' where rollno = 'R007'
update Student set fullname = 'TRAN VAN X' where rollno = 'R007'
update Student set gender = '123' where rollno = 'R007'

create trigger trigger_check_update_gender_student on Student
for update
as
begin
	if (select count(gender) from inserted where gender not in ('Nam', 'Nu', 'Khac')) > 0
	begin
		print N'Yeu cau gioi tinh phai nhan 1 trong cac gia tri sau Nam, Nu, Khac'
		rollback transaction
	end
end

alter trigger trigger_check_update_gender_student on Student
for update
as
begin
	-- update Student set fullname = 'TRAN VAN X' where rollno = 'R007'
	-- gender => khong ton tai query tren => inserted
	if (select count(gender) from inserted where gender in ('Nam', 'Nu', 'Khac')) = 0
	begin
		print N'Yeu cau gioi tinh phai nhan 1 trong cac gia tri sau Nam, Nu, Khac'
		rollback transaction
	end
end

-- Trigger cho phép xoá sinh viên theo rollno
----- rollno trong bang Student => tao khoa ngoai (foreign key) tren nhung bang nao
-------- ClassMember, Attendance => Neu xoa ban nghi tren ClassMember & Attendance => thi co
----------------- bi error khong??? Khong co foreign key toi cac bang khac
create trigger InsteadOf_Delete_Rollno_Student on Student
instead of delete
as
begin
	delete from ClassMember where rollno in (select rollno from deleted)
	delete from Attendences where rollno in (select rollno from deleted)
	delete from Student where rollno in (select rollno from deleted)
end

select * from Student

delete from Student where rollno = 'R005'


Đã sao chép!!!