跟我学Oracle从入门到精通培训教程Oracle课程的课后作业及答案.docx
《跟我学Oracle从入门到精通培训教程Oracle课程的课后作业及答案.docx》由会员分享，可在线阅读，更多相关《跟我学Oracle从入门到精通培训教程Oracle课程的课后作业及答案.docx（41页珍藏版）》请在冰点文库上搜索。
跟我学Oracle从入门到精通培训教程Oracle课程的课后作业及答案
1.1跟我学Oracle从入门到精通培训教程——Oracle课程的课后作业及答案
1.1.1课后作业1及答案
1、按照如下的要求设计一个数据库中的三个数据库表结构及定义每个数据库表的完整性要求（最好在Oracle和MySQL中同时实现）
（1）Student学生数据库表的结构
Student（Sno，Sname，Ssex，Sage，Sdept），其中属性Sno、Sname、Ssex、Sage和Sdept分别表示学号、姓名、性别、年龄和所在系。
该Student数据库表的完整性要求如下：
Sno为主键
Sname不为空
Ssex只能为“男”或者“女”
Sage只能在15-35岁之间
Sdept的默认值为软件学院
（2）Course课程信息数据库表的结构
Course（Cno，Cname，Cpno，Ccredit），其中属性Cno、Cname、Cpno和Ccredit分别表示课程号、课程名、先修课号和学分。
该Course数据库表的完整性要求如下：
Cno为主键
Cname不为空
Cpno不为空，并且只能在1—10之间
Ccredit不为空，并且只能是1、2、4三种可能的值
（3）CourseResult学生选课结果信息数据库表结构
CourseResult（Sno，Cno，Grade），其中属性Sno、Cno和Grade分别表示学号、课程号和成绩。
该CourseResult数据库表的完整性要求如下：
主键为复合主键（Sno，Cno），不能为空值
Grade为百分制成绩（0---100之间）
2、对上面的Student、Course和CourseResult三个数据库表按照如下的要求设计出相关的查询SQL语句（高级部分，其中需要用到明天学习的内容，可以提前预习）
（1）查询每个学生（姓名）选了哪门课（课程名）得了多少分？
（2）查询计算机系中男生、女生分别多少人？
（3）查询选修人数在三人（包括三人）的课程
（4）查询哪个学生没有选课？
（使用notin、notexists以及外关联三种方法）
--创建学生表
createtablestudent（
snonumberprimarykey,
snamevarchar（20）,
ssexchar
（2）check（ssexin（'男','女'））,
sagenumber（3）check（sage>18andsage<120）,
sdeptvarchar（20）
）;
droptablestudent;--删除学生表
--创建课程表
createtablecourse（
cnonumberprimarykey,
cnamevarchar（20）,
cpnonumber,
ccreditnumber
（2）check（ccredit>0andccredit<5）
）;
droptablecourse;--删除课程表
--创建学生选课表
createtableCourseResult（
snonumberreferencesstudent（sno）,
cnonumberreferencescourse（cno）,
gradenumber（3）check（grade>0andgrade<101）,
primarykey（sno,cno）--复合组键
）;
droptableCourseResult;--删除学生选课表
--添加信息
insertintostudent（sno,sname,ssex,sage,sdept）values（0903100216,'李闯','男',23,'计算机系'）;
insertintostudent（sno,sname,ssex,sage,sdept）values（0903100206,'张三','女',24,'商贸旅游系'）;
insertintostudent（sno,sname,ssex,sage,sdept）values（0903100222,'王五','男',24,'机械系'）;
insertintostudent（sno,sname,ssex,sage,sdept）values（0903100221,'武大','男',24,'外语系'）;
insertintostudent（sno,sname,ssex,sage,sdept）values（0903100220,'孙悟空','男',24,'工商系'）;
insertintostudent（sno,sname,ssex,sage,sdept）values（0903100219,'猪八戒','女',22,'外语系'）;
insertintostudent（sno,sname,ssex,sage,sdept）values（0903100218,'沙僧','女',28,'电气系'）;
insertintocourse（cno,cname,cpno,ccredit）values（001,'java高级编程',10086,2）;
insertintocourse（cno,cname,cpno,ccredit）values（002,'旅游英语',10010,2）;
insertintocourse（cno,cname,cpno,ccredit）values（003,'自动化',11000,2）;
insertintocourse（cno,cname,cpno,ccredit）values（004,'数控机床',11110,2）;
insertintoCourseResult（sno,cno,grade）values（0903100216,001,90）;
insertintoCourseResult（sno,cno,grade）values（0903100206,002,70）;
insertintoCourseResult（sno,cno,grade）values（0903100222,003,80）;
insertintoCourseResult（sno,cno,grade）values（0903100221,003,50）;
insertintoCourseResult（sno,cno,grade）values（0903100220,003,60）;
--查询信息
select*fromstudent;
select*fromcourse;
select*fromCourseResult;
--查询每个学生选了哪门课得了多少分
（1）应用内连接实现
selectstudent.sname,ame,CourseResult.gradefrom
studentinnerjoinCourseResultonstudent.sno=CourseResult.sno
innerjoincourseonCourseRo=o;
（2）应用左外连接实现
selectstudent.sname,ame,CourseResult.gradefrom
studentleftjoinCourseResultonstudent.sno=CourseResult.sno
leftjoincourseonCourseRo=o;
--查询选修人数在三人（包括三人）的课程
selectcnamefromcoursewhere
cnoin（selectcnofromCourseResultgroupby（cno）havingcount（sno）>=3）;
--查询计算机系中的男生、女生分别多少人
（1）不采用分组实现
selectcount（ssex）fromstudentwheressex='男';
selectcount（ssex）fromstudentwheressex='女';
（2）采用分组实现，并应用having条件
selectcount（*）fromstudentgroupbyssex,sdepthavingsdept='计算机系';
（3）采用分组实现，并应用where条件
selectcount（*）fromstudentwheresdept='计算机系'groupbyssex;
--查询哪个学生没有选课
select*fromstudentwheresnonotin（selectCourseResult.snofromCourseResult）;
select*fromstudentwherenotexists（selectCourseResult.snofromCourseResult）;
1.1.2课后作业2及答案
1、已知客户表customer的结构为（customerID、name、address）、登陆流水表loginLanding的结构为（id、customerID、time）、购物流水表shopLanding的结构为（id、customerID、time、productid、productnum）
（1）获得每个客户的最新登陆时间time、姓名name和客户customerID等信息
（2）采用一条SQL语句实现查最新登陆并且已经购买商品的客户的customerID和name、登陆的时间time等信息
createtablecustomer（
customeridnumberprimarykeynotnull,
namevarchar（20）,
addressvarchar（20）
）;
createtableloginlanding（
idnumberprimarykeynotnull,
customeridnumberreferencescustomer（customerid）,
timedatedefaultsysdate
）;
createtableshoplanding（
idnumberreferencesloginlanding（id）,
customeridnumberreferencescustomer（customerid）,
timedatedefaultsysdate,
productidnumber,
productnumnumber,
primarykey（productid）
）;
insertintocustomer（customerid,name,address）values（001,'张三','河北省'）;
insertintocustomer（customerid,name,address）values（002,'李四','河北省'）;
insertintocustomer（customerid,name,address）values（003,'王五','北京市'）;
insertintocustomer（customerid,name,address）values（004,'赵六','河南省'）;
insertintocustomer（customerid,name,address）values（005,'李大伟','湖南省'）;
select*fromcustomer;
insertintologinlanding（id,customerid,time）values（9001,001,sysdate）;
insertintologinlanding（id,customerid,time）values（9002,002,sysdate）;
insertintologinlanding（id,customerid,time）values（9003,003,sysdate）;
insertintologinlanding（id,customerid,time）values（9004,004,sysdate）;
select*fromloginlanding;
insertintoshoplanding（id,customerid,time,productid,productnum）
values（9001,001,sysdate,1001,20）;
insertintoshoplanding（id,customerid,time,productid,productnum）
values（9002,002,sysdate,1002,50）;
insertintoshoplanding（id,customerid,time,productid,productnum）
values（9003,003,sysdate,1003,100）;
select*fromshoplanding;
（1）获得每个客户的每次登陆时间time、姓名name和客户customerID等信息
selectcustomer.customerid,customer.name,loginlanding.time
fromcustomerinnerjoinloginlanding
oncustomer.customerid=loginlanding.customerid;
（2）获得每个客户的最新登陆时间time、姓名name和客户customerID等信息
selectcustomer.customerid,customer.name,max（loginlanding.time）
fromcustomerinnerjoinloginlanding
oncustomer.customerid=loginlanding.customerid
groupbycustomer.customerid,customer.name;
（3）获得所有客户的登录状况信息（已登录或者没有登录过系统）
selectcustomer.customerid,customer.name,max（loginlanding.time）
fromcustomerleftouterjoinloginlanding
oncustomer.customerid=loginlanding.customerid
groupbycustomer.customerid,customer.name;
selectshoplanding.customerid,shoplanding.productid,shoplanding.productnum,customer.name,customer.address,loginlanding.timefrom
customerinnerjoinloginlandingoncustomer.customerid=loginlanding.customerid
innerjoinshoplandingonshoplanding.customerid=customer.customerid;
1.1.3课后作业3及答案
1、现有如下的数据库表
1）员工信息表employees中主要有employee_id（员工编号）、last_name（姓名）、job_id（职位）、yearSalary（年薪）、department_id（部门编号）等字段；
2）职位信息表jobs中主要有job_id（职位）、job_title（职位全称）、grade_id（工资级别编号）等字段；
3）工资级别表salgrades中主要有grade_id（工资级别编号）、grade_level（工资级别）、lowest_salary（最低工资）、highest_salary（最高工资）等字段；
4）部门信息表departments中主要包括有department_id（部门编号）、department_name（部门名称）、location_id（位置编号）等字段；
（1）试创建视图v_emp_80，包含80号部门的员工编号，姓名，年薪列。
（2）从视图v_emp_80中查询年薪在12万元以上的员工的信息。
（3）创建试图v_dml，包含部门编号大于100号的部门的信息。
（4）向视图v_dml中插入如下记录信息：
部门编号360，部门名称AAA，位置编号1700
（5）从视图v_dml中删除300号以上的部门信息。
--3-8作业员工信息表
createtableemployees（
employee_idnumber,
last_namevarchar（20）,
job_idnumberreferencesjobs（job_id）,
yearsalarynumber,
department_idnumberreferencesdepartments（department_id）,
primarykey（employee_id）
）;
droptableemployees;
--jobs职位信息表
createtablejobs（
job_idnumber,
job_titlevarchar（20）,
grade_idnumberreferencessalgrades（grade_id）,
primarykey（job_id）
）;
droptablejobs;
--salgrades工资级别表
createtablesalgrades（
grade_idnumber,
grade_leve1char（10）,
lowest_salarynumber,
highest_salarynumber,
primarykey（grade_id）
）;
droptablesalgrades;
--departments部门信息表
createtabledepartments（
department_idnumber,
department_namevarchar（20）,
location_idnumber,
primarykey（department_id）
）;
--部门信息
insertintodepartments（department_id,department_name,location_id）values（01,'java开发',001）;
insertintodepartments（department_id,department_name,location_id）values（02,'java测试',002）;
insertintodepartments（department_id,department_name,location_id）values（03,'java溜达',003）;
insertintodepartments（department_id,department_name,location_id）values（04,'java溜达',004）;
select*fromdepartments;
--工资级别表
insertintosalgrades（grade_id,grade_leve1,lowest_salary,highest_salary）values（101,'低',10000,13000）;
insertintosalgrades（grade_id,grade_leve1,lowest_salary,highest_salary）values（102,'中',13000,15000）;
insertintosalgrades（grade_id,grade_leve1,lowest_salary,highest_salary）values（103,'高',15000,20000）;
insertintosalgrades（grade_id,grade_leve1,lowest_salary,highest_salary）values（104,'特高',20000,25000）;
select*fromsalgrades;
--职位信息
insertintojobs（job_id,job_title,grade_id）values（201,'web前段',101）;
insertintojobs（job_id,job_title,grade_id）values（202,'程序测试',102）;
insertintojobs（job_id,job_title,grade_id）values（203,'溜溜达达',103）;
insertintojobs（job_id,job_title,grade_id）values（204,'溜溜达',104）;
select*fromjobs;
--员工信息
insertintoemployees（employee_id,last_name,job_id,yearsalary,department_id）values（0001,'张三',201,100000,01）;
insertintoemployees（employee_id,last_name,job_id,yearsalary,department_id）values（0002,'张二',202,150000,02）;
insertintoemployees（employee_id,last_name,job_id,yearsalary,department_id）values（0003,'张一',203,200000,03）;
insertintoemployees（employee_id,last_name,job_id,yearsalary,department_id）values（0004,'张零',204,200000,04）;
select*fromemployees;
--第一题试创建视图v_emp_003，包含003号部门的员工编号，姓名，年薪列。
createorreplaceviewv_emp_003asselectemployee_id,last_name,yearsalary,departments.department_idfrom
departmentsinnerjoinemployeesondepartments.department_id=employees.department_id;
selectemployee_id,last_name,yearsalary,depart
升级会员 