Preface
Recently, bloggers are preparing for written examinations and interviews for data analysis posts, and the focus of which is SQL. I heard that after practicing these 50 questions, I'm not afraid to tear SQL on the spot, so I'll practice quickly! Heroes, you will not be afraid of anything after practice~
Environmental Science:
Navicat Premium 12
Explain:
This exercise is topic 1 -: 10. It's very simple, and novices don't have to be afraid~
Topics 11-20:
Title 21-30:
Topics 31-40:
Title 41-50:
Article directory
Create table
Table description
There are four tables created this time:
- Student: including four fields: student number, student name, date of birth, gender
- Teacher table: including two fields: teacher number and teacher name
- Grade table (sc): including three fields of student number, course number and grade
- Course: including three fields: course number, course name and teacher number
Table creation
# Create student table create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10)); insert into Student values('01' , 'Zhao Lei' , '1990-01-01' , 'male'); insert into Student values('02' , 'Qian Dian' , '1990-12-21' , 'male'); insert into Student values('03' , 'Sun Feng' , '1990-05-20' , 'male'); insert into Student values('04' , 'Li Yun' , '1990-08-06' , 'male'); insert into Student values('05' , 'Zhou Mei' , '1991-12-01' , 'female'); insert into Student values('06' , 'Wu Lan' , '1992-03-01' , 'female'); insert into Student values('07' , 'Zheng Zhu' , '1989-07-01' , 'female'); insert into Student values('08' , 'Wang Ju' , '1990-01-20' , 'female'); # Create Curriculum create table Course(cid varchar(10),cname varchar(10),tid varchar(10)); insert into Course values('01' , 'Chinese' , '02'); insert into Course values('02' , 'Mathematics' , '01'); insert into Course values('03' , 'English?' , '03'); # Create teacher table create table Teacher(tid varchar(10),tname varchar(10)); insert into Teacher values('01' , 'Zhang San'); insert into Teacher values('02' , 'Li Si'); insert into Teacher values('03' , 'Wang Wu'); # Create score sheet create table SC(sid varchar(10),cid varchar(10),score decimal(18,1)); insert into SC values('01' , '01' , 80); insert into SC values('01' , '02' , 90); insert into SC values('01' , '03' , 99); insert into SC values('02' , '01' , 70); insert into SC values('02' , '02' , 60); insert into SC values('02' , '03' , 80); insert into SC values('03' , '01' , 80); insert into SC values('03' , '02' , 80); insert into SC values('03' , '03' , 80); insert into SC values('04' , '01' , 50); insert into SC values('04' , '02' , 30); insert into SC values('04' , '03' , 20); insert into SC values('05' , '01' , 76); insert into SC values('05' , '02' , 87); insert into SC values('06' , '01' , 31); insert into SC values('06' , '03' , 34); insert into SC values('07' , '02' , 89); insert into SC values('07' , '03' , 98);
Table display
The student table is as follows:
The teacher table is as follows:
The results are as follows:
The curriculum is as follows:
subject
Topic 1
Query the student numbers of all students whose grades of "01" are higher than those of "02";
SELECT t1.sid,t1.score,t2.score FROM (select sid,score from sc where cid = '01') as t1 JOIN (select sid,score from sc where cid = '02') as t2 ON t1.sid = t2.sid and t1.score>t2.score
matters needing attention:
- Find out the students who took courses 01 and 02 respectively, and then join
Operation result:
Topic 2
Inquire the student number and average score of students whose average score is more than 60;
SELECT sid,AVG(score) AS AVG FROM sc GROUP BY sid having avg(score)>60
matters needing attention:
- Use group by and aggregate function AVG to
- After using group by, you must aggregate. Any one of max/min/avg/sum/count is OK. If you do not aggregate, the final result is the first one in the group
Operation result:
Topic 3
Query the student number, name, number of selected courses and total scores of all students
SELECT t1.sid,t1.sname,t2.cnt,t2.total_score FROM (SELECT sid,sname FROM student GROUP BY sid) AS t1 LEFT JOIN (SELECT sid,COUNT(*) AS cnt,SUM(score) AS total_score FROM sc GROUP BY sid) AS t2 ON t1.sid = t2.sid
Operation result:
Topic 4
Check the number of teachers whose surname is "Li";
SELECT COUNT( DISTINCT tid ) AS cnt FROM teacher WHERE tname LIKE 'Plum%'
matters needing attention:
- Wildcard%: indicates that it contains one or more characters
- Wildcard: indicates that it contains one character
- Wildcard [charlist]: indicates to include any single character in the list
- Wildcard [^ charlist] or [! Charlist]: indicates that it does not contain any single character in the list
- like: search for a pattern
Operation result:
Topic 5
Inquire the student number and name of the students who have not learned "Zhang San" teacher's class;
SELECT DISTINCT sid,sname FROM student WHERE sid NOT IN (SELECT sid FROM sc LEFT JOIN course AS c ON sc.cid = c.cid LEFT JOIN teacher AS t ON c.tid = t.tid WHERE t.tname = 'Zhang San')
matters needing attention:
- The IN operator allows us to specify multiple values IN the WHERE clause
Operation result:
Topic 6
Inquire the student number and name of the student who has learned the course No. "01" and has also learned the course No. "02" (analogy topic 1)
SELECT DISTINCT sid,sname FROM student WHERE sid IN ( SELECT DISTINCT t1.sid FROM (SELECT sid,score FROM sc WHERE cid = '01') as t1 JOIN (SELECT sid,score FROM sc WHERE cid = '02') as t2 ON t1.sid = t2.sid )
matters needing attention:
- First, find out the students who took courses 01 and 02 respectively, and then join
- Then, join the above results with the student table to get the student name
Operation result:
Topic 7
Inquire the student number and name of the students who have learned the lesson taught by Mr. Zhang San; (analogy topic 5)
SELECT DISTINCT sid,sname FROM student WHERE sid IN (SELECT sid FROM sc LEFT JOIN course AS c ON sc.cid = c.cid LEFT JOIN teacher AS t ON c.tid = t.tid WHERE t.tname = 'Zhang San')
Operation result:
Topic 8
Inquire the student number and name of all students whose score of course No. "01" is lower than that of course No. "02" (analogy questions 1 and 6)
SELECT DISTINCT s.sid,s.sname FROM student AS s JOIN( SELECT t1.sid FROM (SELECT sid,score FROM sc WHERE cid = '01') as t1 JOIN (SELECT sid,score FROM sc WHERE cid = '02') as t2 ON t1.sid = t2.sid AND t1.score < t2.score ) t ON s.sid = t.sid
Operation result:
Topic 9
Query the student number and name of all students whose course score is less than 60;
SELECT DISTINCT s.sid,s.sname FROM student AS s JOIN( SELECT sid FROM sc GROUP BY sid HAVING MAX(score) < 60 )AS t ON s.sid = t.sid
matters needing attention:
- Scores of all courses are less than 60, which means that the maximum scores are less than 60
Operation result:
Topic 10
Inquire the student number and name of the students who have not learned all the courses;
SELECT s.sid,s.sname FROM student AS s LEFT JOIN sc on s.sid = sc.sid GROUP BY s.sid HAVING COUNT(sc.cid)<3
matters needing attention:
- First, we need to combine the student table and the score table to get the course selection information of all students, and then count them in groups;
- Just look at the score sheet, and Miss Wang Ju who didn't choose a course~
Operation result: