Bao Jianjia operation

requirement

  1. You need to enter dB by the given field_ Create their own mode under qingniu
  2. The tables created need to meet the technical specifications
  3. Multiple answer results
  4. The shorter the SQL execution time, the better
  5. You need to provide SQL statements in the process of creating table statements and inserting statements

Known table information

  • Table structure

Table 1: exam_record, fields (uid user ID, exam_id, exam ID, start_time, start answering time, submit_time, submission time, score)

Table 2: examination_info, fields (exam_id, test ID, tag, test category, difficulty, duration, test duration, release_time)

  • Table data
INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'SQL', 'easy', 60, '2020-02-01 10:00:00'),
  (9003, 'algorithm', 'medium', 80, '2020-08-02 10:00:00'),
  (9004, 'High number', 'medium', 80, '2020-08-02 10:00:00');
  
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:31:01', 84),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9002, '2021-02-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null),
(1004, 9004, '2021-09-06 12:01:01', null, null);

Create table statement

Considering the type of test paper, the difficulty of test paper is generally a single value code, so VC100 is used

CREATE TABLE "db_bjj"."examination_info" (
  "exam_id" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "tag" varchar(100) COLLATE "pg_catalog"."default",
  "difficulty" varchar(100) COLLATE "pg_catalog"."default",
  "duration" varchar(300) COLLATE "pg_catalog"."default",
  "release_time" timestamp(6)
)
;

ALTER TABLE "db_bjj"."examination_info" 
  OWNER TO "sa";

COMMENT ON COLUMN "db_bjj"."examination_info"."exam_id" IS 'test paper ID';

COMMENT ON COLUMN "db_bjj"."examination_info"."tag" IS 'Test paper category';

COMMENT ON COLUMN "db_bjj"."examination_info"."difficulty" IS 'Test paper difficulty';

COMMENT ON COLUMN "db_bjj"."examination_info"."duration" IS 'Examination duration';

COMMENT ON COLUMN "db_bjj"."examination_info"."release_time" IS 'Release time';

COMMENT ON TABLE "db_bjj"."examination_info" IS 'Test paper information form';


CREATE TABLE "db_bjj"."exam_record" (
  "uid" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "exam_id" varchar(32) COLLATE "pg_catalog"."default",
  "start_time" timestamp(6),
  "submit_time" timestamp(6),
  "score" int4
)
;

ALTER TABLE "db_bjj"."exam_record" 
  OWNER TO "sa";

COMMENT ON COLUMN "db_bjj"."exam_record"."uid" IS 'user ID';

COMMENT ON COLUMN "db_bjj"."exam_record"."exam_id" IS 'test paper ID';

COMMENT ON COLUMN "db_bjj"."exam_record"."start_time" IS 'start time';

COMMENT ON COLUMN "db_bjj"."exam_record"."submit_time" IS 'Submission time';

COMMENT ON COLUMN "db_bjj"."exam_record"."score" IS 'score';

COMMENT ON TABLE "db_bjj"."exam_record" IS 'Test paper score table';

Topic 1: find the lowest score that is not less than the average score

  • describe

The lowest score of users whose SQL test paper score is not less than the average score of this kind of test paper,

  • example

For the SQL category of the test paper, the scores of the two test papers are [80,81,84,89,87,90], the average score is 85.16, and the minimum score not less than the average score is 87

Idea: 1. In table Association, the small table comes first and the large table comes last, so as to reduce the amount of data as much as possible. 2

2. Use the alias of the table to reduce the resolution time

3. According to the sql execution order, select after where. Filter out more data in the where condition as much as possible

Method 1

The most common way of writing is to find out the average value first, then obtain a score greater than the average value, and take the minimum score

SELECT MIN
	( score ) 
FROM
	exam_record 
WHERE
	score > ( SELECT AVG ( score ) FROM "examination_info" info LEFT JOIN exam_record record ON record.exam_id = info.exam_id WHERE info.tag = 'SQL' ) 
	AND exam_record.exam_id < '9003'

Method 2

Temporary tables that exist for queries using with

WITH bjj AS (
	SELECT
		record.score 
	FROM
		examination_info info
		LEFT JOIN exam_record record ON record.exam_id = info.exam_id 
	WHERE
		record.score IS NOT NULL 
		AND info.tag = 'SQL' 
	) SELECT MIN
	( score ) 
FROM
	bjj 
WHERE
	score >= ( SELECT AVG ( score ) FROM bjj )

Method 3

Window function

WITH bjj AS (
	SELECT
		record.score,
		AVG ( score ) OVER ( PARTITION BY tag ) AS AVG 
	FROM
		exam_record record
		LEFT JOIN examination_info info ON info.exam_id = record.exam_id 
	WHERE
		record.score IS NOT NULL 
		AND info.tag = 'SQL' 
	) SELECT MIN
	( score ) 
FROM
	bjj 
WHERE
	bjj.score > bjj.AVG

Topic 2: find the truncated average of scores

  • describe

Truncated average value of score of SQL test paper with difficulty of hard (average value after removing a maximum value and a minimum value)

  • example

The scores of the test paper are [80,81,84,89,87], excluding the highest and lowest scores, it is [87,81,84], with an average score of 84

Method 1

Processing with mathematical addition, subtraction, multiplication and division

SELECT
	( SUM ( score ) - MAX ( score ) - MIN ( score ) ) / ( COUNT ( score ) - 2 ) AS AVG 
FROM
	examination_info info
	LEFT JOIN exam_record record ON record.exam_id = info.exam_id 
WHERE
	info.tag = 'SQL' 
	AND info.difficulty = 'hard'

Method 2

Window function

With bjj as(
	SELECT
		record.score,
		RANK ( ) OVER ( PARTITION BY tag ORDER BY record.score ASC ) AS ASC,
		RANK ( ) OVER ( PARTITION BY tag ORDER BY record.score DESC ) AS DESC 
	FROM
		exam_record record
		LEFT JOIN examination_info info ON info.exam_id = record.exam_id 
	WHERE
		record.score IS NOT NULL 
		AND info.tag = 'SQL' 
		AND info.difficulty = 'hard' 
		)
		SELECT round(avg(score)) from bjj where
		 bjj.ASC <> '1' 
	 and bjj.DESC <> '1'

Topic 3: count the number of answers

  • describe

    Count the number of answers, the number of test papers completed and the number of test papers completed

  • example

Indicates that up to now, there are 12 test paper answering records, the number of completed answers is 7 (those who quit halfway are in the unfinished state, and the submission time and number of copies are NULL), and the number of completed test papers (900190029003, not in the unfinished state)

Output answer: 12|7|3

SELECT
	concat ( A.ALL, '|', A.ywczd, '|', A.ywccs ) 
FROM
	(
	SELECT COUNT
		( uid ) AS ALL,
		SUM ( CASE WHEN submit_time IS NOT NULL THEN 1 ELSE 0 END ) AS ywczd,
		COUNT ( DISTINCT CASE WHEN submit_time IS NOT NULL THEN exam_id ELSE NULL END ) AS ywccs 
	FROM
	exam_record 
	) A
wczd, '|', A.ywccs ) 
FROM
	(
	SELECT COUNT
		( uid ) AS ALL,
		SUM ( CASE WHEN submit_time IS NOT NULL THEN 1 ELSE 0 END ) AS ywczd,
		COUNT ( DISTINCT CASE WHEN submit_time IS NOT NULL THEN exam_id ELSE NULL END ) AS ywccs 
	FROM
	exam_record 
	) A

------------------------------------- Create table:-------------------------------------
CREATE TABLE "db_zyg"."exam_record" (
  "c_bh" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "uid" varchar(32) COLLATE "pg_catalog"."default",
  "exam_id" varchar(32) COLLATE "pg_catalog"."default",
  "start_time" timestamp(6),
  "submit_time" timestamp(6),
  "score" int4,
  "c_cjr" varchar(300) COLLATE "pg_catalog"."default",
  "dt_cjsj" timestamp(6),
  "c_xgr" varchar(300) COLLATE "pg_catalog"."default",
  "dt_zhxgsj" timestamp(6),
  CONSTRAINT "exam_record_pkey" PRIMARY KEY ("c_bh")
)
;

CREATE TABLE "db_zyg"."examination_info" (
  "exam_id" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "tag" varchar(300) COLLATE "pg_catalog"."default",
  "difficulty" varchar(300) COLLATE "pg_catalog"."default",
  "duration" varchar(300) COLLATE "pg_catalog"."default",
  "release_time" timestamp(6),
  "c_cjr" varchar(300) COLLATE "pg_catalog"."default",
  "dt_cjsj" timestamp(6),
  "c_xgr" varchar(300) COLLATE "pg_catalog"."default",
  "dt_zhxgsj" timestamp(6),
  CONSTRAINT "examination_info_pkey" PRIMARY KEY ("exam_id")
)
;

------------------------------------- Insert:-------------------------------------
INSERT INTO "db_zyg"."exam_record" VALUES ('0', '1001', '9001', '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80, NULL, NULL, NULL, NULL);
INSERT INTO "db_zyg"."exam_record" VALUES ('1', '1001', '9001', '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81, NULL, NULL, NULL, NULL);
INSERT INTO "db_zyg"."exam_record" VALUES ('2', '1001', '9001', '2021-06-02 19:01:01', '2021-06-02 19:31:01', 84, NULL, NULL, NULL, NULL);
INSERT INTO "db_zyg"."exam_record" VALUES ('3', '1002', '9001', '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89, NULL, NULL, NULL, NULL);
INSERT INTO "db_zyg"."exam_record" VALUES ('4', '1002', '9002', '2021-09-02 12:01:01', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO "db_zyg"."exam_record" VALUES ('5', '1002', '9003', '2021-09-01 12:01:01', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO "db_zyg"."exam_record" VALUES ('6', '1002', '9001', '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87, NULL, NULL, NULL, NULL);
INSERT INTO "db_zyg"."exam_record" VALUES ('7', '1002', '9002', '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90, NULL, NULL, NULL, NULL);
INSERT INTO "db_zyg"."exam_record" VALUES ('8', '1003', '9002', '2021-02-06 12:01:01', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO "db_zyg"."exam_record" VALUES ('9', '1003', '9003', '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86, NULL, NULL, NULL, NULL);
INSERT INTO "db_zyg"."exam_record" VALUES ('10', '1004', '9003', '2021-09-06 12:01:01', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO "db_zyg"."exam_record" VALUES ('11', '1004', '9004', '2021-09-06 12:01:01', NULL, NULL, NULL, NULL, NULL, NULL);

INSERT INTO "db_zyg"."examination_info" VALUES ('9001', 'SQL', 'hard', '60', '2020-01-01 10:00:00', NULL, NULL, NULL, NULL);
INSERT INTO "db_zyg"."examination_info" VALUES ('9002', 'SQL', 'easy', '60', '2020-02-01 10:00:00', NULL, NULL, NULL, NULL);
INSERT INTO "db_zyg"."examination_info" VALUES ('9003', 'algorithm', 'medium', '80', '2020-08-02 10:00:00', NULL, NULL, NULL, NULL);
INSERT INTO "db_zyg"."examination_info" VALUES ('9004', 'High number', 'medium', '80', '2020-08-02 10:00:00', NULL, NULL, NULL, NULL);

------------------------------------- Topic 1:-------------------------------------
SELECT
	score 
FROM
	db_zyg.exam_record re
	INNER JOIN examination_info info ON re.exam_id = info.exam_id 
WHERE
	tag = 'SQL' 
	AND score > (
	SELECT AVG
		( score )
	FROM
		db_zyg.exam_record re
		INNER JOIN examination_info info ON re.exam_id = info.exam_id 
	WHERE
		tag = 'SQL' 
		AND score IS NOT NULL 
	) 
ORDER BY
	score ASC 
	LIMIT 1;
	
------------------------------------- Topic 2:-------------------------------------
SELECT AVG
	( score ) 
FROM
	(
	SELECT
		score,
		ROW_NUMBER ( ) OVER ( ORDER BY score ) AS ROW_NUMBER 
	FROM
		db_zyg.exam_record re
		INNER JOIN examination_info info ON re.exam_id = info.exam_id 
	WHERE
		tag = 'SQL' 
		AND difficulty = 'hard' 
		AND score IS NOT NULL 
	) TEMP 
WHERE
	ROW_NUMBER != 1 
	AND ROW_NUMBER != (
	SELECT COUNT
		( 1 ) 
	FROM
		db_zyg.exam_record re
		INNER JOIN examination_info info ON re.exam_id = info.exam_id 
	WHERE
		tag = 'SQL' 
		AND difficulty = 'hard' 
		AND score IS NOT NULL 
	);

SELECT AVG
	( score ) 
FROM
	(
	SELECT
		score 
	FROM
		db_zyg.exam_record re
		INNER JOIN examination_info info ON re.exam_id = info.exam_id 
	WHERE
		tag = 'SQL' 
		AND difficulty = 'hard' 
		AND score IS NOT NULL 
		ORDER BY score
		LIMIT (
		SELECT COUNT
			( 1 ) - 2 
		FROM
			db_zyg.exam_record re
			INNER JOIN examination_info info ON re.exam_id = info.exam_id 
		WHERE
			tag = 'SQL' 
			AND difficulty = 'hard' 
			AND score IS NOT NULL 
		) OFFSET 1 
	) TEMP;

SELECT AVG
	( score ) 
FROM
	(
	SELECT
		score 
	FROM
		db_zyg.exam_record re
		INNER JOIN examination_info info ON re.exam_id = info.exam_id 
	WHERE
		tag = 'SQL' 
		AND difficulty = 'hard' 
		AND c_bh NOT IN (
			(
			SELECT
				c_bh 
			FROM
				db_zyg.exam_record re
				INNER JOIN examination_info info ON re.exam_id = info.exam_id 
			WHERE
				tag = 'SQL' 
				AND difficulty = 'hard' 
			ORDER BY
				score ASC 
				LIMIT 1 
			) UNION ALL
			(
			SELECT
				c_bh 
			FROM
				db_zyg.exam_record re
				INNER JOIN examination_info info ON re.exam_id = info.exam_id 
			WHERE
				tag = 'SQL' 
				AND difficulty = 'hard' 
			ORDER BY
				score DESC 
				LIMIT 1 
			) 
		) 
	) TEMP;

------------------------------------- Topic 3:-------------------------------------
SELECT
	* 
FROM
	( SELECT COUNT ( 1 ), SUM ( CASE WHEN score IS NULL THEN 0 ELSE 1 END ) FROM db_zyg.exam_record ) t1,
	( SELECT COUNT ( DISTINCT exam_id ) FROM db_zyg.exam_record WHERE score IS NOT NULL ) t2;
	
SELECT
	* 
FROM
	( SELECT COUNT ( 1 ), SUM ( CASE WHEN score IS NULL THEN 0 ELSE 1 END ) FROM db_zyg.exam_record ) t1,
	( SELECT COUNT ( 1 ) FROM ( SELECT exam_id FROM db_zyg.exam_record WHERE score IS NOT NULL GROUP BY exam_id ) T ) t2;

Tags: Database

Posted on Fri, 12 Nov 2021 03:24:32 -0500 by noeledoran

Hot Tags

©2023 Programmer Help