瑞星卡卡安全论坛

《1》子查询和关联查询
建立表如下:

学生基本信息表
CREATE Student(
[Studentid][Int]IDENTITY(1,1)NOT NULL primary key,--主键
[StudentName][char]NOT NULL

)
课程信息表
CREATE Subject(
[SubjectID][char]NOT NULL primary key,         --主键
[SubjectName][char]NOT NULL
)
成绩表
CREATE Grade(
[Studentid][Int]NOT NULL,        --联合主键
[SubjectID][char]NOT NULL,        --联合主键
[Grade] [INT]NOT NULL,
primary key (studentid,subjectid)
)

1.将建表命令改为ORACLE语句在ORACLE中建表
create table student(
studentid number(3) primary key,
studentname varchar2(20) );

create table subject(
subjectid char(3) primary key,
subjectname varchdemar2(20)
);


create table grade(
studentid number(3) references student(studentid),
subjectid char(3) references subject(subjectid),
mark      number(3),
primary key (studentid,subjectid)
);

insert into student values (101,'张三');
insert into student values (102,'李云');
insert into student values (103,'未');

insert into subject values ('A01','C++');
insert into subject values ('A02','ASP');
insert into subject values ('A03','JAVA');


insert into grade values (101,'A01',59);
insert into grade values (101,'A02',72);
insert into grade values (101,'A03',90);

insert into grade values (102,'A01',75);
insert into grade values (102,'A02',91);

insert into grade values (103,'A01',71);


2.作如下4题

第一问：查询出以下信息

学号 学生姓名 课程名称 成绩 （要全部学生信息）

关联查询 (多张表的)
别名

select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",mark "成  绩"
from student a , subject b , grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid



第二问：查询出以下信息

学号 学生姓名 课程名称 成绩（只显示每科最高分）
select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",mark "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
and (subjectname,mark)
in (select subjectname "课程名称",max(mark) "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
group by subjectname)


select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",mark "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
and (select count(*) from grade
where subjectid = b.subjectid and
mark > c.mark) = 0


第三问：查询出以下信息

学号 学生姓名 课程名称 成绩 （成绩大于60时的显示及格，小于60时的显示不及格）

select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",
decode(sign(mark-60),-1,'不及格','及格') "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid

第四问：查询出以下信息

学号 学生姓名 （查询出课程超过1门以上学生的信息）

select a.studentid "学  号",studentname "学生姓名"
from student a , subject b , grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
group by a.studentid,studentname
having count(subjectname) >= 2



《2》复制一张表的结构
--数据一起复制
  create table mydept as select * from dept;
--只复制结构
  create table mydept1 as select * from dept where 1=2;
--把数据从一个表复制到另一个结构相同的表
  insert into mydept1 select * from dept;
--列的别名 
  select ename "员工 姓名" from emp;

a.没有主键的表如何消重复记录

create table test(
    id number(2),
    name varchar2(10));

  insert into test values (1,'aa');
  insert into test values (1,'aa');
  insert into test values (1,'aa');
  insert into test values (2,'bb');
  insert into test values (3,'cc');
 
  create table test1 as select distinct * from
  test;
  或者
  create table test1 as select id,name from test
  group by id,name;

 
  rename test to test2;
  rename test1 to test;

b.有主键消重复行
 
create table test(
    id number(2) primary key,
    name varchar2(10));

  insert into test values (1,'aa');
  insert into test values (2,'aa');
  insert into test values (3,'aa');
  insert into test values (4,'bb');
  insert into test values (5,'cc');

create table test1 as select name from test
group by name;

alter table test1 add (id number(2));

update test1 set id=(select max(id) from test
  where name=test1.name);

create table test2 as select id,name from test1;

rename test to testb;
rename test2 to test;

alter table test add primary key (id);