SQL简单练习

SQL简单练习

建表

# 创建数据库STUDENT
CREATE DATABASE STUDENT;
use STUDENT;
# 创建表STUDENT
CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
 SNAME VARCHAR(4) NOT NULL,
 SSEX VARCHAR(2) NOT NULL,
 SBIRTHDAY DATETIME,
 CLASS VARCHAR(5));
# 创建表COURSE
CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
 CNAME VARCHAR(10) NOT NULL,
 TNO VARCHAR(10) NOT NULL);
# 创建表SCORE
CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
 CNO VARCHAR(5) NOT NULL,
 DEGREE NUMERIC(10, 1) NOT NULL);
# 创建表TEACHER
CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
 TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
 TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),
 DEPART VARCHAR(10) NOT NULL);
# 向各表导入数据
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES
(108 ,'曾华' ,'男' ,'1977-09-01',95033),
(105 ,'匡明' ,'男' ,'1975-10-02',95031),
(107 ,'王丽' ,'女' ,'1976-01-23',95033),
(101 ,'李军' ,'男' ,'1976-02-20',95033),
(109 ,'王芳' ,'女' ,'1975-02-10',95031),
(103 ,'陆君' ,'男' ,'1974-06-03',95031);
INSERT INTO COURSE(CNO,CNAME,TNO) VALUES
('3-105' ,'计算机导论',825),
('3-245' ,'操作系统' ,804),
('6-166' ,'数据电路' ,856),
('9-888' ,'高等数学' ,100);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES
(103,'3-245',86),
(105,'3-245',75),
(109,'3-245',68),
(103,'3-105',92),
(105,'3-105',88),
(109,'3-105',76),
(101,'3-105',64),
(107,'3-105',91),
(108,'3-105',78),
(101,'6-166',85),
(107,'6-106',79),
(108,'6-166',81);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES
(804,'李诚','男','1958-12-02','副教授','计算机系'),
(856,'张旭','男','1969-03-12','讲师','电子工程系'),
(825,'王萍','女','1972-05-05','助教','计算机系'),
(831,'刘冰','女','1977-08-14','助教','电子工程系');

题目

# 1、 查询Student表中的所有记录的Sname、Ssex和Class列。
# 2、 查询教师所有的单位即不重复的Depart列。
# 3、 查询Student表的所有记录。
# 4、 查询Score表中成绩在60到80之间的所有记录。
# 5、 查询Score表中成绩为85，86或88的记录。
# 6、 查询Student表中“95031”班或性别为“女”的同学记录。
# 7、 以Class降序查询Student表的所有记录。
# 8、 以Cno升序、Degree降序查询Score表的所有记录。
# 9、 查询“95031”班的学生人数。
# 10、查询Score表中的最高分的学生学号和课程号。
# 11、查询‘3-105’号课程的平均分。
# 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
# 13、查询最低分大于70，最高分小于90的Sno列。
# 14、查询所有学生的Sname、Cno和Degree列。
# 15、查询所有学生的Sno、Cname和Degree列。
# 16、查询所有学生的Sname、Cname和Degree列。
# 17、查询“95033”班所选课程的平均分。
# 18、假设使用如下命令建立了一个grade表：
# create table grade(low varchar(3),upp varchar(3),rank varchar(3));
# 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');
# 现查询所有同学的Sno、Cno和rank列。
# 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
# 20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
# 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
# 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
# 23、查询“张旭“教师任课的学生成绩。
# 24、查询选修某课程的同学人数多于5人的教师姓名。
# 25、查询95033班和95031班全体学生的记录。
# 26、查询存在有85分以上成绩的课程Cno.
# 27、查询出“计算机系“教师所教课程的成绩表。
# 28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
# 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
# 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
# 31、查询所有教师和同学的name、sex和birthday.
# 32、查询所有“女”教师和“女”同学的name、sex和birthday.
# 33、查询成绩比该课程平均成绩低的同学的成绩表。
# 34、查询所有任课教师的Tname和Depart.
# 35 查询所有未讲课的教师的Tname和Depart.
# 36、查询至少有2名男生的班号。
# 37、查询Student表中不姓“王”的同学记录。
# 38、查询Student表中每个学生的姓名和年龄。
# 39、查询Student表中最大和最小的Sbirthday日期值。
# 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
# 41、查询“男”教师及其所上的课程。
# 42、查询最高分同学的Sno、Cno和Degree列。
# 43、查询和“李军”同性别的所有同学的Sname.
# 44、查询和“李军”同性别并同班的同学Sname.
# 45、查询所有选修“计算机导论”课程的“男”同学的成绩表。

答案

# 1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select SNAME,SSEX,CLASS from student;
# 2、 查询教师所有的单位即不重复的Depart列。
select distinct DEPART from teacher;
# 3、 查询Student表的所有记录。
select * from student;
# 4、 查询Score表中成绩在60到80之间的所有记录。
select * from score where DEGREE between 60 and 80;
# 5、 查询Score表中成绩为85，86或88的记录。
select * from score where DEGREE in (85,86,88,87);
# 6、 查询Student表中“95031”班或性别为“女”的同学记录。
select * from student where CLASS=95031 or SSEX='女';
# 7、 以Class降序查询Student表的所有记录。
select * from student s order by CLASS desc;
# 8、 以Cno升序、Degree降序查询Score表的所有记录。
select * from score order by CNO, DEGREE desc;
# 9、 查询“95031”班的学生人数。
select count(*) number from student where CLASS=95031;
# 10、查询Score表中的最高分的学生学号和课程号。
select SNO,CNO from score where (select max(DEGREE) from score);
# 11、查询‘3-105’号课程的平均分。
select avg(DEGREE) from score where CNO='3-105';
# 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select avg(DEGREE), CNO
from score s
where CNO like '3%'
group by CNO
having count(CNO)>5;
# 13、查询最低分大于70，最高分小于90的Sno列。
select SNO from score where DEGREE>70 and DEGREE<90;
# 14、查询所有学生的Sname、Cno和Degree列。
select stu.SNAME,sc.CNO,sc.DEGREE from student stu inner join score sc on stu.SNO = sc.SNO;
# 15、查询所有学生的Sno、Cname和Degree列。
select SNO,CNAME,DEGREE
from COURSE A inner join SCORE S on A.CNO = S.CNO;
# 16、查询所有学生的Sname、Cname和Degree列。
select SNAME,CNAME,DEGREE
from COURSE A
    right join SCORE S on A.CNO = S.CNO
    left join STUDENT S2 on S.SNO = S2.SNO;
select SNAME,CNAME,DEGREE
from COURSE A
         inner join SCORE S on A.CNO = S.CNO
         inner join STUDENT S2 on S.SNO = S2.SNO;
select SNAME,CNAME,DEGREE
from COURSE A
         inner join (SCORE S, STUDENT S2)
             on A.CNO = S.CNO and S.SNO = S2.SNO;

# 17、查询“95033”班所选课程的平均分。
select avg(DEGREE)
from student st right join SCORE S on st.SNO = S.SNO
where CLASS=95033;
# 18、假设使用如下命令建立了一个grade表：
# create table grade(low varchar(3),upp varchar(3),rank varchar(3));
# 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');
# 现查询所有同学的Sno、Cno和rank列。
select SNO,CNO,`rank` from score s,grade g
where s.DEGREE > g.low and s.DEGREE < g.upp;
# 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select * from student
where sno in (
    select distinct sno
    from score
    where CNO='3-105' and DEGREE > ALL (
        select DEGREE
        from score
        where CNO='3-105' and SNO = 109
    )
);
# 20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
select t1.sno, t2.cno, t2.degree
from score t2,
     (select sno, max(degree) maxdegree from score 
      group by sno having count(sno) > 1) t1
where t1.sno = t2.sno and t2.degree < t1.maxdegree;
# 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select * from score
where DEGREE > (
    select DEGREE from score
    where SNO = '109' and CNO = '3-105'
);
# 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select SNO, SNAME, SBIRTHDAY from student
where year(SBIRTHDAY) = (select year(SBIRTHDAY) from student where SNO = 108);
# 23、查询“张旭“教师任课的学生成绩。
select SNO, DEGREE
from score inner join (COURSE C , teacher t)
              on C.TNO = t.TNO and SCORE.CNO = C.CNO
where t.TNAME = '张旭';
# 24、查询选修某课程的同学人数多于5人的教师姓名。
select TNAME
from teacher inner join course c on TEACHER.TNO = c.TNO
where c.CNO = (select sc.CNO from score sc group by sc.CNO having count(1)>5);
# 25、查询95033班和95031班全体学生的记录。
select *
from student
where CLASS in ('95033', '95031');
# 26、查询存在有85分以上成绩的课程Cno.
select distinct CNO
from score
where DEGREE > 85;
# 27、查询出“计算机系“教师所教课程的成绩表。
select *
from score
         join (COURSE C , teacher t)
              on SCORE.CNO = C.CNO and C.TNO = t.TNO
where DEPART = '计算机系';
# 28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
select TNAME, PROF
from teacher
where DEPART in ('计算机系', '电子工程系') + 6;
# 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select CNO, SNO, DEGREE
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”课程的同学的Cno、Sno和Degree.
select CNO, SNO, DEGREE
from score
where CNO = '3-105'
  and DEGREE > all
      (select DEGREE from score where CNO = '3-245');
# 31、查询所有教师和同学的name、sex和birthday.
select sno, sname, ssex, sbirthday
from student
union
select tno, tname, tsex, tbirthday
from teacher;
# 32、查询所有“女”教师和“女”同学的name、sex和birthday.
select sno, sname, ssex, sbirthday
from student
where SSEX = '女'
union
select tno, tname, tsex, tbirthday
from teacher
where TSEX = '女';
# 33、查询成绩比该课程平均成绩低的同学的成绩表。
select c1.*
from score c1
where DEGREE < any (
    select avg(DEGREE)
    from score c2
    where c1.CNO = c2.CNO
    group by c2.CNO);

SELECT A.*
FROM SCORE A
         INNER JOIN
     (SELECT CNO, AVG(DEGREE) AVG
      FROM SCORE
      GROUP BY CNO) B
     ON A.CNO = B.CNO
WHERE DEGREE < AVG;

# 34、查询所有任课教师的Tname和Depart.
select TNAME, DEPART
from teacher
         join COURSE C on TEACHER.TNO = C.TNO;
# 35、查询所有未讲课的教师的Tname和Depart.
SELECT TNAME, DEPART
FROM TEACHER
WHERE TNO NOT IN (SELECT TNO FROM COURSE);
# 36、查询至少有2名男生的班号。
select CLASS
from student
where SSEX = '男'
group by CLASS
having count(*) > 1;
# 37、查询Student表中不姓“王”的同学记录。
select *
from student
where not SNAME like '王%';
# 38、查询Student表中每个学生的姓名和年龄。
select SNAME, year(now()) - year(SBIRTHDAY) Age
from student;
# 39、查询Student表中最大和最小的Sbirthday日期值。
select MAX(Sbirthday), MIN(Sbirthday)
from student;
# 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select *
from student
order by CLASS desc, SBIRTHDAY;
# 41、查询“男”教师及其所上的课程。
select TNAME, CNAME
from teacher
         join COURSE C on TEACHER.TNO = C.TNO
where TSEX = '男';
# 42、查询最高分同学的Sno、Cno和Degree列。
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, CLASS) = (select SSEX, CLASS from student where SNAME = '李军');
# 45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
select *
from score
         join STUDENT S on SCORE.SNO = S.SNO
         join COURSE C on SCORE.CNO = C.CNO
where CNAME = '计算机导论'
  and SSEX = '男';
版权声明：本文为qtds114原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。