课程设计一: 1)创建以上几张表,并给表中输入数据。 学生表1 T_STUDENT create table T_student( stuno varchar2(16) primary key, stuname varchar2(16), stusex varchar2(6), stubir date ) 课程信息表2 T_COURSE create table T_course( courseno varchar2(16) primary key, coursename varchar2(16), teano varchar2(16), foreign key(teano) references T_teacher(teano) ) 教师信息表 3 T_TEACHER create table T_teacher( teano varchar2(16) primary key, teaname varchar2(16), teatitle varchar2(16) ) 考试成绩表 4 T_SCORE create table T_score( stuno varchar2(16), courseno varchar2(16), type varchar2(16), socre float, primary key(stuno,courseno,type), foreign key(courseno) references T_course(courseno) ) 2)查询T_STUDENT中所有的数据 select * from T_student; 3)查询T_STUDENT中所有学生的姓名和性别 select stuname,stusex from T_student; 4)将学号和姓名显示,其中,姓名的格式为:“姓名:xxx” select '姓名:'||stuname as " ",stuno from T_student; 5)为了更好地体现各个学生的考试情况,将T_SCORE中的信息显示,分数显示为与60分的差。 select stuno,courseno,type,score-60 from T_score; 6) 将T_SCORE中的信息显示,分数显示为与60分的差值,列名为“差值”,如果第一条记录分数为空,会得出来什么结果。 (注意:空值具有特殊性,包括空值的任何算术表达式都等于空) select stuno,courseno,type,score-60 as 差值 from T_score; 7) 将学号和姓名显示,其中,列名分别显示为“学生”和姓名。 select stuno as 学号,stuname as 姓名 from T_student; 8) 将学号和姓名显示在一个列中,列名显示为:信息。 select stuno 信息 from T_student union select stuname from T_student; 9) 查询教师的职称种类 select count(distinct teatitle) from T_teacher; 10) 查询女生的姓名 select stuname from T_student where stusex='女'; 11) 查询课程VB的信息 select * from T_course where coursename='VB'; 12) 显示所有期中考试及格的记录 select * from T_score where type='期中'and score>=60; 13) 为了找出考试尖子,需要显示所有期末考试在90-100的考试记录(使用<,> between and) select * from T_score where score>90 and score<=100 and type='期末'; select * from T_score where score between '90'and '100' and type='期末'; 14) 学校要举行一帮一活动,让高分学生帮助低分学生。查询90分以上的期末考试记录,以及不及格的期末考试记录 select * from T_score where (score>90 or score<60) and type='期末' ; 15) 利用BETWEEN谓词显示所有期末考试在65-80的考试记录 select * from T_score where (score between '65' and '85') and type='期末'; 16) 使用IN谓词,显示分数是60,70,80的考试记录 select * from T_score where score in (60,70,80) order by score DESC--降序 ; 17) 查询姓李的学生资料 select * from T_student where stuname like '王%'; 18) 查询姓“王”,名字为一个字的学生,并将这类学生的详细信息显示出来 select * from T_student where stuname like '王_'; 19) 查询性别为空的学生资料 select * from T_student where stusex is NULL; 20) 用升序显示学生S001的所有期末考试成绩 select score from T_score where stuno='S001'and type='期末' order by score--默认为升序; 21) 用降序显示课程C001的所有期末考试成绩,对于相等的成绩,则按照课程编号升序显示。 select courseno,score from T_score where courseno='C001' and type='期末' order by score DESC; 22) 查询姓名为“郭莉芳”的考试成绩 select* from T_score where stuno=( select stuno from T_student where stuname='郭莉芳' ); 23) 显示各个教师及其讲授课程的详细情况 select T_teacher.teaname,T_course.*(这两个顺序有关) from T_teacher right join T_course on T_teacher.teano=T_course.teano; 24) 查询名为“梁天”的教师没有上过的课程。 select coursename from T_course where not exists( select * from T_teacher where T_course.teano=T_teacher.teano and teaname='梁天' ); 25) 课程“大学物理”,有哪些学生选过?请列出这些学生的姓名 select stuname from T_student where stuno=( select stuno from T_score where courseno=( select courseno from T_course where coursename='大学物理' ) ); 26) 查询学号为”S002”的学生,参加课程“C001”考试的成绩,显示格式为: 期中成绩 期末成绩 总评成绩 其中,总评成绩=期中成绩*0.4+期末成绩*0.6 select x.score as 期中成绩,y.score as 期末成绩,x.score*0.4+y.score*0.6 as 总评成绩 from T_score x ,T_score y where x.stuno='S002' and y.stuno='S002'and x.type='期中' and y.type='期末' and x.courseno='C001' and y.courseno='C001'; 27) 查询课程“大学物理”是哪一位老师教的,列出其姓名 select T_teacher.teaname,T_course.coursename from T_teacher ,T_course where T_teacher.teano in ( select teano from T_course where coursename='大学物理') and T_course.coursename='大学物理'; 28) 使用左外连接完成27) select T_teacher.teaname,T_course.coursename from T_teacher left join T_course on T_teacher.teano in ( select teano from T_course where coursename='大学物理') and T_course.coursename='大学物理'; 29) 使用右外连接完成27) select T_teacher.teaname,T_course.coursename from T_teacher right join T_course on T_teacher.teano in ( select teano from T_course where coursename='大学物理') and T_course.coursename='大学物理'; 30) 查询T_STUDENT内所有人的姓名和性别 select stuname,stusex from T_student; 31) 将学号和姓名用下划线连接,显示在一列。 select stuname||'_'||stuno as " " from T_student; 32) 显示教授的所有资料 select * from T_teacher,T_course where T_teacher.teatitle='教授' and T_teacher.teano=T_course.teano; 33) 显示姓张的男生的姓名 select stuname from T_student where stuname like '王%'; 34) 将所有的分数显示为与60分的差值,同时也显示原分数。 select score,score-60 as 与60分的差值 from T_score; 35) 查询高级职称以下的教师姓名,高级职称以下为副教授和讲师 select teaname,teatitle from T_teacher where teatitle in ('副教授','讲师') order by teatitle; 36) 学校需要请学生对教授的教学作评价,因此需要通知相关学生。请查询出:教授所教过的课程,有哪些学生选过?列出他们的姓名 select distinct T_teacher.teaname, T_student.stuname,T_course.coursename from T_teacher,T_student,T_course,T_score where T_teacher.teatitle='教授' and T_teacher.teano=T_course.teano and T_score.courseno=T_course.courseno and T_score.stuno=T_student.stuno order by T_teacher.teaname; 37) 查询郭莉芳的哪些科目期末考试没有及格?列出这些科目的名称和分数 select T_course.coursename,T_score.score from T_course,T_score,T_student where T_student.stuname='郭莉芳' and T_score.score<60 and T_course.courseno=T_score.courseno and T_student.stuno=T_score.stuno and T_score.type='期末'; 38) 统计学生姓名的数量 select count(stuname) from T_student; 39) 查询学校有多少名教师 select count(teaname) from T_teacher; 40) 查询为“梁天”的教师讲了多少门课 select count(teano) from T_course where teano=( select teano from T_teacher where teaname='梁天' ); 41) 查询参加过考试的学生数量 select count(distinct stuno) from T_score; 42) 查询郭莉芳选了多少门课 select count(distinct T_score.courseno) as 郭莉芳选课门数 from T_score,T_student where T_score.stuno=T_student.stuno and T_student.stuname='郭莉芳'; 43) 查询课程C002的期末考试平均分 select AVG(T_score.score) as 课程C002的期末考试平均分 from T_score,T_course where T_course.courseno=T_score.courseno and T_course.courseno='C002' and T_score.type='期末'; 44) 查询课程C003的期中考试总分 select SUM(distinct T_score.score) as 课程C003的期中考试总分 from T_score,T_course where T_course.courseno=T_score.courseno and T_course.courseno='C003' and T_score.type='期中'; 45) 查询学校所有考试记录的总分 select sum(score) from T_score; 46) 查询课程C004的期末考试最高分 select max(T_score.score) from T_score,T_course where T_score.type='期末' and T_score.courseno=T_course.courseno and T_course.courseno='C004'; 47) 查询每个教师讲授的课程数量,并将其姓名和课程数量显示出来 select T_teacher.teaname as 教师,count(T_course.teano) as 课程数量 from T_teacher left join T_course on T_teacher.teano=T_course.teano group by T_teacher.teaname; 48) 查询郭莉芳每门课的平均分,显示课程名称和平均分 select T_course.coursename as 课程,AVG(T_score.score) as 平均分 from T_course,T_score,T_student where T_student.stuno=T_score.stuno and T_score.courseno=T_course.courseno and T_student.stuname='郭莉芳' group by T_course.coursename; 49) 学校要查询哪门课的授课效果最好,请查询各门课程平均分的最大值 做法1: select AVG(T_score.score) as 各门课程平均分的最大值 from T_score,T_course where T_score.courseno in (select courseno from(select courseno,AVG(score), rank()over(order by avg(score) desc)k from T_score group by courseno) where k=1); 做法2: select max(avg(score)) as 各门课程平均分的最大值 from T_score group by courseno having avg(score) in (select avg(score) from T_score group by courseno ); 50) 向T_TEACHER表中添加一条记录,教师编号为T009,教师姓名为汤路名,职称为教授 insert into T_teacher values('T008','汤路名','教授'); 51) 由于有些学生表现比较优秀,拟将其升级为教师。向T_TEACHER表中添加一些记录,这些记录为:平均分75分以上的学生和学号、姓名,他们的职称为助教 insert into T_teacher select stuno,stuname,'助教' from T_student where stuno in ( select stuno from T_score group by stuno having AVG(score)>=75 ); delete from T_teacher where teano='S002'; 52) 删除T_STUDENT表中的女生记录 delete from T_student where stusex='女'; 53) 删除T_COURSE表中的全部记录 先新建一张表: create table T_course1(courseno varchar2(16),coursename varchar2(16),teano varchar2(16)); delete from T_course1; 54) 删除女生的分数记录 delete from T_score where T_score.stuno in (select T_score.stuno from T_student,T_score where T_score.stuno=T_student.stuno and T_student.stusex='女' ); S005 C002 期末 60 55) 将所有的学生分数增加5分 update T_score set score=score+5; 56) 将所有的学生分数增加5分,类型变为“正常考试” update T_score set score=score+5,type='正常考试'; 57) 将所有女生的性别变为GIRL update T_student set stusex='GIRL' where stusex='女'; 58) 创建一个员工工资表,包含各个员工的编号、姓名、性别、出生年月、工资。性别为默认“男”。编号为主键。 create table wage(num varchar(20) primary key,name varchar(20),sex char(5) default '男',bir date,salary number); 59) 将54创建的员工工资表改名; alter table wage rename to wages; 60) 创建一个员工工资表,包含各个员工的编号、姓名、性别、出生年月、工资。其中,员工的编号来自所有学生的学号,员工的姓名来自所有学生姓名,员工的性别来自学生性别,出生年月来自学生的出生年月,工资为0 (利用:CREATE TABLE 表名【列名1(,列名2…..)】 AS 子查询 ) create table wage(num primary key,name,sex,bir,salary) as select stuno,stuname,stusex,stubir,'0' from T_student; 61) 在T_TEACHER表中增加性别和出生年月列 alter table T_teacher add (sex char(10),bir date); 62) 在T_TEACHER表中删除性别和出生年月列 alter table T_teacher drop (sex,bir); 63) 在T_TEACHER表中,将TEANAME重命名为“教师姓名” alter table T_teacher rename column 教师姓名 to teaname; 64) 在T_TEACHER表中,将TEATITLE的数据类型改为VARCHAR2(30) alter table T_teacher modify(teatitle varchar2(30)); 65) 创建一个员工工资表,包含各个员工的编号、姓名、性别、出生年月、工资。编号和姓名组合为主键。 create table wage2(num varchar(20),name varchar(20),sex char(5) default '男',bir date,salary number, primary key(num,name) ); 66) 创建一个员工工资表,包含各个员工的编号、姓名、性别、出生年月、工资。编号为主键,姓名唯一 create table wage66(num varchar(20) primary key,name varchar(20) unique,sex char(5) ,bir date,salary number); 67) 将T_STUDENT表中的STUNAME指定为唯一性约束 alter table T_student add constraint stuname unique(stuname); 68) 将65题中指定的唯一性约束删除 alter table T_student drop constraint stuname; 69) 创建一个员工工资表,包含各个员工的编号、姓名、性别、出生年月、工资。编号为主键,性别只能是男或者女 create table wage69(num varchar(20) primary key,name varchar(20),sex varchar2(10) check(sex in('男','女')),bir date,salary number); 70) T_SCORE中的分数,要求在0-100之间 alter table T_score add constraint score check(score between 0 and 100); 71) 为T_STUDENT的STUNAME建立索引 create index idx_stuname on T_student(stuname); 72) 删除71题建立的索引 drop index idx_stuname; 73) 女生部长关心女生的信息,请将女生信息创建为一个视图 create view female as select T_student.*,T_score.courseno,T_score.type,T_score.score from T_student right join T_score on T_student.stuno=T_score.stuno; drop view glf; 74) 郭莉芳关心自己参加考试的课程编号、名称和考试平均成绩,请建立视图 create view glf(courseno,coursename,score) as select T_course.courseno,T_course.coursename,AVG(score) from T_score , T_course,T_student where T_student.stuname='郭莉芳' and T_student.stuno=T_score.stuno and T_course.courseno=T_score.courseno group by T_course.courseno,T_course.coursename; 75) 将全校的学生分数平均数查出来,存储在变量avascore内并打印 declare avascore T_score.score%type; begin select avg(score) into avascore from T_score; dbms_output.put_line('平均值是: '||avascore); end; / 76) 定义一个表变量stunames,请将几名学生的姓名放入,并打印 declare stunames T_student%rowtype; result T_student.stuname%type; cursor cur_name is select stuname into stunames.stuname from T_student; begin if not cur_name%isopen then open cur_name; end if; loop fetch cur_name into result; exit when cur_name%notfound; dbms_output.put_line('姓名是: '||result); end loop; close cur_name; end; / 77) 查询郭莉芳的所有科目平均成绩,决定她的等级。90-100为优秀,70-89为良好, 60-69为及格,其他为差。请将其分数和等级打印出来。 select avg(score),decode(sign(avg(score)-90),1,'优秀',0,'优秀',-1, decode(sign(avg(score)-70),1,'良好',0,'良好',-1, decode(sign(avg(score)-60),1,'及格',0,'及格',-1,'不及格'))) from T_score,T_student where T_student.stuname='郭莉芳' and T_score.stuno=T_student.stuno; 78) 编写存储过程,打印全校所有学生平均分 create or replace procedure p_score (tt_stuno t_score.stuno%type, tt_score out t_score.score%type) as begin select avg(score) into tt_score from t_score where stuno=tt_stuno; end; / declare ttt_score varchar2(50); begin p_score('S001',ttt_score); dbms_output.put_line('平均成绩是:'||ttt_score); end; / 79) 编写一个存储过程,输出一个学生的学号,将这个学生所选的课程的数量、所有课程的平均分存储在两个输出参数内 create procedure pro790(sl,ag,cur_out cursor) as begin open cur_out for select T_student.stuno,count(T_score.stuno) from T_score,T_student where T_student.stuno=T_score.stuno; end; / 80) 修改某门课程的任课教师。如果任课教师室教授,不可以进行修改 create or replace trigger cc Before update on t_course For each row Declare tea_title t_teacher.teatitle%type; Begin Select teatitle into tea_title from t_teacher Where teano=:old.teano; if tea_title='教授' then RAISE_APPLICATION_ERROR(-20000, '是教授,不能修改表'); End if; End; / 验证 update t_course set teano='T009' where teano='T003'; 81) 显示教授的姓名,用空格隔开,保存在一个字符串内,打印。 set serveroutput on declare cursor cur_name is select teaname from T_TEACHER WHERE t_teacher.TEATITLE='教授'; medi_name T_TEACHER.TEATITLE%type; result varchar2(200); begin open cur_name; loop fetch cur_name into medi_name; exit when cur_name%notfound; result:=result||' '||medi_name; end loop; dbms_output.put_line(result); close cur_name; end;