01. Add, delete and modify

insert record

SQL1 insert record (I)

Now there are two users' response records, as follows:

• User 1001 began to answer test paper 9001 at 10:11:12 p.m. on September 1, 2021, and submitted it 50 minutes later, with 90 points;
• User 1002 began to answer test paper 9002 at 7:1:2 a.m. on September 4, 2021, and quit the platform 10 minutes later.

Please insert these two records into the table with one statement.

INSERT INTO exam_record VALUES
(null, 1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(null, 1002, 9002, '2021-09-04 07:01:02', null, null);

SQL2 insert record (II)

The test questions have been imported into exam before 2021_ record_ before_ Form 2021 to back up the test question answering records before 2021.

INSERT INTO exam_record_before_2021
SELECT NULL, uid, exam_id, start_time, submit_time, score
FROM exam_record
WHERE submit_time < '2021-01-01 00:00:00';

SQL3 insert record (III)

Insert 2021-01-01 00:00:00 as the release time into the examination question information table_ Info, whether the ID test paper exists or not, it must be inserted successfully.

REPLACE INTO examination_info
(id, exam_id, tag, difficulty, duration, release_time)
VALUES(null, 9003, 'SQL', 'hard', 90, '2021-01-01 00:00:00')

Update record

SQL4 update record (I)

Put the examination_ In the info table, the tag fields with the tag Python are all modified to python.

UPDATE examination_info
SET tag ='Python' WHERE tag='PYTHON'

SQL5 update record (II)

Take exam_ In the record form, all the unfinished records that began to answer before September 1, 2021 are changed to passive completion, that is, the completion time is changed to '2099-01-01 00:00:00', and the score is changed to 0.

UPDATE exam_record
SET submit_time='2099-01-01 00:00:00', score=0
WHERE start_time<'2021-09-01' AND submit_time is null

Delete record

SQL6 delete record (I)

Delete exam_ In the record form, the answer time is less than 5 minutes and the score is unqualified (the pass line is 60 points).

DELETE FROM exam_record
WHERE timestampdiff(minute,start_time,submit_time) < 5 AND score < 60

SQL7 delete record (II)

Delete exam_ Among the records in the record table that did not complete the answer or the answer time was less than 5 minutes, the first three records began to answer.

DELETE FROM exam_record
WHERE timestampdiff(minute, start_time, submit_time) < 5
OR submit_time IS null
ORDER BY start_time
LIMIT 3

SQL8 delete record (III)

Delete exam_record all records in the table and reset the self incrementing primary key.

TRUNCATE TABLE exam_record;

02. Table and index operation

Creating, modifying and deleting tables

SQL9 creates a new table

Create a high-quality user information table user_ info_ The VIP table structure is consistent with the user information table.

CREATE TABLE IF NOT EXISTS user_info_vip (
    id int(11) primary key auto_increment comment 'Self increasing ID',
    uid int(11) unique not null comment 'user ID',
    nick_name varchar(64) comment 'nickname',
    achievement int(11) default 0 comment 'Achievement value',
    level int(11) comment 'User level',
    job varchar(32) comment 'Career direction',
    register_time datetime default current_timestamp comment 'Registration time'
);

SQL10 modify table

In the user information table, add a column of field school that can store up to 15 Chinese characters after the field level; The job column name in the table is changed to profession al, and the length of varchar field is changed to 10; The default value of achievement is set to 0.

ALTER TABLE user_info ADD school varchar(15) AFTER LEVEL;
ALTER TABLE user_info CHANGE job profession varchar(10);
ALTER TABLE user_info MODIFY achievement int(11) DEFAULT 0;

SQL11 delete table

Delete (if any) (2011 to 2014) the backup table.

DROP TABLE
IF EXISTS
exam_record_2011,
exam_record_2012,
exam_record_2013,
exam_record_2014;

Index creation and deletion

SQL12 create index

In order to query the table more conveniently and quickly, you need to_ The following indexes are created for the info table. The rules are as follows:

Create a normal index idx in the duration column_ Duration, in exam_ Create unique index uniq for ID column_ idx_ exam_id. create full-text index in tag column_ idx_ tag.

# unique index
ALTER TABLE examination_info
ADD UNIQUE INDEX uniq_idx_exam_id(exam_id);

# Full text index
ALTER TABLE examination_info
ADD FULLTEXT INDEX full_idx_tag(tag);
 
# General index
ALTER TABLE examination_info
ADD INDEX idx_duration(duration);

SQL13 delete index

Delete examination_ Unique index uniq on info table_ idx_ exam_ ID and full text index_ idx_ tag.

DROP index uniq_idx_exam_id ON examination_info;
DROP index full_idx_tag ON examination_info;

03. Aggregate grouping query

Aggregate function

Sql14 truncated average score of SQL type high difficulty test paper

From exam_ In the record data table, calculate the truncated average value of the scores of all users completing the SQL difficult test paper (the average value after removing a maximum value and a minimum value).

SELECT tag,difficulty,
round((SUM(score) - MIN(score) - MAX(score)) / (COUNT(score) - 2), 1) as avg_score
FROM examination_info 
JOIN exam_record using(exam_id)
WHERE tag='SQL' AND difficulty='hard';

SQL15 counts the number of answers

Count the total number of answers from the table_ PV. The test paper has been completed_ PV. Number of test papers completed_ exam_ cnt.

SELECT DISTINCT COUNT(*) as total_pv, COUNT(submit_time) as compelete_pv,
COUNT(DISTINCT exam_id AND score IS not NULL) as complete_exam_cnt
FROM exam_record;

SQL16 score is not less than the lowest score of the average score

Find the lowest score of users whose SQL test paper score is not less than the average score of this kind of test paper from the table.

SELECT MIN(score) as min_score_over_avg
FROM examination_info as i 
JOIN exam_record as r
ON i.exam_id = r.exam_id
WHERE tag = 'SQL'
AND score >= (
    SELECT AVG(score) FROM examination_info as i
    JOIN exam_record as r
    ON i.exam_id = r.exam_id
    WHERE tag = 'SQL'
);

Grouping query

SQL17 average active days and monthly number

Calculate the average monthly active days AVG of users in the test paper answering area in each month in 2021_ active_ Days and monthly active number mau.

SELECT concat(substr(submit_time, 1, 4), substr(submit_time, 6, 2)) as month,
ROUND(COUNT(DISTINCT uid, day(submit_time)) / COUNT(DISTINCT uid), 2) as avg_active_days,
ROUND(COUNT(DISTINCT(uid)), 0) as mau
FROM exam_record
WHERE submit_time IS NOT null AND year(submit_time) = '2021'
GROUP BY month;

Total number of questions brushed in August and daily average number of questions brushed in sql1

Count the total monthly number of questions brushed by users in each month in 2021, month_q_cnt and AVG of questions per day_ day_ q_ CNT (sorted in ascending order by month) and the overall situation of the year.

SELECT ifnull(a.ymd, '2021 Summary'), COUNT(1), ROUND(COUNT(1) / max(da), 3)
FROM (
    SELECT DATE_FORMAT(submit_time, '%Y%m') ymd, 
    DAYOFMONTH(last_day(submit_time)) da
    FROM  practice_record 
    WHERE  year(submit_time) = '2021' 
) a
GROUP BY a.ymd WITH ROLLUP

SQL19 valid users whose number of unfinished test papers is greater than 1

04. Multi table query

nested subqueries

Users who have completed no less than 3 test papers in SQL20 like to answer

Number of respondents and average score on the day of release of SQL21 test paper

User grade distribution of people whose score of SQL22 answering test paper is greater than 80

Merge query

SQL23 number and number of answers to each question and test paper

SQL24 meets the needs of people in two activities respectively

join query

SQL25 number of completed test papers and exercises of qualified users

SQL26 activity of each level 6 / 7 user

05. Window function

Special window function

SQL27 top 3 scores of each type of test paper

The difference between the second fast / slow use time of SQL28 is greater than half of the test paper time

SQL29 maximum time window for answering test papers for two consecutive times

Completion of users who have not completed 0 test papers in SQL30 in recent three months

Responses of 50% users with high completion rate of SQL31 in recent three months

Year on year growth rate and ranking change of completed number of SQL32 test papers in 2020

Aggregate window function

SQL33 normalizes the score of the test paper by Min max

SQL34 monthly answers for each test paper and the total number of answers by the end of the month.

Answers of SQL35 every month and up to the current month

06. Other common operations

Null value processing

SQL36 counts the number and rate of unfinished papers with unfinished status

Average time and average score of sql37 level 0 user's difficult test paper

Advanced conditional statement

SQL38 filters users who qualify nickname achievement values and active dates

SQL39 screen the answering records of nickname rules and test paper rules

SQL40 outputs different information according to whether the specified record exists

SQL41 proportion of different score performance of each user level

Limited query

SQL42 the first three people registered

SQL43 completed the third page of the list of test papers on the day of registration

Text conversion function

SQL44 fixes the serial records

SQL45 intercepts too long nicknames

SQL46 filtering statistics in case of case confusion