SQL statement exercises and Answers

Table structure

CREATE TABLE Data
SET NAMES UTF8;
SET foreign_key_checks = 0;

-- ----------------------------
--Table structure for ' class '
-- ----------------------------
DROP TABLE IF EXISTS ' class ';
CREATE TABLE ' class ' (
' CID ' int (one) not NULL auto_increment,
' caption ' varchar (+) not NULL,
PRIMARY KEY (' CID ')
) Engine=innodb auto_increment=5 DEFAULT Charset=utf8;

-- ----------------------------
--Records of ' class '
-- ----------------------------
BEGIN;
INSERT into ' class ' VALUES (' 1 ', ' three years '), (' 2 ', ' three-year-three '), (' 3 ', ' Two shifts a year '), (' 4 ', ' two-year-nine-shift ');
COMMIT;

-- ----------------------------
--Table structure for ' course '
-- ----------------------------
DROP TABLE IF EXISTS ' course ';
CREATE TABLE ' Course ' (
' CID ' int (one) not NULL auto_increment,
' CNAME ' varchar (+) not NULL,
' teacher_id ' int (one) is not NULL,
PRIMARY KEY (' CID '),
KEY ' Fk_course_teacher ' (' teacher_id '),
CONSTRAINT ' Fk_course_teacher ' FOREIGN KEY (' teacher_id ') REFERENCES ' teacher ' (' tid ')
) Engine=innodb auto_increment=5 DEFAULT Charset=utf8;

-- ----------------------------
--Records of ' course '
-- ----------------------------
BEGIN;
INSERT into ' Course ' VALUES (' 1 ', ' creature ', ' 1 '), (' 2 ', ' Physical ', ' 2 '), (' 3 ', ' Sport ', ' 3 '), (' 4 ', ' Art ', ' 2 ');
COMMIT;

-- ----------------------------
--Table structure for ' score '
-- ----------------------------
DROP TABLE IF EXISTS ' score ';
CREATE TABLE ' score ' (
' Sid ' Int (one) not NULL auto_increment,
' student_id ' int (one) is not NULL,
' course_id ' int (one) is not NULL,
' num ' int (one) is 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 auto_increment=53 DEFAULT Charset=utf8;

-- ----------------------------
--Records of ' score '
-- ----------------------------
BEGIN;
    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 ', ' 6 7 '), (' 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 ');
COMMIT;

-- ----------------------------
--Table structure for ' student '
-- ----------------------------
DROP TABLE IF EXISTS ' student ';
CREATE TABLE ' Student ' (
' Sid ' Int (one) not NULL auto_increment,
' Gender ' char (1) Not NULL,
' class_id ' int (one) is not NULL,
' sname ' varchar (+) not NULL,
PRIMARY KEY (' Sid '),
KEY ' Fk_class ' (' class_id '),
CONSTRAINT ' Fk_class ' FOREIGN KEY (' class_id ') REFERENCES ' class ' (' CID ')
) Engine=innodb auto_increment=17 DEFAULT Charset=utf8;

-- ----------------------------
--Records of ' student '
-- ----------------------------
BEGIN;
INSERT into ' student ' VALUES (' 1 ', ' Male ', ' 1 ', ' Understanding '), (' 2 ', ' Female ', ' 1 ', ' Steel Egg '), (' 3 ', ' Male ', ' 1 ', ' Zhang San '), (' 4 ', ' Male ', ' 1 ', ' Zhang Yi '), ( ' 5 ', ' Female ', ' 1 ', ' Zhang Yi '), (' 6 ', ' Male ', ' 1 ', ' Zhang Si '), (' 7 ', ' Female ', ' 2 ', ' Hammer '), (' 8 ', ' Male ', ' 2 ', ' lie Triple '), (' 9 ', ' Male ', ' 2 ', ' Lee '), (' 10 ', ' Female ', ' 2 ', ' Li Er '), (' 11 ', ' Male ', ' 2 ', ' John Doe '), (' 12 ', ' Female ', ' 3 ', ' Like Flower '), (' 13 ', ' Male ', ' 3 ', ' Liu San '), (' 14 ', ' Male ', ' 3 ', ' Liu Yi '), (' 15 ', ' female ') , ' 3 ', ' Ryuji '), (' 16 ', ' Male ', ' 3 ', ' Liusi ');
COMMIT;

-- ----------------------------
--Table structure for ' teacher '
-- ----------------------------
DROP TABLE IF EXISTS ' teacher ';
CREATE TABLE ' Teacher ' (
' Tid ' int (one) not NULL auto_increment,
' Tname ' varchar (+) not NULL,
PRIMARY KEY (' tid ')
) Engine=innodb auto_increment=6 DEFAULT Charset=utf8;

-- ----------------------------
--Records of ' teacher '
-- ----------------------------
BEGIN;
INSERT into ' teacher ' VALUES (' 1 ', ' Zhang Lei '), (' 2 ', ' Miss Li '), (' 3 ', ' Liu Haiyan Teacher '), (' 4 ', ' Zhu Yunhai teacher '), (' 5 ', ' Li Jie Teacher ');
COMMIT;

SET foreign_key_checks = 1;


Temporary tables, linked list queries
--Query the number of all students who have higher grades in "biology" than the "physics" course;
SELECT a.student_id from (
(SELECT Student_id,num from score
Left JOIN course on Score.course_id=course.cid
Where cname= ' creature ') as A
Left JOIN
(SELECT Student_id,num from score left joins course on Score.course_id=course.cid
where cname= ' physical ') as B on a.student_id=b.student_id)
WHERE a.num>b.num;

--Check all students ' student number, name, course number, total
SELECT A.student_id,student.sname,a.choice_num,a.sum_score from (
SELECT Student_id,count (1) as Choice_num, SUM (num) as Sum_score from score GROUP by student_id) as A
Left JOIN
Student on A.student_id=student.sid

--Query The course number "2" of the grade than the course number "1" of all students of the school number, name
SELECT student.sid,student.sname from student where Student.sid in (
SELECT a.student_id from (
(SELECT Student_id,num from score left JOIN course on score.course_id=course.cid where cid= ' 2 ') A
Left JOIN
(SELECT Student_id,num from score left JOIN course on score.course_id=course.cid where cid= ' 1 ') B
On a.student_id=b.student_id) WHERE a.num>b.num);

--Query and the "2" number of students to study the course of the same other student number and name

SELECT student_id from (
SELECT * FROM score where student_id= (
SELECT student_id from score GROUP by STUDENT_ID have count (1) = (
SELECT count (1) as Count_course from score GROUP by student_id have student_id=2) and student_id!=2) and course_id in
(select course_id from score WHERE student_id=2)) As A GROUP by STUDENT_ID have count (*) =
(SELECT count (1) as Count_course from score GROUP by student_id have student_id=2)

--Query the number, name and average scores of all students with average scores greater than 85;
SELECT student_id,avg (num) as Avgnum,student.sname from score left joins student on Score.student_id=student.sid
GROUP by student_id have avgnum > 85

--Query the names and scores of students whose course name is "physical" with a score below 60;
SELECT Student.sname,score.num from course left join score on Score.course_id=course.cid left join student on Score.studen T_id=student.sid
WHERE course.cname= ' physical ' and Score.num < 60

--to inquire about the student's number and name of the course Number 3 and the course score above 80;
SELECT Student_id,student.sname from course left join score on Score.course_id=course.cid left join student on Score.stude Nt_id=student.sid
WHERE cid=3 and Score.num>80

--to inquire about the students with the highest performance in the courses offered by "Li Ping", their names and their achievements;
SELECT A.num,a.sname,a.cname from (
SELECT score.num,student.sname,course.cname from teacher left join course on Course.teacher_id=teacher.tid left join Scor E on course.cid=score.course_id
Left JOIN student on Score.student_id=student.sid
WHERE teacher.tname= ' Miss Li ' ORDER by num desc] as A GROUP by CNAME

--The number of students who have chosen the course
SELECT Count (*) as person from (
Select Student.sid from student left JOIN score as score1 on Score1.student_id=student.sid WHERE student.sid in (SELECT STUDENT_ID from score GROUP by student_id)
GROUP by Student.sid) as A

--inquire about the names of students who have not studied any of the courses taught by "Li Ping";
SELECT Student_id,sname from score left JOIN student
On Score.student_id=student.sid
WHERE score.student_id Not IN (
SELECT DISTINCT s1.student_id from teacher left joins course on Course.teacher_id=teacher.tid
Left JOIN score as S1 on course.cid=s1.course_id
WHERE tname= ' Mr. Li Ping ')



Conditional query Statements
--inquire about the student's number and name of the students who have not studied "Li Ping" class
SELECT student.sid,student.sname from student where Student.sid not in (
SELECT student_id from
(SELECT CID from course left joins teacher on Course.teacher_id=teacher.tid
Where tname= ' Miss Li ') as A left JOIN score on Score.course_id=a.cid GROUP by student_id)

--Inquire about the students ' number and name of all the classes taught by "Li Ping"
SELECT student.sid,student.sname from student where Student.sid in (
SELECT student_id from
(SELECT CID from course left joins teacher on Course.teacher_id=teacher.tid
Where tname= ' Miss Li ') as A left JOIN score on Score.course_id=a.cid GROUP by student_id)

--query for at least one class with the student number "1" students learn the same student number and name
SELECT student.sid,student.sname from student where Sid in (
SELECT student_id from score where course_id in (
SELECT course_id from score where student_id = ' 1 ') GROUP by student_id have count (course_id) >1);

--Check the student's number, course number, and student score for different courses but with the same results
SELECT S1.student_id,s1.course_id,s1.num from score as s1,score as S2 WHERE s1.num=s2.num and s1.student_id=s2.student_id and s1.course_id!=s2.course_id

Group queries
--Search for students with average scores greater than 60 points and average scores
SELECT student_id,avg (num) as number from score GROUP by student_id have number > 60;

--Inquire about "1" and also learn the number and name of the students in the "2" course;
SELECT Student_id,count (1) as Count_course from score where course_id in (All) GROUP by student_id have count_course> 1;

--Check the number of students who have a course score of less than 60 points, name
SELECT sid,sname from student where Sid in (
SELECT student_id from score WHERE num < GROUP by student_id)

--inquire about the student's number and name of the students who did not learn all the classes
SELECT student.sid,student.sname from student where Student.sid in (
SELECT student_id from score GROUP by student_id have count (course_id) < (
SELECT count (1) from course);

--query for at least one class with the student number "1" students learn the same student number and name
SELECT student.sid,student.sname from student where Sid in (
SELECT student_id from score where course_id in (
SELECT course_id from score where student_id = ' 1 ') GROUP by student_id have count (course_id) >1) and Sid!=1;

--Query the highest and lowest scores of each section: shown in the following form: Course ID, highest score, lowest score;
Select Course_id,max (num) as the highest score, min (num) as Min. from score GROUP by course_id;

--Query the highest and lowest scores of each section: shown in the following form: Course ID, highest score, lowest score;
Select Course_id,max (num) as the highest score, min (num) as Min. from score GROUP by course_id;

-average course score from high to low display (real classroom teacher)
SELECT course_id,teacher_id,avg (num) as Avgnum from course left JOIN score
On Score.course_id=course.cid
GROUP by course_id ORDER by avgnum Desc

--Query the number of students who are enrolled in each course
SELECT Course_id,count (*) as person from score GROUP by course_id

--Find out the number and name of all the students who have only one course of study
SELECT * FROM (
SELECT Student_id,course_id,count (*) as Course_count from score GROUP by student_id) as A
Left JOIN student on student.sid=a.student_id WHERE a.course_count=1

--inquiring into the number of boys and girls;
Select Gender,count (*) as person from student GROUP by gender

--Check the names of students with the same name and count the number
SELECT Sname,count (*) as person from student GROUP by Student.sname have person>1

--Retrieving the student number of at least two elective courses;
SELECT Student_id,count (*) as Count_course from score GROUP by student_id have count_course >= 2

--Query the course number and course name of the courses that all students take;
Select Course_id,count (*) as person from score GROUP by course_id have person= (SELECT count (1) from student)

--The minimum number of student studies and course numbers for inquiry studies
SELECT s1.student_id,s1.course_id from score as S1
GROUP by s1.student_id ORDER by Count (1) Limit 1

--to inquire the students of two or more failed courses, and their average scores;
SELECT student_id,course_id,avg (num) from score where num < GROUP by student_id have count (1) >= 2

---Search "4" course score is less than 60, according to the Grade descending class number;
Select student_id from score where course_id=4 and num < ORDER by num DESC


Fuzzy matching
--the number of teachers who queried the surname "Li"
SELECT * from teacher where tname like ' li% '

--a list of students who are queried for their surname "Zhang";
SELECT * FROM student WHERE sname like ' Zhang% ';


Sort
--from low to high and the percentage of passing rates from high to low according to the average grades of each section
Select COURSE_ID, AVG (num) as avgnum,sum (case is score.num > then 1 else 0 END)/count (1) *100 as percent from score Group BY course_id ORDER by Avgnum asc,percent desc;

--Check the record of the top three records of each section: (regardless of the performance of the situation)
SELECT Score1.sid,score1.course_id,res.first_num,res.second_num,res.third_num from score as Score1 left JOIN (
SELECT SID,
(SELECT num from score as Score2 WHERE score2.course_id=score3.course_id ORDER by num desc LIMIT 0,1) as First_num,
(SELECT num from score as Score2 WHERE score2.course_id=score3.course_id ORDER by num desc LIMIT 3,1) as Second_num,
(SELECT num from score as Score2 WHERE score2.course_id=score3.course_id ORDER by num desc LIMIT 4,1) as Third_num
From score as Score3
) as Res
On Score1.sid=res.sid GROUP by score1.course_id;

--the average score of each course is queried, and the results are arranged in ascending order of average grade and the average grade is the same, descending by the course number.
SELECT * FROM (
SELECT course_id,avg (num) as Avgnum from score GROUP by course_id) as A ORDER by A.avgnum asc,a.course_id DESC

--Search for the top two of each course score;
Select score1.course_id, res.first_num,res.second_num from score as Score1 left JOIN
(SELECT Sid,
(select num from score as Score2 WHERE score3.course_id=score2.course_id ORDER by score2.num desc LIMIT 0,1) as First_num,
(select num from score as Score2 WHERE score3.course_id=score2.course_id ORDER by score2.num desc LIMIT 3,1) as Second_num
From score as Score3
) as Res
On Res.sid=score1.sid
GROUP by score1.course_id



Case and then else end
--from low to high and the percentage of passing rates from high to low according to the average grades of each section
Select COURSE_ID, AVG (num) as avgnum,sum (case is score.num > then 1 else 0 END)/count (1) *100 as percent from score Group BY course_id ORDER by Avgnum asc,percent desc;

Delete
--delete "2" students "1" of the results of the course;
Delete from score where student_id=2 and course_id=1

SQL statement exercises and Answers