OracleSql练习.doc

简单sql及分组解锁HR用户。（1）以SYSTEM或SYS登录ORACLE的企业管理器，点击安全性-用户。（2）在屏幕右方，双击HR用户。（3）在弹出屏幕上，修改HR的用户密码为：test;（4）单击未锁定按钮。（5）单击确定按钮。此时，解锁HR用户，其密码更改为：test;（6）查看HR中各表的结构及数据？2使用HR/TEST登录SQLPLUS，完成下列各题。（1）下面的SELECT语句是否执行成功：SELECTlast_name,job_id,salaryASSalFROMemployees;（2）在下面的语句中有4个编码错误，你能找出他们吗？SELECTemployee_id,last_namesalx12ANNUALSALARYFROMemployees;（3）创建一个查询，显示收入超过$12,000的雇员的名字和薪水（4）显示所有在部门20和50中的雇员的名字和部门号，并以名字按字母顺序排序。（5）写一个查询用首字母大写，其它字母小写显示雇员的lastnames，显示名字的长度，对所有名字开始字母是J、A或M的雇员，给每列一个适当的标签。用雇员的lastnames排序结果。（6）写一个查询显示所有雇员的lastname、departmentnumber、anddepartmentname。（7）.创建一个查询显示所有与被指定雇员工作在同一部门的雇员(同事)的lastnames、departmentnumbers。给每列一个适当的标签。（8）.显示所有雇员的最高、最低、合计和平均薪水，列标签分别为：Maximum、Minimum、Sum和Average。四舍五入结果为最近的整数。（9）.显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人。排除最低薪水小于等于$6,000的组。按薪水降序排序输出。（10）.写一个查询显示与Zlotkey在同一部门的雇员的lastname和hiredate，结果中不包括Zlotkey。（11）显示在Executive部门的每个雇员的departmentnumber、lastname和jobID。（12）创建一个查询显示雇员总数，和在1995、1996、1997和1998受雇的雇员人数。创建适当的列标题。函数及基本Sql语句下面的查询练习题是基于Oracle 10g数据库中的scott用户下的表来进行练习的。首先给出scott用户中各个表的表结构第一：emp（员工信息表），如图1所示emp表中各个字段的介绍如下：（1） empno：员工编号（2） ename：员工姓名（3） job：职位（4） mgr：上级领导编号（5） hiredate：雇佣日期（6） sal：薪水（7） comm：佣金（8） deptno：部门编号第二：dept（部门信息表），如图2所示dept表中各个字段的介绍如下：（1） deptno：部门编号（2） dname：部门名称（3） loc：部门所在地练习：1. 选择部门30中的所有员工?2. 列出所有办事员(CLERK)的姓名，编号和部门编号?3. 找出佣金高于薪金的员工?4. 找出佣金高于薪金的60%的员工?5. 找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料？6. 找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK)， 既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料？7. 找出收取佣金的员工的不同工作？8. 找出不收取佣金或收取的佣金低于100的员工？9. 找出各月倒数第3天受雇的所有员工？10. 找出早于12年前受雇的员工？11. 以首字母大写的方式显示所有员工的姓名？12. 显示正好为5个字符的员工的姓名？13. 显示不带有R的员工的姓名？14. 显示所有员工姓名的前三个字符？15. 显示所有员工的姓名,用a替换所有A？16. 显示满10年服务年限的员工的姓名和受雇日期？17. 显示员工的详细资料,按姓名排序？18. 显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面？19. 显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序？20. 显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面？21. 显示在一个月为30天的情况所有员工的日薪金,忽略余数？22. 找出在(任何年份的)2月受聘的所有员工？23. 对于每个员工,显示其加入公司的天数？24. 显示姓名字段的任何位置包含A的所有员工的姓名？25. 以年月日的方式显示所有员工的服务年限？26. 列出各种工作职位的最低工资，且显示最低工资大于1500的记录27. 列出所有雇员的雇员名称、部门名称和工资28. 列出从事同一种工作但属于不同部门的雇员的员工信息29. 列出按年薪排序的所有雇员的年薪30. 列出至少有一个员工的所有部门31. 列出薪金比SMITH多的所有员工32. 列出薪金高于公司平均水平的所有员工33. 列出与“SCOTT”从事相同工作的所有员工34. 列出某些员工的姓名和薪金，条件是他们的薪金等于部门30中任何一个员工的薪金35. 列出所有员工的姓名及其直接上级的姓名36. 列出各个部门的MANAGER（经理）的最低薪金37. 列出所有部门名称和这些部门的员工信息，同时列出那些没有员工的部门38. 列出所有部门的详细信息和部门人数39. 显示所有薪金高于各自部门平均薪金的人。40. 查询在1981年2月1日1981年5月1日之间入职的雇员姓名、职位及入职时间，并以入职时间的先后进行排序41. 查询与BLAKE同一部门的其他员工。复杂Sqlcreate table student(sno varchar2(10) primary key,sname varchar2(20),sage number(2),ssex varchar2(5);create table teacher(tno varchar2(10) primary key,tname varchar2(20);create table course(cno varchar2(10),cname varchar2(20),tno varchar2(20),constraint pk_course primary key (cno,tno);create table sc(sno varchar2(10),cno varchar2(10),score number(4,2),constraint pk_sc primary key (sno,cno);/*初始化学生表的数据*/insert into student values (s001,张三,23,男);insert into student values (s002,李四,23,男);insert into student values (s003,吴鹏,25,男);insert into student values (s004,琴沁,20,女);insert into student values (s005,王丽,20,女);insert into student values (s006,李波,21,男);insert into student values (s007,刘玉,21,男);insert into student values (s008,萧蓉,21,女);insert into student values (s009,陈萧晓,23,女);insert into student values (s010,陈美,22,女);commit;/*初始化教师表*/insert into teacher values (t001, 刘阳);insert into teacher values (t002, 谌燕);insert into teacher values (t003, 胡明星);commit;/*初始化课程表*/insert into course values (c001,J2SE,t002);insert into course values (c002,Java Web,t002);insert into course values (c003,SSH,t001);insert into course values (c004,Oracle,t001);insert into course values (c005,SQL SERVER 2005,t003);insert into course values (c006,C#,t003);insert into course values (c007,JavaScript,t002);insert into course values (c008,DIV+CSS,t001);insert into course values (c009,PHP,t003);insert into course values (c010,EJB3.0,t002);commit;/*初始化成绩表*/insert into sc values (s001,c001,78.9);insert into sc values (s002,c001,80.9);insert into sc values (s003,c001,81.9);insert into sc values (s004,c001,60.9);insert into sc values (s001,c002,82.9);insert into sc values (s002,c002,72.9);insert into sc values (s003,c002,81.9);insert into sc values (s001,c003,59);insert into sc values (s002,c003,58);commit;练习：注意：以下练习中的数据是根据初始化到数据库中的数据来写的SQL 语句，请大家务必注意。1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；2、查询平均成绩大于60 分的同学的学号和平均成绩；3、查询所有同学的学号、姓名、选课数、总成绩；4、查询姓“刘”的老师的个数；5、查询没学过“谌燕”老师课的同学的学号、姓名；6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；9、查询所有课程成绩小于60 分的同学的学号、姓名；10、查询没有学全所有课的同学的学号、姓名；11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；12、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名；13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名；15、删除学习“谌燕”老师课的SC 表记录；16、向SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩；17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分18、按各科平均成绩从低到高和及格率的百分数从高到低顺序19、查询不同老师所教不同课程平均分从高到低显示20、统计列印各科成绩,各分数段人数:课程ID,课程名称,100-85,85-70,70-60, 6021、查询各科成绩前三名的记录:(不考虑成绩并列情况)22、查询每门课程被选修的学生数23、查询出只选修了一门课程的全部学生的学号和姓名24、查询男生、女生人数25、查询姓“张”的学生名单26、查询同名同性学生名单，并统计同名人数27、1981 年出生的学生名单(注：Student 表中Sage 列的类型是number)28、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩30、查询课程名称为“数据库”，且分数低于60 的学生姓名和分数31、查询所有学生的选课情况；32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；33、查询不及格的课程，并按课程号从大到小排列34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；35、求选了课程的学生人数36、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩37、查询