文章/答案/技术大牛

发布

社区首页 >专栏 >SQL必知必会：刷题笔记

SQL必知必会：刷题笔记

陈大剩博客

发布于 2023-07-09 06:07:27

40700

代码可运行

文章被收录于专栏：陈大剩博客专栏陈大剩博客专栏

运行总次数：0

代码可运行

记录一些 sql 刷题笔记

力扣

1. 删除重复的电子邮箱

表: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id是该表的主键列。
该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。

编写一个 SQL 删除语句 来删除所有重复的电子邮件，只保留一个id最小的唯一电子邮件。

以 任意顺序 返回结果表。（注意：仅需要写删除语句，将自动对剩余结果进行查询）

查询结果格式如下所示。示例 1:

输入: 
Person 表:
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
输出: 
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+
解释: john@example.com重复两次。我们保留最小的Id = 1。

题解

# where 子查询
delete from Person where id not in (select * from (select min(id) from Person group by Email) as x )

# LeetCode

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

196. 删除重复的电子邮箱

2. 按日期分组销售产品

表 Activities：

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| sell_date   | date    |
| product     | varchar |
+-------------+---------+
此表没有主键，它可能包含重复项。
此表的每一行都包含产品名称和在市场上销售的日期。

编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。每个日期的销售产品名称应按词典序排列。返回按 sell_date 排序的结果表。查询结果格式如下例所示。

示例 1:

输入：
Activities 表：
+------------+-------------+
| sell_date  | product     |
+------------+-------------+
| 2020-05-30 | Headphone   |
| 2020-06-01 | Pencil      |
| 2020-06-02 | Mask        |
| 2020-05-30 | Basketball  |
| 2020-06-01 | Bible       |
| 2020-06-02 | Mask        |
| 2020-05-30 | T-Shirt     |
+------------+-------------+
输出：
+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-05-30 | 3        | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |
+------------+----------+------------------------------+
解释：
对于2020-05-30，出售的物品是 (Headphone, Basketball, T-shirt)，按词典序排列，并用逗号 ',' 分隔。
对于2020-06-01，出售的物品是 (Pencil, Bible)，按词典序排列，并用逗号分隔。
对于2020-06-02，出售的物品是 (Mask)，只需返回该物品名。

题解

select sell_date, count(distinct product) as num_sold,group_concat(distinct product) as products from Activities group by sell_date;

按日期分组销售产品

丢失信息的雇员

表: Employees

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
+-------------+---------+
employee_id 是这个表的主键。
每一行表示雇员的id 和他的姓名。

表: Salaries

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| salary      | int     |
+-------------+---------+
employee_id is 这个表的主键。
每一行表示雇员的id 和他的薪水。

写出一个查询语句，找到所有丢失信息的雇员id。当满足下面一个条件时，就被认为是雇员的信息丢失：

雇员的姓名丢失了，或者
雇员的 薪水信息 丢失了，或者

返回这些雇员的id employee_id ，从小到大排序。查询结果格式如下面的例子所示。示例 1：

输入：
Employees table:
+-------------+----------+
| employee_id | name     |
+-------------+----------+
| 2           | Crew     |
| 4           | Haven    |
| 5           | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5           | 76071  |
| 1           | 22517  |
| 4           | 63539  |
+-------------+--------+
输出：
+-------------+
| employee_id |
+-------------+
| 1           |
| 2           |
+-------------+
解释：
雇员1，2，4，5 都工作在这个公司。
1号雇员的姓名丢失了。
2号雇员的薪水信息丢失了。

题解

select employee_id FROM (
SELECT employee_id FROM Employees 
UNION ALL 
SELECT employee_id FROM Salaries
) as t 
group by employee_id
having count(*)=1
order by employee_id

丢失信息的雇员

CSDN 题目

各个表的基本信息

# 学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
#课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
#教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
#成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20), # 学号
`c_id` VARCHAR(20),# 课程号
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);

#插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
#课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

#教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

#成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

题型

1. 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

SELECT
    t1.s_id,
    t3.s_name,
    t1.s_score '01',
    t2.s_score '02'
FROM
    Score AS t1
    JOIN Score AS t2 ON t1.s_id = t2.s_id 
    JOIN Student as t3 on t1.s_id=t3.s_id
    AND t1.s_score > t2.s_score 
    AND t2.c_id = '02' 
WHERE
    t1.c_id = '01'

2. 查询平均成绩大于60分的学生的学号和平均成绩

SELECT
    t1.s_id ,
    avg(t1.s_score )
FROM
    Score AS t1 
GROUP BY
    t1.s_id 
HAVING
    avg(t1.s_score ) > 60

3. 查询所有学生的学号、姓名、选课数、总成绩

SELECT
    t1.s_id,
    t1.s_name,
    count( t2.c_id ) AS c,
    SUM( t2.s_score ) 'total' 
FROM
    Student AS t1
    left JOIN Score AS t2 ON t1.s_id = t2.s_id 
GROUP BY
    t1.s_id

4. 查询姓“猴”的老师的个数

select count(*) from Teacher where t_name like '猴%';

5. 查询没学过“张三”老师课的学生的学号、姓名

SELECT
    * 
FROM
    student 
WHERE
    s_id NOT IN ( SELECT s_id FROM score WHERE c_id = ( SELECT c_id FROM course WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = '张三' ) ) )

6. 查询学过“张三”老师所教的所有课的同学的学号、姓名

SELECT
    * 
FROM
    student 
WHERE
    s_id  IN ( SELECT s_id FROM score WHERE c_id = ( SELECT c_id FROM course WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = '张三' ) ) )

7. 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

SELECT
    t1.s_id,
    t3.s_name
FROM
    Score AS t1
    JOIN Score AS t2 ON t1.s_id = t2.s_id
    JOIN Student AS t3 ON t1.s_id = t3.s_id 
WHERE
    t1.c_id = '01' 
    AND t2.c_id = '02' 
GROUP BY
    t1.s_id

8. 查询课程编号为“02”的总成绩

select SUM(s_score) total FROM Score WHERE c_id='02'

9. 查询学生每门课程成绩均小于60分的学生的学号、姓名

SELECT
    t3.s_id,
    t3.s_name 
FROM
    ( SELECT s_id, count(*) total FROM Score WHERE s_score < 60 GROUP BY s_id ) AS t1
    JOIN ( SELECT s_id, count(*) total FROM Score GROUP BY s_id ) AS t2 ON t1.s_id = t2.s_id
    JOIN Student AS t3 ON t1.s_id = t3.s_id 
WHERE
    t1.total = t2.total

10. 查询没有学全所有课的学生的学号、姓名

SELECT
    t2.s_id,
    t2.s_name 
FROM
    Student AS t2
    LEFT JOIN ( SELECT s_id, count(*) total FROM Score GROUP BY s_id ) AS t1 ON t1.s_id = t2.s_id 
WHERE
    total <>(
    SELECT
        COUNT(*) total 
    FROM
        Course 
    ) 
    OR t1.s_id IS NULL

11. 查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

SELECT
    Student.s_id,
    Student.s_name 
FROM
    Score
    JOIN Student ON Score.s_id = Student.s_id 
WHERE
    c_id IN ( SELECT c_id FROM Score WHERE s_id = '01' ) 
    AND Score.s_id <> '01' 
GROUP BY
    s_id

12. 查询和“01”号同学所学课程完全相同的其他同学的学号

SELECT
    t1.s_id,COUNT(t2.c_id)
FROM
    Score AS t1
    LEFT JOIN Score AS t2 ON t1.c_id = t2.c_id 
    AND t2.s_id = '01' 
    AND t1.s_id <> '01' 
WHERE
    t1.s_id <> '01'
    GROUP BY t1.s_id
    having count(t2.c_id)=(select count(*) FROM Score where s_id='01')

13. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT
    t1.s_id,
    t2.s_name,
    AVG( t1.s_score ) AS score 
FROM
    Score AS t1
    JOIN Student AS t2 ON t1.s_id = t2.s_id 
WHERE
    t1.s_id IN ( SELECT s_id FROM score WHERE s_score < 60 GROUP BY s_id HAVING COUNT( s_id )>= 2 ) 
GROUP BY
    t1.s_id;

14. 检索”01”课程分数小于60，按分数降序排列的学生信息

SELECT
    Score.s_id,
    Student.s_name,
    Score.s_score 
FROM
    Score
    JOIN Student ON Score.s_id = Student.s_id 
WHERE
    s_score < 60 
    AND c_id = '01' 
ORDER BY
    s_score DESC

15. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT
    t1.s_id,
    t1.s_name,
    AVG( t2.s_score ) AS 'avg',
    SUM( t2.s_score ) 'total' 
FROM
    Student AS t1
    LEFT JOIN Score AS t2 ON t1.s_id = t2.s_id 
GROUP BY
    t1.s_id 
ORDER BY
    avg DESC

16. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT
    t1.c_id '课程ID',
    t2.c_name '课程名',
    MAX( s_score ) '最高分',
    min( s_score ) '最低分',
    avg( s_score ) '平均分',
    SUM( CASE WHEN t1.s_score >= 60 THEN 1 ELSE 0 END )/ COUNT( t1.s_id ) '及格率',
    SUM( CASE WHEN t1.s_score >= 70 AND t1.s_score < 80 THEN 1 ELSE 0 END )/ COUNT( t1.s_id ) '中等率',
    SUM( CASE WHEN t1.s_score >= 80 AND t1.s_score < 90 THEN 1 ELSE 0 END )/ COUNT( t1.s_id ) '优良率',
    SUM( CASE WHEN t1.s_score >= 90 THEN 1 ELSE 0 END )/ COUNT( t1.s_id ) '优秀率' 
FROM
    Score AS t1
    JOIN Course AS t2 ON t1.c_id = t2.c_id 
GROUP BY
    t1.c_id;

17. 查询学生的总成绩并进行排名

SELECT
    t1.s_id,
    t2.s_name,
    SUM( t1.s_score ) AS 'total' 
FROM
    Score AS t1
    JOIN Student AS t2 ON t1.s_id = t2.s_id 
GROUP BY
    t1.s_id 
ORDER BY
    total DESC;

18. 查询每个老师所教课程平均分从高到低显示以课程为主体来求平均分

SELECT
    t1.t_name,
    t2.c_name,
    t3.c_id,
    avg( t3.s_score ) AS 'avg' 
FROM
    Teacher AS t1
    JOIN Course AS t2 ON t1.t_id = t2.t_id
    JOIN Score AS t3 ON t2.c_id = t3.c_id 
GROUP BY
    t3.c_id 
ORDER BY
    avg DESC;

19. 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

SELECT
    t1.c_id,t2.c_name,(
    SUM( CASE WHEN t1.s_score < 60 THEN 1 ELSE 0 END )) AS '0-60',
    (
    SUM( CASE WHEN t1.s_score >= 60 AND t1.s_score <= 70 THEN 1 ELSE 0 END )) AS '60-70',
    (
    SUM( CASE WHEN t1.s_score > 70 AND t1.s_score <= 85 THEN 1 ELSE 0 END )) AS '70-85',
    (
    SUM( CASE WHEN t1.s_score > 85 AND t1.s_score <= 100 THEN 1 ELSE 0 END )) AS '85-100' 
FROM
    Score as t1
    JOIN Course as t2 on t1.c_id=t2.c_id
GROUP BY
    t1.c_id

20. 查询学生平均成绩及其名次

SELECT
    t2.s_name,
    AVG( t1.s_score ) 'avg' 
FROM
    score AS t1
    JOIN Student AS t2 ON t1.s_id = t2.s_id 
GROUP BY
    t1.s_id 
ORDER BY
    'avg' DESC

21. 查询每门课程被选修的学生数

SELECT
    t1.c_id,
    t2.c_name,
    COUNT(t1.s_id) as total
FROM
    Score AS t1
    JOIN Course AS t2 ON t1.c_id = t2.c_id 
GROUP BY
    t1.c_id

22. 查询出只有两门课程的学生的学号和姓名

SELECT
    t1.s_id,
    t2.s_name 
FROM
    Score AS t1
    JOIN Student AS t2 ON t1.s_id = t2.s_id 
GROUP BY
    t1.s_id 
HAVING
    COUNT( t1.c_id )= 2

23. 查询男生、女生人数

SELECT
    s_sex,
    COUNT(*) as total
FROM
    Student 
GROUP BY
    s_sex

24. 查询名字中含有”风”字的学生信息

SELECT *FROM student 
WHERE s_name LIKE '%风%'

25. 查询1990年出生的学生名单

SELECT
    * 
FROM
    Student 
WHERE
    YEAR ( s_birth )= '1990'

26. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT
    t2.s_name,
    t1.s_id,
    avg( t1.s_score ) 'avg' 
FROM
    Score AS t1
    JOIN Student AS t2 ON t1.s_id = t2.s_id 
GROUP BY
    t1.s_id 
HAVING
    avg( t1.s_score )> 85

27. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

SELECT
    t1.c_id,
    t2.c_name,
    avg( t1.s_score ) 'avg' 
FROM
    Score AS t1
    JOIN Course AS t2 ON t1.c_id = t2.c_id 
GROUP BY
    t1.c_id 
ORDER BY
    avg ASC,
    t1.c_id DESC

28. 查询课程名称为”数学”，且分数低于60的学生姓名和分数

SELECT
    t2.s_name,
    t1.s_score 
FROM
    Score AS t1
    JOIN Student AS t2 ON t1.s_id = t2.s_id 
WHERE
    t1.c_id =(
    SELECT
        c_id 
    FROM
        Course 
    WHERE
        c_name = '数学' 
    ) 
    AND t1.s_score < 60

29. 查询所有学生的课程及分数情况

SELECT t1.s_id,t1.s_name,
MAX(CASE WHEN t3.c_name='语文' THEN t2.s_score ELSE NULL END)AS '语文',
MAX(CASE WHEN t3.c_name='英语' THEN t2.s_score ELSE NULL END)AS '英语'
MAX(CASE WHEN t3.c_name='数学' THEN t2.s_score ELSE NULL END)AS '数学',
FROM student AS t1 LEFT JOIN 
score AS t2 ON t1.s_id=t2.s_id LEFT JOIN 
course AS t3 ON t2.c_id=t3.c_id 
GROUP BY t1.s_name ,t1.s_id 
ORDER BY t1.s_id

30. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SELECT
    t1.s_id,
    t3.s_name,
    GROUP_CONCAT(t4.c_name ) as c_name,
    GROUP_CONCAT(t1.s_score ) as s_score
FROM
    Score AS t1
    JOIN Student AS t3 ON t1.s_id = t3.s_id
    JOIN Course AS t4 ON t1.c_id = t4.c_id 
WHERE
    t1.s_score > 70 
GROUP BY
    t1.s_id 
HAVING
    COUNT( t1.s_score )>=(
    SELECT
        COUNT( t2.s_score ) 
    FROM
        Score AS t2 
    WHERE
        t2.s_id = t1.s_id 
GROUP BY
    t2.s_id)

31. 查询不及格的课程并按课程号从大到小排列

SELECT
    t1.c_id,
    t2.c_name 
FROM
    Score AS t1
    JOIN Course AS t2 ON t1.c_id = t2.c_id 
WHERE
    t1.s_score < 60 
GROUP BY
    t1.c_id 
ORDER BY
    t1.c_id DESC

32. 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名

SELECT
    t1.s_id,
    t2.s_name
FROM
    Score as t1
    JOIN Student as t2 on t1.s_id=t2.s_id
WHERE
    t1.c_id = '03' 
    AND t1.s_score > 80 
GROUP BY
    t1.s_id

33. 查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩

SELECT
    t3.s_id,
    t4.s_name,
    t3.s_score
FROM
    Score AS t3 
    JOIN Student as t4 on t3.s_id=t4.s_id
WHERE
    t3.c_id =(
    SELECT
        t2.c_id 
    FROM
        Course AS t2 
    WHERE
        t2.t_id =(
        SELECT
            t1.t_id 
        FROM
            Teacher AS t1 
        WHERE
            t1.t_name = '张三' 
        )) 
ORDER BY t3.s_score desc 
LIMIT 1

34. 查询 2 门及以上课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT
    t1.s_id,
    t3.s_name,
    t1.c_id AS '第一门课程id',
    t1.s_score AS '第一门课程成绩',
    t2.c_id AS '第二门课程id',
    t2.s_score AS '第二门课程成绩'
FROM
    Score AS t1
    INNER JOIN Score AS t2 ON t1.s_id = t2.s_id 
    AND t1.c_id <> t2.c_id 
    AND t1.s_score = t2.s_score
    JOIN Student AS t3 ON t1.s_id = t3.s_id;

35. 查询各学生的年龄

select *,YEAR(NOW())-YEAR(s_birth) as 'age' from Student;

36. 查询选修了全部课程的学生

SELECT
    t1.s_id,
    t2.s_name,
    COUNT( t1.c_id ) AS total 
FROM
    score AS t1
    JOIN Student AS t2 ON t1.s_id = t2.s_id 
GROUP BY
    t1.s_id 
HAVING
    total =(
    SELECT
        COUNT( c_id ) 
FROM
    course)

37. 查询下周过生日的学生

SELECT
    * 
FROM
    student 
WHERE
    WEEK ( '2023-04-11' )+ 1 = WEEK (
        CONCAT(
            YEAR (
            NOW()),
    SUBSTRING( s_birth, 5, 6 )))

38. 查找本月过生日的人

SELECT
    * 
FROM
    student 
WHERE
    MONTH (
    NOW())= MONTH (
    s_birth)

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2023-07-03 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

暂无评论

编辑精选文章

换一批

万字详解高可用架构设计

1486

Go 开发者必备：Protocol Buffers 入门指南

亿级月活的社交 APP，陌陌如何做到 3 分钟定位故障？

654

60页PPT全解：DeepSeek系列论文技术要点整理

1347

小学生SQL50题

sql

已知有如下4张表：学生表： student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别课程表： course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号教师表： teacher(t_id,t_name) –教师编号,教师姓名成绩表： ccore(s_id,c_id,s_s_score) –学生编号,课程编号,分数要求，根据以上信息按照下面要求写出对应的SQL语句。看看你能答对几道题？

大数据真好玩

2019/08/08

8560

SQL学习笔记之SQL查询练习1

其他

–1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 –3.教师表 Teacher(t_id,t_name) –教师编号,教师姓名 –4.成绩表 Score(s_id,c_id,s_score) –学生编号,课程编号,分数

Jetpropelledsnake21

2018/08/01

6130

深夜小酌，50道经典SQL题，真香~

sql

现在是6月9号00:15分，花了近3小时撸完这小50题，有点困了，不想对所谓标准答案了。。心中有猛虎，何必细嗅蔷薇？

陈哈哈

2022/06/12

9480

MySQL经典50题：面试必备

云数据库 SQL Server 云数据库 MySQL 数据库 TDSQL MySQL 版

标题 MySQL经典50题解析及答案作者 Peter 微信 756803877 公众号尤而小屋时间 2021-09-02 MySQL经典50题解析及答案下面是网传经典的MySQL50题的习题及参考答案💪，供参考和学习，有更好的方法或者不恰当的地方，欢迎提出来  题目1 题目要求查询"01"课程比"02"课程成绩高的学生的信息及课程分数 SQL实现 -- 方法1 select a.* ,b.s_score as 1_score ,c.s_score

皮大大

2021/09/02

1.9K0

Java面试——SQL语句题

having union

【1】查询"01"课程比"02"课程成绩高的学生的信息及课程分数：当对一张表中的一列数据比较时，应当将一张表拆分为两张表；

Java架构师必看

2021/05/14

6010

MySQL50题-分类总结

sql 数据库云数据库 SQL Server

笔者最近将网上流传的MySQL数据库经典50题进行了练习，梳理了一份自己的练习成果。下图是MySQL练习题中涉及到的4张表和它们的具体字段：

皮大大

2021/03/01

8060

数据分析sql面试必会6题经典_数据分析师SQL面试必备50题[通俗易懂]

case count join select sum

以下是SQL面试必备的经典的50道题目，每道题都有博主本人的解题思路和对应的SQL语句。

全栈程序员站长

2022/09/07

1.5K0

MySQL 经典30题，拿走不谢！！！

sql

这里将开始我们的 sql 之旅，在这里希望对 sql 能力稍弱的同学，有一定的帮助。如果大家在以下 sql 学习中，发现更具有优化性的建议，可以留言给小编或者加技术群交流，让我们一起成长。（底部有WeChat方式）

八点半的Bruce、D

2020/06/09

1.2K0

MySQL50-5-第11-15题

sql

自己的方法一开始在课程的最大数中没有使用Course表，导致多使用了一个临时表的结果，现在改成使用Course表的统计值（3）作为课程的总数：

皮大大

2021/03/01

7100

MySQL50-6-第16-20题

sql

学生信息：Student-------s_id,s_name,s_sex,s_birth

皮大大

2021/03/01

3820

50道MySQL面试题，经典~

sql

好了，请打开你亲爱的navicat，关闭百度、手机等一切阻碍你进步的绊脚石。开始你的表演~

码农编程进阶笔记

2022/08/18

9620

经典的SparkSQL/Hive-SQL/MySQL面试-练习题

sql hive mysql

32.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

大数据学习与分享

2020/10/23

1.3K0

SQL练习题共50道附答案（MySQL）

sql mysql

大家可以根据自己的查询需求更改数据,如果你sql很6那么当然最好啦,如果不是特别厉害建议理解这50道Sql题目,完事你会发现自己进步蛮大!!!加油

JAVA葵花宝典

2019/11/18

1K0

超经典MySQL练习50题，做完这些你的SQL就过关了！

sql 数据库云数据库 SQL Server

相信大多学习了 Mysql 数据库语言的同学都会上网找练习来练手，而大部分的人肯定知道有一篇 Mysql 经典练习题50题的帖子，上面的题目基本上涵盖了 Mysql 查询语句的关键知识点。

小F

2020/12/16

4.9K0

mysql 练习题及答案 50道

count having join select

— 1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号。 — 方法1 select * from score a inner join score b on (a.s_id = b.s_id and a.c_id=’01’ and b.c_id=’02’ and a.s_score>b.s_score); — 方法2 select * from score a inner join score b where (a.s_id = b.s_id and a.c_id=’01’ and b.c_id=’02’ and a.s_score>b.s_score); — 方法3 select * from (select * from score where c_id=’01’) as a inner join (select * from score where c_id=’02’) as b on a.s_id=b.s_id where a.s_score>b.s_score;

全栈程序员站长

2022/09/30

5410

MySQL练习题

join mysql select 权限数据库

用户11097514

2024/05/30

1060

Mysql Sql 语句练习题（50道）

sql mysql

MySql 语句练习50题表名和字段 –1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 –3.教师表 Teacher(t_id,t_name) –教师编号,教师姓名 –4.成绩表 Score(s_id,c_id,s_score) –学生编号,课程编号,分数测试数据 --建表 --学生表 CREATE TA

梅花

2020/09/28

6430

【SQL测试题】SQL编程测试

编程测试 sql insert varchar

3. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)

哈__

2024/04/08

1430

Hive SQL50道练习题

hive csv data export vi

– 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩: – (包括有成绩的和无成绩的)

王知无-import_bigdata

2020/04/07

7.6K0

【Mysql学习之旅-2】经典sql面试题及答案分析

sql 数据库云数据库 SQL Server

1、学生表 student（s_id：学生id，s_name：学生姓名，s_birth：学生生日，s_sex：学生性别）：

云深i不知处

2020/09/16

1.5K0