sql学习笔记2

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

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

需要熟练掌握的

查询选修毛概的学生的姓名成绩，并且升序输出

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

查询所有科目分数总和大于1100的学生姓名与分数总和

select sname,sex,SUM(score)
from student,s_c
where student.sno=s_c.sno
group by sname,sex
having SUM(score)>1100

查询年龄在20~23的学生

select sname,age
from student
where age between 20 and 23

查询年龄不在20~23的学生

select sname,age
from student
where age not between 20 and 23

查询第2，3，4组的学生姓名与性别

select sname,sex
from student
where gno in(2,3,4)

查询不在2，3，4组的学生

select sname,sex
from student
where gno not in(2,3,4)

查询不在2，3，4组的姓刘的同学

select sname,sex
from student
where gno not in(2,3,4) and sname like '刘%'

查询不在2，3，4组且名字第二个字为‘金’的同学

select sname,sex
from student
where gno not in(2,3,4) and sname like '_金%'

查询所有不姓刘但是名字第二个字为’金’的同学所有信息

select *
from student
where sname not like '刘%' and sname like '_金%'

查询成绩为空的学生姓名

select distinct sname
from student,s_c
where s_c.sno=student.sno and
s_c.score is null

查询选修毛概的学生的姓名成绩，并且升序输出

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

查询所有科目分数总和大于1100的学生姓名与分数总和

select sname,sex,SUM(score)
from student,s_c
where student.sno=s_c.sno
group by sname,sex
having SUM(score)>1100

用嵌套查询，查询所有选修了计网实验的学生姓名

select sname
from student
where sno in(
select sno
from s_c
where cno in(
select cno
from course
where cname='计网实验'
)
)

查询与王艺璇所在同一组的其他同学姓名性别

select sname,sex
from student
where gno in (
select gno
from student
where sname='王艺璇'
) and sname!='王艺璇'

找出每个学生超过自己课程平均分的课程名字

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

实验八
求学生的总人数

select COUNT(*)
from student

求选修了课程的学生人数

select COUNT(student.sno)
from student
where exists(
    select *
    from student_course
    where student.sno=student_course.sno
)

求课程的课程号和选修该课程的人数。

select tcid,COUNT(student_course.sno)
from student_course
group by tcid

求选修课超过3 门课的学生学号

select sno
from student_course
group by sno
having COUNT(tcid)>3

11-05更新

求选修了高等数学的学生学号和姓名（生成的课程数据中没有高等数学，改为数据库）

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

求数据库课程的成绩高于张三的学生学号和成绩

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='数据库'
         )
    )
)

求其他系中比计算机系某一学生年龄小的学生信息（即求其它系中年龄小于计算机系年龄最大者的学生）

select *
from student
where student.dno!='计算机' and
student.birthday > (
    select min(birthday)
    from student b
    where b.dno='计算机'
)

求其他系中比计算机系学生年龄都小的学生信息

select *
from student
where student.dno!='计算机' and
student.birthday > (
 select MAX(birthday)
 from student b
 where b.dno='计算机'
 )

求选修了数据库课程的学生姓名

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='数据库'
        )
    )
)

求没有选修数据库课程的学生姓名

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 = '数据库'
        )
    )
)

查询选修了全部课程的学生的姓名

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

求至少选修了学号为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
        )
    )
)