One: Preparing data
#Create tables and insert records
CREATE TABLE class ( cid int(11) NOT NULL AUTO_INCREMENT, caption varchar(32) NOT NULL, PRIMARY KEY (cid) ) ENGINE=InnoDB CHARSET=utf8; INSERT INTO class VALUES (1, 'Class Two, Three Years'), (2, 'Three-year shift'), (3, 'Class Two a Year'), (4, 'Nine Classes in Two Years'); CREATE TABLE course( cid int(11) NOT NULL AUTO_INCREMENT, cname varchar(32) NOT NULL, teacher_id int(11) NOT NULL, PRIMARY KEY (cid), KEY fk_course_teacher (teacher_id), CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO course VALUES (1, 'Biology', 1), (2, 'Physics', 2), (3, 'Sports', 3), (4, 'Fine Arts', 2); CREATE TABLE score ( sid int(11) NOT NULL AUTO_INCREMENT, student_id int(11) NOT NULL, course_id int(11) NOT NULL, num int(11) NOT NULL, PRIMARY KEY (sid), KEY fk_score_student (student_id), KEY fk_score_course (course_id), CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid), CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO score VALUES (1, 1, 1, 10), (2, 1, 2, 9), (5, 1, 4, 66), (6, 2, 1, 8), (8, 2, 3, 68), (9, 2, 4, 99), (10, 3, 1, 77), (11, 3, 2, 66), (12, 3, 3, 87), (13, 3, 4, 99), (14, 4, 1, 79), (15, 4, 2, 11), (16, 4, 3, 67), (17, 4, 4, 100), (18, 5, 1, 79), (19, 5, 2, 11), (20, 5, 3, 67), (21, 5, 4, 100), (22, 6, 1, 9), (23, 6, 2, 100), (24, 6, 3, 67), (25, 6, 4, 100), (26, 7, 1, 9), (27, 7, 2, 100), (28, 7, 3, 67), (29, 7, 4, 88), (30, 8, 1, 9), (31, 8, 2, 100), (32, 8, 3, 67), (33, 8, 4, 88), (34, 9, 1, 91), (35, 9, 2, 88), (36, 9, 3, 67), (37, 9, 4, 22), (38, 10, 1, 90), (39, 10, 2, 77), (40, 10, 3, 43), (41, 10, 4, 87), (42, 11, 1, 90), (43, 11, 2, 77), (44, 11, 3, 43), (45, 11, 4, 87), (46, 12, 1, 90), (47, 12, 2, 77), (48, 12, 3, 43), (49, 12, 4, 87), (52, 13, 3, 87); CREATE TABLE student( sid int(11) NOT NULL AUTO_INCREMENT, gender char(1) NOT NULL, class_id int(11) NOT NULL, sname varchar(32) NOT NULL, PRIMARY KEY (sid), KEY fk_class (class_id), CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO student VALUES (1, 'male', 1, 'Understand'), (2, 'female', 1, 'Steel Eggs'), (3, 'male', 1, 'Zhang San'), (4, 'male', 1, 'Zhang Yi'), (5, 'female', 1, 'Zhang Er'), (6, 'male', 1, 'Zhang Si'), (7, 'female', 2, 'Hammer'), (8, 'male', 2, 'Li San'), (9, 'male', 2, 'Li Yi'), (10, 'female', 2, 'Li Er'), (11, 'male', 2, 'Li Si'), (12, 'female', 3, 'Flowers like flowers'), (13, 'male', 3, 'Liu San'), (14, 'male', 3, 'Liu Yi'), (15, 'female', 3, 'Liu Er'), (16, 'male', 3, 'Liu Si'); CREATE TABLE teacher( tid int(11) NOT NULL AUTO_INCREMENT, tname varchar(32) NOT NULL, PRIMARY KEY (tid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO teacher VALUES (1, 'Mr. Zhang Lei'), (2, 'Mr. Li Ping'), (3, 'Teacher Liu Haiyan'), (4, 'Teacher Zhu Yunhai'), (5, 'Mr. Li Jie');
2. Title 1
1. Query the names of all the courses and the corresponding teachers
2. Query how many males and females are on the student list
3. Query the names of students whose physical results are equal to 100
4. Query names and average scores of students with average scores greater than 80%
5. Query the number, name, number of courses selected and total results of all students
6. Query the number of Mr. Li
7. Query the names of the students who did not report for Mr. Li Ping's class
8. Query the number of students whose physical courses are higher than the biological courses
9. Query the names of students who have not taken both physical and physical education courses
10. Query the names and classes of students with more than two registered disciplines (including two)
, query the names of students who have taken all courses
12. Query all the records of the results of the lessons taught by Mr. Li Ping
13. Query the number and title of the course selected by all the students
14. Query the number of elections for each course
15. Name and number of the student selected for one course
16. Query the results of all the students and rank them from high to low (the results are weighted)
17. Query names and average scores of students with average scores greater than 85
18. Query the names of students who have failed in biology and their corresponding bio-scores
19. Query the names of the students with the highest average scores in all the courses (not all courses) that have taken Ms. Li Ping's courses.
20. Query the names of the top two students in each course
21. Query different courses but get the same number, number and result
22. Query the names of the students who have not studied the teacher's course and the names of the selected courses;
23. Query the student numbers and names of all the students who have taken one or more courses with student number 1.
24. Teachers with the most classes Name of students with the highest single subject achievement
3. Answers
#1. Query the names of all courses and the corresponding teachers
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
#2. Query how many males and females are in the student list
SELECT
gender Gender,
count(1) Number of people
FROM
student
GROUP BY
gender;
#3, Query the names of students whose physical results are equal to 100
SELECT
student.sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
INNER JOIN course ON score.course_id = course.cid
WHERE
course.cname = 'Physics'
AND score.num = 100
);
#4. Names and average scores of students with average query results greater than 80%
SELECT
student.sname,
t1.avg_num
FROM
student
INNER JOIN (
SELECT
student_id,
avg(num) AS avg_num
FROM
score
GROUP BY
student_id
HAVING
avg(num) > 80
) AS t1 ON student.sid = t1.student_id;
#5. Query the number, name, number of courses selected, total score of all students (Note: for those who have not taken any courses)
SELECT
student.sid,
student.sname,
t1.course_num,
t1.total_num
FROM
student
LEFT JOIN (
SELECT
student_id,
COUNT(course_id) course_num,
sum(num) total_num
FROM
score
GROUP BY
student_id
) AS t1 ON student.sid = t1.student_id;
#6, Number of inquiries about Teacher Li
SELECT
count(tid)
FROM
teacher
WHERE
tname LIKE 'plum%';
#7. Query the names of the students who did not report for Teacher Li Ping's course (find out the students who signed up for Teacher Li Ping's course, and then take the opposite)
SELECT
student.sname
FROM
student
WHERE
sid NOT IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
course_id IN (
SELECT
course.cid
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = 'Mr. Li Ping'
)
);
#8. Query the numbers of students whose physical courses are higher than those of biology courses (get the physical and biological results tables separately, then join the tables)
SELECT
t1.student_id
FROM
(
SELECT
student_id,
num
FROM
score
WHERE
course_id = (
SELECT
cid
FROM
course
WHERE
cname = 'Physics'
)
) AS t1
INNER JOIN (
SELECT
student_id,
num
FROM
score
WHERE
course_id = (
SELECT
cid
FROM
course
WHERE
cname = 'Biology'
)
) AS t2 ON t1.student_id = t2.student_id
WHERE
t1.num > t2.num;
#9, Query the names of students who have not taken both physical and physical education courses (not taking both refers to taking one course, the idea is to get the student information table of physical + physical education courses, then count (course) =1) based on the group of students
SELECT
student.sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
cname = 'Physics'
OR cname = 'Sports'
)
GROUP BY
student_id
HAVING
COUNT(course_id) = 1
);
#10. Query the names and classes of students with more than two (including two) registered subjects (find out the table <60, then group the students and count the number of courses >=2)
SELECT
student.sname,
class.caption
FROM
student
INNER JOIN (
SELECT
student_id
FROM
score
WHERE
num < 60
GROUP BY
student_id
HAVING
count(course_id) >= 2
) AS t1
INNER JOIN class ON student.sid = t1.student_id
AND student.class_id = class.cid;
#11. Query the names of the students who have selected all the courses (first count the total number of courses from the coursetable, then group them according to student_id based on the score table, and then count the course data equal to the total number of courses)
SELECT
student.sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
COUNT(course_id) = (SELECT count(cid) FROM course)
);
#12. Query all the records of the results of the lessons taught by Mr. Li Ping
SELECT
*
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = 'Mr. Li Ping'
);
#13, Query the number and name of the course that all the students have taken (take all the students and then group the courses based on the score table to find out that count(student_id) equals the number of students)
SELECT
cid,
cname
FROM
course
WHERE
cid IN (
SELECT
course_id
FROM
score
GROUP BY
course_id
HAVING
COUNT(student_id) = (
SELECT
COUNT(sid)
FROM
student
)
);
#14, Query the number of times each course has been selected
SELECT
course_id,
COUNT(student_id)
FROM
score
GROUP BY
course_id;
#15. Query the names and numbers of the students who took one course
SELECT
sid,
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
COUNT(course_id) = 1
);
#16. Query the results of all students and rank them from highest to lowest (results are weighted)
SELECT DISTINCT
num
FROM
score
ORDER BY
num DESC;
#17, Name and Average Score of Students with Average Score > 85
SELECT
sname,
t1.avg_num
FROM
student
INNER JOIN (
SELECT
student_id,
avg(num) avg_num
FROM
score
GROUP BY
student_id
HAVING
AVG(num) > 85
) t1 ON student.sid = t1.student_id;
#18. Query the names of students who have failed in biology and their corresponding bio-scores
SELECT
sname Full name,
num Biological Performance
FROM
score
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN student ON score.student_id = student.sid
WHERE
course.cname = 'Biology'
AND score.num < 60;
#19. Query the name of the student with the highest average score in all the courses (not all courses) that have been taken by Mr. Li Ping
SELECT
sname
FROM
student
WHERE
sid = (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
course.cid
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = 'Mr. Li Ping'
)
GROUP BY
student_id
ORDER BY
AVG(num) DESC
LIMIT 1
);
#20, Query the names of the top two students in each course
#View information on each course sorted by score to provide a basis for finding the right or wrong for the following
SELECT
*
FROM
score
ORDER BY
course_id,
num DESC;
#Table 1: Find out the course_id for each course, with the highest score first_num
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id;
#Table 2: Remove the highest score and then group according to the course. The highest score you get is the second highest score
second_num
SELECT
score.course_id,
max(num) second_num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t ON score.course_id = t.course_id
WHERE
score.num < t.first_num
GROUP BY
course_id;
#Combine Tables 1 and 2 to get a table t3 containing course_id and the
first_num and second_num
SELECT
t1.course_id,
t1.first_num,
t2.second_num
FROM
(
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t1
INNER JOIN (
SELECT
score.course_id,
max(num) second_num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t ON score.course_id = t.course_id
WHERE
score.num < t.first_num
GROUP BY
course_id
) AS t2 ON t1.course_id = t2.course_id;
#Query the first two students (it is possible to be in the first or second place side by side)
SELECT
score.student_id,
t3.course_id,
t3.first_num,
t3.second_num
FROM
score
INNER JOIN (
SELECT
t1.course_id,
t1.first_num,
t2.second_num
FROM
(
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t1
INNER JOIN (
SELECT
score.course_id,
max(num) second_num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t ON score.course_id = t.course_id
WHERE
score.num < t.first_num
GROUP BY
course_id
) AS t2 ON t1.course_id = t2.course_id
) AS t3 ON score.course_id = t3.course_id
WHERE
score.num >= t3.second_num
AND score.num <= t3.first_num;
Significant Points to See After Sorting
SELECT
score.student_id,
t3.course_id,
t3.first_num,
t3.second_num
FROM
score
INNER JOIN (
SELECT
t1.course_id,
t1.first_num,
t2.second_num
FROM
(
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t1
INNER JOIN (
SELECT
score.course_id,
max(num) second_num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t ON score.course_id = t.course_id
WHERE
score.num < t.first_num
GROUP BY
course_id
) AS t2 ON t1.course_id = t2.course_id
) AS t3 ON score.course_id = t3.course_id
WHERE
score.num >= t3.second_num
AND score.num <= t3.first_num
ORDER BY
course_id;
#You can verify the correctness of the above query with the following commands
SELECT
*
FROM
score
ORDER BY
course_id,
num DESC;
- 21. Query different courses but get the same number, number, result
- 22. Query the names of the students who have not studied the teacher's course and the names of the selected courses;
- 23. Query the student numbers and names of all the students who have taken one or more courses with student number 1.
- 24. Teachers with the most classes Name of the student with the highest single subject achievement
Reproduction:https://www.cnblogs.com/12345huangchun/p/10123881.html
Subquery Summary
- Subquery If the query yields a field (single column), then use it as a condition after where
- Subquery If a query results in a table (with multiple columns), it is treated as a table (with an alias)
Three Paradigms
-
1NF First Specification
- Atomicity, so that columns cannot be split
- The first paradigm is also the most basic one. Table subsections in databases are single attributes and cannot be split
-
Second Norm 2NF
- Building on the first paradigm, the goal is to ensure that each column in the table is related to a primary key
- A table can only describe one thing
-
Third Norm 3NF
- Eliminate Delivery Dependency
- Table information, if deduced, should not be separately designed for storage
- Eliminate Delivery Dependency
Database Anti-Tri-Norm
- Anti-tri-paradigms only improve data read performance by increasing redundancy and duplication
- Waste storage space, save query time (space for time)
- Redundant Fields
- When designing a database, a field belongs to one table, and when it appears in another table or tables at the same time and is exactly what it meant to be, it is called a redundant field.
Summary:
- There are two options for creating a relational database design
- Follow the rules of the paradigm theory as much as possible, minimize redundant fields, and make your database design look exquisite, elegant and intoxicating
- Reasonably adding redundant fields as a lubricant, with fewer join s, makes the database perform better and faster