MySQL 8.0.22 | Select Subquery (Practice Questions 2)

Published in

Buzz Code

2 min readDec 1, 2020

Hi guys! Welcome to another MySQL sesh with me. Continue from the last time let’s do some more practice on subquery! If you missed my last post, please click the link down below! You would really need it today…

MySQL | Select Subquery + Auto_increment

Subquery — The query inside of the query.

studentkim.medium.com

[Practice Questions]

//Copy the Queries down below and solve the questions.create database shop;
use shop;create table member(
no int primary key auto_increment,
name varchar(5) not null,
addr varchar(2),
gender varchar(1),
grade int default 1,
point int default 100);insert into member values
(default,'Nina','NY','w',2,300),
(default,'Tom','NY','m',1,200),
(default,'Amy','LA','w',1,150),
(default,'Jake','DC','m',2,100),
(default,'Lisa','LA','w',2,300),
(default,'Nicky','NY','w',3,400),
(default,'Tim','LA','m',4,300);

Q1. Return the average point of each grade, but only when it’s higher than the total average.

Just like the last time, I’ll divide this question into two select statement.

1.
select grade, avg(point) from member group by grade having (?????)2.
select avg(point) from member

Do you see which one should be the subquery here? That’s right. it’s the second one!

Answer :mysql> select grade, avg(point)
    -> from member
    -> group by grade
    -> having avg(point)>(select avg(point) from member);
+-------+------------+
| grade | avg(point) |
+-------+------------+
|     3 |   400.0000 |
|     4 |   300.0000 |
+-------+------------+
2 rows in set (0.00 sec)

This time I put the subquery in the having clause to give a certain condition on the group.

Q2. Select the people who has the lower point than ‘Tom’.

This one is pretty easier than other subquery practices. In this case we should get the point of ‘Tom’ first. And make it into subquery.

1. Subquery
select point from member where name ='Tom'2. Main query
select name, point from member where point ???

A-ha, Now we need to put the subquery into the main query!

Answer :mysql> select name, point
    -> from member
    -> where point<(select point from member where name='Tom');
+------+-------+
| name | point |
+------+-------+
| Amy  |   150 |
| Jake |   100 |
+------+-------+
2 rows in set (0.00 sec)

Just like this.

Well done today guys! In the next session I’ll be talking about the view. Thanks!