01. Add, delete and modify
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')
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
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
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' );
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
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
SQL23 number and number of answers to each question and test paper
SQL24 meets the needs of people in two activities respectively
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
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