www.9778.com:MSSQL Server面试题整合

(select name from student where name like 张%

  1. 学生表student【创建表语句】create table student(sid int primary key
    auto_increment,sname char(10),gender enum(‘男’,’女’) not null,class_id
    int);
    【插入记录语句】insert into student
    values(1,’乔丹’,’女’,1),(2,’艾弗森’,’女’,1),(3,’科比’,’男’,2),(4,’葫芦娃’,’男’,3),(5,’张三丰’,’男’,5),(6,’洞房不败’,’男’,4),(7,’樱木花道’,’男’,2),(8,’松岛菜菜子’,’女’,3),(9,’洞房不败’,’女’,5); 
  2. 老师表teacher【创建表语句】create table teacher(tid int primary key
    auto_increment,tname char(10));
    【插入记录语句】Insert into teacher
    values(1,’张三’),(2,’李四’),(3,’王五’),(4,’萧峰’),(5,’一休哥’),(6,’诸葛’),(7,’李四’); 
  3. 课程表course【创建表语句】create table course(cid int primary key
    auto_increment,cname char(10),teacher_id int);
    【插入记录语句】insert into course
    values(1,’生物’,1),(2,’体育’,1),(3,’物理’,2),(4,’数学’,3),(5,’语文’,4),(6,’英语’,2),(7,’土遁?沙地送葬’,5),(8,’夏日喂蚊子大法’,3),(9,’麻将牌九扑克千术’,6); 
  4. 成绩表score【创建表语句】create table score(sid int primary key
    auto_increment,student_id int,course_id int,score int);
    【插入记录语句】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); 
  5. 年级表class_grade【创建表语句】create table class_grade(gid int
    primary key auto_increment,gname char(10));
    【插入记录语句】insert class_grade
    values(1,’一年级’),(2,’二年级’),(3,’三年级’),(4,’四年级’),(5,’五年级’); 
  6. 班级任职表teach2cls【创建表语句】create table teach2cls(tcid int
    primary key auto_increment,tid int,cid int);
    【插入记录语句】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,
    snamefrom student where sid
    in(select student_idfrom scorewhere student_id
    in(select student_idfrom scorewhere 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 studentwhere sid
    in (select t1.student_id from (select student_id from scorewhere course_id
    = (select cid from course where cname = ‘生物’) and score >= 60) as
    t1inner join (select student_id from score where course_id = (select
    cid from course where cname = ‘物理’) and score >= 60) as
    t2on 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.gidgroup by class.grade_idorder
    by count(class.cid) desclimit 3;
    5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩select stu.sid, stu.sname, avg(score)
    as 平均成绩from student as stu inner join score as scoon stu.sid =
    sco.student_idgroup by stu.sidhaving avg(score)
    = (select avg(score) from score group by student_idorder by avg(score)
    desclimit 1) or avg(score) = (select avg(score) from score group
    by student_idorder by avg(score) asclimit 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 t1inner join student as s on t1.cid =
    s.class_idgroup 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_idgroup 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 t1inner
    joincourse as t2 on t1.course_id = t2.cidgroup by t2.cidhaving score in
    (max(score),min(score));
    9、查询姓“李”的老师的个数和所带班级数;select count(te.tid) as
    姓李老师个数,count(tc.cid) as 所带班级数from teacher as te inner join
    teach2cls as tcon te.tid = tc.tidwhere 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 gon
    c.grade_id = g.gidgroup by c.grade_idhaving count(c.cid)<5;
    11、查询班级信息,包括班级id、班级名称、年级、年级级别(12为低年级,34为中年级,56为高年级),示例结果如下;select cid
    as 班级id,caption as 班级名称,gname as 年级,casewhen 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 gon 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_idwhere sco.course_id
    in (select c.cidfrom teacher as t inner join course as con t.tid =
    c.teacher_idwhere t.tname = ‘张三’)group
    by stu.sidhaving count(sco.course_id) >= 2;
    13、查询教授课程超过2门的老师的id和姓名;selecttid as id,tname as
    姓名from teacher as t inner join course as c on t.tid =
    c.teacher_idgroup by c.teacher_idhaving count(c.cid) >= 2;
    14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名;select sid as
    学号,sname as 姓名from studentwhere sid
    in (select student_id from scorewhere student_id
    in (select student_id from scorewhere course_id = 1)and course_id =
    2);
    15、查询没有带过高年级的老师id和姓名;select tid as 老师id,tname as
    姓名from teacherwhere tid not in (select tc.tidfrom class as c inner
    join teach2cls as tc on c.cid = tc.cidwhere c.grade_id in (5,6));
    16、查询学过“张三”老师所教的所有课的同学的学号、姓名;select distinctstu.sid
    as 学号,stu.sname as 姓名from student as stu inner join score as sco on
    stu.sid = sco.student_idwhere sco.course_id
    in (select c.cid from teacher as t inner join course as c on t.tid =
    c.teacher_idwhere t.tname = “张三”);
    17、查询带过超过2个班级的老师的id和姓名;select tid as id,tname as
    姓名from teacherwhere tid in (select tid from teach2clsgroup
    by tidhaving count(cid) >= 2);
    18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名;select sid
    as 学号,sname as 姓名from studentwhere sid
    in(select t1.student_idfrom (select * from scorewhere course_id = 1)
    as t1inner join (select * from score where course_id = 2) as
    t2on t1.student_id = t2.student_idwhere t1.score > t2.score);
    19、查询所带班级数最多的老师id和姓名;select tid as id,tname as
    姓名from teacher where tid in (select tidfrom teach2clsgroup
    by tidhaving count(cid) = (select count(cid)from teach2clsgroup
    by tidorder by count(cid) desclimit 1));
    20、查询有课程成绩小于60分的同学的学号、姓名;select sid as 学号,sname
    as 姓名from studentwhere sid
    in (select student_idfrom score where score < 60);
    21、查询没有学全所有课的同学的学号、姓名;select sid as 学号,sname as
    姓名from studentwhere sid in (select student_idfrom scoregroup
    by student_idhaving count(course_id) != (select count(cid) from
    course));
    22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名;select sid
    as 学号,sname as 姓名from studentwhere sid
    in (select student_idfrom scorewhere course_id
    in (select course_id from scorewhere student_id = 1));
    23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名;select sid
    as 学号,sname as 姓名from studentwhere sid
    in (select student_idfrom scorewhere course_id
    in (select course_id from scorewhere student_id = 1) and student_id
    != 1);
    24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名;select sid
    as 学号,sname as 姓名from studentwhere sid
    in (select student_idfrom scorewhere student_id != 2group
    by student_idhaving group_concat(course_id order by course_id asc)
    = (select group_concat(course_id order by course_id
    asc)from scorewhere student_id = 2group by student_id));
    25、删除学习“张三”老师课的score表记录;delete from scorewhere course_id
    in (select c.cid from teacher as t inner join course as con t.tid =
    c.teacher_idwhere t.tname = ‘张三’);
    26、向score表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“2”课程的同学学号;②插入“2”号课程的平均成绩;【插入第一条】insert
    into score(student_id, course_id,
    score) values((select sidfrom studentwhere sid not
    in(select s.student_idfrom score as swhere s.course_id = 2)order
    by sid desclimit 0,1),2,(select avg(s.score)from score as
    swhere s.course_id = 2));【插入第二条】insert into score(student_id,
    course_id, score) values((select sidfrom studentwhere sid not
    in(select s.student_idfrom score as swhere s.course_id = 2)order
    by sid desclimit 1,1),2,(select avg(s.score)from score as
    swhere 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 con s.course_id = c.cid) as t1right join student
    as t2on t1.student_id = t2.sidgroup by t2.sidorder 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 con s.course_id = c.cid) as t1right join student
    as t2on t1.student_id = t2.sidgroup by t2.sidorder by avg(t1.score)
    asc;
    28、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;select course_id
    as 课程ID,max(score) as 最高分,min(score) as 最低分from scoregroup
    by course_id;
    29、按各科平均成绩从低到高和及格率的百分数从高到低顺序;【这里优先按平均成绩从低到高排序,若遇到平均成绩相同的则按及格率百分数从高到低排序】select course_www.9778.com:MSSQL Server面试题整合。id
    as 课程ID,avg(score) as 平均成绩,concat(100*count(case when
    score>=60 then 1 else null end)/count(score),”%”) as
    及格率from scoregroup by course_idorder 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 con t.tid = c.teacher_id) as
    t1 inner join score as t2on t1.cid = t2.course_idgroup
    by t2.course_idorder by avg(t2.score) desc;
    31、查询各科成绩前三名的记录(不考虑成绩并列情况)【本题与44题类似,不会做,于是百度了下”如何在mysql中查询每个分组的前几名”,参照其中的一个方法,写出了答案】【注:这里仍然是按照score表默认的排序,即sid的排序】select*from scorewhere(selectcount(*)from score
    as swheres.course_id = score.course_idands.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_idgroup by c.cid;
    33、查询选修了2门以上课程的全部学生的学号和姓名;select sid as
    学号,sname as 姓名from studentwhere sid
    in (select student_id from scoregroup
    by student_idhaving count(course_id) >= 2);
    34、查询男生、女生的人数,按倒序排列;select gender,
    count(sid)from studentgroup by genderorder by count(sid) desc;
    35、查询姓“张”的学生名单;【查法1——正则】select sname from studentwhere sname
    regexp “^张.*”;
    【查法2——like】select snamefrom studentwhere sname like “张%”;
    36、查询同名同姓学生名单,并统计同名人数;select sname as
    姓名,count(sid) as 同名人数 from studentgroup by snamehaving count(sid)
    > 1;
    37、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;select avg(score),course_idfrom scoregroup
    by course_idorder by avg(score) asc, course_id desc;
    38、查询课程名称为“数学”,且分数低于60的学生姓名和分数;select stu.sname
    as 学生姓名,sco.score as 分数from student as stu inner join score as
    scoon stu.sid = sco.student_idwhere sco.course_id
    = (select cid from course where cname = ‘数学’)and sco.score < 60;
    39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名;select sid
    as 学号,sname as 姓名from studentwhere sid
    in (select student_id from scorewhere course_id = 3 and score >=
    80);
    40、求选修了课程的学生人数select count(1) as
    学生人数from(select distinct student_idfrom score) as t1;
    41、查询选修“王五”老师所授课程的学生中,成绩最高和最低的学生姓名及其成绩;select stu.sname
    as 学生姓名,sco.score as 成绩from student as stu inner join score as
    scoon stu.sid = sco.student_idwhere score
    in ((select max(score)from scorewhere course_id
    in (select c.cidfrom teacher as t inner join course as con t.tid =
    c.teacher_id where t.tname =
    ‘王五’)), (select min(score)from scorewhere course_id
    in (select c.cidfrom teacher as t inner join course as con 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_idgroup by c.cid;
    43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩;select student_id
    as 学号,course_id as 课程号,score as 学生成绩from scoregroup
    by scorehaving count(student_id) > 1;
    44、查询每门课程成绩最好的前两名学生id和姓名;【注:这里指定了前两名,所以若出现多名同分的学生也只取倒序排的默认前2名】【与31题类似…不会写,于是百度了下”如何在mysql中查询每个分组的前几名”,参照其中一种比较高端且高效的自定义变量的方法,写出了答案】
    set @num := 0, @cname := ”;selectt2.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 scoon stu.sid =
    sco.student_id) as t1right joincourse as t2on t1.course_id =
    t2.cidgroup byt2.cid, t1.score, t1.snamehaving排名 <= 2;

事务 Transaction 触发器 TRIGGER 继续 continue 唯一 unqiue

二、案例一

学生表student:

+—–+——–+—–+——-+————+————–+

| id  | name  | sex | birth | department | address      |

+—–+——–+—–+——-+————+————–+

| 901 | 张老大 | 男  |  1985 | 计算机系  | 北京市海淀区 |

| 902 | 张老二 | 男  |  1986 | 中文系    | 北京市昌平区 |

| 903 | 张三  | 女  |  1990 | 中文系    | 湖南省永州市 |

| 904 | 李四  | 男  |  1990 | 英语系    | 辽宁省阜新市 |

| 905 | 王五  | 女  |  1991 | 英语系    | 福建省厦门市 |

| 906 | 王六  | 男  |  1988 | 计算机系  | 湖南省衡阳市 |

+—–+——–+—–+——-+————+————–+

成绩表score:

+—-+——–+——–+——-+

| id | stu_id | c_name | grade |

+—-+——–+——–+——-+

|  1 |    901 | 计算机 |    98 |

|  2 |    901 | 英语  |    80 |

|  3 |    902 | 计算机 |    65 |

|  4 |    902 | 中文  |    88 |

|  5 |    903 | 中文  |    95 |

|  6 |    904 | 计算机 |    70 |

|  7 |    904 | 英语  |    92 |

|  8 |    905 | 英语  |    94 |

|  9 |    906 | 计算机 |    90 |

| 10 |    906 | 英语  |    85 |

+—-+——–+——–+——-+

1、男同学的考试科目

select distinct(c_name) from score s1,student s2 where s2.id=s1.stu_id
and sex=’男‘;

select distinct(c_name) from score where stu_id in (select id from
student where sex=’男’);

2、姓张同学的考试科目

select distinct(c_name) from score where stu_id in (select id from
student where name like ‘张%’);

select c_name from score,student where score.stu_id=student.id and
name like ‘张%’;

3、同时学习英语和计算机的学生信息

select * from student where id in (select stu_id from score where
c_name=’计算机’  and stu_id in (select stu_id from score where
c_name=’英语’));

select s1.* from student s1,score s2,score s3 where s1.id=s2.stu_id
and s1.id=s3.stu_id and s2.c_name=’计算机’ and s3.c_name=’英语’;

练习:

1、女同学的考试科目

2、同时学习中文和计算机的学生信息;

3、姓王的同学并且有一科以上成绩大于80分的学生信息;

4、查询李四的考试科目(c_name)和考试成绩(grade)

select c_name,grade from score,student where student.id=score.stu_id
and name=’李四’;

5、查询计算机成绩低于95的学生信息

select student.* from score,student where student.id=score.stu_id and
c_name=’计算机’ and grade<95;

6、查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

select name,department,c_name,grade from student,score where
student.id=score.stu_id and (name like ‘王%’ or name like ‘张%’ )

练习:

1、查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

2、成绩大于80分的女同学的信息

3、查询出女生成绩最低的人的姓名;

having min(score)=60)

 

**9、说出以下聚合数的含义:avg ,sum ,max ,min , count ,count()

案例二

如下,有三张表:

学生表student:

+—–+——-+—–+—–+

| SNO | SNAME | AGE | SEX |

+—–+——-+—–+—–+

|  1 | 李强  |  23 | 男  |

|  2 | 刘丽  |  22 | 女  |

|  5 | 张友  |  22 | 男  |

+—–+——-+—–+—–+

课程表course:

+—–+————+———+

| CNO | CNAME      | TEACHER |

+—–+————+———+

| k1  | c语言      | 王华    |

| k5  | 数据库原理 | 程军    |

| k8  | 编译原理  | 程军    |

+—–+————+———+

成绩表sc:

+—–+—–+——-+

| SNO | CNO | SCORE |

+—–+—–+——-+

|  1 | k1  |    83 |

|  2 | k1  |    85 |

|  5 | k1  |    92 |

|  2 | k5  |    90 |

|  5 | k5  |    84 |

|  5 | k8  |    80 |

+—–+—–+——-+

1、检索"李强"同学不学课程的课程号(CNO);

select cno from course where cno not in (select cno from

sc,student where sname=’李强’ andstudent.sno=sc.sno) ;

2、查询“李强”同学所有课程的成绩:

select score from student,sc where

student.sname=’李强’ and student.sno=sc.sno;

3、查询课程名为“C语言”的平均成绩

select avg(score) from sc,course where cname=’c语言’ and
course.cno=sc.cno;

练习:

1、求选修K1 课程的学生的平均年龄;

select avg(age)from student,sc where student.sno=sc.sno and cno=’k1′;

2、求王老师所授课程的每门课程的学生平均成绩。

select avg(score) from sc,course where teacherlike ‘王%’
andcourse.cno=sc.cno group by sc.cno;

where a1.name=team.name )

  1. 学生表student

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

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

查成这样一个结果

案例三

有四张表格:

学生表student:

+—–+——-+———————+——+

| sid | sname | sage                | ssex |

+—–+——-+———————+——+

| 01  | 赵雷  | 1990-01-01 00:00:00 | 男  |

| 02  | 钱电  | 1990-12-21 00:00:00 | 男  |

| 03  | 孙风  | 1990-05-06 00:00:00 | 男  |

| 04  | 李云  | 1990-08-06 00:00:00 | 男  |

| 05  | 周梅  | 1991-12-01 00:00:00 | 女  |

| 06  | 吴兰  | 1992-03-01 00:00:00 | 女  |

| 07  | 郑竹  | 1898-07-01 00:00:00 | 女  |

| 08  | 王菊  | 1990-01-20 00:00:00 | 女  |

+—–+——-+———————+——+

教室表teacher:

+—–+——-+

| tid | tname |

+—–+——-+

| 01  | 张三  |

| 02  | 李四  |

| 03  | 王五  |

| 04  | 赵六  |

+—–+——-+

课程表course:

+—–+——-+—–+

| cid | cname | tid |

+—–+——-+—–+

| 01  | 语文  | 02  |

| 02  | 数学  | 01  |

| 03  | 英语  | 03  |

| 04  | 物理  | 04  |

+—–+——-+—–+

成绩表score:

+—–+—–+——-+

| sid | cid | score |

+—–+—–+——-+

| 01  | 01  |    80 |

| 01  | 02  |    90 |

| 01  | 03  |    99 |

| 02  | 01  |    70 |

| 02  | 02  |    60 |

| 02  | 02  |    80 |

| 03  | 01  |    80 |

| 03  | 02  |    80 |

| 03  | 03  |    80 |

| 04  | 01  |    50 |

| 04  | 02  |    30 |

| 04  | 03  |    20 |

| 05  | 01  |    76 |

| 05  | 02  |    87 |

| 06  | 01  |    31 |

| 06  | 03  |    34 |

| 07  | 02  |    89 |

| 07  | 03  |    98 |

+—–+—–+——-+

题目:

1、查询”01″课程比”02″课程成绩高的学生的信息及课程分数

1.1、查询同时存在”01″课程和”02″课程的情况

select a.* , b.score,c.score from student a , score b , score c where
a.sid = b.sid and a.sid = c.sid and b.cid = ’01’ and c.cid = ’02’ and
b.score > c.score

1.2、查询同时存在”01″课程和”02″课程的情况和存在”01″课程但可能不存在”02″课程
的情况(不存在时显示为 null)

select a.* , b.score ,c.score from student a left join score b on a.sid
= b.sid and b.cid = ’01’ left join score c on a.sid = c.sid and c.cid =
’02’ where b.score>IFNULL(c.score,0)

2、查询”01″课程比”02″课程成绩低的学生的信息及课程分数

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select a.sid , a.sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , score b where a.sid = b.sid group by a.sid , a.sname
having cast(avg(b.score) as decimal(18,2)) >= 60 order by a.sid

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(注意成绩为null的学生,使用ifnull()函数)

select a.sid , a.Sname , IFNULL(cast(avg(b.score) as decimal(18,2)),0)
avg_score from Student a left join score b on a.sid = b.sid group by
a.sid , a.sname having ifnull(cast(avg(b.score) as decimal(18,2)),0)
< 60 order by a.Sid

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select a.sid AS 学生编号, a.Sname as 学生姓名, count(b.cid) 选课总数,
sum(score) 所有课程的总成绩 from Student a left join score b on a.sid =
b.sid group by a.sid,a.Sname order by a.sid

查询出该俱乐部里男性会员和女性会员的总数

一、表关系

Year Salary 

一、多表查询

–编写多表查询语句的一般过程

–(1)、分析句子要涉及到哪些表

–(2)、对应的表中要查询哪些关联字段

–(3)、确定连接条件或筛选条件

–(4)、写成完整的SQL查询语句

1、多表查询指使用SQL查询时不只是一张表的查询,要点:

① 多个表之间必须建立连接关系

② 表别名的用法

③ 如果from后面使用了表的别名 ,则select后和where后必须用别名代替

3。表内容如下 —————————– ID LogTime 1 2008/10/10
10:00:00 1 2008/10/10 10:03:00 1 2008/10/10 10:09:00 2 2008/10/10
10:10:00 2 2008/10/10 10:11:00 …… —————————–

 

www.9778.com,①:insert into b(a,b,c) select d,e,f from
a

(select s_name from score

  1. 老师表teacher

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

year       month     amount

(四部分) 表名:club

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

2000 1000 

表名:team ID(number型) Name(varchar2型) 1 a 2 b 3 b 4 a 5 c 6 c
要求:执行一个删除语句,当Name列上有相同时,只保留ID这列上值小的
例如:删除后的结果应如下: ID(number型) Name(varchar2型) 1 a 2 b 5 c
请写出SQL语句。

一、表关系请创建如下表,并创建相关约束1. 班级表class【创建表语句】create
table class(cid int primary key auto_increment,caption
char(10),grade_id int);
【插入记录语句】insert into class
values(1,’少一一班’,1),(2,’少二一班’,2),(3,’少三二班’,3),(4,’少四一班’,4),(5,’少五三班’,5); 

①:select * from A limit 30,10

select min(id) from team group by name)

 

王五 英语 90

表名:高考信息表 准考证号 科目 成绩 2006001 语文 119 2006001 数学 108
2006002 物理 142 2006001 化学 136 2006001 物理 127 2006002 数学 149
2006002 英语 110 2006002 语文 105 2006001 英语 98 2006002 化学 129 ……

  1. 班级表class

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

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

select * from ppp

核心提示:请问各位高手,如何查询登陆时间间隔不超过5分钟的所有记录

45、检索至少选修两门课程的学生学号;select sid as
学号from studentwhere sid in (select student_id from scoregroup
by student_idhaving count(course_id) >= 2);
46、查询没有学生选修的课程的课程号和课程名;select cid as 课程号,cname
as 课程名from coursewhere cid not in (select distinct
course_idfrom score);
47、查询没带过任何班级的老师id和姓名;selecttid as 老师id,tname as
姓名from teacherwhere tid not in (select distinct tidfrom teach2cls);
48、查询有两门以上课程超过80分的学生id及其平均成绩;select student_id
as 学生id,avg(score) as 平均成绩from scorewhere student_id
in (select student_idfrom scorewhere score >= 80group
by student_idhaving count(course_id) >= 2)group by student_id;
49、检索“3”课程分数小于60,按分数降序排列的同学学号;select
distinctstudent_id as 学号from scorewhere course_id = 3 and score <
60order by score desc;
50、删除编号为“2”的同学的“1”课程的成绩;delete from scorewhere student_id
= 2 and course_id = 1;
51、查询同时选修了物理课和生物课的学生id和姓名;select sid as
学生id,sname as 姓名from studentwhere sid
in (select student_idfrom scorewhere course_id = (select cid from
course where cname = ‘生物’))and sid
in (select student_idfrom scorewhere course_id
= (select cid from course where cname = ‘物理’));

    where num in (select num from ppp group by num having count(num) =
4);

group by s_name

(case   when  数学>=80 then ‘优秀’ when
 数学>=60 then ‘及格’ else ‘不及格’) AS 数学,

where s_name not in

(select amount  from aaa m where month=3 amd
m.year=aaa.year) as m3,

where score60)

(select amount  from aaa m where month=2 amd
m.year=aaa.year) as m2,

表名:商品表 名称 产地 进价 苹果 烟台 2.5 苹果 云南 1.9 苹果 四川 3 西瓜
江西 1.5 西瓜 北京 2.4 ……

select top 10 *  from tablename order by
newid()—SQL SEVER

select 准考证号 from 高考信息表 where (数学+语文+英语+物理+化学) 600

MIN:求最小值

(select s_name from score

3. 面试题:怎么把这样一个表儿(aaa)

几道经典的SQL笔试题目

3               2005001    张三       0001              数学         69

(

Answer:

delete from team where id not in

①:select year ,

给出成绩全部合格的学生信息,注:分数在60以上评为合格

自然,不使用 max,找出表 ppp 中 num 最大的数:

delete from team where id not in

select  id,avg(score) from stu group by id
having avg(score) >60

(

8) 查询学生表的数据,查询所有信息,列出前25%的记录

给出高考总分在600以上的学生准考证号

2. 选择表 ppp 中的重复记录

id gender age 67 M 19 68 F 30 69 F 27 70 F 16 71 M 32 ……

8)select top 25 percent * from stu 

表名:student

Answer:

select * from score


select * from student where name in

MAX:求最大值

select min(a1.id) from team a1

Answer:

表名:高考信息表 准考证号 数学 语文 英语 物理 化学 2006001 108 119 98
127 136 2006002 149 105 110 142 129 ……

1               2005001    张三       0001              数学         69

group by name having avg(score) 75)

Answer:

name course score 张青 语文 72 王华 数学 72 张华 英语 81 张青 物理 67
李立 化学 98 张燕 物理 70 张青 化学 76

Answer:

select gender,count(id) from club group by gender

from aaa 

请问各位高手,如何查询登陆时间间隔不超过5分钟的所有记录.

***8、查询表A中存在ID重复三次以上的记录,完整的查询语句如下

select 准考证号 from 高考信息表 group by 准考证号 having sum(成绩) 600

王五 数学 100

表名:成绩表 姓名 课程 分数 张三 语文 81 张三 数学 75 李四 语文 56 李四
数学 90 王五 语文 81 王五 数学 100 王五 英语 49 ……

Year Salary 

select 名称 from 商品表 group by 名称 having avg(进价) 2

6、编写SQL语句

查询出“张”姓学生中平均成绩大于75分的学生信息

1991          3            1.3

给出高考总分在600以上的学生准考证号

7)select 姓名 ,学号 from stu where 学历=’大专’
 and 年龄<22

或者: select * from score where s_name in

1. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名

给出平均进价在2元以下的商品名称

②:select b.id,b.name,(select ID,Name,ROW_NUMBER() over(partition by ID) ‘排名’from A) b
where b.排名>3—仅SQL SEVER

9)select 姓名,性别 from stu order by  年龄
desc

1991          4            1.4

SUM:求和

1992          3            2.3


4) 向学生表添加如下信息:

删除除了自动编号不同, 其他都相同的学生冗余信息

1991          2            1.2

5) 修改学生表的数据,将电话号码以11开头的学员的学历改为“大专”

6) 删除学生表的数据,姓名以C开头,性别为‘男’的记录删除

2002 6000 

from table

1 A 22 男 123456 小学

①delete from table where 自动编号 not in (select
nin(自动编号) from table group by  学号, 姓名
,课程编号,课程名称,分数)

1992          4            2.4

李四 数学 90

王五 语文 81

2. 学生表 如下:

select num from ppp where num <= all(select num from ppp);

(case   when  英语>=80 then ‘优秀’ when
 英语>=60 then ‘及格’ else ‘不及格’) AS 英语

自动编号    学号          姓名    课程编号      课程名称   分数

查询表中出现 四 次的记录,group by having

1992          1            2.1

10) 按照性别分组查询所有的平均年龄

***5.有一张表,里面有3个字段:语文,数学,英语。其中有3条记录分别表示语文70分,数学80分,英语58分,请用一条sql语句查询出这三条记录并按以下条件显示出来(并写出您的思路): 

2003 4000

2)alter table stu add 学历 varchar(5)

2 B 21 男 119 中学

group by year

3)alter table stu drop column
家庭住址

想得到如下形式的查询结果 

3) 修改学生表的结构,删除一列信息,家庭住址

Answer:

只返回单独的一条记录

1991 1.1 1.2 1.3 1.4

2000 1000 

7、查询A(ID,Name)表中第31至40条记录,ID作为主键可能是不是连续增长的列,完整的查询语句如下:

1992 2.1 2.2 2.3 2.4

2003 10000

学号 姓名年龄性别联系电话学历

10、说明:随机取出10条数据

①:select 

①select distinct name from table where name not
in (select  distinct name from table where fenshu<=80)

(select amount  from aaa m where month=1 amd
m.year=aaa.year) as m1,

约束 constraint

Answer:

表形式如下: 

2002 3000 

及格 优秀 不及格 

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

7)
查询学生表的数据,将所有年龄小于22岁的,学历为“大专”的,学生的姓名和学号示出来

1991          1            1.1

select * from ppp order by num desc limit 1;

select * from ppp

name kecheng fenshu 

不可以使用 min 函数,但可以实用 order by 和 limit 相组合呀;

sql语句怎么写?

张三 数学 75

year m1 m2 m3 m4

2) 修改学生表的结构,添加一列信息,学历

4. 说明:拷贝表( 拷贝数据, 源表名:a 目标表名:b)

2               2005002    李四       0001              数学         89

(select amount  from aaa m where month=4 amd
m.year=aaa.year) as m4

select num from ppp order by num limit1;

9) 查询出所有学生的姓名,性别,年龄降序排列

语文 数学 英语 

1. 不使用 min,找出表 ppp 中 num(列)最小的数

1)
创建一张学生表,包含以下信息,学号,姓名,年龄,性别,家庭住址,联系电话

10)select avg(年龄)   from stu group by
性别



1992          2            2.2

select num from ppp where num >=all(select num from ppp);

6)delect from stu where 姓名 like ‘C%’ and
 性别=‘男’

(case   when  语文>=80 then ‘优秀’ when
 语文>=60 then ‘及格’ else ‘不及格’) AS 语文,

②select   name from table group by name having
 min(fenshu)>80

12、解释名词

补充:

②:select top 10 * from A where ID >(select
max(ID) from (select top 30 ID from A order by A ) T) order by
A—怀疑

主键 primary key 标识列 identity 外键 foreign key 检查 check

select * from ppp group by num having count(num) = 1;

5)update stu set 学历=’大专’ where 联系电话 like
‘11%’

2001 2000 

AVG:求平均值

①:select * from A where ID in (select ID from
A group by ID having count(ID)>3)

4)insert into stu values(1 ,’A’ ,22, ‘男’
,123456, ‘小学’),(2 ,’B’ ,21 ,’男’ ,119 ,’中学’)

select b.year, sum(a.salary) from hell0 a, hello b where a.year <=
b.year group by b.year;

张三 语文 81

*Answer:
*

显示格式: 

1)create table stu (学号 int,姓名
varchar(10),年龄 int
,性别 varchar(4) ,家庭住址 varchar(50),联系电话 int)

    where num in (select num from ppp group by num having count(num)
> 1);

李四 语文 76

Answer:

Answer:

Answer:

3. 影分身,一表当做两表用

2001 3000 

大于或等于80表示优秀,大于或等于60表示及格,小于60分表示不及格。