SQL经典50题总结

已于 2023-05-28 09:28:22 修改
阅读量2k 收藏 8
点赞数
分类专栏： # mysql 文章标签： sql 数据库 mysql
于 2023-04-24 18:40:56 首次发布
伟仔
本文链接：https://blog.csdn.net/weixin_42439274/article/details/130349293
版权

SQL经典50题总结

SQL经典50题总结

1.数据准备

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50730
 Source Host           : localhost:3306
 Source Schema         : sql50

 Target Server Type    : MySQL
 Target Server Version : 50730
 File Encoding         : 65001

 Date: 05/07/2022 18:15:43
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `CId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Cname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `TId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语⽂', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `SId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `CId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `score` decimal(18, 1) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('01', '01', 80.0);
INSERT INTO `sc` VALUES ('01', '02', 90.0);
INSERT INTO `sc` VALUES ('01', '03', 99.0);
INSERT INTO `sc` VALUES ('02', '01', 70.0);
INSERT INTO `sc` VALUES ('02', '02', 60.0);
INSERT INTO `sc` VALUES ('02', '03', 80.0);
INSERT INTO `sc` VALUES ('03', '01', 80.0);
INSERT INTO `sc` VALUES ('03', '02', 80.0);
INSERT INTO `sc` VALUES ('03', '03', 80.0);
INSERT INTO `sc` VALUES ('04', '01', 50.0);
INSERT INTO `sc` VALUES ('04', '02', 30.0);
INSERT INTO `sc` VALUES ('04', '03', 20.0);
INSERT INTO `sc` VALUES ('05', '01', 76.0);
INSERT INTO `sc` VALUES ('05', '02', 87.0);
INSERT INTO `sc` VALUES ('06', '01', 31.0);
INSERT INTO `sc` VALUES ('06', '03', 34.0);
INSERT INTO `sc` VALUES ('07', '02', 89.0);
INSERT INTO `sc` VALUES ('07', '03', 98.0);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `SId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Sage` datetime(0) NULL DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `student` VALUES ('03', '孙⻛', '1990-12-20 00:00:00', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-12-06 00:00:00', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '⼥');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-01-01 00:00:00', '⼥');
INSERT INTO `student` VALUES ('07', '郑⽵', '1989-01-01 00:00:00', '⼥');
INSERT INTO `student` VALUES ('09', '张三', '2017-12-20 00:00:00', '⼥');
INSERT INTO `student` VALUES ('10', '李四', '2017-12-25 00:00:00', '⼥');
INSERT INTO `student` VALUES ('11', '李四', '2012-06-06 00:00:00', '⼥');
INSERT INTO `student` VALUES ('12', '赵六', '2013-06-13 00:00:00', '⼥');
INSERT INTO `student` VALUES ('13', '孙七', '2014-06-01 00:00:00', '⼥');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `TId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Tname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');

SET FOREIGN_KEY_CHECKS = 1;

库表结构

在这里插入图片描述

学生表 student

教师表 teacher

课程信息表 course

成绩表 sc

50题

有些例题比较简单直接略过！

1.查询"01" 课程比 “02” 课程成绩高的学生的信息及课程分数

考点总结：考察联表语句应用，多表联表。

SELECT
	A.*,
	sc1.sc1_core,
	sc2.sc2_core 
FROM
	student AS A
	LEFT JOIN ( SELECT SId, score AS sc1_core FROM sc WHERE CId = '01' ) AS sc1 ON A.SId = sc1.SId
	LEFT JOIN ( SELECT SId, score AS sc2_core FROM sc WHERE CId = '02' ) AS sc2 ON A.SId = sc2.SId 
WHERE
	sc1_core > sc2_core

SId	Sname	Sage	Ssex	sc1_core	sc2_core
02	钱电	1990-12-21 00:00:00	男	70.0	60.0
04	李云	1990-12-06 00:00:00	男	50.0	30.0

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

考点总结：

1.考察having的使用，跟函数有关的条件语句要用having不能用where

2.group by的使用 通过sid分组而计算平均值

SELECT
	SId,
	avg( score ) AS avgcore 
FROM
	sc 
GROUP BY
	SId 
HAVING
	avgcore > 60

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

考点总结：

1.考察ifnull 函数使用

2.考察 sum 函数和case when语句的使用，注意case when 后边要加end

3.count函数的使用

SELECT
	a.Sid,
	a.Sname,
	count( sc.Cid ),
	IFNULL( sum( sc.score ), 0 ) --如果为null则赋值为0
  -- sum( CASE WHEN sc.score IS NULL THEN 0 ELSE sc.score END )
FROM
	student AS a
	LEFT JOIN sc ON sc.SId = a.Sid 
GROUP BY
	a.Sid,a.Sname

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

select count(*) from teacher where Tname like '张%'

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

SELECT
	SId,
	Sname 
FROM
	student 
WHERE
	SId NOT IN (
	SELECT DISTINCT
		a.SId 
	FROM
		student a
		LEFT JOIN sc b ON a.SId = b.SId
		LEFT JOIN course c ON b.CId = c.CId
		LEFT JOIN teacher d ON c.TId = d.TId 
	WHERE
	d.Tname = '张三' 
	)

6.查询学过张三老师所教的所有课程的同学的学号和姓名

SELECT
	a.SId,
	a.Sname 
FROM
	student a
	LEFT JOIN sc b ON a.SId = b.SId
	LEFT JOIN course c ON b.CId = c.CId
	LEFT JOIN teacher d ON c.TId = d.TId 
WHERE
	d.Tname = '张三'

7.查询学过01课程并且学过02课程的学生

方法一 直接用条件查询

方法二联表晒出交集 注意这就涉及了join 的八种变换，详细见总结过的联表变换

数据库总结之八种常用join变化

-- 方法一
select SId,Sname from student where SId in 
(select SId from sc WHERE CId = '01')
and SId  in 
(select SId from sc where CId = '02')

-- 方法二
SELECT
	* 
FROM
	student 
WHERE
	student.sid IN (
	SELECT
		a.sid 
	FROM
		( SELECT sid FROM sc WHERE sc.CId = '01' ) AS a
	INNER JOIN ( SELECT sid FROM sc WHERE sc.CId = '02' ) AS b ON a.sid = b.sid 
	)

8.查询学过02课程的总成绩

SELECT sum(score) from sc where CId = '02'

9.查询所有课程成绩小于60的学生

用不及格的科目数等于所有科目数来判断所有科都不及格。

SELECT
	a.sid,
	student.sname
FROM
	( SELECT sc.sid, count( sc.CId ) AS acount FROM sc WHERE sc.score < 60 GROUP BY sc.sid )
	AS a
	INNER JOIN 
	( SELECT sc.sid, count( sc.CId ) AS bcount FROM sc GROUP BY sc.sid ) 
	AS b ON a.sid = b.sid 
	inner join student on student.sid = a.sid
WHERE
	a.acount = b.bcount

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

SELECT
	a.SId,
	a.Sname 
FROM
	student a
	LEFT JOIN sc b ON a.SId = b.SId 
GROUP BY
	a.SId,
	a.Sname 
HAVING
	count(DISTINCT b.cid ) < (
	SELECT
		count( cid ) 
	FROM
	course 
	)

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

SELECT
	student.sid,
	student.sname 
FROM
	student
	INNER JOIN (
	SELECT DISTINCT
		sc.SId 
	FROM
		sc 
	WHERE
		sc.CId IN ( SELECT sc.cid FROM sc WHERE sc.SId = '01' ) 
	AND sc.SId != '01' 
	) AS a ON a.SId = student.sid

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

SELECT
	* 
FROM
	student 
WHERE
	SId IN (SELECT SId 
			FROM
		(SELECT	* FROM	sc a WHERE CId IN ( SELECT CId FROM sc WHERE SId = 01 )) b 
	GROUP BY SId 
	HAVING
		count( CId ) =(SELECT count( CId ) FROM sc c WHERE SId = 01 )) 
	AND SId != 01

13.查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT
	student.sid,
	student.sname 
FROM
	student 
WHERE
	student.sid NOT IN (
	SELECT
		sc.sid 
	FROM
		sc
		INNER JOIN course ON sc.CId = course.CId
		INNER JOIN teacher ON course.tid = teacher.TId 
	AND teacher.Tname = '张三' 
	)

15.查询两门及其以上不及格的同学的学号、姓名和平均成绩

SELECT
	sc.sid,
	student.sname,
	avg( sc.score ) 
FROM
	sc
	INNER JOIN student ON student.sid = sc.sid 
WHERE
	sc.score < 60 GROUP BY sid HAVING count( DISTINCT sc.cid ) >=2

16.(略)检索01课程分数小于60 ，按课程分数降序排列学生信息

SELECT
	student.sid,
	student.sname 
FROM
	sc
	INNER JOIN student ON sc.sid = student.sid 
WHERE
	sc.cid = '01' 
	AND sc.score < 60 
ORDER BY
	sc.score DESC

17.按平均成绩从高到低显示所有学生的所有课程的成绩和平均成绩

SELECT
	sc.sid,
	max( CASE WHEN sc.cid = '01' THEN sc.score ELSE NULL END ) '语文',
	max( CASE WHEN sc.cid = '02' THEN sc.score ELSE NULL END ) '数学',
	max( CASE WHEN sc.cid = '03' THEN sc.score ELSE NULL END ) '英语',
	avg( sc.score ) 
FROM
	sc 
GROUP BY
	sc.sid 
ORDER BY
	avg( sc.score ) DESC

18.查询各科成绩最高分、最低分、平均分、及格率、中等率、优良率、优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT
	course.cid,
	course.Cname,
	max( sc.score ) '最高分',
	min( sc.score ) '最低分',
	avg( sc.score ) '平均分',
	sum( CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END )/ count( sc.SId ) '及格',
	sum( CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END )/ count( sc.SId ) '中等',
	sum( CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END )/ count( sc.SId ) '良好',
	sum( CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END )/ count( sc.SId ) '优秀' 
FROM
	course
	INNER JOIN sc ON sc.cid = course.cid 
GROUP BY
	course.cid

19.按各科成绩进行排序，并显示排名

SELECT
	sc.sid,
	sc.cid,
	sc.score,
	ROW_NUMBER() over ( ORDER BY sc.score DESC ) 
FROM
	sc

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

SELECT
	sc.SId,
	sum( sc.score ) 
FROM
	sc 
GROUP BY
	sc.sid 
ORDER BY
	sum( sc.score ) DESC

21.查询不同老师所教不同课程平均分从高到低展示

SELECT
	teacher.Tname,
	course.Cname,
	avg( sc.score ) 
FROM
	teacher
	INNER JOIN course ON teacher.TId = course.tid
	INNER JOIN sc ON course.cid = sc.cid 
GROUP BY
	sc.cid 
ORDER BY
	avg( sc.score ) DESC

22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

-- 窗口函数
SELECT
	* 
FROM
	(
	SELECT
		*,
		ROW_NUMBER() over ( PARTITION BY sc.cid ORDER BY sc.score DESC ) m 
	FROM
		sc
		INNER JOIN student stu ON stu.sid = sc.sid 
	) a 
WHERE
	m IN ( 2, 3 )

23.使用分段来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

SELECT
	course.cid,
	course.Cname,
	count( CASE WHEN sc.score >= 60 THEN 1 ELSE NULL END ) '及格',
	count( CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE NULL END ) '中等',
	count( CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE NULL END ) '良好',
	count( CASE WHEN sc.score >= 90 THEN 1 ELSE NULL END ) '优秀' 
FROM
	course
	INNER JOIN sc ON sc.cid = course.cid 
GROUP BY
	course.cid

24.查询学生平均成绩以及排名

-- 窗口函数
SELECT
	sc.sid,
	avg(sc.score),
	ROW_NUMBER() over ( ORDER BY avg(sc.score) DESC ) 
FROM
	sc

25.查询各科成绩前三名的记录（不考虑成绩并列情况）

-- 窗口函数
SELECT
	* 
FROM
	(
	SELECT
		*,
		ROW_NUMBER() over ( PARTITION BY sc.cid ORDER BY sc.score DESC ) m 
	FROM
		sc
		INNER JOIN student stu ON stu.sid = sc.sid 
	) a 
WHERE
	m IN ( 1,2, 3 )

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

SELECT
	course.cid,
	count( sc.sid ) 
FROM
	sc
	INNER JOIN course ON sc.CId = course.cid 
GROUP BY
	course.cid

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

SELECT
	student.sid,
	student.sname 
FROM
	student
	INNER JOIN sc ON student.sid = sc.sid 
GROUP BY
	sc.sid 
HAVING
	count( sc.cid )=2

28.查询男生、女生人数

SELECT
	student.ssex,
	count( student.sid ) 
FROM
	student 
GROUP BY
	student.ssex

29. 查询名字中含有"三"字的学生信息

SELECT
	student.sid,
	student.sname 
FROM
	student 
WHERE
	student.sname LIKE '%三%'

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

SELECT
	student.sid,
	student.sname 
FROM
	student 
WHERE
	YEAR(sage) =1990
-- 或者是用like
SELECT
	student.sid,
	student.sname 
FROM
	student 
WHERE
	student.sage like '1990%'

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

SELECT
	student.sid,
	student.sname,
	avg( sc.score ) 
FROM
	sc
	INNER JOIN student ON sc.sid = student.sid 
GROUP BY
	sc.sid 
HAVING
	avg(sc.score )

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

SELECT
	sc.cid,
	avg( sc.score ) 
FROM
	sc 
GROUP BY
	sc.cid 
ORDER BY
	avg( sc.score ) ASC,
	sc.cid DESC

34.(略)查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT
	student.sid,
	student.sname,
	sc.score 
FROM
	sc
	INNER JOIN student ON student.sid = sc.sid
	INNER JOIN course ON course.cid = sc.CId AND course.cname = '数学' 
WHERE
	sc.score < 60

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

SELECT
	student.sid,
	student.sname,
	max( CASE WHEN course.cname = '语文' THEN sc.score ELSE NULL END ) '语文',
	max( CASE WHEN course.cname = '数学' THEN sc.score ELSE NULL END ) '数学',
	max( CASE WHEN course.cname = '英语' THEN sc.score ELSE NULL END ) '英语' 
FROM
	student
	INNER JOIN sc ON sc.sid = student.sid
	INNER JOIN course ON sc.CId = course.CId 
GROUP BY
	student.sid

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

SELECT
	student.sname,
	course.cname,
	sc.score 
FROM
	sc
	INNER JOIN student ON student.sid = sc.sid
	INNER JOIN course ON sc.cid = course.cid 
WHERE
	sc.score > 70

37.(略)查询不及格的课程并按课程号从大到小排列

SELECT
	student.sid,
	student.sname,
	course.cname,
	sc.cid,
	sc.score 
FROM
	sc
	INNER JOIN course ON course.cid = sc.cid
	INNER JOIN student ON student.sid = sc.SId 
WHERE
	sc.score < 60 
ORDER BY
	sc.cid DESC

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

SELECT
	student.sname,
	sc.score 
FROM
	sc
	INNER JOIN student ON student.sid = sc.SId 
WHERE
	sc.CId = '03' 
	AND sc.score > 80

39.求每门课程的学生人数

SELECT
  course.cid,
	count( distinct course.sid ) 
FROM
	course
	INNER JOIN sc ON sc.CId = course.cid 
GROUP BY
	course.cid

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

SELECT
	student.sname,
	sc.score 
FROM
	sc
	INNER JOIN student ON sc.sid = student.sid
	INNER JOIN course ON sc.cid = course.cid
	INNER JOIN teacher ON teacher.tid = course.TId 
	AND teacher.Tname = '张三' 
ORDER BY
	sc.score DESC 
	LIMIT 1

41.查询某个不同课程但成绩相同的学生编号、课程编号、学生成绩

SELECT
	sid 
FROM
	(SELECT b.sid,b.score 
	 FROM sc AS b
	 INNER JOIN ( SELECT sid FROM sc GROUP BY sid HAVING count( DISTINCT cid )> 1 ) AS c ON b.sid = c.sid 
	GROUP BY
		b.sid,
		b.score 
	) AS a 
GROUP BY sid HAVING	count( sid )=1

42.查询每一门课程最好的前两名

SELECT
	sc.cid,
	COUNT( 1 ) cnt 
FROM
	sc 
GROUP BY
	sc.cid 
HAVING
	count( 1 )> 5 
ORDER BY
	cnt DESC,cid ASC

43.统计每门课程的学生选修人数（超过5人的课程才统计）。

44.检索至少选修两门课程的学生学号

SELECT
	student.sid,
	student.sname 
FROM
	student
	INNER JOIN sc ON student.sid = sc.sid 
GROUP BY
	sc.sid 
HAVING
	count( sc.cid )>=2

45.查询选修了全部课程的学生信息

SELECT
	student.sid,
	student.sname,
	count( sc.cid ) cnt 
FROM
	student
	INNER JOIN sc ON sc.sid = student.sid 
GROUP BY
	sc.sid 
HAVING
	cnt = (SELECT count( cid ) FROM course)
函数描述
NOW()返回当前的日期和时间
CURDATE()返回当前的日期
CURTIME()返回当前的时间
DATE()提取日期或日期/时间表达式的日期部分
EXTRACT()返回日期/时间按的单独部分
DATE_ADD()给日期添加指定的时间间隔
DATE_SUB()从日期减去指定的时间间隔
DATEDIFF()返回两个日期之间的天数
DATE_FORMAT()用不同的格式显示日期/时间

46.(略)查询各学生的年龄

SELECT
	Sname,
IF
	(
		m.MONTH != 0,
		CONCAT( m.YEAR, "岁", m.MONTH, "个月" ),
	CONCAT( m.YEAR, "岁" )) "年龄" 
FROM
	(
	SELECT
		Sname,
		FLOOR( DATEDIFF( CURRENT_DATE, Sage ) / 365 ) YEAR,
		ROUND(( DATEDIFF( CURRENT_DATE, Sage ) % 365 ) / 30 ) MONTH 
	FROM
	Student 
	) m
	
	-- 不精确到月的话
SELECT
	sname,
	TIMESTAMPDIFF(YEAR,sage,CURDATE()) 
FROM
	student

47.(略)查询本周过生日的学生

SELECT
	* 
FROM
	student 
WHERE
	WEEK ( sage, 1 )= WEEK ( CURDATE(), 1 );

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

SELECT
	* 
FROM
	student 
WHERE
	WEEK ( sage, 1 )= WEEK ( CURDATE(), 1 ) + 1;

49.(略)查询本月过生日的学生

SELECT
	* 
FROM
	student 
WHERE
	month ( sage)= month (CURDATE());

50.查询下月过生日的学生

SELECT
	* 
FROM
	student 
WHERE
CASE
		
		WHEN MONTH ( sage )= 12 THEN
	MONTH ( sage )= 1 ELSE MONTH ( sage )= MONTH (
	CURDATE())+ 1 END;
一个双鱼座的测开
关注
  • 0
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
50道SQL练习题及答案与详细分析
12-14
50道SQL练习题及答案与详细分析 数据表介绍 –1.学生表 Student(SId,Sname,Sage,Ssex) –SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 –2.课程表 Course(CId,Cname,TId) –CId 课程编号,Cname 课程名称,TId 教师编号 –3.教师表 Teacher(TId,Tname) –TId 教师编号,Tname 教师姓名 –4.成绩表 SC(SId,CId,score) –SId 学生编号,CId 课程编号,score 分数 学生表 Student CREATE TABLE Student ( SId
常见的SQL面试题：经典50例
2401_84046677的博客
04-07 3829
本文从基础到高级再到实战，由浅入深，把MySQL讲的清清楚楚，明明白白，这应该是我目前为止看到过最好的有关MySQL的学习笔记了，我相信如果你把这份笔记认真看完后，无论是工作中碰到的问题还是被面试官问到的问题都能迎刃而解！MySQL50道高频面试题整理：《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》点击传送门即可获取！每组最大的N条记录案例：查询各科成绩前两名的记录第1步，查出有哪些组我们可以按课程号分组，查询出有哪些组，对应这个问题里就是有哪些课程号。
SQL经典50题
纸上得来终觉浅，绝知此事要躬行
07-07 1万+
SQL经典50题参考自B站视频
经典50道SQL练习题
weixin_42408447的博客
08-21 2524
sql练习题
SQL 50 题（MySQL 版，包括建库建表、插入数据等完整过程，适合复习 SQL 知识点）
代码星辰的博客
11-16 1万+
本文整理了经典的 50 道 SQL 题目，文本分为建库建表、插入数据以及 SQL 50 题这三个部分。
sql语句练习50题(含解析)
热门推荐
m0_45210394的博客
09-21 1万+
sql练习笔记
经典SQL面试题总结.doc
最新发布
07-15
数据库
2023年经典SQL面试题.doc
11-08
经典SQL面试题 这篇文章主要介绍了经典SQL面试题，涵盖了基本的SQL语句、数据表设计、数据查询、数据分析等方面的知识点。 数据表设计 在这篇文章中，我们可以看到四个数据表的设计：学生表（Student）、课程表...
SQL经典练习题_sql练习_
10-03
本资料包中的"SQL经典练习题.sql练习."包含了个人收藏的一系列SQL题目和案例，这些内容常常在面试中出现，对提升SQL技能和准备面试具有很高的价值。 首先，我们要了解SQL的基本概念。SQL主要包括以下几个部分： 1....
经典SQL语句50题
12-02
一个项目涉及到50个SQL语句。（经典）
sql高级查询50题.docx
10-10
"SQL高级查询50题" SQL 高级查询是数据库管理中的一种重要技术，能够帮助开发者和数据库管理员更好地管理和维护数据库。下面是50个 SQL 高级查询题目的知识点总结： 查询“001”课程比“002”课程成绩高的所有...
2023年SQL数据库经典面试题修改笔试题有答案.doc
11-07
在本文中，我们将对SQL数据库经典面试题修改笔试题进行总结，涵盖了数据库抽出部门、平均工资、字符串顺序排序、最小值、每个Customer的Revenue总和、选出score最高的一条记录等多方面的知识点。 一、数据库抽出...
50道SQL练习题
皮卡丘的情绪
06-14 1万+
50道SQL练习题
SQL练习题共50道附答案（MySQL）
JAVA葵花宝典
11-16 3796
大家可以根据自己的查询需求更改数据,如果你sql很6那么当然最好啦,如果不是特别厉害建议理解这50道Sql题目,完事你会发现自己进步蛮大!!!加油1.学生表student(s_id,s_name,s_birth,s_sex)-- 学生编号,学生姓名, 出生年月,学生性别2.课程表course(c_id,c_name,t_id)-- 课程编号, 课程名称, 教师编号3.教师表teach...
常见的SQL面试题：经典50题（简单）
yeewingho的学习笔记
05-18 9515
题目来源：知乎猴子 常见的SQL面试题：经典50题 一、简单查询 1. 查询姓“猴”的学生名单 select * from student where sname like "猴%" 2.查询姓“孟”老师的个数 select count(*) from teacher where tname like "孟%" 二、汇总分析 1.查询课程编号为“0002”的总成绩 select sum(score) from score where cid="0002" 2.查询选了课程的学生人数
经典sql面试及答案（50）
chuyinquan2438的博客
10-10 1108
经典sql面试及答案（50） （新链接）SQL 经典五十道题 联想笔记本数据库csdn1010 复习 重点知识/关键词 like、rlike MySQL EXTRACT() 函数 行列转换&多行合并为一行（第40）【数据库表行列转置的实现_My...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

一个双鱼座的测开

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值