Buzz Code
Published in

Buzz Code

MySQL 8.0.22 | Select Subquery (Practice Questions 2)

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…

[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!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store