oracle 数据库增删改查练习50例-答案.pdf

oracle 数据库增删改查练习50例-答案 一、建表 --学生表 drop ta le student; create ta le student (sno varchar2(10),sname varchar2(10),sage date,ssex varchar2(10)); insert into student values(01,赵雷,to_date(1990/01/01,yyyy/mm/dd),男); insert into student values(02,钱电,to_date(1990/12/21,yyyy/mm/dd),男); insert into student values(03,孙风,to_date(1990/05/20,yyyy/mm/dd),男); insert into student values(04,李云,to_date(1990/08/06,yyyy/mm/dd),男); insert into student values(05,周梅,to_date(1991/12/01,yyyy/mm/dd),女); insert into student values(06,吴兰,to_date(1992/03/01,yyyy/mm/dd),女); insert into student values(07,郑竹,to_date(1989/07/01,yyyy/mm/dd),女); insert into student values(08,王菊,to_date(1990/01/20,yyyy/mm/dd),女); --课程表 drop ta le course; create ta le course (cno varchar2(10),cname varchar2(10),tno varchar2(10)); insert into course values (01,语文,02); insert into course values (02,数学,01); insert into course values (03,英语,03); --教师表 drop ta le teacher; create ta le teacher (tno varchar2(10),tname varchar2(10)); insert into teacher values(01,张三); insert into teacher values(02,李四); insert into teacher values(03,王五); --成绩表 drop ta le sc; create ta le sc (sno varchar2(10),cno varchar2(10),score num er(18,1)); insert into sc values(01,01,80.0); insert into sc values(01,02,90.0); insert into sc values(01,03,99.0); insert into sc values(02,01,70.0); insert into sc values(02,02,60.0); insert into sc values(02,03,80.0); insert into sc values(03,01,80.0); insert into sc values(03,02,80.0); insert into sc values(03,03,80.0); insert into sc values(04,01,50.0); insert into sc values(04,02,30.0); insert into sc values(04,03,20.0); insert into sc values(05,01,76.0); insert into sc values(05,02,87.0); insert into sc values(06,01,31.0); insert into sc values(06,03,34.0); insert into sc values(07,02,89.0); insert into sc values(07,03,98.0); commit; 二、查询 1.1、查询同时存在01课程和02课程的情况 select s.sno, s.sname, s.sage, s.ssex, sc1.scor