MySQL实战，SQL语句

student数据库

student学生表，course课程表表，sc成绩表

-- 1.找出成绩为95分的学生的姓名

SELECT Sname

FROM student

WHERE Sno IN(

SELECT DISTINCT Sno

FROM sc

WHERE Grade=95)

-- 2.查询大数据专业不及格学生名单

SELECT *

FROM student

WHERE Sno IN(

SELECT DISTINCT Sno

FROM sc

WHERE Sdept='大数据' AND Grade<=60)

-- 3.查询全部学生的学生名和所学课程号及成绩

SELECT Sname,Cno,Grade

FROM student,sc

WHERE student.Sno=sc.Sno

-- 4.找出籍贯(bplace)为山西或河北，成绩为80分以上的学生的姓名、籍贯和成绩

SELECT Sname,bplace,Grade

FROM student

WHERE Sno IN(

SELECT DISTINCT Sno

FROM sc

WHERE Grade>80

)

AND bplace='山西'OR bplace='河北'

-- 5.查出课程成绩在85分以上的女学生的姓名、课程名和成绩

SELECT Sname,Cname,Grade

FROM student

WHERE Sno IN(

SELECT Sno

FROM sc

WHERE Grade>85 AND Cno IN(

SELECT Cno

FROM course)

AND Ssex='女'

-- 6.查询没有选任何课程的学生的学号和姓名

SELECT Sno,Sname

FROM student

WHERE NOT EXISTS

(

SELECT *

FROM sc

WHERE student.`Sno`=sc.`Sno`

)

-- 7.查询哪些课程只有男生选读

SELECT DISTINCT

FROM course

WHERE '男'=ALL(

SELECT Ssex

FROM student,sc

WHERE student.`Sno`=sc.`Sno` AND course.`Cno`=sc.`Cno`)

-- 8.将电子商务专业的学生成绩加3分

UPDATE sc SET Grade=Grade+3

WHERE Sno IN(

SELECT Sno

FROM student

WHERE Sdept='电子商务'

)

-- 9.找出选修了全部课程的学生的姓名

SELECT Sname

FROM student

WHERE NOT EXISTS(

SELECT*

FROM course

WHERE NOT EXISTS(

SELECT *

FROM sc

WHERE student.`Sno`=sc.`Sno` AND course.`Cno`=sc.`Cno`

))

-- 10.查询选修C语言课程学生的学号、姓名和所在系

SELECT student.Sno,Sname,Sdept

FROM student,sc,course

WHERE student.Sno=sc.Sno

AND course.Cno=sc.Cno

AND Cname='C语言'

-- 11.删除李丽学生的选课信息

DELETE FROM sc

WHERE Sname='李丽'

-- 12.统计有多少名20岁以下的男同学

SELECT COUNT(Sno)

FROM student

WHERE Ssex='男' AND Sage>20

-- 13.向course表中填加一门课程

INSERT INTO course

VALUES('A07','C语言','4','2')

-- 14.列出年龄最大的学生名单

SELECT *

FROM student

WHERE Sage=(SELECT MAX(Sage)FROM student)

-- 15.列出所有年龄超过平均值的学生名单，按年龄的降序显示

SELECT *

FROM student

WHERE Sage>(SELECT AVG(Sage)FROM student)

ORDER BY Sage DESC

-- 16.检索至少选修课程号为A01和A02的学生学号

SELECT student.Sno

FROM sc

INNER JOIN student  -- 内连接

ON student.Sno=sc.Sno AND (Cno='A01' AND Cno='A02')

-- 17.找出每门课程的平均成绩，且只选择学生超过2人的课程的平均成绩

SELECT student.Sno,AVG(Grade)

FROM student,sc

WHERE student.`Sno`=sc.`Sno`

HAVING COUNT(*)>2

-- 18.列出其他专业中比网络技术某个学生年龄小的学生

SELECT *

FROM student

WHERE Sdept='网络技术'

AND Sage <(SELECT MAX(Sage)FROM student WHERE Sdept='网络技术')

-- 19.查询所有大于学号为‘200201002’的学生的成绩的学生学号、姓名、课程名和成绩

SELECT student.Sno,Sname,Cname,Grade

FROM student

WHERE Sno IN(

SELECT Sno

FROM sc

WHERE Cno IN(

SELECT Cno

FROM course)

AND Sno>'200201002'))

巴特西

MySQL实战，SQL语句

最新文章

热门文章