数据库-查询语句习题

SELECT Sname '姓 名','year of birth:' 出生年,YEAR(GETDATE())-Sage BIRTHYEAR,LOWER(SNAME) SNAME --起别名 没有特殊字符不需要引号，有特殊字符要加引号；别名（解释作用显示给用户看）用空格或as连接
FROM STUDENT;
--消除重复行 DISTINCT
--查询选修了课程的学生学号 在SC中出现表示选课了
SELECT DISTINCT Sno
From SC
WHERE CNO IS NOT NULL;
--比较大小
--查询年龄在20岁以下全体学生的名单
SELECT Sname
FROM Student
WHERE Sage<20;
--确定范围 between and
--查年龄20-23的sname sage
SELECT SNAME,SAGE
FROM Student
WHERE SAGE BETWEEN 20 AND 23;
--确定集合 in  not in 属于不属于 差
SELECT SNAME,SSEX
FROM STUDENT
WHERE SSEX IN('男','女');
--字符匹配 like 字符串查询 没有通配符=与like没啥区别 
--通配符 不精确查询对象 _下划线：匹配单个字符 %百分号：匹配任意长度（包括0）字符串 【】中括号：匹配其中任意字符 
--查询所有姓刘 赵 王学生SNAME SNO SSEX 查多个，可用【】括起来
SELECT SNAME,SNO,SSEX
FROM STUDENT
WHERE SNAME LIKE '[赵王张]%';
--姓欧阳全名三个字 只要不是固定长度的数据尽量用VARCHAR类型 不然之后补空格无法处理 定长字符串把后边的空格缩成最后一个空格了？？？？？？？？
--查找的东西有%_，通配符不当通配符，转码字符\只要不是查询的内容内就可以,通过关键字ESCAPE来指示
--查DB_Design课程的课程号
SELECT CNO
FROM COURSE
WHERE CNAME LIKE 'DB\_Design' ESCAPE '\';
--空值查询IS NULL  IS NOT NULL 空值参与运算 不是等于NULL
--多重条件查询 AND OR条件组合 用OR实现IN运算 区分逻辑运算的并交
--对查询结果排序 ORDER BY ASC升 DESC降 ASC可省略 可加TOP 数，TOP几几 persent 前百分之几（最好的）
--查询选修了3号课程的，结果按分数降序排列 
SELECT SNO,GRADE
FROM SC
WHERE CNO='3'
ORDER BY GRADE DESC
--使用聚集函数统计数据 聚集函数/统计函数/集合函数：作用是对数据进行汇总和统计 通过满足条件的多行进行操作
--主要聚集函数：统计元组个数COUNT（*/distinct默认不去除重复）统计一列中值的个数COUNT（<列名>）计算一列值的总和SUM 最大值MAX 最小值MIN
--聚集函数不能嵌套 
--查询学生总数
SELECT COUNT(*)
FROM STUDENT
--查询选修课程的学生人数
SELECT COUNT(DISTINCT SNO)
FROM SC;
--计算一号课程的学生平均成绩 AVG与Sno不是一一对应
SELECT AVG(GRADE)
FROM SC
WHERE Cno='1';
--对查询结果分组 GROUP BY，完成分组统计和限定一些分组应满足的条件，分完组后聚集函数对每个组运算;若要决定某个组满足某个条件HAVING；
--求各个课程号及相应选课人数 CNO与人数一一对应了 
SELECT CNO,COUNT(*)
FROM SC
GROUP BY CNO --课程号分组
--查询选修了三门以上课程学号 where只能判断当前行，不能整聚集函数 判断满足条件的组（HAVING）
SELECT SNO,COUNT(*)
FROM SC
GROUP BY SNO
HAVING COUNT(*)>2;
--统计各个系各个出生年龄的学生人数  聚集函数之外的表达式必须在组内 起别名后，排序可以用别名（排序是对得到结果进行显示的），其余的操作不能用别名
--sno换位sdept
SELECT SNO,YEAR(GETDATE())-Sage,COUNT(*)
FROM STUDENT
GROUP BY SNO,YEAR(GETDATE())-Sage--GROUP BY SDEPT 不行
--连接查询
--两表有相同属性名，要指定哪个表的，.操作符（参与连接的属性）
--非ANSI 表1.列名1 表2.列名2 关系表达式；ANSI FROM 表1，表2 where
--等值非等值连接
--查询每个学生及其选修课程i情况
--重复两次学号，student.sno,sname,.....替换*（自然连接）
SELECT *
FROM STUDENT,SC
--Student join SC
WHERE STUDENT.SNO=SC.SNO;
--ON s.sno=sc.sno
--自身连接 整个副本，起别名，起别名后，在查询中任何地方都使用该别名而不使用原来表名
--找间接先修课
SELECT FIRST.CNO,SECOND.CPNO
FROM COURSE FIRST,COURSE SECOND
WHERE FIRST.CPNO=SECOND.CNO;
--FROM COURSE FIRST JOIN COURSE SECOND ON FIRST.Cpno=Second.Cno
--起别名：表名太长，简化表名；两个表相关，起别名，区分
--既学过1有学过2号课的学生学号
select sc1.sno
from sc sc1,sc sc2
where sc1.sno=sc2.sno and sc1.cno='1' and sc2.cno='2';
--1号课程成绩比2号课程成绩高的学生学号
select sc1.sno
from sc sc1,sc sc2
where sc1.sno=sc2.sno and sc1.cno='1' and sc2.cno='2' and sc1.grade>sc2.grade;
--复合条件连接 除连接条件外还有其他限制条件WHERENASI 非NASI用AND
--查询选修二号课程且成绩在90以上所有学生
SELECT Student.sno,sname
From student,sc
where student.sno=sc.sno

--查询每个学生学号姓名选修课程名及成绩 三表连接 成绩大于80
select student.sno,sname,cname,grade
from student,course,sc
where student.sno=sc.sno and sc.cno=course.cno and grade>80;
--ANSI
select student.sno,sname,cname,grade
from student join sc on student.sno=sc.sno
            join course on sc.cno=course.cno;
--外连接 左外 右外 全连接full join right join left join outer可加可不加在join前
--学生学号 学生姓名 课程号 成绩  没选课也要其成绩
select student.sno,sname,cno,grade
from student left join sc on student.sno=sc.sno;
--查询选修超过三门的学生学号、姓名及选课数量
select student.sno,sname,count(*)
from student,sc
where student.sno=sc.sno
group by student.sno,sname
having count(*)>3;

SELECT student.sno,sname,sclass,cname,chour,grade
FROM student,sc,course
where student.sno=sc.sno and sc.cno=course.cno;

SELECT student.sno,sname,AVG(grade)
FROM student,sc
where student.sno=sc.sno
Group by student.sno,sname
ORDER BY AVG(grade) DESC;

SELECT s2.sno,s2.sname,s2.sclass
FROM sc,course,Student s1,student s2
where s1.sname='王辰' and s1.sno=sc.sno and course.cno=sc.cno and s2.sage=s1.sage;

Select student.sno,sname,grade
From student,sc,course
Where student.sno=sc.sno and sc.cno=course.cno and cname='计算机网络';

--嵌套查询 子查询 父查询 没扫描一个父查询看其是否在子查询 子查询不需要去除重复、排序等 in类似自连接 not in类似差运算
--含有in谓词的嵌套查询 where 表达式 not in 子查询  先执行子查询，利用子查询结果执行父查询
--查询与刘晨在同一系学习学生
Select sno,sname,sdept
from student
where sdept in (
        select sdept
        from student
        where sname='刘晨');
--自连接
Select s1.sno,s1.sname,s1.sdept
from student s1, student s2
where s1.sdept=s2.sdept and s2.sname='刘晨';
--查询选修课为信息系统学号姓名
Select sno,sname
from student
where sno in (
select sno
from sc
where cno in (
select cno
from course
where cname='信息系统'));
--查询没有选修课程的sname，sno <>不等号，不能使，课程不等于1直接输出了，但是下一行可能还有等于1 使用正向思维，1-正向，用差not in
Select sno,sname
from student
where sno not in (
            select sno
            from sc);
--含有比较运算符的嵌套，确保子查询返回的结果为单值
--刘晨只有一个系
--查询与刘晨在同一系学习学生
Select sno,sname,sdept
from student
where sdept = (
        select sdept
        from student
        where sname='刘晨');
--多值 使用any all 某一个 所有
--查询其他系中比信息系某一学生年龄小的学生姓名 年龄
select sname,sage
from student
where sage<any(
            select sage
            from student
            where sdept='IS')
    and sdept<>'IS';
--也可使用聚集函数 最好使用聚集函数，但是不能嵌套
select sname,sage
from student
where sage<(
            select min(sage) from student
            where sdept='is')
    and sdept<>'is';
--相关不相关嵌套查询
--子查询查询条件依赖于父查询 子父查询 改名
--找每个学生超过它选修课程平均成绩的课程号 同一表用两次，相关——改名
select sno,cno
from sc scx--找选课记录对应的sno cno
where grade>(
            select avg(grade)
            from sc scy 
            where scy.sno=scx.sno);--找平均成绩高的
--具体解释：首先外层查询取出一个SC元组SCX，然后根据SCX对应的学号 即SCX.Sno，去在SC表的另 个副本SCY中计算该学生对应学号 SCX.Sno的平均成绩，如果元组SCX对应的成绩超过了计算出的
--平均成绩，则输出元组SCX对应的课程号，否则继续提取下 个SC元组重复上述过程，直到外层查询扫描SC表一遍
--带有exists 存在量词 得到真假两个逻辑值 not exists 不存在 in not in所有都可用exists not exists做，反过来不一定 用存在一般相关子查询，表达能力超强
--查询所有选修一号课程sname,sno
select sname,sno
from student
where exists (
            select *
            from sc
            where sc.sno=student.sno and cno='1');
--刘晨一个系
Select sno,sname,sdept
from student
where sdept in (
        select sdept
        from student
        where sname='刘晨');
--自连接
Select s1.sno,s1.sname,s1.sdept
from student s1, student s2
where s1.sdept=s2.sdept and s2.sname='刘晨';
 
select sno,sname
from student s1
where exists(
            select * 
            from student s2
            where s2.sname='刘晨' and s2.sdept=s1.sdept);
--全称量词 改量词 否结论 否定
--查询选修了所有课程的sname 1）要查询这样的学生student对应的学生姓名sname
select sname
from student
--2)不存在一门课程course
where not exists (
            select *
            from course 
--3)student没有选course，也就是不存在一个student选修course的选课记录sc
            where not exists (
                            select *
                            from sc
                            where sc.sno=student.sno and sc.cno=course.cno));
--蕴含逻辑运算 p->q等价非p并q
--查询至少选修201215122选修的全部课程学生号码 不存在它选了但是检索到的学生没选
--1)我们要查询这样的学生student对应的学号sno
select sno
from student
--2)不存在一个201215122学生的选课记录scx 后边还涉及选课
where not exists(
                select *
                from sc scx
                where sno='0905010102'
--3)student没有选修scx对应的课程，也即是不存在一个student选修了scx.cno对应课程的选课记录scy
                    and not exists (
                    select *
                    from sc scy
                    where scy.sno=student.sno and scy.cno=scx.cno));--没有这句话就是全部学生学号都输出了
select sno
from student
--2)不存在一个201215122学生的选课记录scx 后边还涉及选课
where not exists(
                select *
                from sc scx
                where sno='201215122'
--3)student没有选修scx对应的课程，也即是不存在一个student选修了scx.cno对应课程的选课记录scy
                    and scx.sno not in (
                    select scy.sno
                    from sc scy
                    where scy.sno=student.sno and scy.cno=scx.cno));
--只差了sno，可以使用sc
--1)我们要查询这样的学生scx应的学号sno
select distinct sno
from sc scx
--2)不存在一个201215122学生的选课记录scy 后边还涉及选课
where not exists(
                select *
                from sc scy
                where sno='201215122'
--3)student没有选修scx对应的课程，也即是不存在一个scx修了scy.cno对应课程的选课记录scz
                    and not exists (
                    select *
                    from sc scz
                    where scz.sno=scx.sno and scz.cno=scy.cno));
--集合查询 查询结果元组集合 多个select得到的结果由相同结果
--并：自动去除重复Union 【all】 all不去重复
--交：Intersect
--查询同时选修了课程1、2的sno
select sno from sc where cno='1'
intersect
select sno from sc where cno='2';
--不能使用cno=‘1’and cno=‘2’ 一行中不能由两个cno 使用嵌套查询 
--差 except
--基于派生表的查询 from表 此处可以是一个查询，但是要存成表，临时表
--超过平均值
select sno,cno
from sc,(
            select sno,avg(grade)
            from sc
            group by sno 
            as avg_sc(avg_sno,avg_grade))
where sc.sno=avg_sc.avg_sno 
    and sc.grade>=avg_grade);
--#选修数据库原理并且分数与王辰该课程成绩相同sno
select student.sno
from course,student,sc sc1,sc sc2
where sc1.sno=student.sno and sc1.cno=course.cno and sc1.sno=sc2.sno  
        and course.cname='数据库原理' and sc1.grade=sc2.grade;

select distinct student.sno
from student,sc s1,sc s2,course
where s1.cno=s2.cno and s2.grade=s1.grade and course.cno=s2.cno and cname='数据库原理' and 
    s2.sno<>'王辰';

select distinct s1.sno 
from sc s1,sc s2
where s1.sno=s2.sno and s1.grade=s2.grade and s2.cno in (
    select cno
    from course
    where cname='数据库原理');
select distinct s.sno
from student s
join sc s1 on s.sno=s1.sno
join sc s2 on s1.sno=s2.sno and s2.grade=s1.grade
join course c on s2.cno=c.cno
where c.cname='数据库原理' and s2.sno<>'王辰';
--不及格课程sname sno
select sname,sc.sno
from student,sc
where student.sno=sc.sno and grade<70;
select sname,sno
from student
where sno in (
        select sno
        from sc
        where grade<70);
--数据库原理最高分
select max(grade)
from sc
where grade in (
                select grade
                from course
                where course.cname='数据库原理');
--查询平均成绩80分以上学生sname
select sname
from student
where sno in (
            select sno
            from sc
            group by sno
            having avg(grade)>80);
--查询没有选修数据库原理课程的学生姓名和所在班级
select student.sname,sclass
from student
where sno not in(
            select sno
            from sc
            where cno in(
                    select cno
                    from course
                    where cname='数据库原理'));
--#被所有计算机091班学生选修的课程的cname和chour
select cname,chour
from course
where not exists(
                select *
                from student
                where sclass='计算机091' and not exists(
                                                select *
                                                from sc));
--被所有计算机091班学生选修的课程的cname和chour
SELECT cname, chour
FROM course c
WHERE NOT EXISTS (
    SELECT *
    FROM student s
    WHERE sclass = '计算机091' AND sno NOT IN (
        SELECT sno
        FROM sc
        WHERE sc.cno = c.cno
    )
);
--not in与not exists 转换通过一个连接进行
SELECT cname, chour
FROM course c
WHERE NOT EXISTS (
    SELECT *
    FROM student s
    WHERE sclass = '计算机091' AND not exists (
        SELECT *
        FROM sc
        WHERE sc.cno = c.cno and sc.sno=s.sno
    )
);
--查询至少选修201215122选修的全部课程学生号码 不存在它选了但是检索到的学生没选
--1)我们要查询这样的学生student对应的学号sno
select sno
from student
--2)不存在一个201215122学生的选课记录scx 后边还涉及选课
where not exists(
                select *
                from sc scx
                where sno='0905010102'
--3)student没有选修scx对应的课程，也即是不存在一个student选修了scx.cno对应课程的选课记录scy
                    and not exists (
                    select *
                    from sc scy
                    where scy.sno=student.sno and scy.cno=scx.cno));--没有这句话就是全部学生学号都输出了

--查询年龄超过所在班级平均年龄的学生姓名、性别、年龄、所在班级
select sname,ssex,sage,sclass
from student s1
where sage>(
        select avg(sage)
        from student s2
        where s1.sclass=s2.sclass);

select sno from course,sc
where course.cno=sc.cno and sno=sc.sno and cname='数据库原理' and grade=(
        select grade from sc,student,course
        where sc.sno=student.sno and course.cno=sc.cno and sname='王辰' and cname='数据库原理');

select top 1 sname from student,sc
where sc.sno=student.sno and sclass='计算机091' and student.sno in (
    select sno
    from sc,course
    where sc.cno=course.cno and cname='数据库原理'
 )
 order by grade desc;

 select sno
 from course,sc
 where cname='数据库原理' and grade=(
    select grade
    from sc,course,student
    where sc.sno=student.sno and course.cno=sc.cno and sname='王辰' and cname='数据库原理');

select distinct student.sno,student.sname
from student,sc
where student.sno=sc.sno and grade<70;

select max(grade)
from sc,course
where sc.cno=course.cno and cname='数据库原理';

select sname
from sc,student
where sc.sno=student.sno 
group by sc.sno,sname
having avg(grade)>70;

select s.sname,s.sclass
from student s
where sno not in(
    select sno
    from sc
    where cno in(
        select cno
        from course
        where cname='数据库原理'));