• mysql数据库查询练习45题


     

    #建学生信息表studentcreate table student

    (

    sno varchar(20) not null primary key,

    sname varchar(20) not null,

    ssex varchar(20) not null,

    sbirthday datetime,

    class varchar(20)

     

    );

    #建立教师表create table teacher

    (

    tno varchar(20) not null primary key,

    tname varchar(20) not null,

    tsex varchar(20) not null,

    tbirthday datetime,

    prof varchar(20),

    depart varchar(20) not null

     

    );

    #建立课程表coursecreate table course

    (

    cno varchar(20) not null primary key,

    cname varchar(20) not null,

    tno varchar(20) not null,foreign key(tno) references teacher(tno)

     

    );

    #建立成绩表create table score

    (

    sno varchar(20) not null primary key,foreign key(sno) references student(sno),

    cno varchar(20) not null,foreign key(cno) references course(cno),

    degree decimal

     

    );

     

    #添加学生信息insert into student values('108','曾华','男','1977-09-01','95033');insert into student values('105','匡明','男','1975-10-02','95031');insert into student values('107','王丽','女','1976-01-23','95033');insert into student values('101','李军','男','1976-02-20','95033');insert into student values('109','王芳','女','1975-02-10','95031');insert into student values('103','陆君','男','1974-06-03','95031');

     

     

     

     

    #添加教师表insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');

     

    #添加课程表insert into course values('3-105','计算机导论','825');insert into course values('3-245','操作系统','804');insert into course values('6-166','数字电路','856');insert into course values('9-888','高等数学','831');

    #添加成绩表

    insert into score values('103','3-245','86');insert into score values('105','3-245','75');insert into score values('109','3-245','68');insert into score values('103','3-105','92');insert into score values('105','3-105','88');insert into score values('109','3-105','76');insert into score values('103','3-105','64');insert into score values('105','3-105','91');insert into score values('109','3-105','78');insert into score values('103','6-166','85');insert into score values('105','6-166','79');insert into score values('109','6-166','81');

     题目:

     

    1、 查询Student表中的所有记录的SnameSsexClass列。

    select sname,ssex,class from student;

     

    2、 查询教师所有的单位即不重复的Depart列。

    select distinct depart from teacher;

     

    3、 查询Student表的所有记录。

    select * from student;

     

    4、 查询Score表中成绩在6080之间的所有记录。

    select * from score where degree between 60 and 80;

     

    5、查询Score表中成绩为858688的记录

    select * from score where degree=85 or degree=86 or degree=88;

    select * from score where degree in(85,86,88)

     

    6、 查询Student表中“95031”班或性别为的同学记录。

    select * from student where class='95031' or ssex='女';

     

    7、 以Class降序查询Student表的所有记录。

    select * from student order by class desc;

     

    8、 以Cno升序、Degree降序查询Score表的所有记录。

    select * from score order by cno asc,degree desc;

     

    9、 查询“95031”班的学生人数。

    select count(*) from student where class='95031';

     

    10、 查询Score表中的最高分的学生学号和课程号。(子查询或者排序)

    select sno,cno from score where degree=(select max(degree) from score);

     

    11、 查询每门课的平均成绩。

    select cno,avg(degree) from score group by cno;

     

    详解--查询每门课的平均成绩--分组和聚合结合的时候,先分组,然后对每一组分别进行聚合Select AVG(Degree) from Score group by Cno;--先按Cno分组,在对Cno聚合

     

    12、 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

    解法1select * from score where cno in(select cno from score group by cno having count(*)>=5) and cno like '3%';

    解法2select cno,avg(degree) from score where cno like'3%'group by cno having count(*) >=5;

     

    详解--select Cno,AVG(Degree) from Score where Cno like '3%' group by Cno having COUNT(*)>=5--筛选分组完了再对每一组进行AVG聚合,分完组之后相当于只剩下分完组的列

     

    13、 查询分数大于70,小于90Sno列。

    select sno from score group by sno having min(degree)>70 and max(degree)<90

     

    14、 查询所有学生的SnameCnoDegree列。

    select student.sname,score.cno,score.degree from student,score where student.sno=score.sno;

     

    详解--表连接,一定有主外键关系:都好隔开两个表形成笛卡尔积,再进行where筛选,通过表主外键关系筛选,where只是个筛选条件

     

    15、查询所有学生的SnoCnameDegree列。

    select score.sno,score.degree,course.cname from score,course where score.cno=course.cno;

     

    16、 查询所有学生的SnameCnameDegree列。

    解法1:select student.sname,score.degree,course.cname from student,score,course where score.cno=course.cno and score.sno=student.sno;

    解法2:select Sname,Cname,Degree from student join Score on Student.Sno=Score.Sno join Course on Score.Cno=Course.Cno

     

    17、 查询“95033”班学生的平均分。

    解法1:select avg(degree) from student,score where student.sno=score.sno and class='95033';

     

    解法2:select avg(degree) from score where sno in(select sno from student where class='95033');

     

     18、假设使用如下命令建立了一个grade表:

    create table grade(low  int(3),upp  int(3),rank  char(1))

    insert into grade values(90,100,’A’)

    insert into grade values(80,89,’B’)

    insert into grade values(70,79,’C’)

    insert into grade values(60,69,’D’)

    insert into grade values(0,59,’E’)

    现查询所有同学的SnoCnorank列。

    解法1:select sno,cno,rank from score,grade where degree between low and upp order by rank;

     

    解法2:select sno,cno,degree,[rank] from Score join  grade on degree between low and upp order by [rank]--on后面加筛选条件解法3:select Sno,Cno,(select [rank] from grade where Score.Degree between low and upp) as LV from Score order by Degree desc--子查询得到的结果必须唯一

     

    19查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

    select * from score where cno='3-105' and degree>(select degree from score  where sno=109 and cno='3-105');

     

    20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。

     

    理解1:选学多门课程的同学中,分数小于所有学生中最高分:
    1.select * from score where sno in(2)and degree<(3)

    2.select sno from score group by sno having count(*)>1

    3.select max(degree) from score  where sno in(4)

    4.select sno from score group by sno having count(*)>1

    结合起来:select * from score where sno in(select sno from score group by sno having count(*)>1) and degree<(select max(degree) from score  where sno in(select sno from score group by sno having count(*)>1));

     

    理解2:选多门课程中,分数小于每门课程最高分的学生信息
    select * from score a where sno in(select sno from score group by sno having count(*)>1) and degree<(select max(degree) from score  b where b.cno = a.cno )

    21 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

    select *from score where degree>(select degree from score where sno='109'and  Cno='3-105')

     

    22、查询和学号为108的同学同年出生的所有学生的SnoSnameSbirthday

    select sno,sname,sbirthday from student where year(sbirthday)=(select year(sbirthday) from student where sno='108');

     

    23、查询张旭教师任课的学生成绩。

    1.select * from score where cno in ()2.select cno from course where tno=()3.select tno from teacher where tname='张旭'
    结合起来:

    select * from score where cno in (select cno from course where tno=(select tno from teacher where tname='张旭'))

     

     

    解法1:select teacher.tname,course.cno,score.degree from teacher,course,score where teacher.tno=course.tno and course.cno=score.cno and tname='张旭';

     

    解法2:select degree from Score join Course on Score.Cno=Course.Cno join Teacher on Course.Tno=Teacher.Tno where Tname = '张旭'

     

    24、查询选修某课程的同学人数多于3人的教师姓名。

    1.select tname from teacher where tno=()2.select tno from course where cno=()3.select cno from score group by cno having count(*)>3
    联合起来:

    select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>3))

     

    解法1;select teacher.tname from teacher,score,course where teacher.tno=course.tno and course.cno=score.cno group by tname having count(*)>3 ;

     

    解法2:select Tname from Teacher where Tno=(select Tno from Course where Cno=(select Cno from Score group by Cno having COUNT(Cno)>=3)) 

    25、查询95033班和95031班全体学生的记录。

    解法1:select * from student where class='95033' or class='95031';

    解法2:select*from student inner join Score on Student.Sno=Score.Sno where Class in(‘95033’,‘95031’)

    解法3:select *from Student,Score where Class in(95033,95031) and Student.Sno=Score.Sno

    select * from(score left join course on score.cno=course.cno)left join student on score.sno=student.sno where class="95033" or class="95031";

     

    26、 查询存在有85分以上成绩的课程Cno.

    select score.cno from score,course where score.cno=course.cno and degree>85;

    select cno from score group by cno having max(degree)>85;

     

    27、查询出计算机系教师所教课程的成绩表

    解法1:select degree from teacher,score,course where teacher.tno=course.tno and course.cno=score.cno and teacher.depart='计算机系';

     

    解法2:select *from Score where Cno in( select Cno from Course where Tno in (select Tno from Teacher where Depart='计算机系'))解法3:select Sno,Score.Cno,Degree from Score join Course on Score.Cno=Course.Cno join Teacher on Course.Tno=Teacher.Tno where Depart='计算机系'

    28、查询“计算 机系”与“电子工程系“不同职称的教师的Tname和Prof。

    解法1:select tname,prof from teacher where depart='计算机系' and prof not in (select prof from teacher where depart='电子工程系');

     

    解法2:select tname,prof from teacher where prof not in

    (select prof from teacher where depart='电子工程系' and prof in (select prof from teacher where depart='计算机系'))and depart in ('计算机系','电子工程系')--查询两个系中教师相同职称名称,不在这里面的就是除去两个系都有的剩下的,见上面select prof from teacher where depart='电子工程系' and prof in (select prof from teacher where depart='计算机系')  

     

    29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的CnoSnoDegree,并按Degree从高到低次序排序。any的用法)

    解法1;select * from score where cno='3-105' and degree > (select min(degree) from score where cno='3-245')order by degree desc;

     

    解法2:select * from score where cno='3-105' and degree > any(select degree from score where cno='3-245')order by degree desc;

     

     

    30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的CnoSnoDegree.all的用法)

    select * from score where cno='3-105' and degree > (select max(degree) from score where cno='3-245');

     

    select * from score where cno='3-105' and degree > all(select degree from score where cno='3-245');

     

     

    31 查询所有教师和同学的namesexbirthday.

    select tname,tsex,tbirthday from teacherunionselect sname,ssex,sbirthday from student

     

    32、查询所有教师和同学的namesexbirthday.

    select tname,tsex,tbirthday from teacher where tsex='女'union select sname,ssex,sbirthday from student where ssex='女'

     

    33 查询成绩比该课程平均成绩低的同学的成绩表。

    select * from score as aa where degree<(select avg(degree) from score as bb where aa.cno=bb.cno);

     

    解法2:详解--相关子查询--同一门学科的平均分,,每门学科低于自身平均分的select * from score as a where a.degree<

    (select AVG(degree) from score as b where a.cno = b.cno  group by cno)--相当于foreach列出每一门课的平均分select * from score as a where a.degree<

    (select AVG(degree) from score as b  group by cno having a.cno = b.cno)

     

    34、 查询所有任课教师的TnameDepart

    解法1:select tname,depart from teacher where exists (select * from course where teacher.tno=course.tno);

     

    解法2:select Tname,Depart from Teacher where Tno in(select distinct Tno from Course)  

     

    35 、 查询所有未讲课的教师的TnameDepart.

    解法1:select tname,depart from teacher where not exists (select * from course where teacher.tno=course.tno);

     

     

    36、查询至少有2名男生的班号。

    select class from student where ssex='男'group by class having count(*)>=2;

     

     

    37、查询Student表中不姓的同学记录。

    select * from student where sname not like'王%';

     

    38、查询Student表中每个学生的姓名和年龄。

    select sname,year(now())-year(sbirthday) from student;

     

    39、查询Student表中最大和最小的Sbirthday日期值。

    select max( date(student.sbirthday)) ,min( date(student.sbirthday)) from student;

     

    40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

    select * from student order by class desc,date(sbirthday) asc;

     

    41、查询教师及其所上的课程。

    解法1:select teacher.tname,teacher.tsex,course.cname from teacher,course where teacher.tno=course.tno and tsex='男';

     

    解法2:select teacher.tno,tname,tsex,cname,cno from teacher join course on teacher.tno=course.tno where tsex='男'

     

    42、查询最高分同学的SnoCnoDegree列。

    select sno,cno,degree from score where degree=(select max(degree) from score);

     

     

    43、查询和李军同性别的所有同学的Sname.

    select sname from student where ssex=(select ssex from student where sname='李军');

     

    44、查询和李军同性别并同班的同学Sname.

    select sname from student where ssex=(select ssex from student where sname='李军') and class=(select class from student where sname='李军');

     

    45、查询所有选修计算机导论课程的同学的成绩表。

    select student.sname,score.degree from student,score,course where student.sno=score.sno and course.cno=score.cno and course.cname='计算机导论' and student.ssex='男';

    select * from score where sno in(select sno from student where ssex='男') and cno=(select cno from course where cname='计算机导论')

     

  • 相关阅读:
    SQL Server中的执行引擎入门
    SQL Server复制入门(一)复制简介
    Django 代码片断收集(持续更新)
    今天思路有点乱,随便记一点关于 xmlrpc 的
    PIL 学习笔记(1)
    Django newforms
    在 Django 的 View 中利用 function decorator 可实现一定程度的代码重用
    今天在 Linux 上顺利编译 PIL 1.1.6 成功
    用 PIL 写了个简单的缩略图生成程序
    [转贴] 中药内外合治急慢性鼻窦炎
  • 原文地址:https://www.cnblogs.com/yang1182/p/9613988.html
Copyright © 2020-2023  润新知