oralce各种练习题

ORACLE课堂练习：
----1.员工表
create table Emps(
Emp_id varchar2(10),
Emp_name varchar(10),
Emp_sex char(2),
Emp_birthday date,
Emp_age number(3),
Emp_wage number(6,1)
)
alter table Emps add constraint pk_Emp_id primary key(Emp_id)
insert into Emps values('2','张三','男',to_date('2014-02-14','yyyy-mm-dd'),34,7);
insert into Emps values('6','李四','女',to_date('2014-02-14','yyyy-mm-dd'),34,7.2);
insert into Emps values('3','anm','男',to_date('1980-05-14','yyyy-mm-dd'),32,6.3);
insert into Emps values('4','旺财','女',to_date('2011-02-14','yyyy-mm-dd'),33,2.5);
insert into Emps values('5','王冲','男',to_date('2002-02-14','yyyy-mm-dd'),22,2.5);
insert into Emps values('7','小米','女',to_date('1990-02-24','yyyy-mm-dd'),87,7.2);
insert into Emps values('8','小明','男',to_date('1970-11-14','yyyy-mm-dd'),13,22.3);
insert into Emps values('9','小美','女',to_date('1920-12-16','yyyy-mm-dd'),45,34.5);
insert into Emps values('10','小泽','男',to_date('1981-09-23','yyyy-mm-dd'),78,79.5)
insert into Emps values('1','小泽','男',to_date('1981-09-23','yyyy-mm-dd'),78,79.5333)
select * from Emps
delete  from Emps
----(1)查询职工表中职工年收入情况（12*工资） ，用"年收入"字段名表示
select (Emp_wage*12) as  年收入  from Emps ;
---(2)查询出职工表中性别为男性的人员全部信息
select * from emps where Emp_sex='男'
---(3)查询出职工表中工资不是1200的人员信息
select * from emps where Emp_wage != 1200
---(4)查询出生于1984年以前的人员姓名
select Emp_name from emps where Emp_birthday<to_date('1984-01-01','yyyy-mm-dd')
select Emp_name from emps where extract(year from  emp_birthday)  <1984
---(5)查询出职工表中性别为"女"年龄为18岁或性别为"男"年龄为21岁的人员信息
select * from emps where( Emp_sex='女' and Emp_age=16) or (Emp_sex='男' and Emp_age=21)
---(6)查询出职工表中工资不是1200的人员信息，按照工资升序、姓名降序、职工号升序排列
select * from emps where not Emp_wage=1200 order by Emp_wage, Emp_name desc ,Emp_wage
---(7)查询出1984年到1994年中出生的人员姓名，按照出生日期降序排列，按姓名升序排列
select emp_name from emps where Emp_birthday between to_date('1980-01-01','yyyy-mm-dd')and to_date('2014-01-01','yyyy-mm-dd')order by Emp_birthday desc,emp_name
select emp_name from emps where  extract(year from emp_birthday) between 1984 and 1994 order by Emp_birthday desc,emp_name
---(8)查询职工表的平均工资，用"平均工资"字段名表示
select avg(emp_wage) as 平均工资 from emps
---(9)查询职工表基本工资的总和、最小值及最大值
select sum(emp_wage),min(emp_wage),max(emp_wage) from emps
---(10)查询职工表中工资中最大与最小值之间的差额
select (max(emp_wage)-min(emp_wage)) as 差值 from emps
---(11)查询职工表中记录总数
select count(*) from emps

----2.工资表
create table EmpSalary(
empid number(6),
empname char(15),
empsal number(8)
)
alter table EmpSalary add constraint pk_empid primary key(empid)
insert into EmpSalary values(1,'张三',3000);
insert into EmpSalary values(2,'李四',5000);
insert into EmpSalary values(3,'吴王',7000);
insert into EmpSalary values(4,'王五',8000);
insert into EmpSalary values(5,'马六',5900);
insert into EmpSalary values(6,'何文',7500)
insert into EmpSalary values(7,'hdh',8500)
insert into EmpSalary values(8,'hello',9500)
select * from EmpSalary
---给每个职员加工资，其标准是：工资在6000元以下的将加20%,工资在6000元-8000元的将加18%,工资在8000元以上的加15%.。使用case或者decode显示加薪后的工资情况。
select decode(sign(empsal - 6000),-1,empsal*1.2,1,empsal*1.15) from EmpSalary ---两个阶段
select decode(sign(empsal - 6000),-1,empsal*1.2,1,decode(sign(empsal - 8000),-1,empsal*1.18,1,empsal*1.15)) from EmpSalary ---三个阶段
---下面这个是老师的 方法一
select empname,decode (sign(empsal-6000),-1,empsal*1.2,0,empsal,1，--0和-1的情况都有了，下面就是默认1的情况
               decode(sign(empsal-8000),-1,empsal*1.18,0,empsal,1，
                decode(sign(8000-empsal),-1,empsal*1.15)))
                from EmpSalary
 ---下面这个是老师的，方法二
 select empname,decode (sign(empsal-6000),-1,empsal*1.2,0,empsal,
               decode(sign(empsal-8000),-1,empsal*1.18,0,empsal,-1，empsal*1.15))
                from EmpSalary
----3.商品销售表
create table sale(
month char(6),---月份
sell number(10,2)---销售金额
)
insert into sale values('1',5680.5932);
insert into sale values('2',5680.5932);
insert into sale values('3',8000);
insert into sale values('4',2100.1);
insert into sale values('5',4520.5555);
insert into sale values('6',5999.66)
insert into sale values('12',5999.66)
insert into sale values('10',5999.66)

select * from sale
/**创建视图
create or replace view V_sale
as
 select s.month,s.sell
FROM sale s
select * from V_sale
**/
select name as "姓名",
min(decode(subject,'数学',grade)) as "数学",
min(decode(subject,'语文',grade)) as "语文",
min(decode(subject,'英语',grade)) as "英语"
 from score1 group by name
----\\的用法
select  decode( sign(to_char(month)-10 ),
              1,to_char(month),
              -1,('0' || to_char(month)),
              0,to_char(month)
)from sale
----横纵表转化
select '2000'as 年份,
      min(decode(month,1,sell)) "month01",
      min(decode(month,2,sell)) "month02",
      min(decode(month,3,sell)) "month03",
      min(decode(month,4,sell)) "month04",
      min(decode(month,5,sell)) "month05",
      min(decode(month,6,sell)) "month06",
      min(decode(month,7,sell)) "month07",
      min(decode(month,8,sell)) "month08",
      min(decode(month,9,sell)) "month09",
      min(decode(month,10,sell)) "month10",
      min(decode(month,11,sell)) "month11",
      min(decode(month,12,sell)) "month12"
from sale
-----3.1题
create table sale1(
month char(6),---月份
sell number(10,2)---销售金额
)
insert into sale1 values('200001',5680.5932);
insert into sale1 values('200003',5680.5932);
insert into sale1 values('200004',8000);
insert into sale1 values('200008',2100.1);
insert into sale1 values('200101',4520.5555);
insert into sale1 values('200501',5999.66);
insert into sale1 values('200403',5999.66);
insert into sale1 values('200801',5999.66)
select * from sale1
select to_char(month) from sale1---转化成字符串,不用to_char了，因为month在创建的时候就是char类型的
select substr(to_char(month),5,2) from sale1;---截取子串，作为月份
select substr(to_char(month),1,4) from sale1---截取子串，作为年份
（'month' || substr(to_char(month),5,2)）,
---利用子串和decode函数完成横表转化成纵表
select substr(to_char(month),1,4 ) as 年份,---截取前四位
     min(decode(substr(month,5,2),01,sell)) as "month02",---截取后两位
     max(decode(substr(to_char(month),5,2),02,sell)) "month02",---这里可以选用聚合函数的任意一个，因为每一行都是一个值，聚合不聚合的结果都一样，但是聚合了就不用写group by了
     min(decode(substr(to_char(month),5,2),03,sell)) "month03",
     min(decode(substr(to_char(month),5,2),04,sell)) "month04",
     min(decode(substr(to_char(month),5,2),05,sell)) "month05",
     min(decode(substr(to_char(month),5,2),06,sell)) "month06",
     min(decode(substr(to_char(month),5,2),07,sell)) "month07",
     min(decode(substr(to_char(month),5,2),08,sell)) "month08",
     min(decode(substr(to_char(month),5,2),09,sell)) "month09",
     min(decode(substr(to_char(month),5,2),10,sell)) "month10",
     min(decode(substr(to_char(month),5,2),11,sell)) "month11",
     min(decode(substr(to_char(month),5,2),12,sell)) "month12"
from sale1
group by substr(to_char(month),1,4 )

 

  
----4.分数表score1
create table score1(
name varchar2(10),
subject varchar2(10),
grade number(3)
)
insert into score1 values('张三','语文',50);
insert into score1 values('张三','数学',80);
insert into score1 values('张三','英语',80);
insert into score1 values('李四','语文',80);
insert into score1 values('李四','数学',99);
insert into score1 values('李四','英语',84);
insert into score1 values('王五','语文',50);
insert into score1 values('王五','数学',90);
insert into score1 values('王五','英语',70)
select * from score1
---横表转化成纵表，decode(subject,'数学',grade)不能用在select里面，因此应该放在group by后面或者聚合函数里面，现在是放在了聚合函数里了，这个只是为了不让他报错，这个函数没有实际的意义
select name "姓名",
min(decode(subject,'数学',grade)) "数学",
min(decode(subject,'语文',grade)) "语文",
min(decode(subject,'英语',grade)) "英语"
 from score1 group by name

---5.学生表
create table student1(
stu_id varchar2(10),
stu_name varchar2(30),
stu_sex char(2),
stu_high number(4,1),
stu_weight number(4,1)
)
alter table student1 add constraint pk_stu_id primary key(stu_id)
insert into student1 values('110','战神',32,180.22,170.59);
insert into student1 values('111','李四',22,168.89,150.23);
insert into student1 values('112','历史',14,180.2,980.6);
insert into student1 values('113','王五',19,172.9,90.54);
insert into student1 values('114','王武',56,170,110);
insert into student1 values('115','小米',31,160.62,150.59)
select * from student1
---(1) 所有男生中个子最高的学生名字
select stu_name,stu_high from student1 where stu_sex='男'----1.找出所有男生，这个写在from后面（作为一个新表，查询的时候就查询这个表）
select max(stu_high) from student1----2.找出身高最大的，这个写在where后面（这是个查询结果，因此需要变量去接收，stu_weight=或者stu_weight in）
------汇总：
select stu_name,stu_high from student1 where stu_sex='男' and  stu_high = (select max(stu_high) from student1)
----(2) 所有女生中个子最矮的学生名字
select stu_name from (select stu_name,stu_weight from student1 where stu_sex='男') where stu_weight=(select min(stu_weight) from student1) group by stu_name
----(3) 按性别分组，求男、女的平均身高,平均体重
select stu_sex as 性别,avg(stu_weight) as 平均体重,avg(stu_high) as 平均身高 from student1 group by stu_sex

----6.成绩表
create table score2(
grade number(3)
)
insert into score2 values(45);
insert into score2 values(67);
insert into score2 values(34);
insert into score2 values(87);
insert into score2 values(98);
insert into score2 values(56);
insert into score2 values(99);
insert into score2 values(78)
select * from score2
--统计 60以下的人数 , 60 -80 的 人数 ,80以上的人数
select count(grade) as 以下 from score2 where grade<60---单个
---方法：
select '60分以下'  as 分数段,count(grade) as 人数  from score2 where grade<60
union
select '60-80'  as 分数段,count(grade) as 人数  from score2 where grade between 60 and 80
union
select '80以上'  as 分数段,count(1) as 人数  from score2 where grade>80

 

----第七题：员工人员情况表
create table employee(
Emp_eid varchar2(10),
Emp_name varchar2(10),
Emp_age varchar2(2),
Emp_wh varchar2(20)--文化程度
)
drop table employee
alter table employee add constraint pk_Emp_eid primary key(Emp_eid)
insert into employee values('110','张安','20','本科以上');
insert into employee values('112','ss','20','本科以上');
insert into employee values('111','da','20','本科以上');
insert into employee values('114','是滴是滴','20','本科以上');
insert into employee values('113','发到','21','大专');
insert into employee values('115','圣斗士','21','大专');
insert into employee values('116','方法','21','高中');
insert into employee values('117','图图','17','初中')
select * from employee
delete from employee
/**
某公司的人员情况表（employee）:里面有一字段文化程度（wh）：包括四种情况（本科以上，大专，高中，初中以下）,
现在要根据年龄字段查询统计出：表中文化程度为本科以上，大专，高中，初中以下，各有多少人，占总人数多少
**/

select Emp_wh from employee group by Emp_wh---第一步：找出分组
select count(*) from employee where emp_wh='高中'---第二步：随便找出一组进行统计
---总结这两步
select Emp_wh as 学历，count(*) as 人数,min(emp_age) as 年龄,(count(*)/(select count(*) from employee))*100 as 百分比
from employee where emp_wh in(select Emp_wh from employee group by Emp_wh) group by Emp_wh

---------第八题
create table NewStudent(
ClassID number(1),
StudID number(1),
Name varchar2(20),
Sex number(1),
Chinese number(3,1)
)
 
alter table Newstudent add constraint pk_syudid primary key(Studid)
insert into  newstudent values(1,1,'张三',0,78.244);
insert into  newstudent values(1,2,'李四',0,33.21);
insert into  newstudent values(3,4,'莉莉丝',1,99.2);
insert into  newstudent values(2,3,'斌口',1,56.73);
insert into  newstudent values(3,5,'小米',1,88.2);
insert into  newstudent values(1,6,'熊爱明',0,99);
insert into  newstudent values(2,7,'小明',0,90)
select * from newstudent
--(1)求姓"何"的男生的资料。
select * from newstudent where Name like '李%'
--(2)求所有学生中，男生与女生的数量
select  '男生' as 性别, count(*)as 数量 from newstudent where Sex=1
union
select  '女生' as 性别, count(*)as 数量 from newstudent where Sex=0
--（3）求各班的语文总分、平均分
select sum(Chinese) ,avg(Chinese)from newstudent
--(4)显示所有的数据，Sex=1显示为"男"，Sex=0显示为"女"
select ClassID,StudID,name,Chinese,decode(sex,1,'男',0,'女') from newstudent
--(5)求男生最多的一个班级
(select count(studid) from newstudent where sex=1 group by classid) order by count(studid) desc-----第一步：求出每个班男生数量，并排序
select * from newstudent where rownum=1--- 第二步：找出表中第一行
---方法一：综合这两步
select * from (
(select count(studid) from newstudent where sex=1 group by classid) order by count(studid) desc
)where rownum = 1
---方法二：
----select,from,where,聚合函数，group by,having执行的先后顺序：select-->from-->where-->聚合函数--->having
----因此having中的max(count(*))中的count(*)现在已经不是聚合函数了，而是一个具体的数值（不会出现聚合函数套聚合函数））
select classid ,count(*) from newstudent where sex=1 group by classid
having count(*) in(select max(count(*)) from newstudent where sex=1 group by classid)
-----------------------
----第九题
create table student2
(
  id varchar2(5),
  name varchar2(20)
)
create table subject(
subid varchar2(10),
subname varchar2(20)
)
create table score3(
scoreid varchar2(10),
stuid varchar2(5),
subid varchar2(10),
score number(3)
)
alter table student2 add constraint pk_studentid primary key(id)
alter table subject add constraint pk_subid primary key(subid)
alter table score3 add constraint pk_scoreid primary key(scoreid)
alter table score3 add constraint fk_stuid foreign key(stuid) references student2(id)
alter table score3 add constraint fk_subid foreign key(subid) references subject(subid)
insert into student2 values('s01','张三');
insert into student2 values('s02','李四');
insert into student2 values('s03','王五')
insert into subject values('k001','语文');
insert into subject values('k002','数学');
insert into subject values('k003','英语')
insert into score3 values('1','s01','k001',88);
insert into score3 values('2','s01','k002',87);
insert into score3 values('3','s01','k003',45);
insert into score3 values('4','s02','k001',76);
insert into score3 values('5','s02','k002',90);
insert into score3 values('6','s02','k003',69)
select * from student2
select * from subject
select * from score3
select name from score3 left join student2 on score3.stuid=student2.id group by student2.name---1
----纵表转化成横表
select name  "姓名",
min(decode(subname,'数学',score)) "数学",
min(decode(subname,'语文',score)) "语文",
min(decode(subname,'英语',score)) "英语"
from score3 left join student2  on score3.stuid=student2.id left join subject  on score3.subid=subject.subid
group by student2.name

----------------------------------
---第十题：学生表，课程表，成绩表，成绩等级表，图书表，借书表，图书表
create table student3(
  id number(6),
  name varchar2(20),
  birthday date,
  sex char(1),
  class_id number(6)
)
create table course1(
   id number(6),
   name varchar2(20),
   credit number(2)
)
create table score4
(
   id number(6),
   stuid number(6),
   courseid number(6),
   score number(5,2)
)
create table book
(
   id number(6),
   name varchar2(20),
   description varchar2(100)
)
create table book_loaned
(
    id number(6),
    stuid number(6),
    bookid number(6),
    load_date date,
    book_fine number(5,2)
)
create table score_grade
(
  id number(2,0),
  grade char(1),
  score_high number(5,2),
  score_low number(5,2)
）
alter table student3 add constraint pk_id primary key(id)
alter table course1 add constraint pk_idc primary key(id)
alter table score4 add constraint pk_ids primary key(id)
alter table book_loaned add constraint pk_bid primary key(id)
alter table book add constraint pk_bookid primary key(id)
alter table score_grade add constraint pk_sgid primary key(id)
alter table score4 add constraint fk_ids foreign key(stuid) references student3(id)
alter table score4 add constraint fk_idc foreign key(courseid) references course1(id)
alter table book_loaned add constraint fk_idstu foreign key(stuid) references student3(id)
alter table book_loaned add constraint fk_idbook foreign key(bookid) references book(id)
alter table student3 modify sex char(2)---发现student3的sex字段char(1)太小，修改成char(2)

insert into student3 values(110,'admin',to_date('1999-01-01','yyyy-mm-dd'),'男',1);
insert into student3 values(111,'李明浩',to_date('1970-01-01','yyyy-mm-dd'),'男',2);
insert into student3 values(112,'杨志猛',to_date('1980-01-01','yyyy-mm-dd'),'男',1);
insert into student3 values(113,'宋悦',to_date('2003-01-01','yyyy-mm-dd'),'女',1)
insert into course1 values(1110,'数学',34);
insert into course1 values(1111,'语文',21);
insert into course1 values(1112,'英语',65)
insert into score4 values(1,110,1110,85.5);
insert into score4 values(2,110,1111,65);
insert into score4 values(3,111,1111,56);
insert into score4 values(4,111,1112,44);
insert into score4 values(5,112,1110,98)
insert into book values(1001,'十万个为什么','描述描述描述描述');
insert into book values(1002,'疯狂java','描述描述描述描述');
insert into book values(1003,'中国近代史','描述描的淡淡的述描述');
insert into book values(1004,'十万个为什么','描述描任溶溶述描述')
insert into book_loaned values(1,110,1001,to_date('2003-01-01','yyyy-mm-dd'),60);
insert into book_loaned values(2,110,1003,to_date('2014-01-01','yyyy-mm-dd'),800);
insert into book_loaned values(3,111,1001,to_date('2009-01-01','yyyy-mm-dd'),600)
insert into score_grade values(1,'A',100,90);
insert into score_grade values(2,'B',89,80);
insert into score_grade values(3,'C',79,60);
insert into score_grade values(4,'D',59,0)
delete from score_grade where id=4
select * from student3;
select * from course1;
select * from score4
select * from book
select * from book_loaned
select * from score_grade
--(1),列出所有被借阅书的书名、借阅学生的编号和借阅的日期。
select b.name,bl.stuid,bl.load_date from book_loaned  bl left join book  b  on bl.bookid=b.id
--(2).列出所有被借阅书的书号、书名、借阅学生的姓名和借阅的日期。
select book_loaned.bookid,book.name,book_loaned.load_date from book_loaned left join book on book_loaned.bookid=book.id
left join student3 on book_loaned.stuid=student3.id
---(3).列出选学‘语文’这门课的所有学生的姓名和这门课的成绩。
select student3.name,score4.score from score4 left join course1 on score4.courseid=course1.id left join student3 on score4.stuid=student3.id
where course1.name='语文'
---(4).列出所有选学课程学员的姓名、每门课程的编号、考试的分数和等级
select student3.name,course1.id,score4.score
from score4 left join course1 on score4.courseid=course1.id left join student3 on score4.stuid=student3.id
---(6).列出所有课程的被选择情况，包括课程的名称、选课学员的姓名和考试的分数
select course1.name,student3.name ,score4.score from score4 left join course1 on score4.courseid=course1.id left join student3 on score4.stuid=student3.id

 

-----11学生表,课程表，学生分数表
create table stu(
sno varchar2(10)　not　null,
name　varchar2(20)　not　null,
 sex varchar2(8)  not null,
 age　number(3)    not null,
bir　date,
shen　varchar2(18)　not　null,
school　varchar(20),　
 primary　key(sno)
)
create　table　 cou
( cid　varchar2(6)　not　null,
  cname varchar2(50) not null,
 xf  number(3) not null ,
 primary　key　(cid)
)

create　table　student_course
（sno　varchar2(10)　not　null,
　cid varchar2(6)　not　null, 
　cj numeric(4,1) not　null,
  --primary　key　(sno,cid)
)
alter table student_course add constraint fk_sno1 foreign key(sno) references  stu(sno)
alter table student_course add constraint   fk_cid1 foreign key(cid) references cou(cid)
insert into stu values('1','zhangsan','男',32,to_date('1970-11-14','yyyy-mm-dd'),'120224','北京理工');
insert into stu values('2','zhangsan','男',32,to_date('1970-11-14','yyyy-mm-dd'),'120224','北京理工');
insert into stu values('3','zhangsan','男',32,to_date('1970-11-14','yyyy-mm-dd'),'120224','北京理工');
insert into stu values('4','李四','女',32,to_date('2000-11-14','yyyy-mm-dd'),'120224','北京理工');
insert into stu values('5','王伟','女',32,to_date('2000-11-14','yyyy-mm-dd'),'120255','北京师范')
insert into stu values('6','nans','女',32,to_date('2000-11-14','yyyy-mm-dd'),'120255','北京大学')
insert into cou values('100','疯狂java',6);
insert into cou values('110','html',4);
insert into cou values('130','jsp',6);
insert into cou values('140','sping',8);
insert into cou values('120','oracle',9)
insert into student_course values('2','100',50);
insert into student_course values('3','130',89);
insert into student_course values('4','130',65);
insert into student_course values('5','140',46);
insert into student_course values('2','110',54);
insert into student_course values('1','110',38)
insert into student_course values('5','130',98);

select * from stu;
select * from cou;
select * from student_course;
delete from student_course
delete from stu
delete from cou
drop table student_course
--(1)查询选修了课程但考试成绩全不及格的学生的学号、姓名
select distinct sno, cid from student_course where (cj <60)---错了
---老师的
select distinct sno, cid from student left join student_course on
where sno not in(select distinct sno, cid from student_course where cj >=60 and
student_course.cj is not null)

--（2）查询“信息系”,“数学系”和“计算机系”学生的姓名和性别
select name,sex from stu where school in('北京理工','北京大学')
--（3）查询“张三”同学选修的所有的课程名以及学分、成绩
---什么也不用想，其实就是写个主外键关系
select cj,xf from cou,stu,student_course where stu.sno=student_course.sno and student_course.cid=cou.cid and stu.name='王伟'
--(4)查询选修了课程的学生人数
select  distinct sno from student_course where cj is not null---1.列出所有人
select count(*) from  (select  distinct sno from student_course where cj is not null)--2.统计
--(5)查询没有选修任何课程的学生学号及姓名(老师：学生表中有，成绩表没有，左连接，过滤isnot)
select  distinct sno from student_course --参加过考试的学生
select sno from stu where sno not in(select  distinct sno from student_course)
--(6)6.查询选修了2门以上的课程的学生学号、姓名
select sno,count(sno) from student_course group by sno;---统计学生考试门数
select sno,count(sno) from student_course group by sno having  count(student_course.sno)>1---过滤出2门以上（包括两门）
--总：统计，过滤，加主外键约束条件（where）
select stu.sno,stu.name from student_course,stu where student_course.sno=stu.sno  group by stu.sno,stu.name having  count(student_course.sno)>1
---(7).求各个课程号及相应的选课人数
select cid ,count(sno) from student_course group by cid
--(8)查询选修130号课程且成绩在80分以上的所有学生。（老师：where会在统计之前执行，所有where先找出大于90的）
select count(sno) from student_course where cid=130 and cj>80
--(9)查询平均成绩大于60的学生学号、课程数、总成绩
----select  sno ,sum(cj),count(cid) from student_course  group by sno算出了学号，课程号，总成绩，但是还没加限制条件
----由于限制条件是个聚合函数，根据查询语句执行的先后顺序（where-->group by-->having），因此这个限制条件不能是where，只能是having
select  sno ,sum(cj),count(cid) from student_course  group by sno  having avg(cj)>60
---老师第九题，学生表和课程表左连接

---(10).10.为学生成绩评级，定义成绩90分及以上为“优”，75分至90分为“良”，60分至75分为“中”，
        --60分以下为“差”，分别统计姓名、课程、成绩评级。如张三、JAVA、优
  select name as 姓名,cname as 课程名,decode(sign(cj-90),
                                      1,'优',
                                      0,'优',
                                      -1,decode(sign(cj-75),
                                       1,'良',
                                       0,'良',
                                       -1,decode(sign(cj-60),
                                       1,'中',
                                       0,'中',
                                       -1,'差' ))) 
                                       from student_course,stu,cou
                                       where student_course.sno=stu.sno
                                       and student_course.cid=cou.cid
                                     
 ---12题与13题合并.Employees,wage,tax
 create table Employees(---职工表
     EmpID      Number(5),
    Name       VarChar2(10),
    Sex       Char(2) ,
    Birthdate    Date,
    Address     Varchar2(50),
    Phone       Char(13)
)
create table wage( ---工资表
    EmpID      Number(5),
    Name      VarChar2(10),
    Wage      Number(5),
    Putdate     Date
)
create table tax(  ---税收表
    EmpID       Number(5),
    Name       VarChar2(10) ,
    Tax         Number(5,2) ,
    Paydate      Date
)                                  
 alter table employees add constraint pk_empid1 primary key(empid) ---主键约束
 alter table wage add constraint fk_empid1 foreign key(empid) references employees(empid)--外检约束                                                      
 alter table tax add constraint fk_empid2 foreign key(empid) references employees(empid)--外检约束
 alter table employees add constraint un_phone unique(phone)----唯一约束
insert into employees values(110,'周建安',23,to_date('1980,02-05','yyyy-mm-dd'),'大胡同','1111111110');
insert into employees values(111,'华藏寺',23,to_date('1980,02-05','yyyy-mm-dd'),'大胡同','1111111111');
insert into employees values(112,'展示柜',23,to_date('2009,02-05','yyyy-mm-dd'),'大胡同','1111111112');
insert into employees values(113,'张三',23,to_date('1934,02-05','yyyy-mm-dd'),'大胡同','1111111113');
insert into employees values(114,'展示',23,to_date('1985,02-05','yyyy-mm-dd'),'大胡同','1111111114');
insert into employees values(115,'张珊',23,to_date('1980,02-05','yyyy-mm-dd'),'大胡同','1111111115');
insert into employees values(116,'小米',23,to_date('1980,02-05','yyyy-mm-dd'),'大胡同','1111111116');
insert into employees values(117,'熊爱明',23,to_date('1984,02-05','yyyy-mm-dd'),'大胡同','1111111117');
insert into employees values(118,'小明',23,to_date('1998,02-05','yyyy-mm-dd'),'大胡同','1111111118');
insert into employees values(119,'旺财',23,to_date('1980,02-05','yyyy-mm-dd'),'大胡同','1111111119')
insert into wage values(110,'周建安',3000,sysdate);
insert into wage values(111,'华藏寺',8000,sysdate);
insert into wage values(112,'展示柜',5400,sysdate);
insert into wage values(113,'张三',4400,sysdate);
insert into wage values(114,'展示',7600,sysdate)
insert into tax values(110,'周建安',10,sysdate);
insert into tax values(111,'华藏寺',23.6,sysdate);
insert into tax values(112,'展示柜',27.4,sysdate);
insert into tax values(113,'张三',45.48,sysdate);
insert into tax values(114,'展示',11.2,sysdate)
delete from tax--删除所有内容
drop table tax--删除表
select * from employees
select * from wage
select * from tax
alter table tax add ceshi varchar2(20)---在表tax中添加字段
alter table tax add ceshi111 varchar2(20)---在表tax中添加字段
select empid,name,tax,paydate,nvl(ceshi,'添加新字段的缺省值') from tax---nvl添加缺省值,在添加记录的时候就会显示这个了
insert into tax(EmpID,Name,Tax) values(115,'展示',11.2)
alter table tax modify name char(20) ---修改字段的属性，将name字段的类型改成char(20)
alter table tax drop column ceshi111----删除列
alter table wage rename to xiugai_wage----修改表名字
select * from wage
select * from xiugai_wage

------------------------------------
---14.
---15.
---16.对12题(员工表和工资表)创建视图
select * from Employees
select * from wage
select * from tax;
---左连接的效果
create or replace view empInfo
as
select Employees.Empid,Employees.Name,wage.wage,tax.tax from Employees
 left join wage on Employees.Empid= wage.empid left join tax on Employees.Empid=tax.empid
select * from empInfo
---内连接的效果
create or replace view empInfo
as
select Employees.Empid,Employees.Name,wage.wage,tax.tax from Employees
inner join wage on Employees.Empid= wage.empid inner join tax on Employees.Empid=tax.empid
select * from empInfo
-----------------------------------
--17题
declare
  type number_table_type is table of number(2) index by varchar2(4);
  v_n number_table_type;
begin
  v_n('1'):=1;
  v_n('2'):=2;
  v_n('3'):=3;
  v_n('a'):=4;
  v_n(5):=5;---自动转化成varchar
 dbms_output.put_line(v_n('1'));
 dbms_output.put_line(v_n('2'));
 dbms_output.put_line(v_n('3'));
 dbms_output.put_line(v_n('a'));
 dbms_output.put_line(v_n(5));
 end;
------------------------------------------------
---索引类型改成number不行（改成其他的不受限制）。因为number可以出现小数位
declare
  type number_table_type is table of number(5) index by number(5);
  v_n number_table_type;
begin
  v_n(1):=1;
  v_n(2):=2;
 dbms_output.put_line(v_n(1));
 dbms_output.put_line(v_n(2));
 end;
--------------------------------------
---------测试blob
create table blob_test 
(
 id number(4)   primary key ,
 phote blob
 )
 insert into blob_test values(1,'scr/abc.jpg');
  insert into blob_test values(1,empty_blob());
 drop table blob_test
   select * from blob_test

 
 create table clob_test
(
 id number(4)   primary key ,
 phote clob
 )
  insert into clob_test values(1,'scr/abc.jpg');
  insert into blob_test values(1,empty_clob());
  select * from clob_test
  drop table clob_test
-------------------------------------
----18.使用表s,在下面
----(1) 定义一个record变量存放第二条记录, 并输出结果
declare
  type v_record is record
  (
    v_sid s.sid%type,
    v_sn  s.sn%type,
    v_sd  s.sd%type,
    v_sa  s.sa%type
  );
  v_r v_record;
  begin
   select sid,sn ,sd,sa into v_r.v_sid,v_r.v_sn,v_r.v_sd,v_r.v_sa from s  where sid='1001';
    dbms_output.put_line(v_r.v_sid);
    dbms_output.put_line(v_r.v_sn);
    dbms_output.put_line(v_r.v_sd);
    dbms_output.put_line(v_r.v_sa);
end;
---定义一个table 类型变量存放3条记录, 并输出结果
declare
 type v_table is table of varchar2(20) index by varchar2(4);----索引不能是number,因为可能出现小数
 v_t v_table;
 begin
   select sid,sn ,sd,sa into v_t(1),v_t(2),v_t(3),v_t('4') from s where sid ='1002';---自动转化成字符串
   select sid,sn ,sd,sa into v_t('a'),v_t('b'),v_t('c'),v_t('d') from s where sid ='1003';
    dbms_output.put_line(v_t(1));
    dbms_output.put_line(v_t(2));
    dbms_output.put_line(v_t(3));
    dbms_output.put_line(v_t(4));
    dbms_output.put_line(v_t('a'));
    dbms_output.put_line(v_t('b'));
    dbms_output.put_line(v_t('c'));
    dbms_output.put_line(v_t('d'));
 end;
 
 
 declare
 type v_table is table of s%rowtype index by varchar2(4);
 v_t v_table;
 begin
   select sid,sn ,sd,sa into v_t('1').sid,v_t('1').sn,v_t('1').sd,v_t('1').sa from s where sid ='1002';
   select sid,sn ,sd,sa into v_t('2').sid,v_t('2').sn,v_t('2').sd,v_t('2').sa from s where sid ='1003';
  --for v_x in 1..2 loop---int自动转化字符串
    for v_x in '1'..'2' loop
    dbms_output.put_line(v_t(v_x).sid || '  ' ||  v_t(v_x).sn|| '  ' ||  v_t(v_x).sd ||'  ' ||  v_t(v_x).sa );
end loop;  
 end;
 ----------------------------------
 --19,输出学生表student的编号
 declare
  v_sno student.sno%type;
   v_sname student.sname%type;
  begin
  select sno，sname into v_sno,v_sname from student where sno='112'; 
    dbms_output.put_line(v_sno||'  '||v_sname);
end;
-----------------------------------
--20.
create table wage1( ---工资表
    id number(3),
    EmpID      Number(5),
    Name      VarChar2(10),
    Wage      varchar2(11),
    Putdate     Date
)
insert into wage1 values(1,110,'周建安','3000',sysdate);
insert into wage1 values(2,111,'华藏寺','1000',sysdate);
insert into wage1 values(3,112,'展示柜','5400',sysdate);
insert into wage1 values(4,113,'张三','4400',sysdate);
insert into wage1 values(5,114,'展示','7600',sysdate)
select * from wage1
drop table wage1
---求所得税，普通select查询   收入1500 以下税率 0%,1500 ~ 3000  3%，3000 以上   5% 
select Wage as "收入", decode(sign(wage-1500),-1,'0%',
                                           0,'0%',                
                   1,decode(sign(wage-3000),-1,'3%',
                                          0,'3%',
                                          1,'5%' )) as "税率"
                   from wage1 
 ---（1）求所得税(用函数实现)                                          
create or replace function wage_tax(v_wage number) return varchar2
is
   v_return varchar2(11);
begin
     if v_wage<'1500' then
       v_return:=v_wage;
     elsif v_wage<'3000' then
      v_return:=v_wage*0.97;
     else
      v_return:=v_wage*0.95;
      end if;
   return v_return;
end;  
select wage,wage_tax(wage)  from wage1 ;
--(2).求所有雇员中交所得税最多的雇员姓名,所缴的税额(用过程实现)

-------------------------------------------------------
--21.1.对所有员工（Emp表）,如果该员工职位是MANAGER，并且在DALLAS工作那么就给他薪金加15％；
--如果该员工职位是CLERK，并且在NEW YORK工作那么就给他薪金扣除5％;其他情况不作处理。要求：使用游标或函数编程实现。
create table dept(   ---创建部门表(工作表)
    did varchar2(6),
    dname varchar2(20)
)
create table zhiwei(---职位表
   zid varchar2(6),
   zname varchar2(20)
)

 create table emp( ---创建员工表
    eid varchar2(6),
    did varchar2(6),
    zid varchar2(6),
    ename varchar2(20),
    money number(5,2)
)
alter table dept add constraint pk_did primary key(did)
alter table zhiwei add constraint pk_zid primary key(zid)
alter table emp add constraint pk_eid primary key(eid)
alter table emp add constraint fk_zid foreign key(zid) references zhiwei(zid)
alter table emp add constraint fk_did foreign key(did) references dept(did)
insert into dept values('d1','研发部');
insert into dept values('d2','测试部');
insert into dept values('d3','实施部');
insert into dept values('4','产品部');
insert into dept values('d5','人事部');
insert into dept values('d6','财务部');
insert into dept values('d7','法律部')
insert into zhiwei values('z1','总经理');
insert into zhiwei values('z2','部门经理');
insert into zhiwei values('z3','部门组长');
insert into zhiwei values('z4','职员')
alter table emp modify money number(5)
insert into emp values('1','d1','z2','张三',50000);
insert into emp values('2','d1','z3','小米',8000);
insert into emp values('3','d1','z3','小星',5000);
insert into emp values('5','d1','z4','小明',3000);
insert into emp values('6','d1','z4','小三',6000);
insert into emp values('4','d3','z3','王五',7000);
insert into emp values('7','4','z2','额昂藏',9000);
insert into emp values('8','d5','z4','旺财',4000);
insert into emp values('9','d6','z4','小红',8000)
select * from dept;
select * from zhiwei;
select * from emp;
函数是按照行来的
create or replace function a(v_dept in varchar2,v_zhiwei in varchar2,v_money in number) 
 return number
is
   v_return number;
begin
    if v_dept='d1' and v_zhiwei='z3' then
     v_return:=v_money*1.15;---只是查询结果便了，因此要改成update
    -- update emp set money=v_money*1.15 ;----注释：
   elsif v_dept='d3' and v_zhiwei='z3' then
       v_return:=v_money*0.8;
      --  update emp set money=v_money*0.97 ;
   else
     v_return:=v_money;
     -- update emp set money=v_money ;
    end if;
  return v_return;
  end;
  select money from emp;
  select ename,a(did,zid,money) from  emp
 

------第一题，方法1：函数：三个参数，职位，部门，工资。方法2：函数里面封装游标：职位，部门。游标是按行来的，意思是说只操作一行，而且是遍历这一行，其实也更确切的说是操作这行的列，确定行后，找的是每一列
declare
current a is select did,zid,money， eid  from emp;
begin
  for x in a loop
    if xv_dept='d1' and x. v_zhiwei='z3'  then
      update emp set sal=x.sal*1.15 where empid=x.empid;----注释：
      elsif
      end if;
  end;

 

-----第二题：

 

-------------------------------------------------------------
SQL练习：
 select dname,zname from emp inner join zhiwei on emp.zid=zhiwei.zid
   inner join dept on emp.did=dept.did;
------学生表s
create table s(
sid varchar2(4),---学号
sn varchar2(8),---姓名
sd varchar2(20),---单位
sa number(3)---年龄
)
drop table s
alter table s add constraint pk_sid primary key(sid)
insert into s values('1001','琳琳','中国汽车',34);
insert into s values('1002','cioao','中国汽车',51);
insert into s values('1003','张三','中软国籍',37);
insert into s values('1004','白羊','星火',89);
insert into s values('1005','水平','摩卡',32);
insert into s values('1006','双子','软通动力',66);
insert into s values('1007','默写','小蜜蜂',12)
select * from s

-----课程表c
create table c(
cid varchar2(8),--课程编号
cn varchar(10)---课程名称
)
alter table c add constraint cid primary key(cid)
insert into c values('s001','java基础');
insert into c values('s002','Ajax');
insert into c values('s003','javascript');
insert into c values('s004','javabean');
insert into c values('s005','jdbc');
insert into c values('s006','struts');
insert into c values('s007','hibernate');
insert into c values('s008','spring')
select * from c

---成绩表
create table sc(
sid varchar2(4),---学号
cid varchar2(8),--课程编号
g number(3)
)

alter table sc add constraint fk_sid foreign key(sid) references s(sid);
alter table sc add constraint fk_kechengid foreign key(cid) references c(cid)
insert into sc values('1002','s001',99);
insert into sc values('1001','s001',66);
insert into sc values('1001','s001',44);
insert into sc values('1005','s004',78);
insert into sc values('1004','s005',38);
insert into sc values('1002','s008',96)
select * from sc