sql学习笔记2

发表于 | 更新于: | 分类于 | | 热度: 2℃
字数统计: 1.1k | 阅读时长 ≈ 5

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

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

需要熟练掌握的

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

11-05更新

  1. 求选修了高等数学的学生学号和姓名（生成的课程数据中没有高等数学，改为数据库）
1
2
3
4
5
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. 求数据库课程的成绩高于张三的学生学号和成绩
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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. 求其他系中比计算机系某一学生年龄小的学生信息（即求其它系中年龄小于计算机系年龄最大者的学生）
1
2
3
4
5
6
7
8
select *
from student
where student.dno!='计算机' and
student.birthday > (
	select min(birthday)
	from student b
	where b.dno='计算机'
)
  1. 求其他系中比计算机系学生年龄都小的学生信息
1
2
3
4
5
6
7
8
select *
from student
where student.dno!='计算机' and
student.birthday > (
 select MAX(birthday)
 from student b
 where b.dno='计算机'
 )
  1. 求选修了数据库课程的学生姓名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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. 求没有选修数据库课程的学生姓名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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. 查询选修了全部课程的学生的姓名
1
2
3
4
5
6
7
8
9
10
11
12
13
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
		)
	)
)







我是小鸡，这篇文章的标题是——sql学习笔记2
喜欢的话可以转载，不过记得标记出处

2018年11月05日