sql学习笔记2

小鸡
阅读566 喜欢3 sql 更新2018-11-5

今天继续做了会儿练习语句

都是比较基本的sql查询语句

需要熟练掌握的

  1. 查询选修毛概的学生的姓名成绩,并且升序输出
select sname,score
from student,s_c,course
where student.sno=s_c.sno and
s_c.cno=course.cno and cname=毛概
and score is not null
order by score desc
  1. 查询所有科目分数总和大于1100的学生姓名与分数总和
select sname,sex,SUM(score)
from student,s_c
where student.sno=s_c.sno
group by sname,sex
having SUM(score)>1100
  1. 查询年龄在20~23的学生
select sname,age
from student
where age between 20 and 23
  1. 查询年龄不在20~23的学生
select sname,age
from student
where age not between 20 and 23
  1. 查询第2,3,4组的学生姓名与性别
select sname,sex
from student
where gno in(2,3,4)
  1. 查询不在2,3,4组的学生
select sname,sex
from student
where gno not in(2,3,4)
  1. 查询不在2,3,4组的姓刘的同学
select sname,sex
from student
where gno not in(2,3,4) and sname like 刘%
  1. 查询不在2,3,4组且名字第二个字为‘金’的同学
select sname,sex
from student
where gno not in(2,3,4) and sname like \_金%
  1. 查询所有不姓刘但是名字第二个字为’金’的同学所有信息
select *
from student
where sname not like 刘% and sname like \_金%
  1. 查询成绩为空的学生姓名
select distinct sname
from student,s_c
where s_c.sno=student.sno and
s_c.score is null
  1. 查询选修毛概的学生的姓名成绩,并且升序输出
select sname,score
from student,s_c,course
where student.sno=s_c.sno and
s_c.cno=course.cno and cname=毛概
and score is not null
order by score desc
  1. 查询所有科目分数总和大于1100的学生姓名与分数总和
select sname,sex,SUM(score)
from student,s_c
where student.sno=s_c.sno
group by sname,sex
having SUM(score)>1100
  1. 用嵌套查询,查询所有选修了计网实验的学生姓名
select sname
from student
where sno in(
select sno
from s_c
where cno in(
select cno
from course
where cname=计网实验
)
)
  1. 查询与王艺璇所在同一组的其他同学姓名性别
select sname,sex
from student
where gno in (
select gno
from student
where sname=王艺璇
) and sname!=王艺璇
  1. 找出每个学生超过自己课程平均分的课程名字
select sname,cname
from student,course,s_c x
where score>(
select AVG(score)
from s_c y
where y.sno=x.sno
)
and student.sno=x.sno and x.cno=course.cno
  1. 实验八
  1. 求学生的总人数
select COUNT(*)
from student
  1. 求选修了课程的学生人数
select COUNT(student.sno)
from student
where exists(
select *
from student_course
where student.sno=student_course.sno
)
  1. 求课程的课程号和选修该课程的人数。
select tcid,COUNT(student_course.sno)
from student_course
group by tcid
  1. 求选修课超过3 门课的学生学号
select sno
from student_course
group by sno
having COUNT(tcid)>3

11-05更新

  1. 求选修了高等数学的学生学号和姓名(生成的课程数据中没有高等数学,改为数据库)
select distinct student.sno,sname
from student,course,student_course,teacher_course
where course.cname=数据库 and
course.cno=teacher_course.cno and
student.sno=student_course.sno
  1. 求数据库课程的成绩高于张三的学生学号和成绩
select sno,score
from student_course sc_a
where sc_a.tcid in(
select tcid
from teacher_course
where cno=(
select cno
from course
where cname=数据库
)
) and score>(
select distinct score
from student_course sc_b
where sno=(
select sno
from student
where student.sname=张三22
) and sc_b.tcid in (
select tcid
from teacher_course
where cno=(
select cno
from course
where cname=数据库
)
)
)
  1. 求其他系中比计算机系某一学生年龄小的学生信息(即求其它系中年龄小于计算机系年龄最大者的学生)
select *
from student
where student.dno!=计算机 and
student.birthday > (
select min(birthday)
from student b
where b.dno=计算机
)
  1. 求其他系中比计算机系学生年龄都小的学生信息
select *
from student
where student.dno!=计算机 and
student.birthday > (
select MAX(birthday)
from student b
where b.dno=计算机
)
  1. 求选修了数据库课程的学生姓名
select sname
from student
where sno in (
select distinct sno
from student_course
where tcid in(
select tcid
from teacher_course
where cno=(
select cno
from course
where cname=数据库
)
)
)
  1. 求没有选修数据库课程的学生姓名
select sname
from student
where sno not in (
select distinct sno
from student_course
where tcid in(
select tcid
from teacher_course
where cno in (
select cno
from course
where cname = 数据库
)
)
)
  1. 查询选修了全部课程的学生的姓名
select sname
from student
where not exists(
select *
from course
where not exists(
select *
from teacher_course,student_course
where student.sno=student_course.sno and
student_course.tcid=teacher_course.tcid and
course.cno=teacher_course.cno
)
)
  1. 求至少选修了学号为101的学生所选修的全部课程的学生学号和姓名。
select sno,sname
from student
where sno in(
	select sno
	from student_course sc1
	where not exists(
		select *
		from student_course sc2
		where sc2.sno=101 and not exists(
			select *
			from student_course sc3
			where sc3.sno=sc1.sno and
			sc3.tcid=sc2.tcid
		)
	)
)