SQL语句基础5/select查询语句练习2

SQL语句基础5

注：除指定外，所有数据来源于Oracle数据库自带的SCOTT用户表。

1. DDL语句

DDL（数据定义语言）用于操作对象和对象的属性，这种对象包括数据库本身，以及数据库对象，像：表、视图等等，DDL 对这些对象和属性的管理和定义具体表现在 create、drop 和 alter 上。

• create：创建数据库和数据库的一些对象，创建过程添加约束条件，约束条件分为主键约束，非空约束，唯一约束，默认约束，检查约束，外键约束。语法：

create table 表名(
             字段名1 字段类型,
             字段名2 字段类型,
             .....
)

• drop：可以删除数据表，索引，条件约束等，语法：

drop table 表名...

• alter：修改数据表定义及属性，语法：

alter table 表名...

参考代码如下：

--创建班级表
create table bd_class(
       cno number(3) primary key,--主键约束
       cname varchar2(30)
)
--创建带约束条件的学生表
create table bd_student(
       sno number(3) constraints pri_sno primary key,--主键约束
       sname varchar2(12) constraints nnull_sname not null,--非空约束
       ssex char(1 char) constraints check_ssex check(ssex in ('男','女')),--检查约束
       shireday date default(sysdate),--默认约束
       telephone varchar2(11) constraints uni_phone unique,--唯一约束
       cno number(3) references bd_class(cno)  --外键约束
)
--删除表
drop table bd_class cascade constraints;  --cascade constraints级联删除约束，删除主表的同时删除主外键约束关系
--修改字段名
alter table bd_student rename column sname to studentname;
---修改类型
alter table bd_student modify(studentname varchar2(30));
--添加列
alter table bd_student add money number(7,2) unique ;  --七位有效数字，其中2为是效数
--删除列
alter table bd_student drop column money;

2. DML语句

DML（数据操控语言）用于操作数据库对象中包含的数据，也就是说操作的单位是记录。DML语句包括以下几种语句：

• insert：向数据表插入一条记录，语法：

insert into 表名 values(所有字段的值列表);--添加一条数据,必须定义所有字段值
insert into 表名(字段1,字段2...) values(指定字段的值列表);--添加一条数据,为指定的字段赋值

• delete：删除数据表中的一条或多条记录，语法：

delete from 数据源;--删除数据源中的所有数据
delete from 数据源 where 条件;--满足条件的数据删除

• update：修改已存在表中的记录，语法：

update 表名 set 字段名 = 字段值，字段名 = 字段值...;--修改表中的所有数据的指定字段值
update 表名 set 字段名 = 字段值，字段名 = 字段值... where 条件;--修改一张表中的某些记录的某些数据

参考代码如下：

--添加所有需要的数据到dept表中
insert into dept values(50,'教学部','上海');
--添加指定的数据到dept表中
insert into dept(deptno,dname) values(60,'后勤部');
--删除dept表所有数据
delete from dept;
--删除dept表中deptno为50或60的数据
delete from dept where deptno in(50,60);
--修改emp表中所有数据的sal和deptno，sal加上100，deptno改为40
update emp set sal=sal+100,deptno = 40;
--修改emp表中deptno为30数据的sal和deptno，sal加上100，deptno改为40
update emp set sal=sal+100,deptno = 40 where deptno = 30;
--删除从表中的数据可直接删除
delete from emp where deptno = 30;
--如果删除的是主表中没有引用到从表的数据,可以直接删除
delete from dept where deptno = 40;

注：如果删除的是主表中引用到从表的数据，不可以直接删除，但可以有如下方法：

• 先删除从表中的关联数据，然后再删除主表数据
• 从表相关数据外键约束后添加 on delete cascade
• 从表相关数据外键约束后添加 null on delete set null

select查询语句练习题

根据SQL语句建立新的表，代码如下：

--学生表 
create table student(
sno varchar2(10) primary key, --学生编号字段为主键
sname varchar2(20),
sage number(2),
ssex varchar2(5)
);
--教师表
create table teacher(
tno varchar2(10) primary key, --教师编号字段为主键
tname varchar2(20)
);
--课程表
create table course(
cno varchar2(10) primary key, -- 课程编号字段为主键
cname varchar2(20),
tno varchar2(20)
);
--成绩表
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
constraint pk_sc primary key (sno,cno) --学生编号,课程字段为联合主键
);
/*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
commit;
/******************初始化教师表***********************/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
commit;
/***************初始化课程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002',' Java  Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004',' Oracle ','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007',' Javascript ','t002');
insert into course values ('c008','DIV+ CSS ','t001');
insert into course values ('c009',' PHP ','t003');
insert into course values ('c010','EJB3.0','t002');
commit;
/***************初始化成绩表***********************/
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');
commit;

根据新建的表有以下select查询语句练习题，题目及代码如下：

--1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
select sno
  from sc s1
 group by sno
having (select score
          from sc s2
         where cno = 'c001'
           and s2.sno = s1.sno) > (select score
                                     from sc s2
                                    where cno = 'c002'
                                      and s2.sno = s1.sno);
--2、查询平均成绩大于60分的同学的学号和平均成绩；
select *
  from (select sno, avg(score) pingjun from sc group by sno)
 where pingjun > 60;
--3、查询所有同学的学号、姓名、选课数、总成绩；
select sno, sname, count(cno), sum(score)
  from (select * from student s1 join sc s2 using (sno))
 group by sno, sname;
--4、查询姓“刘”的老师的个数；
select count(tname) from teacher where tname like '刘%';
--5、查询没学过“谌燕”老师课的同学的学号、姓名；
select sno, sname
  from student
 where sno not in
       (select sno
          from sc
         where cno in
               (select cno
                  from course
                 where tno = (select tno from teacher where tname = '谌燕')));
--6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
select s1.sno, s3.sname
  from (select sno, cno from sc where cno = 'c001') s1
  join (select sno, cno from sc where cno = 'c002') s2
    on s1.sno = s2.sno
  join student s3
    on s2.sno = s3.sno;
--7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；
select sno, sname
  from student
 where sno in
       (select sno
          from sc
         where cno in
               (select cno
                  from course
                 where tno = (select tno from teacher where tname = '谌燕')));
--8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
select sno, sname
  from student
 where sno in
       (select sno
          from sc s1
         group by sno
        having (select score
                 from sc s2
                where cno = 'c001'
                  and s2.sno = s1.sno) > (select score
                                           from sc s2
                                          where cno = 'c002'
                                            and s2.sno = s1.sno));
--9、查询所有课程成绩小于60分的同学的学号、姓名；
select sno, sname
  from student
 where sno in
       (select sno
          from (select sno, max(score) zuida from sc group by sno)
         where zuida < 60);
--10、查询没有学全所有课的同学的学号、姓名；
select sno, sname
  from student
 where sno in
       (select sno
          from sc
         group by sno
        having count(cno) = (select count(cno) from course));
--11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；
select sno, sname
  from student
 where sno in
       (select sno
          from sc
         where cno in (select cno from sc where sno = 's001'))
   and sno != 's001';
--12、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名；
select sno, sname
  from student
 where sno in
       (select sno
          from sc
         where cno in (select cno from sc where sno = 's001'))
   and sno != 's001';              
--13、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cno,max(score),min(score) from sc group by cno;
--14、查询不同老师所教不同课程平均分从高到低显示
select tname, cno, avg(score)
  from (select s1.tname, c.cno, s2.score
          from teacher s1, course c, sc s2
         where s1.tno = c.tno
           and c.cno = s2.cno)
 group by tname, cno order by avg(score) desc;
--15、查询每门课程被选修的学生数
select cno,count(sno) from (select c.cno,s.sno from course c left join sc s on c.cno = s.cno) group by cno;
--16、查询出只选修了一门课程的全部学生的学号和姓名
select sno,sname from student where sno in (select sno from sc group by sno having count(cno) = 1);
--17、查询男生、女生人数
select ssex,count(sno) from student group by ssex;
--18、查询姓“张”的学生名单
select sno,sname from student where sname like '张%';
--19、1981年出生的学生名单(注：student 表中Sage 列的类型是number)
select * from student where to_char(sysdate-sage*365,'yyyy') = '1981';
--20、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select cno,avg(score) from sc group by cno order by avg(score) asc,cno desc;
--21、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select s1.sno, s2.sname, s1.pingjun
  from (select sno, avg(score) pingjun
          from sc
         group by sno
        having avg(score) > 75) s1,
       student s2
 where s1.sno = s2.sno;
--22、查询课程名称为“J2SE”，且分数低于70的学生姓名和分数
select s1.sno, s2.sname, s1.score
  from (select *
          from sc
         where cno = (select cno from course where cname = 'J2SE')) s1,
       student s2
 where s1.sno = s2.sno
   and s1.score < 70;
--23、查询所有学生的选课情况；
select c.cno,s.sno from course c,sc s where c.cno = s.cno;
--24、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select *
  from (select s1.sname, c.cname, s2.score
          from student s1
          join sc s2
            on s1.sno = s2.sno
          join course c
            on c.cno = s2.cno)
 where score > 70;
--25、查询不及格的课程，并按课程号从大到小排列
select cno from sc where score<60 order by cno desc;
--26、查询课程编号为c001且课程成绩在80分以上的学生的学号和姓名；
select sno,sname from student where sno in (select sno from sc where cno='c001' and score>80);
--27、求选了课程的学生人数
select count(distinct sno) from sc;
--28、查询各个课程及相应的选修人数
select cno, count(sno)
  from (select s1.cno, s2.sno
          from (select cno from course) s1
          left join sc s2
            on s1.cno = s2.cno)
 group by cno;
--29、统计每门课程的学生选修人数。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select cno, count(sno)
  from (select s1.cno, s2.sno
          from (select cno from course) s1
          left join sc s2
            on s1.cno = s2.cno)
 group by cno
 order by count(sno) desc, cno asc;
--30、检索至少选修两门课程的学生学号
select sno from sc group by sno having count(cno)>1;
--31、查询全部学生都选修的课程的课程号和课程名
select cno, cname
  from course
 where cno in
       (select cno
          from sc
         group by cno
        having count(sno) = (select count(distinct sno) from sc));
--32、查询没学过“谌燕”老师讲授的任一门课程的学生姓名
select sname
  from student
 where sno not in
       (select sno
          from sc
         where cno in
               (select cno
                  from course
                 where tno = (select tno from teacher where tname = '谌燕')));
--33、查询有两门不及格课程的同学的学号及其平均成绩
select sno,avg(score) from sc group by sno having sno in (select sno from sc where score<60 group by sno having count(cno)>2);
--34、检索“c003”课程分数小于60，按分数降序排列的同学学号
select sno,score from sc where score<60 order by score desc;