数据库高级查询题目集

一、销售信息管理系统实现要求

--员工信息表
create table tbEmp (
       eid int primary key,
       ename varchar2(6),
       esex  char(2)  default '男' 
             constraints CK_tbEmp_esex check(esex='男' or esex='女'),
       eage  int  
             constraints CK_tbEmp_eage check(eage>=18 or eage<=50),
       eaddr varchar2(20) default '地址不详'
);
--添加非空约束
alter table tbEmp modify eid not null;
alter table tbEmp modify ename not null;
alter table tbEmp modify eage not null;
--添加唯一约束
alter table tbEmp add constraints CK_tbEmp_ename unique(ename);
--员工编号从10000自增
create sequence seq_eid start with 10000 increment by 1;


--产品信息表
create table tbProd(
       pid int primary key,
       ptype varchar2(10),
       pmark varchar2(20),
       pspec varchar2(20)
);
--添加非空约束
alter table tbProd modify  ptype not null;
--产品编号自增
create sequence seq_pid start with 1 increment by 1;


--销售信息表
create table tbSales(
       srno int primary key,
       eid int,
       pid int,
       pqty int
               constraints CK_tbSales_pqty check(pqty>0),
       pamount int
               constraints CK_tbSales_pamount check(pamount>0),
       sdate date
);


--srno 自增
create sequence seq_srno start with 10100 increment by 1;
--添加eid外键约束
alter table tbSales add constraints FK_tbSales_eid foreign key(eid) references tbEmp(eid);
--添加pid外键约束
alter table tbSales add constraints FK_tbSales_pid foreign key(pid) references tbProd(pid);
--添加非空约束
alter table tbSales modify pqty not null;
alter table tbSales modify pamount not null;


drop sequence seq_eid;
drop sequence seq_pid;
drop sequence seq_srno;


select * from  tbEmp;
select * from  tbProd;
select * from  tbSales;


drop table tbEmp;
drop table tbProd;
drop table tbSales;


--建关系


--插入数据
insert into tbEmp values(seq_eid.nextval,'张三','男',21,'湖南长沙');
insert into tbEmp values(seq_eid.nextval,'李四',default,22,'湖南衡阳');
insert into tbEmp values(seq_eid.nextval,'吴昊','男',20,'广东广州');
insert into tbEmp values(seq_eid.nextval,'小小','女',20,'湖南邵阳');
insert into tbEmp values(seq_eid.nextval,'乐乐','女',23,'广西桂林');
insert into tbEmp values(seq_eid.nextval,'潇潇','女',24,'湖南耒阳');
insert into tbEmp values(seq_eid.nextval,'注定','女',20,'湖南');




insert into tbProd values(seq_pid.nextval,'手机','oppo','R7');
insert into tbProd values(seq_pid.nextval,'手机','iphone','plus6');
insert into tbProd values(seq_pid.nextval,'笔记本','惠普','G560');
insert into tbProd values(seq_pid.nextval,'笔记本','戴尔','G360');
insert into tbProd values(seq_pid.nextval,'电视机','长虹','37寸');
insert into tbProd values(seq_pid.nextval,'电视机','熊猫','40寸');




insert into tbSales values(seq_srno.nextval,10000,1,100,200000,to_date('2015-1','yyyy-mm' ));
insert into tbSales values(seq_srno.nextval,10001,2,50,250000,to_date('2015-2','yyyy-mm'));
insert into tbSales values(seq_srno.nextval,10002,3,50,150000,to_date('2015-3','yyyy-mm'));
insert into tbSales values(seq_srno.nextval,10003,4,30,90000,to_date('2015-4', 'yyyy-mm'));
insert into tbSales values(seq_srno.nextval,10004,5,30,120000,to_date('2015-5', 'yyyy-mm'));
insert into tbSales values(seq_srno.nextval,10005,6,20,80000,to_date('2015-6','yyyy-mm'));
insert into tbSales values(seq_srno.nextval,10005,6,20,2500000,to_date('2015-6','yyyy-mm'));
insert into tbSales values(seq_srno.nextval,10001,2,50,2500000,to_date('2015-2','yyyy-mm'));




/*=======查询数据练习=========*/ 
--1.查询三表的数据--
select * from  tbEmp;
select * from  tbProd;
select * from tbSales;


--2.查询男员工名单-- 
select ename from tbEmp where esex='男';


---多表查询(不需要查询总销售量和总销售价值,只需显示商品的销售情况即可)
--1. 所有职员,所有商品的销售情况,包括:职员姓名,商品类别,商品品牌,商品规格,销售数量,销售价值,销售时间,按销售时间从高到低排列
select ename,ptype,pmark,pspec,pqty,pamount,sdate from tbEmp e,tbProd p,tbSales s where e.eid=s.eid and p.pid=s.pid order by sdate asc;




--2. 手机2月份的销售情况,包括:商品编号,商品品牌,商品规格,销售数量,销售价值,按销售数量从低到高排列
select p.pid,ptype,pmark,pspec,pqty,pamount,sdate from tbProd p,tbSales s 
where p.pid=s.pid and extract(MONTH from sdate)=2 order by pqty asc;


--3. 李四2月份的销售情况,包括:商品编号,销售数量,销售价值
select ename,pid,pqty,pamount,sdate from tbEmp e,tbSales s where e.eid=s.eid and ename='李四' 
and extract(MONTH from sdate)=2;




--4. 长虹29英寸艺术电视机的销售情况,包括:销售数量,销售价值,销售时间
select pqty,pamount,sdate,pmark,pspec from tbProd p,tbSales s where p.pid=s.pid and pmark='长虹'and pspec='37寸';


--5. 长沙男职员4月份的销售情况,包括:职员姓名,商品类别,商品品牌,商品规格,销售数量,销售价值
select ename,esex,eaddr,ptype,pmark,pspec,pqty,pamount,sdate from tbEmp e,tbProd p,tbSales s
where e.eid=s.eid and p.pid=s.pid and esex='男' and eaddr='湖南长沙';
--统计
--1. 总共有多少个职员？
select count(ename) from tbEmp;


--2. 长虹电视机被销售了多少次?
select count(pid) from  tbSales where pid=5;


--3. 年龄最小的女职员的年龄(年龄最小的女职员的姓名,年龄)   
select ename,eage,esex from tbEmp where eage=(select min(eage)from tbEmp) and esex='女';


--4. 笔记本单笔销售价值最高的单笔销售价值
select ptype,pqty,pamount from tbProd p,tbSales s where p.pid=s.pid and ptype='笔记本' 
and pamount=(select max(s.pamount) from tbSales s where s.pid=3 or s.pid=4);


--5. 熊猫电视机的每次销售的平均销售价值(熊猫电视机的平均销售价格)
select 
--6. 戴尔笔记本4月份的销售总额
select ptype,pmark,pamount,sdate from tbProd p,tbSales s where p.pid=s.pid and ptype='笔记本' and
pmark='戴尔' and extract(MONTH from sdate)=4;


--分组与合计
--1. 按类型,品牌统计商品的销售总数量,销售总数量低于5的不统计
select ptype,pmark,pqty from tbProd p,tbSales s where p.pid=s.pid and pqty>=5;


--2. 按品牌,规格统计笔记本的平均销售价格,按销售价格从高到低的顺序显示
select avg(pqty) from (select p.ptype,p.pmark,p.pspec,s.pqty from tbProd p,tbSales s
 where p.pid=s.pid and p.ptype='笔记本');
 
--3. 按姓名统计男职员3月份的销售总额,销售总额低于10000的不统计
select ename,sdate,total from tbEmp e,tbSales s,(select sum(s.pamount) total from tbSales s) a
 where e. eid=s.eid and extract(MONTH from sdate)=2 and total>10000;
 
 
--4. 找出销售总额最高的职员的姓名
select * from(select ename,s.pamount from tbEmp e,tbSales s where e.eid=s.eid order 
by s.pamount desc)where rownum=1;


select ename,s.pamount from tbEmp e,tbSales s where s.pamount in(select max(pamount)from tbSales) and e.eid=s.eid;
--5. 按姓名统计每个职员一共完成了多少笔销售业务
select ename,count(ename)from(select e.ename,e.eid from tbEmp e,tbSales s where e.eid=s.eid) group by ename;


--6. 找出完成销售业务笔数最多的职员的姓名和他完成的业务数
select ename,count(ename)from(select e.ename,e.eid from tbEmp e,tbSales s where e.eid=s.eid) 
group by ename order by count(ename) desc;


--7. 按性别统计职员的最大年龄和最小年龄
select max(eage),min(eage)  from tbEmp ;




--(8) 创建登录账号和数据库用户
-- 假定该数据库的用户是销售总监和员工，销售总监负责查看销售信息和管理员工信息，员工负责录入销售信息和产品信息，所以他们的权限分别是:
 -- 销售总监: 对员工表tbemp具备增、删、改、查的权限，对销售表tbsales和产品表tbprod只有查看的权限，不能修改
 -- 员工: 对tbsales和tbprod具备增、删、改、查的权限，对tbemp表只有查看的权限，不能修改
--a. 请创建sql server登录账号：销售总监账号 salesmanager, 员工账号 emp, 默认访问salesdb数据库
--b. 创建销售总监和员工对应的数据库用户，并授予相应权限
create user xszj identified by a;
create user emplyee identified by a;
grant create session to xszj;
grant create session to emplyee;
grant alter,select on scott.tbEmp to xszj;
grant select on scott.tbSales to xszj;
grant select on scott.tbProd to xszj;


grant alter,select on scott.tbProd to emplyee;
grant select on scott.tbSales to emplyee;


select * from scott.tbEmp;
select * from scott.tbSales;
select * from scott.tbProd;

二、商品销售数据库系统设计

--商品销售数据库
create table Article(
       商品号 varchar2(4) primary key,
       商品名 varchar2(16),
       单价   number(8,2),
       库存量 int
);


create table Customer(
       顾客号 varchar2(4) primary key,
       顾客名 varchar2(8),
       性别   varchar2(2) default '男'
              constraints CK_Customer_性别 check (性别='男' or 性别='女'),
       年龄  int
              constraints CK_Customer_年龄 check (年龄>10 and 年龄<100)
);


create table OrderIterm(
       顾客号  varchar2(4) ,
       商品号  varchar2(16),
       数量   int,
       购买价  number(8,2),
       日期  varchar2(20)
);
--添加商品号外键约束
alter table  OrderIterm add constraints FK_OrderItem_商品号 foreign key(商品号) references Article(商品号);
alter table OrderIterm add constraints FK_OrderItem_顾客号 foreign key(顾客号) references Customer(顾客号);


insert into Article values ('S001','计算机',5000,10);
insert into Article values ('S002','打印机',1000,12);
insert into Article values ('S003','洗衣机',800,10);
insert into Article values ('S004','电冰箱',1100,20);
insert into Article values ('S005','电视机',3000,10);


insert into Customer values ('G001','张三','男',29);
insert into Customer values ('G002','李四','女',25);
insert into Customer values ('G003','王武',default,31);
insert into Customer values ('G004','赵六','女',25);


insert into OrderIterm values ('G001','S001',2,4000,'2005-1-25');
insert into OrderIterm values ('G001','S002',1,800,'2005-1-25');
insert into OrderIterm values ('G001','S003',3,600,'2005-1-25');
insert into OrderIterm values ('G001','S004',1,880,'2005-1-25');
insert into OrderIterm values ('G002','S001',3,4500,'2005-2-25');
insert into OrderIterm values ('G003','S001',1,5000,'2005-1-1');
insert into OrderIterm values ('G003','S002',1,1000,'2005-1-1');


drop table Article;
drop table Customer;
drop table OrderIterm;


select * from Article;
select * from Customer;
select * from OrderIterm;




--2. 检索定购商品号为‘S001’的顾客号和顾客名。
select c.顾客号,顾客名,商品号 from Customer c,OrderIterm o where c.顾客号=o.顾客号 and 商品号='S001';


--3. 检索定购商品号为‘S001’或‘S002’的顾客号和顾客名。
select c.顾客号,顾客名,商品号 from Customer c,OrderIterm o where c.顾客号=o.顾客号 
and 商品号='S001' or 商品号='S002';  


--4. 检索至少定购商品号为‘S001’和‘S002’的顾客号。 
select distinct(顾客号) from OrderIterm  where 商品号='S001' and  
顾客号 in(select 顾客号 from OrderIterm  where 商品号='S002');
--5. 检索至少定购商品号为‘S001’和‘S002’的顾客号。(用自表连接方法) 
select * from Customer c inner join OrderIterm o on c.顾客号=o.顾客号 where 商品号 in
(select o.商品号 from OrderIterm o where 商品号='S001' or 商品号='S002')




--6. 检索没定购商品的顾客号和顾客名。
select 顾客号,顾客名 from (select distinct(c.顾客号),顾客名 from Customer c where c.顾客号 not in
(select distinct(o.顾客号) from OrderIterm o));select one.顾客号 from OrderIterm one,OrderIterm two where one.顾客号=two.顾客号 and
 one.商品号='S001' and two.商品号='S002';
--7. 检索一次定购商品号‘S001’商品数量最多的顾客号和顾客名。 
select * from (select c.顾客号,c.顾客名,a.商品名,a.商品号,o.数量 from Article a,Customer c,OrderIterm o where a.商品号=o.商品号 
and c.顾客号=o.顾客号 and a.商品号='S001'  order by o.数量 desc ) where rownum=1;
--8. 检索男顾客的人数和平均年龄。
select count(c.顾客号),avg(年龄) from Customer c where c.性别='男';
--9. 检索至少订购了一种商品的顾客数。
select distinct(顾客号) from OrderIterm where 顾客号 in(select 顾客号 from OrderIterm 
group by 顾客号 having count(商品号)>1 );
--10. 检索订购了商品的人次数。     ************************************************
select count(o.顾客号) from OrderIterm o where exists(select distinct(c.顾客号) from Customer c  where 
(select distinct(o.顾客号) from OrderIterm o ) );
--11. 检索顾客张三订购商品的总数量及每次购买最多数量和最少数量之差。  
select sum(数量),max(数量)-min(数量) from Orderiterm o,customer c where o.顾客号=c.顾客号 and c.顾客名='张三';
--12. 检索至少订购了3件商品的顾客号和顾客名及他们定购的商品次数和商品总数量，并按商品总数量降序排序。 
select o.顾客号,c.顾客名,count(o.顾客号) from Customer c inner join OrderIterm o on c.顾客号=o.顾客号
 where o.顾客号 in(select o.顾客号 from OrderIterm group by  o.顾客号 having  count(o.商品号)>=3);
--13. 检索年龄在30至40岁的顾客所购买的商品名及商品单价。 
select b.商品名,b.单价,b.年龄,b.商品号 from (select a.商品名,a.单价, a.商品号,c.年龄,o.顾客号 from Article a,Customer c,OrderIterm o where a.商品号=o.商品号 and c.顾客号=o.顾客号) b 
where b.年龄 between 30 and 40;
--14. 创建一个视图GM，字段包括：顾客号，顾客名和定购的商品名，日期和金额（金额=数量*单价）。指定用内连接方式做。
--15. 检索购买的商品的单价至少有一次高于或等于1000元的顾客号和顾客名。?
 select distinct(c.顾客号),o.顾客号 from OrderIterm o inner join Customer c on c.顾客号=o.顾客号  where  o.顾客号 in
  (select o.顾客号,a.单价 from  OrderIterm o,Article a group by o.顾客号,a.单价 having a.单价>=1000);
--16. 检索购买的购买价都高于或等于1000元的顾客号和顾客名。 
select c.顾客名,c.顾客号,o.购买价 from Customer c,OrderIterm o where c.顾客号=o.顾客号 and o.购买价>=1000;
--17. 检索女顾客购买的商品号，商品名和数量合计。 
select a.商品号,a.商品名,o.数量,c.性别 from Article a,Customer c,OrderIterm o where a.商品号=o.商品号
and c.顾客号=o.顾客号 and c.性别='女';
--18. 检索所有的顾客号和顾客名以及它们所购买的商品号。（包括没买商品的顾客）
select c.顾客号,c.顾客名,a.商品号 from Article a,Customer c,OrderIterm o where a.商品号=o.商品号 and c.顾客号=o.顾客号;
--19. 检索这样的顾客号,顾客名,他们定购了所有的商品  
select b.顾客号,count(b.顾客号) from (select o.顾客号,c.顾客名 from Customer c,OrderIterm o 
where c.顾客号=o.顾客号) b group by b.顾客号 where count>=4 ;
--20. 检索这样的顾客号，他们至少订购了顾客号为“G002”所订购的所有商品
select distinct(o.顾客号) from OrderIterm o where not exists(select 顾客号 from Customer where 顾客号 in
(select distinct (顾客号) from OrderIterm where 顾客号='G002')and not exists 
(select c.顾客号 from Customer c inner join OrderIterm o on c.顾客号=o.顾客号));
--21. 向Article表插入一条纪录。删除无人购买的商品。（检验一下刚插入的记录是否已被删除）
delete  from Article a where a.商品号 not in (select o.商品号 from OrderIterm o);
--22. 降低已售出的数量总合超过10件的商品单价为原价的95%。
update article set 单价=单价*0.95 where 商品号 in(select 商品号 from orderiterm group by 商品号 having sum(数量)>5)
--23. 建立约束：顾客的年龄必须大于18岁。
alter table  Customer  add  constraint CK_Age check (年龄>18);


三、学生表

--Student(S#,Sname,Sage,Ssex) 学生表 
create table student(
   Sno int primary key,
   Sname varchar2(50),
   Sage date,
   Ssex varchar2(50)
);
create sequence seq_sid start with 1 increment by 1;
drop table student;
drop sequence seq_sid;


--Teacher(T#,Tname) 教师表
create table teacher(
   Tno int primary key,
   Tname varchar2(50)
);
create sequence seq_tid start with 1 increment by 1;
drop table teacher;
drop sequence seq_tid;


--Course(C#,Cname,T#) 课程表 
create table course(
   Cno int primary key,
   Cname varchar2(50),
   Tno int constraint FK_Tno references teacher(Tno)
);
create sequence seq_cid start with 1 increment by 1;
drop table course;
drop sequence seq_cid;


--SC(S#,C#,score) 成绩表 
create table SC(
   Cno int constraint FK_Cno references course(Cno),
   Sno int constraint FK_Sno references student(Sno),
   score int 
);
drop table sc;




--数据
insert into student values(seq_sid.nextval,'张三',to_date('1981-6-5','YYYY-MM-DD'),'男');
insert into student values(seq_sid.nextval,'李四',to_date('1982-6-5','YYYY-MM-DD'),'女');
insert into student values(seq_sid.nextval,'王五',to_date('1981-6-5','YYYY-MM-DD'),'男');
insert into student values(seq_sid.nextval,'赵六',to_date('1981-6-5','YYYY-MM-DD'),'女');
insert into student values(seq_sid.nextval,'钱七',to_date('1981-6-5','YYYY-MM-DD'),'男');
insert into student values(seq_sid.nextval,'王八',to_date('1981-4-30','YYYY-MM-DD'),'男');
insert into student values(seq_sid.nextval,'周九',to_date('2012-6-5','YYYY-MM-DD'),'女');
insert into student values(seq_sid.nextval,'吴十',to_date('2012-6-6','YYYY-MM-DD'),'男');
insert into student values(seq_sid.nextval,'郑一',to_date('2012-6-6','YYYY-MM-DD'),'女');
insert into student values(seq_sid.nextval,'孙二',to_date('2012-6-5','YYYY-MM-DD'),'男');
insert into student values(seq_sid.nextval,'罗四',to_date('2012-6-5','YYYY-MM-DD'),'女');
insert into student values(seq_sid.nextval,'哈哈',to_date('2012-8-5','YYYY-MM-DD'),'女');
insert into student values(seq_sid.nextval,'DJ',to_date('2012-10-5','YYYY-MM-DD'),'男');
insert into student values(seq_sid.nextval,'赛诺菲',to_date('2012-2-5','YYYY-MM-DD'),'女');
insert into student values(seq_sid.nextval,'赛诺菲',to_date('2012-2-5','YYYY-MM-DD'),'男');




insert into teacher values(seq_tid.nextval,'叶平');
insert into teacher values(seq_tid.nextval,'李一');
insert into teacher values(seq_tid.nextval,'李二');


insert into course values(seq_cid.nextval,'企业管理',2);
insert into course values(seq_cid.nextval,'马克思',1);
insert into course values(seq_cid.nextval,'OOUML',1);
insert into course values(seq_cid.nextval,'数据库',2);
insert into course values(seq_cid.nextval,'语文',1);
insert into course values(seq_cid.nextval,'数学 ',3);
insert into course values(seq_cid.nextval,'英语',3);

insert into SC values(1,1,81);
insert into SC values(1,2,76);
insert into SC values(1,3,81);
insert into SC values(1,4,36);
insert into SC values(1,5,75);
insert into SC values(1,6,56);
insert into SC values(1,7,98);
insert into SC values(1,8,67);
insert into SC values(1,9,65);
insert into SC values(1,10,78);
insert into SC values(1,11,56);
insert into SC values(2,1,45);
insert into SC values(2,2,87);
insert into SC values(2,3,45);
insert into SC values(2,4,65);
insert into SC values(2,5,87);
insert into SC values(2,6,34);
insert into SC values(2,7,65);
insert into SC values(2,8,87);
insert into SC values(2,9,23);
insert into SC values(2,10,54);
insert into SC values(2,11,54);
insert into SC values(3,1,81);
insert into SC values(3,3,81);
insert into SC values(3,4,36);
insert into SC values(3,5,75);
insert into SC values(3,6,56);
insert into SC values(3,7,98);
insert into SC values(3,8,67);
insert into SC values(3,9,65);
insert into SC values(3,10,78);
insert into SC values(3,11,56);
insert into SC values(4,1,81);
insert into SC values(4,2,76);
insert into SC values(4,3,81);
insert into SC values(4,4,36);
insert into SC values(4,5,75);
insert into SC values(4,6,56);
insert into SC values(4,7,98);
insert into SC values(4,8,67);
insert into SC values(4,9,65);
insert into SC values(4,10,78);
insert into SC values(4,11,56);
insert into SC values(5,1,81);
insert into SC values(5,2,76);
insert into SC values(5,3,81);
insert into SC values(6,1,81);
insert into SC values(6,2,76);
insert into SC values(6,3,81);
insert into SC values(7,1,81);
insert into SC values(7,2,76);
insert into SC values(7,3,81);
insert into SC values(7,12,81);


insert into SC values(1,13,34);
insert into SC values(3,13,56);
insert into SC values(5,13,55);
insert into SC values(2,13,44);
insert into SC values(7,13,45);


insert into SC values(1,14,34);
insert into SC values(2,14,33);
insert into SC values(4,14,55);
insert into SC values(5,14,44);
insert into SC values(6,14,45);
insert into SC values(7,14,45);


commit;


select  * from student;
select  * from teacher;
select  * from course;
select  * from SC;


--1、查询“001”课程比“002”课程成绩高的所有学生的学号;
select one.sno from sc one,sc two
where one.sno=two.sno and one.cno='1' and two.cno='2'
and one.score>two.score


--2、查询平均成绩大于60分的同学的学号和平均成绩；
select sno 学号,round(avg(score),2) 平均成绩 from sc group by sno having avg(score)>60
 
--3、查询所有同学的学号、姓名、选课数、总成绩； 
select s.sno 学号,sname 姓名,count(cno) 选课数,sum(score) 成绩 from student s,sc where s.sno=sc.sno group by s.sno,sname 


--4、查询姓“李”的老师的个数； 
select count(tno) from teacher where tname like '李%'


--5、查询没学过“叶平”老师课的同学的学号、姓名； 
select sno 学号,sname 姓名 from student s where sno not in( 
select distinct(s.sno) from Student s inner join SC on 
(s.sno=sc.sno) inner join Course c on(c.Cno=SC.Cno)
 inner join Teacher t on (t.Tno=c.tno) where t.Tname='叶平')

--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； 
select sno 学号,sname 姓名 from student where sno in
(select one.sno from sc one,sc two
where one.sno=two.sno and one.cno='1' and two.cno='2')


--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； 
select distinct(s.sno),sname from Student s inner join SC on 
(s.sno=sc.sno) inner join Course c on(c.Cno=SC.Cno)
 inner join Teacher t on (t.Tno=c.tno) where t.Tname='叶平'


--8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select sno 学号,sname 姓名 from student where sno in(
select one.sno from sc one,sc two
where one.sno=two.sno and one.cno='1' and two.cno='2'
and one.score>two.score)

--9、查询所有课程成绩小于60分的同学的学号、姓名； 
select s.sno, sname from student s where s.sno not in
(select sc.sno from student s,sc where sc.score>=60 and s.sno=sc.sno);  


select s.sno, sname from student s where not exists 
(select * from sc where sc.sno=s.sno and sc.score>=60); 


--10、查询没有学全所有课的同学的学号、姓名；
select s.sno 学号,sname 姓名 from student s,sc where s.sno=sc.sno group by s.sno,sname having count(cno)<(select count(cno) from course)


--11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select distinct(s.sno),sname from student s, sc where s.sno=sc.sno and cno in(select cno from sc where sno=1)

--12、查询至少学过学号为“001”同学所有( 一门 )课程的其他同学学号和姓名； 
select sno,sname  from student s where not exists(
select * from course c where cno in(select cno from sc where sno=1)
and not exists(
select * from sc where sc.sno=s.sno and sc.cno=c.cno
) ) and sno!=1


--13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
update sc set score =   
(select round(avg(score),2) from sc where cno=sc.cno) where cno in 
(select c.cno from teacher t,course c where t.tno=c.tno and t.tname='叶平')






--14、查询和“1002 ”号的同学学习的课程（完全相同）的其他同学学号和姓名；
select distinct(sno) from sc where sno not in--不存在学号不在下面这张表中
(select sno from sc where cno not in (select cno from sc where sno=2))  --不存在课程你选了而2 没选
group by sno  --学号分组
having count(cno) = (select count(cno) from sc where sno=2) --两个条件
and sno<>2;




--这是选修了包括2 号同学选修的课程的其他同学
select sno,sname from student s where not exists(
       select * from course c where cno in (select cno from sc where sno=2) and not exists
       ( select * from sc where sc.sno=s.sno and sc.cno=c.cno)
) and sno<>2 
     
--15、删除学习“叶平”老师课的SC表记录； 
delete sc where cno in(  
select cno from course c,teacher t 
where c.tno=t.tno and t.tname='叶平');

--16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2号课的平均成绩；
      -- 没上过3号课程 ,有2号课程的成绩 
Insert  into SC select '2',sno,(select avg(score) from SC where cno=2) 
from student where sno not in (select sno from SC where cno=3); 


--17 、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分 
select sno 学生ID,
sum(case cname when '数据库' then score end) 数据库,
sum(case cname when '企业管理' then score end) 企业管理,
sum(case cname when '英语' then score end) 英语,
count(cno) 有效课程数,
round(avg(score),2) 有效平均分
from (
select s.sno,sname,c.cno,cname,score
from course c,sc,student s
where c.cno=sc.cno and s.sno=sc.sno
and cname in('数据库','企业管理','英语')
)
group by sno
order by round(avg(score),2) desc


--18、查询各科成绩最高和最低的分：以如下形式显示：课程ID ，最高分，最低分 
select distinct(s1.cno) 课程ID,s1.score 最高分,s2.score 最低分
from sc s1,sc s2
where s1.cno=s2.cno
and s1.score=(
    select max(score) from sc where s1.cno=sc.cno group by cno
)
and s2.score=(
    select min(score) from sc where s2.cno=sc.cno group by cno
)    order by 课程ID


--19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 ???
select sc.cno,(select cname from course c where c.cno=sc.cno) 课程名,  
       round(avg(sc.score),2) 平均成绩,  
       round((select count(*) from sc where cno=sc.cno and score>=60)/  
       (select count(*) from sc where cno= sc.cno)*100,2)||'%' 及格率  
from sc  group by cno  order by 平均成绩,及格率 desc;
    
--20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）
--？？？
select
sum(decode(cname,'企业管理',sum(score)/count(sno),0)) 企业管理,
sum(decode(cname,'马克思',score,0)) 马克思,
sum(decode(cname,'OOUML',score,0)) OOUML,
sum(decode(cname,'数据库',score,0)) 数据库 ,round(avg(score))
from(
select s.sno,sname,cname,round(avg(score)) as score from sc,course c,student s 
where sc.cno=c.cno and sc.sno=s.sno group by s.sno,sc.cno,sname,cname ) a group by a.sno,sname;






--21、查询不同老师所教不同课程平均分从高到低显示 
select tname,cname,round(avg(score),2) 平均分 from sc,teacher t,student s,course c
where s.sno=sc.sno and sc.cno=c.cno and sc.sno=s.sno and t.tno=c.tno 
group by t.tno,tname,c.cno,cname order by 平均分 desc


--22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002 ），UML （003 ），数据库（004 ） ，按以下列形式输出:
-- [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩 
--三到六名  分页   多建几个表
select * from
(
       select e.*,rownum rn from 
       (  
              select sno 学生ID,sname 学生姓名,
              sum(case cname when '企业管理' then score end) 企业管理,
              sum(case cname when '马克思' then score end) 马克思,
              sum(case cname when 'OOUML' then score end) OOUML,
              sum(case cname when '数据库' then score end) 数据库,
              round(avg(score),2) 平均分
        from (
             select s.sno,sname,c.cno,cname,score
             from course c,sc,student s
             where c.cno=sc.cno and s.sno=sc.sno
             and cname in('企业管理','马克思','OOUML','数据库')
)
group by sno,sname
order by round(avg(score),2) desc
) e  where rownum<=6
) where rn>=3




--23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[84-70],[69-60],[ <60] , 
select c.cno 课程ID,cname 课程名称,
sum(case when score between 85 and 100 then 1 else 0 end) "[85-100]",
sum(case when score between 70 and 84 then 1 else 0 end) "[70-84]",
sum(case when score between 60 and 69 then 1 else 0 end) "[60-69]",
sum(case when score<60 then 1 else 0 end) "[<60]"
from sc,course c where sc.cno=c.cno group by c.cno,cname order by c.cno;


--24、查询学生平均成绩及其名次 
select sname,rownum  from 
(select sname,round(avg(score)) 平均成绩 from sc,student s where s.sno=sc.sno 
group by s.sno,sname order by 平均成绩 desc)

--25、查询各科成绩前三名的记录:(不考虑成绩并列情况) 
select * from (select row_number() over(partition by cno order by score desc) rk,sc.* from sc) t
where t.rk<=3;


        --各科总排名 考虑并列情况
        select sno,cno,score,dense_rank() over(partition by cno order by score desc) as pm from sc;
        --各科总排名 不考虑考虑并列情况
        select sno,cno,score,row_number() over(partition by cno order by score desc) as 排名 from sc;


--26、查询每门课程被选修的学生数 
select cno,count(sno) from sc group by cno


--27、查询出只选修了一门课程的全部学生的学号和姓名 
select s.sno,sname from student s,sc where sc.sno=s.sno group by s.sno,sname having count(cno)=1 


--28、查询男生、女生人数 
select count(ssex) 男生人数 from student where ssex='男';
select count(ssex) 女生人数 from student where ssex='女';


--29、查询姓“张”的学生名单
select * from student s where sname like '张%';


--30、查询同名同性学生名单，并统计同名人数
select sname,ssex,count(ssex) 同名同性人数 from student group by sname,ssex having count(ssex)>1
 
  
--31、1981年出生的学生名单(注：Student表中Sage列的类型是date) 
select * from student  where  extract(year from sage)='1981'


--32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select cno,round(avg(score)) 平均成绩 from sc group by cno 
order by 平均成绩,cno desc


--33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩 
select s.sno,sname,avg(score) 平均成绩 from student s,sc where s.sno=sc.sno group by s.sno,sname having avg(score)>85

--34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数 
select sname,score from sc,student s,course c 
where s.sno=sc.sno and c.cno=sc.cno and cname='数据库' and score<60 

--35、查询所有学生的选课情况； 
select sname,cname from student s,sc,course c where s.sno=sc.sno and c.cno=sc.cno

--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数； 
select sname,cname,score from student s,sc,course c where s.sno=sc.sno and c.cno=sc.cno and score>70

--37、查询不及格的课程，并按课程号从大到小排列
select distinct(c.cno),cname from sc,course c where c.cno=sc.cno and score<60
order by cno desc;
 
--38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； 
select s.sno,sname from sc,student s where s.sno=sc.sno and cno=3 and score>80

--39、求选了课程的学生人数 
select count(distinct(sno)) from sc 

--40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩 
select * from 
(select s.sno,sname,cno,score from sc,student s  where sc.sno=s.sno and cno in 
(select distinct(c.cno) from sc,course c,teacher t where sc.cno=c.cno and c.tno=t.tno and tname='叶平')
order by score desc)
where rownum=1

--41、查询各个课程及相应的选修人数 
select c.cno,cname,count(sno) from course c,sc where c.cno=sc.cno group by c.cno,cname

--42、查询不同课程成绩相同的学生的学号、课程号、学生成绩 --
select sc.* from sc,(select cno,score from sc group by cno,score having count(1)>1) n
where sc.cno=n.cno and sc.score=n.score  

--43、查询每门功成绩最好的前两名 
select * from (select row_number() over(partition by cno order by score desc) rk,sc.* from sc) t
where t.rk<=2;


--44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 
select cno 课程号,count(sno) 选修人数 from sc group by cno having count(sno)>10 
order by count(sno) desc,cno asc


--45、检索至少选修两门课程的学生学号
select sno from sc group by sno having count(cno)>2


--46、查询全部学生都选修的课程的课程号和课程名
select c.cno,cname from course c,sc where c.cno=sc.cno
group by c.cno,cname having count(sno)=(select count(sno) from student)

--47、查询没学过“叶平”老师讲授的任一门课程的学生姓名 
select sno,sname from student where sno not in(
select distinct(s.sno) from student s,sc where s.sno=sc.sno and cno  in
(select cno from course c,teacher t where c.tno=t.tno and tname='叶平'))


--48、查询两门以上不及格课程的同学的学号及其平均成绩 
select sno 学号,round(avg(score),2) 平均成绩 from sc where score<60 group by sno having count(cno)>2


--49、检索“004”课程分数小于60 ，按分数降序排列的同学学号 
select sno,score from sc where cno=4 and score<60 order by score desc

--50、删除“002 ”同学的“001”课程的成绩 
delete from sc  where  score in (select score from sc where sno=2 and cno=1) and sno=2 and cno=1

 四、报表

create table sales(
sid int primary key,
pamount numeric(10,2),
sdate date
);


create sequence seq_sid start with 1001 increment by 1;


insert into sales values(seq_sid.nextval,4000,to_date('2012-5-14','yyyy-mm-dd'));
insert into sales values(seq_sid.nextval,28000,to_date('2012-3-14','yyyy-mm-dd'));
insert into sales values(seq_sid.nextval,4200,to_date('2012-3-26','yyyy-mm-dd'));
insert into sales values(seq_sid.nextval,4200,to_date('2012-3-26','yyyy-mm-dd'));
insert into sales values(seq_sid.nextval,20000,to_date('2012-4-14','yyyy-mm-dd'));
insert into sales values(seq_sid.nextval,4200,to_date('2012-5-12','yyyy-mm-dd'));
insert into sales values(seq_sid.nextval,8400,to_date('2012-6-14','yyyy-mm-dd'));
insert into sales values(seq_sid.nextval,4000,to_date('2012-2-18','yyyy-mm-dd'));
insert into sales values(seq_sid.nextval,12000,to_date('2012-3-14','yyyy-mm-dd'));
insert into sales values(seq_sid.nextval,4200,to_date('2012-3-12','yyyy-mm-dd'));
insert into sales values(seq_sid.nextval,4200,to_date('2012-4-12','yyyy-mm-dd'));
insert into sales values(seq_sid.nextval,8600,to_date('2012-3-12','yyyy-mm-dd'));
insert into sales values(seq_sid.nextval,11000,to_date('2012-5-12','yyyy-mm-dd'));
insert into sales values(seq_sid.nextval,12000,to_date('2012-4-14','yyyy-mm-dd'));
insert into sales values(seq_sid.nextval,4800,to_date('2012-4-18','yyyy-mm-dd'));


select * from sales;
--日报表
select sdate,sum(pamount) as 总额 from sales group by sdate;


--月报表
select extract(year from sdate) 年份,extract(month from sdate) as 月份,sum(pamount) as 总额 from sales
group by extract(year from sdate),extract (month from sdate) order by 年份,月份
--季度报表
select extract(year from sdate) 年份,
sum(case when extract (month from sdate)between 1 and 3 then pamount else 0 end) as 第一季度,
sum(case when extract(month from sdate)between 4 and 6 then pamount else 0 end) as 第二季,
sum(case when extract(month from sdate)between 7 and 9 then pamount else 0 end) as 第三季度,
sum(case when extract(month from sdate)between 10 and 12 then pamount else 0 end ) as 第四季度
from sales group by extract(year from sdate) order by 年份;


--年报表
select extract(year from sdate) 年份,sum(pamount) as 总额 from sales group by extract(year from sdate )order by 年份

五、查询课堂案例学生版

 --scott用户
drop table stuInfo;
drop table course;
drop table score;


drop sequence seq_stuid;
drop sequence seq_cid;
drop sequence seq_scid;


create table stuinfo( --学生
   stuid int primary key,
   sname varchar2(50) unique,
   age int not null 
        constraint CK_sage check(age>=18 and age<=30),
   addr varchar2(50),
   sex char(2) default 'M'
       constraint CK_sexs check(sex in('M','F'))
);


create sequence seq_stuid start with 10001 increment by 1;




create table course( --科目
  cid int primary key,
  cname varchar2(50)      
); 


create sequence seq_cid start with 10001 increment by 1;
    


create table score( --成绩
   scid int primary key,
   stuid int
      constraint FK_stuid references stuinfo(stuid),
   courseid int
      constraint FK_courseid references course(cid),
   score int
);


create sequence seq_scid start with 10001 increment by 1;
   
insert into course values(seq_cid.nextval,'j2se精讲');
insert into course values(seq_cid.nextval,'Oracle');
insert into course values(seq_cid.nextval,'html网页设计');
insert into course values(seq_cid.nextval,'Java基础');
   
insert into stuinfo values(seq_stuid.nextval,'张果老',22,null,'M');
insert into stuinfo values(seq_stuid.nextval,'李豹',22,null,'M');
insert into stuinfo values(seq_stuid.nextval,'老胡',22,'北京','M');
insert into stuinfo values(seq_stuid.nextval,'老江',24,'湖南','M');
insert into stuinfo values(seq_stuid.nextval,'张无忌',26,'衡阳','M');
insert into stuinfo values(seq_stuid.nextval,'二师兄',28,'长沙','M');
insert into stuinfo values(seq_stuid.nextval,'凤姐',24,'上海','F');
insert into stuinfo values(seq_stuid.nextval,'元芳',28,'上海','M');
insert into stuinfo values(seq_stuid.nextval,'离歌',23,'上海','M');
insert into stuinfo values(seq_stuid.nextval,'八戒',24,'上海','M');
insert into stuinfo values(seq_stuid.nextval,'赵柳',22,'北京','F');


insert into score values(seq_scid.nextval,10005,10001,95);
insert into score values(seq_scid.nextval,10005,10002,58);
insert into score values(seq_scid.nextval,10005,10003,56);
insert into score values(seq_scid.nextval,10005,10004,81);
insert into score values(seq_scid.nextval,10005,10002,80);


insert into score values(seq_scid.nextval,10001,10001,78);
insert into score values(seq_scid.nextval,10001,10003,81);
insert into score values(seq_scid.nextval,10001,10004,92);




insert into score values(seq_scid.nextval,10002,10003,30);
insert into score values(seq_scid.nextval,10002,10003,78);
insert into score values(seq_scid.nextval,10003,10003,66);
insert into score values(seq_scid.nextval,10004,10003,66);
insert into score values(seq_scid.nextval,10006,10003,78);
insert into score values(seq_scid.nextval,10007,10003,76);
insert into score values(seq_scid.nextval,10008,10002,81);
insert into score values(seq_scid.nextval,10009,10002,90);
insert into score values(seq_scid.nextval,10010,10003,42);


insert into score values(seq_scid.nextval,10002,10001,80);
insert into score values(seq_scid.nextval,10004,10002,78);
insert into score values(seq_scid.nextval,10008,10003,69);
insert into score values(seq_scid.nextval,10008,10001,69);




select * from stuinfo;
select * from course;
select * from score order by stuid;


commit;


--查出所有姓'张'的学员信息
select * from stuinfo where sname like '张%';


--查出所有address字段为'NULL'值的学员的信息
select * from stuinfo where addr is null


--查出成绩在60到70分之间的学员的id号
select stuid,score from score where score between 60 and 70




--查出地址是北京和上海的学生的信息
select * from stuinfo where addr='北京' or addr='上海'--(in(北京，上海))




--显示学生姓名，课程名及成绩  
select s.sname,cname,score from stuinfo s,course c,score e where s.stuid=e.stuid and c.cid=e.courseid
select s.sname,cname,score from stuinfo s inner join score sc on s.stuid=sc.stuid inner join course c on c.cid=sc.courseid
--最好的
select s.sname,cname,score from stuinfo s left join score sc on s.stuid=sc.stuid left join course c on c.cid=sc.courseid




--查出学员在'oracle'这门课程中的总成绩和平均成绩,最高分数，最低分数
select avg(score) as 平均值,sum(score) as 总和值,max(score) as 最大值,min(score) as 最小值 from score where courseid='10002'


--查出学员在'html网页设计'这门课程中不及格的学生人数
select count(stuid) from score sc,course c where sc.courseid=c.cid and courseid='10003' and score<60


  --至今未及格  补考多次没有及格  
  select s.stuid,cname,max(score) from course c,score s 
  where c.cid=s.courseid and stuid=s.stuid 
  group by stuid,cname having max(score)<60 order by stuid;
  
  select s.stuid,sd.sname,cname,max(score) from stuinfo sd,course c,score s where s.stuid=sd.stuid and c.cid=s.courseid group by s.stuid,cname having max(score)<60 order by stuid


--查出至今为止没有及格的学生姓名和课程名以及分数
  select * from stuinfo sf,
  (select s.stuid,cname,max(score) from course c,score s where c.cid=s.courseid  group by stuid,cname having max(score)<60 order by stuid) a
  where sf.stuid=a.stuid;
  
  select stuid,cname,s.sname from stuinfo sf,
  (select s.stuid,cname,max(score) from course c,score s where c.cid=s.courseid  group by stuid,cname having max(score)<60 order by stuid) a
  where sf.stuid=a.stuid;
  


--多列分组的情况: 需求描述: 要求求出每次测试不同学员的不同课目的成绩(如果有补考，则为多次成绩的平均值)
--select sname,cname,avg(score) from stuinfo s,
select stuid,cname,avg(score) from course c,score sc 
where c.cid=sc.courseid group by stuid,cname  order by stuid
--where s.stuid=a.stuid;




--查询选修了两个以上课程的学生姓名  


  --根据学号分组  
  select sname from stuinfo where stuid in
 ( select stuid from score sc group by stuid having count(distinct(courseid))>2 )order by stuid

 select s.stuid,sname from score sc,stuinfo s where sc.stuid=s.stuid group by s.stuid,sname
 having count(distinct(courseid))>2
 
--查出没有选修1号课程的学生信息   


select * from stuinfo where stuid not in
(select s.stuid from course c,score sc,stuinfo s where c.cid=sc.courseid and s.stuid=sc.stuid group by s.stuid,cid having cid=10001 ) order by stuid




--查询选修了所有课程的学生信息(不存在这样的课程，他没有参加选修)  
select stuid from course c,score sc  where c.cid=sc.courseid group by stuid having count(distinct(cid))=4
 --方法一
 select * from stuinfo where stuid in
 (select stuid from score sc group by stuid having count ( distinct(courseid) ) >= (select count(cid) from course) )
 --方法二  no exist 返回的是true or false 所以不要明确定义列
        --没有课程 没有成绩  sid  cid   不存在没有课程没成绩的人
 select * from stuinfo s where not exists(
        select * from course c where not exists(
               select * from score sc where s.stuid=sc.stuid and c.cid=sc.courseid) );


 select * from  stuinfo s,course c,score sc where s.stuid=sc.stuid and c.cid=sc.courseid  ？？？
 
--查询至少选修了2号学生所有课程的学生信息(查询这样的学生信息->不存在这样的课程->2号学生选修的课程——>2 号学生有成绩而你没有成绩)
 select * from stuinfo s where not exists(
        select * from course c where cid in(select distinct(courseid) from score sc where sc.stuid=10002) and 
        not exists(
                      --查询表里面是否存在这样的数据
                      select * from score where s.stuid=score.stuid and c.cid=score.courseid  
               )
 );




--选修了除2号课程以外的其他课程的学生信息


  --9号学生不符合条件  下面语句不对
  select * from stuinfo s where not exists(
         select * from course c where cid=10002 and not exists(
                select * from score sc where s.stuid=sc.stuid  
         )
  );
  --正确语句
  select * from stuinfo where stuid in
  (select stuid from score group by stuid having count(distinct(courseid))>1 and 
  stuid in(select stuid from score sc where sc.courseid=10002)
  )
  




--检索至少选修了1号和2的课程的学生信息  
--方法一
select * from stuinfo where stuid in(select stuid from score sc where courseid=10001 and stuid in(select stuid from score sc where courseid=10002))
  --and这里出错了
  --select stuid from score sc group by stuid,courseid having courseid=10001 and  courseid=10002
  --select * from score order by stuid
  
--方法二  分身术。。。。  自表查询
select distinct s.*,sc1.courseid,sc1.score from stuinfo s,score sc1,score sc2
       where s.stuid=sc1.stuid and sc1.courseid=10001 and s.stuid=sc2.stuid and sc2.courseid=10002;




--替换，如果sex为M则显示男，如果为F则显示女
 select sname,case sex when 'M' then '男' when 'F' then '女' end 性别 from stuinfo
 
--select * from stuinfo
--update  stuinfo set sex='男' where sex='M';
--update  stuinfo set sex='女' where sex='F'; 


--统计列印各科成绩,各分数段人数:课程名称,[100-85],[85-70],[70-60],[ <60] , 
--典型的行数据变成列数据   
select cname 课程名称,
sum(case when score between 85 and 100 then 1 else 0 end) "[85-100]",
sum(case when score between 70 and 84 then 1 else 0 end) "[70-84]",
sum(case when score between 60 and 69 then 1 else 0 end) "[60-69]",
sum(case when score<60 then 1 else 0 end) "[<60]"
from score s,course c where s.courseid=c.cid group by cname;




--去掉补考的(只统计最高分) --加个a表 从a表里面判断 把最大值重命名方便select
select cname 课程名称,
sum(case when score between 85 and 100 then 1 else 0 end) "[85-100]",
sum(case when score between 70 and 84 then 1 else 0 end) "[70-84]",
sum(case when score between 60 and 69 then 1 else 0 end) "[60-69]",
sum(case when score<60 then 1 else 0 end) "[<60]" 
from
(select stuid,cname,max(score) as score from score s,course c where s.courseid=c.cid group by stuid,cname) a
group by cname;
 
--求人数和  显示格式为  姓名  Java基础   j2se精讲  html网页设计   Oracle
select stuid 学号,sname 姓名,
sum(decode(cname,'java基础',score,0)) "java基础",
sum(decode(cname,'j2se精讲',score,0)) "j2se精讲",
sum(decode(cname,'html网页设计',score,0)) "html网页设计",
sum(decode(cname,'Oracle',score,0)) "Oracle" from(
select sc.stuid,sname,cname,max(score) as score from score sc,course c,stuinfo s where sc.courseid=c.cid and sc.stuid=s.stuid group by sc.stuid,sname,cname) a group by stuid,sname;






--在上面基础上，只想查看参加过补考的学员的补考课目的平均成绩.
--分析，这时要对分组后的数据进行分析，如果是补考的课目，则分组后会有多条记录，




--查询所有学生的成绩，包括学生姓名，课程名，成绩,按学生姓名分组






--查询张无忌的成绩，包括姓名，课程名，成绩,按学生姓名分组，如有补考，只显示最高的一次