SQL3 query result de duplication

//Method 1: duplicate the distinct keyword and use it in front of the column.
select distinct university from user_profiledistinct

//Method 2: grouping and de duplication, and filtering out duplicate results by grouping.
select university from user_profile group by university

SQL4 query results limit the number of rows returned

Knowledge foreshadowing: LIMIT is used to LIMIT the result set. The LIMIT clause can be used to force the SELECT statement to return the specified number of records. LIMIT accepts one or two numeric parameters. The parameters must be an integer constant. Here are three examples.
1. If only one parameter is given, it indicates that the maximum number of record lines is returned.
Example: retrieve the first three record lines

select * from table limit 3

2. If two parameters are given, the first parameter specifies the offset of the first return record line, and the second parameter specifies the maximum number of return record lines.
Example 5 Retrieving record lines 6-10

select * from table limit 5,5

3. In order to retrieve all record lines from an offset to the end of the Recordset, you can specify the second parameter as - 1. The offset of the initial record line is 0 (not 1).
Example: retrieve record line 11 last

select * from table limit 10,-1

answer:

select device_id from user_profile limit 2 //Low efficiency
select device_id from user_profile limit 0,2 //High efficiency

SQL8 find user information of a certain age group

//Method 1: connect the condition range with and.
select device_id, gender, age 
from user_profile 
where age >= 20 and age <= 23

//Method 2: use between to delimit the area.
select device_id, gender, age,
from user_profile
where age between 20 and 23

SQL9 find user information except Fudan University

IN: when the value IN the column is equal to a constant value IN in, the result is True, indicating that this record is a record that meets the query criteria.
NOT IN: when the value in the column is equal to a constant value, the result is False, indicating that this record does not meet the query criteria.

//Method 1: use not in.
select device_id,gender,age,university 
from user_profile
where university not in ('Fudan University')

//Method 2: use the operator! =.
select device_id, gender, age, university 
from user_profile 
where university != 'Fudan University'

SQL10 practice of filtering null values with where

Knowledge foreshadowing: NULL values represent uncertain values in the database.
For example, when students have not taken an exam after elective courses, these students have course selection records, but have no exam results, so the exam results are null.
To judge whether a value is NULL, ordinary comparison operators cannot be used.

//The sentence format for judging that the value is empty is:
Listing is null

//The sentence format for judging that the value is not empty is:
Listing is not null

answer:

select device_id, gender, age, university 
from user_profile 
where age is not null

Mixed application of SQL14 operators

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

like fuzzy matching: the matching string can contain the following four wildcards:
_ : Match any character;
%: match 0 or more characters;
[]: match any character in [] (if the character to be compared is continuous, it can be expressed by hyphen "-");
[^]: does not match any character in [].
Here are some specific examples:

//Query the details of the student whose last name is "Zhang" in the student table.
select * from Student list where full name like 'Zhang%'

//Query the names of students whose last name is "Zhang" and whose first name is 3 characters.
select * from Student list where full name like 'Zhang__'

//Inquire about the students whose surnames are "Zhang", "Li" and "Liu" in the student table.
select * from Student list where full name like '[Zhang Li Liu]%'

//Query the name and student number of the student whose name is "small" or "large" in the second word of the name in the student table.
select full name,Student number from Student list where full name like '_[Small big]%'

//Query all students whose surname is not "Liu" in the student table.
select full name from student where full name not like 'Liu%'

//Query the student information whose last student number is not 2, 3 or 5 from the student table.
select * from Student list where Student number like '%[^235]'

answer:

select device_id, age, university 
from user_profile
where university like '%Beijing%'

Tips: SQL statement optimization is also a part of database optimization, and like fuzzy query will cause full table scanning, which is slow. We should try to avoid using like keyword for fuzzy query.

SQL16 finding the highest GPA value

//Method 1: use the max function to find the maximum value of gpa.
select max(gpa) as gpa //Header rename
from user_profile
where university = 'Fudan University'

//Method 2: use order by gpa desc in descending order, and then take the value of the first one.
select gpa
from user_profile
where university = 'Fudan University'
order by gpa desc limit 1

SQL17 calculates the number of boys and the average GPA

// count function counts, avg function calculates the average value, and round function retains the number of decimal places
select count(gender) as male_num, round(avg(gpa), 1) as avg_gpa // as rename
from user_profile
where gender = 'male' //Qualification: Male

SQL18 group calculation exercise

select gender, university, count(id) as user_num, avg(active_days_within_30) as avg_activite_days,
avg(question_cnt) as avg_question_cnt
from user_profile
group by gender, university //Each gender in each school, grouped by school and gender

SQL19 group filtering exercise

HAVING clause is used to filter the grouped results. Its function is a bit like WHERE clause, but it is used for groups rather than individual records. Statistical functions can be used in the HAVING clause, but not in the WHERE clause. HAVING is usually used with the GROUP BY clause.

select university, avg(question_cnt) as avg_question_cnt, avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university //Each school needs to be output, so it is grouped by school
having avg(question_cnt) < 5 or avg(answer_cnt) < 20

SQL20 group sorting exercise

select university, avg(question_cnt) as avg_question_cnt
from user_profile
group by university //Group by school
order by avg_question_cnt //Ascending by average number of Posts

Thanks for watching. If you have any questions, please criticize and correct!