1. Sort after finding

subject ： Now the operator wants to take out the user age in the user information table , Please take out the corresponding data , And sort them in ascending order of age .

Create table statement ：

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,' Peking University, ',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',23,' Fudan University ',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,' Peking University, ',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,' Zhejiang University ',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,' Shandong University ',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,' Beijing Normal University ',3.3);

The answer is ：

SELECT device_id , age FROM user_profile  ORDER by age ASC 

2. Sort multiple columns after searching

subject ： Now the operator wants to take out the age and... In the user information table gpa data , And first according to gpa Ascending sort , Then sort the output in ascending order of age , Please take out the corresponding data .

Create table statement ：

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,' Peking University, ',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',23,' Fudan University ',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,' Peking University, ',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,' Zhejiang University ',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,' Shandong University ',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,' Beijing Normal University ',3.3);

The answer is ：

SELECT device_id, gpa, age 
FROM user_profile 
ORDER BY gpa, age;

3. Sort in descending order after finding

subject ： Now the operator wants to retrieve the corresponding data in the user information table , And first according to gpa、 Sort the output in descending order of age , Please take out the corresponding data .

Create table statement ：

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,' Peking University, ',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',23,' Fudan University ',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,' Peking University, ',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,' Zhejiang University ',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,' Shandong University ',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,' Beijing Normal University ',3.3);

The answer is ：

select device_id,gpa,age
from user_profile
order by gpa desc,age desc;

4. Find the student information of the school is Peking University

subject ： Now the operator wants to screen out all Peking University Students for user research , Please take the qualified data from the user information table , The result is returned to the device id And school .

Create table statement ：

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32)  NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,' Peking University, ','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,' Fudan University ','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,' Peking University, ','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,' Zhejiang University ','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,' Shandong University ','Shandong');

The answer is ：

SELECT device_id,university
FROM user_profile
WHERE university=" Peking University, "

5. Look for ages greater than 24 Years old user information

subject ： Now operations want to target 24 Users over the age of , Please take out the equipment that meets the conditions ID、 Gender 、 Age 、 School .

Create table statement ：

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32)  NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,' Peking University, ','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,' Fudan University ','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,' Peking University, ','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,' Zhejiang University ','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,' Shandong University ','Shandong');

The answer is ：

SELECT device_id,gender,age,university
FROM user_profile
WHERE age>24;

6. How to make question brushing more efficient ?

Recently, many kids who have learned the basics asked me how to improve my programming level ？ What should I do after learning the basics ？ Mingming learned a lot , Do the project but don't know how to get started , In fact, this is too little practice , Only pay attention to learning , But ignore the question , Only continuous practice can improve and consolidate programming thinking and ability ！

Link address ： Cattle from | SQL Brush topic , Stop talking nonsense and speed up ！！！