数据库表

sql语句格式：

实战：

• 设置sc(成绩表)表中sno,cno唯一键

sql">alter table sc add unique(sno,cno);

• 查询所有同学的学号、姓名、选课数、总成绩

sql">SELECT sc.sno,sname,COUNT(cno) '选课数',SUM(score) 'sum'
FROM student,sc
WHERE sc.sno=student.sno
GROUP BY sc.sno

• 查询姓“李”的老师的个数

sql">SELECT count(tno)
FROM 	teacher
where tname LIKE '李%'

• 求选择了Java课程且成绩小于60的同学名单，按名字升序排列

sql">select student.sname from  sc,student

where  student.sno=sc.sno and sc.cno in
 (select cno from course where cname='JAVA')and sc.score<60

ORDER BY  student.sname

• 查询课程1的成绩比课程2的成绩高的所有学生的学号

sql">SELECT s1.sno
from 
		(SELECT sno ,score from sc where cno=1) s1,
		(SELECT sno ,score from sc where cno=2) s2
where s1.sno=s2.sno and s1.score>s2.score;

• 求Java课程小于平均分的学生的各科成绩

sql">select sno ,cno,score
from sc
where sno in(
	SELECT sno
	from sc,(SELECT cno,avg(score) a from  sc where cno =(SELECT cno from course where cname='JAVA')) avgsc  
    where sc.cno=avgsc.cno and sc.score<avgsc.a 
);

• 查询平均成绩大于60分的同学的学号和平均成绩

sql">SELECT sno ,AVG(score)
FROM sc
GROUP BY sno
HAVING AVG(score)>60

• 查询所有课程成绩小于60分的同学的学号、姓名

sql">SELECT sno,sname
FROM  student
where sno not in (SELECT DISTINCT sno from sc where score >60 );

• 给选择了JAVA课程且成绩小于等于90分的成绩加10分

sql">UPDATE  sc
SET score=score+10
where cno= (SELECT cno from course where cname='JAVA') and sc.score<90

相关连接：

SQL常用语句总结  https://blog.csdn.net/u012757419/article/details/94829361 
经典sql实例大全     https://blog.csdn.net/LoulseLong/article/details/79388345?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task