# 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:

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:

# 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

Tags: less SQL Navicat

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