LintCode SQL教程 练习题答案

SQL 教程链接：https://www.lintcode.com/learn

LEVEL1:

• 2045 · Output Hello LintCode

  SELECT "Hello LintCode!";

• 2013 · Check the name of the teacher

  SELECT `name` FROM `teachers`

• 2007 · Check course name and class size

  SELECT `name`,`student_count` FROM courses

• 2009 · Query all teachers

  SELECT * FROM `teachers`

• 1981 · Check the nationality of all teachers

  SELECT DISTINCT country FROM teachers

• 2011.Search for information on courses with more than 1000 participants

  SELECT * FROM `courses` WHERE `student_count`>1000

• 2012 · Find course information for the course named Artificial Intelligence

  SELECT * FROM courses WHERE `name`= 'Artificial Intelligence';

• 2017 · Inserting SQL course information into the course table

  INSERT INTO courses VALUES (14,"SQL",200,"2021-02-25",1)

• 2021 · Insert teacher information into the specified column of the teachers table

  INSERT INTO `teachers` (`name`,`email`,`age`,`country`) VALUES
  ('XiaoFu','XiaoFu@lintcode.com',20,'CN');

• 2020 · Update on the number of students choosing artificial intelligence

  UPDATE `courses` SET `student_count`=500 WHERE `name`='Artificial Intelligence'

• 2004 · Delete all courses until 2020

  DELETE FROM `courses` WHERE `created_at`<'2020-1-1'

• 2019 · Delete all rows in the table

  DELETE FROM `courses`

LEVEL2：

• 1952 · Query teachers over 20 years old

  SELECT * FROM `teachers` WHERE `age`>20

• 1953 · Query the name of the Chinese teacher

  SELECT `name` FROM `teachers` WHERE `country`='CN'

• 1957 · Inquire about courses starting before May 2020

  SELECT `name`,`created_at` FROM `courses` WHERE `created_at`>='2020-1-1' AND `created_at`<'2020-5-1'

• 1958 · Query the courses that meet the conditions taught by the specified teacher

  SELECT * FROM `courses` WHERE `teacher_id`=4 AND `student_count`>500

• 2001 · Query the course information of ‘Web’ or ‘Big Data’

  SELECT * FROM `courses`WHERE `name`='Web' OR `name`='Big Data';

• 2040 · Search for courses with an instructor id of less than 3 and more than 800 students

  SELECT * FROM `courses` WHERE (NOT `teacher_id`=3) AND (`student_count`>800);

• 1960 · Query course information for a specific time

  SELECT * FROM `courses` WHERE created_at IN ('2021-1-1','2021-1-3')

• 1962 · Query courses with teacher id other than 1 and 3

  SELECT `name` FROM courses WHERE `teacher_id` NOT IN(1,3)

• 1964 · Query for course information about the number of students within the specified range

  SELECT * FROM `courses` WHERE `student_count` BETWEEN 50 AND 55;

• 1972 · Inquire about Chinese and Japanese teachers who have e-mail addresses

  SELECT * FROM teachers WHERE (email IS NOT NULL) AND (`country`='CN' OR `country`='JP');

• 1974 · Query teacher information by email

  SELECT `name`,`email` FROM `teachers` WHERE `email` LIKE '%@qq.com';

• 1982 · Check the age of teachers and sort them in ascending order

  SELECT DISTINCT `age` FROM `teachers` ORDER BY `age`;

• 1977 · Sorted by age of Chinese teachers in descending order

  SELECT * FROM `teachers` WHERE `country`='CN' ORDER BY `age` DESC;

• 1980 · Search for the oldest Chinese teacher

  SELECT * FROM `teachers` WHERE `country`='CN' ORDER BY `age` DESC LIMIT 1;

LEVEL3:

• 2034 · Check the average age of teachers at the end of the specified mailbox

  SELECT AVG(`age`) AS `average_teacher_age` FROM `teachers` WHERE `email` LIKE('%@qq.com')

• 1987 · Find the age of the oldest Chinese teacher

  SELECT MAX(`age`) AS 'max_age' FROM `teachers` WHERE `country`='CN';

• 1989 · Check the age of the youngest teacher

  SELECT MIN(`age`) AS min_age FROM `teachers`

• 1991 · Count the total number of students for teacher #3

  SELECT SUM(`student_count`) AS select_student_sum FROM `courses` WHERE `teacher_id`=3;

• 1995 · Check the average age of teachers over 20 years old

  SELECT ROUND(AVG(`age`)) AS avg_teacher_age FROM `teachers` WHERE `age`>20

• 1997 · Check the information of teachers who do not have email and are older than 20 years old

  SELECT * FROM `teachers` WHERE ISNULL(`email`) AND `age`>20

• 1985 · Number of teachers aged 20 to 28 who are Chinese and British nationals

  SELECT COUNT(*) AS teacher_count FROM `teachers` WHERE `country` IN('CN','UK') AND `age` BETWEEN 20 AND 28;

• 2081 · Insert the current date into the table

  INSERT INTO `records` VALUES (CURDATE());

• 2037 · Search for course titles and course dates through August 2020

  SELECT `name`,DATE(`created_at`) AS `created_date` FROM courses WHERE `created_at`<'2020-8-1'

• 2030 · Query the hours of all course creation times

  SELECT `name`,EXTRACT(HOUR FROM `created_at`) AS `created_hour` FROM `courses`

• 2046 · The date the course was created is displayed in ‘year-month-day hour:minute:second’

  SELECT DATE_FORMAT(`created_at`,'%Y-%m-%d %H:%i:%s') AS `DATE_FORMAT` FROM courses;

• 2028 · Postpone all course creation dates by one day

  SELECT `name`,DATE_ADD(`created_at`,INTERVAL 1 DAY) AS new_created FROM `courses`

• 2032 · Advance all course creation dates by one day

  SELECT `id`,`name`,DATE_SUB(`created_at`, INTERVAL 1 FROM `courses` DAY) AS `new_created`

• 2029 · Calculate the number of days from 03/26/2019 to the course creation time

  SELECT DATEDIFF(`created_at`,'2019-3-26') AS date_diff FROM `courses`

• 2036 · Calculate the number of months difference between the start date and the current date of all courses in the schedule

  SELECT TIMESTAMPDIFF(MONTH,`created_at`,'2020-4-22') AS `MonthDiff` FROM `courses`

LEVEL4：

• 2084 · Add primary key constraints to the course table courses

  ALTER TABLE `courses` ADD PRIMARY KEY(`id`);

• 2085 · Remove the primary key constraint from the course table courses

  ALTER TABLE `courses` DROP PRIMARY KEY;

• 2091 · Adding Foreign Key Constraints to Course Tables

  ALTER TABLE `courses` ADD FOREIGN KEY(`teacher_id`) REFERENCES teachers(`id`)

• 2062 · Query the id and name of all courses taught by the specified teacher

  SELECT `c`.`id`,`c`.`name` AS `course_name`, `t`.`name` AS `teacher_name` FROM `courses` `c` INNER JOIN `teachers` `t` ON `c`.`teacher_id`=`t`.`id` WHERE `t`.`name`='Eastern Heretic';

• 2051 · Search for the names of teachers from China and the names of courses they taught

  SELECT c.name AS course_name, t.name AS teacher_name FROM teachers t LEFT JOIN courses c ON t.id=c.teacher_id WHERE `t`.`country`='CN';

• 2053 · Check the name, email and course name of the teacher from China

  SELECT c.name AS course_name,t.name AS teacher_name,t.email AS teacher_email FROM courses c RIGHT JOIN teachers t ON t.id=c.teacher_id WHERE t.country='CN'

• 2055 · Search for all course names and their corresponding instructor names and nationalities

  SELECT c.name AS course_name,t.name AS teacher_name,t.country AS teacher_country FROM courses c LEFT JOIN teachers t on c.teacher_id=t.idUNION
  SELECT c.name AS course_name,t.name AS teacher_name,t.country AS teacher_country FROM courses c RIGHT JOIN teachers t on c.teacher_id=t.id

LEVEL 5:

• 2078 · Find out the number of teachers of different ages

  SELECT `age`,COUNT(`age`) AS age_count FROM `teachers` GROUP BY `age` ORDER BY `age` DESC

• 2082 · Statistics on the number of courses taught by each teacher

  SELECT t.name AS teacher_name,IFNULL(COUNT(c.id),0) AS course_count FROM teachers t LEFT JOIN courses c ON t.id=c.teacher_id GROUP BY t.name ORDER BY course_count DESC, t.name

• 2076 · Search for information on teachers of the same nationality whose average age is greater than the average age of all teachers

  SELECT * FROM teachers WHERE country=any(SELECT country FROM teachers GROUP BY `country` HAVING AVG(age)>(SELECT AVG(age) FROM teachers));

• 2060 · Search for the name of the teacher for the ‘Big Data’ course

  SELECT `name` FROM teachers WHERE id=(SELECT teacher_id FROM courses WHERE name='Big Data')

• 2056 · Copy the data in the teachers table that are older than 20 to another table

  INSERT INTO `teachers_bkp` SELECT * FROM teachers WHERE age>20;

• 2057 · Modify course information created by instructor Eastern Heretic

  UPDATE `courses` SET `name`='PHP',student_count=300 WHERE teacher_id=(SELECT id FROM teachers WHERE name='Eastern Heretic')

• 2059 · Remove faculty who have created courses before 2020

  DELETE FROM `teachers` WHERE id IN(SELECT teacher_id FROM courses WHERE created_at<'2020-1-1');

• 2077 · Search for information on courses and instructors with the highest number of students

  SELECT course_name,student_count,teacher_name FROM (SELECT `c`.`name` `course_name`, c.student_count AS `student_count`,`t`.`name` AS `teacher_name`
  FROM `teachers` `t` ASLEFT JOIN `courses` `c` ON `c`.`teacher_id` = `t`.`id`) `T` WHERE student_count=(SELECT MAX(student_count) FROM `courses`)

• 2065 · Check the course names of all courses taught by all teachers who are older than 20 years old

  SELECT name FROM `courses` WHERE teacher_id IN (SELECT id FROM `teachers` WHERE age>20);

• 2070 · Search for the name of a course created later than the creation time of any of the specified teacher’s courses

  SELECT `name` FROM `courses` WHERE (`created_at`>ANY(SELECT `created_at` FROM `courses` WHERE teacher_id=(SELECT id FROM teachers WHERE name='Southern Emperor'))) AND teacher_id <> (SELECT id FROM teachers WHERE name='Southern Emperor');

• 2066 · Search for course information for courses with more students than the number of students in all courses of the oldest teacher

  SELECT * FROM `courses` WHERE student_count>ALL(SELECT student_count FROM `courses` WHERE teacher_id IN(SELECT id FROM `teachers` WHERE age=(SELECT MAX(age) FROM teachers)));

• 2069 · Search for the course name and number of students in the course with the highest number of students per instructor

  SELECT name,student_count FROM `courses` WHERE (teacher_id,student_count) IN(SELECT teacher_id,MAX(student_count) FROM `courses` GROUP BY teacher_id)

• 2086 · Search for the nationality of the teacher starting with ‘U’ and the total number of students between 2000 and 5000 and the total number of students of that nationality

  SELECT t.country AS country,SUM(c.student_count) AS student_count FROM teachers t LEFT JOIN courses c ON t.id=c.teacher_id GROUP BY country HAVING SUM(student_count) BETWEEN 2000 AND 5000 AND country LIKE 'U%';

LEVEL6：

• 2616 · Insert Kansas information into the teacher table

  BEGIN;
  
  -- 插入 Kansas 的信息 --
  -- Write your SQL Query here --
  INSERT INTO `teachers` (`name`, `age`, `country`)
  VALUES ('Kansas', 41, 'UK');
  
  COMMIT;

LEVEL7：