Experimental content

According to the database db_student Medium tb_student surface 、tb_score、tb_course, Complete the following UPDATE statement

Table structure

• tb_student(sno,sn,dept,sex,birthday,polity)
• tb_score(sno,cno,score)
• tb_cource(cno,cn,ct,th)

Task solution

Task a

Insert a record into the student form , The student number is 00000000, The name is XXX, The gender is X The record of .

INSERT INTO tb_student(sno,sn,sex)
VALUES('00000000','XXX','X')

Task 2

Insert a record into the student's , The values of each field are :00000000,name,sex,department,XXXX-XX-XX,polity.

INSERT INTO tb_student(sno,sn,sex,dept,birthday,polity)
VALUES('00000000','name','sex','department','XXXX-XX-XX','polity')

Task three

Add a new course to the curriculum , The information is ：（‘C8’,‘ New computer technology ’,‘XXX’）.

ALTER TABLE tb_course
ADD na varchar(10) null
INSERT INTO tb_course(cno,cn,na) 
VALUES('c8',' New computer technology ','XXX')

Task 4

In the elective relationship table SC Add all student pairs to ’C8’ Elective relationship record of the course , The result is tentatively set as 60, Please complete this batch addition task with one command .

INSERT INTO tb_score(sno,cno,score) 
SELECT sno,'c8',60 -- cno/score  Set value 
from tb_student

Task five

Form all the data of girls into a new table , Table, student_girl.

select * 
INTO student_girl 
FROM tb_student WHERE sex=' Woman '

Task six

Name the students in the form as XX classmate , Change gender to female , The date of birth is 1998-1-1.

UPDATE tb_student
SET sex=' Woman ',birthday='1998-1-1'
WHERE sn='XX'

Task seven

take tb_score In the table c04 The course is equivalent to 60% And plus 40 branch .

UPDATE tb_score
SET score=score*0.6+40
WHERE cno='c04'

Task eight

Increase the scores of students taking data structure courses 15 branch .

UPDATE tb_score
SET score=score+15
WHERE cno=(SELECT cno 
		   FROM tb_course 
		   WHERE cn=' data structure ')

Task nine

Select “ New computer technology ” The grades of male students in the course are temporarily initialized and reset to 0.

UPDATE tb_score
SET score=0
WHERE sno IN (SELECT sno
			  FROM tb_student
			  WHERE sex=' male ')
AND cno=(SELECT cno --  Two parallel sub queries 
		 FROM tb_course
		 WHERE cn=' New computer technology ')

Task ten

Delete all students surnamed Zhang in the student table .

DELETE FROM tb_student
WHERE LEFT JOIN(sn,1)=' Zhang ' --  or  WHERE sn LIKE ' Zhang %'

Task 11

Delete student All the information of the failed students in the table .

DELETE FROM tb_student
WHERE sno=(SELECT DISTINCT sno
		   FROM tb_score
		   WHERE score<60)

Task 12

XXX Delete all students' information and course selection .

--  If the deletion order is reversed, you will not be able to find XXX Student student id 
-- 1. Delete the grade sheet XXX Grade information 
DELETE FROM tb_score
WHERE sno=(SELECT sno
           FROM tb_student
		   WHERE sn='XXX')
-- 2. Delete XXX Student information  
DELETE FROM tb_student
WHERE sn='XXX'

Task 13

Delete all student information in the student table （ The two methods ）.

-- method1
DELETE FROM tb_student

-- method2
TRUNCATE TABLE tb_student

---

Write the information in the modification table SQL When the sentence is , follow SET、UPDATE、WHERE The order of writing

That is, set the value first , Then select the scope , Finally, add restrictions . This way of thinking will be clearer