开发手册 欢迎您！
软件开发者资料库

从多个表中获取数据问题

从多个表中获取数据问题 - Oracle数据库11g准备工作指南管理员认证助理，OPN认证专家考试。本指南可帮助您逐步在线准备证书考试，并且完全免费。此认证旨在使您获得赢得基于Oracle SQL的项目的简短列表。 Oracle技术认证是一种有价值的，行业认可的凭证，表明已经证明的知识水平和技能。

1.以下哪项与关系数据库无关？

  1. 选择

  2. 投影

  3. 加入

  4. 以上都不是

答案:D.选项A，B和C是Oracle关系数据库的主要功能.

2.以下哪种方法用于编写包含多个列的查询表？

  1. SELECT

  2. GROUP BY

  3. ORDER BY

  4. JOINS

答案:D.连接用于连接Oracle中多个表的多个表和项目列数据.

3.以下哪种是最常用的加入多个方法之一桌子？

  1. 哈希加入

  2. Equijoin

  3. 自我加入

  4. 交叉加入

答案:B. Equijoin是最常见和最简单的技术之一加入多个表格. Equijoins也称为简单连接或内连接.Equijoin涉及主键和外键.

4.如果列值，将使用以下哪些内容与其他表连接行落在由不平等运营商定义的范围内？

  1. Equijoin

  2. 简单加入

  3. 非等值连接

  4. 以上都不是

答案:C. Equijoins使用相等运算符来连接行，非等值连接使用不等式运算符.

5.以下哪些语句关于Oracle连接是否属实？

  1. 结果集中包含NULL值

  2. 仅提取具有匹配条件的行

  3. 提取任何一个表中存在的所有行

  4. 以上都不是

答案:B.当joi时，不包括NULL值和公共连接列中的不同条目使用ns.

6.以下哪项可以用来将表的行与同一个表的其他行连接起来？

  1. Equijoin

  2. Non-equijoin

  3. 外联接

  4. 自我加入

答案:D.该关联基于在具有逻辑和通常层次关系的列上.

7.在Oracle DB中两个表的笛卡尔连接有什么用？

  1. 必须避免，因为它成本高且未经优化

  2. 它是在一个表中的每一行都与第二个表中的所有行相连接

  3. A和B

  4. 以上都不是

答案:B.笛卡尔联接通常是缺少或不充分的连接条件的结果.它只是两个表的交叉乘积.

8.以下哪项是Oracle中基本的连接类型之一DB？

  1. 外部联接

  2. 自我加入

  3. Equi-join

  4. 以上所有

答案:C. Equi-join和non-equijoin是Oracle DB中两种基本类型的连接.

9.使用的主要条件是什么连接Oracle DB中的源表和目标表以获取非笛卡尔积的结果？

  1. 没有条件

  2. 两个表中至少有一列应该是通用的.

  3. 两者中列的名称使用连接的连接表应该相同

  4. 以上都不是

答案:B .表必须通过一个与两个实体相关的公共列连接.在一个公共列上连接的表生成非笛卡尔乘积.

10.以下哪项可用于从单个SQL查询中的多个表中获取行？

  1. SELECT

  2. WHERE

  3. FROM

  4. Equi-joins

答案:D. Equijoins也称为简单连接或内连接. Equijoin涉及主键和外键.

11.关于Oracle连接的源表和目标表有什么用？

  1. 他们必须至少有一个同名的列

  2. 所有列都应该是用于连接两个表的相同名称和相同数据类型

  3. 源表和目标表不能交换且位置特定

  4. 以上都不是

答案:D.源表和目标表可以交换，不会固定在它们的位置.取决于查询中使用的连接类型，结果可能不同或保持不变.

12. Oracle DB中的自然连接是什么？

  1. 源表和目标表的列名应该相同

  2. 如果源列表和目标表的列名不相同，Oracle隐含地需要

  3. NATURAL JOINS，USING和ON是与Natural Joins相关联的关键字

  4. 以上所有

答案:C.关键字NATURAL JOIN指示Oracle识别源表和目标表之间具有相同名称的列.自然连接使用具有匹配名称和数据类型的所有列来连接表. USING子句可用于仅指定应用于等值连接的那些列.

13.假设必须使用NATURAL JOIN连接表EMPLOYEES和DEPARTMENT.以下两个查询之间有什么区别？ (考虑给定的表结构) 

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)

SQL> DESC departments Name Null?  Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID  NUMBER(6) LOCATION_ID  NUMBER(4)

  SELECT department_id  FROM employees NATURAL JOIN department  WHERE first_name ='John' AND last_name ='Bacon';

  SELECT department_id  FROM department NATURAL JOIN employees  WHERE first_name ='John' AND last_name ='培根';

  1. 没有区别

  2. 结果在两种情况下都不同

  3. 这两个查询都会在执行时出现ORA错误

  4. 以上都不是

答案:B.使用NATURAL JOIN时，可以交换源表和目标表，从而提供相关的不同结果集.

14.关于Oracle DB中的NATURAL JOIN，以下哪个选项是正确的？

  1. 使用NATURAL JOIN时，提及两个表中所有列的名称是必需的

  2. 只有两个表的所有列的名称都可以使用NATURAL JOIN是相同的

  3. 仅当用户指定源表和目标表的列时，才会发生NATURAL JOIN中的连接.

  4. 没有必要在使用NATURAL JOINS时提及列.

答案:D.有一个n当使用NATURAL JOIN时，隐式连接源表和目标表中的列. NATURAL JOIN是一个JOIN操作，它根据要连接的两个表中的公共列为您创建隐式连接子句.常用列是两个表中具有相同名称的列.

15. NATURAL JOIN与JOIN..ON的连接有什么区别？

  1. 两者之间没有区别

  2. JOIN..ON加入具有相同名称的特定列上的源表和目标表

  3. NATURAL JOIN隐式连接源表和目标表中的所有匹配列

  4. 以上都不是

答案:B，C.自然连接的连接条件基本上是所有具有相同名称的列的等值连接.使用ON子句指定任意条件或指定要连接的列.连接条件是分开的从其他搜索条件. ON子句使代码易于理解.

16. Oracle DB中的JOIN..ON子句有什么用？

  1. 它不依赖于具有相同名称的源表和目标表中的列

  2. 仅限那些来自源表和具有相同名称的目标表的列可以与此子句一起使用

  3. 它是NATURAL JOIN的格式

  4. 全部上面的

答案:A，C.自然连接的连接条件基本上是所有列的等值连接.一样的名字.使用ON子句指定任意条件或指定要连接的列.连接条件与其他搜索条件分开. ON子句使代码易于理解.

17.数据库设计者以不同的方式命名两个表中的键(唯一)列.在加入这些表时，以下哪个是最佳实践？

  1. JOIN..ON

  2. NATURAL JOIN或JOIN ... ON子句

  3. 两者A和B

  4. 以上都不是

答案:A.使用NATURAL在这种情况下，JOINS会产生意外结果，因为隐式搜索具有相同名称的列，在这种情况下不存在.

18.以下哪些内容可以使用在Oracle DB中获取不匹配的行以及源表和目标表之间的匹配行？

  1. EQUI-JOIN

  2. SELF-JOIN

  3. NATURAL JOIN

  4. OUTER-JOIN

答案:D.当记录需要包含在结果中而没有连接中的相应记录时，会创建外部联接表.这些记录与NULL记录匹配，以便它们包含在输出中.

19.什么是笛卡尔连接，也称为Oracle DB？

  1. Equi-join

  2. 反加入

  3. 交叉加入

  4. 以上都不是

答案:C.两个表之间的笛卡尔联接会返回表中每个可能的行组合.可以通过在查询中不包括连接操作或使用CROSS JOIN来生成笛卡尔连接.

20.两个表之间的NATURAL JOIN的结果是什么员工和部门在下面的查询中给出？ (考虑给定的表结构) 

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)

SQL> DESC departments Name Null?  Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID  NUMBER(6) LOCATION_ID  NUMBER(4)

 SELECT * FROM employees NATURAL JOIN department;

  1. 两个表中具有相同名称的公共列DEPARTMENT_ID将在结果集中出现两次

  2. 所有具有相同名称的列与NATURAL JOIN连接，将出现两次结果集

  3. 结果集对于来自两个表的每对具有相同名称的列只有一列

  4. 以上都不是

答案:C. NATURAL JOIN关键字不需要条件来建立两个表之间的关系.但是，必须存在公共列.列限定符不能与NATURAL JOIN关键字一起使用.

21.在Oracle DB中，NATURAL JOIN和EQUI-JOIN之间有什么区别？

  1. 没有区别

  2. 它们是相同的从两者获得的结果集

  3. A和B两者

  4. 以上都不是

答案:D. NATURAL JOIN连接具有相同名称的所有列，而EQUI-JOIN要求在SQL查询中明确提及列.

22. Oracle DB中的内部联接是什么？

  1. 在两个表之间给出匹配记录的连接称为INNER JOIN

  2. 内连接可以使用

  3. 等运算符

  4. 两者A和B

  5. 以上都不是

答案:C. A join可以是内连接，其中返回的唯一记录在al中具有匹配的记录l表或外连接，无论连接中是否有匹配的记录，都可以返回记录.

23. INNER JOIN和INNER JOIN之间有什么区别Oracle DB中的EQUI-JOIN？

  1. 它们在语法和结果方面是相同的得到的集合.

  2. INNER JOIN是EQUI-JOIN的子集

  3. INNER JOIN可以使用像

  4. 以上所有

答案:C. EQUI-JOIN是一种在连接条件下包含"="运算符的INNER JOIN，而INNER JOIN既包含相等也包含非相等运算符

24.根据ANSI SQL中的NATURAL JOINS，在Oracle DB中的1999语法是什么？

  1. 使用等式运算符(=)

  2. 与传统语法相比，它们会获取不同的结果

  3. ANSI SQL语法在SQL查询中使用NATURAL JOIN等词语

  4. 以上都不是.

答案:C. ANSI SQL语法与传统方式使用(=)的传统方式不同.在ANSI SQL语法中有像NATURAL JOIN等关键字来区分使用的连接.

25.对于下面给出的查询，以下是什么？ (考虑给定的表结构) 

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)

SQL> DESC departments Name Null?  Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID  NUMBER(6) LOCATION_ID  NUMBER(4)

 SELECT first_name，salary  FROM employees e，departments d  WHERE e.department_id(+)= d.department_id;

  1. 两个表的department_id之间有一个外连接，相当于ANSI SQL中的右外连接

  2. 两个表的department_id之间有一个外连接，相当于ANSI SQL中的左外连接

  3. 它从employees表中获取department_id的所有记录，无论它们是否匹配

  4. 它从department表中获取department_id的所有记录，无论它们是否匹配

答案:A，D.条件e.department_id(+)= d.department_id表示它将执行Right Outer Join和all将显示department表中的department_id是否匹配

26.以下语法模型中的哪些广泛用于全球软件系统？

  1. ANSI SQL:1999

  2. 两种传统的Oracle语法ax和ANSI SQL:1999语法

  3. 传统的Oracle语法

  4. 所有选项

答案:C. ANSI SQL:1999语法虽然没有像传统的Oracle语法那样使用，但它仍然是可以在Oracle SQL中使用的语法之一

27.关于Oracle DB中的笛卡尔积，以下是什么？

  1. 如果'N'是加入的表的no，那么如果没有.连接是N-1，笛卡尔积不执行

  2. 如果'N'是加入的表的数量，那么如果没有.连接是N，执行笛卡尔积

  3. 如果'N'是连接的表的数量，那么如果没有.连接是N + 1，执行笛卡尔积

  4. 如果'N'是连接的表的数量，那么如果没有.连接数为N-1或更少，执行笛卡尔积.

答案:A.两个表之间的笛卡尔连接返回表中每个可能的行组合.可以通过在查询中不包括连接操作或使用CROSS JOIN来生成笛卡尔连接.查询必须至少具有(N-1)个连接条件才能阻止笛卡尔积，其中N是查询中表的数量.

28.原因是什么以下SQL查询中的错误？ (考虑给定的表结构) 

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)

SQL> DESC departments Name Null?  Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID  NUMBER(6) LOCATION_ID  NUMBER(4)

 SELECT first_name，last_name  FROM employees，departments  WHERE department_id(+)= department_id;

  1. 表中没有使用别名.

  2. 这里没有使用RIGHT OUTER JOIN这个词因此它会抛出错误

  3. (+)应该在相等条件的右侧，而不是在左侧

  4. 表别名应该与条件中的department_id一起使用以删除含糊不清的命名

答案: D. 如果没有表别名，Oracle无法派生所连接列的原点，因此会在执行时抛出Ambiguity错误.

29.Which of以下用于避免Oracle DB中的模糊列问题？

  1. ;

  2. ，

  3. .

  4. /

答案:C.删除不明确的列问题的语法是:table_alias.column_name

30.以下哪项是最合适的关于以下查询？ (考虑给定的表结构) 

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)

SQL> DESC departments Name Null?  Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID  NUMBER(6) LOCATION_ID  NUMBER(4)

 SELECT employee_id，first_name，last_name  FROM employees e right outer join department d  on e.department_id = d.department_id;

  1. 它提供了不在任何部门的员工的详细信息

  2. 它提供了那些没有任何员工的部门的详细信息

  3. 它提供了所有部门的详细信息，无论他们是否有任何员工

  4. 它提供了公司'ABC'雇用的员工的详细信息，无论部门如何.

答案:C.使用外部联接的JOIN方法，您可以添加LEFT，RIGHT或FULL关键字.左外连接包括连接左侧列出的表中的所有记录，即使在连接操作中找不到与另一个表匹配的记录也是如此.完全外连接包括来自两个表的所有记录，即使找不到另一个表中的相应记录.

31.以下查询的结果是什么？ (考虑给定的表结构) 

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)

SQL> DESC departments Name Null?  Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID  NUMBER(6) LOCATION_ID  NUMBER(4)

 SELECT *  FROM employees e，department d  WHERE a.department_id = b.department_id;

  1. 它将给出两个具有相同值的表中的所有匹配值department_id

  2. 它将提供表员工的所有列，只显示departments表中的前100行

  3. 它会产生ORA错误: "b.department_id"无效标识符

  4. 以上所有选项

答案:C.在FROM子句中声明的WHERE子句中应该使用相同的别名

32.关于表连接中的USING和ON子句，以下哪些是正确的？ (如果适用，请选择多个选项)

  1. ON子句可用于连接表在具有相同数据类型但不一定是相同名称的列上

  2. USING和ON子句仅用于equijoins和非等值连接

  3. 不ON子句可以使用多个条件

  4. 可以在USING..ON子句之后写入WHERE子句以应用其他条件

答案:A，D. JOIN ... USING方法类似于NATURAL JOIN方法，除了在USING子句中指定了公共列. USING子句中不能包含条件以指示表是如何相关的.此外，列限定符不能用于USING子句中指定的公共列. JOIN ... ON方法根据指定的条件连接表. FROM子句中的JOIN关键字指示要连接的表，ON子句指示两个表的关联方式.如果要连接的表没有每个表中具有相同名称的公共列，则必须使用此方法.

33.使用以下表可以连接多少个表加入Oracle DB？

  1. 1

  2. 2

  3. 255

  4. 无限制

答案:D .目前参与加入的表数没有限制.

34.在SQL语句中使用多个连接时是什么情况？

  1. 连接的评估来自从左到右

  2. 从右到左评估联接

  3. 评估联接的过程中没有优先级

  4. 以上都不是

答案:A.当声明中存在多连接时，它们是从左到右进行评估.

35.以下查询是什么情况？ (考虑给定的表结构) 

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)

SQL> DESC departments Name Null?  Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID  NUMBER(6) LOCATION_ID  NUMBER(4)

SQL> desc bonus Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER JOB_ID   VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2)

 SELECT bonus，first_name，department_id  FROM bonus b NATURAL JOIN employees e NATURAL JOIN department d;

  1. 使用NATURAL JOIN连接多个表是更好的选择和收益准确的结果

  2. 当使用NATURAL JOIN连接多个表时，错误概率非常小

  3. 条款USING..JOIN..ON给出更多在连接多个表时，结果比NATURAL JOIN更准确

  4. 当使用NATURAL JOIN时，Oracle隐式连接多个表，因此使用NATURAL JOINS是一个好习惯

答案:C.使用NATURAL JOINS可以创建行的笛卡尔积，并且使用不可靠的结果集也容易出错.

36. Oracle DB中的JOIN..ON条款有什么用？

  1. 与连接多个表时的NATURAL JOINS相比，它们不是很可靠

  2. JOIN..ON子句类似于WHERE子句，它限制具有条件的行

  3. 另外一个使用JOIN..ON子句时必须使用WHERE子句

  4. 以上都不是

答案:B. JOIN .... ON方法根据指定的条件连接表. FROM子句中的JOIN关键字指示要连接的表，ON子句指示两个表的关联方式.如果要连接的表在每个表中没有具有相同名称的公共列，则必须使用此方法.

检查给定的表结构.回答下面给出的查询后面的问题37和38: 

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)

SQL> DESC departments Name Null?  Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID  NUMBER(6) LOCATION_ID  NUMBER(4)

 SELECT e.salary，d.department_id  FROM employees e JOIN department d  On(e.department_id = d.department_id and e.last_name ='Brandon ");

37.关于上面给出的查询是什么情况？

  1. It gives an ORA error as the mandatory WHERE clause is not present

  2. The JOIN..ON clause can't contain more than one condition

  3. The query ignores the last condition and executes without an ORA error

  4. The JOIN..ON clause can be written in the form given above for putting more conditions.

Answer: D. The WHERE clause can be omitted and the relevant conditions can be accommodated in the JOIN..ON clause itself as shown in the given query

38.With respect to the given query, if the JOIN used is replaced with NATURAL JOIN, it throws an error. What is the reason for this error?

  1. When the NATURAL JOIN is used, a WHERE clause is mandatory, omitting which gives an error

  2. The ON clause should be replaced with the USING clause

  3. The words NATURAL, JOIN and USING are mutually exclusively in the context of the same join clause

  4. A query can’t combine the NATURAL JOIN and ON (or USING) clauses while joining.

Answer: C, D.

39.What is true about Non-equijoins in Oracle DB?

  1. They join based on the keyword NON-EQUI JOIN

  2. They are used using the JOIN..ON clause with "=" sign

  3. The results are obtained when the result of the inequality mentioned evaluates to true.

  4. None of the above

Answer: C. The non-equi joins are used with the JOIN..ON clause but with inequality operators.

Examine the structures of the tables EMPLOYEES and DEPARTMENTS as given and answer the questions 40 and 41 that follow. 

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)
SQL> DESC departments Name Null?  Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID  NUMBER(6) LOCATION_ID  NUMBER(4)

40.What will be the outcome of the following query in Oracle DB? 

SELECT e.first_name, e.last_name, e.employee_id   FROM employees e JOIN department d  ON (e.salary BETWEEN 1000 AND 10000);

 

  1. It will throw an ORA error as the condition in the ON clause is incorrect.

  2. It will throw an ORA error due to a syntax error as there is no Equality sign "=" in the ON clause

  3. It will execute successfully and give the first name, last name and employee ID of employees with the condition mentioned.

  4. Non-equi joins can only be used for showing in-equalities and not ranges.

Answer: C.

41.You need to find a report which lists the first names and last names of the employees whose salary is greater than 20000 and who are located in any of the departments in the location Geneva. Which of the following queries will give the required results?

  1. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>20000)  AND d.loc = upper (’Geneva’);
  2. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>=20000);
  3. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>=20000)  AND d.loc = ’Geneva’;
  4. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>20000)  WHERE d.loc = upper(’Geneva’);

Answer: A, C.

42.On which of the following conditions is a row returned when an EQUI-JOIN is used to join tables?

  1. The result of the inequality match operation is true

  2. The result of the inequality match operation is 0

  3. The result of the inequality match operation is 1

  4. The result of the inequality match operation is false

Answer: A. An equality join is created when data joining records from two different tables is an exact match (that is, an equality condition creates the relationship).The traditional approach uses an equal sign as the comparison operator in the WHERE clause. The JOIN approach can use the NATURAL JOIN, JOIN ... USING, or JOIN ... ON keywords.

41.You need to find a report which lists the first names and last names of the employees whose salary is greater than 20000 and who are located in any of the departments in the location Geneva. Which of the following queries will give the required results?

  1. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>20000)  AND d.loc = upper (’Geneva’);
  2. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>=20000);
  3. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>=20000)  AND d.loc = ’Geneva’;
  4. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>20000)  WHERE d.loc = upper(’Geneva’);

Answer: A, C.

42.On which of the following conditions is a row returned when an EQUI-JOIN is used to join tables?

  1. The result of the inequality match operation is true

  2. The result of the inequality match operation is 0

  3. The result of the inequality match operation is 1

  4. The result of the inequality match operation is false

Answer: A. An equality join is created when data joining records from two different tables is an exact match (that is, an equality condition creates the relationship).The traditional approach uses an equal sign as the comparison operator in the WHERE clause. The JOIN approach can use the NATURAL JOIN, JOIN ... USING, or JOIN ... ON keywords.

41.You need to find a report which lists the first names and last names of the employees whose salary is greater than 20000 and who are located in any of the departments in the location Geneva. Which of the following queries will give the required results?

  1. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>20000)  AND d.loc = upper (’Geneva’);
  2. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>=20000);
  3. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>=20000)  AND d.loc = ’Geneva’;
  4. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>20000)  WHERE d.loc = upper(’Geneva’);

Answer: A, C.

42.On which of the following conditions is a row returned when an EQUI-JOIN is used to join tables?

  1. The result of the inequality match operation is true

  2. The result of the inequality match operation is 0

  3. The result of the inequality match operation is 1

  4. The result of the inequality match operation is false

Answer: A. An equality join is created when data joining records from two different tables is an exact match (that is, an equality condition creates the relationship).The traditional approach uses an equal sign as the comparison operator in the WHERE clause. The JOIN approach can use the NATURAL JOIN, JOIN ... USING, or JOIN ... ON keywords.

41.You need to find a report which lists the first names and last names of the employees whose salary is greater than 20000 and who are located in any of the departments in the location Geneva. Which of the following queries will give the required results?

  1. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>20000)  AND d.loc = upper (’Geneva’);
  2. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>=20000);
  3. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>=20000)  AND d.loc = ’Geneva’;
  4. SELECT e.first_name, e.last_name  FROM employees e JOIN departments d  ON (e.department_id  = d.department_id  and e.salary>20000)  WHERE d.loc = upper(’Geneva’);

Answer: A, C.

42.On which of the following conditions is a row returned when an EQUI-JOIN is used to join tables?

  1. The result of the inequality match operation is true

  2. The result of the inequality match operation is 0

  3. The result of the inequality match operation is 1

  4. The result of the inequality match operation is false

Answer: A. An equality join is created when data joining records from two different tables is an exact match (that is, an equality condition creates the relationship).The traditional approach uses an equal sign as the comparison operator in the WHERE clause. The JOIN approach can use the NATURAL JOIN, JOIN ... USING, or JOIN ... ON keywords.

43.What is true regarding a Self-Join in Oracle DB?

  1. Only two tables are required for the join to work

  2. The columns in the result set are obtained from two tables but are displayed in one table

  3. Conceptually, the source table duplicates itself to create the target table. (Oracle doesn’t duplicate tables)

  4. All of the above

Answer: C. Self-joins are used when a table must be joined to itself to retrieve the data you need. Table aliases are required in the FROM clause to perform a self-join.

44. With respect to the query and the table structure given below,answer the question.

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)

SELECT *  FROM employees a join employees b  WHERE a.employee_id  = b.employee_id ;

 

Which of the following tables does Oracle treat as source table and target table?

  1. a is source and b is target

  2. b is source and a is target

  3. Oracle doesn’t treat any of the tables as source or target

  4. None of the above

Answer: A. The first occurrence of employees table is taken as source and the subsequent occurrences as b, c and so on.

45.In what scenarios can we use Self-Joins ideally in Oracle DB?

  1. When we need to find the duplicate records in a table

  2. When we need to obtain NULL values from a table

  3. When we need to display a column of a table twice

  4. When we need to display hierarchy of relevant entities

Answer: D.

46. What is true about NATURAL JOINS in Oracle DB?

  1. The joined columns have the same name but can have different data types

  2. The joined columns can have the same data type but different names

  3. The joined columns should have identical names and the same data type

  4. None of the above

Answer: C. The NATURAL JOIN keywords don’t require a condition to establish the relationship between two tables. However, a common column must exist.Column qualifiers can’t be used with the NATURAL JOIN keywords.

47.A report has to be extracted which gives the department name, department ID, department city and location ID only for departments 100 and 101. Using NATURAL JOINS, which of the following options will give the required results?


  Table DEPARTMENTS
  Table LOCATIONS
 

  1. SELECT department_id , department_name  ,location, city  FROM departments   NATURAL JOIN locations   WHERE department_id in (100,101);
  2. SELECT department_id , department_name  ,location, city  FROM locations   NATURAL JOIN departments   WHERE department_id BETWEEN 100 AND 101;
  3. SELECT department_id , department_name  ,location, city  FROM departments   NATURAL JOIN locations   WHERE department_id>100  AND department_id>101;
  4. SELECT department_id , department_name  ,location, city  FROM departments   NATURAL JOIN locations ;

Answer: A. The WHERE can be used for additional conditions after the NATURAL JOIN clause.

48.In which of the following scenarios shall a USING clause or a NATURAL JOIN clause be used?

  1. When the names of the columns from the tables are identical then use USING clause

  2. When the data types of the columns from the tables are identical then use NATURAL JOINS

  3. If several columns have the same names but the data types do not match, USING can be used

  4. NATURAL JOINS should be used only when the column names and their data types are the same

Answer: C, D. NATURAL JOINS and USING are mutually exclusive, the USING clause should be used to match only one column when more than one columns match.

49.Examine the table structures given. What will be the outcome of the following query? (Choose the most appropriate answer)

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)

SQL> desc bonus   Name\t\t\t Null? Type   ----------------------- -------- ----------------   EMPLOYEE_ID\t\t NOT NULL NUMBER   JOB_ID \t\t\t  VARCHAR2(10)   SALARY \t\t\t  NUMBER(8,2)   COMMISSION_PCT \t\t  NUMBER(2,2)

SELECT e.name, b.bonus  FROM employees e   JOIN bonus b  USING (job_id)  WHERE e.job_id  like ’SA%’);

  1. It gives the names and bonus obtained by all the employees in some company

  2. It gives the names and bonus obtained by all the employees in a particular job title in a company

  3. It executes successfully giving all the names and the bonus obtained by all the employees in all jobs

  4. It throws an ORA error.

Answer: D. The column(s) used in the USING clause should not have a qualifier (table name or alias) anywhere in the SQL statement.

50.What is true with respect to INNER JOINS and OUTER JOINS in Oracle DB?

  1. INNER JOIN returns only the rows that are matched

  2. OUTER JOIN returns only the rows that are not matched

  3. OUTER JOIN returns the rows that are matched as well as those which do not match

  4. None of the above

Answer: A, C. A join can be an inner join,in which the only records returned have a matching record in all tables,or an outer join, in which records can be returned regardless of whether there’s a matching record in the join.An outer join is created when records need to be included in the results without having corresponding records in the join tables. These records are matched with NULL records so that they’re included in the output.

51. What is true regarding FULL OUTER JOIN in Oracle DB?

  1. When both LEFT OUTER JOIN and RIGHT OUTER JOIN appear in the same query,it is called a FULL OUTER JOIN

  2. A FULL OUTER JOIN is the same as an OUTER JOIN

  3. Both A and B

  4. A join between two tables that returns the results of an INNER join and a LEFT and RIGHT OUTER JOIN is called a FULL OUTER JOIN

Answer: D. A full outer join includes all records from both tables, even if no corresponding record in the other table is found.

Examine the given table structures and answer the questions 52 and 53 that follow.

SQL> DESC employees   Name\t\t\t Null? Type   ----------------------- -------- ----------------   EMPLOYEE_ID\t\t NOT NULL NUMBER(6)   FIRST_NAME\t\t\t  VARCHAR2(20)   LAST_NAME\t\t NOT NULL VARCHAR2(25)   EMAIL\t\t\t NOT NULL VARCHAR2(25)   PHONE_NUMBER\t\t\t  VARCHAR2(20)   HIRE_DATE\t\t NOT NULL DATE   JOB_ID \t\t NOT NULL VARCHAR2(10)   SALARY \t\t\t  NUMBER(8,2)   COMMISSION_PCT \t\t  NUMBER(2,2)   MANAGER_ID\t\t\t  NUMBER(6)   DEPARTMENT_ID\t\t\t  NUMBER(4)
SQL> desc bonus Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER JOB_ID   VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2)
SQL> desc bonus Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER JOB_ID   VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2)

52.Consider the following query.

SELECT e.job_id , e.first_name, d.department_id   FROM departments D JOIN employees e JOIN BONUS b  USING (job_id );

This query results in an error. What is the reason of the error?

  1. A JOIN..USING can happen only between two tables at a time

  2. USING clause in the query doesn’t have any column from the department

  3. There is no WHERE clause in the query

  4. None of the above

Answer: A. Table1 JOIN table2 JOIN table3 is not allowed without the ON clauses for between each JOIN

53.You need to display all the non-matching rows from the EMPLOYEES table and the non-matching rows from the DEPARTMENT table without giving a Cartesian product of rows between them. Which of the following queries will give the desired output?

  1. SELECT *  FROM employees e, department d  WHERE e.department_id  = d.department_id ;
  2. SELECT *  FROM employees e NATURAL JOIN department d;
  3. SELECT *  FROM employees e FULL OUTER JOIN department d  ON  e.department_id  = d.department_id ;
  4. SELECT *  FROM employees e JOIN  department d  ON ( e.department_id > d.department_id ) ;

Answer: C. The FULL OUTER JOIN returns the non-matched rows from both the tables. A full outer join includes all records from both tables, even if no corresponding record in the other table is found.

54.Which of the following ANSI SQL: 1999 join syntax joins are supported by Oracle?

  1. Cartesian products

  2. Natural joins

  3. Full OUTER join

  4. Equijoins

Answer: D.

55.Which of the following is not a format for Outer Joins in Oracle DB?

  1. Right

  2. Left

  3. Centre

  4. Full

Answer: C. Except ’Centre’, rest 3 types are the types of formats of the Outer Joins in Oracle DB. With the JOIN method for outer joins, you can add the LEFT, RIGHT, or FULL keywords.

Examine the given table structures. Answer the questions 56, 57 and 58 that follow by referring to the following query:

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)

SQL> DESC departments Name Null?  Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID  NUMBER(6) LOCATION_ID  NUMBER(4)

SELECT *  FROM employees e NATURAL JOIN department d;

 

56.You need to find the results obtained by the above query only for the departments 100 and 101. Which of the following clauses should be added/modified to the above query?

  1. ON (e.department_id  = d.department_id ) should be added

  2. USING (e.department_id ) should be added

  3. WHERE e.department_id  in (100,101) should be added

  4. None of the above

Answer: C. The NATURAL JOIN clause implicitly matches all the identical named columns. To add additional conditions the WHERE clause can be used.

57.You need to find the results obtained by the above query for all those employees who have salaries greater than 20000. Which of the following clauses should be added/modified to the above query?

  1. ON (e.department_id  = d.department_id )
    WHERE salary> 20000;

  2. USING (e.department_id )
    WHERE salary> 20000;

  3. USING (department_id )
    WHERE salary>20000;

  4. WHERE salary>20000;

Answer: D.

58.If the NATURAL JOIN in the above query is replaced by only JOIN which of the following should be added/modified to the above query to give the results pertaining to Department 100?

  1. ON (department_id  = 100);

  2. USING (e.department_id =100);

  3. WHERE d.department_id  = 100;

  4. ON (e.department_id  = d.department_id  and d.department_id  = 100);

Answer: D. The equi-joins can be added for more conditions after the ON clause.

59.A report has to be extracted to get the Managers for all the employees in the departments 10 and 20 of a company ’ABC’. Which of the following queries will give the required results? (Consider the table structure as given) 

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)

  1. SELECT a.first_name || ’ ’||a.last_name "Manager", b.first_name||’ ’||b.last_name "Employees"  FROM employees a join employees b  On (employee_id );
  2. SELECT a.first_name || ’ ’||a.last_name "Manager", b.first_name||’ ’||b.last_name "Employees"  FROM employees a join employees b  On (b.employee_id  = a.employee_id );

  3. SELECT a.first_name || ’ ’||a.last_name "Manager", b.first_name||’ ’||b.last_name "Employees"  FROM employees a join employees b  On (a.manager_id  = b.employee_id )  WHERE department_id  in (10,20);
  4. SELECT a.first_name || ’ ’||a.last_name "Manager", b.first_name||’ ’||b.last_name "Employees"  FROM employees a join employees b  On (a.manager_id  = b.employee_id )  WHERE a.department_id  in (10,20);

Answer: D. The option C is incorrect because the non-aliased department_id  in the WHERE clause will throw an error.

60.Which of the following queries will give results without duplicate values between the two tables EMPLOYEES and DEPARTMENT? (Consider the table structures as given)

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)
SQL> DESC departments Name Null?  Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID  NUMBER(6) LOCATION_ID  NUMBER(4)

  1. SELECT *  FROM employees e NATURAL JOIN department d;
  2. SELECT *  FROM employees e JOIN department d;
  3. SELECT *  FROM employees e NATURAL JOIN department d  USING (e.department_id );
  4. SELECT *   FROM employees e FULL OUTER JOIN department d  USING (department_id );

Answer: D. The FULL OUTER JOIN will give all the matching as well non-matching rows from both the tables excluding duplicate values.

Examine the structures for the tables as given here and answer the questions 61 to 64. 

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)
SQL> desc bonus Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER JOB_ID   VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2)

SQL> desc locations Name Null?  Type ----------------------- -------- ---------------- LOCATION_ID NOT NULL NUMBER(4) STREET_ADDRESS   VARCHAR2(40) POSTAL_CODE  VARCHAR2(12) CITY NOT NULL VARCHAR2(30) STATE_PROVINCE   VARCHAR2(25) COUNTRY_ID  CHAR(2)

SQL> desc locations Name Null?  Type ----------------------- -------- ---------------- LOCATION_ID NOT NULL NUMBER(4) STREET_ADDRESS   VARCHAR2(40) POSTAL_CODE  VARCHAR2(12) CITY NOT NULL VARCHAR2(30) STATE_PROVINCE   VARCHAR2(25) COUNTRY_ID  CHAR(2)

61.What is true about the following query? (Choose the most appropriate answer)

SELECT *  FROM bonus b, employees e  WHERE b.job_id  (+) = e.job_id ;

  1. It will display all the bonuses obtained by all the employees.

  2. It will display NULL for the bonus column if a particular employee has not received any bonus

  3. Both A and B

  4. None of the above

Answer: B. The (+) is on the LHS of the equation means it is a RIGHT OUTER JOIN and vice versa.

62.You have to list all the departments who have no employees yet in a company named ’XYZ’. Which of the following queries will give you the required results?

  1. SELECT department_id , department_name FROM departments d NATURAL JOIN employees e;
  2. SELECT department_id , department_name FROM employees e JOIN departments d  ON (e.department_id  = d.department_id );
  3. SELECT department_id , department_name FROM employees e LEFT OUTER JOIN departments d  USING (department_id );
  4. SELECT department_id , department_name FROM employees e RIGHT OUTER JOIN departments d  ON (e.department_id  = d.department_id );

Answer: D.

63.You need to extract a report which displays ’No department yet’ for all those employees who are yet to be allocated to a department. Which of the following will fulfill the purpose?

  1. SELECT nvl(department_id ,’No department yet’)  FROM employees e RIGHT OUTER JOIN departments d  ON (e.department_id  = d.department_id );
  2. SELECT nvl(department_id ,’No department yet’)  FROM departments d LEFT OUTER JOIN employees e  ON (e.department_id  = d.department_id );
  3. SELECT nvl(department_id ,’No department yet’)  FROM employees e LEFT OUTER JOIN departments d  ON (e.department_id  = d.department_id );
  4. SELECT nvl(department_id ,’No department yet’)  FROM employees e FULL OUTER JOIN departments d  ON (e.department_id  = d.department_id );

Answer: C.

64.You need to extract a report which displays all the departments which have not been assigned to a city yet. Which of the following queries will give you the required output?

  1. SELECT department_id , department_name FROM departments d NATURAL JOIN locations l;
  2. SELECT department_id , department_name FROM departments d FULL OUTTER JOIN locations l  ON (d.location_id = l.location_id);
  3. SELECT  d.department_id , d.department_name FROM departments d JOIN locations l  USING (location_id);
  4. SELECT department_id , department_name FROM departments d LEFT OUTER JOIN locations l  ON (d.location_id = l.location_id);

Answer: D.

65.In which two cases an OUTER JOIN should be used?

  1. If the joined tables’ columns have NULL values

  2. If the joined tables have NOT NULL columns

  3. If the joined tables have only un-matched data

  4. If the joined tables have both matching as well as non-matching data

Answer: A, D. An outer join is created when records need to be included in the results without having corresponding records in the join tables. These records are matched with NULL records so that they’re included in the output.

66.You need to find the salary grade obtained by each employee. Which of the following query will you use? (Consider the table structures as given)

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)
SQL> desc grade Name Null?  Type ----------------------- -------- ---------------- GRADE   NUMBER LOSAL   NUMBER HISAL  NUMBER

  1. SELECT employee_id , salary, grade  FROM employees e JOIN grade g  ON g.grade BETWEEN g.losal AND g.hisal
  2. SELECT employee_id , salary, grade  FROM employees e FULL OUTER JOIN grade g  WHERE g.grade> g.losal AND
  3. SELECT employee_id , salary, grade   FROM employees e JOIN grade g  ON (MIN(g.grade) = g.losal  AND MAX(g.grade) = g.hisal);

  4. None of the above

Answer: A.

67.Examine the table structures given.

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)

SQL> desc bonus Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER JOB_ID   VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2)

Evaluate this SQL statement:

  SELECT e.employee_id , (.25* e.salary) + (.5 * e.commission_pct) + (.75 * b.bonus) as calc_val  FROM employees e, bonus b  WHERE e.employee_id  = b.employee_id ;

What will happen if you remove all the parentheses from the calculation?

  1. The value displayed in the calc_val column will be lesser.

  2. The value displayed in the calc_val column will be higher.

  3. There will be no difference in the calc_val column.

  4. An error will be reported.

Answer: C.

68.Consider the exhibit and examine the structures of the EMPLOYEES, DEPARTMENTS, and GRADE tables. For which situation would you use a non-equijoin query?

Table EMPLOYEES Table DEPARTMENTS Table GRADE

  1. To find the grade for each of the employees

  2. To list the name, job_id, and manager name for all the employees

  3. To find the department name of employees.

  4. To find the number of employees working for the Administrative department and earning less than 30000

Answer: A. A non-equality join establishes a relationship based on anything other than an equal condition. Range values used with non-equality joins must be mutually exclusive.

69.In which three cases would you use the USING clause? (Choose three.)

  1. You want to create a non-equijoin.

  2. The tables to be joined have multiple NULL columns.

  3. The tables to be joined have columns of the same name and different data types.

  4. You want to use a NATURAL join, but you want to restrict the number of columns in the join condition.

Answer: C, D. The JOIN .... USING approach is similar to the NATURAL JOIN approach, except the common column is specified in the USING clause. A condition can’t be included in the USING clause to indicate how the tables are related. In addition, column qualifiers can’t be used for the common column specified in the USING clause.

70.If the tables EMPLOYEES and BONUS have two columns with identical names viz: - SALARY and JOB_ID, which of the following queries are equivalent to each other? (Consider the table structures as given)

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)

SQL> desc bonus Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER JOB_ID   VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2)

  1. SELECT * FROM employees E JOIN bonus B on (salary, job_id )
  2. SELECT * FROM employees E NATURAL JOIN bonus B on (salary, job_id )
  3. SELECT * FROM employees E JOIN bonus B USING (salary, job_id )
  4. SELECT * FROM employees E JOIN bonus B on (salary, job_id )

Answer: B, C.

71.Examine the table structures as given. 

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)
SQL> DESC departments Name Null?  Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID  NUMBER(6) LOCATION_ID  NUMBER(4)

Examine the following two SQL statements:

Query 1 

  SELECT first_name,department_id   FROM employees E JOIN departments D  USING (department_id );

Query 2 

  SELECT first_name,department_id   FROM employees E NATURAL JOIN departments D  USING (department_id );

Which statement is true regarding the outcome?

  1. Only query 1 executes successfully and gives the required result.

  2. Only query 2 executes successfully and gives the required result.

  3. Both queries 1 and 2 execute successfully and give different results.

  4. Both queries 1 and 2 execute successfully and give the same required result.

Answer: D.

72.You need to generate a report showing the department location along with the employee name for all hires made before 20th January, 2013.

Table DEPARTMENTS
  Table EMPLOYEES
  Table LOCATIONS
 

You issue the following query:

  SELECT department_name  , first_name||’ ’||last_name  FROM employees E JOIN department d  ON ( hire_date <’20-JAN-2013’)  JOIN locations L  ON  (l.location_id = d.location_id) ;

Which statement is true regarding the above query?

  1. It executes successfully and gives the required result.

  2. It executes successfully but does not give the required result.

  3. It produces an error because the join order of the tables is incorrect.

  4. It produces an error because equijoin and non-equijoin conditions cannot be used in the same SELECT statement.

Answer: B.

73.Examine the structure of the EMPLOYEES table:

Table EMPLOYEES

You want to find out if any employee’ details have been entered more than once using different EMPLOYEE_ID , by listing all the duplicate names. Which method can you use to get the required result?

  1. self-join

  2. full outer-join with self-join

  3. left outer-join with self-join

  4. right outer-join with self-join

Answer: A. Self-joins are used when a table must be joined to itself to retrieve the data you need. Table aliases are required in the FROM clause to perform a self-join.

Examine the structure of the tables DEPARTMENTS and LOCATIONS and answer the questions 74 and 75 that follow. 

SQL> DESC departments Name Null?  Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID  NUMBER(6) LOCATION_ID  NUMBER(4)

SQL> desc locations Name Null?  Type ----------------------- -------- ---------------- LOCATION_ID NOT NULL NUMBER(4) STREET_ADDRESS   VARCHAR2(40) POSTAL_CODE  VARCHAR2(12) CITY NOT NULL VARCHAR2(30) STATE_PROVINCE   VARCHAR2(25) COUNTRY_ID  CHAR(2)

74.You need to find out the departments that have not been allocated any location. Which query would give the required result?

  1. SELECT d.department_id , d.department_name FROM departments d JOIN locations l  ON (d.location_id = l.location_id);
  2. SELECT d.department_id , d.department_name FROM departments d RIGHT OUTER JOIN locations l  ON (d.location_id = l.location_id);
  3. SELECT d.department_id , d.department_name FROM departments d FULL JOIN locations l  ON (d.location_id = l.location_id);
  4. SELECT d.department_id , d.department_name FROM departments d LEFT OUTER JOIN locations l  ON (d.location_id = l.location_id);

Answer: B.

75.You want to list all departments that are not located in any location along with the department name. Evaluate the following query:

  SELECT d.department_id , d.department_name  ,l.location_id, l.city  FROM departments D __________________   location L   ON (d.location_id = l.location_id);

Which two JOIN options can be used in the blank in the above query to give the correct output?

  1. JOIN

  2. NATURAL JOIN

  3. LEFT OUTER JOIN

  4. RIGHT OUTER JOIN

Answer: A, C.

76. You need to generate a report that shows all department IDs, with corresponding employees (if any) and bonus details (if any), for all employees. Which FROM clause gives the required result? (Consider the table structures as given)

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)

SQL> desc bonus Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER JOB_ID   VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2)

  1. FROM departments LEFT OUTER JOIN employees USING (department_id ) FULL OUTER JOIN bonus

  2. FULL OUTER JOIN department USING (department_id );

  3. FROM bonus JOIN employees USING (job_id )

  4. FROM employees FULL OUTER JOIN departments FULL OUTER JOIN bonus

Answer: A.

77. Examine the following exhibits:

Table BONUS
  Table DEPARTMENTS
  Table EMPLOYEES
 

You want to generate a report listing the employees’ IDs and their corresponding commissions and departments (if any), if the commissions exist or not. Evaluate the following query:

  SELECT e.employee_id , bonus, department_name FROM bonus b_____________ employees  USING (job_id ) ____________ departments   USING (department_id )  WHERE commission_pct  IS NOT NULL;

Which combination of joins used in the blanks in the above query gives the correct output?

  1. JOIN; LEFT OUTER JOIN

  2. FULL OUTER JOIN; FULL OUTER JOIN

  3. RIGHT OUTER JOIN; LEFT OUTER JOIN

  4. LEFT OUTER JOIN; RIGHT OUTER JOIN

Answer: A.

78.Predict the outcome of the following query.

SELECT e.salary, bonus  FROM employees E JOIN bonus b  USING (salary,job_id );

  1. It executes successfully.

  2. It throws an error because bonus in SELECT is not aliased

  3. It throws an error because the USING clause cannot have more than 1 column.

  4. It executes successfully but the results are not correct.

Answer: D.

View the Exhibit and examine the structure of the EMPLOYEES, DEPARTMENTS, LOCATIONS and BONUS. Answer the questions from 79 and 80 that follow:

Table BONUS
  Table DEPARTMENTS
  Table EMPLOYEES
  Table GRADE
  Table LOCATIONS

79.You need to list all the departments in the city of Zurich. You execute the following query: 

  SELECT D.DEPARTMENT_ID , D.DEPARTMENT_NAME  , L.CITY  FROM departments D JOIN LOCATIONS L  USING (LOC_ID,CITY)   WHERE L.CITY = UPPER(’ZURICH’);

Predict the outcome of the above query.

  1. It executes successfully.

  2. It gives an error because a qualifier is used for CITY in the SELECT statement.

  3. It gives an error because the column names in the SELECT do not match

  4. It gives an error because the USING clause has CITY which is not a matching column.

Answer: D. Only the matching column names should be used in the USING clause.

80.Answer the question that follows the query given below:

   SELECT e.first_name, d.department_id , e.salary, b.bonus  FROM bonus b join employees e  USING (job_id )  JOIN department d  USING (department_id )  WHERE d.loc = ’Zurich’;

 

You need to extract a report which gives the first name, department number, salary and bonuses of the employees of a company named ’ABC’. Which of the following queries will solve the purpose?

  1. SELECT e.first_name, d.department_id , e.salary, b.bonus  FROM bonus b join employees e join departments d  on (b.job_id  = e.job_id )  on (e.department_id =d.department_id )  WHERE d.loc = ’Zurich’;
  2. SELECT e.first_name, d.department_id , e.salary, b.bonus  FROM bonus b join employees e  on (b.job_id  = e.job_id )  JOIN department d  on (e.department_id =d.department_id )  WHERE d.loc = ’Zurich’;
  3. SELECT e.first_name, d.department_id , e.salary, b.bonus  FROM employees e join bonus b  USING (job_id )  JOIN department d  USING (department_id )  WHERE d.loc = ’Zurich’;

  4. None of the above

Answer: C. The query A will throw a syntactical error, query B will throw an invalid identifier error between bonus and department.

Examine the Exhibits given below and answer the questions 81 to 85 that follow.

Table BONUS
  Table DEPARTMENTS
  Table EMPLOYEES

81.\tYou need to find the managers’ name for those employees who earn more than 20000. Which of the following queries will work for getting the required results? 

  1. SELECT e.employee_id  "Employee", salary, employee_id ,   FROM employees E JOIN employees M  USING (e.manager_id  = m.employee_id )  WHERE e.salary>20000;
  2. SELECT e.employee_id  "Employee", salary, employee_id ,   FROM employees E JOIN employees M  USING (e.manager_id)  WHERE e.salary>20000;
  3. SELECT e.employee_id  "Employee", salary, employee_id ,   FROM employees E  NATURAL JOIN employees M  USING (e.manager_id = m.employee_id )  WHERE e.salary>20000;
  4. SELECT e.employee_id  "Employee", salary, employee_id ,   FROM employees E JOIN employees M  ON (e.manager_id = m.employee_id )  WHERE e.salary>20000;

Answer: D.

82.You issue the following query:

  SELECT e.employee_id ,d.department_id   FROM employees e NATURAL JOIN department d NATURAL JOIN bonus b  WHERE department_id  =100;

Which statement is true regarding the outcome of this query?

  1. It executes successfully.

  2. It produces an error because the NATURAL join can be used only with two tables.

  3. It produces an error because a column used in the NATURAL join cannot have a qualifier.

  4. It produces an error because all columns used in the NATURAL join should have a qualifier.

Answer: C.

83.You want to display all the employee names and their corresponding manager names. Evaluate the following query:

  SELECT e.first_name "EMP NAME", m.employee_name "MGR NAME"  FROM employees e ______________ employees m  ON e.manager_id = m.employee_id ;

Which JOIN option can be used in the blank in the above query to get the required output?

  1. Simple inner JOIN

  2. FULL OUTER JOIN

  3. LEFT OUTER JOIN

  4. RIGHT OUTER JOIN

Answer: C. A left outer join includes all records from the table listed on the left side of the join, even if no match is found with the other table in the join operation.

Consider the below exhibit and following query to answer questions 84 and 85. (Assume the table department has manager_id and department_name as its columns)

Table DEPARTMENTS 

   Select *  FROM employees e JOIN department d  ON (e.employee_id  = d.manager_id);

84. You need to display a sentence "(first_name) (last_name) is manager of the (department_name) department". Which of the following SELECT statements will successfully replace ’*’ in the above query to fulfill this requirement? 

  1. SELECT e.first_name||’ ’||e.last_name||’ is manager of the ’||d.department_name||’ department.’ "Managers"
  2. SELECT e.first_name, e.last_name||’ is manager of the ’||d.department_name||’ department.’ "Managers"
  3. SELECT e.last_name||’ is manager of the ’||d.department_name||’ department.’ "Managers"

  4. None of the above

Answer: A.

85.What will happen if we omit writing the braces "( )" after the ON clause in the above query?

  1. It will give only the names of the employees and the managers’ names will be excluded from the result set

  2. It will give the same result as with braces "( )"

  3. It will give an ORA error a s it is mandatory to write the braces "()" when using the JOIN..ON clause

  4. None of the above

Answer: B. The braces are not mandatory, but using them provides a clear readability of the conditions within it.

86. Which of the following queries creates a Cartesian join?

  1. SELECT title, authorid FROM books, bookauthor;
  2. SELECT title, name FROM books CROSS JOIN publisher;
  3. SELECT title, gift FROM books NATURAL JOIN promotion;

  4. all of the above

Answer: A, B. A Cartesian join between two tables returns every possible combination of rows from the tables. A Cartesian join can be produced by not including a join operation in the query or by using a CROSS JOIN.

87. Which of the following operators is not allowed in an outer join?

  1. AND

  2. =

  3. OR

  4. >

Answer: C. Oracle raises the exception "ORA-01719: outer join operator (+) not allowed in operand of OR or IN"

88. Which of the following queries contains an equality join?

  1. SELECT title, authorid FROM books, bookauthor WHERE books.isbn = bookauthor.isbn AND retail> 20;
  2. SELECT title, name FROM books CROSS JOIN publisher;
  3. SELECT title, gift FROM books, promotion WHERE retail>= minretail AND retail <= maxretail;

  4. None of the above

Answer: A. An equality join is created when data joining records from two different tables is an exact match (that is, an equality condition creates the relationship).

89. Which of the following queries contains a non-equality join?

  1. SELECT title, authorid FROM books, bookauthor WHERE books.isbn = bookauthor.isbn AND retail> 20;
  2. SELECT title, name FROM books JOIN publisher USING (pubid);
  3. SELECT title, gift FROM books, promotion WHERE retail>= minretail AND retail <= maxretail;

  4. None of the above

Answer: D. Nonequijoins match column values from different tables based on an inequality expression. The value of the join column in each row in the source table is compared to the corresponding values in the target table. A match is found if the expression used in the join, based on an inequality operator, evaluates to true. When such a join is constructed, a nonequijoin is performed.A nonequijoin is specified using the JOIN..ON syntax, but the join condition contains an inequality operator instead of an equal sign.

90. The following SQL statement contains which type of join?

  SELECT title, order#, quantity  FROM books FULL OUTER JOIN orderitems  ON books.isbn = orderitems.isbn;

  1. equality

  2. self-join

  3. non-equality

  4. outer join

Answer: D. A full outer join includes all records from both tables, even if no corresponding record in the other table is found.

91. Which of the following queries is valid?

  1. SELECT b.title, b.retail, o.quantity FROM books b NATURAL JOIN orders od NATURAL JOIN orderitems o WHERE od.order# = 1005;

  2. SELECT b.title, b.retail, o.quantity FROM books b, orders od, orderitems o WHERE orders.order# = orderitems.order# AND orderitems.isbn=books.isbn AND od.order#=1005;
  3. SELECT b.title, b.retail, o.quantity FROM books b, orderitems o WHERE o.isbn = b.isbn AND o.order#=1005;

  4. None of the above

Answer: C. If tables in the joins have alias, the selected columns must be referred with the alias and not with the actual table names.

92. Given the following query.

  SELECT zip, order#  FROM customers NATURAL JOIN orders;

Which of the following queries is equivalent?

  1. SELECT zip, order# FROM customers JOIN orders WHERE customers.customer# = orders.customer#;
  2. SELECT zip, order# FROM customers, orders WHERE customers.customer# = orders.customer#;
  3. SELECT zip, order# FROM customers, orders WHERE customers.customer# = orders.customer# (+);

  4. none of the above

Answer: B. Natural join instructs Oracle to identify columns with identical names between the source and target tables.

93. Examine the table structures as given. Which line in the following SQL statement raises an error? 

SQL> DESC employees Name Null?  Type ----------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME  VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER  VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID  NOT NULL VARCHAR2(10) SALARY   NUMBER(8,2) COMMISSION_PCT   NUMBER(2,2) MANAGER_ID  NUMBER(6) DEPARTMENT_ID  NUMBER(4)
SQL> DESC departments Name Null?  Type ----------------------- -------- ---------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID  NUMBER(6) LOCATION_ID  NUMBER(4)

  1. SELECT e.first_name, d.department_name  2. FROM employees  e, department d  3. WHERE e.department_id=d.department_id

  1. line 1

  2. line 2

  3. line 3

  4. No errors

Answer: A. If a query uses alias names in the join condition, their column should use the alias for reference.

94. Given the following query:

  SELECT lastname, firstname, order#  FROM customers LEFT OUTER JOIN orders  USING (customer#)  ORDER BY customer#;

Which of the following queries returns the same results?

  1. SELECT lastname, firstname, order# FROM customers c OUTER JOIN orders o ON c.customer# = o.customer# ORDER BY c.customer#;
  2. SELECT lastname, firstname, order# FROM orders o RIGHT OUTER JOIN customers c ON c.customer# = o.customer# ORDER BY c.customer#;

  3. SELECT lastname, firstname, order# FROM customers c, orders o WHERE c.customer# = o.customer# (+) ORDER BY c.customer#;

  4. None of the above

Answer: B, C.

95. Which of the below statements are true?

  1. Group functions cannot be used against the data from multiple data sources.

  2. If multiple tables joined in a query, contain identical columns, Oracle selects only one of them.

  3. Natural join is used to join rows from two tables based on identical columns.

  4. A and B

Answer: C. Group functions can be used on a query using Oracle joins. Ambiguous columns must be referenced using a qualifier.

96. Which line in the following SQL statement raises an error?

  1. SELECT name, title  2. FROM books JOIN publisher  3. WHERE books.pubid = publisher.pubid  4. AND  5. cost<45.95

  1. line 1

  2. line 2

  3. line 3

  4. line 4

Answer: C. Since the tables are joined using JOIN keyword, the equality condition should be written with the USING clause and not WHERE clause.

97. Given the following query:

  SELECT title, gift  FROM books CROSS JOIN promotion;

Which of the following queries is equivalent?

  1. SELECT title, gift  FROM books NATURAL JOIN promotion;
  2. SELECT title  FROM books INTERSECT  SELECT gift  FROM promotion;
  3. SELECT title  FROM books UNION ALL  SELECT gift  FROM promotion;
  4. SELECT title, gift  FROM books, promotion;

Answer: D. Cartesian joins are same as Cross joins.

98. If the CUSTOMERS table contains seven records and the ORDERS table has eight records, how many records does the following query produce?

  SELECT *  FROM customers CROSS JOIN orders;

  1. 0

  2. 56

  3. 7

  4. 15

Answer: B. Cross join is the cross product of rows contained in the two tables.

99. Which of the following SQL statements is not valid?

  1. SELECT b.isbn, p.name  FROM books b NATURAL JOIN publisher p;
  2. SELECT isbn, name  FROM books b, publisher p  WHERE b.pubid = p.pubid;
  3. SELECT isbn, name  FROM books b JOIN publisher p  ON b.pubid = p.pubid;
  4. SELECT isbn, name  FROM books JOIN publisher  USING (pubid);

Answer: A. Ambiguous columns must be referred with the table qualifiers.

100. Which of the following lists all books published by the publisher named ’Printing Is Us’?

  1. SELECT title  FROM books NATURAL JOIN publisher  WHERE name = ’PRINTING IS US’;
  2. SELECT title  FROM books, publisher  WHERE pubname = 1;
  3. SELECT *  FROM books b, publisher p  JOIN tables ON b.pubid = p.pubid  WHERE name = ’PRINTING IS US’;

  4. none of the above

Answer: A. Assuming that the column NAME is not contained in BOOKS table, query A is valid.

101. Which of the following SQL statements is not valid?

  1. SELECT isbn  FROM books  MINUS  SELECT isbn  FROM orderitems;
  2. SELECT isbn, name  FROM books, publisher  WHERE books.pubid (+) = publisher.pubid (+);
  3. SELECT title, name  FROM books NATURAL JOIN publisher

  4. All the above SQL statements are valid.

Answer: B. The query B raises an exception "ORA-01468: a predicate may reference only one outer-joined table".

102. Which of the following statements about an outer join between two tables is true?

  1. If the relationship between the tables is established with a WHERE clause, both tables can include the outer join operator.

  2. To include unmatched records in the results, the record is paired with a NULL record in the deficient table.

  3. The RIGHT, LEFT, and FULL keywords are equivalent.

  4. all of the above

Answer: B.

103. Which line in the following SQL statement raises an error?

  1. SELECT name, title  2. FROM books b, publisher p  3. WHERE books.pubid = publisher.pubid  4. AND  5. (retail> 25 OR retail-cost> 18.95);

  1. line 1

  2. line 3

  3. line 4

  4. line 5

Answer: B. Since the tables used in the query have a qualifier, the columns must be referred using the same.

104. What is the maximum number of characters allowed in a table alias?

  1. 10

  2. 155

  3. 255

  4. 30

Answer: D. The table alias can be maximum of 30 characters.

105. Which of the following SQL statements is valid?

  1. SELECT books.title, orderitems.quantity  FROM books b, orderitems o  WHERE b.isbn= o.ibsn;
  2. SELECT title, quantity  FROM books b JOIN orderitems o;
  3. SELECT books.title, orderitems.quantity  FROM books JOIN orderitems  ON books.isbn = orderitems.isbn;

  4. none of the above

Answer: C.