45个sql练习

最新推荐文章于 2022-09-13 09:50:33 发布
最新推荐文章于 2022-09-13 09:50:33 发布
阅读量236 收藏 1
点赞数
分类专栏： 数据库 文章标签： sql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/THB66666/article/details/121806728
版权

有几个写的有问题，仅仅只是为了写sql的时候参考一下

#from–on–join–where–group by（分组）–having（分组数据过滤）
#-- select–distinct（去重）–order by（升降序）
#查询全部
SELECT student.*,Course.`Cname`,sc.`score` FROM Student JOIN sc ON 
Student.sid=sc.`SId` JOIN Course ON Course.`CId`=sc.`CId` 

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

SELECT s1.sid,s1.sname,s1.cname,s1.score,s2.cname,s2.score FROM 
(SELECT sc.`SId` ,student.sname AS  sname,Course.`Cname`,sc.`score` AS score FROM Student JOIN sc ON 
Student.sid=sc.`SId` JOIN Course ON Course.`CId`=sc.`CId` WHERE sc.`CId`='01' )AS s1, 
(SELECT sc.`SId` ,Course.`Cname`,sc.`score` AS score FROM Student JOIN sc ON 
Student.sid=sc.`SId` JOIN Course ON Course.`CId`=sc.`CId` WHERE sc.`CId`='02' )AS s2 
WHERE s1.sid=s2.sid AND s1.score>s2.score


 #  1.1 查询同时存在" 01 "课程和" 02 "课程的情况
 
 SELECT * FROM 
(SELECT sc.`SId` ,student.sname AS  sname,Course.`Cname`,sc.`score` AS score,sc.`CId` FROM Student JOIN sc ON 
Student.sid=sc.`SId` JOIN Course ON Course.`CId`=sc.`CId` WHERE sc.`CId`='01' )AS s1, 
(SELECT sc.`SId` ,Course.`Cname`,sc.`score` AS score,sc.`CId` FROM Student JOIN sc ON 
Student.sid=sc.`SId` JOIN Course ON Course.`CId`=sc.`CId` WHERE sc.`CId`='02' )AS s2
WHERE s1.sid=s2.sid #AND s1.cid is not null and s2.cid IS NOT NULL 
 
 #  1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(==不存在时显示为 null== )
 
 SELECT * FROM 
(SELECT sc.`SId` ,student.sname AS  sname,Course.`Cname`,sc.`score` AS score,sc.`CId` FROM Student JOIN sc ON 
Student.sid=sc.`SId` JOIN Course ON Course.`CId`=sc.`CId` WHERE sc.`CId`='01' )AS s1 LEFT JOIN 
(SELECT sc.`SId` ,Course.`Cname`,sc.`score` AS score,sc.`CId` FROM Student JOIN sc ON 
Student.sid=sc.`SId` JOIN Course ON Course.`CId`=sc.`CId` WHERE sc.`CId`='02' )AS s2
ON s1.sid=s2.sid 
 #  1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT * FROM sc WHERE sc.`CId`='02' AND sc.`SId` NOT IN (SELECT sc.`SId` FROM sc WHERE sc.`CId`='01')


#2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT student.`SId`,student.`Sname`,AVG(score) FROM student JOIN sc ON student.`SId`=sc.`SId`
GROUP BY sc.`SId` HAVING AVG(score)>=60

#3. 查询在 SC 表存在成绩的学生信息
SELECT * FROM sc WHERE sc.`score` IS NOT NULL 
#4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT Student.`SId`,Student.`Sname` ,COUNT(sc.`CId`)AS '选课总数',SUM(sc.`score`)AS '总成绩' 
FROM sc LEFT JOIN Student ON sc.`SId`=Student.`SId` GROUP BY Student.`SId`


 #  4.1 查有成绩的学生信息
 SELECT * FROM Student WHERE EXISTS (SELECT * FROM sc WHERE sc.`SId`=Student.`SId` )
#5. 查询「李」姓老师的数量 
SELECT COUNT(Teacher.`TId`)AS '数量' FROM Teacher WHERE Teacher.`Tname`LIKE '李%'
#6. 查询学过「张三」老师授课的同学的信息 
SELECT Student.* FROM Student INNER JOIN sc ON Student.`SId`=sc.`SId` INNER JOIN Course ON 
sc.`CId`=Course.`CId` INNER JOIN Teacher ON Teacher.`TId`=Course.`TId` AND  Teacher.`Tname`='张三' 
#7. 查询没有学全所有课程的同学的信息 
#select distinct Student.* from Student inner join sc on  Student.`SId`=sc.`SId` inner join Course  on  sc.`CId`=Course.`CId` 


SELECT DISTINCT Student.* FROM Student,(SELECT Student.`SId`,Course.cid FROM Student,Course)AS s1 LEFT JOIN
(SELECT sc.`SId`,sc.`CId` FROM sc)AS s2 ON s1.sid=s2.sid AND s1.cid=s2.cid WHERE s1.sid=Student.`SId` AND s2.cid IS NULL

#8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息 
SELECT DISTINCT Student.* FROM Student,sc WHERE sc.`CId` IN (SELECT sc.`CId` FROM sc WHERE sc.`SId`='01') AND student.`SId`=sc.`SId`

#9. 查询和" 01 "号的同学学习的课程   完全相同的其他同学的信息 
SELECT sc.`CId` FROM student,sc WHERE sc.`SId`='01'
SELECT student.* FROM student,sc WHERE sc.`CId` 


SELECT DISTINCT student.`SId`,s.cid FROM student,(SELECT sc.`CId` FROM student,sc WHERE sc.`SId`='01')AS s


SELECT DISTINCT student.* FROM student,(SELECT  student.`SId`,s.cid FROM student,
(SELECT sc.`CId` FROM student,sc WHERE sc.`SId`='01')AS s)AS s1 LEFT JOIN sc ON 
s1.sid=sc.`SId` AND s1.cid=sc.`CId` WHERE student.`SId`=s1.sid AND sc.`SId` IS NULL

#10. 查询没学过"张三"老师讲授的任一门课程的学生姓名 
SELECT student.`Sname` FROM student
SELECT course.`CId` FROM teacher,course WHERE teacher.`TId`=course.`TId`  AND teacher.`Tname`='张三'


SELECT student.`Sname` FROM student,(SELECT course.`CId` FROM teacher,course WHERE 
teacher.`TId`=course.`TId`  AND teacher.`Tname`='张三')AS s LEFT JOIN sc WHERE s.cid=sc.`CId`

SELECT * FROM student WHERE student.`SId` NOT IN (
SELECT student.`SId` FROM student LEFT JOIN sc ON student.`SId`=sc.`SId` LEFT JOIN course ON	
sc.`CId`=course.`CId` LEFT JOIN teacher ON teacher.`TId`=course.`TId` WHERE teacher.`Tname`='张三'

)

SELECT * FROM student WHERE student.`SId` NOT IN (
SELECT student.`SId` FROM student LEFT JOIN sc ON student.`SId`=sc.`SId`
WHERE EXISTS (
SELECT * FROM teacher,course
WHERE teacher.`Tname`='张三' AND teacher.`TId`=course.`TId` AND course.`CId`=sc.`CId`
)
)


#11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
 SELECT student.sid,student.`Sname`,AVG(sc.`score`)AS '平均成绩' FROM student,sc 
 WHERE sc.`score` < 60 AND student.`SId`=sc.`SId`
 GROUP BY sc.`SId`
 HAVING COUNT(sc.`score` >= 2 )
 
 
#12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT student.*,sc.`score` FROM student,sc WHERE student.`SId`=sc.`SId` AND sc.`CId`='01' AND sc.`score` < 69
ORDER BY sc.`score` DESC


SELECT sc.SId,sc.CId,sc.score,t1.avgscore 
FROM  sc LEFT JOIN (SELECT sc.SId,AVG(sc.score) AS avgscore FROM sc GROUP BY sc.SId) AS t1 ON sc.SId =t1.SId 
ORDER BY t1.avgscore DESC
#13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT * FROM sc LEFT JOIN (SELECT sc.`SId`,AVG(sc.`score`)AS av 
FROM sc GROUP BY sc.`SId`)AS s1 ON sc.`SId`=s1.sid
ORDER BY s1.av DESC


#14. 查询各科成绩最高分、最低分和平均分
   #以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
   #及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
   #要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT sc.`CId`,COUNT(*) AS '选修人数', MAX(sc.`score`)AS '最高分',MIN(sc.`score`)AS '最低分',AVG(sc.`score`)AS '平均分',
SUM(CASE WHEN sc.`score`>=60 THEN 1 ELSE 0 END)/COUNT(*) AS '及格率',
SUM(CASE WHEN sc.`score`>=70 AND sc.`score` < 80 THEN 1 ELSE 0 END)/COUNT(*) AS '中等率',
SUM(CASE WHEN sc.`score`>=80 AND sc.`score` < 90 THEN 1 ELSE 0 END)/COUNT(*) AS '优良率',
SUM(CASE WHEN sc.`score`>=90 THEN 1 ELSE 0 END)/COUNT(*) AS '优秀率'
FROM sc 
GROUP BY sc.`CId`
ORDER BY COUNT(*) DESC, sc.`CId` ASC 
   
#15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

SELECT sc.`CId`,@newRank:=@newRank+1 AS '排名',sc.`score`
FROM sc,(SELECT @newRank:=0)AS r
ORDER BY sc.`score` DESC

SELECT 
 sc.CId , 
 sc.score,
 CASE 
  WHEN @prescore=score 
  THEN @curRank 
  WHEN @prescore:=score 
  THEN @curRank:=@curRank+1  
 END AS rank
FROM  
  sc,
  (SELECT @curRank:=0 ,@prescore:=NULL) AS t
ORDER BY sc.score DESC

   #15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
SELECT CId,rank,score
FROM (SELECT CId,score,@curRank:=IF(@preRank=score,@curRank,@incRank)
AS rank,@incRank:=@incRank+1,@preRank:=score FROM sc,(SELECT @curRank:=0,@preRank:=NULL,@incRank:=1) r1
ORDER BY score DESC 
)r2

#16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺



SELECT t1.*,@currank:= @currank+1 AS rank
FROM (SELECT sc.SId, SUM(score)
FROM sc
GROUP BY sc.SId 
ORDER BY SUM(score) DESC) AS t1,(SELECT @currank:=0) AS t

   #16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺（这几个做好像的有问题吧，题意）

SELECT s1.*,CASE WHEN @prescore=s1.sums THEN @curRank 
WHEN @prescore:=s1.sums THEN @curRank:=@curRank+1 END AS rank
FROM (SELECT sc.sid,SUM(score) AS sums 
FROM sc 
GROUP BY sc.sid 
ORDER BY SUM(score) DESC)AS s1,(SELECT @curRank:=0,@prescore:=NULL)AS t


SELECT course.CId,course.Cname,t1.*
FROM course LEFT JOIN (
SELECT sc.CId,
CONCAT(SUM(CASE WHEN sc.score>=85 AND sc.score<=100 THEN 1 ELSE 0 END )/COUNT(*)*100,'%') AS '[85-100]',
CONCAT(SUM(CASE WHEN sc.score>=70 AND sc.score<85 THEN 1 ELSE 0 END )/COUNT(*)*100,'%') AS '[70-85)',
CONCAT(SUM(CASE WHEN sc.score>=60 AND sc.score<70 THEN 1 ELSE 0 END )/COUNT(*)*100,'%') AS '[60-70)',
CONCAT(SUM(CASE WHEN sc.score>=0 AND sc.score<60 THEN 1 ELSE 0 END )/COUNT(*)*100,'%') AS '[0-60)'
FROM sc
GROUP BY sc.CId) AS t1 ON course.CId=t1.CId
#17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
SELECT course.`CId`,course.`Cname`,s1.*
FROM course LEFT JOIN(
SELECT sc.`CId`,
CONCAT(SUM(CASE WHEN sc.`score`>=85 AND sc.`score`<=100 THEN 1 ELSE 0 END )/COUNT(*)*100,'%')AS '[85-100]', 
CONCAT(SUM(CASE WHEN sc.`score`>=70 AND sc.`score`< 85 THEN 1 ELSE 0 END )/COUNT(*)*100,'%')AS '[70-85)',
CONCAT(SUM(CASE WHEN sc.`score`>=60 AND sc.`score`< 70 THEN 1 ELSE 0 END )/COUNT(*)*100,'%')AS '[60-70)',
CONCAT(SUM(CASE WHEN sc.`score`>=0 AND sc.`score`< 60 THEN 1 ELSE 0 END )/COUNT(*)*100,'%')AS '[0-60)'
FROM sc
GROUP BY sc.`CId`)AS s1 ON course.`CId`=s1.cid

#18.查询各科成绩前三名的记录
SELECT * FROM sc
WHERE(SELECT COUNT(*) FROM sc AS num WHERE sc.`CId`=num.cid AND sc.`score`<num.score )<3
ORDER BY sc.`CId`ASC, sc.`score` DESC 

#19. 查询每门课程被选修的学生数
SELECT sc.`CId`,COUNT(sc.`SId`) FROM sc GROUP BY sc.`CId`   
#20. 查询出只选修两门课程的学生学号和姓名 
SELECT student.`SId`,student.`Sname` FROM student,sc
WHERE student.`SId`=sc.`SId`
GROUP BY  sc.`SId`
HAVING COUNT(*)=2
#21. 查询男生、女生人数
SELECT	student.`Ssex`,COUNT(*)AS '人数' FROM student
GROUP BY student.`Ssex`


#22. 查询名字中含有「风」字的学生信息
SELECT * FROM student
WHERE Sname LIKE '%风%'
#23. 查询同名同性学生名单，并统计同名人数
SELECT student.*,COUNT(*)AS 同名人数 FROM student GROUP BY student.`Sname`,student.`Ssex`
HAVING COUNT(同名人数)>1

SELECT *  FROM student LEFT JOIN (SELECT student.*,COUNT(*)AS 同名人数 FROM student GROUP BY student.`Sname`,student.`Ssex`)
AS s1 ON student.`Sname`=s1.sname AND student.`Ssex`=s1.ssex
WHERE s1.同名人数>1

#24. 查询 1990 年出生的学生名单
SELECT * FROM student
WHERE student.`Sage` LIKE '%1990%'
#25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT sc.`CId`,AVG(sc.`score`)AS `平均成绩` FROM sc
GROUP BY sc.`CId`
ORDER BY AVG(sc.`score`) DESC,sc.`CId` ASC
#26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 //试一下下
SELECT student.`SId` 学号,student.`Sname`AS 姓名 ,AVG(sc.`score`)AS '平均成绩 ' FROM student LEFT JOIN sc ON student.`SId`=sc.`sid`
GROUP BY student.`SId`
HAVING AVG(sc.`score`)>=85


#27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT student.sname,sc.`score` FROM student,sc,course 
WHERE student.`SId`=sc.`SId`  AND sc.`CId`=course.`CId` AND
course.`Cname`='数学' AND sc.`score`<60
 

#28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECT student.`SId`,sc.`CId`,sc.`score` FROM student 
LEFT JOIN sc ON student.`SId`=sc.`SId`

#29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT student.`Sname`,course.`Cname`,sc.`score` FROM student,course,sc
WHERE student.`SId`=sc.`SId` AND sc.`CId`=course.`CId` AND sc.`score`>70
#30. 查询不及格的课程
SELECT * FROM sc
WHERE sc.`score`<60

SELECT student.SId,student.Sname
FROM student ,sc
WHERE sc.CId='01'
AND  student.SId=sc.SId
AND  sc.score>80

#31. 查询课程编号为 01 且课程成绩在 80 分以上xia的学生的学号和姓名
SELECT student.`SId`,student.`Sname`,sc.`score` 
FROM student,sc 
WHERE student.`SId`=sc.`SId`AND sc.`score`<80 AND sc.`CId`='01'

#32. 求每门课程的学生人数 
SELECT sc.`CId`,COUNT(sc.`SId`)AS "学生人数"  FROM sc 
GROUP BY sc.`CId`


#33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT student.*,sc.`CId`,sc.`score` FROM student,sc,course,teacher
WHERE student.`SId`=sc.`SId` AND sc.`CId`=course.`CId` AND course.`TId`=teacher.`TId`
AND teacher.`Tname`='张三'
ORDER BY sc.`score` DESC
LIMIT 0,1


#34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT student.* ,t1.score 
FROM student LEFT JOIN (SELECT sc.`SId`,sc.`score`, CASE WHEN @prescore=sc.`score` THEN @curRank WHEN @prescore:=sc.`score` THEN @curRank:=@curRank+1 END AS rank 
 FROM course,teacher,sc,(SELECT @curRank:=0,@prescore:=NULL) AS t
 WHERE course.`CId`=sc.`CId` AND teacher.`TId`=course.`TId` AND teacher.`Tname`='张三' ORDER BY sc.`score` DESC)AS t1
ON t1.sid=student.`SId`
WHERE t1.rank=1



#35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
SELECT * FROM sc AS t1
WHERE EXISTS (SELECT * FROM sc AS t2 WHERE t1.sid=t2.sid AND t1.cid!=t2.cid AND t1.score=t2.score)

#36. 查询每门功成绩最好的前两名#


SELECT * FROM sc AS t1 
WHERE (SELECT COUNT(*) FROM sc AS t2 WHERE t1.cid=t2.cid AND t1.score<t2.score) <2 
ORDER BY t1.cid  

#37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
SELECT sc.cid,COUNT(*) FROM sc
GROUP BY sc.`CId` 
HAVING COUNT(*)>5
#38. 检索至少选修两门课程的学生学号 
SELECT DISTINCT t1.sid FROM sc AS t1
WHERE (SELECT COUNT(*) FROM sc AS t2 WHERE t1.sid=t2.sid )>2


#39. 查询选修了全部课程的学生信息
SELECT student.* 
FROM student,sc
WHERE student.`SId`=sc.`SId`
GROUP BY student.`SId`
HAVING COUNT(*)=(SELECT COUNT(*)FROM course)

SELECT	CURRENT_DATE()
SELECT CURDATE()
SELECT NOW()
SELECT LOCALTIME()
SELECT SYSDATE()
#40. 查询各学生的年龄，只按年份来算 
SELECT student.`SId`,student.`Sname`,TIMESTAMPDIFF(YEAR,student.`Sage`,NOW()) AS '学生年龄'
FROM student

#41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
SELECT SId ,sname,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(Sage,'%Y') -
(CASE WHEN DATE_FORMAT(NOW(),'%m%d')<DATE_FORMAT(Sage,'%m%d') THEN 1 ELSE 0 END)) AS age
FROM student 


#42. 查询本周过生日的学生
SELECT  *  FROM student
WHERE WEEK(CONCAT(YEAR(CURDATE()),"-" ,MID(sage,6,5))) =  WEEK(NOW())

SELECT * FROM Student
WHERE WEEKOFYEAR(CONCAT
(
DATE_FORMAT(CURRENT_DATE,'%Y'),
'-',DATE_FORMAT(Sage,'%m-%d')
)
) = WEEKOFYEAR(CURRENT_DATE)
#43. 查询下周过生日的学生

SELECT * FROM student
WHERE WEEKOFYEAR(
CONCAT(
DATE_FORMAT(NOW(),'%Y'),'-',DATE_FORMAT(student.`Sage`,'%m-%d')
)
)=WEEKOFYEAR(NOW())+1


#44. 查询本月过生日的学生
SELECT *
FROM student 
WHERE EXTRACT(MONTH FROM student.Sage)=EXTRACT(MONTH FROM CURDATE())


#45. 查询下月过生日的学生
SELECT *
FROM student 
WHERE EXTRACT(MONTH FROM student.Sage)=EXTRACT(MONTH FROM CURDATE())+1
这个神明好水
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL中教师职称英文_mysql综合性练习
weixin_36349918的博客
02-27 523
题目描述设定有一个数据库，里面有4张表：学生表(student)课程表(course)成绩表(score)教师信息表(teacher)表结构如下：表一_学生表(student)属性名数据类型含 义snoint学号(主键)snamevarchar学生姓名ssexvarchar学生性别sbirthdaydate学生出生年月classidvarchar学生所在班级表二_课程表(course)属性名数据类...
50个常用的SQL语句练习.doc
11-30
"SQL语句练习" SQL语句是数据库管理系统中的一种语言，用于管理和操作数据库。下面是对给定文件的知识点总结： 1. 查询“001”课程比“002”课程成绩高的所有学生的学号： 这个问题考察了SQL语句中子查询的使用。...
学习好sql
HuoDaLao的博客
09-16 433
科目表 Course数据表介绍 --1.学生表 Student(SId,Sname,Sage,Ssex) --SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 --2.课程表 Course(CId,Cname,TId) --CId 课程编号,Cname 课程名称,TId 教师编号 --3.教师表 Teacher(TId,Tname) --TId 教师编号,Tna...
sql面试题（学生表_课程表_成绩表_教师表）
04-02 4576
原帖链接:http://bbs.csdn.net/topics/280002741 表架构 Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname,T#) 课程表SC(S#,C#,score) 成绩表Teacher(T#,Tname) 教师表 建表语句 CREATE TABLE student ( s# ...
SQL语句加强
不知谁唱、唱给谁的专栏
03-20 623
这里是我总结的SQL语句练习员工表准备CREATE TABLE employee ( id INT PRIMARY KEY auto_increament, -- 主键 name VARCHAR (50), -- 名字 salary BIGINT, -- 薪水 depid INT -- 部门id )一、用两种方式根据部门号从高到低，工资从低到高列出每个员工的信息。
MySQL 学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）
热门推荐
lenka的专栏
09-04 7万+
设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）~表（四）所示，数据如表1-2的表（一）~表（四）所示。用SQL语句创建四个表并完成相关题目。 表1-1数据库的表结构 表（一）Student (学生表) 字段名...
SQL经典练习题_sql练习_
10-03
本资料包中的"SQL经典练习题.sql练习."包含了个人收藏的一系列SQL题目和案例，这些内容常常在面试中出现，对提升SQL技能和准备面试具有很高的价值。 首先，我们要了解SQL的基本概念。SQL主要包括以下几个部分： 1....
sql练习题sql练习题sql练习题sql练习题
最新发布
06-04
本压缩包文件包含了一份“mysql练习题.md”，这显然是一份关于MySQL数据库的SQL练习题目，旨在帮助学习者加深对SQL语言的理解和应用能力。 SQL练习题通常涵盖了以下核心概念和操作： 1. **数据类型**：SQL中的数据...
经典sql练习
10-25
经典sql练习，链接给出题型： https://www.jobui.com/mianshiti/it/shujuku/5838/ 本意无需资源分数，但csdn变坏了，没有0分可以选，没有分数的，发qq邮箱索要1943680751
sql基础练习
03-12
在这个sql基础练习中，我们将探讨如何使用SQL来操作数据库，包括创建表、插入数据、查询数据等基本操作。 首先，我们创建了三个表：STUDENT（学生）、COURSE（课程）和SCORE（分数）。在STUDENT表中，包含了学生的...
sql面试题- (学生表_课程表_成绩表_教师表)
08-06
1、查询“001”课程比“002”课程成绩高的所有学生的学号； select a.S# from (select s#,score from SC where C#='001') a,(select s#,score from SC where C#='002') b where a.score>b.score and a.s#=b.s#; 2、查询平均成绩大于60分的同学的学号和平均成绩； select S#,avg(score) from sc group by S# having avg(score) >60; 3、查询所有同学的学号、姓名、选课数、总成绩； select Student.S#,Student.Sname,count(SC.C#),sum(score) from Student left Outer join SC on Student.S#=SC.S# group by Student.S#,Sname 4、查询姓“李”的老师的个数； select count(distinct(Tname)) from Teacher where Tname like '李%'; 5、查询没学过“叶平”老师课的同学的学号、姓名； select Student.S#,Student.Sname from Student where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');
【mysql题目】按各科成绩排序，并显示名次（同名次空缺vs合并）
Athain的博客
12-11 1万+
按各科成绩排序，并显示名次（同名次空缺vs合并） 数据表 -- 学生表 Student(sid,sname,sage,ssex) insert into Student values('01','张三','1991-11-11','男'); -- 课程表 Course(cid,cname,tid) insert into Course values('01','语文','01'); -- 教师表 ...
MySql建表语句，教师，学生，班级
07-13 3447
DROP DATABASE IF EXISTS Teach; CREATE DATABASE Teach DEFAULT CHARACTER SET utf8; USE Teach; CREATE TABLE class ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) DEFAULT NULL, PRIMARY K...
SQL加强（一）概念
有梦就有马
07-26 686
MySQL技术内幕：SQL编程学习
SQL server语句创建表
雷的博客
04-29 548
use LstDatabase --打开数据库名 create table techer --创建表名 ( 教师编号 int , --字段信息 教师职称 varchar(50), 教师年龄 int, 教师薪金 money, )
SQL经典案例（学生表，课程表，选课表，教师表） 练习
weixin_45791987的博客
09-13 3673
练习
50条经典（学生，课程，成绩，教师）表SQL语句~~值得一看!
OYY_90的博客
10-26 2308
  /*student(学号#,姓名,性别,年龄)  course(课程号#，课程名，教师号#)  score(学号#，课程号#，成绩)  teacher(教师号#，教师名)*/     --1.查询“001”课程比“002”课程成绩高的所有学生的学号   select a.stuNo from score a,score b   where a.cNo='c001' and b.cNo='c...
50条常用SQL语句
feiyangyangfei的专栏
10-31 2289
[cpp] view plaincopy /*student(学号#,姓名,性别,年龄)  course(课程号#，课程名，教师号#)  score(学号#，课程号#，成绩)  teacher(教师号#，教师名)*/   --1.查询“001”课程比“002”课程成绩高的所有学生的学号   select a.stuNo from score a,score 
MySQL经典四表查询(教师，学生，成绩，课程表)多表查询
hank.ll的博客
06-15 3万+
我用的工具：MySQL5.7+SQLyog-11.2.5-0 1、表架构呈现 student(sid,sname,sage,ssex) 学生表 teacher(tid,tname) 教师表 course(cid,cname,tid) 课程表 sc(sid,cid,score) 成绩表 2、建表SQL语句并插入数据 -- 1.学生表 CREATE TABLE student( sid INT PRIMARY KEY AUTO_INCREMENT, sname...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值