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 - 18:28 22/04/2021 8,329 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:10 10/05/2021



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 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


Đào Mạnh Dũng [C2010L]

Ngày viết: 21:16 22/04/2021



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', '[email protected]', 123456789),
	(32, 'Nguyen Van B', 'Nam', '2001-4-1', 'Ha Tay', '[email protected]', 127246235),
	(33, 'Nguyen Van C', 'Nam', '1999-4-1', 'Ha Nam', '[email protected]', 12451345),
	(34, 'Nguyen Thi A', 'Nu', '1994-4-1', 'Ha Noi', '[email protected]', 21353544),
	(35, 'Nguyen Thi B', 'Nu', '1995-4-1', 'Ha Tay', '[email protected]', 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 [email protected]




 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 [email protected] 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

 
 

















Võ Như Việt [C2010L]

Ngày viết: 20:55 22/04/2021



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','[email protected]','012345678'),
('Tran Van B','Nu','2000-1-2','HaNam','[email protected]','012345678'),
('Tran Van C','Nam','2000-5-6','NamDinh','[email protected]','012345678'),
('Tran Van D','Nu','2000-3-7','HaTay','[email protected]','012345678'),
('Tran Van E','Nam','2000-2-10','HaDong','[email protected]','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


hieuvm0512 [community,C2010L]

Ngày viết: 20:19 22/04/2021



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','[email protected]','037.713.2000'),
('Vu Minh Hiu','Nam','2000-12-15','Ha Nam','[email protected]','037.713.2052'),
('Vu Minh Hi','Nu','2000-12-25','Ha Cau','[email protected]','037.713.2054'),
('Vu Minh Hu','Nam','2000-12-07','Ha Tri','[email protected]','037.713.2021'),
('Vu Minh Hie','Nu','2000-12-19','Ha Tay','[email protected]','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



Vũ Ngọc Văn [community,C2010L]

Ngày viết: 15:18 22/04/2021



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', '[email protected]', 123456789),
	(32, 'Nguyen Van B', 'Nam', '2001-4-1', 'Ha Tay', '[email protected]', 127246235),
	(33, 'Nguyen Van C', 'Nam', '1999-4-1', 'Ha Nam', '[email protected]', 12451345),
	(34, 'Nguyen Thi A', 'Nu', '1994-4-1', 'Ha Noi', '[email protected]', 21353544),
	(35, 'Nguyen Thi B', 'Nu', '1995-4-1', 'Ha Tay', '[email protected]', 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


Đã sao chép!!!