SQL Lab 5

--Log on to the SQL Server server as a system administrator and use T-SQL statements for the following operations;
--1. The SNO of the student table in the STU database is defined as the primary key;
ALTER TABLE [student] Add constraint pk_student primary key (SNO)
--2. The CNO field of the table course of the database Stu is defined as the primary key, and the constraint name is CNO_PK;
ALTER TABLE [course] Add constraint CNO_PK primary key (CNO)
--3. Add a unique value constraint for the field CNAME in table course;
ALTER TABLE [course] Add constraint uq_course_name unique (CNAME)
--alter table[course] Drop constraint Uq_canme
--4. The Sno and CNO field combinations of the table SC of the database Stu are defined as the primary key, and the constraint name is SC_PK;
Alter TABLE[SC] DROP constraint PK_SC
Alter TABLE[SC] ADD constraint PK_SC primary key (SNO,CNO)
--5. The Sno and CNO fields for the data table SC are defined as external codes, which correspond to the main code sno of the table student and the main code course of the table CNO to achieve the following referential integrity:
Alter TABLE[SC]
Add constraint Fk_sc_sno foreign key (SNO) references student (SNO)
Alter TABLE[SC]
Add constraint Fk_sc_cno foreign key (CNO) References course (CNO)
--1) Delete the records in the student table while deleting the records in the SC table that have the same value as the Record Sno field;
Create Trigger S_c
On student after delete
As
Begin
DECLARE @preid char (9);
Select @preid =sno from deleted;
Delete SC where [email protected]
End
--2) When modifying the Sno of a record in a student table, if there are several records corresponding to the field value in the SC table, the modification is refused;
Create Trigger Modify_sno_rule
On student for insert
As
Begin
DECLARE @m_id char (9);
declare @num int;
Select @m_id =sno from inserted;

End
--3) When modifying the course Table CNO field value, the corresponding value of the field in the SC table should also be modified;
----4) When deleting a record in the course table, if the field exists in the SC table, delete the record for that field;
--5) When adding a record to the SC table, if the value of the Sno field in the record does not exist in student, the insertion is rejected;
--6. Defining a Check constraint requires that the student number Sno must be a 9-digit numeric character and cannot start with 0, and the 23rd digit is 0;
ALTER TABLE [student] Add constraint ck_sno1 check (sno like ' [1-9][0][0][0-9][0-9][0-9][0-9][0-9][0-9] ')
--7. Define the age value of the student in the student table in the STU database within the 16-25 range;
ALTER TABLE student add constraint ck_age check (sage between and 25)
--8. Defining the Student table in the STU database the names of middle school students are between 2-8;
ALTER TABLE student add constraint ck_name_length check (sname like ' __ ' | ' ___ ' | ' ____ ' | ' _____ ' | ' ___ ' | ' _______ ' | ' ________‘)
--9. Defining a student table in a Stu database only "male" or "female" can be entered in the sex column of middle school students;
ALTER TABLE student add constraint ck_sex check (ssex like ' man ' | ' Female ')
--10. Define the STU database student the default value of the student age value is 20;
ALTER TABLE student ADD constraint df_age default (for Sage)
--11. Modifying the student table the student's age value constraint can be in the range of 15-30;
ALTER TABLE student DROP constraint Ck_age
ALTER TABLE student add constraint ck_age check (sage between and 30)
--12. Delete the above unique value constraints, foreign key constraints, and check constraints;
ALTER TABLE [course] drop constraint uq_course_name
ALTER TABLE [student] drop constraint ck_sno1
ALTER TABLE student DROP constraint Ck_age
ALTER TABLE student DROP constraint Ck_name_length
--13. When inserting or modifying a record into the SC table, the trigger checks to see if the value of the record number field exists in the student table.
--Also check that the value of the course number exists if it does not exist, cancel the insert or modify it, or insert it successfully, perform an insert, modify operation on the SC, and verify the execution of the trigger.
Create Trigger Logic1 on student
For INSERT or update

--14. Design an update trigger that, when modified by the CNO column in the course table, activates the trigger to update records in the SC table at the same time.
--15. Design a trigger, constrain the course capacity of the database system is 80.
--16. Select title: Design examples to verify the similarities and differences between after triggers and instead OF triggers.

SQL Lab 5