Khoá học lập trình SQL [Examination] Hệ thống quản lý sinh viên - SQL Server

Dưới đây là bản văn bản thuần (plain text) đã được định dạng sạch sẽ, giữ nguyên nội dung tiếng Anh và loại bỏ thang điểm để bạn dễ dàng sao chép:

1. Database Creation

• Create a database named ‘StudentManagementSystem’

• Make use of the database

2. Table Creation

Class – table to store class information

• Column: ClassId | Datatype: INT | Constraint: NOT NULL

• Column: ClassCode | Datatype: NVARCHAR(50)

Student – table to store student information

• Column: StudentId | Datatype: INT | Constraint: NOT NULL

• Column: StudentName | Datatype: NVARCHAR(50)

• Column: BirthDate | Datatype: DATETIME

• Column: ClassId | Datatype: INT

Subject – table to store subject information

• Column: SubjectId | Datatype: INT | Constraint: NOT NULL

• Column: SubjectName | Datatype: NVARCHAR(100)

• Column: SessionCount | Datatype: Int | Description: Number of sessions

Result – table to store study result of each student.

• Column: StudentId | Datatype: INT | Constraint: NOT NULL

• Column: SubjectId | Datatype: INT | Constraint: NOT NULL

• Column: Mark | Datatype: INT

3. Index Creation and Table Alternation

• Create NONCLUSTERED INDEX: Name: NCI_Student_StudentName | Table: Student | Column: StudentName

• Alter table Result, change data type of the column Mark to FLOAT

4. Constraints (added to Tables)

PRIMARY KEY

• PK_Class: ClassId (Table: Class)

• PK_Student: StudentId (Table: Student)

• PK_Subject: SubjectId (Table: Subject)

• PK_Result: StudentId, SubjectId (Table: Result)

FOREIGN KEY

• FK_Student_Class: Student(ClassId) references Class(ClassId)

• FK_Result_Student: Result(StudentId) references Student(StudentId)

• FK_Result_Subject: Result(SubjectId) references Subject(SubjectId)

CHECK

• CK_Subject_SessionCount: Table: Subject | Column: SessionCount | Condition: SessionCount > 0

5. Inserting Data

• Class: (1, 'C1106KV'), (2, 'C1108GV'), (3, 'C1108IV'), (4, 'C1108HV'), (5, 'C1109GV')

• Student: 1, PhạmTuấnAnh, 1993-08-05, 1 2, PhanVănHuy, 1992-06-10, 1 3, NguyễnHoàng Minh, 1992-09-07, 2 4, TrầnTuấnTú, 1993-10-10, 2 5, ĐỗAnhTài, 1992-06-06, 3

• Subject: 1, C Programming, 22 2, Web Design, 18 3, Database Management, 23

• Result: (1,1,8), (1,2,7), (2,3,5), (3,2,6), (4,3,9), (5,2,8)

6. Query Operations

• Search students whose BirthDate is between from ‘1992-10-10’ and ‘1993-10-10’. Display info of StudentId, StudentName, DateOfBirth.

• Count students in each class with information of ClassId, ClassCode, TotalStudent.

• Sum up mark of all subjects for each student, then display only students whose sum of mark is greater than 10. Display info of: StudentId, StudentName, TotalMark.

7. Views

• Create a view named view_StudentSubjectMark which should show result of each student by each subject, with information of: StudentId, StudentName, SubjectName, Mark.

• Show top 3 records from that view, which ordered by Mark of student (Descending).

8. Procedures

• Create a procedure named up_IncreaseMark, with input parameter @SubjectId (INT). This procedure increases 1 point for every Mark of every Student by that Subject.

• Execute the procedure with SubjectId=2.

9. Trigger (INSERT)

• Create a trigger named TG_Result_Insert, which would be execute when user inserts a new record into Result. This trigger will check if Mark < 0, then display an error message: “Cannot insert mark less than zero.”. Otherwise, allows user to insert record normally.

• Please execute the trigger using testing data (StudentId, SubjectId, Mark)=(1,3,-2)

10. Trigger (UPDATE)

• Create a trigger for UPDATE event named [TG_Subject_Update]. When the update event on [SubjectName] column of table [Subject] occurs, it will be failed and print message “You don’t update this column”.