SQL basic grammar interview questions

The table is designed as follows:

domain name	data type	Field description	remarks
Class	Varchar	class	PK
No	Varchar	Student number	PK
Name	Varchar	Student name
Manager	varchar	Class cadre Y: Yes N: no

domain name	data type	Field description	remarks
Class	Varchar	class	PK
No	Varchar	Student number	PK
Maths	Float	Mathematics achievement
Chinese	Float	grade scores of Chinese
English	Float	English achievement

1. List the grade data of class cadres in all classes in the following format: class, student number, student name, math score, Chinese score, English score, total, and sort from high to low according to the total score

Answer:
I'll adjust some data first to facilitate the test

The answer to question 1 gradually surfaced
In fact, we need to be quick in the company, so we have to not only finish the analysis slowly, but also practice slowly to make perfect
List the grade data of class cadres in all classes in the following format: class, student number, student name, math score, Chinese score, English score, total, and sort from high to low according to the total score

Note that when sorting, the default is asc, that is, from low to high

2. Add a student field data = = > class: A1; No : 0004 ; Name: Chen Chen; Manager : N

3. Update the grade file to update the English scores of two students with class 1 and student numbers 0002 and 0003 to 87.5 (listed in a SQL grammar)

[deepen]: assuming that there is no Class number in the grade table, you need to combine another student table to query the corresponding data
After deepening, this question is the focus, because I haven't figured out how to write it for a long time. In fact, I'm confused. In fact, it's still very simple. Check it out first


The above figure is the result of confusion. It's complicated, but it's actually very simple
We finally updated the grade score table, but we just changed the sql we queried earlier,
The skeleton of the following UPDATE statement has not changed, or even the following UPDATE statement has not changed at all

Final result:

Guess that there can be no query such as where in the brackets after in
Temporary tables can solve this problem, or they can be written paragraph by paragraph and not executed together
Let's use a temporary table to solve this problem

4. Delete the data of all students surnamed Chen in the student's basic data file (for example, Chen Chen, Chen Feng, etc. belong to this range)

mysql Delete statement:

1,delete Delete a row: delete from student where id=1. 

2,delete Delete multiple rows: delete from student where in (1,2,3)3. 

3,Delete all data in the table: delete from student，Please use without where Clause delete sentence

Solution: create a temporary table

5. Conditions for listing student grade data: Class 1 with student number greater than 0002 or class 2 with student number greater than 0003 (listed in one SQL syntax)

The complete code is as follows:

select * from student s ,Grade g WHERE s.`No`=g.`No` and s.Manager="Y"

select * from student s ,Grade g WHERE s.`No`=g.`No` and s.Manager="Y"


select * from student s ,Grade g WHERE s.`No`=g.`No` and s.Manager="Y" and s.`Name`='Li Liang'

select sum(g.Maths,g.English,g.Chinese) from student s ,Grade g WHERE s.`No`=g.`No` and s.Manager="Y" and s.`Name`='Li Liang'

-- Question 1: list the grade data of class cadres in all classes, The format is as follows: class, Student number, Student name, Mathematics achievement, grade scores of Chinese, English achievement, Total score(total), And sorted by total score from high to low
select  s.class'class',
				s.`No`'Student number',
				s.`Name` 'Student name',
				g.Maths 'Mathematics achievement',
				g.Chinese'grade scores of Chinese',
				g.English 'English achievement',
			  g.Maths+g.English+g.Chinese as 'Total score' 
from student s ,Grade g WHERE s.`No`=g.`No` and s.Manager="Y" 
ORDER BY Total score 

-- 2.Add a student's basic information (Student)Field data==> Class: A1;  No : 0004 ;   Name: Chen Chen;    Manager : N
SELECT * FROM student
insert into student(cLASS,No,Name,ManageR) VALUES("A1","0007","Chen Chen","N")

-- ## 3. Update the grade file to update the English scores of two students with class 1 and student numbers 0002 and 0003 to 87.5 (listed in a SQL grammar)
select  DISTINCT * from student s ,grade g 
where s.`No`=g.`No` and g.Class= '1 class' 
and (g.`No` = '0002' or g.`No`='0003')

update (select  DISTINCT * from student s ,grade g 
where s.`No`=g.`No` and g.Class= '1 class' 
and (g.`No` = '0002' or g.`No`='0003')) as t
set  t.English==87


-- ## 4. Delete the data of all students surnamed Chen in the student's basic data file (for example, Chen Chen, Chen Feng, etc. belong to this range)

delete from student s
where s.`Name` in 
(select s1.Name FROM student s1  where s1.`Name` like 'Chen%' )

-- Solution: create a temporary table
create table tmp as (select s1.Name FROM student s1  where s1.`Name` like 'Chen%' )

delete from student
where Name in
(select * from tmp)

drop table IF EXISTS tmp


--- ## 5. Conditions for listing student grade data: Class 1 with student number greater than 0002 or class 2 with student number greater than 0003 (listed in one SQL syntax)

select * from grade g 
where (g.Class='1 class' and g.`No`>'0002')
or(g.Class='2 class' and g.`No`>'0003')