SQL written test classic 50 questions and answer analysis (questions 1-10)

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:

Published 4 original articles, won praise 1 and visited 317
Private letter follow

Tags: less SQL Navicat

Posted on Mon, 16 Mar 2020 01:46:09 -0400 by porto88

Hot Tags

©2020 Programmer Help