MSSQL Server面试题组成

(select s_name from score

  1. 老师表teacher

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

事务 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、查询出女生成绩最低的人的姓名;

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

一、表关系

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

一、多表查询

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

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

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

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

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

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

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

② 表别名的用法

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

(select name from student where name like 张%

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 = ‘物理’));

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

案例三

有四张表格:

学生表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

select min(a1.id) from team a1

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

year m1 m2 m3 m4

案例二

如下,有三张表:

学生表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;

表名: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); 

2002 3000 

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

 

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

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 …… —————————–

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

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

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

 

只返回单独的一条记录

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

 

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

select min(id) from team group by 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); 
  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_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;

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

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

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

Year Salary 

where a1.name=team.name )

from aaa 

(

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

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

group by year

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

6、编写SQL语句

或者: select * from score where s_name in

12、解释名词

group by name having avg(score) 75)

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

select * from score

Answer:

表名:student

显示格式: 

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

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

having min(score)=60)

1               2005001    张三       0001              数学         69

where s_name not in

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

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

1992 2.1 2.2 2.3 2.4

(

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

delete from team where id not in

8)select top 25 percent * from stu 

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

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

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

王五 数学 100

where score60)

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

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


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

2. 学生表 如下:

select * from student where name in

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

几道经典的SQL笔试题目

select * from ppp

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

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

delete from team where id not in

2000 1000 

group by s_name

1991          2            1.2

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

2               2005002    李四       0001              数学         89

发表评论

电子邮件地址不会被公开。 必填项已用*标注