Teacher’s Table

(Teacher no.) ID (Teacher’s name
1 Zhang SAN
2 Li si
3 Cathy

Course

(Course Id) (Course name (Teacher ID) t_id
1 Chinese language and literature 2
2 mathematics 1
3 English 3

Student Table

Student ID (Student name Date of birth Sex (ID number) ID_NO
1 Wang2 xiao3 ming2 110102199001011619
2 Zhang Yida 110104199012211630
3 zhang 110103199005206122
4 Yun-long li 110104199008061173
5 Chu jia jia 110101199112018221
6 Yun-fei zhao 110109199203014722
7 Li xin 110228198907018304
8 Yang ping 110111199001201382
9 Ms ulrich 110114199401201677

Score

(Student ID) s_id (Course Id) C_id (score) s_score
1 1 80
1 2 90
1 3 99
2 1 70
2 2 60
2 3 80
3 1 80
3 2 80
3 3 80
4 1 50
4 2 30
4 3 20
5 1 76
5 2 87
5 3 95
6 1 31
6 2 88
6 3 34
7 1 66
7 2 89
7 3 98
8 1 59
8 2 88
9 2 67
9 3 88

1. Calculate the date of birth of the student according to the rules of the ID number (from the 7th digit to the 14th digit of the ID number indicates the date of birth) and store it in the birth field in date format.

UPDATE
  student 
SET 
  birth = SUBSTR(id_no, 7.8);
Copy the code

2. According to the rule of the id number (the second digit from the bottom if singular is a boy. If it’s even, it’s girl), calculate the gender of the student and store it in the sex field.

UPDATE
  student
SET
  sex = CASE WHEN LEFT(RIGHT(id_no, 2), 1) % 2 = 0 THEN 'woman' ELSE 'male' END;
Copy the code

Query the student whose birth date is between 1990-07-01 and 1992-02-01.

SELECT
  *
FROM
  student
WHERE
  birth BETWEEN '1990-07-01' AND '1992-02-01';
Copy the code

4. Query the total score and average score of each course, by course code, course name, total score, average score display.

SELECT
  c.id, c.name, SUM(s.s_score), AVG(s.s_score)
FROM
  score s
  LEFT JOIN course c ON s.c_id = c.id
GROUP BY s.c_id;
Copy the code

Add a salary field to the teacher table. Salary field is a floating point number with two decimal places.

ALTER TABLE teacher ADD salary DECIMAL(18.2);
Copy the code

6. Query transcript of Chinese course, ask to display student number, student name, score, and rank.

SELECT
  stu.id, stu.name, s.s_score
FROM
  score s
  LEFT JOIN student stu ON s.s_id = stu.id
  LEFT JOIN course c ON s.c_id = c.id
WHERE
  c.name = 'Chinese'
ORDER BY s.s_score DESC;
Copy the code

Select name, id number and course score from student where math is higher than language.

SELECT
  stu.name, stu.id_no, s1.s_score
FROM
  student stu,
  (SELECT s.s_id, s.s_score FROM score s LEFT JOIN course c ON s.c_id = c.id WHERE c.name = 'Chinese') s1,
  (SELECT s.s_id, s.s_score FROM score s LEFT JOIN course c ON s.c_id = c.id WHERE c.name = 'mathematics') s2
WHERE
  stu.id = s1.s_id
  AND stu.id = s2.s_id
  AND s1.s_score > s2.s_score;
Copy the code

Select * from student where gpa > 60; select * from student where gpa > 60;

SELECT
  stu.id, stu.name, AVG(s.s_score)
FROM
  score s
  LEFT JOIN student stu ON s.s_id = stu.id
GROUP BY s.s_id
HAVING AVG(s.s_score) > = 60;
Copy the code

Select student id, student name, total number of courses, total score of all courses.

SELECT
  stu.id, stu.name, COUNT(1), SUM(s_score)
FROM
  score s
  LEFT JOIN student stu ON s.s_id = stu.id
GROUP BY s.s_id;
Copy the code

Query the number of teachers surnamed Wang.

SELECT
  COUNT(1)
FROM
  teacher
WHERE
  name LIKE 'the king %';
Copy the code

Query the names of the students who have studied Mr. Zhang’s lectures.

SELECT
  stu.name
FROM
  score s
  LEFT JOIN student stu ON s.s_id = stu.id
  LEFT JOIN course c ON s.c_id = c.id
WHERE
  c.name = (SELECT c.name FROM course c LEFT JOIN teacher t ON c.t_id = t.id WHERE t.name = 'Joe');
Copy the code

Query the name of the student who has not studied Teacher Li si’s lecture.

SELECT
  stu.name
FROM
  student stu
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      score s,
      course c
    WHERE
      s.s_id = stu.id
      AND s.c_id = c.id
      AND c.name = (SELECT c.name FROM course c LEFT JOIN teacher t ON c.t_id = t.id WHERE t.name = 'bill'));Copy the code

Select name from student who took course 1 and who also took course 2;

SELECT
  name
FROM
  student
WHERE
  id IN (SELECT s_id FROM score WHERE c_id = 1)
  AND id IN (SELECT s_id FROM score WHERE c_id = 2);
Copy the code

14. Shows the grades of all students in all courses, as well as their average grades, from highest to lowest gpa.

SELECT
  s.*, s1.avgScore 
FROM
  score s,
  (SELECT s_id, AVG(s_score) avgScore FROM score GROUP BY s_id) s1
WHERE
  s.s_id = s1.s_id
ORDER BY s1.avgScore DESC;
Copy the code

Select course number, course name, highest score, lowest score, average score, pass rate, medium rate, excellent rate, excellent rate.

Pass: >= 60, medium: 70 80, Excellent: 80 90, Excellent: >= 90

SELECT
  c.id 'Course Number',
  c.name 'Course Name'.MAX(s.s_score) 'Highest score'.MIN(s.s_score) 'Lowest score'.AVG(s.s_score) 'Average score'.SUM(CASE WHEN s.s_score > = 60 THEN 1 ELSE 0 END) / COUNT(s.s_id) * 100 'Pass rate'.SUM(CASE WHEN s.s_score > = 70 AND s.s_score < 80 THEN 1 ELSE 0 END) / COUNT(s.s_id) * 100 'Average'.SUM(CASE WHEN s.s_score > = 80 AND s.s_score < 90 THEN 1 ELSE 0 END) / COUNT(s.s_id) * 100 'Good rate'.SUM(CASE WHEN s.s_score > = 90 THEN 1 ELSE 0 END) / COUNT(s.s_id) * 100 'Excellence rate'
FROM
  score s
  LEFT JOIN course c ON s.c_id = c.id
GROUP BY s.c_id;
Copy the code

Friendship tips: the topic comes from each real enterprise, the above answers are for reference only, can not determine whether to meet the topic to examine the knowledge point!