您的位置:新葡亰496net > 网络数据库 > 操作练习,第四模块MySQL50题作业

操作练习,第四模块MySQL50题作业

发布时间:2019-10-20 23:49编辑:网络数据库浏览(94)

    一、表关系

    (一)

    一、表关系

    不错的sql面试题   

     

    先参照如下表结构创建7张表格,并创建相关约束

     
    

    答案:

    一、表关系

                   
    班级表:class       学生表:student      
    cid caption grade_id   sid sname gender class_id
    1 一年一班 1   1 乔丹 1
    2 二年一班 2   2 艾弗森 1
    3 三年二班 3   3 科比 2
                   
    老师表:teacher       课程表:course      
    tid tname     cid cname teacher_id  
    1 张三     1 生物 1  
    2 李四     2 体育 1  
    3 王五     3 物理 2  
                   
    成绩表:score        

    年级表:

    class_grade

       
    sid student_id course_id score   gid gname  
    1 1 1 60   1 一年级  
    2 1 2 59   2 二年级  
    3 2 2 99   3 三年级  
                   
    班级任职表:teach2cls              
    tcid tid cid          
    1 1 1          
    2 1 2          
    3 2 1          
    4 3 2        

    1.写出一条Sql语句:取出表A中第31到第40记录(SQLServer, 以自动增长的ID作为主键,  注意:ID可能不是连续的。)
    select top 10 * from A where id not in (select top 30 id from A)
    解2: select top 10 * from A where id > (selectmax(id) from (select top 30 id from A )as A)

    答案一:

    Student(S#,Sname,Sage,Ssex) 学生表
    Course(C#,Cname,T#) 课程表
    SC(S#,C#,score) 成绩表
    Teacher(T#,Tname) 教师表

    请创建如下表,并创建相关约束

     

    1. 班级表class

    新葡亰496net 1新葡亰496net 2

    create table class
        (
        cid int primary key auto_increment,
        caption char(10),
        grade_id int
        );
    

    【创建表语句】

    新葡亰496net 3新葡亰496net 4

    insert into class values
    (1,'少一一班',1),
    (2,'少二一班',2),
    (3,'少三二班',3),
    (4,'少四一班',4),
    (5,'少五三班',5);
    

    【插入记录语句】

    1. 学生表student

    新葡亰496net 5新葡亰496net 6

    create table student
        (
        sid int primary key auto_increment,
        sname char(10),
        gender enum('男','女') not null,
        class_id int
        );
    

    【创建表语句】

    新葡亰496net 7新葡亰496net 8

    insert into student values
    (1,'乔丹','女',1),
    (2,'艾弗森','女',1),
    (3,'科比','男',2),
    (4,'葫芦娃','男',3),
    (5,'张三丰','男',5),
    (6,'洞房不败','男',4),
    (7,'樱木花道','男',2),
    (8,'松岛菜菜子','女',3),
    (9,'洞房不败','女',5);
    

    【插入记录语句】

    1. 老师表teacher

    新葡亰496net 9新葡亰496net 10

    create table teacher
        (
        tid int primary key auto_increment,
        tname char(10)
        );
    

    【创建表语句】

    新葡亰496net 11新葡亰496net 12

    insert into teacher values
    (1,'张三'),
    (2,'李四'),
    (3,'王五'),
    (4,'萧峰'),
    (5,'一休哥'),
    (6,'诸葛'),
    (7,'李四');
    

    【插入记录语句】

    1. 课程表course

    新葡亰496net 13新葡亰496net 14

    create table course
    (
    cid int primary key auto_increment,
    cname char(10),
    teacher_id int
    );
    

    【创建表语句】

    新葡亰496net 15新葡亰496net 16

    insert into course values
    (1,'生物',1),
    (2,'体育',1),
    (3,'物理',2),
    (4,'数学',3),
    (5,'语文',4),
    (6,'英语',2),
    (7,'土遁?沙地送葬',5),
    (8,'夏日喂蚊子大法',3),
    (9,'麻将牌九扑克千术',6);
    

    【插入记录语句】

    1. 成绩表score

    新葡亰496net 17新葡亰496net 18

    create table score
    (
    sid int primary key auto_increment,
    student_id int,
    course_id int,
    score int
    );
    

    【创建表语句】

    新葡亰496net 19新葡亰496net 20

    insert score values
    (1,1,1,60),
    (2,1,2,21),
    (3,2,2,99),
    (4,3,3,56),
    (5,4,1,56),
    (6,5,3,94),
    (7,5,4,40),
    (8,6,4,80),
    (9,7,3,37),
    (10,8,5,100),
    (11,8,6,89),
    (12,8,7,0),
    (13,3,8,45),
    (14,7,1,89),
    (15,2,7,89),
    (16,2,1,61);
    

    【插入记录语句】

    1. 年级表class_grade

    新葡亰496net 21新葡亰496net 22

    create table class_grade
        (
        gid int primary key auto_increment,
        gname char(10)
        );
    

    【创建表语句】

    新葡亰496net 23新葡亰496net 24

    insert class_grade values
    (1,'少一年级'),
    (2,'少二年级'),
    (3,'少三年级'),
    (4,'少四年级'),
    (5,'少五年级');
    

    【插入记录语句】

    1. 班级任职表teach2cls

    新葡亰496net 25新葡亰496net 26

    create table teach2cls
        (
        tcid int primary key auto_increment,
        tid int,
        cid int
        );
    

    【创建表语句】

    新葡亰496net 27新葡亰496net 28

    insert into teach2cls values
    (1,1,1),
    (2,1,2),
    (3,2,1),
    (4,3,2),
    (5,4,5),
    (6,5,3),
    (7,5,5),
    (8,6,2),
    (9,6,4),
    (10,6,3),
    (11,4,1),
    (12,1,4);
    

    【插入记录语句】

     

    二、操作表

    ★注:由于样本数量有限,为了能够得到足够的查询结果,所有题目中涉及到“超过”或“以上”字样的,均默认为包含该值

    (例如:查询教授课程超过2门的老师的id和姓名,视作教授课程数>=2)

     

    1、自行创建测试数据;

    (创建语句见"一、表关系")

    2、查询学生总人数

    select 
        count(*) as 学生总人数 
    from 
        student;
    

     

    3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名

    【查法1——子查询】

    select 
        sid, sname
    from 
        student 
    where 
        sid in
        (
        select 
            student_id
        from 
            score
        where 
            student_id in
                (select 
                    student_id
                from 
                    score
                where 
                    course_id = (select cid from course where cname = '生物') 
                    and 
                    score >= 60)
            and 
                course_id = 
                (select cid from course where cname = '物理') 
            and 
                score >= 60
        );
    

    【查法2——联表】

    select 
        sid, 
        sname 
    from 
        student
    where 
        sid in 
        (
        select 
            t1.student_id 
        from 
            (
            select 
                student_id 
            from 
                score
            where 
                course_id = (select cid from course where cname = '生物') 
                and 
                score >= 60
            ) as t1
            inner join 
                (
                select 
                    student_id 
                from 
                    score 
                where 
                    course_id = (select cid from course where cname = '物理') 
                    and 
                    score >= 60
                ) as t2
            on 
                t1.student_id=t2.student_id
        );
    

     

    4、查询每个年级的班级数,取出班级数最多的前三个年级

    select 
        class.grade_id, 
        class_grade.gname, 
        count(class.cid) as 班级数
    from 
        class inner join class_grade 
        on class.grade_id=class_grade.gid
    group by 
        class.grade_id
    order by 
        count(class.cid) desc
    limit 3;
    

     

    5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩

    select 
        stu.sid, 
        stu.sname, 
        avg(score) as 平均成绩
    from 
        student as stu inner join score as sco
        on stu.sid = sco.student_id
    group by 
        stu.sid
    having 
        avg(score) = 
            (
            select 
                avg(score) 
            from 
                score 
            group by 
                student_id
            order by 
                avg(score) desc
            limit 1
            ) 
        or 
            avg(score) = 
                (
                select 
                    avg(score) 
                from 
                    score 
                group by 
                    student_id
                order by 
                    avg(score) asc
                limit 1
                );
    

     

    6、查询每个年级的学生人数

    select 
        t1.gname, 
        count(s.sid) as 学生人数
    from 
        (
        select 
            * 
        from 
            class as c inner join class_grade as g 
            on c.grade_id = g.gid
        ) as t1
        inner join 
            student as s 
        on 
            t1.cid = s.class_id
    group by 
        t1.gid;
    

     

    7、查询每位学生的学号,姓名,选课数,平均成绩

     

    select 
        stu.sid as 学号,
        stu.sname as 姓名,
        count(sco.course_id) as 选课数,
        avg(sco.score) as 平均成绩
    from 
        student as stu left join score as sco 
        on stu.sid = sco.student_id
    group by 
        sco.student_id;
    

     

    8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数

    select 
        t1.sname as 姓名,
        t2.cname as 课程名,
        t1.score as 分数
    from 
        (select 
            stu.sid, stu.sname, sco.course_id, sco.score 
        from 
            student as stu inner join score as sco 
            on stu.sid = sco.student_id 
            where stu.sid=2) as t1
        inner join
            course as t2 
        on 
            t1.course_id = t2.cid
    group by 
        t2.cid
    having 
        score in (max(score),min(score));
    

     

    9、查询姓“李”的老师的个数和所带班级数;

    select 
        count(te.tid) as 姓李老师个数,
        count(tc.cid) as 所带班级数
    from 
        teacher as te inner join teach2cls as tc
        on te.tid = tc.tid
    where 
        te.tname regexp "^李.*"
    group by 
        te.tid;
    

     

    10、查询班级数小于5的年级id和年级名;

    select 
        c.grade_id as 年级id,
        g.gname as 年级名
    from 
        class as c inner join class_grade as g
        on c.grade_id = g.gid
    group by 
        c.grade_id
    having 
        count(c.cid)<5;
    

     

    11、查询班级信息,包括班级id、班级名称、年级、年级级别(12为低年级,34为中年级,56为高年级),示例结果如下;

    新葡亰496net 29

    select 
        cid as 班级id,
        caption as 班级名称,
        gname as 年级,
        case
            when g.gid in (1,2) then '低年级'
            when g.gid in (3,4) then '中年级'
            when g.gid in (5,6) then '高年级'
            else '其他' 
        end as 年级级别
    from 
        class as c inner join class_grade as g
        on c.grade_id = g.gid;
    

     

    12、查询学过“张三”老师2门课以上的同学的学号、姓名;

    select 
        stu.sid as 学号,
        stu.sname as 姓名
    from 
        student as stu inner join score as sco 
        on stu.sid = sco.student_id
    where 
        sco.course_id in 
            (
            select 
                c.cid
            from 
                teacher as t inner join course as c
                on t.tid = c.teacher_id
            where 
                t.tname = '张三'
            )
    group by 
        stu.sid
    having 
        count(sco.course_id) >= 2;
    

    13、查询教授课程超过2门的老师的id和姓名;

    select
        tid as id,
        tname as 姓名
    from 
        teacher as t inner join course as c 
        on t.tid = c.teacher_id
    group by 
        c.teacher_id
    having 
        count(c.cid) >= 2;
    

     

    14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名;

    select 
        sid as 学号,
        sname as 姓名
    from 
        student
    where 
        sid in 
            (
            select 
                student_id 
            from 
                score
            where 
                student_id in 
                    (
                    select 
                        student_id 
                    from 
                        score
                    where 
                        course_id = 1
                    )
                and 
                    course_id = 2
            );
    

     

    15、查询没有带过高年级的老师id和姓名;

    select 
        tid as 老师id,
        tname as 姓名
    from 
        teacher
    where 
        tid not in 
            (
            select 
                tc.tid
            from 
                class as c inner join teach2cls as tc 
                on c.cid = tc.cid
            where 
                c.grade_id in (5,6)
            );
    

     

    16、查询学过“张三”老师所教的所有课的同学的学号、姓名;

    select 
        distinct
        stu.sid as 学号,
        stu.sname as 姓名
    from 
        student as stu inner join score as sco 
        on stu.sid = sco.student_id
    where 
        sco.course_id in 
            (
            select 
                c.cid 
            from 
                teacher as t inner join course as c 
                on t.tid = c.teacher_id
            where 
                t.tname = "张三"
            );
    

     

    17、查询带过超过2个班级的老师的id和姓名;

    select 
        tid as id,
        tname as 姓名
    from 
        teacher
    where 
        tid in 
            (
            select 
                tid 
            from 
                teach2cls
            group by 
                tid
            having 
                count(cid) >= 2
            );
    

     

    18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名;

    select 
        sid as 学号,
        sname as 姓名
    from 
        student
    where 
        sid in
            (
            select 
                t1.student_id
            from 
                (
                select 
                    * 
                from 
                    score
                where 
                    course_id = 1
                ) as t1
                inner join 
                    (
                    select 
                        * 
                    from 
                        score 
                    where 
                        course_id = 2
                    ) as t2
                on 
                    t1.student_id = t2.student_id
            where 
                t1.score > t2.score
            );
    

     

    19、查询所带班级数最多的老师id和姓名;

    select 
        tid as id,
        tname as 姓名
    from 
        teacher 
    where 
        tid in 
            (
            select 
                tid
            from 
                teach2cls
            group by 
                tid
            having 
                count(cid) = 
                    (
                    select 
                        count(cid)
                    from 
                        teach2cls
                    group by 
                        tid
                    order by 
                        count(cid) desc
                    limit 1
                    )
            );
    

     

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

    select 
        sid as 学号,
        sname as 姓名
    from 
        student
    where 
        sid in 
            (
            select 
                student_id
            from 
                score 
            where 
                score < 60
            );
    

     

    21、查询没有学全所有课的同学的学号、姓名;

    select 
        sid as 学号,
        sname as 姓名
    from 
        student
    where 
        sid in 
            (
            select 
                student_id
            from 
                score
            group by 
                student_id
            having 
                count(course_id) != (select count(cid) from course)
            );
    

     

    22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名;

    select 
        sid as 学号,
        sname as 姓名
    from 
        student
    where 
        sid in 
            (
            select 
                student_id
            from 
                score
            where 
                course_id in 
                    (
                    select 
                        course_id 
                    from 
                        score
                    where 
                        student_id = 1
                    )
            );
    

     

    23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名;

    select 
        sid as 学号,
        sname as 姓名
    from 
        student
    where 
        sid in 
            (
            select 
                student_id
            from 
                score
            where 
                course_id in 
                    (
                    select 
                        course_id 
                    from 
                        score
                    where 
                        student_id = 1
                    ) 
                and 
                    student_id != 1
            );
    

     

    24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名;

    select 
        sid as 学号,
        sname as 姓名
    from 
        student
    where 
        sid in 
            (select 
                student_id
            from 
                score
            where 
                student_id != 2
            group by 
                student_id
            having 
                group_concat(course_id order by course_id asc) = 
                    (select 
                        group_concat(course_id order by course_id asc)
                    from 
                        score
                    where 
                        student_id = 2
                    group by 
                        student_id)
            );
    

     

    25、删除学习“张三”老师课的score表记录;

    delete 
    from 
        score
    where 
        course_id in 
            (
            select 
                c.cid 
            from 
                teacher as t inner join course as c
                on t.tid = c.teacher_id
            where 
                t.tname = '张三'
            );
    

     

    26、向score表中插入一些记录,这些记录要求符合以下条件:

    ①没有上过编号“2”课程的同学学号;②插入“2”号课程的平均成绩;

    【插入第一条】

    insert into 
        score(student_id, course_id, score) 
    values
        (
            (
            select 
                sid
            from 
                student
            where 
                sid not in
                    (
                    select 
                        s.student_id
                    from 
                        score as s
                    where 
                        s.course_id = 2
                    )
            order by 
                sid desc
            limit 0,1
            ),
            2,
            (
            select 
                avg(s.score)
            from 
                score as s
            where 
                s.course_id = 2
            )
        );
    

    【插入第二条】

    insert into 
        score(student_id, course_id, score) 
    values
        (
            (
            select 
                sid
            from 
                student
            where 
                sid not in
                    (
                    select 
                        s.student_id
                    from 
                        score as s
                    where 
                        s.course_id = 2
                    )
            order by 
                sid desc
            limit 1,1
            ),
            2,
            (
            select 
                avg(s.score)
            from 
                score as s
            where 
                s.course_id = 2
            )
        );
    

    【改limit后的第一个参数值,可继续插入第三、四、...条】

    27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分;

    【这里题目有歧义:有效课程数和有效平均分是仅以这3门课来统计,还是以学生所有科目来统计】

    【解一:仅以这3门课来统计】

    select 
        t2.sid as 学生ID, 
        sum(case when t1.cname = '语文' then t1.score else null end) as 语文,
        sum(case when t1.cname = '数学' then t1.score else null end) as 数学,
        sum(case when t1.cname = '英语' then t1.score else null end) as 英语,
        count(case when t1.cname in ('语文','数学','英语') then 1 else null end) as 有效课程数,
        avg(case when t1.cname in ('语文','数学','英语') then t1.score else null end) as 有效平均分
    from 
        (select * from score as s inner join course as c
        on s.course_id = c.cid) as t1
        right join 
            student as t2
        on 
            t1.student_id = t2.sid
    group by 
        t2.sid
    order by 
        avg(case when t1.cname in ('语文','数学','英语') then t1.score else null end) asc;
    

    【解二:以该学生所有科目来统计】

    select 
        t2.sid as 学生ID, 
        sum(case when t1.cname = '语文' then t1.score else null end) as 语文,
        sum(case when t1.cname = '数学' then t1.score else null end) as 数学,
        sum(case when t1.cname = '英语' then t1.score else null end) as 英语,
        count(t1.score) as 有效课程数,
        avg(t1.score) as 有效平均分
    from 
        (select * from score as s inner join course as c
        on s.course_id = c.cid) as t1
        right join 
            student as t2
        on 
            t1.student_id = t2.sid
    group by 
        t2.sid
    order by 
        avg(t1.score) asc;
    

     

    28、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;

    select 
        course_id as 课程ID,
        max(score) as 最高分,
        min(score) as 最低分
    from 
        score
    group by 
        course_id;
    

     

    29、按各科平均成绩从低到高和及格率的百分数从高到低顺序;

    【这里优先按平均成绩从低到高排序,若遇到平均成绩相同的则按及格率百分数从高到低排序】

    select 
        course_id as 课程ID,
        avg(score) as 平均成绩,
        concat(100*count(case when score>=60 then 1 else null end)/count(score),"%") as 及格率
    from 
        score
    group by 
        course_id
    order by 
        avg(score) asc, 
        count(case when score>=60 then 1 else null end)/count(score) desc;
    

     

    30、课程平均分从高到低显示(显示任课老师);

    select 
        t1.cname as 课程名称,
        avg(t2.score) as 平均分,
        t1.tname as 任课老师
    from 
        (select * from teacher as t inner join course as c
        on t.tid = c.teacher_id) as t1 
        inner join 
            score as t2
        on 
            t1.cid = t2.course_id
    group by 
        t2.course_id
    order by 
        avg(t2.score) desc;
    

     

    31、查询各科成绩前三名的记录(不考虑成绩并列情况)

    【本题与44题类似,不会做,于是百度了下"如何在mysql中查询每个分组的前几名",参照其中的一个比较优雅的方法,写出了答案】

    【注:这里仍然是按照score表默认的排序,即sid的排序】

    select
        *
    from 
        score
    where
        (
        select
            count(*)
        from 
            score as s
        where
            s.course_id = score.course_id
            and
            s.score <= score.score
        )
        <= 3;
    

     

    32、查询每门课程被选修的学生数;

    select 
        cname as 课程名,
        count(s.student_id) as 选修学生数
    from 
        course as c left join score as s 
        on c.cid = s.course_id
    group by 
        c.cid;
    

     

    33、查询选修了2门以上课程的全部学生的学号和姓名;

    select 
        sid as 学号,
        sname as 姓名
    from 
        student
    where 
        sid in 
            (
            select 
                student_id 
            from 
                score
            group by 
                student_id
            having 
                count(course_id) >= 2
            );
    

     

    34、查询男生、女生的人数,按倒序排列;

    select 
        gender, count(sid)
    from 
        student
    group by 
        gender
    order by 
        count(sid) desc;
    

     

    35、查询姓“张”的学生名单;

    【查法1——正则】

    select 
        sname 
    from 
        student
    where 
        sname regexp "^张.*";
    

    【查法2——like】

    select 
        sname
    from 
        student
    where 
        sname like "张%";
    

     

    36、查询同名同姓学生名单,并统计同名人数;

    select 
        sname as 姓名,
        count(sid) as 同名人数 
    from 
        student
    group by 
        sname
    having 
        count(sid) > 1;
    

     

    37、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;

    select 
        avg(score),course_id
    from 
        score
    group by 
        course_id
    order by 
        avg(score) asc, course_id desc;
    

     

    38、查询课程名称为“数学”,且分数低于60的学生姓名和分数;

    select 
        stu.sname as 学生姓名,
        sco.score as 分数
    from 
        student as stu inner join score as sco
        on stu.sid = sco.student_id
    where 
        sco.course_id = 
            (
            select 
                cid 
            from 
                course 
            where 
                cname = '数学'
            )
        and 
            sco.score < 60;
    

     

    39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名;

    select 
        sid as 学号,
        sname as 姓名
    from 
        student
    where 
        sid in 
            (
            select 
                student_id 
            from 
                score
            where 
                course_id = 3 
                and 
                score >= 80
            );
    

     

    40、求选修了课程的学生人数

    select 
        count(1) as 学生人数
    from
        (
        select 
            distinct student_id
        from 
            score
        ) as t1;
    

     

    41、查询选修“王五”老师所授课程的学生中,成绩最高和最低的学生姓名及其成绩;

    select 
        stu.sname as 学生姓名,
        sco.score as 成绩
    from 
        student as stu inner join score as sco
        on stu.sid = sco.student_id
    where 
        score in 
            (
                (
                select 
                    max(score)
                from 
                    score
                where 
                    course_id in 
                        (
                        select 
                            c.cid
                        from 
                            teacher as t inner join course as c
                            on t.tid = c.teacher_id 
                        where 
                            t.tname = '王五'
                        )
                ), 
                (
                select 
                    min(score)
                from 
                    score
                where 
                    course_id in 
                        (
                        select 
                            c.cid
                        from 
                            teacher as t inner join course as c
                            on t.tid = c.teacher_id 
                        where 
                            t.tname = '王五'
                        )
                )
            );
    

     

    42、查询各个课程及相应的选修人数;

    select 
        cname as 课程名,
        count(s.student_id) as 选修学生数
    from 
        course as c left join score as s 
        on c.cid = s.course_id
    group by 
        c.cid;
    

     

    43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩;

    select 
        student_id as 学号,
        course_id as 课程号,
        score as 学生成绩
    from 
        score
    group by 
        score
    having 
        count(student_id) > 1;
    

     

    44、查询每门课程成绩最好的前两名学生id和姓名;

    【注:这里指定了前两名,所以若出现多名同分的学生也只取倒序排的默认前2名】

    【与31题类似…不会写,于是百度了下"如何在mysql中查询每个分组的前几名",参照其中一种比较高端且高效的自定义变量的方法,写出了答案】

    set @num := 0, @cname := '';
    select
        t2.cid as 课程ID,
        t2.cname as 课程名,
        t1.sid as 学生ID,
        t1.sname as 学生名,
        t1.score as 成绩,
        @num := if(@cname = t2.cname, @num   1, 1) as 排名,
        @cname := t2.cname as 课程名确认
    from 
        (
        select 
            stu.sid, stu.sname, sco.course_id, sco.score 
        from 
            student as stu inner join score as sco
            on stu.sid = sco.student_id
        ) as t1
        right join
            course as t2
        on 
            t1.course_id = t2.cid
    group by
        t2.cid, t1.score, t1.sname
    having
        排名 <= 2;
    

    但是导师不推荐这种写法,于是附上导师的参考答案吧

    select c.sid, a.course_id, c.sname, d.cname, a.score
    from 
      score a
      inner join 
      (
       select course_id, score, rank
       from 
        (
        select a.course_id, a.score, count(*) as rank
           from 
          (select course_id, score
               from score
               group by course_id, score
               order by course_id, score desc) a
               inner join 
          (select course_id, score
               from score
               group by course_id, score
               order by course_id, score desc) b
               on a.course_id = b.course_id
               and a.score <= b.score
               group by course_id, score
              ) t1
          where rank in (1, 2)
          order by course_id, rank
          ) b
          on a.course_id = b.course_id
          and a.score = b.score
       inner join student c
       on a.student_id = c.sid
       inner join course d
       on a.course_id = d.cid
    order by course_id, score desc, sid asc
    ;
    

     

    45、检索至少选修两门课程的学生学号;

    select 
        sid as 学号
    from 
        student
    where 
        sid in 
            (
            select 
                student_id 
            from 
                score
            group by 
                student_id
            having 
                count(course_id) >= 2
            );
    

     

    46、查询没有学生选修的课程的课程号和课程名;

    select 
        cid as 课程号,
        cname as 课程名
    from 
        course
    where 
        cid not in 
            (
            select 
                distinct course_id
            from 
                score
            );
    

     

    47、查询没带过任何班级的老师id和姓名;

    select
        tid as 老师id,
        tname as 姓名
    from 
        teacher
    where 
        tid not in 
            (
            select 
                distinct tid
            from 
                teach2cls);
    

     

    48、查询有两门以上课程超过80分的学生id及其平均成绩;

    select 
        student_id as 学生id,
        avg(score) as 平均成绩
    from 
        score
    where 
        student_id in 
            (
            select 
                student_id
            from 
                score
            where 
                score >= 80
            group by 
                student_id
            having 
                count(course_id) >= 2
            )
    group by 
        student_id;
    

     

    49、检索“3”课程分数小于60,按分数降序排列的同学学号;

    select distinct
        student_id as 学号
    from 
        score
    where 
        course_id = 3 and score < 60
    order by 
        score desc;
    

     

    50、删除编号为“2”的同学的“1”课程的成绩;

    delete 
    from 
        score
    where 
        student_id = 2 and course_id = 1;
    

     

    51、查询同时选修了物理课和生物课的学生id和姓名;

    select 
        sid as 学生id,
        sname as 姓名
    from 
        student
    where 
        sid in 
            (
            select 
                student_id
            from 
                score
            where 
                course_id = (select cid from course where cname = '生物')
            )
        and 
            sid in 
                (
                select 
                    student_id
                from 
                    score
                where 
                    course_id = 
                        (
                        select 
                            cid 
                        from 
                            course 
                        where 
                            cname = '物理'
                        )
                );
    

     

    三、作业引申

    ★count(*)、count(1)与count(COL)的抉择?

    【参考文章】《Select count(*)和Count(1)的区别和执行方式》

      往常我经常会看到一些所谓的优化建议不使用Count(* )而是使用Count(1),从而可以提升性能,给出的理由是Count( *)会带来全表扫描。而实际上如何写Count并没有区别。

      实际上 count 的意思是,评估count()中的表达式是否为NULL,如果括号中表达式为NULL则结果不计数(计为0),而括号中表达式非NULL则会计数。

      1、比如我们看下面的所示,在 count 中指定NULL(优化器不允许显式指定NULL,因此需要赋值给自定义变量才能指定)。

    SET @xx=NULL
    
    SELECT COUNT(@xx) FROM class;
    

      由于这里相当于对所有行都计为NULL,所以结果全部计数为0,结果如下图所示。

     ------------ 
    | count(@xx) |
     ------------ 
    |          0 |
     ------------ 
    1 row in set (0.00 sec)
    

      2、因此当你指定Count(*)或者Count(1)或者无论Count(‘anything’)时结果都会一样,因为括号里这些值都不为NULL,语句如下图所示。

    select count(*) from class;
    
    select count(1) from class;
    
    select count('anything') from class;
    

      运行结果都一样:

     ---------- 
    | count(*) |
     ---------- 
    |        5 |
     ---------- 
    1 row in set (0.00 sec)
    
     ---------- 
    | count(1) |
     ---------- 
    |        5 |
     ---------- 
    1 row in set (0.00 sec)
    
     ------------------- 
    | count('anything') |
     ------------------- 
    |                 5 |
     ------------------- 
    1 row in set (0.00 sec)
    

      3、那么count(COL)对某一列(字段)进行计数呢?

      对于Count(列)来说,同样适用于上面规则,评估括号内的列中每一行的值是否为NULL,如果某行为NULL则该行不计数,某行不为NULL则该行计数。因此Count(列)会计算列或这列的组合不为空的计数。

      例如下面这张test表:

     ------ ------ ------ 
    | a    | b    | c    |
     ------ ------ ------ 
    |    1 |    1 |    1 |
    |    2 |    2 |    2 |
    | NULL | NULL | NULL |
     ------ ------ ------ 
    3 rows in set (0.00 sec)
    

      我们使用count(a) 对a列进行计数,语句和结果如下:

    select count(a) from test;
    
     ---------- 
    | count(a) |
     ---------- 
    |        2 |
     ---------- 
    1 row in set (0.00 sec)
    

      而我们用count(*)(或count(1)、count('anything')等)则计数为3:

    select count(*) from test;
    
     ---------- 
    | count(*) |
     ---------- 
    |        3 |
     ---------- 
    1 row in set (0.00 sec)
    

    【总结】

      如果我们要统计有多少条记录(连全为null的记录也算),则直接用count(*)或count(1)都可以;

      如果我们要统计某一列有多少条有效记录(为null的记录/行不算),则用count(列)的方式。

     

    ★group_concat的具体玩法?

    【参考文章】《mysql之group_concat函数详解》

      group_concat函数的具体语法如下:

    group_concat( [DISTINCT]  要连接的字段   [Order BY 排序字段 ASC/DESC]   [Separator '分隔符'] )
    

      下面举例说明

    select * from goods;
    
     ------ ------- 
    | id   | price |
     ------ ------- 
    |    1 |    10 |
    |    1 |    20 |
    |    1 |    20 |
    |    2 |    20 |
    |    3 |   200 |
    |    3 |   500 |
     ------ ------- 
    6 rows in set (0.00 sec)
    

      group_concat的基本功能是:以id分组,把price字段的值在同一行打印出来,逗号分隔(默认)

    select id, group_concat(price) from goods group by id;
    
     ------ --------------------- 
    | id   | group_concat(price) |
     ------ --------------------- 
    |    1 | 10,20,20            |
    |    2 | 20                  |
    |    3 | 200,500             |
     ------ --------------------- 
    3 rows in set (0.00 sec)
    

      对于这样的查询结果,我们可以使用group_concat的参数对结果进行一些处理:

      1、以id分组,把price字段的值在一行打印出来,改为"/"号分隔 

    select id, group_concat(price separator "/") from goods group by id;
    
     ------ ----------------------------------- 
    | id   | group_concat(price separator "/") |
     ------ ----------------------------------- 
    |    1 | 10/20/20                          |
    |    2 | 20                                |
    |    3 | 200/500                           |
     ------ ----------------------------------- 
    3 rows in set (0.00 sec)
    

      2、以id分组,把去除重复冗余的price字段的值打印在一行,逗号分隔

    select id,group_concat(distinct price) from goods group by id; 
    
     ------ ------------------------------ 
    | id   | group_concat(distinct price) |
     ------ ------------------------------ 
    |    1 | 10,20                        |
    |    2 | 20                           |
    |    3 | 200,500                      |
     ------ ------------------------------ 
    3 rows in set (0.00 sec)
    

      3、以id分组,把price字段的值打印在一行,逗号分隔,按照price倒序排列

    select id,group_concat(price order by price desc) from goods group by id;
    
     ------ ----------------------------------------- 
    | id   | group_concat(price order by price desc) |
     ------ ----------------------------------------- 
    |    1 | 20,20,10                                |
    |    2 | 20                                      |
    |    3 | 500,200                                 |
     ------ ----------------------------------------- 
    3 rows in set (0.00 sec)
    

     

    ★SELECT CASE WHEN的具体玩法?

    【参考文章】《CASE WHEN 及 SELECT CASE WHEN的用法》

      case when能为我们提供什么样的玩法呢?

      1、已知数据按照另外一种方式进行分组,分析。

      例如:根据如下的国家人口数据,统计亚洲和北美洲的人口数量。

      先创建表格,并插入数据

    新葡亰496net 30新葡亰496net 31

    create table population(
    country char(20) primary key,
    population int);
    

    创建表格

    新葡亰496net 32新葡亰496net 33

    insert into population values
    ('中国', 600),
    ('美国', 100),
    ('加拿大',100),
    ('英国', 200),
    ('法国', 300),
    ('日本', 250),
    ('德国', 200),
    ('墨西哥', 50),
    ('印度', 250);
    

    插入数据

      得到表格:

     ----------- ------------ 
    | country   | population |
     ----------- ------------ 
    | 中国      |        600 |
    | 加拿大    |        100 |
    | 印度      |        250 |
    | 墨西哥    |         50 |
    | 德国      |        200 |
    | 日本      |        250 |
    | 法国      |        300 |
    | 美国      |        100 |
    | 英国      |        200 |
     ----------- ------------ 
    9 rows in set (0.00 sec)
    

      用CASE WHEN ELSE END对字段进行分类处理:

    SELECT  
        CASE country 
            WHEN '中国' THEN '亚洲' 
            WHEN '印度' THEN '亚洲' 
            WHEN '日本' THEN '亚洲' 
            WHEN '美国' THEN '北美洲' 
            WHEN '加拿大' THEN '北美洲' 
            WHEN '墨西哥' THEN '北美洲' 
            ELSE '其他' 
        END as '洲',
        SUM(population) as '人口'
    FROM 
        population 
    GROUP BY
        CASE country 
            WHEN '中国' THEN '亚洲' 
            WHEN '印度' THEN '亚洲'
            WHEN '日本' THEN '亚洲' 
            WHEN '美国' THEN '北美洲' 
            WHEN '加拿大' THEN '北美洲' 
            WHEN '墨西哥' THEN '北美洲' 
            ELSE '其他' 
        END; 
    

      结果如下:

     ----------- -------- 
    | 洲        | 人口   |
     ----------- -------- 
    | 亚洲      |   1100 |
    | 其他      |    700 |
    | 北美洲    |    250 |
     ----------- -------- 
    3 rows in set (0.00 sec)
    

    【注】题目11——查询班级信息,包括班级id、班级名称、年级、年级级别(12为低年级,34为中年级,56为高年级)

        解法就是用到这种方法

    select 
        cid as 班级id,
        caption as 班级名称,
        gname as 年级,
        case
            when g.gid in (1,2) then '低年级'
            when g.gid in (3,4) then '中年级'
            when g.gid in (5,6) then '高年级'
            else '其他' 
        end as 年级级别
    from 
        class as c inner join class_grade as g
        on c.grade_id = g.gid;
    
     ---------- -------------- ----------- -------------- 
    | 班级id   | 班级名称       | 年级       | 年级级别      |
     ---------- -------------- ----------- -------------- 
    |        1 | 一年一班      | 一年级      | 低年级       |
    |        2 | 二年一班      | 二年级      | 低年级       |
    |        3 | 三年二班      | 三年级      | 中年级       |
    |        5 | 少四一班      | 少五        | 中年级       |
    |        4 | 少五三班      | 少四        | 高年级       |
     ---------- -------------- ----------- -------------- 
    5 rows in set (0.00 sec)
    

     

      2、用一个SQL语句完成不同条件的分组计数

      例如:对下述数据,按照国家和性别进行分组统计。

      先创建表格,并插入数据

    新葡亰496net 34新葡亰496net 35

    create table population2(
    country char(20),
    sex int,
    population int);
    

    创建表格

    新葡亰496net 36新葡亰496net 37

    insert into population2 values
    ('中国', 1, 340),
    ('中国', 2, 260),
    ('美国', 1, 45),
    ('美国', 2, 55),
    ('加拿大',1, 51),
    ('加拿大',2, 49),
    ('英国', 1, 40),
    ('英国', 2, 60);
    

    插入数据

      用CASE WHEN ELSE END对数据进行国家和性别的分组计数:

    SELECT 
        country as '国家', 
        SUM( CASE WHEN sex = '1' THEN  population ELSE 0 END) as '男',
        SUM( CASE WHEN sex = '2' THEN  population ELSE 0 END) as '女'
    FROM 
        population2
    GROUP BY 
        country;
    

      得到分组计数结果:

     ----------- ------ ------ 
    | 国家       | 男   | 女   |
     ----------- ------ ------ 
    | 中国       |  340 |  260 |
    | 加拿大     |   51 |   49 |
    | 美国       |   45 |   55 |
    | 英国       |   40 |   60 |
     ----------- ------ ------ 
    4 rows in set (0.00 sec)
    

    【注】题目27——按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示:学生ID,语文,数学,英语,有效课程数,有效平均分;

        解法就是用到这种方法

    【这里题目有歧义:有效课程数和有效平均分是仅以这3门课来统计,还是以学生所有科目来统计】
    【解一:仅以这3门课来统计】
    select 
        t2.sid as 学生ID, 
        sum(case when t1.cname = '语文' then t1.score else null end) as 语文,
        sum(case when t1.cname = '数学' then t1.score else null end) as 数学,
        sum(case when t1.cname = '英语' then t1.score else null end) as 英语,
        count(case when t1.cname in ('语文','数学','英语') then 1 else null end) as 有效课程数,
        avg(case when t1.cname in ('语文','数学','英语') then t1.score else null end) as 有效平均分
    from 
        (select * from score as s inner join course as c
        on s.course_id = c.cid) as t1
        right join 
            student as t2
        on 
            t1.student_id = t2.sid
    group by 
        t2.sid
    order by 
        avg(case when t1.cname in ('语文','数学','英语') then t1.score else null end) asc;
    
    【解二:以该学生所有科目来统计】
    select 
        t2.sid as 学生ID, 
        sum(case when t1.cname = '语文' then t1.score else null end) as 语文,
        sum(case when t1.cname = '数学' then t1.score else null end) as 数学,
        sum(case when t1.cname = '英语' then t1.score else null end) as 英语,
        count(t1.score) as 有效课程数,
        avg(t1.score) as 有效平均分
    from 
        (select * from score as s inner join course as c
        on s.course_id = c.cid) as t1
        right join 
            student as t2
        on 
            t1.student_id = t2.sid
    group by 
        t2.sid
    order by 
        avg(t1.score) asc;
    
    【解一结果】
     ---------- -------- -------- -------- ----------------- ----------------- 
    | 学生ID    | 语文   | 数学    | 英语    | 有效课程数        | 有效平均分       |
     ---------- -------- -------- -------- ----------------- ----------------- 
    |        2 |      0 |      0 |      0 |               0 |            NULL |
    |        9 |      0 |      0 |      0 |               0 |            NULL |
    |        3 |      0 |      0 |      0 |               0 |            NULL |
    |        7 |      0 |      0 |      0 |               0 |            NULL |
    |        4 |      0 |      0 |      0 |               0 |            NULL |
    |        1 |      0 |      0 |      0 |               0 |            NULL |
    |        5 |      0 |     40 |      0 |               1 |         40.0000 |
    |        6 |      0 |     80 |      0 |               1 |         80.0000 |
    |        8 |    100 |      0 |     89 |               2 |         94.5000 |
     ---------- -------- -------- -------- ----------------- ----------------- 
    9 rows in set (0.00 sec)
    
    【解二结果】
     ---------- -------- -------- -------- ----------------- ----------------- 
    | 学生ID    | 语文   | 数学    | 英语    | 有效课程数        | 有效平均分       |
     ---------- -------- -------- -------- ----------------- ----------------- 
    |        9 |   NULL |   NULL |   NULL |               0 |            NULL |
    |        4 |   NULL |   NULL |   NULL |               0 |            NULL |
    |        1 |   NULL |   NULL |   NULL |               1 |         13.0000 |
    |        7 |   NULL |   NULL |   NULL |               1 |         37.0000 |
    |        3 |   NULL |   NULL |   NULL |               2 |         50.5000 |
    |        8 |    100 |   NULL |     89 |               3 |         63.0000 |
    |        5 |   NULL |     40 |   NULL |               2 |         67.0000 |
    |        6 |   NULL |     80 |   NULL |               1 |         80.0000 |
    |        2 |   NULL |   NULL |   NULL |               1 |         89.0000 |
     ---------- -------- -------- -------- ----------------- ----------------- 
    9 rows in set (0.00 sec)
    

     

      3、在count中直接用CASE WHEN ESLE END来针对符合特定条件的记录进行计数

      这里直接拿 题目29 来举例——按各科平均成绩从低到高和及格率的百分数从高到低顺序:

    【这里优先按平均成绩从低到高排序,若遇到平均成绩相同的则按及格率百分数从高到低排序】
    select 
        course_id as 课程ID,
        avg(score) as 平均成绩,
        concat(100*count(case when score>=60 then 1 else null end)/count(score),"%") as 及格率
    from 
        score
    group by 
        course_id
    order by 
        avg(score) asc, 
        count(case when score>=60 then 1 else null end)/count(score) desc;
    

      查询结果:

     ---------- -------------- ----------- 
    | 课程ID    | 平均成绩      | 及格率     |
     ---------- -------------- ----------- 
    |        2 |         NULL | NULL      |
    |        7 |      34.0000 | 33.3333%  |
    |        8 |      45.0000 | 0.0000%   |
    |        4 |      60.0000 | 50.0000%  |
    |        3 |      62.3333 | 33.3333%  |
    |        6 |      89.0000 | 100.0000% |
    |        5 |     100.0000 | 100.0000% |
     ---------- -------------- ----------- 
    7 rows in set (0.00 sec)
    

     

    ★如何在mysql中同时查询显示每个分组的前几名

    【参考文章】《如何在mysql中查询每个分组的前几名》

      1、一种较优雅的方式

      这里直接拿 题目31举例——查询各科成绩前三名的记录(不考虑成绩并列情况):

    【注:这里仍然是按照score表默认的排序,即sid的排序】
    select
        *
    from 
        score
    where
        (
        select
            count(*)
        from 
            score as s
        where
            s.course_id = score.course_id
            and
            s.score <= score.score
        )
        <= 3;
    
     ----- ------------ ----------- ------- 
    | sid | student_id | course_id | score |
     ----- ------------ ----------- ------- 
    |   1 |          1 |         1 |    60 |
    |   2 |          1 |         2 |    21 |
    |   3 |          2 |         2 |    99 |
    |   4 |          3 |         3 |    56 |
    |   5 |          4 |         1 |    56 |
    |   6 |          5 |         3 |    94 |
    |   7 |          5 |         4 |    40 |
    |   8 |          6 |         4 |    80 |
    |   9 |          7 |         3 |    37 |
    |  10 |          8 |         5 |   100 |
    |  11 |          8 |         6 |    89 |
    |  12 |          8 |         7 |     0 |
    |  13 |          3 |         8 |    45 |
    |  15 |          2 |         7 |    89 |
    |  16 |          2 |         1 |    61 |
     ----- ------------ ----------- ------- 
    15 rows in set (0.00 sec)
    

      缺点——时间复杂度均为分组中条目数的二次方。很多优化器都不能优化这种查询,使得它的耗时最好为全表行数的二次方(尤其在没有设置正确的索引时),而且数据量大时,可能将服务器会停止响应。那么还有更好的方法吗?有没有办法可以仅仅扫描一次数据,而不是通过子查询进行多次扫描。

     

      2、一种更高效的方式(使用自定义变量)

      这里直接拿 题目44举例——查询每门课程成绩最好的前两名学生id和姓名

    set @num := 0, @cname := '';
    select
        t2.cid as 课程ID,
        t2.cname as 课程名,
        t1.sid as 学生ID,
        t1.sname as 学生名,
        t1.score as 成绩,
        @num := if(@cname = t2.cname, @num   1, 1) as 排名,
        @cname := t2.cname as 课程名确认
    from 
        (
        select 
            stu.sid, stu.sname, sco.course_id, sco.score 
        from 
            student as stu inner join score as sco
            on stu.sid = sco.student_id
        ) as t1
        right join
            course as t2
        on 
            t1.course_id = t2.cid
    group by
        t2.cid, t1.score, t1.sname
    having
        排名 <= 2;
    
     ---------- -------------------------- ---------- ----------------- -------- -------- -------------------------- 
    | 课程ID    | 课程名                   | 学生ID    | 学生名           | 成绩    | 排名    | 课程名确认                |
     ---------- -------------------------- ---------- ----------------- -------- -------- -------------------------- 
    |        1 | 生物                      |        4 | 葫芦娃           |     56 |       2 | 生物                     |
    |        1 | 生物                      |        7 | 樱木花道         |     89 |       1 | 生物                     |
    |        2 | 体育                      |        1 | 乔丹            |     21 |       2 | 体育                     |
    |        2 | 体育                      |        2 | 艾弗森          |     99 |       1 | 体育                     |
    |        3 | 物理                      |        7 | 樱木花道         |     37 |       1 | 物理                     |
    |        3 | 物理                      |        3 | 科比            |     56 |       2 | 物理                     |
    |        4 | 数学                      |        5 | 流河旱树         |     40 |       1 | 数学                     |
    |        4 | 数学                      |        6 | 美少女战士       |     80 |       2 | 数学                     |
    |        5 | 语文                      |        8 | 松岛菜菜子       |    100 |       1 | 语文                     |
    |        6 | 英语                      |        8 | 松岛菜菜子       |     89 |       1 | 英语                     |
    |        7 | 土遁•沙地送葬              |        8 | 松岛菜菜子        |      0 |      2 | 土遁•沙地送葬              |
    |        7 | 土遁•沙地送葬              |        2 | 艾弗森           |     89 |      1 | 土遁•沙地送葬              |
    |        8 | 夏日喂蚊子大法             |        3 | 科比             |     45 |      1 | 夏日喂蚊子大法             |
    |        9 | 麻将牌九扑克千术            |     NULL | NULL            |   NULL |      1 | 麻将牌九扑克千术           |
     ---------- -------------------------- ---------- ----------------- -------- -------- -------------------------- 
    14 rows in set (0.00 sec)
    

      这种查询方法在MySQL中只进行一次扫描,而且没有文件排序(filesort)和临时表(但似乎只支持group by为其中一个连接表的主键?)。

     
    

    SELECT
    *
    FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
    LEFT JOIN course ON score.course_id = course.cid

    问题:
    1、查询“001”课程比“002”课程成绩高的所有学生的学号;
      select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
      from SC where C#='002') b
      where a.score>b.score and a.s#=b.s#;
    2、查询平均成绩大于60分的同学的学号和平均成绩;
        select S#,avg(score)
        from sc
        group by S# having avg(score) >60;
    3、查询所有同学的学号、姓名、选课数、总成绩;
      select Student.S#,Student.Sname,count(SC.C#),sum(score)
      from Student left Outer join SC on Student.S#=SC.S#
      group by Student.S#,Sname
    4、查询姓“李”的老师的个数;
      select count(distinct(Tname))
      from Teacher
      where Tname like '李%';
    5、查询没学过“叶平”老师课的同学的学号、姓名;
        select Student.S#,Student.Sname
        from Student  
        where S# not in (select distinct( SC.S#) from SC,Course,Teacher where  SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');
    6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
      select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
    7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
      select S#,Sname
      from Student
      where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher  where Teacher.T#=Course.T# and Tname='叶平'));
    8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
      Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2
      from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score;
    9、查询所有课程成绩小于60分的同学的学号、姓名;
      select S#,Sname
      from Student
      where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);
    10、查询没有学全所有课的同学的学号、姓名;
        select Student.S#,Student.Sname
        from Student,SC
        where Student.S#=SC.S# group by  Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);
    11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
        select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';
    12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;
        select distinct SC.S#,Sname
        from Student,SC
        where Student.S#=SC.S# and C# in (select C# from SC where S#='001');
    13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
        update SC set score=(select avg(SC_2.score)
        from SC SC_2
        where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');
    14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;
        select S# from SC where C# in (select C# from SC where S#='1002')
        group by S# having count(*)=(select count(*) from SC where S#='1002');
    15、删除学习“叶平”老师课的SC表记录;
        Delect SC
        from course ,Teacher  
        where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';
    16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、2、
        号课的平均成绩;
        Insert SC select S#,'002',(Select avg(score)
        from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');
    17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
        SELECT S# as 学生ID
            ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库
            ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理
            ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语
            ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
        FROM SC AS t
        GROUP BY S#
        ORDER BY avg(t.score)  
    18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
        SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分
        FROM SC L ,SC AS R
        WHERE L.C# = R.C# and
            L.score = (SELECT MAX(IL.score)
                          FROM SC AS IL,Student AS IM
                          WHERE L.C# = IL.C# and IM.S#=IL.S#
                          GROUP BY IL.C#)
            AND
            R.Score = (SELECT MIN(IR.score)
                          FROM SC AS IR
                          WHERE R.C# = IR.C#
                      GROUP BY IR.C#
                        );
    19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
        SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩
            ,100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
        FROM SC T,Course
        where t.C#=course.C#
        GROUP BY t.C#
        ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
    20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004)
        SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分
            ,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数
            ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分
            ,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数
            ,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
            ,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数
            ,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分
            ,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数
      FROM SC

    新葡亰496net 38

    2. 写sql语句实现下列查询
    (表)t
    a b(列)
    1 2
    1 3
    1 4
    2 1
    2 2
    3 1
    4 1
    5 3
    5 2
    查询结果要求
    a b
    1 2
    2 1
    3 1
    4 1
    5 2

     

    21、查询不同老师所教不同课程平均分从高到低显示
      SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩
        FROM SC AS T,Course AS C ,Teacher AS Z
        where T.C#=C.C# and C.T#=Z.T#
      GROUP BY C.C#
      ORDER BY AVG(Score) DESC
    22、查询如下课程成绩第 3 名到第 6 名的学生成绩单:企业管理(001),马克思(002),UML (003),数据库(004)
        [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
        SELECT  DISTINCT top 3
          SC.S# As 学生学号,
            Student.Sname AS 学生姓名 ,
          T1.score AS 企业管理,
          T2.score AS 马克思,
          T3.score AS UML,
          T4.score AS 数据库,
          ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0) as 总分
          FROM Student,SC  LEFT JOIN SC AS T1
                          ON SC.S# = T1.S# AND T1.C# = '001'
                LEFT JOIN SC AS T2
                          ON SC.S# = T2.S# AND T2.C# = '002'
                LEFT JOIN SC AS T3
                          ON SC.S# = T3.S# AND T3.C# = '003'
                LEFT JOIN SC AS T4
                          ON SC.S# = T4.S# AND T4.C# = '004'
          WHERE student.S#=SC.S# and
          ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0)
          NOT IN
          (SELECT
                DISTINCT
                TOP 15 WITH TIES
                ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0)

     

     
    

    答案二:

    • ISNULL(T4.score,0)
            FROM sc
                  LEFT JOIN sc AS T1
                            ON sc.S# = T1.S# AND T1.C# = 'k1'
                  LEFT JOIN sc AS T2
                            ON sc.S# = T2.S# AND T2.C# = 'k2'
                  LEFT JOIN sc AS T3
                            ON sc.S# = T3.S# AND T3.C# = 'k3'
                  LEFT JOIN sc AS T4
                            ON sc.S# = T4.S# AND T4.C# = 'k4'
            ORDER BY ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0) DESC);

     

    3.一组通话记录(总共500万条):
    ID 主叫号码 被叫号码 通话起始时间   通话结束时间           通话时长
    1  98290000 0215466546656 2007-02-01 09:49:53.000 2007-02-01 09:50:16.00023
    2  98290000 021546654666  2007-02-01 09:50:29.000 2007-02-0109:50:41.000 12
    3  98290000 021546654666  2007-02-01 09:50:58.000 2007-02-0109:51:12.000 14
    4  68290900 0755133329866 2007-02-01 10:04:31.000 2007-02-01 10:07:13.000162
    5  78290000 0755255708638 2007-02-01 10:48:26.000 2007-02-01 10:49:23.00057
    6  78290000 0755821119109 2007-02-01 10:49:39.000 2007-02-01 10:52:55.000196
    7  78290000 035730928370  2007-02-01 11:30:45.000 2007-02-0111:31:58.000 73
    8  78290000 0871138889904 2007-02-01 11:33:47.000 2007-02-01 11:35:00.00073
    9  68290000 035730928379  2007-02-01 11:52:20.000 2007-02-0111:54:56.000 156
    10 68290000 0298521811199 2007-02-01 12:44:45.000 2007-02-01 12:45:04.000 19
    求其中同一个号码的两次通话之间间隔大于10秒的通话记录ID
    例如:6,7,8,9,10条记录均符合。

    SELECT
    score.sid,
    score.student_id,
    student.sname,
    course.cname,
    score.number
    FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
    LEFT JOIN course ON score.course_id = course.cid

    23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
        SELECT SC.C# as 课程ID, Cname as 课程名称
            ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
            ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
            ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
            ,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]
        FROM SC,Course
        where SC.C#=Course.C#
        GROUP BY SC.C#,Cname;

    二、操作表

     
    

    二、操作表

    24、查询学生平均成绩及其名次
          SELECT 1 (SELECT COUNT( distinct 平均成绩)
                  FROM (SELECT S#,AVG(score) AS 平均成绩
                          FROM SC
                      GROUP BY S#
                      ) AS T1
                WHERE 平均成绩 > T2.平均成绩) as 名次,
          S# as 学生学号,平均成绩
        FROM (SELECT S#,AVG(score) 平均成绩
                FROM SC
            GROUP BY S#
            ) AS T2
        ORDER BY 平均成绩 desc;
     
    25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
          SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
          FROM SC t1
          WHERE score IN (SELECT TOP 3 score
                  FROM SC
                  WHERE t1.C#= C#
                ORDER BY score DESC
                  )
          ORDER BY t1.C#;
    26、查询每门课程被选修的学生数
      select c#,count(S#) from sc group by C#;
    27、查询出只选修了一门课程的全部学生的学号和姓名
      select SC.S#,Student.Sname,count(C#) AS 选课数
      from SC ,Student
      where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
    28、查询男生、女生人数
        Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex='男';
        Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex='女';
    29、查询姓“张”的学生名单
        SELECT Sname FROM Student WHERE Sname like '张%';
    30、查询同名同性学生名单,并统计同名人数
      select Sname,count(*) from Student group by Sname having  count(*)>1;;
    31、1981年出生的学生名单(注:Student表中Sage列的类型是datetime)
        select Sname,  CONVERT(char (11),DATEPART(year,Sage)) as age
        from student
        where  CONVERT(char(11),DATEPART(year,Sage))='1981';
    32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
        Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;
    33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
        select Sname,SC.S# ,avg(score)
        from Student,SC
        where Student.S#=SC.S# group by SC.S#,Sname having    avg(score)>85;
    34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数
        Select Sname,isnull(score,0)
        from Student,SC,Course
        where SC.S#=Student.S# and SC.C#=Course.C# and  Course.Cname='数据库'and score <60;
    35、查询所有学生的选课情况;
        SELECT SC.S#,SC.C#,Sname,Cname
        FROM SC,Student,Course
        where SC.S#=Student.S# and SC.C#=Course.C# ;
    36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
        SELECT  distinct student.S#,student.Sname,SC.C#,SC.score
        FROM student,Sc
        WHERE SC.score>=70 AND SC.S#=student.S#;
    37、查询不及格的课程,并按课程号从大到小排列
        select c# from sc where scor e <60 order by C# ;
    38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
        select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003';
    39、求选了课程的学生人数
        select count(*) from sc;
    40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩
        select Student.Sname,score
        from Student,SC,Course C,Teacher
        where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where C#=C.C# );
    41、查询各个课程及相应的选修人数
        select count(*) from sc group by C#;
    42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
      select distinct  A.S#,B.score from SC A  ,SC B where A.Score=B.Score and A.C# <>B.C# ;
    43、查询每门功成绩最好的前两名
        SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
          FROM SC t1
          WHERE score IN (SELECT TOP 2 score
                  FROM SC
                  WHERE t1.C#= C#
                ORDER BY score DESC
                  )
          ORDER BY t1.C#;
    44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列  
        select  C# as 课程号,count(*) as 人数
        from  sc  
        group  by  C#
        order  by  count(*) desc,c#  
    45、检索至少选修两门课程的学生学号
        select  S#  
        from  sc  
        group  by  s#
        having  count(*)  >  =  2
    46、查询全部学生都选修的课程的课程号和课程名
        select  C#,Cname  
        from  Course  
        where  C#  in  (select  c#  from  sc group  by  c#)  
    47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
        select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平');
    48、查询两门以上不及格课程的同学的学号及其平均成绩
        select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;
    49、检索“004”课程分数小于60,按分数降序排列的同学学号
        select S# from SC where C#='004'and score <60 order by score desc;
    50、删除“002”同学的“001”课程的成绩
    delete from Sc where S#='001'and C#='001';

    1、自行创建测试数据

     

    1.

    问题描述:
    本题用到下面三个关系表:
    CARD     借书卡。   CNO 卡号,NAME  姓名,CLASS 班级
    BOOKS    图书。     BNO 书号,BNAME 书名,AUTHOR 作者,PRICE 单价,QUANTITY 库存册数
    BORROW   借书记录。 CNO 借书卡号,BNO 书号,RDATE 还书日期
    备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。
    要求实现如下15个处理:
      1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束。
      2. 找出借书超过5本的读者,输出借书卡号及所借图书册数。
      3. 查询借阅了"水浒"一书的读者,输出姓名及班级。
      4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期。
      5. 查询书名包括"网络"关键词的图书,输出书号、书名、作者。
      6. 查询现有图书中价格最高的图书,输出书名及作者。
      7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者,输出其借书卡号,并按卡号降序排序输出。
      8. 将"C01"班同学所借图书的还期都延长一周。
      9. 从BOOKS表中删除当前无人借阅的图书记录。
      10.如果经常按书名查询图书信息,请建立合适的索引。
      11.在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是"数据库技术及应用",就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。
      12.建立一个视图,显示"力01"班学生的借书信息(只要求显示姓名和书名)。
      13.查询当前同时借有"计算方法"和"组合数学"两本书的读者,输出其借书卡号,并按卡号升序排序输出。
      14.假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句。
      15.对CARD表做如下修改:
        a. 将NAME最大列宽增加到10个字符(假定原为6个字符)。
        b. 为该表增加1列NAME(系名),可变长,最大20个字符。

    2、查询“生物”课程比“物理”课程成绩高的所有学生的学号;

     
    

    导出现有数据库数据:

    1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束
      --实现代码:
      CREATE TABLE BORROW(
          CNO int FOREIGN KEY REFERENCES CARD(CNO),
          BNO int FOREIGN KEY REFERENCES BOOKS(BNO),
          RDATE datetime,
          PRIMARY KEY(CNO,BNO))

    2. 找出借书超过5本的读者,输出借书卡号及所借图书册数
      --实现代码:
      SELECT CNO,借图书册数=COUNT(*)
      FROM BORROW
      GROUP BY CNO
      HAVING COUNT(*)>5

    3. 查询借阅了"水浒"一书的读者,输出姓名及班级
      --实现代码:
      SELECT * FROM CARD c
      WHERE EXISTS(
          SELECT * FROM BORROW a,BOOKS b
          WHERE a.BNO=b.BNO
              AND b.BNAME=N'水浒'
              AND a.CNO=c.CNO)

    4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期
      --实现代码:
      SELECT * FROM BORROW
      WHERE RDATE<GETDATE()

    5. 查询书名包括"网络"关键词的图书,输出书号、书名、作者
      --实现代码:
      SELECT BNO,BNAME,AUTHOR FROM BOOKS
      WHERE BNAME LIKE N'%网络%'

    6. 查询现有图书中价格最高的图书,输出书名及作者
      --实现代码:
      SELECT BNO,BNAME,AUTHOR FROM BOOKS
      WHERE PRICE=(
          SELECT MAX(PRICE) FROM BOOKS)

      select A.student_id from (select score.sid,score.student_id,course.cname,score.num from score LEFT JOIN course on score.course_id=course.cid where course.cname="生物") as A INNER JOIN (select score.sid,score.student_id,course.cname,score.num from score LEFT JOIN course on score.course_id=course.cid where course.cname="物理") as B on A.student_id = B.student_id where A.num > B.num

    (二)

    • mysqldump -u用户名 -p密码 数据库名称 >导出文件路径           # 结构 数据
    • mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径       # 结构 

    7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者,输出其借书卡号,并按卡号降序排序输出
    --实现代码:
    SELECT a.CNO
    FROM BORROW a,BOOKS b
    WHERE a.BNO=b.BNO AND b.BNAME=N'计算方法'
        AND NOT EXISTS(
            SELECT * FROM BORROW aa,BOOKS bb
            WHERE aa.BNO=bb.BNO
                AND bb.BNAME=N'计算方法习题集'
                AND aa.CNO=a.CNO)
    ORDER BY a.CNO DESC

    3、查询平均成绩大于60分的同学的学号和平均成绩; 

     
    

    导入现有数据库数据:

    1. 将"C01"班同学所借图书的还期都延长一周
      --实现代码:
      UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)
      FROM CARD a,BORROW b
      WHERE a.CNO=b.CNO
          AND a.CLASS=N'C01'

    2. 从BOOKS表中删除当前无人借阅的图书记录
      --实现代码:
      DELETE A FROM BOOKS a
      WHERE NOT EXISTS(
          SELECT * FROM BORROW
          WHERE BNO=a.BNO)

    3. 如果经常按书名查询图书信息,请建立合适的索引
      --实现代码:
      CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)

      select student_id,avg(num) from score GROUP BY student_id HAVING avg(num) > 60;

    Student(S#,Sname,Sage,Ssex) 学生表
    Course(C#,Cname,T#) 课程表
    SC(S#,C#,score) 成绩表
    Teacher(T#,Tname) 教师表

    • mysqldump -uroot -p密码  数据库名称 < 文件路径  

    11. 在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是"数据库技术及应用",就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)
    --实现代码:
    CREATE TRIGGER TR_SAVE ON BORROW
    FOR INSERT,UPDATE
    AS
    IF @@ROWCOUNT>0
    INSERT BORROW_SAVE SELECT i.*
    FROM INSERTED i,BOOKS b
    WHERE i.BNO=b.BNO
        AND b.BNAME=N'数据库技术及应用'

      --如何要在显示学生姓名,就要讲刚刚的结果建成临时表,在连表学生表,left join student,在临时表中有聚合函数avg(),所以给avg起别名才能用,起aaa。

     
    

    2.略过

    1. 建立一个视图,显示"力01"班学生的借书信息(只要求显示姓名和书名)
      --实现代码:
      CREATE VIEW V_VIEW
      AS
      SELECT a.NAME,b.BNAME
      FROM BORROW ab,CARD a,BOOKS b
      WHERE ab.CNO=a.CNO
          AND ab.BNO=b.BNO
          AND a.CLASS=N'力01'

      SELECT B.studetn_id,student.name,B.aaa from (select student_id,avg(num) as aaa from score GROUP BY student_id HAVING avg(num) > 60)as B  left join student on B.student_id=student.sid;

    问题:
    1、查询“001”课程比“002”课程成绩高的所有学生的学号;
      select a.S# from (select s#,score from SC where C#='001') a,(selects#,score
      from SC where C#='002') b
      where a.score>b.score and a.s#=b.s#;
    2、查询平均成绩大于60分的同学的学号和平均成绩;
        select S#,avg(score)
        from sc
        group by S# having avg(score) >60;
    3、查询所有同学的学号、姓名、选课数、总成绩;
      select Student.S#,Student.Sname,count(SC.C#),sum(score)
      from Student left Outer join SC on Student.S#=SC.S#
      group by Student.S#,Sname
    4、查询姓“李”的老师的个数;
      select count(distinct(Tname))
      from Teacher
      where Tname like '李%';
    5、查询没学过“叶平”老师课的同学的学号、姓名;
        select Student.S#,Student.Sname
        from Student 
        where S# not in (select distinct( SC.S#) fromSC,Course,Teacher where  SC.C#=Course.C# and Teacher.T#=Course.T# andTeacher.Tname='叶平');
    6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
      select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S#and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# andSC_2.C#='002');
    7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
      select S#,Sname
      from Student
      where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C#and Teacher.T#=Course.T# and Teacher.Tname='叶平' groupby S# having count(SC.C#)=(select count(C#) from Course,Teacher  whereTeacher.T#=Course.T# and Tname='叶平'));
    8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
      Select S#,Sname from (select Student.S#,Student.Sname,score ,(selectscore from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2
      from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2<score;
    9、查询所有课程成绩小于60分的同学的学号、姓名;
      select S#,Sname
      from Student
      where S# not in (select Student.S# from Student,SC where S.S#=SC.S# andscore>60);
    10、查询没有学全所有课的同学的学号、姓名;
        select Student.S#,Student.Sname
        from Student,SC
        where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);
    11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
        select S#,Sname from Student,SC where Student.S#=SC.S# andC# in select C# from SC where S#='1001';
    12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;
        select distinct SC.S#,Sname
        from Student,SC
        where Student.S#=SC.S# and C# in (select C# from SC whereS#='001');
    13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
        update SC set score=(select avg(SC_2.score)
        from SC SC_2
        where SC_2.C#=SC.C# ) from Course,Teacher whereCourse.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');
    14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;
        select S# from SC where C# in (select C# from SC whereS#='1002')
        group by S# having count(*)=(select count(*) from SC whereS#='1002');
    15、删除学习“叶平”老师课的SC表记录;
        Delect SC
        from course ,Teacher 
        where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';
    16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、2、
        号课的平均成绩;
        Insert SC select S#,'002',(Select avg(score)
        from SC where C#='002') from Student where S# not in (SelectS# from SC where C#='002');
    17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
        SELECT S# as 学生ID
            ,(SELECT score FROM SC WHERESC.S#=t.S# AND C#='004') AS 数据库
            ,(SELECT score FROM SC WHERESC.S#=t.S# AND C#='001') AS 企业管理
            ,(SELECT score FROM SC WHERE SC.S#=t.S#AND C#='006') AS 英语
            ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
        FROM SC AS t
        GROUP BY S#
        ORDER BY avg(t.score) 
    18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
        SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分
        FROM SC L ,SC AS R
        WHERE L.C# = R.C# and
            L.score = (SELECT MAX(IL.score)
                         FROM SC AS IL,Student AS IM
                         WHERE L.C# = IL.C# and IM.S#=IL.S#
                         GROUP BY IL.C#)
            AND
            R.Score = (SELECT MIN(IR.score)
                         FROM SC AS IR
                         WHERE R.C# = IR.C#
                     GROUP BY IR.C#
                       );
    19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
        SELECT t.C# AS 课程号,max(course.Cname)AS课程名,isnull(AVG(score),0) AS 平均成绩
            ,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
        FROM SC T,Course
        where t.C#=course.C#
        GROUP BY t.C#
        ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60THEN 1 ELSE 0 END)/COUNT(*) DESC
    20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004)
        SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分
            ,100 * SUM(CASE WHEN C# = '001' ANDscore >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END)AS 企业管理及格百分数
            ,SUM(CASE WHEN C# = '002' THEN scoreELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分
            ,100 * SUM(CASE WHEN C# = '002' ANDscore >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END)AS 马克思及格百分数
            ,SUM(CASE WHEN C# = '003' THEN scoreELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
            ,100 * SUM(CASE WHEN C# = '003' ANDscore >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END)AS UML及格百分数
            ,SUM(CASE WHEN C# = '004' THEN scoreELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分
            ,100 * SUM(CASE WHEN C# = '004' ANDscore >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END)AS 数据库及格百分数
      FROM SC

    3.查询平均成绩大于60分的同学的学号和平均成绩;

    13. 查询当前同时借有"计算方法"和"组合数学"两本书的读者,输出其借书卡号,并按卡号升序排序输出
    --实现代码:
    SELECT a.CNO
    FROM BORROW a,BOOKS b
    WHERE a.BNO=b.BNO
        AND b.BNAME IN(N'计算方法',N'组合数学')
    GROUP BY a.CNO
    HAVING COUNT(*)=2
    ORDER BY a.CNO DESC

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

     
    

     ——avg()获取平均值

    1. 假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句
      --实现代码:
      ALTER TABLE BOOKS ADD PRIMARY KEY(BNO)

      --先使score成绩表和学生表student连起来,

    21、查询不同老师所教不同课程平均分从高到低显示
      SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩
        FROM SC AS T,Course AS C ,Teacher AS Z
        where T.C#=C.C# and C.T#=Z.T#
      GROUP BY C.C#
      ORDER BY AVG(Score) DESC
    22、查询如下课程成绩第 3 名到第 6 名的学生成绩单:企业管理(001),马克思(002),UML (003),数据库(004)
        [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
        SELECT  DISTINCT top 3
          SC.S# As 学生学号,
            Student.Sname AS 学生姓名 ,
          T1.score AS 企业管理,
          T2.score AS 马克思,
          T3.score AS UML,
          T4.score AS 数据库,
          ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0) as 总分
          FROM Student,SC  LEFT JOIN SC AS T1
                         ON SC.S# = T1.S# AND T1.C# = '001'
                LEFT JOIN SCAS T2
                         ON SC.S# = T2.S# AND T2.C# = '002'
                LEFT JOIN SCAS T3
                         ON SC.S# = T3.S# AND T3.C# = '003'
                LEFT JOIN SCAS T4
                         ON SC.S# = T4.S# AND T4.C# = '004'
          WHERE student.S#=SC.S# and
          ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0)
          NOT IN
          (SELECT
                DISTINCT
                TOP 15 WITHTIES
               ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0)
          FROM sc
                LEFT JOIN scAS T1
                         ON sc.S# = T1.S# AND T1.C# = 'k1'
                LEFT JOIN scAS T2
                         ON sc.S# = T2.S# AND T2.C# = 'k2'
                LEFT JOIN scAS T3
                         ON sc.S# = T3.S# AND T3.C# = 'k3'
                LEFT JOIN scAS T4
                         ON sc.S# = T4.S# AND T4.C# = 'k4'
          ORDER BY ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0) DESC);

    ——left join...on...联表操作

    15.1 将NAME最大列宽增加到10个字符(假定原为6个字符)
    --实现代码:
    ALTER TABLE CARD ALTER COLUMN NAME varchar(10)

      select * from score left join student on score.student_id=student.sid

     
    

    ——as …创建临时表

    15.2 为该表增加1列NAME(系名),可变长,最大20个字符
    --实现代码:
    ALTER TABLE CARD ADD 系名 varchar(20)

      --拿到学生id和姓名

    23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
        SELECT SC.C# as 课程ID, Cname as 课程名称
            ,SUM(CASE WHEN score BETWEEN 85 AND100 THEN 1 ELSE 0 END) AS [100 - 85]
            ,SUM(CASE WHEN score BETWEEN 70 AND85 THEN 1 ELSE 0 END) AS [85

    select student_id,avg(num),sname
    from score 
    left join student on score.student_id=student.sid
    GROUP BY student_id having avg(num)>60
    

    问题描述:
    为管理岗位业务培训信息,建立3个表:
    S (S#,SN,SD,SA)   S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
    C (C#,CN )        C#,CN       分别代表课程编号、课程名称
    SC ( S#,C#,G )    S#,C#,G     分别代表学号、所选修的课程编号、学习成绩

      select score.student_id,studnet.sname from score LEFT JOIN student on score.student_id=student.sid

    • 70]
              ,SUM(CASE WHEN score BETWEEN 60 AND70 THEN 1 ELSE 0 END) AS [70
    • 60]
              ,SUM(CASE WHEN score < 60 THEN 1ELSE 0 END) AS [60 -]
          FROM SC,Course
          where SC.C#=Course.C#
          GROUP BY SC.C#,Cname;  

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

    要求实现如下5个处理:
      1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
      2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
      3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
      4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
      5. 查询选修了课程的学员人数
      6. 查询选修课程超过5门的学员学号和所属单位

      --分组,

    24、查询学生平均成绩及其名次
          SELECT 1 (SELECT COUNT( distinct 平均成绩)
                 FROM (SELECT S#,AVG(score) AS 平均成绩
                         FROM SC
                     GROUP BY S#
                     ) AS T1
                WHERE 平均成绩 > T2.平均成绩) as 名次,
          S# as 学生学号,平均成绩
        FROM (SELECT S#,AVG(score) 平均成绩
                FROM SC
            GROUP BY S#
            ) AS T2
        ORDER BY 平均成绩 desc;
    25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
          SELECT t1.S# as 学生ID,t1.C#as 课程ID,Score as 分数
          FROM SC t1
          WHERE score IN (SELECT TOP 3 score
                 FROM SC
                 WHERE t1.C#= C#
                ORDER BYscore DESC
                 )
          ORDER BY t1.C#;
    26、查询每门课程被选修的学生数
      select c#,count(S#) from sc group by C#;
    27、查询出只选修了一门课程的全部学生的学号和姓名
      select SC.S#,Student.Sname,count(C#) AS 选课数
      from SC ,Student
      where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
    28、查询男生、女生人数
        Select count(Ssex) as 男生人数 fromStudent group by Ssex having Ssex='男';
        Select count(Ssex) as 女生人数 fromStudent group by Ssex having Ssex='女';
    29、查询姓“张”的学生名单
        SELECT Sname FROM Student WHERE Sname like '张%';
    30、查询同名同性学生名单,并统计同名人数
      select Sname,count(*) from Student group by Sname having count(*)>1;;
    31、1981年出生的学生名单(注:Student表中Sage列的类型是datetime)
        select Sname,  CONVERT(char (11),DATEPART(year,Sage))as age
        from student
        where  CONVERT(char(11),DATEPART(year,Sage))='1981';
    32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
        Select C#,Avg(score) from SC group by C# order byAvg(score),C# DESC ;
    33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
        select Sname,SC.S# ,avg(score)
        from Student,SC
        where Student.S#=SC.S# group by SC.S#,Snamehaving    avg(score)>85;
    34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数
        Select Sname,isnull(score,0)
        from Student,SC,Course
        where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname='数据库'and score <60;
    35、查询所有学生的选课情况;
        SELECT SC.S#,SC.C#,Sname,Cname
        FROM SC,Student,Course
        where SC.S#=Student.S# and SC.C#=Course.C# ;
    36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
        SELECT  distinctstudent.S#,student.Sname,SC.C#,SC.score
        FROM student,Sc
        WHERE SC.score>=70 AND SC.S#=student.S#;
    37、查询不及格的课程,并按课程号从大到小排列
        select c# from sc where scor e <60 order by C# ;
    38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
        select SC.S#,Student.Sname from SC,Student whereSC.S#=Student.S# and Score>80 and C#='003';
    39、求选了课程的学生人数
        select count(*) from sc;
    40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩
        select Student.Sname,score
        from Student,SC,Course C,Teacher
        where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T#and Teacher.Tname='叶平' and SC.score=(selectmax(score)from SC where C#=C.C# );
    41、查询各个课程及相应的选修人数
        select count(*) from sc group by C#;
    42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
      select distinct  A.S#,B.score from SC A  ,SC B whereA.Score=B.Score and A.C# <>B.C# ;
    43、查询每门功成绩最好的前两名
        SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
          FROM SC t1
          WHERE score IN (SELECT TOP 2 score
                 FROM SC
                 WHERE t1.C#= C#
                ORDER BYscore DESC
                 )
          ORDER BY t1.C#;
    44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列 
        select  C# as 课程号,count(*) as 人数
        from  sc 
        group  by  C#
        order  by  count(*) desc,c# 
    45、检索至少选修两门课程的学生学号
        select  S# 
        from  sc 
        group  by  s#
        having  count(*)  >  =  2
    46、查询全部学生都选修的课程的课程号和课程名
        select  C#,Cname 
        from  Course 
        where  C#  in  (select  c# from  sc group  by  c#) 
    47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
        select Sname from Student where S# not in (select S# fromCourse,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平');
    48、查询两门以上不及格课程的同学的学号及其平均成绩
        select S#,avg(isnull(score,0)) from SC where S# in (selectS# from SC where score <60 group by S# having count(*)>2)group by S#;
    49、检索“004”课程分数小于60,按分数降序排列的同学学号
        select S# from SC where C#='004'and score <60 order byscore desc;
    50、删除“002”同学的“001”课程的成绩
    delete from Sc where S#='001'and C#='001';

    5.查询姓“李”的老师的个数;

    1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
      --实现代码:
      SELECT SN,SD FROM S
      WHERE [S#] IN(
          SELECT [S#] FROM C,SC
          WHERE C.[C#]=SC.[C#]
              AND CN=N'税收基础')

      select * from score left join student on score.student_id=student.sid GROUP BY score.student_id

     
    

    select count(tid) from teacher where tname like '李%'

    1. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
      --实现代码:
      SELECT S.SN,S.SD FROM S,SC
      WHERE S.[S#]=SC.[S#]
          AND SC.[C#]='C2'

    2. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
      --实现代码:
      SELECT SN,SD FROM S
      WHERE [S#] NOT IN(
          SELECT [S#] FROM SC
          WHERE [C#]='C5')

    3. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
      --实现代码:
      SELECT SN,SD FROM S
      WHERE [S#] IN(
          SELECT [S#] FROM SC
              RIGHT JOIN C ON SC.[C#]=C.[C#]
          GROUP BY [S#]
          HAVING COUNT(*)=COUNT(DISTINCT [S#]))

    4. 查询选修了课程的学员人数
      --实现代码:
      SELECT 学员人数=COUNT(DISTINCT [S#]) FROM SC

    5. 查询选修课程超过5门的学员学号和所属单位
      --实现代码:
      SELECT SN,SD FROM S
      WHERE [S#] IN(
          SELECT [S#] FROM SC
          GROUP BY [S#]
          HAVING COUNT(DISTINCT [C#])>5)

      --最终

    (三)

    6、查询没学过“叶平”老师课的同学的学号、姓名;

    if not object_id('cj')is null
       drop table cj
    go
    create table cj(stuName nvarchar(10),KCM nvarchar(10),cj numeric(5,2))
    insert into cj select '张三','语文',98
    union select '李四','语文',89
    union select '王五','语文',67
    union select '周攻','语文',56
    union select '张三','数学',89
    union select '李四','数学',78
    union select '王五','数学',90
    union select '周攻','数学',87
    方法一:
    select stuname from
        (select stuName,kcm,(select count(*) from cj where stuname!=a.stuname and kcm=a.kcm and cj>a.cj) cnt from cj a) x
        group by stuname having max(cnt)<=1
    go
    方法二:
    SELECT stuname FROM cj1 a  
    where cj IN(SELECT TOP 2 cj FROM cj1 WHERE kcm=a.kcm ORDER BY cj desc)
    GROUP BY stuname HAVING(count(1)>1)
    方法三:
    select distinct stuname from cj a
        where not exists(select kcm from cj b where a.stuname=stuname
                    and (select count(*) from cj where kcm=b.kcm and stuname!=a.stuname and cj>b.cj)>1)

      select score.student_id,studnet.sname,count(student_id),sum(num) from score LEFT JOIN student on score.student_id=student.sid GROUP BY score.student_id;  

     
    

    7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;

    SQLSEREVER测试题(上)

    5、查询姓“李”的老师的个数;

    问题描述:
    本题用到下面三个关系表:
    CARD     借书卡。   CNO 卡号,NAME  姓名,CLASS 班级
    BOOKS    图书。     BNO书号,BNAME 书名,AUTHOR 作者,PRICE 单价,QUANTITY 库存册数
    BORROW   借书记录。 CNO 借书卡号,BNO 书号,RDATE 还书日期
    备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。
    要求实现如下15个处理:
      1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束。
      2. 找出借书超过5本的读者,输出借书卡号及所借图书册数。
      3. 查询借阅了"水浒"一书的读者,输出姓名及班级。
      4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期。
      5. 查询书名包括"网络"关键词的图书,输出书号、书名、作者。
      6. 查询现有图书中价格最高的图书,输出书名及作者。
      7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者,输出其借书卡号,并按卡号降序排序输出。
      8. 将"C01"班同学所借图书的还期都延长一周。
      9. 从BOOKS表中删除当前无人借阅的图书记录。
      10.如果经常按书名查询图书信息,请建立合适的索引。
      11.在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是"数据库技术及应用",就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。
      12.建立一个视图,显示"力01"班学生的借书信息(只要求显示姓名和书名)。
      13.查询当前同时借有"计算方法"和"组合数学"两本书的读者,输出其借书卡号,并按卡号升序排序输出。
      14.假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句。
      15.对CARD表做如下修改:
        a. 将NAME最大列宽增加到10个字符(假定原为6个字符)。
        b. 为该表增加1列NAME(系名),可变长,最大20个字符。

    8、查询学过“叶平”老师所教的所有课的同学的学号、姓名;

    一 SQLSERVER管理部分

    6、查询没学过“叶平”老师课的同学的学号、姓名;

     
    

    联表操作、获取老师所交课程的id、再获取学过该课程的学生id

    1 请讲出身份验证模式与登录帐号的关系及如何用各种帐号进行登录,并画出示意图
    2 请讲出登录帐号、数据库用户及数据库角色之间的关系,并画出示意图
    3 请讲出数据库用户、数据库角色与数据库对象之间的关系,并画出直接对用户授权与间接对用户授权(系统权限与对象权限)的方法
    4 请讲出服务器角色、数据库角色、标准角色与应用程序角色的区别与验证其权限的方法
    5 请讲出数据库还原模型对数据库的影响
    6 有一个执行关键任务的数据库,请设计一个数据库备份策略
    7 请使用文件与文件组恢复的方式恢复数据库
    8 请使用事务日志恢复数据库到一个时间点
    9 请设计作业进行周期性的备份数据库
    10 如何监控数据库的阻塞,并实现数据库的死锁测试
    11 如何监控数据库的活动,并能使用索引优化向导生成索引
    12 理解数据库框图的作用并可以设计表与表之间的关系

      --先查老师表

    1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束
      --实现代码:
      CREATE TABLE BORROW(
          CNO int FOREIGN KEY REFERENCES CARD(CNO),
          BNO int FOREIGN KEY REFERENCES BOOKS(BNO),
          RDATE datetime,
          PRIMARY KEY(CNO,BNO))

       

    2. 找出借书超过5本的读者,输出借书卡号及所借图书册数
      --实现代码:
      SELECT CNO,借图书册数=COUNT(*)
      FROM BORROW
      GROUP BY CNO
      HAVING COUNT(*)>5

       

    3. 查询借阅了"水浒"一书的读者,输出姓名及班级
      --实现代码:
      SELECT * FROM CARD c
      WHERE EXISTS(
          SELECT * FROM BORROW a,BOOKS b
          WHERE a.BNO=b.BNO
              AND b.BNAME=N'水浒'
              AND a.CNO=c.CNO)

       

    4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期
      --实现代码:
      SELECT * FROM BORROW
      WHERE RDATE<GETDATE()

       

    5. 查询书名包括"网络"关键词的图书,输出书号、书名、作者
      --实现代码:
      SELECT BNO,BNAME,AUTHOR FROM BOOKS
      WHERE BNAME LIKE N'%网络%'

       

    6. 查询现有图书中价格最高的图书,输出书名及作者
      --实现代码:
      SELECT BNO,BNAME,AUTHOR FROM BOOKS
      WHERE PRICE=(
          SELECT MAX(PRICE) FROM BOOKS)

       

    -- select cid from course left join teacher on course.teacher_id=teacher.tid where tname='李平老师'  获得老师所交的课程的cid
    SELECT
        student_id,
        student.sname 
    FROM
        score
        LEFT JOIN student ON score.student_id = student.sid 
    WHERE
        course_id IN ( SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE tname = '李平老师' ) 
    GROUP BY
        student_id
    

    SQLSEREVER测试题(中)

      select * from teacher;

    7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者,输出其借书卡号,并按卡号降序排序输出
    --实现代码:
    SELECT a.CNO
    FROM BORROW a,BOOKS b
    WHERE a.BNO=b.BNO AND b.BNAME=N'计算方法'
        AND NOT EXISTS(
            SELECT * FROM BORROW aa,BOOKS bb
            WHERE aa.BNO=bb.BNO
                ANDbb.BNAME=N'计算方法习题集'
                ANDaa.CNO=a.CNO)
    ORDER BY a.CNO DESC

    9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;

    二 SQLSERVER的实现部分

      --连表,老师表和课程表,查叶平的任教课程id

     
    

    联表操作、条件、分组或去重

    1 有订单表,需要实现它的编号,格式如下:200211030001……200222039999等

      select  course.cid  from course  left  join  teacher  on  course.teacher_id=teacher_tid  where  teacher.name = "叶平老师";

    1. 将"C01"班同学所借图书的还期都延长一周
      --实现代码:
      UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)
      FROM CARD a,BORROW b
      WHERE a.CNO=b.CNO
          AND a.CLASS=N'C01'

       

    2. 从BOOKS表中删除当前无人借阅的图书记录
      --实现代码:
      DELETE A FROM BOOKS a
      WHERE NOT EXISTS(
          SELECT * FROM BORROW
          WHERE BNO=a.BNO)

       

    3. 如果经常按书名查询图书信息,请建立合适的索引
      --实现代码:
      CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)

       

    SELECT
        student_id,student.sname 
    FROM
        score 
        left join student on score.student_id=student.sid
    where 
        num < 60 
    GROUP BY
        student_id
    

    2 有表T1,T2,现有一事务,在向表T1添加数据时,同时也必须向T2也添加数据,如何实现该事务

      --去成绩表拿没有叶平课的结果

    11. 在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是"数据库技术及应用",就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)
    --实现代码:
    CREATE TRIGGER TR_SAVE ON BORROW
    FOR INSERT,UPDATE
    AS
    IF @@ROWCOUNT>0
    INSERT BORROW_SAVE SELECT i.*
    FROM INSERTED i,BOOKS b
    WHERE i.BNO=b.BNO
        AND b.BNAME=N'数据库技术及应用'

     

    3 如何向T1中的编号字段(code varchar(20))添加一万条记录,不充许重复,规则如下:编号的数据必须从小写的a-z之间取值

      select * from score where course_id not in (2,4);         in里可以加sql语句。

     
    

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

    4 如何删除表中的重复数据,请使用游标与分组的办法

      --最终

    1. 建立一个视图,显示"力01"班学生的借书信息(只要求显示姓名和书名)
      --实现代码:
      CREATE VIEW V_VIEW
      AS
      SELECT a.NAME,b.BNAME
      FROM BORROW ab,CARD a,BOOKS b
      WHERE ab.CNO=a.CNO
          AND ab.BNO=b.BNO
          AND a.CLASS=N'力01' 

     

    5 如何求表中相邻的两条记录的某字段的值之差

      select student.sid, student.sname  from student where sid not in (

    13. 查询当前同时借有"计算方法"和"组合数学"两本书的读者,输出其借书卡号,并按卡号升序排序输出
    --实现代码:
    SELECT a.CNO
    FROM BORROW a,BOOKS b
    WHERE a.BNO=b.BNO
        AND b.BNAME IN(N'计算方法',N'组合数学')
    GROUP BY a.CNO
    HAVING COUNT(*)=2
    ORDER BY a.CNO DESC

    11、查询没有学全所有课的同学的学号、姓名;

    6 如何统计数据库中所有用户表的数据,显示格式如下:

        select student_id from score where course_id in (select course.cid from course

     
    

    count<count(总课程数)

    表名      记录数

          left join teacher on course.teacher_id = teacher.tid where

    1. 假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句
      --实现代码:
      ALTER TABLE BOOKS ADD PRIMARY KEY(BNO) 
    -- select COUNT(cid) from course 获取总课程数
    select count(student_id) from score GROUP BY student_id having count(student_id)<(select COUNT(cid) from course)
    

      sales      23

            teacher.tname = "叶平老师")group by student_id); 

    15.1 将NAME最大列宽增加到10个字符(假定原为6个字符)
    --实现代码:
    ALTER TABLE CARD ALTER COLUMN NAME varchar(10)

     

    7 如何删除数据库中的所有用户表(表与表之间有外键关系)

    7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;

     
    

    12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名;

    8 表A editor_id       lb2_id
       123           000
       123           003
       123           003
       456           007
       456           006
    表B  lb2_id         lb2_name
        000           a
        003           b
        006           c
        007           d
    显示 a   共1条 (表A内lb2_id为000的条数)
       b   共2条(表A内lb2_id为003的条数)

      --先取得成绩表

    15.2 为该表增加1列NAME(系名),可变长,最大20个字符
    --实现代码:
    ALTER TABLE CARD ADD 系名 varchar(20)

    id!=1

    9 人员情况表(employee):里面有一字段文化程度(wh):包括四种情况(本科以上,大专,高中,初中以下),现在我要根据年龄字段查询统计出:表中文化程度为本科以上,大专,高中,初中以下,各有多少人,占总人数多少。

      select * from score 

     
    

    -- select course_id from score where student_id=1 001同学所选的课程
    SELECT distinct student_id from score where student_id!=1 and course_id in (select course_id from score where student_id=1)

    SELECT wh AS 学历,age as 年龄, Count(*) AS 人数,

      --过滤001和002 并分组

     

     

          Count(*) * 100 /(SELECT Count(*) FROM employee) AS 百分比

      select score.student_id, studnet.sname from score left join student on score.student_id=student.sid where course_id = 1 or course_id = 2 GROUP BY student_id HAVING count(course_id) > 1

     
    

    13、查询至少学过学号为“001”同学所有课程的其他同学学号和姓名;

    FROM employee GROUP BY wh,age

    8、查询学过“叶平”老师所教的所有课的同学的学号、姓名;

    (四)

    id!=1、所学课程数个数》=001所学课程个数

    学历      年龄    人数      百分比

      select student_id from score where course_id in (
      select cid from course left JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师"
    ) GROUP BY student_id having count(course_id) = (select count(cid) from course left JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师")

     
    
    -- select course_id from score where student_id=1 001同学所选的课程
    SELECT 
        student_id 
    FROM
        score 
    WHERE
     course_id IN ( SELECT course_id FROM score WHERE student_id = 1 ) GROUP BY student_id
     having  COUNT(course_id)=( SELECT count(course_id) FROM score WHERE student_id = 1 )
    

    本科以上  20      34          14

    9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;

    问题描述:
    为管理岗位业务培训信息,建立3个表:
    S (S#,SN,SD,SA)   S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
    C (C#,CN )       C#,CN       分别代表课程编号、课程名称
    SC ( S#,C#,G )    S#,C#,G     分别代表学号、所选修的课程编号、学习成绩

     

    大专      20      33          13

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

     
    

    14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名;

    高中      20      33          13

      select student_id from score where num < 60 GROUP BY student_id
      select DISTINCT student_id from score where num < 60

    要求实现如下5个处理:
      1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
      2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
      3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
      4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
      5. 查询选修了课程的学员人数
      6. 查询选修课程超过5门的学员学号和所属单位

     个数相同、002学过的也学过

    初中以下  20      100        40

    11、查询没有学全所有课的同学的学号、姓名;

     
    

    !!002学过的也学过的   怎么找

    本科以上  21      50          20

      select student_id,count(1) from score GROUP BY student_id  HAVING count(1) < (select count(cid) from course);

    1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
      --实现代码:
      SELECT SN,SD FROM S
      WHERE [S#] IN(
          SELECT [S#] FROM C,SC
          WHERE C.[C#]=SC.[C#]
              AND CN=N'税收基础')

       

    2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
      --实现代码:
      SELECT S.SN,S.SD FROM S,SC
      WHERE S.[S#]=SC.[S#]
          AND SC.[C#]='C2'

       

    3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
      --实现代码:
      SELECT SN,SD FROM S
      WHERE [S#] NOT IN(
          SELECT [S#] FROM SC
          WHERE [C#]='C5')

       

    4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
      --实现代码:
      SELECT SN,SD FROM S
      WHERE [S#] IN(
          SELECT [S#] FROM SC
              RIGHT JOIN C ON SC.[C#]=C.[C#]
          GROUP BY [S#]
          HAVING COUNT(*)=COUNT(DISTINCT [S#]))

       

    5. 查询选修了课程的学员人数
      --实现代码:
      SELECT 学员人数=COUNT(DISTINCT [S#]) FROM SC

       

    6. 查询选修课程超过5门的学员学号和所属单位
      --实现代码:
      SELECT SN,SD FROM S
      WHERE [S#] IN(
          SELECT [S#] FROM SC
          GROUP BY [S#]
          HAVING COUNT(DISTINCT [C#])>5)

       

     

    10 现在有三个表student:(FID  学生号,FName  姓名),

    12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名;

    (五)

    15、删除学习“叶平”老师课的score表记录;

    subject:(FSubID  课程号,FSubName 课程名),

      select course_id from score where student_id = 1;
      select student_id from score where student_id != 1 and course_id in (select course_id from score where student_id = 1) GROUP BY student_id

     
    

    找到课程id

    Score(FScoreId  成绩记录号,FSubID    课程号,FStdID    学生号,FScore    成绩)

    13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名;

    一 SQLSERVER管理部分

     

    怎么能实现这个表:

      select course_id from score where student_id = 1;
      select student_id,count(1) from score where student_id != 1 and course_id in (select course_id from score where student_id = 1) GROUP BY student_id HAVING count(1) = (select count(course_id) from score where student_id = 1)

     
    

    16、向score表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“002”课程的同学学号;②插入“002”号课程的平均成绩; 

    姓名  英语  数学  语文  历史

    14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名;

    1 请讲出身份验证模式与登录帐号的关系及如何用各种帐号进行登录,并画出示意图
    2 请讲出登录帐号、数据库用户及数据库角色之间的关系,并画出示意图
    3 请讲出数据库用户、数据库角色与数据库对象之间的关系,并画出直接对用户授权与间接对用户授权(系统权限与对象权限)的方法
    4 请讲出服务器角色、数据库角色、标准角色与应用程序角色的区别与验证其权限的方法
    5 请讲出数据库还原模型对数据库的影响
    6 有一个执行关键任务的数据库,请设计一个数据库备份策略
    7 请使用文件与文件组恢复的方式恢复数据库
    8 请使用事务日志恢复数据库到一个时间点
    9 请设计作业进行周期性的备份数据库
    10 如何监控数据库的阻塞,并实现数据库的死锁测试
    11 如何监控数据库的活动,并能使用索引优化向导生成索引
    12 理解数据库框图的作用并可以设计表与表之间的关系

    not in 、avg()

    张萨  78    67    89    76

      select count(1) from score where student_id = 1;

     
    

    inset ``into 表1(列名、列名) ``select 列名、列名 ``from 表2;

    王强  89    67    84    96

      select student_id from score where student_id in (
      select student_id from score where student_id !=1 GROUP BY student_id HAVING count(1) = (select count(1) from score where student_id = 1)
    ) and course_id in (select course_id from score where student_id = 1) GROUP BY student_id HAVING count(1) = (select count(1) from score where student_id = 1)

    1 有订单表,需要实现它的编号,格式如下:200211030001……200222039999等

        insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2)
        from student where sid not in (
            select student_id from score where course_id = 2
        )
    

    SELECT a.FName AS 姓名,

    insert into tb(student_id,course_id,num)

     
    

     

          英语 = SUM(CASE b.FSubName WHEN '英语' THEN c.FScore END),

      select student_id,2,(SELECT AVG(num) from score where course_id = 2) from score where course_id != 2

    2 有表T1,T2,现有一事务,在向表T1添加数据时,同时也必须向T2也添加数据,如何实现该事务

    17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分;

          数学 = SUM(CASE b.FSubName WHEN '数学' THEN c.FScore END),

    15、删除学习“叶平”老师课的SC表记录;

     
    

     

          语文 = SUM(CASE b.FSubName WHEN '语文' THEN c.FScore END),

    16、向SC表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“002”课程的同学学号;②插入“002”号课程的平均成绩; 

    3 如何向T1中的编号字段(codevarchar(20))添加一万条记录,不充许重复,规则如下:编号的数据必须从小写的a-z之间取值

    18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;

          历史 = SUM(CASE b.FSubName WHEN '历史' THEN c.FScore END)

    17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分;

     
    

    以课程id来分组

    FROM Student a, Subject b, Score c

    18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;

    4 如何删除表中的重复数据,请使用游标与分组的办法

     select course_id,max(num),min(num) from score GROUP BY course_id 

    WHERE a.FID = c.FStdId AND b.FSubID = c.FsubID GROUP BY a.FName

    19、按各科平均成绩从低到高和及格率的百分数从高到低顺序;

     
    

    19、按各科平均成绩从低到高和各科及格率的百分数从高到低顺序;

    11 原始表的数据如下:

    20、课程平均分从高到低显示(现实任课老师);

    5 如何求表中相邻的两条记录的某字段的值之差

    sum()求和、case when .. ``then...else...三元运算

    PID PTime    PNo

    21、查询各科成绩前三名的记录:(不考虑成绩并列情况) 

     
    

    三元运算,>=60就让其为1,否则让其为0,sum后就为及格的个数

    111111    2003-01-28 04:30:09     

    22、查询每门课程被选修的学生数;

    6 如何统计数据库中所有用户表的数据,显示格式如下:

    select course_id, avg(num) as avgnum,sum(case when score.num > 60 then 1 else 0 END)/count(1)*100 as percent 
    from score group by course_id order by avgnum asc,percent desc;
    

    111111    2003-01-28 18:30:00

    23、查询出只选修了一门课程的全部学生的学号和姓名;

     
    

     

    222222    2003-01-28 04:31:09     

    24、查询男生、女生的人数;

    表名      记录数

    20、课程平均分从高到低显示(显示任课老师名称);

    333333    2003-01-28 04:32:09     

    25、查询姓“张”的学生名单;

     
    

    联表操作、avg()、group by ...xxx  desc

    111111    2003-02-09 03:35:25     

    26、查询同名同姓学生名单,并统计同名人数;

      sales      23

    SELECT course_id,avg(num),tname FROM score 
    left join course on score.course_id=course.cid
    left join teacher on course.teacher_id=teacher.tid
    GROUP BY course_id desc
    

    222222    2003-02-09 03:36:25     

    27、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;

     
    

     

    333333    2003-02-09 03:37:25     

    28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩;

    7 如何删除数据库中的所有用户表(表与表之间有外键关系)

    21、查询各科成绩前三名的记录:(不考虑成绩并列情况) 

    查询生成表

    29、查询课程名称为“数学”,且分数低于60的学生姓名和分数;

     
    

    order by...排序、分页选取第一名和第三名成绩、从而选出第二名成绩

    PDate        111111        222222    333333      ......

    30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名; 

    8 表A editor_id lb2_id
    123 000
    123 003
    123 003
    456 007
    456 006
    表B lb2_id lb2_name
    000 a
    003 b
    006 c
    007 d
    显示 a 共1条 (表A内lb2_id为000的条数)
    b 共2条(表A内lb2_id为003的条数)

     

    2003-01-28    04:30:09    04:31:09      04:32:09    ......

    31、求选了课程的学生人数

     
    

    22、查询每门课程被选修的学生数;

    2003-01-28    18:30:00

    32、查询选修“杨艳”老师所授课程的学生中,成绩最高的学生姓名及其成绩;

    9 人员情况表(employee):里面有一字段文化程度(wh):包括四种情况(本科以上,大专,高中,初中以下),现在我要根据年龄字段查询统计出:表中文化程度为本科以上,大专,高中,初中以下,各有多少人,占总人数多少。

      select course_id, count(1) from score group by course_id; 

    2003-02-09    03:35:25    03:36:25      03:37:25    ......

    33、查询各个课程及相应的选修人数;

     
    

    23、查询出只选修了一门课程的全部学生的学号和姓名;

    12  表一(AAA)

    34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩;

    SELECT wh AS 学历,age as 年龄, Count(*) AS 人数,

    根据学生id分组,count学生ID=1的人

    商品名称mc  商品总量sl

    35、查询每门课程成绩最好的前两名;

     
    

     select student_id,count(student_id) from score GROUP BY student_id having count(student_id)=1 

      A        100

    36、检索至少选修两门课程的学生学号;

          Count(*) * 100 /(SELECT Count(*) FROMemployee) AS 百分比

    24、查询男生、女生的人数;

      B        120

    37、查询全部学生都选修的课程的课程号和课程名;

     
    

    select 1,2   #select语句可以不用加表

    表二(BBB)

    38、查询没学过“叶平”老师讲授的任一门课程的学生姓名;

    FROM employee GROUP BY wh,age

    select count``(1) ``as man ``from student ``where gender=``'男'``) ``as A  求出男生个数

    商品名称mc  出库数量sl

    39、查询两门以上不及格课程的同学的学号及其平均成绩;

     
    
        select * from
        (select count(1) as man from student where gender='男') as A ,
        (select count(1) as feman from student where gender='女') as B
    

      A        10

    40、检索“004”课程分数小于60,按分数降序排列的同学学号;

    学历      年龄   人数      百分比

     

      A        20

    41、删除“002”同学的“001”课程的成绩;

     
    

    25、查询姓“张”的学生名单;

      B        10

     

    本科以上  20     34          14

    like ‘张%’   %可匹配多个数

      B        20

     
    

    26、查询同名同姓学生名单,并统计同名人数;

      B        30

    大专      20     33          13

    27、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;

    用一条SQL语句算出商品A,B目前还剩多少?

     
    

    28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩;

    高中      20     33          13

    29、查询课程名称为“数学”,且分数低于60的学生姓名和分数;

    declare @AAA table (商品名称  varchar(10), 商品总量  int)

     
    

    30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名; 

    insert into @AAA values('A',100)

    初中以下  20     100        40

           

    insert into @AAA values('B',120)

     
    

     

    declare @BBB table (商品名称 varchar(10), 出库数量 int)

    本科以上  21      50         20

     

    insert into @BBB values('A', 10)

     
    

     

    insert into @BBB values('A', 20)

    10 现在有三个表student:(FID 学生号,FName  姓名),

     

    insert into @BBB values('B', 10)

     
    

     

    insert into @BBB values('B', 20)

    subject:(FSubID  课程号,FSubName 课程名), 

    insert into @BBB values('B', 30)

     
    

    select TA.商品名称,A-B AS 剩余数量 FROM

    Score(FScoreId  成绩记录号,FSubID   课程号,FStdID    学生号,FScore    成绩)

    (select 商品名称,sum(商品总量) AS A

     
    

    from @AAA

    怎么能实现这个表:

    group by 商品名称)TA,

     
    

    (select 商品名称,sum(出库数量) AS B

    姓名  英语  数学 语文  历史

    from @BBB

     
    

    group by 商品名称)TB

    张萨  78    67   89    76

    where TA.商品名称=TB.商品名称

     
    

    王强  89    67   84    96 

    select 商品名称,sum(商品总量) 剩余数量 from (select * from @aaa union all select 商品名称,-出库数量 from @bbb) a group by 商品名称

     
    

    13 优化这句SQL语句

    SELECT a.FName AS 姓名,

    UPDATE tblExlTempYear

     
    

    SET tblExlTempYear.GDQC = tblExlTempMonth.GDQC

          英语 = SUM(CASEb.FSubName WHEN '英语' THEN c.FScore END),

    FROM tblExlTempYear,tblExlTempMonth

     
    

    where tblExlTempMonth.GDXM=tblExlTempYear.GDXM and tblExlTempMonth.TXDZ=tblExlTempYear.TXDZ

          数学 = SUM(CASEb.FSubName WHEN '数学' THEN c.FScore END),

    (1)、加索引:

     
    

    tblExlTempYear(GDXM,TXDZ)

          语文 = SUM(CASEb.FSubName WHEN '语文' THEN c.FScore END),

    tblExlTempMonth (GDXM,TXDZ)

     
    

    (2)、删除无用数据

          历史 = SUM(CASEb.FSubName WHEN '历史' THEN c.FScore END)

    (3)、转移过时数据

     
    

    (4)、加服务器内存,升级服务器

    FROM Student a, Subject b, Score c

    (5)、升级网络系统

     
    

    UPDATE tblExlTempYear

    WHERE a.FID = c.FStdId AND b.FSubID = c.FsubID GROUP BY a.FName

    SET tblExlTempYear.GDQC = tblExlTempMonth.GDQC

     
    

    FROM tblExlTempYear (index indexY),tblExlTempMonth (index indexM)

    11 原始表的数据如下:

    where tblExlTempMonth.GDXM=tblExlTempYear.GDXM and tblExlTempMonth.TXDZ=tblExlTempYear.TXDZ

     
    

    14 品种    日期    数量

    PID PTime    PNo

    P0001  2002-1-10  10

     
    

    P0001  2002-1-10  11

    111111    2003-01-2804:30:09     

    P0001  2002-1-10  50

     
    

    P0001  2002-1-12  9

    111111    2003-01-28 18:30:00

    P0001  2002-1-12  8

     
    

    P0001  2002-1-12  7

    222222    2003-01-2804:31:09     

    P0002  2002-10-10  5

     
    

    P0002  2002-10-10  7

    333333    2003-01-2804:32:09     

    P0002  2002-10-12  0.5

     
    

    P0003  2002-10-10  5

    111111    2003-02-0903:35:25     

    P0003  2002-10-12  7

     
    

    P0003  2002-10-12  9

    222222    2003-02-0903:36:25     

    结果要先按照品种汇总,再按照日期汇总,结果如下:

     
    

    P0001  2002-1-10    71

    333333    2003-02-0903:37:25     

    P0001    2002-1-12  24

     
    

    P0002    2002-10-10  12

    查询生成表

    P0002    2002-10-12  0.5

     
    

    P0003    2002-10-10  5

    PDate       111111        222222   333333      ......

    P0003    2002-10-12  16

     
    

    SQL SERVER能做出这样的汇总吗…

    2003-01-28   04:30:09    04:31:09      04:32:09   ......

    15 在分組查循中with{cube|rollup}的區別是什么?

     
    

    如:

    2003-01-28   18:30:00

      use pangu

     
    

      select firm_id,p_id,sum(o_price_quantity)as sum_values

    2003-02-09   03:35:25    03:36:25     03:37:25    ......

      from orders

     
    

      group by firm_id,p_id

    12  表一(AAA)

      with cube

     
    

      與

    商品名称mc  商品总量sl

      use pangu

     
    

      select firm_id,p_id,sum(o_price_quantity)as sum_values

      A        100

      from orders

     
    

      group by firm_id,p_id

      B        120

      with rollup

     
    

      的區別是什么?

    表二(BBB)

    CUBE 和 ROLLUP 之间的区别在于:

     
    

    CUBE 生成的结果集显示了所选列中值的所有组合的聚合。

    商品名称mc  出库数量sl

    ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。

     
    

    例如,简单表 Inventory 中包含:

      A        10

    Item                Color                Quantity                 

     
    

      A        20

    Table                Blue                124                       

     
    

    Table                Red                  223                       

      B        10

    Chair                Blue                101                       

     
    

    Chair                Red                  210                       

      B        20

    下列查询将生成小计报表:

     
    

    SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'

      B        30

                ELSE ISNULL(Item, 'UNKNOWN')

     
    

          END AS Item,

    用一条SQL语句算出商品A,B目前还剩多少?

          CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'

     
    

                ELSE ISNULL(Color, 'UNKNOWN')

          END AS Color,

     
    

          SUM(Quantity) AS QtySum

    declare @AAA table (商品名称  varchar(10), 商品总量  int)

    FROM Inventory

     
    

    GROUP BY Item, Color WITH ROLLUP

    insert into @AAA values('A',100)

    Item                Color                QtySum                   

     
    

    insert into @AAA values('B',120)

    Chair                Blue                101.00                   

     
    

    Chair                Red                  210.00                   

    declare @BBB table (商品名称 varchar(10), 出库数量 int)

    Chair                ALL                  311.00                   

     
    

    Table                Blue                124.00                   

    insert into @BBB values('A', 10)

    Table                Red                  223.00                   

     
    

    Table                ALL                  347.00                   

    insert into @BBB values('A', 20)

    ALL                  ALL                  658.00                   

     
    

    (7 row(s) affected)

    insert into @BBB values('B', 10)

    如果查询中的 ROLLUP 关键字更改为 CUBE,那么 CUBE 结果集与上述结果相同,只是在结果集的末尾还会返回下列两行:

     
    

    ALL                  Blue                225.00                   

    insert into @BBB values('B', 20)

    ALL                  Red                  433.00                   

     
    

    CUBE 操作为 Item 和 Color 中值的可能组合生成行。例如,CUBE 不仅报告与 Item 值 Chair 相组合的 Color 值的所有可能组合(Red、Blue 和 Red Blue),而且报告与 Color 值 Red 相组合的 Item 值的所有可能组合(Chair、Table 和 Chair Table)。对于 GROUP BY 子句中右边的列中的每个值,ROLLUP 操作并不报告左边一列(或左边各列)中值的所有可能组合。例如,ROLLUP 并不对每个 Color 值报告 Item 值的所有可能组合。ROLLUP 操作的结果集具有类似于 COMPUTE BY 所返回结果集的功能;然而,

    insert into @BBB values('B', 30)

    ROLLUP 具有下列优点: ROLLUP 返回单个结果集;COMPUTE BY 返回多个结果集,而多个结果集会增加应用程序代码的复杂性。ROLLUP 可以在服务器游标中使用;COMPUTE BY 不可以。有时,查询优化器为 ROLLUP 生成的执行计划比为 COMPUTE BY 生成的更为高效。

     
    

    16 假如我有两个表
    表1(电话号码,是否存在)
    表2(电话号码,是否拨打)
    想查找表1中的电话号码是否在表2中存在,如果存在就更新表1中的是否存在字段为1。

    select TA.商品名称,A-B AS 剩余数量 FROM

        UPDATE 表1 SET 是否存在=1
    WHERE EXISTS(SELECT * FROM 表2 WHERE 表2.电话号码 = 表1.电话号码)

     
    

    17 用存储过程调用外部程序.
    不过要做成com控件
    用sp_OACreate存储过程)
    DECLARE @object int
    DECLARE @hr int
    DECLARE @src varchar(255), @desc varchar(255)
    EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
    IF @hr <> 0
    BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
    END

    (select 商品名称,sum(商品总量) AS A

    1、在MS SQL Server中,用来显示数据库信息的系统存储过程是( )
    A sp_ dbhelp
    B sp_ db
    C sp_ help
    D sp_ helpdb

     
    

    2、SQL语言中,删除一个表的命令是( )
    A DELETE
    B DROP
    C CLEAR
    D REMORE

    from @AAA

    3、关系数据库中,主键是(__)
    A、为标识表中唯一的实体
    B、创建唯一的索引,允许空值
    C、只允许以表中第一字段建立
    D、允许有多个主键的

     
    

    4、在Transact-SQL语法中,SELECT语句的完整语法较复杂,但至少包括的部分(1___),使用关键字(2___)可以把重复行屏蔽,将多个查询结果返回一个结果集合的运算符是(3___),如果在SELECT语句中使用聚合函数时,一定在后面使用(4___)。
        ⑴ A、SELECT,INTO              B、SELECT,FROM
          C、SELECT,GROUP            D、仅SELECT
      ⑵ A、DISTINCT                          B、UNION
            C、ALL                                  C、TOP
      ⑶ A、JOIN                                B、UNION
            C、INTO                              C、LIKE
      ⑷ A、GROUP BY                      B、COMPUTE BY
            C、HAVING                          D、COMPUTE

    group by 商品名称)TA,

    5、语句DBCC SHRINKDATABASE (Sample, 25)中的25表示的意思是
    A、25M
    B、剩余占整个空间的25%
    C、已用空间占整个空间的25%
    D、以上都不对

     
    

    6、你是一个保险公司的数据库开发人员,公司的保单信息存储在SQL Server 2000数据库中,你使用以下脚本建立了一个名为Policy的表:
    CREATE TABLE Policy
    (
    PolicyNumber int NOT NULL DEFAULT (0),
    InsuredLastName char (30) NOT NULL,
    InsuredFirstName char (20) NOT NULL,
    InsuredBirthDate datetime NOT NULL,
    PolicyDate datetime NOT NULL,
    FaceAmount money NOT NULL,
    CONSTRAINT PK_Policy PRIMARY KEY (PolicyNumber)
    )
    每次公司销售出一份保单,Policy表中就增加一条记录,并赋予其一个新的保单号,你将怎么做?

    (select 商品名称,sum(出库数量) AS B

    a.建立一个INSTEAD OF INSERT触发器来产生一个新的保单号,并将这个保单号插入数据表中。
    b.建立一个INSTEAD OF UPDATE触发器来产生一个新的保单号,并将这个保单号插入数据表中。
    c.建立一个AFTER UPDATE触发器来产生一个新的保单号,并将这个保单号插入数据表中。
    d.用AFTER UPDATE触发器替代DEFAULT约束条件产生一个新的保单号,并将这个保单号插入数据表中。

     
    

    7、在SQL语言中,如果要建立一个工资表包含职工号,姓名,职称。工资等字段。若要保证工资字段的取值不低于800元,最合适的实现方法是:
    A。在创建工资表时为”工资“字段建立缺省
    B。在创建工资表时为”工资“字段建立检查约束
    C。在工资表建立一个触发器
    D。为工资表数据输入编写一个程序进行控制

    from @BBB

    8、Select 语句中用来连接字符串的符号是______.
    A. “ ” B. “&” C.“||” D.“|”

     
    

    9、你是一个出版公司的数据库开发人员,对特定的书名的每天的销售情况建立了如下的存储过程:
    CREATE PROCEDURE get_sales_for_title
    title varchar(80), @ytd_sales int OUTPUT
    AS
    SELECT @ytd_sales = ytd_sales
    FROM titles
    WHERE title = @title
    IF @@ROWCOUNT = 0
    RETURN(-1)
    ELSE
    RETURN(0)
    另外建立了一个脚本执行这个存储过程,如果执行成功,将返回对应于书名的每天的销售情况的报表,如果执行失败,将返回“No Sales Found”,怎样建立这个脚本?

    group by 商品名称)TB

    A. DECLARE @retval int
    DECLARE @ytd int
    EXEC get_sales_for_title ‘Net Etiquette’, @ytd
    IF @retval < 0
    PRINT ‘No sales found’
    ELSE
    PRINT ‘Year to date sales: ’ STR (@ytd)
    GO

     
    

    B. DECLARE @retval int
    DECLARE @ytd int
    EXEC get_sales_for_title ‘Net Etiquette’, @ytd OUTPUT
    IF @retval < 0
    PRINT ‘No sales found’
    ELSE
    PRINT ‘Year to date sales: ’ STR (@ytd)
    GO

    where TA.商品名称=TB.商品名称

    C. DECLARE @retval int
    DECLARE @ytd int
    EXEC get_sales_for_title ‘Net Etiquette’,@retval OUTPUT
    IF @retval < 0
    PRINT ‘No sales found’
    ELSE
    PRINT ‘Year to date sales: ’ STR (@ytd)
    GO

     
    

    D. DECLARE @retval int
    DECLARE @ytd int
    EXEC @retval = get_sales_for_title ‘Net Etiquette’, @ytd OUTPUT
    IF @retval < 0
    PRINT ‘No sales found’
    ELSE
    PRINT ‘Year to date sales: ’ STR (@ytd)
    GO

    10、You are a database developer for a container manufacturing company. The containers produced by your company are a number of different sizes and shapes. The tables that store the container information are shown in the Size, Container, and Shape Tables exhibit:
    Size
    SizeID
    SizeName
    Height
    Container
    ContainerID
    ShapeID
    SizeID
    Shape
    ShapeID
    ShapeName
    Measurements

     
    

    A sample of the data stored in the tables is shown below:
    Size Table
    SizeID        SizeName        Height
    1            Small          40
    2            Medium          60
    3            Large          80
    4            Jumbo          100
    Shape Table
    ShapeID  ShapeName  Measurement
    1        Triangle        10
    2        Triangle        20
    3        Triangle        30
    4        Square          20
    5        Square          30
    6        Square          40
    7        Circle          15
    8        Circle          25
    9        Circle          35
    Periodically, the dimensions of the containers change. Frequently, the database users require the volume of a container. The volume of a container is calculated based on information in the shape and size tables.
    You need to hide the details of the calculation so that the volume can be easily accessed in a SELECT query with the rest of the container information. What should you do?
    A.    Create a user-defined function that requires ContainerID as an argument and returns the volume of the container.
    B.    Create a stored procedure that requires ContainerID as an argument and returns the volume of the container.
    C.    Add a column named volume to the container table. Create a trigger that calculates and stores volume in this column when a new container is inserted into the table.
    D.    Add a computed column to the container table that calculates the volume of the container.

    select 商品名称,sum(商品总量)剩余数量 from (select * from @aaa union all select 商品名称,-出库数量 from @bbb) a group by 商品名称

    填空题(1空1分共20分)
    1、 如果设计的表不符合第二范式,可能会导致_______,________,_______。
    2、 SQL是由_______语言,________语言,_______语言组成。
    3、 SQL Server在两个安全级上验证用户,分别是______________,_____________________。
    4、 自定义函数由___________函数,_______________函数,___________________函数组成。
    5、 备份策略的三种类型是__________备份,_______________备份,___________________备份组成。
    6、 启动一个显式事务的语句为__________,提交事务的语句为__________,回滚事务的语句为__________
    7、 表的每一行在表中是惟一的实体属于__________完整性,使列的输入有效属于__________完整性,两个表的主关键字和外关键字的数据应该对应一致属于__________完整性。
    简答题(共20分)
    1、 在帮助中[ ,...n ] 意思是什么?(4分)
    2、 请简述一下第二范式(4分)
    3、 现有1销售表,它们结构如下:(4分)
    id int                      (标识号)
    codno char(7)              (商品编码)
    codname varchar(30)        (商品名称)
    spec varchar(20)                        (商品规格)
    price numeric(10,2)            (价格)
    sellnum int                          (销售数量)
    deptno char(3)                    (售出分店编码)
    selldate datetime                    (销售时间)
    要求:写出查询销售时间段在2002-2-15日到2002-4-29之间,分店编码是01的所有记录。
    4、写一个存储过程,要求传入一个表名,返回该表的记录数(假设传入的表在数据库中都存在)(4分)
    5、请简述UPDATE 触发器如何工作原理。(4分)
     
    简答题:(共40分)
    1、(5分)使用一条SQL语句找到重复的值及重复的次数:有一数据表ZD_ks,其中有字段BM,MC,。。。,请查询出在ZD_ks中BM有重复的值及重复的次数,没有的不要列出。如下表:
    BM  DUPCOUNT
    001   3
    002   2

     
    

    2、描述(5分)
    表1 student 学生信息表
    ID    int      学生编号
    Name  varchar  学生姓名
    Sex    bit      性别(男0女1)
    Class int      班级编号

    13 优化这句SQL语句

    表2 schedule 课程信息表
    ID    int      课程编号
    Name  varchar  课程名称

     
    

    表3 Grade  成绩信息表
    ID    int      自动编号
    UID    int      学生编号
    SID    int      课程编号
    Num    int      考试成绩

    UPDATE tblExlTempYear

    (a)求各班的总人数(1分)
    (b)求1班女生和男生的平均成绩(2分)
    (c)各班"数据结构"(课程名称)不及格的人数(2分)

     
    

    3、问题描述:(30分)
    本题用到下面三个关系表:
    CARD    借书卡。  CNO 卡号,NAME 姓名,CLASS 班级
    BOOKS    图书。    BNO 书号,BNAME 书名,AUTHOR 作者,PRICE 单价,QUANTITY 库存册数
    BORROW  借书记录。 CNO 借书卡号,BNO 书号,RDATE 还书日期
    备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。
    要求实现如下15个处理:
      1.找出借书超过5本的读者,输出借书卡号及所借图书册数。(2分)
    2.查询借阅了"水浒"一书的读者,输出姓名及班级。(3分)
    3.查询过期未还图书,输出借阅者(卡号)、书号及还书日期。(3分)
    4.查询书名包括"网络"关键词的图书,输出书号、书名、作者。(2分)
    5.查询现有图书中价格最高的图书,输出书名及作者。(2分)
    6.查询当前借了"计算方法"但没有借"计算方法习题集"的读者,输出其借书卡号,并按卡号降序排序输出。(4分)
    7.将"C01"班同学所借图书的还期都延长一周。(2分)
    8.从BOOKS表中删除当前无人借阅的图书记录。(2分)
    9.在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是"数据库技术及应用",就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。(4分)
    10.建立一个视图,显示"力01"班学生的借书信息(只要求显示姓名和书名)。(3分)
    11.查询当前同时借有"计算方法"和"组合数学"两本书的读者,输出其借书卡号,并按卡号升序排序输出。(3分)

    SET tblExlTempYear.GDQC = tblExlTempMonth.GDQC

    第二套
    1、问题描述:
    新葡亰496net,已知关系模式:
    S (SNO,SNAME)                      学生关系。SNO 为学号,SNAME 为姓名
    C (CNO,CNAME,CTEACHER)  课程关系。CNO 为课程号,CNAME 为课程名,CTEACHER 为任课教师
    SC(SNO,CNO,SCGRADE)        选课关系。SCGRADE 为成绩
    要求实现如下5个处理:
      1. 找出没有选修过“李明”老师讲授课程的所有学生姓名
      2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
      3. 列出既学过“1”号课程,又学过“2”号课程的所有学生姓名
      4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
      5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

     
    

    2、问题描述:
    为管理岗位业务培训信息,建立3个表:
    S (S#,SN,SD,SA)  S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
    C (C#,CN )        C#,CN      分别代表课程编号、课程名称
    SC ( S#,C#,G )    S#,C#,G    分别代表学号、所选修的课程编号、学习成绩

    FROM tblExlTempYear,tblExlTempMonth

    要求实现如下5个处理:
      1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
      2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
      3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
      4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
      5. 查询选修了课程的学员人数
      6. 查询选修课程超过5门的学员学号和所属单位

     
    

    有重复的再发
      Answer 1:找出当月的天数
    select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar) '-' cast(month(getdate()) as varchar) '-01' as datetime))))
      Question2:Can you use a SQL statement to calculating it!
    How can I print "10 to 20" for books that sell for between $10 and $20,"unknown" for books whose price is null, and "other" for all other prices?
      Answer 2:
      select bookid,bookname,price=case when price is null then   'unknown'
          when  price between 10 and 20 then '10 to 20' else price end
    from books
      Question3:Can you use a SQL statement to finding duplicate   values!
      How can I find authors with the same last name?
    You can use the table authors in datatabase pubs. I want to get the result as below:
    Output:
    au_lname                                number_dups

    where tblExlTempMonth.GDXM=tblExlTempYear.GDXM andtblExlTempMonth.TXDZ=tblExlTempYear.TXDZ


     
    

    Ringer                                  2
    (1 row(s) affected)
      Answer 3
      select au_lname,number_dups=count(1) from authors group by au_lname
      Question4:Can you create a cross-tab report in my SQL Server!
      How can I get the report about sale quality for each store and each quarter and the total sale quality for each quarter at year 1993?
      You can use the table sales and stores in datatabase pubs.
    Table Sales record all sale detail item for each store. Column store_id is the id of each store, ord_date is the order date of each sale item, and column qty is the sale qulity. Table stores record all store information.
    I want to get the result look like as below:
    Output:
    stor_name                                Total      Qtr1        Qtr2        Qtr3        Qtr4       

    (1)、加索引:


     
    

    tblExlTempYear(GDXM,TXDZ)

    Barnum's                                50          0          50          0          0
    Bookbeat                                55          25          30          0          0
    Doc-U-Mat: Quality Laundry and Books    85          0          85          0          0
    Fricative Bookshop                      60          35          0          0          25
    Total                                    250        60          165        0          25
    Answer 4:用动态SQL实现

     
    

      Question5: The Fastest Way to Recompile All Stored Procedures
      I have a problem with a database running in SQL Server 6.5 (Service Pack 4). We moved the database (object transfer) from one machine to another last night, and an error (specific to a stored procedure) is cropping up. However, I can't tell which procedure is causing it. Permissions are granted in all of our stored procedures; is there a way from the isql utility to force all stored procedures to recompile?
      Tips: sp_recompile can recomplie a store procedure each time
      Answer 5:在执行存储过程时,使用 with recompile 选项强制编译新的计划;使用sp_recompile系统存储过程强制在下次运行时进行重新编译
      Question6: How can I add row numbers to my result set?
    In database pubs, have a table titles , now I want the result shown as below,each row have a row number, how can you do that?
    Result:
    line-no    title_id

    tblExlTempMonth (GDXM,TXDZ)


     
    

    1          BU1032
    2          BU1111
    3          BU2075
    4          BU7832
    5          MC2222
    6          MC3021
    7          MC3026
    8          PC1035
    9          PC8888
    10          PC9999
    11          PS1372
    12          PS2091
    13          PS2106
    14          PS3333
    15          PS7777
    16          TC3218
    17          TC4203
    18          TC7777
      Answer 6:
      --SQL 2005的写法
      select row_number() as line_no ,title_id from titles
      --SQL 2000的写法
      select line_no identity(int,1,1),title_id into #t from titles
      select * from #t
      drop table #t
    Question 7: Can you tell me what the difference of two SQL statements at performance of execution?
      Statement 1:
      if NOT EXISTS ( select * from publishers where state = 'NY')
      begin

    (2)、删除无用数据

      SELECT 'Sales force needs to penetrate New York market'end
      else
      begin
      SELECT 'We have publishers in New York'
      end
      Statement 2:
      if EXISTS ( select * from publishers where state = 'NY')
      begin
      SELECT 'We have publishers in New York'
      end
      else
      begin
      SELECT 'Sales force needs to penetrate New York market'
      end
      Answer 7:不同点:执行时的事务数,处理时间,从客户端到服务器端传送的数据量大小
      Question8: How can I list all California authors regardless of whether they have written a book?
      In database pubs, have a table authors and titleauthor , table authors has a column state, and titleauhtor have books each author written.
    CA behalf of california in table authors.
      Answer 8:
      select * from  authors where state='CA'
      Question9: How can I get a list of the stores that have bought both 'bussiness' and 'mod_cook' type books?
      In database pubs, use three table stores,sales and titles to implement this requestment.
    Now I want to get the result as below:
    stor_id stor_name                               

     
    

    (3)、转移过时数据

    ...
    7896    Fricative Bookshop
    ...
    ...
    ...
    Answer 9:
    select distinct a.stor_id, a.stor_name from stores a,sales b,titles c
    where a.stor_id=b.stor_id and b.title_id=c.title_id and c.type='business' and
    exists(select 1 from sales k,titles g where stor_id=b.stor_id
    and k.title_id=g.title_id and g.type='mod_cook')   
    Question10: How can I list non-contignous data?
      In database pubs, I create a table test using statement as below, and I insert several row as below
    create table test
    ( id int primary key )
    go
    insert into test values (1 )
    insert into test values (2 )
    insert into test values (3 )
    insert into test values (4 )
    insert into test values (5 )
    insert into test values (6 )
    insert into test values (8 )
    insert into test values (9 )
    insert into test values (11)
    insert into test values (12)
    insert into test values (13)
    insert into test values (14)
    insert into test values (18)
    insert into test values (19)
    go
    Now I want to list the result of the non-contignous row as below,how can I do it?
    Missing after Missing before

     
    

    (4)、加服务器内存,升级服务器

    6            8
    9            11
    ...

     
    

      Answer 10:
      select id from test t where not exists(select 1 from test where id=t.id 1)
    or not exists(select 1 from test where id=t.id-1)
      Question11: How can I list all book with prices greather than the average price of books of the same type?
      In database pubs, have a table named titles , its column named price mean the price of the book, and another named type mean the type of books.
    Now I want to get the result as below:
    type        title                                                                           

    (5)、升级网络系统

    price               



    business    The Busy Executive's Database Guide                                              19.9900
    ...
    ...
    ...
    ...
      Answer 11:
      select a.type,a.title,a.price from titles a,
    (select type,price=avg(price) from titles group by type)b
    where a.type=b.type and a.price>b.price

    向T1中的编号字段(code varchar(20))添加一万条记录,不充许重复:
    网上有给出的答案:
    create table #tmp(id char(1),name char(1))
    create table #tmp1(id varchar(10))
    go
    insert #tmp(id,name) values(0,'a')
    insert #tmp(id,name) values(1,'b')
    insert #tmp(id,name) values(2,'c')
    insert #tmp(id,name) values(3,'d')
    insert #tmp(id,name) values(4,'e')
    insert #tmp(id,name) values(5,'f')
    insert #tmp(id,name) values(6,'g')
    insert #tmp(id,name) values(7,'h')
    insert #tmp(id,name) values(8,'i')
    insert #tmp(id,name) values(9,'j')
    go
    declare @t varchar(10)
    set @t=10000
    while @t <=20000
    begin
            insert #tmp1 values(@t)
            set @t=@t 1
    end
    go
    insert t1(code)
    select (select name from #tmp where id=left(a.id,1))
    (select name from #tmp where id=substring(a.id,2,1))
    (select name from #tmp where id=substring(a.id,3,1))
    (select name from #tmp where id=substring(a.id,4,1))
    (select name from #tmp where id=right(a.id,1))
    from #tmp1 a
    go
    drop table #tmp
    drop table #tmp1
    go

    这样做,确实没想到,测试后本机用了5秒(不往T1里面插入数据,用#T1做临时表,只有一个Code字段),后改成我这样的写法,用时3秒.

    declare @i int,@j int,@r int,@acount int
    create table #tmp(id int null, code varchar(3) null)
    declare @ichar char(1)
    declare @jchar char(1)
    declare @rchar char(1)
    select @i=0
    select @j=0
    select @r=0
    select @acount=0

    select @ichar=''
    while @i <26
    begin
    select @ichar=char(97 @i)
    select @j=0
    while @j <26
    begin
    select @jchar=char(97 @j)
    select @r=0
    while @r <26
    begin
    select @rchar=char(97 @r)
    select @acount=@acount 1
    if @acount>10000
    break
    insert into #tmp(id,code)
    select @acount,@ichar @jchar @rchar
    select @r=@r 1
    end
    if @acount>10000
    break
    select @j=@j 1
    end
    if @acount>10000
    break
    select @i=@i 1
    end
    insert t1(code)
    select code from #tmp order by id
    go
    drop table #tmp

     
    

    UPDATE tblExlTempYear

     
    

    SET tblExlTempYear.GDQC = tblExlTempMonth.GDQC

     
    

    FROM tblExlTempYear (index indexY),tblExlTempMonth (index indexM)

     
    

    where tblExlTempMonth.GDXM=tblExlTempYear.GDXM andtblExlTempMonth.TXDZ=tblExlTempYear.TXDZ

     
    

    14 品种    日期    数量

     
    

    P0001  2002-1-10 10

     
    

    P0001  2002-1-10 11

     
    

    P0001  2002-1-10 50

     
    

    P0001  2002-1-12  9

     
    

    P0001  2002-1-12  8

     
    

    P0001  2002-1-12  7

     
    

    P0002  2002-10-10 5

     
    

    P0002  2002-10-10 7

     
    

    P0002  2002-10-12 0.5

     
    

    P0003  2002-10-10 5

     
    

    P0003  2002-10-12 7

     
    

    P0003  2002-10-12 9

     
    

    结果要先按照品种汇总,再按照日期汇总,结果如下:

     
    

    P0001  2002-1-10   71

     
    

    P0001    2002-1-12 24

     
    

    P0002    2002-10-10 12

     
    

    P0002    2002-10-12 0.5

     
    

    P0003    2002-10-10 5

     
    

    P0003    2002-10-12 16

     
    

    SQL SERVER能做出这样的汇总吗…

     
    

    15 在分組查循中with{cube|rollup}的區別是什么?

     
    

    如:

     
    

      use pangu

     
    

      select firm_id,p_id,sum(o_price_quantity)as sum_values

     
    

      from orders

     
    

      group by firm_id,p_id

     
    

      with cube

     
    

      與

     
    

      use pangu

     
    

      select firm_id,p_id,sum(o_price_quantity)as sum_values

     
    

      from orders

     
    

      group by firm_id,p_id

     
    

      with rollup

     
    

      的區別是什么?

     
    

    CUBE 和 ROLLUP 之间的区别在于:

     
    

    CUBE 生成的结果集显示了所选列中值的所有组合的聚合。

     
    

    ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。

     
    

    例如,简单表 Inventory 中包含:

     
    

    Item               Color               Quantity                 

     
    

     
    

    Table               Blue               124                       

     
    

    Table               Red                 223                       

     
    

    Chair               Blue               101                       

     
    

    Chair               Red                 210 

     
    

    下列查询将生成小计报表:

     
    

    SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'

     
    

               ELSE ISNULL(Item, 'UNKNOWN')

     
    

          END AS Item,

     
    

          CASE WHEN (GROUPING(Color) = 1) THEN'ALL'

     
    

               ELSE ISNULL(Color, 'UNKNOWN')

     
    

          END AS Color,

     
    

          SUM(Quantity) AS QtySum

     
    

    FROM Inventory

     
    

    GROUP BY Item, Color WITH ROLLUP

     
    

    Item               Color               QtySum                   

     
    

     
    

    Chair               Blue               101.00                   

     
    

    Chair               Red                 210.00                   

     
    

    Chair               ALL                 311.00                   

     
    

    Table               Blue               124.00                   

     
    

    Table               Red                 223.00                   

     
    

    Table               ALL                 347.00                   

     
    

    ALL                 ALL                 658.00                   

     
    

    (7 row(s) affected)

     
    

    如果查询中的 ROLLUP 关键字更改为 CUBE,那么 CUBE 结果集与上述结果相同,只是在结果集的末尾还会返回下列两行:

     
    

    ALL                 Blue               225.00                   

     
    

    ALL                 Red                 433.00                   

     
    

    CUBE 操作为 Item 和Color 中值的可能组合生成行。例如,CUBE 不仅报告与Item 值 Chair 相组合的 Color 值的所有可能组合(Red、Blue 和 Red Blue),而且报告与 Color 值 Red 相组合的 Item 值的所有可能组合(Chair、Table 和 Chair Table)。对于 GROUP BY 子句中右边的列中的每个值,ROLLUP 操作并不报告左边一列(或左边各列)中值的所有可能组合。例如,ROLLUP 并不对每个 Color 值报告 Item 值的所有可能组合。ROLLUP 操作的结果集具有类似于 COMPUTE BY 所返回结果集的功能;然而,

     
    

    ROLLUP 具有下列优点: ROLLUP 返回单个结果集;COMPUTE BY 返回多个结果集,而多个结果集会增加应用程序代码的复杂性。ROLLUP可以在服务器游标中使用;COMPUTE BY 不可以。有时,查询优化器为 ROLLUP 生成的执行计划比为 COMPUTE BY 生成的更为高效。

     
    

    16 假如我有两个表
    表1(电话号码,是否存在)
    表2(电话号码,是否拨打)
    想查找表1中的电话号码是否在表2中存在,如果存在就更新表1中的是否存在字段为1。
        UPDATE 表1 SET 是否存在=1
    WHERE EXISTS(SELECT * FROM 表2 WHERE 表2.电话号码 = 表1.电话号码)

     
    

    17 用存储过程调用外部程序.
    不过要做成com控件
    用sp_OACreate存储过程)
    DECLARE @object int
    DECLARE @hr int
    DECLARE @src varchar(255), @desc varchar(255)
    EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
    IF @hr <> 0
    BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
    END

     
    

    (六)

     
    

    1、在MS SQL Server中,用来显示数据库信息的系统存储过程是( )
    A sp_ dbhelp
    B sp_ db
    C sp_ help
    D sp_ helpdb

     
    

    2、SQL语言中,删除一个表的命令是( )
    A DELETE
    B DROP
    C CLEAR
    D REMORE

     
    

    3、关系数据库中,主键是(__)
    A、为标识表中唯一的实体
    B、创建唯一的索引,允许空值
    C、只允许以表中第一字段建立
    D、允许有多个主键的

     
    

    4、在Transact-SQL语法中,SELECT语句的完整语法较复杂,但至少包括的部分(1___),使用关键字(2___)可以把重复行屏 蔽,将多个查询结果返回一个结果集合的运算符是(3___),如果在SELECT语句中使用聚合函数时,一定在后面使用(4___)。
        ⑴ A、SELECT,INTO             B、SELECT,FROM
          C、SELECT,GROUP           D、仅SELECT
      ⑵ A、DISTINCT                         B、UNION
            C、ALL                                 C、TOP
      ⑶ A、JOIN                               B、UNION
            C、INTO                             C、LIKE
      ⑷ A、GROUPBY                     B、COMPUTE BY
            C、HAVING                         D、COMPUTE

     
    

    5、语句DBCC SHRINKDATABASE (Sample, 25)中的25表示的意思是
    A、25M
    B、剩余占整个空间的25%
    C、已用空间占整个空间的25%
    D、以上都不对

     
    

    6、你是一个保险公司的数据库开发人员,公司的保单信息存储在SQL Server 2000数据库中,你使用以下脚本建立了一个名为Policy的表:
    CREATE TABLE Policy
    (
    PolicyNumber int NOT NULL DEFAULT (0),
    InsuredLastName char (30) NOT NULL,
    InsuredFirstName char (20) NOT NULL,
    InsuredBirthDate datetime NOT NULL,
    PolicyDate datetime NOT NULL,
    FaceAmount money NOT NULL,
    CONSTRAINT PK_Policy PRIMARY KEY (PolicyNumber)
    )
    每次公司销售出一份保单,Policy表中就增加一条记录,并赋予其一个新的保单号,你将怎么做?

     
    

    a.建立一个INSTEAD OF INSERT触发器来产生一个新的保单号,并将这个保单号插入数据表中。
    b.建立一个INSTEAD OF UPDATE触发器来产生一个新的保单号,并将这个保单号插入数据表中。
    c.建立一个AFTER UPDATE触发器来产生一个新的保单号,并将这个保单号插入数据表中。
    d.用AFTER UPDATE触发器替代DEFAULT约束条件产生一个新的保单号,并将这个保单号插入数据表中。

     
    

    7、在SQL语言中,如果要建立一个工资表包含职工号,姓名,职称。工资等字段。若要保证工资字段的取值不低于800元,最合适的实现方法是:
    A。在创建工资表时为”工资“字段建立缺省
    B。在创建工资表时为”工资“字段建立检查约束
    C。在工资表建立一个触发器
    D。为工资表数据输入编写一个程序进行控制

     
    

    8、Select 语句中用来连接字符串的符号是______.
    A. “ ” B. “&” C.“||” D.“|”

     
    

    9、你是一个出版公司的数据库开发人员,对特定的书名的每天的销售情况建立了如下的存储过程:
    CREATE PROCEDURE get_sales_for_title
    title varchar(80), @ytd_sales int OUTPUT
    AS
    SELECT @ytd_sales = ytd_sales
    FROM titles
    WHERE title = @title
    IF @@ROWCOUNT = 0
    RETURN(-1)
    ELSE
    RETURN(0)
    另外建立了一个脚本执行这个存储过程,如果执行成功,将返回对应于书名的每天的销售情况的报表,如果执行失败,将返回“NoSales Found”,怎样建立这个脚本?

     
    

    A. DECLARE @retval int
    DECLARE @ytd int
    EXEC get_sales_for_title ‘Net Etiquette’, @ytd
    IF @retval < 0
    PRINT ‘No sales found’
    ELSE
    PRINT ‘Year to date sales: ’ STR (@ytd)
    GO

     
    

    B. DECLARE @retval int
    DECLARE @ytd int
    EXEC get_sales_for_title ‘Net Etiquette’, @ytd OUTPUT
    IF @retval < 0
    PRINT ‘No sales found’
    ELSE
    PRINT ‘Year to date sales: ’ STR (@ytd)
    GO

     
    

    C. DECLARE @retval int
    DECLARE @ytd int
    EXEC get_sales_for_title ‘Net Etiquette’,@retval OUTPUT
    IF @retval < 0
    PRINT ‘No sales found’
    ELSE
    PRINT ‘Year to date sales: ’ STR (@ytd)
    GO

     
    

    D. DECLARE @retval int
    DECLARE @ytd int
    EXEC @retval = get_sales_for_title ‘Net Etiquette’, @ytd OUTPUT
    IF @retval < 0
    PRINT ‘No sales found’
    ELSE
    PRINT ‘Year to date sales: ’ STR (@ytd)
    GO

     
    

    10、You are a database developer for a container manufacturing company.The containers produced by your company are a number of different sizes andshapes. The tables that store the container information are shown in the Size,Container, and Shape Tables exhibit:
    Size
    SizeID
    SizeName
    Height
    Container
    ContainerID
    ShapeID
    SizeID
    Shape
    ShapeID
    ShapeName
    Measurements

     
    

    A sample of the data stored in the tables is shown below:
    Size Table
    SizeID       SizeName        Height
    1           Small          40
    2           Medium          60
    3           Large          80
    4           Jumbo          100
    Shape Table
    ShapeID  ShapeName  Measurement
    1       Triangle        10
    2       Triangle        20
    3       Triangle        30
    4       Square          20
    5       Square          30
    6       Square          40
    7       Circle          15
    8       Circle          25
    9       Circle          35
    Periodically, the dimensions of the containers change. Frequently, the databaseusers require the volume of a container. The volume of a container iscalculated based on information in the shape and size tables.
    You need to hide the details of the calculation so that the volume can beeasily accessed in a SELECT query with the rest of the container information.What should you do?
    A.    Create a user-defined function that requires ContainerIDas an argument and returns the volume of the container.
    B.    Create a stored procedure that requires ContainerID as anargument and returns the volume of the container.
    C.    Add a column named volume to the container table. Create atrigger that calculates and stores volume in this column when a new containeris inserted into the table.
    D.    Add a computed column to the container table thatcalculates the volume of the container.

     
    

    填空题(1空1分共20分)
    1、 如果设计的表不符合第二范式,可能会导致_______,________,_______。
    2、 SQL是由_______语言,________语言,_______语言组成。
    3、 SQL Server在两个安全级上验证用户,分别是______________,_____________________。
    4、 自定义函数由___________函数,_______________函数,___________________函数组成。
    5、 备份策略的三种类型是__________备份,_______________备份,___________________备份组成。
    6、 启动一个显式事务的语句为__________,提交事务的语句为__________,回滚事务的语句为__________
    7、 表的每一行在表中是惟一的实体属于__________完整性,使列的输入有效属于__________完整性,两个表的主关键字和外关键字的数据应该对应一致属于__________完整性。
    简答题(共20分)
    1、 在帮助中[ ,...n ] 意思是什么?(4分)
    2、 请简述一下第二范式(4分)
    3、 现有1销售表,它们结构如下:(4分)
    idint                     (标识号)
    codnochar(7)             (商品编码)
    codname varchar(30)        (商品名称)
    specvarchar(20)                       (商品规格)
    pricenumeric(10,2)           (价格)
    sellnumint                         (销售数量)
    deptnochar(3)                   (售出分店编码)
    selldatedatetime                   (销售时间)
    要求:写出查询销售时间段在2002-2-15日到2002-4-29之间,分店编码是01的所有记录。
    4、写一个存储过程,要求传入一个表名,返回该表的记录数(假设传入的表在数据库中都存在)(4分)
    5、请简述UPDATE 触发器如何工作原理。(4分)
    简答题:(共40分)
    1、(5分)使用一条SQL语句找到重复的值及重复的次数:有一数据表ZD_ks,其中有字段BM,MC,。。。,请查询出在ZD_ks中BM有重复的值及重复的次数,没有的不要列出。如下表:
    BM DUPCOUNT
    001 3
    002 2

     
    

    2、描述(5分)
    表1 student 学生信息表
    ID    int      学生编号
    Name  varchar  学生姓名
    Sex    bit      性别(男0女1)
    Class int      班级编号

     
    

    表2 schedule 课程信息表
    ID    int      课程编号
    Name  varchar  课程名称

     
    

    表3 Grade  成绩信息表
    ID    int      自动编号
    UID    int      学生编号
    SID    int      课程编号
    Num    int      考试成绩

     
    

    (a)求各班的总人数(1分)
    (b)求1班女生和男生的平均成绩(2分)
    (c)各班"数据结构"(课程名称)不及格的人数(2分)

     
    

    3、问题描述:(30分)
    本题用到下面三个关系表:
    CARD    借书卡。  CNO 卡号,NAME 姓名,CLASS 班级
    BOOKS    图书。    BNO 书号,BNAME 书名,AUTHOR 作者,PRICE单价,QUANTITY 库存册数
    BORROW  借书记录。 CNO 借书卡号,BNO书号,RDATE 还书日期
    备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。
    要求实现如下15个处理:
      1.找出借书超过5本的读者,输出借书卡号及所借图书册数。(2分)
    2.查询借阅了"水浒"一书的读者,输出姓名及班级。(3分)
    3.查询过期未还图书,输出借阅者(卡号)、书号及还书日期。(3分)
    4.查询书名包括"网络"关键词的图书,输出书号、书名、作者。(2分)
    5.查询现有图书中价格最高的图书,输出书名及作者。(2分)
    6.查询当前借了"计算方法"但没有借"计算方法习题集"的读者,输出其借书卡号,并按卡号降序排序输出。(4分)
    7.将"C01"班同学所借图书的还期都延长一周。(2分)
    8.从BOOKS表中删除当前无人借阅的图书记录。(2分)
    9.在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是"数据库技术及应用",就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。(4分)
    10.建立一个视图,显示"力01"班学生的借书信息(只要求显示姓名和书名)。(3分)
    11.查询当前同时借有"计算方法"和"组合数学"两本书的读者,输出其借书卡号,并按卡号升序排序输出。(3分)

     
    

     

     
    

    (七)

     
    

    1、问题描述:
    已知关系模式:
    S (SNO,SNAME)                     学生关系。SNO 为学号,SNAME 为姓名
    C (CNO,CNAME,CTEACHER)  课程关系。CNO 为课程号,CNAME 为课程名,CTEACHER 为任课教师
    SC(SNO,CNO,SCGRADE)        选课关系。SCGRADE 为成绩
    要求实现如下5个处理:
      1. 找出没有选修过“李明”老师讲授课程的所有学生姓名
      2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
      3. 列出既学过“1”号课程,又学过“2”号课程的所有学生姓名
      4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
      5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

     
    

    2、问题描述:
    为管理岗位业务培训信息,建立3个表:
    S (S#,SN,SD,SA)  S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
    C (C#,CN )       C#,CN      分别代表课程编号、课程名称
    SC ( S#,C#,G )    S#,C#,G    分别代表学号、所选修的课程编号、学习成绩

     
    

    要求实现如下5个处理:
      1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
      2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
      3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
      4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
      5. 查询选修了课程的学员人数
      6. 查询选修课程超过5门的学员学号和所属单位

     
    

     

     
    

    (八)

     
    

    Student(S#,Sname,Sage,Ssex) 学生表   
    Course(C#,Cname,T#) 课程表   
    SC(S#,C#,score) 成绩表   
    Teacher(T#,Tname) 教师表   
    问题:   
    1、查询“001”课程比“002”课程成绩高的所有学生的学号;   
      select a.S# from (select s#,score from SC where C#='001') a,(selects#,score   
      from SC where C#='002') b   
      where a.score>b.score and a.s#=b.s#;   
    2、查询平均成绩大于60分的同学的学号和平均成绩;   
        select S#,avg(score)   
        from sc   
        group by S# having avg(score) >60;   
    3、查询所有同学的学号、姓名、选课数、总成绩;   
      selectStudent.S#,Student.Sname,count(SC.C#),sum(score)   
      from Student left Outer join SC on Student.S#=SC.S#   
      group by Student.S#,Sname   
    4、查询姓“李”的老师的个数;   
      select count(distinct(Tname))   
      from Teacher   
      where Tname like '李%';   
    5、查询没学过“叶平”老师课的同学的学号、姓名;   
        select Student.S#,Student.Sname   
        from Student    
        where S# not in (select distinct( SC.S#) fromSC,Course,Teacher where  SC.C#=Course.C# and Teacher.T#=Course.T# andTeacher.Tname='叶平');   
    6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;   
      select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S#and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# andSC_2.C#='002');   
    7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;   
      select S#,Sname   
      from Student   
      where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C#and Teacher.T#=Course.T# and Teacher.Tname='叶平' groupby S# having count(SC.C#)=(select count(C#) from Course,Teacher  whereTeacher.T#=Course.T# and Tname='叶平'));   
    8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;   
      Select S#,Sname from (select Student.S#,Student.Sname,score ,(selectscore from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002')score2   
      from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2<score;   
    9、查询所有课程成绩小于60分的同学的学号、姓名;   
      select S#,Sname   
      from Student   
      where S# not in (select Student.S# from Student,SC where S.S#=SC.S# andscore>60);   
    10、查询没有学全所有课的同学的学号、姓名;   
        select Student.S#,Student.Sname   
        from Student,SC   
        where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) fromCourse);   
    11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;   
        select S#,Sname from Student,SC where Student.S#=SC.S# andC# in select C# from SC where S#='1001';   
    12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;   
        select distinct SC.S#,Sname   
        from Student,SC   
        where Student.S#=SC.S# and C# in (select C# from SC whereS#='001');   
    13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;   
        update SC set score=(selectavg(SC_2.score)   
        from SC SC_2   
        where SC_2.C#=SC.C# ) from Course,Teacher whereCourse.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');   
    14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;   
        select S# from SC where C# in (select C# from SC whereS#='1002')   
        group by S# having count(*)=(select count(*) from SC whereS#='1002');   
    15、删除学习“叶平”老师课的SC表记录;   
        Delect SC   
        from course ,Teacher    
        where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';   
    16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、2、   
        号课的平均成绩;   
        Insert SC select S#,'002',(Selectavg(score)   
        from SC where C#='002') from Student where S# not in (SelectS# from SC where C#='002');   
    17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分   
        SELECT S# as 学生ID   
            ,(SELECT score FROM SC WHERESC.S#=t.S# AND C#='004') AS 数据库   
            ,(SELECT score FROM SC WHERESC.S#=t.S# AND C#='001') AS 企业管理   
            ,(SELECT score FROM SC WHERESC.S#=t.S# AND C#='006') AS 英语   
            ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩   
        FROM SC AS t   
        GROUP BY S#   
        ORDER BY avg(t.score)    
    18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分   
        SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分   
        FROM SC L ,SC AS R   
        WHERE L.C# = R.C# and   
            L.score = (SELECTMAX(IL.score)   
                         FROM SC AS IL,Student AS IM   
                         WHERE L.C# = IL.C# and IM.S#=IL.S#   
                         GROUP BY IL.C#)   
            AND   
            R.Score = (SELECTMIN(IR.score)   
                         FROM SC AS IR   
                         WHERE R.C# = IR.C#   
                     GROUP BY IR.C#   
                       );   
    19、按各科平均成绩从低到高和及格率的百分数从高到低顺序   
        SELECT t.C# AS 课程号,max(course.Cname)AS课程名,isnull(AVG(score),0) AS 平均成绩   
            ,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数   
        FROM SC T,Course   
        where t.C#=course.C#   
        GROUP BY t.C#   
        ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60THEN 1 ELSE 0 END)/COUNT(*) DESC   
    20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004)   
        SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分   
            ,100 * SUM(CASE WHEN C# = '001' ANDscore >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END)AS 企业管理及格百分数   
            ,SUM(CASE WHEN C# = '002' THEN scoreELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分   
            ,100 * SUM(CASE WHEN C# = '002' ANDscore >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END)AS 马克思及格百分数   
            ,SUM(CASE WHEN C# = '003' THEN scoreELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分   
            ,100 * SUM(CASE WHEN C# = '003' ANDscore >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END)AS UML及格百分数   
            ,SUM(CASE WHEN C# = '004' THEN scoreELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分   
            ,100 * SUM(CASE WHEN C# = '004' ANDscore >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END)AS 数据库及格百分数   
      FROM SC   
    21、查询不同老师所教不同课程平均分从高到低显示   
      SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩   
        FROM SC AS T,Course AS C ,Teacher AS Z   
        where T.C#=C.C# and C.T#=Z.T#   
      GROUP BY C.C#   
      ORDER BY AVG(Score) DESC   
    22、查询如下课程成绩第 3 名到第 6 名的学生成绩单:企业管理(001),马克思(002),UML (003),数据库(004)   
        [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩   
        SELECT  DISTINCT top 3   
          SC.S# As 学生学号,   
            Student.Sname AS 学生姓名 ,   
          T1.score AS 企业管理,   
          T2.score AS 马克思,   
          T3.score AS UML,   
          T4.score AS 数据库,   
          ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0) as 总分   
          FROM Student,SC  LEFT JOIN SC AST1   
                         ON SC.S# = T1.S# AND T1.C# = '001'   
                LEFT JOIN SCAS T2   
                         ON SC.S# = T2.S# AND T2.C# = '002'   
                LEFT JOIN SCAS T3   
                         ON SC.S# = T3.S# AND T3.C# = '003'   
                LEFT JOIN SCAS T4   
                         ON SC.S# = T4.S# AND T4.C# = '004'   
          WHERE student.S#=SC.S# and   
          ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0)   
          NOT IN   
          (SELECT   
               DISTINCT   
                TOP 15 WITHTIES   
               ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0)

    • ISNULL(T4.score,0)   
            FROM sc   
                  LEFT JOIN scAS T1   
                           ON sc.S# = T1.S# AND T1.C# = 'k1'   
                  LEFT JOIN scAS T2   
                           ON sc.S# = T2.S# AND T2.C# = 'k2'   
                  LEFT JOIN scAS T3   
                           ON sc.S# = T3.S# AND T3.C# = 'k3'   
                  LEFT JOIN scAS T4   
                           ON sc.S# = T4.S# AND T4.C# = 'k4'   
            ORDER BY ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0) DESC);   
      23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[<60]   
          SELECT SC.C# as 课程ID, Cname as 课程名称   
              ,SUM(CASE WHEN score BETWEEN 85 AND100 THEN 1 ELSE 0 END) AS [100 - 85]   
              ,SUM(CASE WHEN score BETWEEN 70 AND85 THEN 1 ELSE 0 END) AS [85
    • 70]   
              ,SUM(CASE WHEN score BETWEEN 60 AND70 THEN 1 ELSE 0 END) AS [70
    • 60]   
              ,SUM(CASE WHEN score < 60 THEN 1ELSE 0 END) AS [60 -]   
          FROM SC,Course   
          where SC.C#=Course.C#   
          GROUP BY SC.C#,Cname;   
      24、查询学生平均成绩及其名次   
            SELECT 1 (SELECT COUNT( distinct 平均成绩)   
                   FROM (SELECT S#,AVG(score) AS 平均成绩   
                           FROM SC   
                       GROUP BY S#   
                       ) AS T1   
                  WHERE 平均成绩 > T2.平均成绩) as 名次,   
            S# as 学生学号,平均成绩   
          FROM (SELECT S#,AVG(score) 平均成绩   
                  FROMSC   
              GROUP BY S#   
              ) AS T2   
          ORDER BY 平均成绩desc;   
      25、查询各科成绩前三名的记录:(不考虑成绩并列情况)   
            SELECT t1.S# as 学生ID,t1.C#as 课程ID,Score as 分数   
            FROM SC t1   
            WHERE score IN (SELECT TOP 3score   
                   FROM SC   
                   WHERE t1.C#= C#   
                  ORDER BYscore DESC   
                   )   
            ORDER BY t1.C#;   
      26、查询每门课程被选修的学生数   
        select c#,count(S#) from sc group by C#;   
      27、查询出只选修了一门课程的全部学生的学号和姓名   
        select SC.S#,Student.Sname,count(C#) AS 选课数   
        from SC ,Student   
        where SC.S#=Student.S# group by SC.S# ,Student.Sname havingcount(C#)=1;   
      28、查询男生、女生人数   
          Select count(Ssex) as 男生人数 fromStudent group by Ssex having Ssex='男';   
          Select count(Ssex) as 女生人数 fromStudent group by Ssex having Ssex='女';   
      29、查询姓“张”的学生名单   
          SELECT Sname FROM Student WHERE Sname like '张%';   
      30、查询同名同性学生名单,并统计同名人数   
        select Sname,count(*) from Student group by Sname having count(*)>1;;   
      31、1981年出生的学生名单(注:Student表中Sage列的类型是datetime)   
          select Sname,  CONVERT(char (11),DATEPART(year,Sage))as age   
          from student   
          where CONVERT(char(11),DATEPART(year,Sage))='1981';   
      32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列   
          Select C#,Avg(score) from SC group by C# order byAvg(score),C# DESC ;   
      33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩   
          select Sname,SC.S# ,avg(score)   
          from Student,SC   
          where Student.S#=SC.S# group by SC.S#,Snamehaving    avg(score)>85;   
      34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数   
          Select Sname,isnull(score,0)   
          from Student,SC,Course   
          where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname='数据库'and score <60;   
      35、查询所有学生的选课情况;   
          SELECT SC.S#,SC.C#,Sname,Cname   
          FROM SC,Student,Course   
          where SC.S#=Student.S# and SC.C#=Course.C#;   
      36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;   
          SELECT  distinctstudent.S#,student.Sname,SC.C#,SC.score   
          FROM student,Sc   
          WHERE SC.score>=70 ANDSC.S#=student.S#;   
      37、查询不及格的课程,并按课程号从大到小排列   
          select c# from sc where scor e <60 order by C#;   
      38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;   
          select SC.S#,Student.Sname from SC,Student whereSC.S#=Student.S# and Score>80 and C#='003';   
      39、求选了课程的学生人数   
          select count(*) from sc;   
      40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩   
          select Student.Sname,score   
          from Student,SC,Course C,Teacher   
          where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T#and Teacher.Tname='叶平' and SC.score=(selectmax(score)from SC where C#=C.C# );   
      41、查询各个课程及相应的选修人数   
          select count(*) from sc group by C#;   
      42、查询不同课程成绩相同的学生的学号、课程号、学生成绩   
        select distinct  A.S#,B.score from SC A  ,SC B whereA.Score=B.Score and A.C# <>B.C# ;   
      43、查询每门功成绩最好的前两名   
          SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数   
            FROM SC t1   
            WHERE score IN (SELECT TOP 2score   
                   FROM SC   
                   WHERE t1.C#= C#   
                  ORDER BYscore DESC   
                   )   
            ORDER BY t1.C#;   
      44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列    
          select  C# as 课程号,count(*) as 人数   
          from  sc    
          group  by  C#   
          order  by  count(*)desc,c#    
      45、检索至少选修两门课程的学生学号   
          select  S#    
          from  sc    
          group  by  s#   
          having  count(*)  >  = 2   
      46、查询全部学生都选修的课程的课程号和课程名   
          select  C#,Cname    
          from  Course    
          where  C#  in  (select  c# from  sc group  by  c#)    
      47、查询没学过“叶平”老师讲授的任一门课程的学生姓名   
          select Sname from Student where S# not in (select S# fromCourse,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平');   
      48、查询两门以上不及格课程的同学的学号及其平均成绩   
          select S#,avg(isnull(score,0)) from SC where S# in (selectS# from SC where score <60 group by S# having count(*)>2)group byS#;   
      49、检索“004”课程分数小于60,按分数降序排列的同学学号   
          select S# from SC where C#='004'and score <60 order byscore desc;   
      50、删除“002”同学的“001”课程的成绩   
      delete from Sc where S#='001'and C#='001';   
      问题描述:  
      本题用到下面三个关系表:  
      CARD     借书卡。   CNO 卡号,NAME  姓名,CLASS 班级  
      BOOKS    图书。     BNO书号,BNAME 书名,AUTHOR 作者,PRICE 单价,QUANTITY 库存册数   
      BORROW   借书记录。 CNO 借书卡号,BNO 书号,RDATE 还书日期  
      备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。  
      要求实现如下15个处理:  
        1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束。  
        2. 找出借书超过5本的读者,输出借书卡号及所借图书册数。  
        3. 查询借阅了"水浒"一书的读者,输出姓名及班级。  
        4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期。  
        5. 查询书名包括"网络"关键词的图书,输出书号、书名、作者。  
        6. 查询现有图书中价格最高的图书,输出书名及作者。  
        7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者,输出其借书卡号,并按卡号降序排序输出。  
        8. 将"C01"班同学所借图书的还期都延长一周。  
        9. 从BOOKS表中删除当前无人借阅的图书记录。  
        10.如果经常按书名查询图书信息,请建立合适的索引。  
        11.在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是"数据库技术及应用",就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。  
        12.建立一个视图,显示"力01"班学生的借书信息(只要求显示姓名和书名)。  
        13.查询当前同时借有"计算方法"和"组合数学"两本书的读者,输出其借书卡号,并按卡号升序排序输出。  
        14.假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句。  
        15.对CARD表做如下修改:  
          a. 将NAME最大列宽增加到10个字符(假定原为6个字符)。  
          b. 为该表增加1列NAME(系名),可变长,最大20个字符。  
    1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束  
      --实现代码:  
      CREATE TABLE BORROW(  
          CNO int FOREIGN KEY REFERENCES CARD(CNO),  
          BNO int FOREIGN KEY REFERENCES BOOKS(BNO),  
          RDATE datetime,  
          PRIMARY KEY(CNO,BNO))   
    2. 找出借书超过5本的读者,输出借书卡号及所借图书册数  
      --实现代码:  
      SELECT CNO,借图书册数=COUNT(*)  
      FROM BORROW  
      GROUP BY CNO  
      HAVING COUNT(*)>5 
    3. 查询借阅了"水浒"一书的读者,输出姓名及班级  
      --实现代码:  
      SELECT * FROM CARD c  
      WHERE EXISTS(  
          SELECT * FROM BORROW a,BOOKS b   
          WHERE a.BNO=b.BNO  
              AND b.BNAME=N'水浒' 
              AND a.CNO=c.CNO)   
    4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期  
      --实现代码:  
      SELECT * FROM BORROW   
      WHERE RDATE<GETDATE()   
    5. 查询书名包括"网络"关键词的图书,输出书号、书名、作者  
      --实现代码:  
      SELECT BNO,BNAME,AUTHOR FROM BOOKS  
      WHERE BNAME LIKE N'%网络%'   
    6. 查询现有图书中价格最高的图书,输出书名及作者  
      --实现代码:  
      SELECT BNO,BNAME,AUTHOR FROM BOOKS  
      WHERE PRICE=(  
          SELECT MAX(PRICE) FROM BOOKS)   
      7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者,输出其借书卡号,并按卡号降序排序输出  
      --实现代码:  
      SELECT a.CNO  
      FROM BORROW a,BOOKS b  
      WHERE a.BNO=b.BNO AND b.BNAME=N'计算方法' 
          AND NOT EXISTS(  
              SELECT * FROM BORROW aa,BOOKSbb  
      操作练习,第四模块MySQL50题作业。        WHERE aa.BNO=bb.BNO  
                  ANDbb.BNAME=N'计算方法习题集' 
                  ANDaa.CNO=a.CNO)  
      ORDER BY a.CNO DESC   
    7. 将"C01"班同学所借图书的还期都延长一周  
      --实现代码:  
      UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)  
      FROM CARD a,BORROW b  
      WHERE a.CNO=b.CNO  
          AND a.CLASS=N'C01'   
    8. 从BOOKS表中删除当前无人借阅的图书记录  
      --实现代码:  
      DELETE A FROM BOOKS a  
      WHERE NOT EXISTS(  
          SELECT * FROM BORROW  
          WHERE BNO=a.BNO)   
    9. 如果经常按书名查询图书信息,请建立合适的索引  
      --实现代码:  
      CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)  
      11. 在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是"数据库技术及应用",就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)  
      --实现代码:  
      CREATE TRIGGER TR_SAVE ON BORROW  
      FOR INSERT,UPDATE  
      AS  
      IF @@ROWCOUNT>0 
      INSERT BORROW_SAVE SELECT i.*  
      FROM INSERTED i,BOOKS b  
      WHERE i.BNO=b.BNO  
          AND b.BNAME=N'数据库技术及应用'   
    10. 建立一个视图,显示"力01"班学生的借书信息(只要求显示姓名和书名)  
      --实现代码:  
      CREATE VIEW V_VIEW  
      AS  
      SELECT a.NAME,b.BNAME  
      FROM BORROW ab,CARD a,BOOKS b  
      WHERE ab.CNO=a.CNO  
          AND ab.BNO=b.BNO  
          AND a.CLASS=N'力01' 
      13. 查询当前同时借有"计算方法"和"组合数学"两本书的读者,输出其借书卡号,并按卡号升序排序输出  
      --实现代码:  
      SELECT a.CNO  
      FROM BORROW a,BOOKS b  
      WHERE a.BNO=b.BNO  
          AND b.BNAME IN(N'计算方法',N'组合数学')  
      GROUP BY a.CNO  
      HAVING COUNT(*)=2 
      ORDER BY a.CNO DESC   
    11. 假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句  
      --实现代码:  
      ALTER TABLE BOOKS ADD PRIMARY KEY(BNO)   
      15.1 将NAME最大列宽增加到10个字符(假定原为6个字符)  
      --实现代码:  
      ALTER TABLE CARD ALTER COLUMN NAME varchar(10)   
      15.2 为该表增加1列NAME(系名),可变长,最大20个字符  
      --实现代码:  
      ALTER TABLE CARD ADD 系名 varchar(20)  
      问题描述:  
      为管理岗位业务培训信息,建立3个表:  
      S (S#,SN,SD,SA)   S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄  
      C (C#,CN )       C#,CN       分别代表课程编号、课程名称  
      SC ( S#,C#,G )    S#,C#,G     分别代表学号、所选修的课程编号、学习成绩  
      要求实现如下5个处理:  
        1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名  
        2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位  
        3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位  
        4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位  
        5. 查询选修了课程的学员人数  
        6. 查询选修课程超过5门的学员学号和所属单位  
    12. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名   
      --实现代码:  
      SELECT SN,SD FROM S  
      WHERE [S#] IN(  
          SELECT [S#] FROM C,SC  
          WHERE C.[C#]=SC.[C#]  
              AND CN=N'税收基础')  
    13. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位  
      操作练习,第四模块MySQL50题作业。--实现代码:  
      SELECT S.SN,S.SD FROM S,SC  
      WHERE S.[S#]=SC.[S#]  
          AND SC.[C#]='C2' 
    14. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位  
      --实现代码:  
      SELECT SN,SD FROM S  
      WHERE [S#] NOT IN(  
          SELECT [S#] FROM SC   
          WHERE [C#]='C5')  
    15. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位  
      --实现代码:  
      SELECT SN,SD FROM S  
      WHERE [S#] IN(  
          SELECT [S#] FROM SC   
              RIGHT JOIN C ONSC.[C#]=C.[C#]  
          GROUP BY [S#]  
          HAVING COUNT(*)=COUNT(DISTINCT [S#]))  
    16. 查询选修了课程的学员人数  
      --实现代码:  
      SELECT 学员人数=COUNT(DISTINCT [S#]) FROM SC  
    17. 查询选修课程超过5门的学员学号和所属单位  
      --实现代码:  
      SELECT SN,SD FROM S  
      WHERE [S#] IN(  
          SELECT [S#] FROM SC   
          GROUP BY [S#]  
          HAVING COUNT(DISTINCT [C#])>5)  
      if not object_id('cj')is null   
         drop table cj  
      go  
      create table cj(stuName nvarchar(10),KCM nvarchar(10),cjnumeric(5,2))   
      insert into cj select '张三','语文',98   
      union select '李四','语文',89   
      union select '王五','语文',67   
      union select '周攻','语文',56   
      union select '张三','数学',89 
      union select '李四','数学',78   
      union select '王五','数学',90   
      union select '周攻','数学',87   
      方法一:  
      select stuname from  
          (select stuName,kcm,(select count(*) from cj wherestuname!=a.stuname and kcm=a.kcm and cj>a.cj) cnt from cj a) x  
          group by stuname having max(cnt)<=1 
      go  
      方法二:  
      SELECT stuname FROM cj1 a    
      where cj IN(SELECT TOP 2 cj FROM cj1 WHERE kcm=a.kcm ORDER BY cj desc)  
      GROUP BY stuname HAVING(count(1)>1)  
      方法三:  
      select distinct stuname from cj a  
          where not exists(select kcm from cj b wherea.stuname=stuname  
                     and (select count(*) from cj where kcm=b.kcm and stuname!=a.stuname andcj>b.cj)>1)

    本文由新葡亰496net发布于网络数据库,转载请注明出处:操作练习,第四模块MySQL50题作业

    关键词: