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'))