SQL查询语句练习3

SQL查询语句练习

建表

/*
创建学生表
列名    说明    数据类型           约束
Sno    学号    字符串，长度为7     主键
Sname  姓名    字符串，长度为15    非空
Ssex   性别    字符串，长度为3     取‘男’或‘女’
Sage   年龄    整数    取值15~45
Sdept  所在系   字符串，长度为30   默认为‘计算机系’
*/
create table student(
	sno varchar2(7) primary key,
    sname varchar2(15) not null,
    ssex varchar2(3) check(ssex='男' or ssex='女'),
    sage number(2) check(sage between 15 and 45),
    sdept varchar2(30) default('计算机系')
);
/*
创建课程表
列名      说明    数据类型            约束
Cno      课程号   字符串，长度为15     主键
Cname    课程名   字符串，长度为20     非空
Ccredit  学分     整数               取值大于0
Semster  学期     整数               取值大于0
*/
create table course(
	cno varchar2(15) primary key,
    cname varchar2(20) not null,
    ccredit number(2) check(ccredit>0),
    semster number(2) check(semster>0)
);
/*
创建成绩表
列名    说明                数据类型                 约束
Sno    学号                字符串，长度为7    关联Student表主键Sno
Cno    课程名              字符串，长度为10    关联Course表主键Cno
Grade  成绩                 整数                 取值0~100
XKLB   课程类别(选修|必修)    字符串 长度6
*/
create table sc(
	sno varchar2(7) references student(sno),
    cno varchar2(10) references course(cno),
    grade number(3) check(grade between 0 and 100),
    xklb varchar2(6)
);

插入数据

--往student表中插入数据
insert into student values('9512101','李勇','男',19,'计算机系');
insert into student values('9512102','刘晨','男',20,'计算机系');
insert into student values('9512103','王敏','女',20,'计算机系');
insert into student values('9521101','张立','男',22,'信息系');
insert into student values('9521102','吴宾','女',21,'信息系');
insert into student values('9521103','张海','男',20,'信息系');
insert into student values('9531101','钱小平','女',18,'数学系');
insert into student values('9531102','王大力','男',19,'数学系');
--往course表中插入数据
insert into course values('C01','计算机文化学',3,1);
insert into course values('C02','VB',2,3);
insert into course values('C03','计算机网络',4,7);
insert into course values('C04','数据库基础',6,6);
insert into course values('C05','高等数学',8,2);
insert into course values('C06','数据结构',5,4);
--往sc表中插入数据
insert into sc values('9512101','C01',90,'必修');
insert into sc values('9512101','C02',86,'选修');
insert into sc values('9512101','C06',,'必修');
insert into sc values('9512102','C02',78,'选修');
insert into sc values('9512102','C04',66,'必修');
insert into sc values('9521102','C01',82,'选修');
insert into sc values('9521102','C02',75,'选修');
insert into sc values('9521102','C04',92,'必修');
insert into sc values('9521102','C05',50,'必修');
insert into sc values('9521103','C02',68,'选修');
insert into sc values('9521103','C06',,'必修');
insert into sc values('9531101','C01',80,'选修');
insert into sc values('9531101','C05',95,'必修');
insert into sc values('9531102','C05',85,'必修');

select查询语句练习

--1.查询三张表的全部信息
select * from student;
select * from course;
select * from sc;
--2.查询全体学生的学号与姓名
select sno, sname from student;
--3.查询全体学生的姓名，学号和所在系
select sname, sno, sdept from student;
--4.查询全体学生的记录
select * from student;
--5.查询全体学生的姓名及其出生年份
select sname, 2021 - sage from student;
--6.查询全体学生的姓名和出生年份，并在出生年份列前加入一个列，此列的每行数据均为“Year of Birth”常量值
select sname, 'Year of Birth', 2021 - sage from student;
--7.在选课表（SC）中查询有哪些学生选修了课程，并列出学生的学号
select distinct sno from sc;
--8.查询计算机系全体学生的姓名
select sname from student where sdept = '计算机系';
--9.查询所有年龄在20岁以下的学生的姓名及年龄
select sname, sage from student where sage < 20;
--10.查询考试成绩不及格的学生的学号
select distinct sno from sc where grade < 60;
--11.查询年龄在20~23岁之间的学生的姓名，所在系和年龄
select sname, sdept, sage from student where sage between 20 and 23;
--12.查询年龄不在20~23之间的学生的姓名，所在系和年龄
select sname, sdept, sage from student where sage not between 20 and 23;
--13.查询信息系，数学系和计算机系学生的姓名和性别
select sname, ssex
  from student
 where sdept in ('信息系', '数学系', '计算机系');
--14.查询既不属于信息系，数学系，也不属于计算机系的学生的姓名和性别
select sname, ssex
  from student
 where sdept not in ('信息系', '数学系', '计算机系');
--15.查询姓“张”的学生的详细信息
select * from student where sname like '张%';
--16.查询学生表中姓“张”，姓“李”和姓“刘”的学生的情况
select *
  from student
 where sname like '张%'
    or sname like '李%'
    or sname like '刘%';
--17.查询名字中第2个字为“小”或“大”字的学生的姓名和学号
select *
  from student
 where sname like '_小%'
    or sname like '_大%';
--18.查询所有不姓“刘”的学生
select * from student where sname not like '李%';
--19.从学生表中查询学号的最后一位不是2，3，5的学生的情况
select *
  from student
 where sno not like '%2'
   and sno not like '%3'
   and sno not like '%5';
--20.查询无考试成绩的学生的学号和相应的课程号
select sno, cno from sc where grade is null;
--21.查询所有有考试成绩的学生的学号和课程号
select sno, cno from sc where grade is not null;
--22.查询计算机系年龄在20岁以下的学生的姓名
select sname
  from student
 where sdept = '计算机系'
   and sage < 20;
--23.将学生按年龄升序排序
select * from student order by sage;
--24.查询选修了课程“c02”的学生的学号及其成绩，查询结果按成绩降序排列
select sno, grade from sc where cno = 'C02' order by grade desc;
--25.查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列
select * from student order by sdept asc, sage desc;
--26.统计学生总人数
select count(1) from student;
--27.统计选修了课程的学生的人数
select count(distinct sno) from sc;
--28.计算学号为9512101的学生的考试总成绩之和
select sum(grade) from sc group by sno having sno = '9512101';
--29.计算课程“c01”的学生的考试平均成绩
select avg(grade) from sc group by cno having cno = 'C01';
--30.查询选修了课程“c01”的学生的最高分和最低分
select max(grade), min(grade) from sc group by cno having cno = 'C01';
--31.统计每门课程的选课人数，列出课程号和人数
select cno, count(sno) from sc group by cno;
--32.查询每名学生的选课们数和平均成绩
select sno, count(cno), avg(grade) from sc group by sno;
--33.查询选修了3门以上课程的学生的学号
select sno, count(cno) from sc group by sno having count(cno) > 3;
--34.查询选课门数等于或大于4门的学生的平均成绩和选课门数
select sno, avg(grade), count(cno)
  from sc
 group by sno
having count(cno) >= 4;
--35.查询每个学生的情况及其选课的情况
select s1.sno, s1.sname, s2.cno, s2.grade
  from student s1
  left join sc s2
    on s1.sno = s2.sno;
--36.如果选课表中出现重复数据,需要对重复数据做处理
delete from sc
 where rowid not in
       (select max(rowid) from sc group by sno, cno, grade, xklb);
--37.查询计算机系学生的选课情况，要求列出学生的名字，所修课的课程号和成绩
select sname, cno, grade
  from (select * from student s1 left join sc s2 on s1.sno = s2.sno)
 where sdept = '计算机系';
--38.查询信息系选修VB课程的学生的成绩，要求列出学生姓名，课程名和成绩
select sname, cname, grade
  from (select *
          from student s1
          join sc s2
            on s1.sno = s2.sno
          join course c
            on s2.cno = c.cno)
 where cname = 'VB';
--39.查询所有选修了VB课程的学生的情况，要求列出学生姓名和所在的系
select sname, sdept
  from student
 where sno in
       (select sno
          from sc
         where cno = (select cno from course where cname = 'VB'));
--40.查询与刘晨在同一个系学习的学生的姓名和所在系
select sname, sdept
  from student
 where sdept = (select sdept from student where sname = '刘晨');
--41.查询学生的选课情况，包括选修课程的学生和没有修课的学生
select s1.sno, sname, cno
  from student s1
  left join sc s2
    on s1.sno = s2.sno;
--42.查询与刘晨在同一个系的学生
select *
  from student
 where sdept = (select sdept from student where sname = '刘晨');
--43.查询成绩大于90分的学生的学号和姓名
select sno, sname
  from student
 where sno in (select sno from sc where grade > 90);
--44.查询选修了“数据库基础”课程的学生的学号和姓名
select sno, sname
  from student
 where sno in
       (select sno
          from sc
         where cno = (select cno from course where cname = '数据库基础'));
--45.查询选修了课程“c02”且成绩高于次课程的平均成绩的学生的学号和成绩
select sno, grade
  from sc
 where cno = 'C02'
   and grade > (select avg(grade) from sc group by cno having cno = 'C02');
--46.查询选修了课程“c01”的学生姓名
select sname
  from student
 where sno in (select sno from sc where cno = 'C01');
--47.查询没有选修课程“c01”的学生姓名和所在系
select sname
  from student
 where sno not in (select sno from sc where cno = 'C01');
--48.查询选修了课程“c01”的学生的姓名和所在系
select sname, sdept
  from student
 where sno in (select sno from sc where cno = 'C01');
--49.查询数学系成绩在80分以上的学生的学号，姓名
select sno, sname
  from student
 where sdept = '数学系'
   and sno in (select sno from sc where grade > 80);
--50.查询计算机系考试成绩最高的学生的姓名        
select sname
  from student
 where sno =
       (select sno
          from sc
         where sno in (select sno from student where sdept = '计算机系')
           and grade =
               (select max(grade)
                  from sc
                 where sno in
                       (select sno from student where sdept = '计算机系')));
--51.将新生纪录（9521105，陈冬，男，信息系，18岁）插入到Student表中
insert into student values ('9521105', '陈冬', '男', 18, '信息系');
--52.在SC表中插入一新记录（9521105，c01），成绩暂缺
insert into sc values ('9521105', 'C01', null, null);
--53.将所有学生的年龄加1
update student set sage = sage + 1;
--54.将“9512101”学生的年龄改为21岁
update student set sage = 21 where sno = '9512101';
--55.将计算机系学生的成绩加5分
update sc
   set grade = grade + 1
 where sno in (select sno from student where sdept = '计算机系');
--56.查询所有科目成绩中排名前三的学生姓名
select sno, cno, grade, rownum
  from (select sno, cno, grade
          from sc
         where grade is not null
         order by grade desc)
 where rownum <= 3;
--56.查询所有科目成绩中排名第四到第六的学生姓名
select sno, cno, grade
  from (select sno, cno, grade, rownum num
          from (select sno, cno, grade
                  from sc
                 where grade is not null
                 order by grade desc))
 where num between 4 and 6;