数据库 105道题目整理与吐血总结

drop table PRODUCT cascade constraints;
create table PRODUCT
(
id NUMBER not null,    --主键
pcode VARCHAR2(20) not null,    --商品编号
pname VARCHAR2(20),    --商品名称
inprice NUMBER(7,2),    --进价
outprice NUMBER(7,2),    --售价
toma VARCHAR2(20),    --管理员名称
lastcou NUMBER,    --剩余库存数量
ptype VARCHAR2(5),    --商品类型
mark NUMBER,    --有效标志
createtime DATE    --生产日期
);
alter table PRODUCT
ADD CONSTRAINT pk_product primary key (ID);

insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (1, 'f-qq45', '香蕉', 2.8, 4, '张三', 12, 'a', 1, to_date('05-05-2017', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (2, 'f-56tt', '苹果', 3.4, 5, '张三', 33, 'a', 1, to_date('16-06-2016', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (3, 'f-332', '梨', 2.7, 4, '张三', 76, 'a', 1, to_date('23-09-2016', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (4, 'f-20', '猪肉', 14, 16, '张三', 56, 'b', 1, to_date('31-12-2013', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (5, 'f-qq46', '圆珠笔', 3, 5, '王五', 55, 'e', 1, to_date('14-09-2016', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (6, 'f-57tt', '樱桃', 8, null, '王五', 12, 'a', 1, to_date('22-05-2006', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (7, 'f-292', '三文鱼', 5, 8, '王五', 23, 'b', 1, to_date('31-07-2001', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (8, 'f-604', '螺丝刀', 12, 15, '王五', 72, 'c', 1, to_date('24-02-2013', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (9, 'f-qq47', '扳手', 14, 19, '王五', 90, 'c', 1, to_date('14-05-2015', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (10, 'f-58tt', '钳子', 10, 13, '张三', 97, 'c', 1, to_date('28-03-2012', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (11, 'f-916', '矿泉水', 2.5, 3, '李四', 44, 'd', 1, to_date('21-07-2012', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (12, 'f-1228', '杏仁露', 4, 5, '李四', 38, 'd', 1, to_date('22-02-2013', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (13, 'f-59tt', '钢笔', null, 43, '李四', 22, 'e', 1, to_date('30-09-2013', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (14, 'f-1540', '铅笔', .5, .8, '赵六', 72, 'e', 1, to_date('14-05-2017', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (15, 'f-1852', '大马哈鱼', 1.5, 2, '赵六', 76, 'b', 1, to_date('17-05-2012', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (16, 'f-2164', '鸡蛋', 2.7, 4, '赵六', 56, 'b', 1, to_date('14-09-2010', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (17, 'p-123h', '雪碧', 2, 3, '王五', 50, 'd', 1, to_date('01-05-2017', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (18, 'p-111h', '冰红茶', 2, 3, '王五', 40, 'd', 1, to_date('18-05-2017', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (19, 'p-18-h', '可乐', 2, 3, '王五', 30, 'd', 1, to_date('09-05-2017', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (20, 'f-qq46', '圆珠笔', 3, 5, '王五', 55, 'e', 1, to_date('14-09-2016', 'dd-mm-yyyy'));
commit;

select t.*, t.rowid from PRODUCT t
-- 使用insert into任意添加三条数据(数据信息自定)加到这个表中
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (21, 'f-qq451', '香j蕉', 2.8, 4, '张三', 12, 'a', 1, to_date('05-05-2017', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (22, 'p-18-ha', '可j乐', 2, 3, '王五', 30, 'd', 1, to_date('09-05-2017', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (23, 'f-qq463', '圆j笔', 3, 5, '王五', 55, 'e', 1, to_date('14-09-2016', 'dd-mm-yyyy'));
commit;
-- 删除'猪肉'的那条记录
delete from product p where p.pname='猪肉';
commit;
-- 查询商品编号重复的记录，并将它们删除
select p.pname,count(p.pcode) num from product p group by p.pname having count(p.pcode)>1

delete from product p
 where p.pname = (select p.pname
               from product p
              group by p.pname
             having count(p.pcode) > 1)
             
DELETE product t WHERE t.pname=( SELECT t.pname FROM product t GROUP BY t.pname HAVING COUNT(*) >=2)

-- 将'扳手'和'钳子'的商品名称分别改为'16号扳手'和'小号钳子', 并将这两个商品的管理人员都改为'赵六'
update product p set p.pname='16号扳手' ,p.toma ='赵六' where p.pname='扳手'
update product p set p.pname='小号钳子', p.toma ='赵六' where p.pname='钳子'
-- 查询商品类型为'a'的所有商品, 并按照售价由大到小排序
select p.* from PRODUCT p where p.ptype='a' order by p.outprice desc
-- 查询王五负责的c类商品
select p.* from PRODUCT p where p.toma='王五' and p.ptype='c'
-- 查询剩余数量小于50的所有商品
select p.* from PRODUCT p where p.lastcou<=50
-- 查询剩余数量在60-80之间的所有商品
select p.* from PRODUCT p where p.lastcou<=80 and p.lastcou>=60
-- 查询商品名称带'笔'的和带'鱼'的记录f
select p.* from PRODUCT p where p.pname like '%笔%' or p.pname like '%鱼%'
-- 查询商品编号中带'tt'的记录
select p.* from product p where p.pcode like '%tt%'
-- 查询商品类型为a, d, c的所有商品
select p.* from PRODUCT p where p.ptype='a' or p.ptype='b' or p.ptype='c'
-- 查询整个表中每一类商品的剩余数量, 并按照剩余数由大到小排序
select p.ptype, sum(p.lastcou) from PRODUCT p group by p.ptype order by sum(p.lastcou)  desc
-- 查询所有'e'类型商品, 并按照剩余库存数量排序
select p.* from PRODUCT p where p.ptype='e' order by p.lastcou desc
-- 查询管理商品数少于4的管理员名称和管理的商品数量
select p.toma,count(p.pcode) from PRODUCT p  group by p.toma having count(p.pcode)<4
-- 查询所有的管理员名称和其手上所管理的所有商品数量
select p.toma,count(p.pcode) from PRODUCT p  group by p.toma
-- 查询所有的管理员名称和其手上所管理的所有'a'类商品名称的数量
select p.toma,count(p.pcode) from PRODUCT p where p.ptype='a'  group by p.toma
-- 查询所有商品中剩余数量最少的一个
select p.lastcou from product p
select p.pname,p.lastcou from PRODUCT p where p.lastcou<= all(select p.lastcou from product p)
select p.pname,p.lastcou from PRODUCT p where p.lastcou=(select min(p.lastcou) from product p)
-- 查询生产日期在2015/5/31之前的数据
select p.* from PRODUCT p where p.createtime <to_date('2015-05-31','yyyy-mm-dd')
-- 假设所有商品的有效期是一年, 以当前2017-06-01为标准, 查询所有商品的信息, 并且加一列标注是否过期
select p.*,
       case
         when (p.createtime - to_date('2017-06-01', 'yyyy-mm-dd')) > -365 then
          '未过期'
         when (p.createtime - to_date('2017-06-01', 'yyyy-mm-dd')) < -365 then
          '过期'
       end
  from PRODUCT p

update PRODUCT p set  p.isnodate='未过期' where (to_date('2017-06-01', 'yyyy-mm-dd')-p.createtime)<365
update PRODUCT p set  p.isnodate='过期' where (to_date('2017-06-01', 'yyyy-mm-dd')-p.createtime)>365

update PRODUCT p
   set case when(to_date('2017-06-01', 'yyyy-mm-dd') - p.createtime) > 365 then p.isnodate = '过期' when(to_date('2017-06-01', 'yyyy-mm-dd') - p.createtime) < 365 then p.isnodate = '未过期' end
    

-- 将所有过期的商品的有效标志改为0
update PRODUCT p set p.mark = 0 where (to_date('2017-06-01', 'yyyy-mm-dd')-p.createtime)>365
update PRODUCT p set p.mark = 0 where p.isnodate='过期'
-- 查询所有商品中还有一个月就过期的商品(一个月按照30天计算)
select p.*
  from PRODUCT p
 where (to_date('2017-06-01', 'yyyy-mm-dd') - p.createtime) < 365
   and (to_date('2017-06-01', 'yyyy-mm-dd') - p.createtime) > 335
-- 查询每个管理员所管理的商品的平均进价价格和售价价格
select p.toma,avg(nvl(p.inprice,0)),avg(nvl(p.outprice,0)) from product p group by p.toma
-- 查询每个管理员手上的商品的利润的平均值
select p.toma,avg(nvl(p.outprice,0)-nvl(p.inprice,0)) from product p group by p.toma
-- 查询假设每个管理员把所有商品售完后各自的利润总和
select p.toma ,sum((p.outprice-p.inprice)*p.lastcou) from product p group by p.toma 
-- 查询出利润总和最高的管理员的名称, 以及利润
--查询
select s.*
  from (select p.toma sname, sum((p.outprice - p.inprice) * p.lastcou) lirun
          from product p0
         group by p.toma) s
--2
select max(s1.lirun) from (select s.*
  from (select p.toma, sum((p.outprice - p.inprice) * p.lastcou) lirun
          from product p
         group by p.toma) s) s1 
--最终版
select * from (select s.*
  from (select p.toma sname, sum((p.outprice - p.inprice) * p.lastcou) lirun
          from product p
         group by p.toma) s
) cxs where cxs.lirun=(select max(s1.lirun) from (select s.*
  from (select p.toma, sum((p.outprice - p.inprice) * p.lastcou) lirun
          from product p
         group by p.toma) s) s1 )

--表结构：
drop table student;
drop table course;
drop table score;
drop table teacher;

CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL, 
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL, 
SBIRTHDAY DATE,
CLASS NUMBER NOT NULL);

CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL, 
CNAME VARCHAR(10) NOT NULL, 
TNO VARCHAR(10) NOT NULL);

CREATE TABLE SCORE 
(SNO VARCHAR(3) NOT NULL, 
CNO VARCHAR(5) NOT NULL, 
DEGREE NUMBER NOT NULL);

CREATE TABLE TEACHER 
(TNO VARCHAR(3) NOT NULL, 
TNAME VARCHAR(4) NOT NULL, 
TSEX VARCHAR(2) NOT NULL, 
TBIRTHDAY DATE NOT NULL, 
PROF VARCHAR(6), 
DEPART VARCHAR(10) NOT NULL);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,to_date('1977-09-01','yyyy-mm-dd'),95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,to_date('1975-10-02','yyyy-mm-dd'),95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,to_date('1976-01-23','yyyy-mm-dd'),95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,to_date('1976-02-20','yyyy-mm-dd'),95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,to_date('1975-02-10','yyyy-mm-dd'),95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,to_date('1974-06-03','yyyy-mm-dd'),95031);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李诚','男',to_date('1958-12-02','yyyy-mm-dd'),'副教授','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','男',to_date('1969-03-12','yyyy-mm-dd'),'讲师','电子工程系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'王萍','女',to_date('1972-05-05','yyyy-mm-dd'),'助教','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'刘冰','女',to_date('1977-08-14','yyyy-mm-dd'),'助教','电子工程系');

select * from course;
--1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select s.sname,s.ssex,s.class from student s;
--2、 查询教师所有的单位即不重复的Depart列。
SELECT distinct t.depart FROM teacher t ;
--3、 查询Student表的所有记录。                                                                                    
select s.* from student;
--4、 查询Score表中成绩在60到80之间的所有记录
select s.* from score s where s.degree between 60 and 80;
--5、 查询Score表中成绩为85，86或88的记录。
select s.* from score s where s.degree in (85,86,88);
--6、 查询Student表中“95031”班或性别为“女”的同学记录。
select s.* from student s where s.class= '95031' or s.ssex='女';
--7、 以Class降序查询Student表的所有记录。
select s.* from student s order by s.class desc;
--8、 以Cno升序、Degree降序查询Score表的所有记录。
select s.* from score s order by s.cno ,degree desc;
--9、 查询“95031”班的学生人数。
select s.* from student s where s.class='95031';
--10、查询Score表中的最高分的学生学号和课程号。
select s.sno,s.cno from score s where s.degree=(select max(s1.degree) from score s1);
--11、查询‘3-105’号课程的平均分。
select avg(s.degree) from score s where s.cno='3-105';
--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select s.*
  from (select s1.cno, count(s1.cno) shuliang from score s1 group by s1.cno) s
 where s.shuliang >= 5
   and s.cno like '3%';
--13、查询最低分大于70，最高分小于90的Sno列。
select s.sno,s.degree from score s where s.degree>70 and s.degree<90;
--14、查询所有学生的Sname、Cno和Degree列。
select s.sname,sc.cno,sc.degree from student s left join score sc on s.sno=sc.sno;
--15、查询所有学生的Sno、Cname和Degree列。
select s.sno,c.cname,s.degree from score s left join course c on s.cno=c.cno ;
--16、查询所有学生的Sname、Cname和Degree列。
select s.sname, c.cname, c.degree
  from student s
  left join (select s.sno, c.cname, s.degree
               from score s
               left join course c
                 on s.cno = c.cno) c
    on s.sno = c.sno;

--17、查询“95033”班所选课程的平均分。
select avg(cx.degree) from (select * from score s left join student st on s.sno=st.sno) cx where cx.class=95033 ;
--18、假设使用如下命令建立了一个grade表：
create table grade
(low number,
upp number,
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');
commit;
--现查询所有同学的Sno、Cno和rank列。
select  s.sno,s.cno,g.rank from grade g join score s on s.degree between g.low and g.upp;

select s.sno,s.cno,g.rank from score s,grade g where s.degree between g.low and g.upp;

--19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select s.*
  from score s
 where s.cno = '3-105'
   and s.degree > (select sc.degree
                     from score sc
                    where sc.sno = 109
                      and sc.cno = '3-105');
--20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。

??
select *
  from score s
 where s.degree < (select MAX(sc.degree) from score sc where s.cno = sc.cno)
   and s.sno in (select s1.sno from score s1 group by s1.sno having count(*) > 1);
--21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select s.*
  from score s
 where s.cno = '3-105'
   and s.degree > (select sc.degree
                     from score sc
                    where sc.sno = 109
                      and sc.cno = '3-105');
--22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select s.sno, s.sname, s.sbirthday
   from student s
  where to_char(sbirthday, 'yyyy') =
        (select to_char(sbirthday, 'yyyy') from student where sno = '108');
--23、查询“张旭“教师任课的学生成绩。
select s.*
  from score s
 where s.cno = (select c.cno
                  from course c
                  left join teacher t
                    on c.tno = t.tno
                 where t.tname = '张旭');
--24、查询选修某课程的同学人数多于5人的教师姓名。
select s.cno,count(*) rs from score s group by s.cno;
select * from (select s.cno,count(*) rs from score s group by s.cno) s1 left join  course c on s1.cno=c.cno ;
select *
  from teacher t
  left join (select *
               from (select s.cno, count(*) rs from score s group by s.cno) s1
               left join course c
                 on s1.cno = c.cno) s2
    on t.tno = s2.tno
 where s2.rs > 5;
--25、查询95033班和95031班全体学生的记录。
select s.* from student s where s.class in(95033,95031);
--26、查询存在有85分以上成绩的课程Cno.
select distinct  s.cno from score s where s.degree>85
--27、查询出“计算机系“教师所教课程的成绩表。
select t.tname,c.cname from teacher t left join course c on c.tno=t.tno where t.depart='计算机系' ;

select s.sno, s.cno, s.degree
  from score s
 where s.cno in
       (select c.cno
          from course c
         where c.tno in (select t.tno from teacher t where t.depart = '计算机系'))
--28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

??

select t.tname, t.prof
  from teacher t
 where t.prof not in (select te.prof from teacher te where t.depart not in te.depart);
--29、查询选修编号为“3-105“且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select s.cno, s.cno, s.degree
  from score s
 where (select sc.degree
          from score sc
         where sc.cno = '3-105'
           and sc.sno = s.sno) >
       (select c.degree
          from score c
         where c.cno = '3-245'
           and c.sno = s.sno)
 order by s.degree desc;
 
select  
--30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”的同学的Cno、Sno和Degree.
select s.cno, s.sno, s.degree
  from score s
 where (select s1.degree from score s1 where s1.cno = '3-105' and s.sno=s1.sno) >
       (select s2.degree from score s2 where s2.cno = '3-245' and s.sno=s2.sno);
--31、查询所有教师和同学的name、sex和birthday.
select s.sname, s.ssex, s.sbirthday
  from student s
union
select t.tname, t.tsex, t.tbirthday
  from teacher t;
--32、查询所有“女”教师和“女”同学的name、sex和birthday.
select s.sname, s.ssex, s.sbirthday
  from student s where s.ssex='女'
union
select t.tname, t.tsex, t.tbirthday
  from teacher t where t.tsex='女';
--33、查询成绩比该课程平均成绩低的同学的成绩表。
select Sno,Cno,Degree from Score a where a.Degree<(select AVG(Degree) from Score b where a.Cno=b.Cno)
--34、查询所有任课教师的Tname和Depart.
select t.tname,t.depart from teacher t ;
--35、查询所有未讲课的教师的Tname和Depart. 
select t1.tname, t1.depart
  from teacher t1
 where t1.tname not in
       (select t.tname from course c, teacher t where c.tno = t.tno);
--36、查询至少有2名男生的班号。
select s.class,count(s.class) from student s where s.ssex='男' group by s.class ;
--37、查询Student表中不姓“王”的同学记录。
select s.* from student s where s.sname not like '王%';
--38、查询Student表中每个学生的姓名和年龄。
select s.sname,to_char(sysdate，'yyyy')-to_char(s.sbirthday,'yyyy') from  student s ;
--39、查询Student表中最大和最小的Sbirthday日期值。
select min(s.sbirthday) 出生最早,max(s.sbirthday) 出生最晚 from  student s;
--40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select s.* from student s order by s.class desc,s.sbirthday;
--41、查询“男”教师及其所上的课程。
select t.*,c.cname from course c left join teacher t  on c.tno=t.tno where t.tsex='男';
--42、查询最高分同学的Sno、Cno和Degree列。
select s.* from score s where s.degree=(select max(s1.degree) from score s1 );
--43、查询和“李军”同性别的所有同学的Sname.
select st.sname from student st where st.ssex=(select s.ssex from student s where s.sname='李军');
--44、查询和“李军”同性别并同班的同学Sname.
select st.sname
  from student st
 where st.ssex = (select s.ssex from student s where s.sname = '李军')
   and st.class = (select s1.class from student s1 where s1.sname = '李军');
--45、查询所有选修“计算机导论”课程的“男”同学的成绩表
select s.*
  from score s
 where s.cno = (select c.cno from course c where c.cname = '计算机导论')
   and s.sno in (select st.sno from student st where st.ssex = '男');

drop table Z_COURSE cascade constraints;
drop table Z_STUDENT cascade constraints;
drop table Z_STU_COUR cascade constraints;

create table Z_COURSE
(
  id          NUMBER not null,
  cour_code   VARCHAR2(20),
  cour_name   VARCHAR2(30),
  p_cour_code VARCHAR2(20)
);
comment on column Z_COURSE.cour_code
  is '课程代码';
comment on column Z_COURSE.cour_name
  is '课程名称';
comment on column Z_COURSE.p_cour_code
  is '父级课程代码';
alter table Z_COURSE
  add constraint PK_Z_COURSE primary key (ID);
alter table Z_COURSE
  add constraint UK_Z_COURSE unique (COUR_CODE);

create table Z_STUDENT
(
  id       NUMBER not null,
  name     VARCHAR2(20),
  code     VARCHAR2(20),
  sex      CHAR(1),
  birthday DATE,
  major    VARCHAR2(20),
  note     VARCHAR2(300)
);
comment on column Z_STUDENT.name
  is '学生姓名';
comment on column Z_STUDENT.code
  is '学生学号';
comment on column Z_STUDENT.sex
  is '性别';
comment on column Z_STUDENT.birthday
  is '生日';
comment on column Z_STUDENT.major
  is '专业';
comment on column Z_STUDENT.note
  is '备注';
alter table Z_STUDENT
  add constraint PK_Z_STUDENT primary key (ID);

create table Z_STU_COUR
(
  id        NUMBER not null,
  stu_code  VARCHAR2(20),
  cour_code VARCHAR2(20),
  agree     NUMBER(4,1)
);
comment on column Z_STU_COUR.stu_code
  is '学生学号';
comment on column Z_STU_COUR.cour_code
  is '课程代码';
comment on column Z_STU_COUR.agree
  is '课程分数';

alter table Z_STU_COUR
  add constraint PK_Z_STU_COURT primary key (ID);

insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (1, 'LAU-100', '汉语言文学专业', null);
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (2, 'C-LAU-101', '语言学概论', 'LAU-001');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (3, 'C-LAU-102', '现代汉语', 'LAU-001');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (4, 'C-LAU-103', '中国当代文学史', 'LAU-001');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (5, 'C-LAU-104', '大学英语', 'LAU-001');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (6, 'NEWS-100', '国际新闻专业', null);
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (7, 'C-NEWS-101', '新闻采访', 'NEWS-100');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (8, 'C-NEWS-102', '报纸编辑', 'NEWS-100');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (9, 'C-NEWS-103', '电视新闻', 'NEWS-100');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (10, 'HIS-121', '历史学专业', null);
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (11, 'C-HIS-335', '中国古代史', 'HIS-121');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (12, 'C-HIS-336', '世界古代史', 'HIS-121');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (13, 'C-HIS-337', '中国近代史', 'HIS-121');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (14, 'ADV-609', '广告学专业', null);
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (15, 'C-ADV-239', '广告文案写作', 'ADV-609');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (16, 'C-ADV-240', '基础美术', 'ADV-609');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (17, 'C-ADV-241', '平面广告设计与制作', 'ADV-609');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (18, 'C-ADV-242', '市场营销学', 'ADV-609');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (19, 'C-ADV-243', '大众传播学', 'ADV-609');
commit;
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (1, '陈迪', 'stu-1011', '1', to_date('14-04-1993', 'dd-mm-yyyy'), 'LAU-100', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (2, '肖东菁', 'stu-1014', '1', to_date('15-02-1992', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (3, '汪佳丽', 'stu-1017', '2', to_date('16-08-1990', 'dd-mm-yyyy'), 'NEWS-100', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (19, '车晓', 'stu-1042', '1', to_date('18-03-1990', 'dd-mm-yyyy'), 'ADV-609', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (5, '王聪', 'stu-1023', '1', to_date('18-03-1990', 'dd-mm-yyyy'), 'ADV-609', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (6, '李璇', 'stu-1026', '2', to_date('19-05-1991', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (7, '马舒滟', 'stu-1029', '1', to_date('20-01-1990', 'dd-mm-yyyy'), 'NEWS-100', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (20, '张光北', 'stu-1018', '1', to_date('15-02-1992', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (9, '徐丹', 'stu-1035', '2', null, 'NEWS-100', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (11, '田野', 'stu-1041', '1', null, 'ADV-609', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (12, '彭亚光', 'stu-1044', '2', to_date('25-11-1990', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (14, '黄欢', 'stu-1050', '1', to_date('27-06-1990', 'dd-mm-yyyy'), 'ADV-609', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (15, '庞琳', 'stu-1053', '1', to_date('28-05-1989', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (16, '张子腾', 'stu-1056', '2', to_date('18-03-1990', 'dd-mm-yyyy'), 'LAU-100', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (17, '姜春阳', 'stu-1059', '2', to_date('30-05-1988', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (18, '陈冰若', 'stu-1062', '1', to_date('31-10-1990', 'dd-mm-yyyy'), 'NEWS-100', '1');
commit;
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (1, 'stu-1011', 'C-LAU-101', 35);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (2, 'stu-1011', 'C-LAU-102', 65);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (3, 'stu-1011', 'C-LAU-103', 25);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (4, 'stu-1011', 'C-LAU-104', 97);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (5, 'stu-1014', 'C-HIS-335', 53);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (6, 'stu-1014', 'C-HIS-336', 35);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (7, 'stu-1014', 'C-HIS-337', 65);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (8, 'stu-1017', 'C-NEWS-101', 25);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (9, 'stu-1017', 'C-NEWS-102', 65);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (10, 'stu-1017', 'C-NEWS-103', 25);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (11, 'stu-1023', 'C-ADV-239', 33);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (12, 'stu-1023', 'C-ADV-240', 42);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (13, 'stu-1023', 'C-ADV-241', 66);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (14, 'stu-1023', 'C-ADV-242', 69);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (15, 'stu-1023', 'C-ADV-243', 82);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (16, 'stu-1026', 'C-HIS-335', 37);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (17, 'stu-1026', 'C-HIS-336', 77);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (18, 'stu-1026', 'C-HIS-337', 34);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (19, 'stu-1029', 'C-NEWS-101', 35);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (20, 'stu-1029', 'C-NEWS-102', 75);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (21, 'stu-1029', 'C-NEWS-103', 32);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (22, 'stu-1035', 'C-NEWS-101', 19);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (23, 'stu-1035', 'C-NEWS-102', 11);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (24, 'stu-1035', 'C-NEWS-103', 93);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (25, 'stu-1041', 'C-ADV-239', 99);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (26, 'stu-1041', 'C-ADV-240', 88);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (27, 'stu-1041', 'C-ADV-241', 89);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (28, 'stu-1041', 'C-ADV-242', 63);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (29, 'stu-1041', 'C-ADV-243', 44);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (30, 'stu-1044', 'C-HIS-335', 73);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (31, 'stu-1044', 'C-HIS-336', 65);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (32, 'stu-1044', 'C-HIS-337', 25);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (33, 'stu-1050', 'C-ADV-239', 33);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (34, 'stu-1050', 'C-ADV-240', 42);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (35, 'stu-1050', 'C-ADV-241', 25);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (36, 'stu-1050', 'C-ADV-242', 33);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (37, 'stu-1050', 'C-ADV-243', 42);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (38, 'stu-1053', 'C-HIS-335', 66);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (39, 'stu-1053', 'C-HIS-336', 69);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (40, 'stu-1053', 'C-HIS-337', 35);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (41, 'stu-1056', 'C-LAU-101', 65);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (42, 'stu-1056', 'C-LAU-102', 25);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (43, 'stu-1056', 'C-LAU-103', 97);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (44, 'stu-1056', 'C-LAU-104', 53);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (45, 'stu-1059', 'C-HIS-335', 35);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (46, 'stu-1059', 'C-HIS-336', 25);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (47, 'stu-1059', 'C-HIS-337', 97);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (48, 'stu-1062', 'C-NEWS-101', 32);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (49, 'stu-1062', 'C-NEWS-102', 19);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (50, 'stu-1062', 'C-NEWS-103', 11);
commit;

--题目
--练习开始(共35道题)：
--(一)简单查询
--查询学生表中的所有内容
select s.* from z_student s ;
--查询学生表中的姓名，专业
select s.name,s.major from z_student s;
--查询学生表中各种专业
select s.major from z_student s group by s.major;
--查询表中前五个同学的姓名，专业
select s.name,s.major,rownum from z_student s where rownum<6; 
--查询表中前五个同学的姓名，专业,按照学号正序排列
select r.*, rownum
  from (select * from z_student st order by st.code) r
 where rownum < 6
 order by r.code;
--查询各学生的学号和姓名以及截止到现在各学生的年龄
select s.code,s.name,trunc((sysdate - s.birthday)/365)||'岁' from z_student s;
--查询专业为国际新闻的学生的所有信息
select c.cour_code from z_course c where c.cour_name='国际新闻专业'; 

select s.*
  from z_student s
 where s.major = (select c.cour_code
                    from z_course c
                   where c.cour_name = '国际新闻专业');
--查询1991年出生的学生姓名和专业
select s.name,s.major from z_student s where to_char(s.birthday,'yyyy')=1991;
--查询历史, 广告, 国际新闻专业的所有学生信息
select *
  from z_student s
  left join z_course c
    on s.major = c.cour_code
 where c.cour_name like '%历史%'
    or c.cour_name like '%新闻%'
    or c.cour_name like '%广告%';
--查询姓名是两个字的姓王, 姓陈, 姓李的所有学生信息
select s.*
  from z_student s
 where s.name like '王_'
    or s.name like '李_'
    or s.name like '陈_';
--查询没有学分的学生信息
select st.*
  from z_student st
 where st.code not in (select distinct sc.stu_code from Z_STU_COUR sc);
 
select st.*
  from z_student st
 where not exists(select sc.* from Z_STU_COUR sc where sc.stu_code=st.code);
--查询计算机专业的没有记录生日的学生信息
select s.* from z_student s where s.birthday is null;   ?and '计算机'?
--查询按照专业降序，学号升序排列所有学生信息
select s.* from z_student s order by s.major desc, s.code;
--查询表中前三个的学生的姓名，专业，到现在的年龄并按照年龄降序排列
select s.name,
       s.major,
       trunc((sysdate - s.birthday) / 365) || '岁' age,
       rownum
  from z_student s
 where rownum < 4
 order by age;

--（二）数据汇总
--查询所有的课程代码和每个课程的平均成绩并按照课程号排序，再剔除课程代码不是C-ADV-240的课程
select sc.cour_code, avg(sc.agree)
  from Z_STU_COUR sc
 where sc.cour_code != 'C-ADV-240'
 group by sc.cour_code
 order by sc.cour_code;
--查询出每个课程代码的最高分和最低分
select sc.cour_code, max(sc.agree),min(sc.agree)
  from Z_STU_COUR sc
 group by sc.cour_code;
--查询学号为stu-1023的学生的各课成绩
select sc.*
  from Z_STU_COUR sc
 where sc.stu_code='stu-1023';
--查询出历史学专业有多少人
select c.p_cour_code from Z_COURSE c where c.cour_name='历史学专业';

select s.major, count(s.major)
  from z_student s
 where s.major = (select c.cour_code
                    from Z_COURSE c
                   where c.cour_name = '历史学专业')
 group by s.major;
--查询各专业各有多少人
select s.major,count(*) from Z_STUDENT s group by s.major;
--查询出各专业里男女生各有多少人
select  s.major,count(select s1.* from Z_STUDENT s1 where s1.sex='1' and s.major=s1.major;) from Z_STUDENT s group by s.major;

select s.major,s.sex,count(*) from  Z_STUDENT s group by s.major,s.sex;

select s.major,
       sum(decode(sex, 1, 1, 2, 0)) sex1,
       sum(decode(sex, 1, 0, 2, 1)) sex2
  from Z_STUDENT s
 group by s.major;
--查询出学生所有课程的平均分在50分以上的学生学号
select sc.*
  from (select c.stu_code, avg(c.agree) avga
          from Z_STU_COUR c
         group by c.stu_code) sc
 where sc.avga > 50;
 
select c.stu_code, avg(c.agree) avga
          from Z_STU_COUR c
         group by c.stu_code
         having avg(c.agree)>50;
--查询每个学生有几门课成绩高于80分
select sc.cst, count(sc.cst)
  from (select c.stu_code cst, c.agree cag
          from Z_STU_COUR c
         where c.agree > 80) sc
 group by sc.cst;
 --（三）连接查询
--查询所有学生的学号，姓名，专业课程号，成绩
select st.code, st.name, sc.cour_code, sc.agree
  from Z_STU_COUR sc
  left join Z_STUDENT st
    on sc.stu_code = st.code;
--查询课程号为C-HIS-336的学生的姓名和成绩
select st.name, sc.cour_code, sc.agree
  from Z_STU_COUR sc
  left join Z_STUDENT st
    on sc.stu_code = st.code
 where sc.cour_code='C-HIS-336';
--查询选修广告学专业的基础美术这门课程的学生学号，姓名，成绩
select st.code, st.name, sc.agree
  from Z_STUDENT st
  left join Z_STU_COUR sc
    on st.code = sc.stu_code
 where sc.cour_code =
       (select co.cour_code from Z_COURSE co where co.cour_name = '基础美术');
--查询选修课程号为C-NEWS-101这门课的所有学生信息和成绩
select st.*, sc.agree
  from Z_STUDENT st
  left join Z_STU_COUR sc
    on st.code = sc.stu_code
 where sc.cour_code = 'C-NEWS-101';
--查询生日是同一天的学生信息
select st.*
  from Z_STUDENT st
 where st.birthday in (select st1.birthday
                         from Z_STUDENT st1
                        group by st1.birthday
                       having count(st1.birthday) > 1);
--查询各课的课程名，课程号，每门课所有学生的平均成绩
select co.cour_name, co.cour_code, avg(sc.agree)
  from Z_STU_COUR sc
  left join Z_COURSE co
    on sc.cour_code = co.cour_code
 group by co.cour_name, co.cour_code;
--查询所有学生的平均成绩
select sc.stu_code,avg(sc.agree) from Z_STU_COUR sc group by sc.stu_code;
--查询每个专业的每个课程的平均分是多少
select co.cour_code, avg(sc.agree)
  from Z_STU_COUR sc
  left join Z_COURSE co
    on sc.cour_code = co.cour_code
 group by co.cour_code;
--查询平均分大于40分的国际新闻专业的每个学生姓名，学号和各课的平均分
select stu.name stuname,cx.avge
  from (select sc.stu_code, avg(sc.agree) avge
          from Z_STU_COUR sc
          left join Z_STUDENT st
            on sc.stu_code = st.code
         group by sc.stu_code
        having avg(sc.agree) > 40) cx
  left join Z_STUDENT stu
    on cx.stu_code = stu.code
 where stu.major in (select cou.cour_code
                       from Z_COURSE cou
                      where cou.cour_name = '国际新闻专业');
--（四）子查询的使用
--查询平均分大于等于课程号为C-ADV-239的课程号和平均分
select cx.cxdo, cx.cxavg
  from (select sc.cour_code cxdo, avg(sc.agree) cxavg
          from Z_STU_COUR sc
         group by sc.cour_code) cx
 where cx.cxavg >= (select avg(sc1.agree)
                     from Z_STU_COUR sc1
                    where sc1.cour_code = 'C-ADV-239');
--查询历史学专业下的课程的及格率(课程得分在50以上的人数除以总人数)
select scc.cour_code, sccc.couu / scc.cou
  from (select sc.cour_code, count(sc.cour_code) couu
          from z_stu_cour sc
         where sc.agree> 50
         group by sc.cour_code) sccc,
       (select sc.cour_code, count(sc.cour_code) cou
          from z_stu_cour sc
         group by sc.cour_code) scc
 where sccc.cour_code = scc.cour_code
   and scc.cour_code in
       (select c.cour_code
          from z_course c
         where c.p_cour_code =
               (select c.cour_code
                  from z_course c
                 where c.cour_name = '历史学专业'));
--查询没有选修C-NEWS-101这门课程的学生信息和课程信息
select *
  from Z_STU_COUR sc
  left join Z_STUDENT st
    on sc.stu_code = st.code
 where sc.cour_code != 'C-NEWS-101';
--查询没有课程成绩的学生信息
select st.*
  from Z_STUDENT st
 where st.code not in (select distinct sc.stu_code from Z_STU_COUR sc);