卸载oracle步骤

不能正确启动监听服务


D:\oracle\ora92\network\admin\listener.ora

D:\oracle\ora92\network\admin\tnsnames.ora  这两个文件中修改用户名

SQL> create user test2 identified by test2;

用户已创建。

SQL> connect test2/test2@erp2;
ERROR:
ORA-01045: user TEST2 lacks CREATE SESSION privilege; logon denied


警告: 您不再连接到 ORACLE。

SQL> grant connect to test2;

授权成功。

create table student(stuid char(5) not null,
name varchar(20) not null,
classid char(3) );
alter table student add primary key(stuid);
grant dba to test2;
create table result(stuid char(5) not null,
socre number(5,2),subject varchar2(10) not null,
examday date not null
)
insert into team values ('t47','47班');
insert into team values ('t44','44班');
insert into student values('t4301','小陈','t43')
insert into student values('t4302','小葛','t43')
insert into student values('t4701','小欧,'t47')
insert into student values('t4702','小玉','t47')
select * from test2.student

insert into result values ('t4301',90,'数学','20-2月-09')
insert into result values ('t4302',90,'数学','20-2月-09')
insert into result values ('t4301',90,'数学','20-3月-09')
insert into result values ('t4301',90,'英语',
to_date('2009-04-22','yyyy-mm-dd')
)
insert into result values ('t4302',96,'英语',
to_date('2009-04-22','yyyy-mm-dd')
)
insert into result values ('t4302',76,'数学',
to_date('2009-04-22','yyyy-mm-dd')
);

insert into result values ('t4701',90,'英语',
to_date('2009-04-22','yyyy-mm-dd')
);

insert into result values ('t4702',96,'英语',
to_date('2009-04-22','yyyy-mm-dd')
);

insert into result values ('t4701',66,'数学',
to_date('2009-05-22','yyyy-mm-dd')
);
insert into result values ('t4701',99,'英语',
to_date('2009-04-22','yyyy-mm-dd')
);

insert into result values ('t4702',86,'英语',
to_date('2009-02-22','yyyy-mm-dd')
);

insert into result values ('t4702',46,'数学',
to_date('2009-01-22','yyyy-mm-dd')
);

select sysdate from dual;

select extract(sysdate,'year') from dual;
SQL> select translate('dcsa87s7d89q234nksjd0asc0asidwj9','#0123456789','#') from dual;

TRANSLATE('DCSA87S7D89Q234NKSJD0ASC0ASIDWJ
------------------------------------------
dcsasdqnksjdascasidwj

select to_char(sysdate,'yyyy-mm') from dual

create table team (id char(3) not null,
name varchar2(10) not null) primary key(id)

select id,team.name,max(socre) ,min(socre) from
  team,result,student
where
and
group by id,team.name
;


select team.id,team.name,max(nvl(result.socre,0)) ,
min(nvl(result.socre,0)) from
team
left join result on result.stuid=student.stuid
left join team on student.classid= team.id
group by team.id,team.name;

selct * from team;

select a.id,a.name,nvl(temp.maxresult,0),
nvl(temp.minresult,0)from
team a,
  (select team.id,team.name,max(socre) as maxresult ,min(socre) as minresult from
   team,result,student
  where result.stuid=student.stuid
  and student.classid= team.id
  group by id,team.name) temp
where temp.id(+)
;
select a.id,a.name,nvl((select max(socre) from result,student where student.classid=a.id and result.stuid=student.stuid),0),
nvl((select min(socre) from result,student where student.classid=a.id and result.stuid=student.stuid),0)
from
team a