ORACLE多表题

合集下载

Oracle基础练习题及答案(多表查询2)

多表查询1.显示所有员工的姓名ename，部门号deptno和部门名称dname。

select ename,a.deptno,dname from emp a,dept b where a.deptno=b.deptno;2.查询20号部门员工的job和20号部门的locselect ename,job,loc from emp a,dept b where a.deptno=b.deptno and a.deptno=20;3.选择所有有奖金comm的员工的ename , dname , locselect ename,dname,loc from emp a,dept b where a.deptno=b.deptno and comm is not null;4.选择在DALLAS工作的员工的ename , job , deptno, dnameselect ename,job,a.deptno,dname from emp a,dept b where a.deptno=b.deptno and loc='DALLAS';5.选择所有员工的姓名ename，员工号deptno，以及他的管理者mgr的姓名ename和员工号deptno，结果类似于下面的格式employees Emp# manager Mgr#SMITH 7369 FORD 7902select a.ename "employees",a.empno "Emp#",b.ename "manager",b.empno "Mgr#" from emp a,emp b where a.mgr=b.empno;6. 查询各部门员工姓名和他们所在位置，结果类似于下面的格式Deptno Ename Loc20 SMITH DALLASselect a.deptno "Deptno",ename "Ename",loc "Loc" from emp a,dept b where a.deptno=b.deptno;。

Oracle练习题习题答案张表题

Oracle练习题+习题答案(张表+题)create 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,'男'); create table student(insert into student values ('s003','吴鹏',25,'男'); sno varchar2(10) primary key,insert into student values ('s004','琴沁',20,'女'); sname varchar2(20),insert into student values ('s005','王丽',20,'女'); sage number(2),insert into student values ('s006','李波',21,'男'); ssex varchar2(5)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', '刘阳');create table teacher(insert into teacher values ('t002', '谌燕');tno varchar2(10) primary key,insert into teacher values ('t003', '胡明星');tname varchar2(20)commit; );/***************初始化课程表****************************/ insert into course values ('c001','J2SE','t002');insert into course values ('c002','Java Web','t002');insert into course values ('c003','SSH','t001'); create table course(insert into course values ('c004','Oracle','t001'); cnovarchar2(10),insert into course values ('c005','SQL SERVER 2005','t003'); cname varchar2(20),insert into course values ('c006','C#','t003'); tno varchar2(20),insert into course values ('c007','JavaScript','t002');constraint pk_course primary key (cno,tno)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);create table sc(insert into sc values ('s003','c001',81.9);sno varchar2(10),insert into sc values ('s004','c001',60.9);cno varchar2(10),insert into sc values ('s001','c002',82.9);score number(4,2),insert into sc values ('s002','c002',72.9);constraint pk_sc primary key (sno,cno)insert into sc values ('s003','c002',81.9); ); insert into sc values ('s001','c003','59'); commit;练习：注意：以下练习中的数据是根据初始化到数据库中的数据来写的SQL 语句，请大家务必注意。

Oracle基础练习题及答案（多表查询1）（共5篇）

Oracle基础练习题及答案（多表查询1）（共5篇）第一篇：Oracle基础练习题及答案(多表查询1)利用scott用户自带的四张表完成如下作业：1.列出至少有一个员工的所有部门select b.deptno,b.dname from emp a,dept b where a.deptno=b.deptno group by b.deptno,b.dname having count(*)>=1;2.列出薪金比SMITH高的所有员工select * from emp where sal>(select sal from emp where ename='SMITH');3.列出所有员工的姓名及其直接上级领导的姓名select a.ename,b.ename “leader” from emp a,emp b wherea.mgr=b.empno;4.列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称select a.empno,a.ename,a.hiredate,c.dname from emp a,emp b,dept c where a.mgr=b.empno and a.deptno=c.deptno anda.hiredate5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门select b.dname,a.* from emp a,dept b wherea.deptno(+)=b.deptno;6.列出所有CLERK(办事员)的姓名，及其部门名称，部门人数select aa.ename,aa.job,bb.dname,(select count(a.deptno)from emp a,dept b where a.deptno=b.deptno and b.dname=bb.dname group by a.deptno)from emp aa,dept bb where aa.deptno(+)=bb.deptno and aa.job='CLERK';7.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数select a.job,min(sal),count(ename)from emp a,dept b wherea.deptno=b.deptno having min(sal)>1500 group by a.job;8.列出在部门SALES(销售部)工作的员工的姓名，假定不知道销售部的部门编号。

oracle试题

oracle试题一、选择题1. 在Oracle数据库中，用于创建表的语句是什么？A. CREATE DATABASEB. CREATE TABLEC. CREATE INDEXD. CREATE VIEW答案：B2. 下列哪个命令可以用于查询当前数据库中的所有表？A. DESCB. SHOW TABLESC. LIST TABLESD. SELECT * FROM ALL_TABLES答案：C3. 在Oracle中，如何删除一个表？A. REMOVE TABLE table_name;B. DELETE TABLE table_name;C. DROP TABLE table_name;D. ERASE TABLE table_name;答案：C4. 在Oracle SQL中，哪个关键字用于选择所有列？A. ALLB. EACHC. EVERYD. ALL COLUMNS答案：A5. 如何在Oracle SQL查询中对结果进行排序？A. ORDER BY column_name ASC/DESC;B. SORT BY column_name ASC/DESC;C. ARRANGE BY column_name ASC/DESC;D. CLASSIFY BY column_name ASC/DESC;答案：A二、填空题1. 在Oracle数据库中，用来创建视图的关键字是_______。

答案：CREATE VIEW2. 若要在Oracle中为表添加一个新的列，应使用_______语句。

答案：ALTER TABLE3. 在Oracle SQL中，用于合并两个或多个查询结果的关键字是_______。

答案：UNION4. 若要在Oracle数据库中重命名表，应使用_______语句。

答案：RENAME5. 在Oracle中，用来删除表中的行的语句是_______。

答案：DELETE三、简答题1. 请简述Oracle数据库中的事务是什么？答：事务是一组SQL语句的集合，它们作为一个单一的工作单元执行。

Oracle中的多表查询（笛卡尔积原理）

Oracle中的多表查询（笛卡尔积原理）本次预计讲解的知识点1、多表查询的操作、限制、笛卡尔积的问题；2、统计函数及分组统计的操作；3、⼦查询的操作，并且结合限定查询、数据排序、多表查询、统计查询⼀起完成各个复杂查询的操作；⼀、多表查询的基本概念在之前所使⽤的查询操作之中，都是从⼀张表之中查询出所需要的内容，那么如果现在⼀个查询语句需要显⽰多张表的数据，则就必须应⽤到多表查询的操作，⽽多表查询的语法如下：SELECT [DISTINCT] * | 字段[别名] [,字段 [别名] ,…]FROM 表名称[别名], [表名称 [别名] ,…][WHERE 条件(S)][ORDER BY 排序字段 [ASC|DESC] [,排序字段 [ASC|DESC] ,…]];但是如果要进⾏多表查询之前，⾸先必须先查询出⼏个数据 —— 雇员表和部门表中的数据量，这个操作可以通过COUNT()函数完成。

范例：查询emp表中的数据量 ——返回了14条记录SELECT COUNT(*) FROM emp;范例：查询dept表中的数据量 ——4条记录SELECT COUNT(*) FROM dept;额外补充⼀点：何为经验？在⽇后的开发之中，很多⼈都肯定要接触到许多新的数据库和数据表，那么在这种时候有两种做法：做法⼀：新⼈做法，上来直接输⼊以下的命令：SELECT * FROM 表名称;如果此时数据量较⼤的话，⼀上⽆法浏览数据，⼆有可能造成系统的死机；做法⼆：⽼⼈做法，先看⼀下有多少条记录：SELECT COUNT(*) FROM 表名称;如果此时数据量较⼩，则可以查询全部数据，如果数据量较⼤则不能直接使⽤SELECT查询。

现在确定好了emp和dept表中的记录之后，下⾯完成⼀个基本的多表查询：SELECT * FROM emp, dept;但是现在查询之后发现⼀共产⽣了56条记录 = 雇员表的14条记录 * 部门表的4条记录，之所以会造成这样的问题，主要都是由数据库的查询机制所决定的，例如，如下图所⽰。

oracle练习题(打印版)

oracle练习题（打印版）### Oracle数据库练习题#### 一、选择题1. Oracle数据库中，哪个命令用于创建表？- A. CREATE TABLE- B. CREATE DATABASE- C. DROP TABLE- D. ALTER TABLE2. 以下哪个不是Oracle数据库的数据类型？- A. NUMBER- B. CHAR- C. DATE- D. IMAGE3. 在Oracle数据库中，哪个命令用于删除表？- A. DELETE FROM- B. DROP TABLE- C. REMOVE TABLE- D. ERASE TABLE4. Oracle数据库中，如何查看当前用户？- A. SELECT USER FROM DUAL;- B. SELECT CURRENT_USER FROM DUAL;- C. SELECT USERNAME FROM ALL_USERS;- D. SELECT CURRENT_USER FROM ALL_USERS;5. 以下哪个命令用于在Oracle数据库中创建索引？- A. CREATE INDEX- B. CREATE KEY- C. CREATE CONSTRAINT- D. CREATE UNIQUE#### 二、填空题1. 在Oracle数据库中，使用____命令可以查看表结构。

2. Oracle数据库中，使用____命令可以查看当前数据库的所有表。

3. 要删除Oracle数据库中的行，可以使用____命令。

4. Oracle数据库中，____用于存储二进制数据。

5. Oracle数据库中，____命令用于查看数据库中所有的索引。

#### 三、简答题1. 描述Oracle数据库中事务的ACID属性。

2. 解释Oracle数据库中的锁定机制。

3. 说明Oracle数据库中视图的作用。

#### 四、操作题1. 创建一个名为`Employees`的表，包含以下字段：- `EmployeeID` NUMBER(10) PRIMARY KEY,- `FirstName` VARCHAR2(50),- `LastName` VARCHAR2(50),- `HireDate` DATE,- `Salary` NUMBER(10, 2),- `DepartmentID` NUMBER(10).2. 向`Employees`表中插入以下数据：- `EmployeeID`: 1001, `FirstName`: 'John', `LastName`:'Doe', `HireDate`: '2023-01-01', `Salary`: 70000,`DepartmentID`: 101.- `EmployeeID`: 1002, `FirstName`: 'Jane', `LastName`:'Smith', `HireDate`: '2023-02-15', `Salary`: 50000,`DepartmentID`: 102.3. 编写一个查询，显示所有员工的姓名和工资，按工资从高到低排序。

ORACLE数据库多表关联查询效率问题解决方案

ORACLE数据库多表关联查询效率问题解决⽅案最近在做项⽬中遇到多表关联查询排序的效率问题（5张以上40W+数据的表），查询⼀次⼤概要20多秒，经过⼀番苦思冥想，处理⽅案如下: 1、软件设计初期，需要⼀对⼀关联的表应该设计在⼀张⼤表⾥，这样虽然字段多些，但是对于查询的速度提升是⾮常明显的，特别是在排序的情况下。

2、我们在关联的时候可能需要排序的表只是其中的⼀张或者两张，我们可以先针对这两张需要排序的表先查询排序，然后再⽤这两个表查询的结果关联其他表。

代码如下：1SELECT*FROM (2SELECT*FROM M_RK_JBXX A3LEFT JOIN M_RK_ZFGX B ON A.RKID = B.RKID4LEFT JOIN(SELECT*5FROM M_DZ_MP D6LEFT JOIN M_DZ_LD E ON D.LDID = E.LDID7ORDER BY E.XC, E.PXH, D.PXH)DD ON B.MPID = DD.MPID8LEFT JOIN M_DZ_WGGL F ON DD.WGID = F.WGID9LEFT JOIN M_RK_HJXXZH C ON C.MPID = DD.MPID10 )后来我在需要分页的环境下，⽤rownum来进⾏分页，发现按照上⾯的排序的顺序就被打乱了。

在⽹上找了很多资料也看了很多博客，也没找到解决⽅案。

所以上⾯的代码的适⽤性就很局限了（不分页的情况下）。

但是我需要⾼效率的分页怎么办呢？rownum满⾜不了我呀。

后来我⼜找到了⼀个函数ROW_NUMBER() OVER(ORDER BY column) ，通过该函数可以极⼤的提⾼查询的效率。

使⽤的⽅式为1SELECT*2FROM (SELECT ROW_NUMBER() OVER(ORDER BY A.PXH, B.PXH) NUM,3 A.*,B.*4FROM TAB1 A5LEFT JOIN TAB2 B6ON A.T2= B.ID7 )8WHERE NUM BETWEEN1AND20通过这种⽅式我的查询速度提⾼了将近10倍，当然我的实际环境⽐这段代码要⿇烦很多。

史上最全Oracle数据库基本操作练习题（含答案）

Oracl‎e基本操作‎练习题使用表：员工表(emp)：(empno‎N UMBE‎R(4)notnu‎l l,--员工编号，表示唯一ename‎V ARCH‎A R2(10),--员工姓名job VARCH‎A R2(9),--员工工作职‎位mgrNU‎M BER(4),--员工上级领‎导编号hired‎a teDA‎T E,--员工入职日‎期salNU‎M BER(7,2),--员工薪水commN‎U MBER‎(7,2),--员工奖金deptn‎o NUMB‎E R(2)—员工部门编‎号)部门表（dept）：(deptn‎o NUMB‎E R(2)notnu‎l l,--部门编号dname‎V ARCH‎A R2(14),--部门名称locVA‎R CHAR‎2(13)—部门地址)说明：增删改较简‎单，这些练习都‎是针对数据‎查询，查询主要用‎到函数、运算符、模糊查询、排序、分组、多变关联、子查询、分页查询等‎。

建表脚本.txt建表脚本(根据需要使‎用)：练习题：1.找出奖金高‎于薪水60‎%的员工信息‎。

SELEC‎T * FROM emp WHERE‎comm>sal*0.6;2.找出部门1‎0中所有经‎理（MANAG‎E R）和部门20‎中所有办事‎员（CLERK ‎）的详细资料‎。

SELEC‎T* FROM emp WHERE‎(JOB='MANAG‎E R' AND DEPTN‎O=10) OR (JOB='CLERK‎' AND DEPTN‎O=20);3.统计各部门‎的薪水总和‎。

SELEC‎T deptn‎o,SUM(sal) FROM emp GROUP‎BY deptn‎o;4.找出部门1‎0中所有理‎（M ANAG‎E R），部门20中‎所有办事员‎（C LERK 以及既不是‎经理又不是‎办事员但其‎薪水大于或‎等2000‎的所有员工‎的‎）详细资料‎。

oracle练习题之多表查询

Select couno,couname,deptment.departno,departname from course right join deptment on course.departno= deptment.departno;
(4)查询课程名相同但任课老师不同的课程信息（课程编号、课程名称、任课教师）
stu查询课程编号、课程名称和选择该课程的学生学号和成绩
select course.couno,couname,stuno,score from course left join score on course.couno = score.couno;
(3)右外连接查询课程编号、课程名称、开设该课程的系编号以及相应的系部名称
course
class
score
student
Deptment
(1)查询学号为463501101的同学的姓名、班级编号、班级名称
select stuname,student.classno,classname from student inner join class on student.classno=class.classno where
(6)查询成绩在90分以上的学生学号、姓名、班级编号
select stuno,stuname,classno from student where stuno in(select stuno from score where score>90);
select c1.couno,c1.couname,c1.teacher from course c1,course c2 where c1.couname=c2.course and c1.teacher<>c2.teacher;

oracle多选考试题

oracle多选考试题
Oracle多选考试题可以包括多个方面，例如SQL查询、表连接、子查询、动态SQL等。

以下是一些示例题目：
1. 关于子查询，说法正确的有：
A. 子查询只应检索一行。

B. 子查询可以检索零个或多个行。

C. 子查询只能在SELECT语句中使用，不能在GROUP BY、ORDER BY等子句中使用。

D. 子查询不能被多于两个级别嵌套。

E. 子查询不能在使用组函数的SQL查询语句中使用。

F. 当子查询与外部SQL语句中的不等式比较运算符一起使用时，子查询的SELECT子句中的列只能包含一列。

2. 在Oracle中，用于限制分组函数的返回值的字句是：
A. WHERE
B. HAVING
C. ORDER BY
D. 无法限定分组函数的返回
3. 在Oracle PL/SQL中，执行动态SQL的语句是：
A. NXECUTE
B. START
C. RUN
D. EXECUTE IMMEDIATE
4. 下列哪些表空间是运行一个数据库必须的一个表空间？
A. 系统表空间
B. 临时表空间
C. 用户表空间
D. 更名现有同名对象。

这些题目涵盖了Oracle数据库的不同方面，包括SQL查询、表连接、子查询和动态SQL等。

在考试中，可以根据需要选择适当的题目数量和难度，以评估考生的Oracle知识水平。

oracle练习题及答案

oracle练习题及答案Oracle练习题及答案Oracle数据库是全球最受欢迎的关系型数据库管理系统之一，广泛应用于企业级应用程序开发和数据管理。

为了提高对Oracle数据库的理解和应用能力，下面将提供一些常见的Oracle练习题及其答案。

1. 查询表中的所有数据答案：使用SELECT语句可以查询表中的所有数据，例如：SELECT * FROM 表名;2. 查询表中的特定列数据答案：使用SELECT语句并指定需要查询的列名，例如：SELECT 列名1, 列名2 FROM 表名;3. 查询表中满足特定条件的数据答案：使用SELECT语句并使用WHERE子句来指定条件，例如：SELECT * FROM 表名 WHERE 列名 = 值;4. 对查询结果进行排序答案：使用SELECT语句并使用ORDER BY子句来指定排序的列和排序方式，例如：SELECT * FROM 表名 ORDER BY 列名 ASC/DESC;5. 对查询结果进行分组答案：使用SELECT语句并使用GROUP BY子句来指定分组的列，例如：SELECT 列名1, 列名2 FROM 表名 GROUP BY 列名1;6. 查询表中的唯一数据答案：使用SELECT DISTINCT语句可以查询表中的唯一数据，例如：SELECT DISTINCT 列名 FROM 表名;7. 查询表中的前N条数据答案：使用SELECT语句并使用ROWNUM关键字来限制查询结果的行数，例如：SELECT * FROM 表名 WHERE ROWNUM <= N;8. 使用聚合函数进行数据统计答案：使用聚合函数如SUM、AVG、COUNT等可以对数据进行统计，例如：SELECT COUNT(*) FROM 表名;9. 进行多表查询答案：使用SELECT语句并使用JOIN关键字来连接多个表，例如：SELECT * FROM 表名1 JOIN 表名2 ON 表名1.列名 = 表名2.列名;10. 更新表中的数据答案：使用UPDATE语句可以更新表中的数据，例如：UPDATE 表名 SET 列名 = 值 WHERE 条件;11. 删除表中的数据答案：使用DELETE语句可以删除表中的数据，例如：DELETE FROM 表名 WHERE 条件;12. 创建新表答案：使用CREATE TABLE语句可以创建新表，例如：CREATE TABLE 表名 (列名1 数据类型, 列名2 数据类型, ...);13. 修改表结构答案：使用ALTER TABLE语句可以修改表的结构，例如：ALTER TABLE 表名 ADD 列名数据类型;14. 删除表答案：使用DROP TABLE语句可以删除表，例如：DROP TABLE 表名;以上是一些常见的Oracle练习题及其答案，希望能够帮助读者更好地理解和应用Oracle数据库。

oracle练习题及答案

oracle练习题及答案Oracle练习题及答案Oracle是一种广泛使用的关系型数据库管理系统，它具有强大的数据处理和管理能力。

对于想要提升自己的数据库技能的人来说，练习题是一个非常有效的学习方法。

通过解答一系列的练习题，可以帮助我们更好地理解Oracle的使用和原理。

下面是一些常见的Oracle练习题及其答案，供大家参考。

1. 查询一个表中所有的数据答案：可以使用SELECT语句来查询一个表中的所有数据。

例如，假设我们有一个名为"employees"的表，可以使用以下语句来查询所有的数据：SELECT * FROM employees;2. 查询一个表中特定列的数据答案：如果我们只想查询一个表中特定列的数据，可以使用SELECT语句，并在其中指定要查询的列名。

例如，如果我们只想查询"employees"表中的"first_name"和"last_name"列，可以使用以下语句：SELECT first_name, last_name FROM employees;3. 查询满足特定条件的数据答案：如果我们只想查询满足特定条件的数据，可以在SELECT语句中使用WHERE子句来指定条件。

例如，如果我们只想查询"employees"表中工资大于5000的员工，可以使用以下语句：SELECT * FROM employees WHERE salary > 5000;4. 对查询结果进行排序答案：如果我们想对查询结果进行排序，可以在SELECT语句中使用ORDER BY子句，并指定要排序的列名。

例如，如果我们想按照"employees"表中的"last_name"列进行升序排序，可以使用以下语句：SELECT * FROM employees ORDER BY last_name ASC;5. 对查询结果进行分组答案：如果我们想对查询结果进行分组，可以在SELECT语句中使用GROUP BY 子句，并指定要分组的列名。

oracle两表查询练习附答案

Sutdent表的定义字段名字段描述数据类型主键外键非空唯一自增Id学号INT(10)是否是是是Name姓名VARCHAR(20)否否是否否Sex性别VARCHAR(4)否否否否否Birth出生年份YEAR否否否否否Department院系VARCHAR(20)否否是否否Address家庭住址VARCHAR(50)否否否否否Score表的定义字段名字段描述数据类型主键外键非空唯一自增Id编号INT(10)是否是是是Stu_id学号INT(10)否否是否否C_name课程名VARCHAR(20)否否否否否Grade分数INT(10)否否否否否1.创建student和score表create table student(id number(10)not null primary key,name varchar2(20)not null,sex varchar2(4),birth number,department varchar2(20)not null,address varchar2(50));create table score(id number(10)not null primary key,stu_id number(10)not null,c_name varchar2(20),grade number(10));2.为student表和score表增加记录向student表插入记录的INSERT语句如下：Insert into student values(100101,'张三','男',23,'计算机系','北京市朝阳区');Insert into student values(100102,'李四','男',21,'英语系','北京市海淀区');Insert into student values(100103,'王五','女',19,'建工系','北京市昌平区');Insert into student values(100104,'孙六','女',21,'化学系','北京市苏州桥');Insert into student values(100105,'齐七','男',23,'英语系','北京市海淀区');向score表插入记录的INSERT语句如下：Insert into score values(001,100101,'计算机基础',89);Insert into score values(002,100101,'英语',93);Insert into score values(003,100101,'数学',87);Insert into score values(004,100102,'计算机基础',83);Insert into score values(005,100102,'英语',81);Insert into score values(006,100102,'数学',77);Insert into score values(007,100103,'计算机基础',91);Insert into score values(008,100103,'英语',85);Insert into score values(009,100103,'数学',88);Insert into score values(010,100104,'计算机基础',84);Insert into score values(011,100104,'英语',71);Insert into score values(012,100104,'数学',83);3.查询student表的所有记录select*from student;4.查询student表的第2条到4条记录1.select *,rownum as con from student where rownum<=4 and rownum>=2;(select*from student where rownum<=4)minus(select*from student where rownum<=1);1、Minus:两个查询,MINUS是从第一个查询结果减去第二个查询结果,如果有相交部分就减去相交部分;否则和第一个查询结果没有区别. INTERSECT是两个查询结果的交集,UNION ALL是两个查询的并集;2、Rownum：对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，而且rownum 不能以任何表的名称作为前缀。

oracle多表查询练习题-答案

in
any
all
select job,count(empno)
from emp
where job in (select job from emp group by job having min(sal)>1500) group by job ;
8、列出在部门“sales”（销售部）工作的员工的姓名，假定不知道销售部的部门编号
5、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
左右连接
selecept d
where e.deptno(+)=d.deptno;
6、列出所有“CLERK”的姓名及其部门名称，部门的人数
select ename,deptno from emp where job='CLERK';
from emp group by deptno)ed
where d.deptno=ed.deptno(+);
16、列出各种工作的最低工资以及从事此工作的雇员姓名
分析：按工作分组，求出最低工资 min（）
select job,min(sal) m from emp group by job;
from emp
group by deptno;
平均服务期限
hiredate
sysdate
select deptno,count(empno),round(avg(sal)) 平均工资,round(avg(months_between(sysdate,hiredate)/12)) 平均服务期限
where e.deptno=d.deptno
group by d.dname;

oracle多表关联的优化问题

oracle多表关联的优化问题ORACLE多表查询优化ORACLE多表查询优化这里提供的是执行性能的优化，而不是后台数据库优化器资料参考数据库开发性能方面的各种问题，收集了一些优化方案统计如下（当然，象索引等优化方案太过简单就不列入了，嘿嘿）：执行路径ORACLE勺这个功能大大地提高了SQL的执行性能并节省了内存的使用：我们发现，单表数据的统计比多表统计的速度完全是两个概念?单表统计可能只要0.02秒，但是2张表联合统计就可能要几十表了.这是因为ORACL只对简单的表提供高速缓冲（cache buffering）, 这个功能并不适用于多表连接查询..数据库管理员必须在init.ora 中为这个区域设置合适的参数,当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了.当你向ORACL提交一个SQL语句,ORACL会首先在这块内存中查找相同的语句.这里需要注明的是‘ORA CLE寸两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同（包括空格,换行等）.共享的语句必须满足三个条件：A. 字符级的比较：当前被执行的语句和共享池中的语句必须完全相同.例如：SELECT * FROM EMP;和下列每一个都不同SELECT * from EMP;Select * From Emp;SELECT * FROM EMP;B. 两个语句所指的对象必须完全相同用户对象名问Jack sal_limit synonym Work_city synonym Pla nt_detail synonymJillsal_limit如何访privatepublicpublic privatesynonym Work_city synonympublicPla nt_detail tableowner考虑一下下列SQL语句能否在这两个用户之间共享.SQL能否共享原因select max（sal_cap）from sal」imit; 不能每个用户都有一个privatesynonym - sal_limit , 它们是不同的对象select count（*） from work_city where sdesc like 'NEW%'; 能两个用户访问相同的对象public synonym - work_cityselect a.sdesc,b .lo cati on from work_city a , pla nt_detail b wherea. city_id =b.city_id 不能用户jack 通过private synonym 访问plant_detail 而jill 是表的所有者，对象不同.C. 两个SQL语句中必须使用相同的名字的绑定变量（bindvariables）例如：第一组的两个SQL语句是相同的（可以共享）,而第二组中的两个语句是不同的（即使在运行时，赋于不同的绑定变量相同的值）a.select pin , n ame from people where pin = :blk1.p in;select pin , n ame from people where pin = :blk1.p in;b.select pin , n ame from people where pin = :blk1.ot_i nd;select pin , n ame from people where pin = :blk1.ov_i nd;重点关注1:选择最有效率的表名顺序（只在基于规则的优化器中有效）重点关注ORACL的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表driving table）将被最先处理.在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表.当ORACL 处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表（FROM?句中最后的那个表）并对记录进行派序，然后扫描第二个表（FROM子句中最后第二个表）, 最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并例如：表TAB1 16,384 条记录表TAB2 1 条记录选择TAB2作为基础表（最好的方法）select count（*） from tab1,tab2 执行时间0.96 秒选择TAB2作为基础表（不佳的方法）select count（*） from tab2,tab1 执行时间26.09 秒如果有3个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表.例如：EMP表描述了LOCATIONS和CATEGORY的交集.SELECT *FROM LOCATION L ,CATEGORY C,EMP EWHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO = C.CAT_NOAND E丄OCN = L.L OCN将比下列SQL更有效率SELECT *FROM EMP E ,LOCATION L ,CATEGORYCWHERE E.CAT_NO = C.CAT_NOAND E丄OCN = L.L OCNAND E.EMP_NO BETWEEN 1000 AND 2000重点关注2:WHER子句中的连接顺序.重点关注ORACL采用自下而上的顺序解析WHER子句，根据这个原理，表之间的连接必须写在其他WHER条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾.例如：(低效,执行时间156.3秒)SELECT …FROM EMP EWHERE SAL >; 50000AND JOB = ‘MANAGERAND 25 < (SELECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO);(高效,执行时间10.6秒)SELECT …FROM EMP EWHERE 25 < (SELECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO)AND SAL >; 50000AND JOB = ‘MANAGER重点关注3:SELECT子句中避免使用‘ * : 重点关注当你想在SELEC子句中列出所有的COLUM时，使用动态SQ咧引用‘*'是一个方便的方法.不幸的是，这是一个非常低效的方法.实际上，ORACL在解析的过程中，会将’*'依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间.7. 减少访问数据库的次数当执行每条SQL语句时,ORACLEE内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等.由此可见，减少访问数据库的次数,就能实际上减少ORACL的工作量.例如，以下有三种方法可以检索出雇员号等于0342或0291的职员.方法1 (最低效)SELECT EMP_NAME , SALARY , GRADE FROM EMPWHERE EMP_NO = 342;SELECT EMP_NAME , SALARY , GRADEFROM EMPWHERE EMP_NO = 291;方法2(次低效)DECLARECURSOR C1 (E_NO NUMBER) ISSELECT EMP_NAME,SALARY,GRADEFROM EMPWHERE EMP_NO = E_NO;BEGINOPEN C1(342);FETCH C1 INTO …;OPEN C1(291);FETCH C1 INTO …;CLOSE C1;END;方法3(高效)SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME ,B.SALARY , B.GRADE FROM EMP A,EMP BWHERE A.EMP_NO = 342AND B.EMP_NO = 291;在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZ参数,可以增加每次数据库访问的检索数据量，建议值为200.重点关注4:使用DECOD E数来减少处理时间.重点关注使用DECOD E数可以避免重复扫描相同记录或重复连接相同的表. 例如：SELECT COUNT(*) SUM(SAL)FROM EMPWHERE DEPT_NO = 0020AND ENAME LIKE ‘ SMITH%；SELECT COUNT(*) SUM(SAL)FROM EMPWHERE DEPT_NO = 0030AND ENAME LIKE ‘ SMITH%；你可以用DECOD E数高效地得到相同结果SELECT COUNT(DECODE(DEPT_NO,O0X0,,NULL)) D0020_COUNTCOUNT(DECODE(DEPT_NO,0030, ,NULL))D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SALFROM EMP WHERE ENAME LlKSMITH%；类似的,DECOD函数也可以运用于GROUP B湘ORDER B子句中.重点关注5:删除重复记录.重点关注最高效的删除重复记录方法（因为使用了ROWID）DELETE FROM EMP EWHERE E.ROWID >; （SELECT MIN（X.ROWID）FROM EMP XWHERE X.EMP_NO = E.EMP_NO）; 重点关注6:用TRUNCAT替代DELETE S点关注当删除表中的记录时,在通常情况下,回滚段（rollback segments ）用来存放可以被恢复的信息.如果你没有COMMI事务,ORACLED将数据恢复到删除之前的状态（准确地说是恢复到执行删除命令之前的状况）而当运用TRUNCAT时，回滚段不再存放任何可被恢复的信息.当命令运行后，数据不能被恢复?因此很少的资源被调用，执行时间也会很短?（译者按：TRUNCAT趴在删除全表适用‘TRUNCATED DDL不是DML）重点关注7:尽量多使用COMM IT重点关注只要有可能，在程序中尽量多使用COMMIT,这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少：COMMITS释放的资源：a. 回滚段上用于恢复数据的信息?b. 被程序语句获得的锁c. redo log buffer 中的空间d. ORACL为管理上述3种资源中的内部花费（译者按：在使用COMMIT寸必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼）重点关注8:减少对表的查询.重点关注在含有子查询的SQL语句中,要特别注意减少对表的查询.例如：低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NAME = ( SELECT TAB_NAMEFROM TAB_COLUMNS。

oracle 复杂sql练习题

oracle 复杂sql练习题Oracle 复杂 SQL 练习题在数据库管理系统中，SQL（Structured Query Language）是一种用于管理和操作关系数据库的标准语言。

Oracle是一种常用的关系数据库管理系统，它支持SQL语言的广泛应用。

本文将介绍一些复杂的Oracle SQL练习题，旨在提高读者对SQL语言的理解和应用能力。

1. 学生成绩统计假设有两张表：学生表（Student）和成绩表（Score）。

学生表包含学生的姓名（Name）和学号（ID），成绩表包含学生的学号（ID）和对应的科目（Subject）及成绩（Grade）。

现在需要统计每个学生的总分和平均分，并按照平均分从高到低排序。

解决方案：```sqlSELECT , SUM(sc.Grade) AS TotalGrade, AVG(sc.Grade) AS AverageGrade FROM Student sJOIN Score sc ON s.ID = sc.IDGROUP BY ORDER BY AverageGrade DESC;```2. 部门工资排名假设有两张表：员工表（Employee）和部门表（Department）。

员工表包含员工的姓名（Name）、工号（ID）和所属部门（DeptID），部门表包含部门的名称（DeptName）和部门编号（DeptID）。

现在需要统计每个部门的平均工资，并按照平均工资从高到低排序。

解决方案：```sqlSELECT d.DeptName, AVG(e.Salary) AS AverageSalaryFROM Department dJOIN Employee e ON d.DeptID = e.DeptIDGROUP BY d.DeptNameORDER BY AverageSalary DESC;```3. 订单统计假设有三张表：订单表（Order）、订单详情表（OrderDetail）和商品表（Product）。

Oracle数据库学习（4）多表查询

Oracle数据库学习（4）多表查询⼀：笛卡尔积问题1、笛卡尔积的现象：多个表⾥的所有记录都会相互匹配⼀次，如：表1有m条记录，表2有n条记录，则笛卡尔积现象会产⽣m*n条记录，如：select * from 表1,表22.为了避免笛卡尔积，可以在WHERE加⼊有效的连接条件，如：select * from 表1,表2 where 表1.id=表2.id⼆：Oracle的连接1、内连接：含义：合并具有同⼀列的两个以上的表的⾏, 结果集中不包含⼀个表与另⼀个表不匹配的⾏内连接⽰意图：（1）等值连接⽅式⼀：使⽤=关联表字段语法：SELECT table1.column, table2.columnFROM table1, table2WHERE table1.column1 = table2.column2;在 WHERE ⼦句中写⼊连接条件在表中有相同列时，在列名之前加上表名前缀例1：两表连接：查询出公司员⼯的 id,last_name,depatment_id, department_nameSELECT e.EMPLOYEE_ID,ST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME FROM EMPLOYEES e ,DEPARTMENTS d WHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID例2：三表连接：查询出公司员⼯的 last_name, department_name, citySELECT e.EMPLOYEE_ID,ST_NAME,d.DEPARTMENT_ID,d.DEPARTMENT_NAME,l.CITYFROM EMPLOYEES e ,DEPARTMENTS d,LOCATIONS lWHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID AND d.LOCATION_ID=l.LOCATION_ID（2）等值连接⽅式⼆：使⽤JOIN ON关联字段例1：两表连接：查询出公司员⼯的 id,last_name,depatment_id, department_nameSELECT e.EMPLOYEE_ID,ST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME FROM EMPLOYEES e JOIN DEPARTMENTS d ON e.DEPARTMENT_ID=d.DEPARTMENT_ID例2：三表连接：查询出公司员⼯的 last_name, department_name, citySELECT e.EMPLOYEE_ID,ST_NAME,d.DEPARTMENT_ID,d.DEPARTMENT_NAME,l.CITYFROM EMPLOYEES e JOIN DEPARTMENTS d ON e.DEPARTMENT_ID=d.DEPARTMENT_IDJOIN LOCATIONS l ON d.LOCATION_ID=l.LOCATION_ID（3）⾮等值连接查询员⼯⼯资对应的档次SELECT e.EMPLOYEE_ID,ST_NAME,e.SALARY,j.GRADE_LEVEL from EMPLOYEES e,JOB_GRADES j WHERE e.SALARY BETWEEN j.LOWEST_SAL and j.HIGHEST_SAL2、外连接左外连接：left join含义：匹配左表全记录，右边满⾜条件的记录⽰意图：例⼦：通过左外连接查询SELECT e.EMPLOYEE_ID,ST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME FROM EMPLOYEES e LEFT JOIN DEPARTMENTS d ON e.DEPARTMENT_ID=d.DEPARTMENT_ID;右外连接：right join含义：匹配右表全记录，左边满⾜条件的记录⽰意图：例⼦：通过右外连接查询SELECT e.EMPLOYEE_ID,ST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME FROM EMPLOYEES e RIGHT JOIN DEPARTMENTS d ON e.DEPARTMENT_ID=d.DEPARTMENT_ID;满外连接：full join含义：匹配左、右表全记录⽰意图：例⼦：通过满外连接查询SELECT e.EMPLOYEE_ID,ST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME FROM EMPLOYEES eFULL JOIN DEPARTMENTS d ON e.DEPARTMENT_ID=d.DEPARTMENT_ID;⾃连接含义：表⾃⾝连接⾃⾝的⼀种⽅式例⼦：查询员⼯姓"chen"的Manager信息SELECT ST_NAME,ST_NAME,manager.SALARY,manager.EMAIL FROM EMPLOYEES emp JOIN EMPLOYEES manager ON emp.MANAGER_ID=manager.MANAGER_ID WHERE LOWER(ST_NAME)='chen';多表查询的案例：1、显⽰所有员⼯的姓名，部门号和部门名称⽅式⼀：使⽤"="关联select last_name,e.department_id,department_name from employees e,departments d where e.department_id = d.department_id(+)⽅式⼆：使⽤Join on关联select last_name,e.department_id,department_name from employees e left outer join departments d on e.department_id = d.department_id2、查询90号部门员⼯的job_id和90号部门的location_idselect distinct job_id,location_id from employees e left join departments don e.department_id = d.department_idwhere d.department_id = 903、选择所有有奖⾦的员⼯的last_name , department_name , location_id , cityselect last_name,department_name,d.location_id,cityfrom employees e join departments don e.department_id = d.department_idjoin locations lon d.location_id = l.location_idwhere mission_pct is not null4、选择city在Toronto⼯作的员⼯的last_name , job_id , department_id , department_name select last_name , job_id , e.department_id , department_namefrom employees e ,departments d,locations lwhere e.department_id = d.department_id and l.city = 'Toronto' and d.location_id = l.location_id。

Oracle数据库（五）——多表查询

Oracle数据库（五）——多表查询（⼀）笛卡尔问题1、笛卡尔积会在下⾯条件下产⽣：省略连接条件连接条件⽆效所有表中的所有⾏互相连接2.为了避免笛卡尔积，可以在WHERE加⼊有效的连接条件（⼆）Oracle连接1、内连接合并具有同⼀列的两个以上的表的⾏, 结果集中不包含⼀个表与另⼀个表不匹配的⾏（1）等值连接使⽤连接在多个表中查询数据SELECT table1.column, table2.columnFROM table1, table2WHERE table1.column1 = table2.column2;在 WHERE ⼦句中写⼊连接条件在表中有相同列时，在列名之前加上表名前缀两表连接：查询出公司员⼯的 last_name, department_name三表连接：查询出公司员⼯的 last_name, department_name, city（2）⾮等值连接查询员⼯⼯资对应的档次2、外连接两个表在连接过程中除了返回满⾜连接条件的⾏以外还返回左（或右）表中不满⾜条件的⾏，这种连接称为左（或右）外连接。

没有匹配的⾏时, 结果表中相应的列为空(NULL). 外连接的 WHERE ⼦句条件类似于内部连接, 但连接条件中没有匹配⾏的表的列后⾯要加外连接运算符, 即⽤圆括号括起来的加号(+).外连接语法使⽤外连接可以查询不满⾜连接条件的数据外连接的符号是（+）左外连接右外连接（三）SQL：1999语法连接使⽤连接从多个表中查询数据：SELECT table1.column, table2.columnFROM table1[CROSS JOIN table2] |[NATURAL JOIN table2] |[JOIN table2 USING (column_name)] |[JOIN table2 ON(table1.column_name = table2.column_name)] |[LEFT|RIGHT|FULL OUTER JOIN table2ON (table1.column_name = table2.column_name)];1、叉集（了解）使⽤CROSS JOIN⾃居连接的表产⽣叉集叉集和笛卡尔集是相同的2、NATURAL JOIN：⾃然连接会以两个表中具有相同名字的列为条件创建等值连接。

Oracle Database数据库高级功能练习题参考答案

Oracle Database数据库高级功能练习题参考答案Oracle Database是一个功能强大的关系型数据库管理系统，拥有许多高级功能，可以满足各种复杂的数据管理和分析需求。

下面是一些Oracle Database高级功能练习题的参考答案，供大家参考和学习。

练习题一：子查询1.查询所有在员工表（Employees）中的非经理员工的信息。

```sqlSELECT *FROM EmployeesWHERE employee_id NOT IN (SELECT manager_id FROM Employees);```练习题二：分区表1.创建一个以年为分区键的分区表（OrderTable），包含订单号（order_id）、订单日期（order_date）和订单金额（order_amount）。

```sqlCREATE TABLE OrderTable(order_id NUMBER,order_date DATE,order_amount NUMBER)PARTITION BY RANGE (TO_CHAR(order_date,'YYYY'))(PARTITION p2018 VALUES LESS THAN ('2019'),PARTITION p2019 VALUES LESS THAN ('2020'),PARTITION p2020 VALUES LESS THAN ('2021'),...);```练习题三：索引1.为员工表（Employees）的姓氏（last_name）列创建一个B树索引。

```sqlCREATE INDEX idx_last_nameON Employees (last_name);```练习题四：触发器1.创建一个触发器，当向订单表（Orders）插入一条新的订单时，在订单历史表（OrderHistory）中插入一条记录。