User information table: user_profile

Device ID (device_id)

gender

age

School (university)

active_days_within_30 field

Post quantity field (question_cnt)

Answer quantity field (answer_cnt)

id	device_id	gender	age	university	gpa	active_days_within_30	question_cnt	answer_cnt
1	2138	male	21	Peking University	3.4	7	2	12
2	3214	male	NULL	Fudan University	4	15	5	25
3	6543	female	20	Peking University	3.2	12	3	30
4	2315	female	23	Zhejiang University	3.6	5	1	2
5	5432	male	25	Shandong University	3.8	20	15	70
6	2131	male	28	Shandong University	3.3	15	7	13
7	4321	male	28	Fudan University	3.6	9	6	52

List of exercises in question bank: question_practice_detail

Device ID (device_id)

question_id

The answer to the question is right or wrong (result)

id	device_id	question_id	result	date
1	2138	111	wrong	2021-05-03
2	3214	112	wrong	2021-05-09
3	3214	113	wrong	2021-06-15
4	6543	111	right	2021-08-13
5	2315	115	right	2021-08-13
6	2315	116	right	2021-08-14
7	2315	117	wrong	2021-08-15

Table: question_detail

question_id

Difficulty_level

id	question_id	difficult_level
1	111	hard
2	112	medium
3	113	easy
4	115	easy
5	116	medium
6	117	easy

Example: user_submit

User profile

Blog address (blog_url)

device_id	profile	blog_url
2138	180cm,75kg,27,male	http:/url/bigboy777
3214	165cm,45kg,26,female	http:/url/kittycc
6543	178cm,65kg,25,male	http:/url/tiger
4321	171cm,55kg,23,female	http:/url/uhksd
2131	168cm,45kg,22,female	http:/urlsydney

01. Basic query

Basic query

SQL1 query multiple columns

Title: view the data of user's device id, gender, age and school.

SELECT device_id, gender, age, university FROM user_profile

SQL2 query all columns

Title: view all data in the user information table.

SELECT id, device_id, gender, age, university, province FROM user_profile

SQL3 query result de duplication

Title: view the weight removal data from which schools the user comes from.

SELECT DISTINCT university FROM user_profile

SQL4 query results limit the number of returned rows

Title: view the detailed equipment ID data of the first two users.

SELECT device_id FROM user_profile WHERE id BETWEEN 1 AND 2

SQL5 renames the column after query

Title: view the detailed equipment ID data of the first two users, and change the column name to 'user'_ infos_ example'.

SELECT device_id as user_infos_example FROM user_profile LIMIT 2

02. Condition query

Basic sorting

SQL36 sort after find

Title: take out the user's age in the user information table and sort it in ascending order of age.

SELECT device_id,age FROM user_profile ORDER BY age

SQL37 multi column sorting after lookup

Title: take out the age and gpa data in the user information table, sort them in ascending order of gpa, and then sort them in ascending order of age for output.

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

SQL38 - sort in descending order after finding

Title: take out the corresponding data in the user information table and output it in descending order according to gpa and age.

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

Base operator

SQL6 ， find the information of students whose school is Peking University

Title: screen out all student users of Peking University, take out the qualified data, and return the results to the device id and school.

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

SQL7 # find user information older than 24

Title: take out the data of user equipment ID, gender, age and school over the age of 24.

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

SQL8 - find user information of a certain age group

Title: take out the data of user equipment ID, gender and age aged 20 and over and 23 and under.

SELECT device_id, gender, age FROM user_profile WHERE age BETWEEN 20 AND 23

SQL9 # find user information except Fudan University

Title: to view the details of all users except Fudan University, please take out the corresponding data.

SELECT device_id, gender, age, university
FROM user_profile WHERE NOT university = 'Fudan University'

SQL10 # practice of filtering null values with where

Title: get the device ID, gender, age and school information of all users whose age value is not empty.

SELECT device_id, gender, age, university FROM user_profile WHERE age != 0

Advanced operator

SQL11 advanced operator exercise (1)

Title: take out male user data with GPA above 3.5 (excluding 3.5).

SELECT device_id, gender, age, university, gpa
FROM user_profile WHERE gender = 'male' AND gpa > 3.5

SQL12 advanced operator exercise (2)

Title: take out the user data of Peking University or GPA above 3.7 (excluding 3.7).

SELECT device_id, gender, age, university, gpa
FROM user_profile WHERE university = 'Peking University' OR gpa > 3.7

SQL13 Where in and Not in

Title: take out the data of students from Peking University, Fudan University and Shanda University.

SELECT device_id, gender, age, university, gpa
FROM user_profile WHERE university IN ('Peking University', 'Fudan University', 'Shandong University')

Mixed application of SQL14} operators

Title: take out the corresponding data of Shandong University users with gpa above 3.5 (excluding 3.5) or Fudan University Students with gpa above 3.8 (excluding 3.8).

SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE (university = 'Shandong University' AND gpa > 3.5) OR (university = 'Fudan University' AND gpa > 3.8)

SQL15 view users with Beijing in the school name

Title: take out the information of users with Beijing in all universities.

SELECT device_id, age, university
FROM user_profile
WHERE university LIKE '%Beijing%'

03. Advanced query

Calculation function

SQL16 - find the highest GPA value

Title: take out the corresponding data of the highest gpa of Fudan University students.

SELECT MAX(gpa) as gpa
FROM user_profile
WHERE university = 'Fudan University'

SQL17 calculate the number of boys and average GPA

Title: take out the number of male users and the corresponding data of their average gpa.

SELECT COUNT(gender) as male_num, AVG(gpa) as avg_gpa
FROM user_profile
WHERE gender = 'male'

Grouping query

SQL18 group calculation exercise

Title: take out the number of users of each gender in each school, the average number of active days and the average number of posts in 30 days.

SELECT gender, university, count(device_id), avg(active_days_within_30), avg(question_cnt)
FROM user_profile GROUP BY gender, university

SQL19 group filtering exercise

Title: take out schools with an average number of Posts less than 5 or schools with an average number of replies less than 20.

SELECT university, avg(question_cnt) as avg_question_cnt, avg(answer_cnt) as avg_answer_cnt
FROM user_profile GROUP BY university
HAVING AVG(question_cnt) < 5 OR AVG(answer_cnt) < 20;

SQL20 group sorting exercise

Title: view the average posting status of users from different universities and arrange the data in ascending order according to the average posting status.

SELECT university, AVG(question_cnt) AS avg_question_cnt
FROM user_profile
GROUP BY university
ORDER BY avg_question_cnt

04. Multi table query

Subquery

SQL21 Zhejiang University user question answer

Title: take out the corresponding data of the answer details of all user questions from Zhejiang University.

SELECT p.device_id, q.question_id, q.result
FROM user_profile p, question_practice_detail q
WHERE p.university = 'Zhejiang University' AND p.device_id = q.device_id
ORDER BY q.question_id

Link query

SQL22: count the average number of users who have answered questions in each school

Title: take out the data of the average number of users who have answered questions in each school.

SELECT u.university, count(u.university) / count(DISTINCT q.device_id) AS avg_answer_cnt
FROM user_profile u, question_practice_detail q
WHERE u.device_id=q.device_id
GROUP BY u.university

SQL23 counts the average number of questions brushed by users of each difficulty in each school

Title: take out the corresponding data of the average answer quantity of users from different schools and different difficulties.

SELECT university, difficult_level, COUNT(q.question_id) / COUNT(DISTINCT q.device_id) avg_answer_cnt
FROM question_practice_detail q, user_profile u, question_detail d
WHERE q.device_id=u.device_id AND q.question_id=d.question_id
GROUP BY university, difficult_level

SQL24 counts the average number of questions brushed by each user

Title: view the corresponding data of the average number of questions answered by users of Shandong University under different difficulties.

SELECT university, difficult_level, COUNT(q.question_id) / COUNT(DISTINCT q.device_id) as avg_answer_cnt
FROM user_profile as u
INNER JOIN question_practice_detail as q
ON u.device_id = q.device_id
INNER JOIN question_detail as d
ON q.question_id = d.question_id
WHERE university = 'Shandong University'
GROUP BY difficult_level

Combined query

SQL25 - find information about Shandong University or male gender

Title: take out the device of Shandong University or male user_ ID, gender, age and gpa do not duplicate data.

SELECT device_id,gender,age,gpa
FROM user_profile
WHERE university='Shandong University'
UNION ALL
SELECT device_id,gender,age,gpa
FROM user_profile
WHERE gender='male'

05 ， must know common functions

Conditional function

SQL26 # calculate the number of users over and under the age of 25

Title: view the number of users under the age of 25 and 25 and over respectively. (if age is null, it is also recorded as under 25 years old)

SELECT if(age >= 25, '25 Years old and over', '25 Under years old') as age_cut, COUNT(device_id) as number
FROM user_profile
GROUP BY age_cut

SQL27 view user details of different age groups

Title: check the details of users under the age of 20, 20-24, 25 and above. (Note: if age is blank, please return other.)

SELECT device_id, gender, CASE WHEN age < 20 THEN '20 Under years old'
WHEN age >= 20 AND age <= 24 THEN '20-24 year'
WHEN age >= 25 THEN '25 Years old and over'
WHEN age is null THEN 'other'
END as age_cut
FROM user_profile

Date function

SQL28 ― calculate the number of exercises per day in August

Title: take out the corresponding data of the number of user practice questions per day in August 2021.

SELECT day(date) as day, COUNT(question_id) as question_cnt
FROM question_practice_detail
WHERE date LIKE '2021-08-%'
GROUP BY date;

SQL29 calculate the average next day retention rate of users

Title: take out the corresponding data of the average probability that users will brush again the next day after one day.

SELECT COUNT(DISTINCT q2.device_id, q2.date) / COUNT(DISTINCT q1.device_id, q1.date) as avg_ret
FROM question_practice_detail q1
LEFT JOIN question_practice_detail q2
ON q1.device_id = q2.device_id and DATEDIFF(q1.date, q2.date) = 1

Text function

SQL30 - count the number of people of each gender

Title: take out the corresponding results of how many contestants there are for users of each gender.

SELECT substring_index(profile, ',', -1) as gender, COUNT(*) AS number
FROM user_submit
GROUP BY gender

SQL32 cut-off age

Title: take out the corresponding results of how many contestants there are for users of each age.

SELECT age, COUNT(*) number
FROM (SELECT SUBSTR(PROFILE, 12, 2) age
FROM user_submit) ages
GROUP BY age

SQL31 extract the user name in the blog URL

Title: blog of users applying to participate in the competition_ The string after the url character in the url field is the user name of the user's personal blog. Take out the user's personal blog and record the user field as a new field separately.

SELECT device_id , substring_index(blog_url, '/', -1) as user_name
FROM user_submit

Window function

SQL33 find out the students with the lowest GPA in each school

Title: take out the minimum gpa of each school.

SELECT device_id, university, gpa
FROM (
    SELECT *, ROW_NUMBER() over (PARTITION BY university ORDER BY gpa) AS rn
    FROM user_profile
) AS temp
WHERE temp.rn = 1

06. Comprehensive exercises

Comprehensive practice

SQL34 statistics of Fudan users' practice in August

Title: take out the total number of questions practiced by each user of Fudan University in August and the number of questions answered correctly. For users who have not practiced, the answer number result returns 0.

SELECT
t1.device_id,
t1.university,
SUM(CASE WHEN t2.result IS NOT null THEN 1 ELSE 0 END),
SUM(CASE WHEN t2.result = 'right' THEN 1 ELSE 0 END)
FROM user_profile t1
LEFT JOIN question_practice_detail t2 
ON t1.device_id = t2.device_id AND MONTH(t2.date) = '08'
WHERE t1.university = 'Fudan University'
GROUP BY t1.device_id

SQL35 accuracy of different difficult questions in Zhejiang University

Title: take out the correct rate of answers given by users of Zhejiang University under different difficult questions, and output them in ascending order of accuracy rate.

SELECT d.difficult_level, 
(SUM(CASE WHEN q.result='right' THEN 1 ELSE 0 END) / COUNT(q.result)) correct_rated
FROM user_profile u, question_practice_detail q, question_detail d
WHERE u.university = 'Zhejiang University' AND u.device_id = q.device_id AND q.question_id = d.question_id
GROUP BY d.difficult_level
ORDER BY correct_rated ASC

Total number of exercises in August of SQL39-21

Title: take out the corresponding results of the total number of users who have practiced the title and the total number of times they have practiced the title in August 2021.

SELECT COUNT(DISTINCT device_id), COUNT(question_id)
FROM question_practice_detail
WHERE date LIKE '2021-08%'