Rules for Updating (Set “Rules for Deleting” & “Rules for Inserting”: Ignore)

Rules for Updating (Set “Rules for Deleting” & “Rules for Inserting”: Ignore)

Cascade: in student table, change “0010 Sanjar” (the student no is “0010”, and the student name is “Sanjar”) to “333” (Change Sanjar’s student no to 333), and his student no in score table will be changed automatically.

Restrict: in student table, update “333 Sanjar” to “0010 Sanjar”, the system will not allow such change.

3. SQL clause

There are the following tables in the Student_Management database:

(1) student(stu_no C(10), stu_name C(20), gender L, dep_no C(2), hometown C(30), birthday D, doe D, is_ccp L)

Notice: doe(date of enrollment), is_ccp(if is a Chinese Communist Party member)

(2) teacher(tea_no C(6), tea_name C(20), gender L, dep_no C(2), birthday D, dow D, cv M, salary N(10, 2), title C(1))

Notice: dow(Date of work)

(3) score(stu_no C(10), course_no C(8), score N(5, 1))

(4) course(course_no C(8), course_name C(50), credit N, is_compulsory L)

(5) department(dep_no C(2), dep_name C(30))

1. Display the total salary and average salary of male and female teachers respectively.

2. Display the number of boys of 2015 class (the first four digits of the student number is class number)

3. Display the name and working years of teachers who were born before 1965.