Oracle练习

1.查询所有学员从入学到今天，一共度过了多少天
select  stuname,sysdate-stujointime from studentinfo;
2.查询你的生日日期到现在相隔的年数（月数/12）
select  months_between(sysdate,'09-9月-1994')/12 from dual;
2.查询每月2号入学的学员信息
select * from studentinfo where extract(day from stujointime)=2;
2.查询周末入学的学员 ‘d’
select * from studentinfo where to_char(stujointime,'d')='1';
select * from studentinfo where to_char(stujointime,'day')='星期日';

select to_char(stujointime,'d') from studentinfo;
select to_char(sysdate,'d') from dual;
3.查询所有学员的毕业日期。假定按每个学员入学时间1年半之后毕业
select stuname,add_months(stujointime,18) from studentinfo; 
4.查询星期四入学的学员姓名，性别，年龄，班级编号  =5?
select stuname,stusex,stuage,sclassid from studentinfo where to_char(stujointime,'d')='5';
select stuname,stusex,stuage,sclassid from studentinfo where to_char(stujointime,'day')='星期四';
5.查询2007-3-10 之前入学de 学员信息  <
select * from studentinfo where stujointime < to_date('2007-3-10','yyyy-mm-dd'); 
6.查询所有学员姓名的长度
select stuname,length(stuname) from studentinfo;
7.查询身份证中第9,10位为89的学员信息（要求使用字符串函数）
 select * from studentinfo where substr(stucard,instr(stucard,'89'),2)='89';
8.修改班主任信息，将邮箱中的yahoo替换为accp
    select replace(teacheremail,'yahoo','accp') from teacherinfo; 
9.查询所有班主任的邮箱的用户名
select substr(teacheremail,1,instr(teacheremail,'@')-1) from teacherinfo;
10.查询所有班主任的邮箱的所属网站
  select substr(teacheremail,instr(teacheremail,'@')+1,instr(teacheremail,'.')-instr(teacheremail,'@')-1) from teacherinfo;

1.列出至少有一个员工的所有部门。
select dname from emp,dept where emp.deptno=dept.deptno group by dname;
2.列出薪金比“SMITH”多的所有员工。
select ename from emp where sal >(select sal from emp where ename='SMITH');
3.列出所有员工的姓名及其直接上级的姓名。
select a.ename,b.ename from emp a,emp b where a.mgr=b.empno;
4.列出受雇日期早于其直接上级的所有员工。
select a.ename from emp a,emp b where a.mgr=b.empno and a.hiredate<b.hiredate;
5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select dname,emp.* from dept left join emp on dept.deptno=emp.deptno;
6.列出所有“CLERK”(办事员)的姓名及其部门名称。
select ename,dname from emp,dept where dept.deptno=emp.deptno and job='CLERK';
7.列出最低薪金大于1500的各种工作。
select job from emp group by job having min(sal)>1500;
8.列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。
select ename from emp,dept where dept.deptno=emp.deptno and dname='SALES';
9.列出薪金高于公司平均薪金的所有员工。
select ename from emp where sal>(select avg(sal) from emp);
10.列出与“SCOTT”从事相同工作的所有员工。
select ename from emp where job=(select job from emp where ename='SCOTT');

练习三：同义词
1、新建用户newdream，并为newdream用户创建访问Scott用户emp表的私有同义词,
来简化SQL，并测试。
create user newdream identified by 123;
grant create synonym to newdream;
grant select on emp to newdream;
create synonym a for scott.emp;
select * from a;
2、为Scott用户的emp表创建公有同义词以供其他用户访问，并测试。
create public synonym  aaa for emp;
select * from aaa;
3、删除刚创建的两个同义词
drop synonym a;
drop public synonym aaa;
练习四：序列
1、创建如下表：
Create table Test
(	tid number primary key not null,
 tname varchar2(20)
);
1、tid的取值为从2开始的偶数，无限增长（如：2，4，6，8，10…）
2、请为该表创建序列，并插入5条记录
3、删除刚创建的序列

create sequence test_seq2
start with 2
increment by 2;
delete test;
insert into test values(test_seq2.nextval,'123');
insert into test values(test_seq2.nextval,'456');
insert into test values(test_seq2.nextval,'789');
select *from test;
drop sequence test_seq;
练习五：视图
1、对于用户而言，需要经常使用到Scott用户emp表的ename员工姓名，
sal薪水两列，请给出合适的解决方案。（提示：创建视图）
create view a
as
select ename 员工姓名,sal 薪水 from emp;
select * from a;
2、在上题基础上，修改视图定义，要求显示emp表的所有列。（提示：替换视图）
replace view a
as
select * from emp;
3、现在需要查看emp表中的员工分别属于哪个部门，即要求显示员工编号，
员工姓名和部门名称，请创建相应视图实现。（提示：连接视图）
create view b
as
select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;
select * from b;
4、删除刚创建的视图
drop view b;
练习六：索引
编写代码实现如下功能：
1、为Test表的Tid列创建标准索引
drop index SYS_C009673;
alter index SYS_C009673  rebuild;
2、为Test表的TName列创建唯一索引
drop index t_dex;
create unique index a_dex on test(tname);
select * from test;
3、因用户经常要使用Test表的Tid列和TName列作为查询条件，
为提高查询速度，请为这两列创建组合索引
create index t_dex on test(tid,tname);
4、Test表有TNo列表示一个顺序增长的序号，请为该列创建反向键索引，
以提高查询速度
alter table test add tno number ;
create sequence tno_seq;
create index pk_dex on test(tno) reverse;
5、Test表中有TCode列，该列只有几十个取值，请在该列上创建位图索引，
以提高查询速度
alter table test add tcode number ;
create bitmap index tc_dex on test(tcode);
6、删除刚创建的索引
drop index xxx;

练习一、使用条件结构
问题描述：公司决定向职员（emp表）发放奖金，
发放原则根据职员所在部门编号（deptno）来计算。
如果部门编号为“10”，奖金为2000元；部门编号为20,奖金为1700元；
部门编号为“30”，奖金为1500元，请用条件结构解决该问题。

提示：首先根据用户输入的员工编号（empno）获得其对应的部门编号（deptno）
，然后根据部门编号（deptno）判断，更新薪水sal。
declare
   pempno emp.empno%type:='&请输入员工编号';
   pdeptno emp.deptno%type;
begin
   select deptno into pdeptno from emp where empno=pempno;
   if pdeptno=10 then
      dbms_output.put_line('奖金为2000元');
   elsif pdeptno=20 then
      dbms_output.put_line('奖金为1700元');
   elsif  pdeptno=30 then
       dbms_output.put_line('奖金为1500元');
   else
       dbms_output.put_line('nononono');
   end if;
end;
/
练习二、使用循环结构
问题描述：请编写一个程序，通过循环向course_details表中插入10条件记录，
要求学生编号每次累加1，课程由用户输入。

表结构：
create table course_details
(
	stuid number,
	course varchar2(20)
);

例如接收的课程为oralce
效果如下：
	stuid				course
1oracle
2oracle
3oracle
4oracle
5oracle
Begin
For  i in  1..5
Loop
   C :='&c';
   Inesert ..............
End loop;
End;
请编写一个程序，通过循环向course_details表中插入10条件记录，
要求学生编号每次累加1，课程由用户输入。
declare
    i number:=1;
    course varchar2(20):='&请输入course';
begin
    loop
      insert into course_details  values(i,course);
      i:=i+1;
      exit when i>10;
      end loop;
end;
/
select * from course_details;
练习三、编写一个程序，显示1至100之间的素数（质数）。
提示：
1、素数（质数）：只能被1和本身整除的数。
2、使用for循环
declare
       i number;
       j number;
       z number:=0;
begin
       for i in 1.. 100  loop
       while j<=i loop
          if mod(i,j)=0 then
             z:=z+1;
          end if;
          j:=j+1;
         end loop;
         j:=0;
        if z = 2 then 
           dbms_output.put_line(i);         
        end if; 
           z:=0;
    end loop;
end;
/

declare
   inum number:=2;
   jnum number;
   flag number;   
begin
      
       loop
          exit when inum>100;
          flag :=1;---假设inum是质数
          jnum:=2;
          loop
               exit when jnum>=inum;
               if mod(inum,jnum)=0 then
                  flag:=2;
               end if;
               jnum:=jnum+1;
          end loop;
          
          if flag=1 then
             dbms_output.put_line(inum||'是质数');
          end if;
          
          --dbms_output.put_line(inum);          
          inum:=inum+1;
       
       end loop;

end;
/

打印输出1-10

create or replace procedure
qwe(a out number)
as
begin
     a:=1;
    for a in 1.. 10 loop
        dbms_output.put_line(a);
     end loop;       
end;
/
declare
   a number;
begin
    qwe(a);
end;
/

按员工工种涨工资，总裁1000，经理800，员工400

create or replace procedure
sal(i IN OUT varchar2 )
as
begin
      IF i= 'PRESIDENT' THEN
      update emp set sal=sal+1000;
      ELSIF i ='MANAGER' THEN
      update emp set sal=sal+800 ;
      ELSIF i ='CLERK' THEN
      update emp set sal=sal+400 ;
      END IF;
end;
/
declare
      job VARCHAR2(200):='&INPUT';
     
begin
      sal(job); 
      COMMIT;
end;
/  

输入a，b两个数，如果a>b弹出自定义错误，否则打印a<=b

declare
       anum number:='&first';
       bnum number:='&second';
       my_exception Exception;
begin
       if anum > bnum then
          raise my_exception;
       else
          dbms_output.put_line(anum||'<='||bnum);
       end if;
end;
/

给指定员工涨薪100，并打印涨前涨后薪资。

create or replace procedure
zhangxin(name  varchar2)
as
begin          
   
           update emp set sal=sal+100 where name=ename;
           COMMIT;
end;
/
declare
           i varchar2(20):='&name';
           psal emp.sal%type;
           
begin
           select sal into psal from emp where i=ename;
           dbms_output.put_line(psal);
           zhangxin(i);
           select sal into psal from emp where i=ename;
           dbms_output.put_line(psal);
end;
/