首页新萄京手机版官网 › sql多如牛毛题-20180223

sql多如牛毛题-20180223

group by name having avg(score) 75)

一、表关系

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

案例二

如下,有三张表:

学生表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、检索"王大帅"同学不学课程的学科号(CNOState of Qatar;

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;

select * from student where name in

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(scoreState of Qatar 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 = '物理'));

1992          1            2.1

案例三

有四张表格:

学生表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(scoreState of Qatar 全部科指标总战绩 from Student a left join score b on a.sid =
b.sid group by a.sid,a.Sname order by a.sid

付出高考总分在600之上的上学的儿童准考证号

  1. 老师表teacher

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

新萄京手机版官网,李四 语文 76

一、多表查询

--编写多表查询语句的日常经过

--(1)、深入分析句子要涉及到哪边表

--(2)、对应的表中要询问哪些关系字段

--(3)、分明连接条件或筛选标准

--(4)、写成完全的SQL查询语句

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

① 多个表之间必得创建连接关系

② 表别名的用法

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

表名:高等学校统一招考新闻表 准考证号 科目 战绩 二零零六001 语文 119 二〇〇五001 数学 108
二零零六002 物理 142 2005001 化学 136 二〇〇六001 物理 127 二零零五002 数学 149
二零零五002 România语 110 二〇〇六002 语文 105 二〇〇七001 Republika Hrvatska语 98 二〇〇六002 化学 129 ……

  1. 班级表class

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

    insert into class values(1,'少一一班',1State of Qatar,(2,'少二一班',2卡塔尔(قطر‎,(3,'少三二班',3卡塔尔,(4,'少四一班',4卡塔尔,(5,'少五三班',5卡塔尔国;

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

二、案例一

学生表student:

+-----+--------+-----+-------+------------+--------------+

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

+-----+--------+-----+-------+------------+--------------+

| 901 | 张老大 | 男  |  1984 | 计算机系  | 香江市海淀区 |

| 902 | 张老二 | 男  |  一九八六 | 中国语言法学系    | 新加坡市昌平区 |

| 903 | 张三  | 女  |  1988 | 中国语言医学系    | 西藏省营口市 |

| 904 | 李四  | 男  |  一九九零 | Republika Hrvatska语系    | 广西省三沙市 |

| 905 | 王五  | 女  |  1993 | 爱尔兰语系    | 云南省厦门市 |

| 906 | 王六  | 男  |  一九九零 | 计算机系  | 湖南省唐山市 |

+-----+--------+-----+-------+------------+--------------+

成绩表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、同期学习中文和Computer的学童音信;

3、姓王的同窗何况有一科以上战绩超过80分的上学的儿童音信;

4、查询李四的考察课程(c_name)和考试成绩(grade)

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

5、查询Computer成绩低于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 s_name from score

  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,'Jordan','女',1State of Qatar,(2,'艾弗森','女',1卡塔尔国,(3,'黑曼巴','男',2卡塔尔国,(4,'葫芦娃','男',3卡塔尔国,(5,'张全一','男',5State of Qatar,(6,'洞房不败','男',4卡塔尔国,(7,'樱木花道','男',2卡塔尔(قطر‎,(8,'松岛菜菜子','女',3卡塔尔国,(9,'洞房不败','女',5卡塔尔国;

Answer:

几道卓越的SQL笔试标题

 

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

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

 

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

having min(score)=60)

先创建如下表,并创办连锁节制

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

select * from score

 

from table

3。表内容如下 ----------------------------- ID LogTime 1 2009/10/10
10:00:00 1 二零零六/10/10 10:03:00 1 二〇〇八/10/10 10:09:00 2 二〇〇八/10/10
10:10:00 2 二零零六/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,'Jordan','女',1卡塔尔,(2,'艾弗森','女',1卡塔尔国,(3,'Kobe','男',2卡塔尔,(4,'葫芦娃','男',3卡塔尔,(5,'张真人','男',5State of Qatar,(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,'李四'State of Qatar,(3,'王五'卡塔尔国,(4,'萧峰'卡塔尔,(5,'一休哥'State of Qatar,(6,'诸葛'State of Qatar,(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,'体育',1State of Qatar,(3,'物理',2卡塔尔国,(4,'数学',3State of Qatar,(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,40State of Qatar,(8,6,4,80卡塔尔,(9,7,3,37State of Qatar,(10,8,5,100State of Qatar,(11,8,6,89State of Qatar,(12,8,7,0State of Qatar,(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,5State of Qatar,(6,5,3State of Qatar,(7,5,5卡塔尔,(8,6,2State of Qatar,(9,6,4State of Qatar,(10,6,3State of Qatar,(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.scoreState of Qatar 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为高年级State of Qatar,示例结果如下;select cid
    as 班级id,caption as 班级名称,gname as 年级,casewhen g.gid in (1,2State of Qatar 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 1State of Qatar卡塔尔(قطر‎;
    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,语文,数学,Turkey语,有效课程数,有效平均分;【这里标题有歧义:有效课程数和管事平均分是仅以那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
    乌Crane语,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 = 'Turkey语' 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.scoreState of Qatar 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、查询各科成绩前三名的记录(不考虑成绩并列景况State of Qatar【本题与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(sidState of Qatar 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(1State of Qatar 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_idState of Qatar 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;

6卡塔尔国 删除学子表的数据,姓名以C最初,性别为‘男’的笔录删除

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

一、表关系请创制如下表,并创建连锁约束1. 班级表class【创设表语句】create
table class(cid int primary key auto_increment,caption
char(10),grade_id int);
【插入记录语句】insert into class
values(1,'少一一班',1卡塔尔,(2,'少二一班',2State of Qatar,(3,'少三二班',3卡塔尔,(4,'少四一班',4卡塔尔,(5,'少五三班',5卡塔尔国; 

及格 优秀 不及格 

询问出该俱乐部里男人会员和女人会员的总额

2               2005002    李四       0001              数学         89

基本提示:试问各位高手,怎么着询问登入时间隔断不超越5分钟的具备记录

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

(四部分) 表名:club

Answer:

询问出“张”姓学子中平均成绩超过75分的学员音信

2001 3000 

(

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

交付平均进价在2元以下的商品名称

1991          4            1.4

select min(a1.id) from team a1

***8、查询表A中设有ID重复叁次以上的笔录,完整的查询语句如下

(select name from student where name like 张%

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

where a1.name=team.name )

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

where score60)

select num from ppp order by num limit1;

where s_name not in

3)alter table stu drop column
家庭住址

表名:student

sql语句怎么写?

提交高考总分在600以上的学习者准考证号

4. 认证:拷贝表( 拷贝数据, 源表名:a 目的表名:b卡塔尔

select 准考证号 from 高等高校统一招考音讯表 where (数学+语文+波兰语+物理+化学卡塔尔国 600

Year Salary 

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

张三 语文 81

(

来得格式: 

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


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

2001 2000 

借问各位高手,怎么样询问登入时间距离不超越5分钟的有所记录.

1State of Qatar创立一张学子表,包涵以下音信,学号,姓名,年龄,性别,家庭住址,联系电话

表名: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语句。

***11、查询平均战表超乎60分的同室的学号和平均成绩;

delete from team where id not in

①:select year ,

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

8)select top 25 percent * from stu 

或者: select * from score where s_name in

SUM:求和

(select s_name from score

1992          4            2.4

select 准考证号 from 高等学园统招考试新闻表 group by 准考证号 having sum(成绩卡塔尔(قطر‎ 600

高于或等于80意味着能够,大于或等于60意味着及格,小于60分表示比不上格。 

转载本站文章请注明出处:新萄京娱乐网址2492777 http://www.cdhbjs.com/?p=5535

上一篇:

下一篇:

相关文章