Homework - Education Management

Phần A: Tạo bảng

1. Viết các câu lệnh tạo bảng như phần mô tả bên dưới.
2. Tạo mỗi bảng từ 5-10 records: Sử dụng câu lệnh INSERT, thứ tự nhập dữ liệu cho các tables

Teachers

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	FirstName	nvarchar(50)
3	LastName	nvarchar(50)
4	Gender	bit
5	Email	varchar(50)				UNIQUE
6	PhoneNumber	varchar(50)	yes			UNIQUE
7	Address	nvarchar(500)	yes
8	DateOfBirth	datetime	yes

Students

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	FirstName	nvarchar(50)
3	LastName	nvarchar(50)
4	Gender	bit
5	Email	varchar(50)				UNIQUE
6	PhoneNumber	varchar(50)	yes			UNIQUE
7	Address	nvarchar(500)	yes
8	DateOfBirth	datetime	yes

Employees

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	FirstName	nvarchar(50)
3	LastName	nvarchar(50)
4	Gender	bit
5	Email	varchar(50)				UNIQUE
6	PhoneNumber	varchar(50)	yes			UNIQUE
7	Address	nvarchar(500)	yes
8	DateOfBirth	datetime	yes

Classes

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	Name	nvarchar(50)				UNIQUE
3	Description	nvarchar(500)	yes
4	StartDate	datetime				StartDate <= EndDate
5	EndDate	datetime				StartDate <= EndDate

Sample data:

Id	Name	Description
1	Batch 31	Khóa học M.E.R.N
2	Batch 84	Mỹ thuật đa phương tiện quốc tế
3	Batch 99	Lập trình viên quốc tế

Classes_Students

Relation Many-to-Many (Classes, Students):

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	ClassId	int		FK		Refrence to Classes (Id)
2	StudentId	int		FK		Refrence to Students (Id)

PRIMARY KEY

PRIMARY KEY (ClassId, StudentId)

Classes_Teachers

Relation Many-to-Many (Classes, Teachers):

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	ClassId	int		FK		Refrence to Classes (Id)
2	TeacherId	int		FK		Refrence to Teachers (Id)

PRIMARY KEY

PRIMARY KEY (ClassId, TeacherId)

Subjects

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	Name	nvarchar(100)				UNIQUE
3	Description	nvarchar(500)	yes

Sample data:

Id	Name	Description
1	HTML5, CSS3, JavaScript	Lập trình WEB căn bản
2	ReactJS	Lập trình FRONTEND ReactJS
3	ExpressJS	Lập trình BACKEND ExpressJS
4	MongoDB	CSDL NoSQL MongoDB
5	MySQL	CSDL MySQL
6	SQL Server	CSDL SQL Server

Subjects_Teachers

Relation Many-to-Many (Subjects, Teachers):

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	SubjectId	int		FK		Refrence to Subjects (Id)
2	TeacherId	int		FK		Refrence to Teachers (Id)

PRIMARY KEY

PRIMARY KEY (SubjectId, TeacherId)

Marks

Relation Many-to-Many (Subjects, Students):

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	StudentId	int		FK		Refrence to Students (Id)
3	SubjectId	int		FK		Refrence to Subjects (Id)
4	Mark	decimal(5, 2)			0	n >= 0 and n <= 10
5	Date	datetime			NOW

AttendanceTypes

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK
2	Name	nvarchar				UNIQUE
3	Description	nvarchar(500)	yes

Sample data:

Id	Name	Description
1	Present	Có mặt
2	Absent	Vắng
3	Late	Muộn
4	Excused	Có xin phép

Attendances

Relation Many-to-Many (Students, Subjects, AttendanceTypes):

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	StudentId	int		FK		Refrence to Students (Id)
3	SubjectId	int		FK		Refrence to Subjects (Id)
4	AttendanceTypeId	int		FK		Refrence to AttendanceTypes (Id)
5	Date	date			NOW
6	Description	nvarchar(500)	yes

UNIQUE CONSTRAINT

UNIQUE (SubjectId, StudentId, Date)

Tuitions

Relation Many-to-Many (Students, Employees):

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	StudentId	int		FK		Refrence to Students (Id)
3	InvoiceCode	varchar(10)				UNIQUE
4	InvoiceDate	datetime			NOW
5	Amount	decimal(18,2)				Amount > 0
6	EmployeeId	int		FK		Refrence to Employees (Id)
7	Description	nvarchar(500)	yes

Phần B: Truy vấn dữ liệu

Lớp học

1. Hiển thị danh sách các lớp học cùng với thông tin giáo viên phụ trách.
2. Hiển thị danh sách các lớp học cùng với thông tin giáo viên phụ trách và số lượng học sinh.
3. Hiển thị danh sách các lớp học cùng với thông tin giáo viên phụ trách và số lượng học sinh, sắp xếp theo tên lớp học.
4. Hiển thị danh sách các lớp học còn trống (chưa có học sinh nào đăng ký).
5. Hiển thị danh sách các lớp học còn học, sắp xếp theo ngày kết thúc tăng dần.
6. Hiển thị các lớp học đã kết thúc, sắp xếp theo ngày kết thúc giảm dần.
7. Hiển thị các lớp học chưa bắt đầu, sắp xếp theo ngày bắt đầu tăng dần.
8. Hiển thị lớp học có ít hơn 10 học sinh.
9. Hiển thị lớp học có nhiều hơn 20 học sinh.
10. Hiển thị lớp học có số lượng học sinh nhiều nhất.

Học sinh

1. Hiển thị danh sách học sinh của lớp học có tên là Batch 31.
2. Hiển thị danh sách học sinh của lớp học có tên là Batch 31, sắp xếp theo tên học sinh.

Môn học

1. Hiển thị danh sách môn học cùng với thông tin giáo viên dạy môn đó.
2. Hiển thị danh sách môn học cùng với thông tin giáo viên dạy môn đó, sắp xếp theo tên môn học.
3. Hiển thị danh sách môn học cùng với thông tin trung bình cộng điểm số của môn đó, sắp xếp theo tên môn học.

Điểm số

1. Hiển thị danh sác các học sinh có điểm số của môn học ReactJS, sắp xếp theo điểm số giảm dần.
2. Hiển thị danh sách các học sinh có điểm số của môn học ReactJS cao nhất, sắp xếp theo điểm số giảm dần.

Học phí

1. Hiển thị danh sách học sinh nộp học phí trong tháng 10/2020.
2. Hiển thị danh sách học sinh nộp học phí trong tháng 10/2020, sắp xếp theo ngày nộp học phí, sắp xếp theo tên học sinh.
3. Hiển thị danh sách học sinh chưa nộp học phí trong tháng 10/2020, sắp xếp theo tên học sinh.
4. Hiển thị tổng số tiền học phí đã đóng của học sinh có tên là Nguyễn Văn A.
5. Hiển thị tổng số tiền học phí đã đóng của học sinh có tên là Nguyễn Văn A trong năm 2020.
6. Hiển thị danh sách học sinh của lớp học có tên là Batch 31 số tiền nộp học phí trong tháng 10/2020, sắp xếp theo tên học sinh.

Điểm danh

1. Hiển thị danh sách điểm danh của học sinh có tên là Nguyễn Văn A trong tháng 10/2020, sắp xếp theo ngày điểm danh.
2. Hiển thị danh sách điểm danh của học sinh có tên là Nguyễn Văn A trong tháng 10/2020, sắp xếp theo ngày điểm danh, sắp xếp theo tên môn học.
3. Hiển thị danh sách điểm danh của học sinh có tên là Nguyễn Văn A trong tháng 10/2020, sắp xếp theo ngày điểm danh, sắp xếp theo tên môn học, sắp xếp theo tên giáo viên dạy môn học.
4. Hiển thị danh sách học sinh với số lần điểm danh vắng trong tháng 10/2020, sắp xếp theo số lần điểm danh vắng giảm dần.
5. Hiển thị danh sách học sinh với số lần điểm danh vắng trong tháng 10/2020, sắp xếp theo số lần điểm danh vắng giảm dần, sắp xếp theo tên học sinh.