1.以下哪项与关系数据库无关？
选择
投影
加入
以上都不是
答案:D.选项A，B和C是Oracle关系数据库的主要功能.
2.以下哪种方法用于编写包含多个列的查询表？
SELECT
GROUP BY
ORDER BY
JOINS
答案:D.连接用于连接Oracle中多个表的多个表和项目列数据.
3.以下哪种是最常用的加入多个方法之一桌子？
哈希加入
Equijoin
自我加入
交叉加入
答案:B. Equijoin是最常见和最简单的技术之一加入多个表格. Equijoins也称为简单连接或内连接.Equijoin涉及主键和外键.
4.如果列值，将使用以下哪些内容与其他表连接行落在由不平等运营商定义的范围内？
Equijoin
简单加入
非等值连接
以上都不是
答案:C. Equijoins使用相等运算符来连接行，非等值连接使用不等式运算符.
5.以下哪些语句关于Oracle连接是否属实？
结果集中包含NULL值
仅提取具有匹配条件的行
提取任何一个表中存在的所有行
以上都不是
答案:B.当joi时，不包括NULL值和公共连接列中的不同条目使用ns.
6.以下哪项可以用来将表的行与同一个表的其他行连接起来？
Equijoin
Non-equijoin
外联接
自我加入
答案:D.该关联基于在具有逻辑和通常层次关系的列上.
7.在Oracle DB中两个表的笛卡尔连接有什么用？
必须避免，因为它成本高且未经优化
它是在一个表中的每一行都与第二个表中的所有行相连接
A和B
以上都不是
答案:B.笛卡尔联接通常是缺少或不充分的连接条件的结果.它只是两个表的交叉乘积.
8.以下哪项是Oracle中基本的连接类型之一DB？
外部联接
自我加入
Equi-join
以上所有
答案:C. Equi-join和non-equijoin是Oracle DB中两种基本类型的连接.
9.使用的主要条件是什么连接Oracle DB中的源表和目标表以获取非笛卡尔积的结果？
没有条件
两个表中至少有一列应该是通用的.
两者中列的名称使用连接的连接表应该相同
以上都不是
答案:B .表必须通过一个与两个实体相关的公共列连接.在一个公共列上连接的表生成非笛卡尔乘积.
10.以下哪项可用于从单个SQL查询中的多个表中获取行？
SELECT
WHERE
FROM
Equi-joins
答案:D. Equijoins也称为简单连接或内连接. Equijoin涉及主键和外键.
11.关于Oracle连接的源表和目标表有什么用？
他们必须至少有一个同名的列
所有列都应该是用于连接两个表的相同名称和相同数据类型
源表和目标表不能交换且位置特定
以上都不是
答案:D.源表和目标表可以交换，不会固定在它们的位置.取决于查询中使用的连接类型，结果可能不同或保持不变.
12. Oracle DB中的自然连接是什么？
源表和目标表的列名应该相同
如果源列表和目标表的列名不相同，Oracle隐含地需要
NATURAL JOINS，USING和ON是与Natural Joins相关联的关键字
以上所有
答案: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 ='培根';
没有区别
结果在两种情况下都不同
这两个查询都会在执行时出现ORA错误
以上都不是
答案:B.使用NATURAL JOIN时，可以交换源表和目标表，从而提供相关的不同结果集.
14.关于Oracle DB中的NATURAL JOIN，以下哪个选项是正确的？
使用NATURAL JOIN时，提及两个表中所有列的名称是必需的
只有两个表的所有列的名称都可以使用NATURAL JOIN是相同的
仅当用户指定源表和目标表的列时，才会发生NATURAL JOIN中的连接.
没有必要在使用NATURAL JOINS时提及列.
答案:D.有一个n当使用NATURAL JOIN时，隐式连接源表和目标表中的列. NATURAL JOIN是一个JOIN操作，它根据要连接的两个表中的公共列为您创建隐式连接子句.常用列是两个表中具有相同名称的列.
15. NATURAL JOIN与JOIN..ON的连接有什么区别？
两者之间没有区别
JOIN..ON加入具有相同名称的特定列上的源表和目标表
NATURAL JOIN隐式连接源表和目标表中的所有匹配列
以上都不是
答案:B，C.自然连接的连接条件基本上是所有具有相同名称的列的等值连接.使用ON子句指定任意条件或指定要连接的列.连接条件是分开的从其他搜索条件. ON子句使代码易于理解.
16. Oracle DB中的JOIN..ON子句有什么用？
它不依赖于具有相同名称的源表和目标表中的列
仅限那些来自源表和具有相同名称的目标表的列可以与此子句一起使用
它是NATURAL JOIN的格式
全部上面的
答案:A，C.自然连接的连接条件基本上是所有列的等值连接.一样的名字.使用ON子句指定任意条件或指定要连接的列.连接条件与其他搜索条件分开. ON子句使代码易于理解.
17.数据库设计者以不同的方式命名两个表中的键(唯一)列.在加入这些表时，以下哪个是最佳实践？
JOIN..ON
NATURAL JOIN或JOIN ... ON子句
两者A和B
以上都不是
答案:A.使用NATURAL在这种情况下，JOINS会产生意外结果，因为隐式搜索具有相同名称的列，在这种情况下不存在.
18.以下哪些内容可以使用在Oracle DB中获取不匹配的行以及源表和目标表之间的匹配行？
EQUI-JOIN
SELF-JOIN
NATURAL JOIN
OUTER-JOIN
答案:D.当记录需要包含在结果中而没有连接中的相应记录时，会创建外部联接表.这些记录与NULL记录匹配，以便它们包含在输出中.
19.什么是笛卡尔连接，也称为Oracle DB？
Equi-join
反加入
交叉加入
以上都不是
答案: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;
两个表中具有相同名称的公共列DEPARTMENT_ID将在结果集中出现两次
所有具有相同名称的列与NATURAL JOIN连接，将出现两次结果集
结果集对于来自两个表的每对具有相同名称的列只有一列
以上都不是
答案:C. NATURAL JOIN关键字不需要条件来建立两个表之间的关系.但是，必须存在公共列.列限定符不能与NATURAL JOIN关键字一起使用.
21.在Oracle DB中，NATURAL JOIN和EQUI-JOIN之间有什么区别？
没有区别
它们是相同的从两者获得的结果集
A和B两者
以上都不是
答案:D. NATURAL JOIN连接具有相同名称的所有列，而EQUI-JOIN要求在SQL查询中明确提及列.
22. Oracle DB中的内部联接是什么？
在两个表之间给出匹配记录的连接称为INNER JOIN
内连接可以使用
等运算符
两者A和B
以上都不是
答案:C. A join可以是内连接，其中返回的唯一记录在al中具有匹配的记录l表或外连接，无论连接中是否有匹配的记录，都可以返回记录.
23. INNER JOIN和INNER JOIN之间有什么区别Oracle DB中的EQUI-JOIN？
它们在语法和结果方面是相同的得到的集合.
INNER JOIN是EQUI-JOIN的子集
INNER JOIN可以使用像
以上所有
答案:C. EQUI-JOIN是一种在连接条件下包含"="运算符的INNER JOIN，而INNER JOIN既包含相等也包含非相等运算符
24.根据ANSI SQL中的NATURAL JOINS，在Oracle DB中的1999语法是什么？
使用等式运算符(=)
与传统语法相比，它们会获取不同的结果
ANSI SQL语法在SQL查询中使用NATURAL JOIN等词语
以上都不是.
答案: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;
两个表的department_id之间有一个外连接，相当于ANSI SQL中的右外连接
两个表的department_id之间有一个外连接，相当于ANSI SQL中的左外连接
它从employees表中获取department_id的所有记录，无论它们是否匹配
它从department表中获取department_id的所有记录，无论它们是否匹配
答案:A，D.条件e.department_id(+)= d.department_id表示它将执行Right Outer Join和all将显示department表中的department_id是否匹配
26.以下语法模型中的哪些广泛用于全球软件系统？
ANSI SQL:1999
两种传统的Oracle语法ax和ANSI SQL:1999语法
传统的Oracle语法
所有选项
答案:C. ANSI SQL:1999语法虽然没有像传统的Oracle语法那样使用，但它仍然是可以在Oracle SQL中使用的语法之一
27.关于Oracle DB中的笛卡尔积，以下是什么？
如果'N'是加入的表的no，那么如果没有.连接是N-1，笛卡尔积不执行
如果'N'是加入的表的数量，那么如果没有.连接是N，执行笛卡尔积
如果'N'是连接的表的数量，那么如果没有.连接是N + 1，执行笛卡尔积
如果'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;
表中没有使用别名.
这里没有使用RIGHT OUTER JOIN这个词因此它会抛出错误
(+)应该在相等条件的右侧，而不是在左侧
表别名应该与条件中的department_id一起使用以删除含糊不清的命名
答案: D. 如果没有表别名，Oracle无法派生所连接列的原点，因此会在执行时抛出Ambiguity错误.
29.Which of以下用于避免Oracle DB中的模糊列问题？
;
，
.
/
答案: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;
它提供了不在任何部门的员工的详细信息
它提供了那些没有任何员工的部门的详细信息
它提供了所有部门的详细信息，无论他们是否有任何员工
它提供了公司'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;
它将给出两个具有相同值的表中的所有匹配值department_id
它将提供表员工的所有列，只显示departments表中的前100行
它会产生ORA错误: "b.department_id"无效标识符
以上所有选项
答案:C.在FROM子句中声明的WHERE子句中应该使用相同的别名
32.关于表连接中的USING和ON子句，以下哪些是正确的？ (如果适用，请选择多个选项)
ON子句可用于连接表在具有相同数据类型但不一定是相同名称的列上
USING和ON子句仅用于equijoins和非等值连接
不ON子句可以使用多个条件
可以在USING..ON子句之后写入WHERE子句以应用其他条件
答案:A，D. JOIN ... USING方法类似于NATURAL JOIN方法，除了在USING子句中指定了公共列. USING子句中不能包含条件以指示表是如何相关的.此外，列限定符不能用于USING子句中指定的公共列. JOIN ... ON方法根据指定的条件连接表. FROM子句中的JOIN关键字指示要连接的表，ON子句指示两个表的关联方式.如果要连接的表没有每个表中具有相同名称的公共列，则必须使用此方法.
33.使用以下表可以连接多少个表加入Oracle DB？
1
2
255
无限制
答案:D .目前参与加入的表数没有限制.
34.在SQL语句中使用多个连接时是什么情况？
连接的评估来自从左到右
从右到左评估联接
评估联接的过程中没有优先级
以上都不是
答案: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;
使用NATURAL JOIN连接多个表是更好的选择和收益准确的结果
当使用NATURAL JOIN连接多个表时，错误概率非常小
条款USING..JOIN..ON给出更多在连接多个表时，结果比NATURAL JOIN更准确
当使用NATURAL JOIN时，Oracle隐式连接多个表，因此使用NATURAL JOINS是一个好习惯
答案:C.使用NATURAL JOINS可以创建行的笛卡尔积，并且使用不可靠的结果集也容易出错.
36. Oracle DB中的JOIN..ON条款有什么用？
与连接多个表时的NATURAL JOINS相比，它们不是很可靠
JOIN..ON子句类似于WHERE子句，它限制具有条件的行
另外一个使用JOIN..ON子句时必须使用WHERE子句
以上都不是
答案: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.关于上面给出的查询是什么情况？
It gives an ORA error as the mandatory WHERE clause is not present
The JOIN..ON clause can't contain more than one condition
The query ignores the last condition and executes without an ORA error
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?
When the NATURAL JOIN is used, a WHERE clause is mandatory, omitting which gives an error
The ON clause should be replaced with the USING clause
The words NATURAL, JOIN and USING are mutually exclusively in the context of the same join clause
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?
They join based on the keyword NON-EQUI JOIN
They are used using the JOIN..ON clause with "=" sign
The results are obtained when the result of the inequality mentioned evaluates to true.
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);
It will throw an ORA error as the condition in the ON clause is incorrect.
It will throw an ORA error due to a syntax error as there is no Equality sign "=" in the ON clause
It will execute successfully and give the first name, last name and employee ID of employees with the condition mentioned.
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?
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’);
SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary>=20000);
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’;
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?
The result of the inequality match operation is true
The result of the inequality match operation is 0
The result of the inequality match operation is 1
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?
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’);
SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary>=20000);
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’;
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?
The result of the inequality match operation is true
The result of the inequality match operation is 0
The result of the inequality match operation is 1
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?
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’);
SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary>=20000);
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’;
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?
The result of the inequality match operation is true
The result of the inequality match operation is 0
The result of the inequality match operation is 1
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?
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’);
SELECT e.first_name, e.last_name FROM employees e JOIN departments d ON (e.department_id = d.department_id and e.salary>=20000);
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’;
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?
The result of the inequality match operation is true
The result of the inequality match operation is 0
The result of the inequality match operation is 1
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?
Only two tables are required for the join to work
The columns in the result set are obtained from two tables but are displayed in one table
Conceptually, the source table duplicates itself to create the target table. (Oracle doesn’t duplicate tables)
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?
a is source and b is target
b is source and a is target
Oracle doesn’t treat any of the tables as source or target
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?
When we need to find the duplicate records in a table
When we need to obtain NULL values from a table
When we need to display a column of a table twice
When we need to display hierarchy of relevant entities
Answer: D.
46. What is true about NATURAL JOINS in Oracle DB?
The joined columns have the same name but can have different data types
The joined columns can have the same data type but different names
The joined columns should have identical names and the same data type
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?
SELECT department_id , department_name ,location, city FROM departments NATURAL JOIN locations WHERE department_id in (100,101);
SELECT department_id , department_name ,location, city FROM locations NATURAL JOIN departments WHERE department_id BETWEEN 100 AND 101;
SELECT department_id , department_name ,location, city FROM departments NATURAL JOIN locations WHERE department_id>100 AND department_id>101;
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?
When the names of the columns from the tables are identical then use USING clause
When the data types of the columns from the tables are identical then use NATURAL JOINS
If several columns have the same names but the data types do not match, USING can be used
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%’);
It gives the names and bonus obtained by all the employees in some company
It gives the names and bonus obtained by all the employees in a particular job title in a company
It executes successfully giving all the names and the bonus obtained by all the employees in all jobs
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?
INNER JOIN returns only the rows that are matched
OUTER JOIN returns only the rows that are not matched
OUTER JOIN returns the rows that are matched as well as those which do not match
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?
When both LEFT OUTER JOIN and RIGHT OUTER JOIN appear in the same query,it is called a FULL OUTER JOIN
A FULL OUTER JOIN is the same as an OUTER JOIN
Both A and B
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?
A JOIN..USING can happen only between two tables at a time
USING clause in the query doesn’t have any column from the department
There is no WHERE clause in the query
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?
SELECT * FROM employees e, department d WHERE e.department_id = d.department_id ;
SELECT * FROM employees e NATURAL JOIN department d;
SELECT * FROM employees e FULL OUTER JOIN department d ON e.department_id = d.department_id ;
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?
Cartesian products
Natural joins
Full OUTER join
Equijoins
Answer: D.
55.Which of the following is not a format for Outer Joins in Oracle DB?
Right
Left
Centre
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?
ON (e.department_id = d.department_id ) should be added
USING (e.department_id ) should be added
WHERE e.department_id in (100,101) should be added
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?
ON (e.department_id = d.department_id )
WHERE salary> 20000;
USING (e.department_id )
WHERE salary> 20000;
USING (department_id )
WHERE salary>20000;
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?
ON (department_id = 100);
USING (e.department_id =100);
WHERE d.department_id = 100;
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)
SELECT a.first_name || ’ ’||a.last_name "Manager", b.first_name||’ ’||b.last_name "Employees" FROM employees a join employees b On (employee_id );
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 );
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);
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)
SELECT * FROM employees e NATURAL JOIN department d;
SELECT * FROM employees e JOIN department d;
SELECT * FROM employees e NATURAL JOIN department d USING (e.department_id );
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 ;
It will display all the bonuses obtained by all the employees.
It will display NULL for the bonus column if a particular employee has not received any bonus
Both A and B
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?
SELECT department_id , department_name FROM departments d NATURAL JOIN employees e;
SELECT department_id , department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id );
SELECT department_id , department_name FROM employees e LEFT OUTER JOIN departments d USING (department_id );
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?
SELECT nvl(department_id ,’No department yet’) FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id );
SELECT nvl(department_id ,’No department yet’) FROM departments d LEFT OUTER JOIN employees e ON (e.department_id = d.department_id );
SELECT nvl(department_id ,’No department yet’) FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id );
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?
SELECT department_id , department_name FROM departments d NATURAL JOIN locations l;
SELECT department_id , department_name FROM departments d FULL OUTTER JOIN locations l ON (d.location_id = l.location_id);
SELECT d.department_id , d.department_name FROM departments d JOIN locations l USING (location_id);
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?
If the joined tables’ columns have NULL values
If the joined tables have NOT NULL columns
If the joined tables have only un-matched data
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
SELECT employee_id , salary, grade FROM employees e JOIN grade g ON g.grade BETWEEN g.losal AND g.hisal
SELECT employee_id , salary, grade FROM employees e FULL OUTER JOIN grade g WHERE g.grade> g.losal AND<g.hisal;
SELECT employee_id , salary, grade FROM employees e JOIN grade g ON (MIN(g.grade) = g.losal AND MAX(g.grade) = g.hisal);
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?
The value displayed in the calc_val column will be lesser.
The value displayed in the calc_val column will be higher.
There will be no difference in the calc_val column.
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?
To find the grade for each of the employees
To list the name, job_id, and manager name for all the employees
To find the department name of employees.
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.)
You want to create a non-equijoin.
The tables to be joined have multiple NULL columns.
The tables to be joined have columns of the same name and different data types.
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)
SELECT * FROM employees E JOIN bonus B on (salary, job_id )
SELECT * FROM employees E NATURAL JOIN bonus B on (salary, job_id )
SELECT * FROM employees E JOIN bonus B USING (salary, job_id )
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?
Only query 1 executes successfully and gives the required result.
Only query 2 executes successfully and gives the required result.
Both queries 1 and 2 execute successfully and give different results.
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.
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?
It executes successfully and gives the required result.
It executes successfully but does not give the required result.
It produces an error because the join order of the tables is incorrect.
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:
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?
self-join
full outer-join with self-join
left outer-join with self-join
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?
SELECT d.department_id , d.department_name FROM departments d JOIN locations l ON (d.location_id = l.location_id);
SELECT d.department_id , d.department_name FROM departments d RIGHT OUTER JOIN locations l ON (d.location_id = l.location_id);
SELECT d.department_id , d.department_name FROM departments d FULL JOIN locations l ON (d.location_id = l.location_id);
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?
JOIN
NATURAL JOIN
LEFT OUTER JOIN
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)
FROM departments LEFT OUTER JOIN employees USING (department_id ) FULL OUTER JOIN bonus
FULL OUTER JOIN department USING (department_id );
FROM bonus JOIN employees USING (job_id )
FROM employees FULL OUTER JOIN departments FULL OUTER JOIN bonus
Answer: A.
77. Examine the following exhibits:
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?
JOIN; LEFT OUTER JOIN
FULL OUTER JOIN; FULL OUTER JOIN
RIGHT OUTER JOIN; LEFT OUTER JOIN
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 );
It executes successfully.
It throws an error because bonus in SELECT is not aliased
It throws an error because the USING clause cannot have more than 1 column.
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:
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.
It executes successfully.
It gives an error because a qualifier is used for CITY in the SELECT statement.
It gives an error because the column names in the SELECT do not match
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?
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’;
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’;
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’;
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.
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?
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;
SELECT e.employee_id "Employee", salary, employee_id , FROM employees E JOIN employees M USING (e.manager_id) WHERE e.salary>20000;
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;
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?
It executes successfully.
It produces an error because the NATURAL join can be used only with two tables.
It produces an error because a column used in the NATURAL join cannot have a qualifier.
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?
Simple inner JOIN
FULL OUTER JOIN
LEFT OUTER JOIN
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)
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?
SELECT e.first_name||’ ’||e.last_name||’ is manager of the ’||d.department_name||’ department.’ "Managers"
SELECT e.first_name, e.last_name||’ is manager of the ’||d.department_name||’ department.’ "Managers"
SELECT e.last_name||’ is manager of the ’||d.department_name||’ department.’ "Managers"
None of the above
Answer: A.
85.What will happen if we omit writing the braces "( )" after the ON clause in the above query?
It will give only the names of the employees and the managers’ names will be excluded from the result set
It will give the same result as with braces "( )"
It will give an ORA error a s it is mandatory to write the braces "()" when using the JOIN..ON clause
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?
SELECT title, authorid FROM books, bookauthor;
SELECT title, name FROM books CROSS JOIN publisher;
SELECT title, gift FROM books NATURAL JOIN promotion;
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?
AND
=
OR
>
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?
SELECT title, authorid FROM books, bookauthor WHERE books.isbn = bookauthor.isbn AND retail> 20;
SELECT title, name FROM books CROSS JOIN publisher;
SELECT title, gift FROM books, promotion WHERE retail>= minretail AND retail <= maxretail;
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?
SELECT title, authorid FROM books, bookauthor WHERE books.isbn = bookauthor.isbn AND retail> 20;
SELECT title, name FROM books JOIN publisher USING (pubid);
SELECT title, gift FROM books, promotion WHERE retail>= minretail AND retail <= maxretail;
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;
equality
self-join
non-equality
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?
SELECT b.title, b.retail, o.quantity FROM books b NATURAL JOIN orders od NATURAL JOIN orderitems o WHERE od.order# = 1005;
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;
SELECT b.title, b.retail, o.quantity FROM books b, orderitems o WHERE o.isbn = b.isbn AND o.order#=1005;
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?
SELECT zip, order# FROM customers JOIN orders WHERE customers.customer# = orders.customer#;
SELECT zip, order# FROM customers, orders WHERE customers.customer# = orders.customer#;
SELECT zip, order# FROM customers, orders WHERE customers.customer# = orders.customer# (+);
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
line 1
line 2
line 3
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?
SELECT lastname, firstname, order# FROM customers c OUTER JOIN orders o ON c.customer# = o.customer# ORDER BY c.customer#;
SELECT lastname, firstname, order# FROM orders o RIGHT OUTER JOIN customers c ON c.customer# = o.customer# ORDER BY c.customer#;
SELECT lastname, firstname, order# FROM customers c, orders o WHERE c.customer# = o.customer# (+) ORDER BY c.customer#;
None of the above
Answer: B, C.
95. Which of the below statements are true?
Group functions cannot be used against the data from multiple data sources.
If multiple tables joined in a query, contain identical columns, Oracle selects only one of them.
Natural join is used to join rows from two tables based on identical columns.
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
line 1
line 2
line 3
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?
SELECT title, gift FROM books NATURAL JOIN promotion;
SELECT title FROM books INTERSECT SELECT gift FROM promotion;
SELECT title FROM books UNION ALL SELECT gift FROM promotion;
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;
0
56
7
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?
SELECT b.isbn, p.name FROM books b NATURAL JOIN publisher p;
SELECT isbn, name FROM books b, publisher p WHERE b.pubid = p.pubid;
SELECT isbn, name FROM books b JOIN publisher p ON b.pubid = p.pubid;
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’?
SELECT title FROM books NATURAL JOIN publisher WHERE name = ’PRINTING IS US’;
SELECT title FROM books, publisher WHERE pubname = 1;
SELECT * FROM books b, publisher p JOIN tables ON b.pubid = p.pubid WHERE name = ’PRINTING IS US’;
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?
SELECT isbn FROM books MINUS SELECT isbn FROM orderitems;
SELECT isbn, name FROM books, publisher WHERE books.pubid (+) = publisher.pubid (+);
SELECT title, name FROM books NATURAL JOIN publisher
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?
If the relationship between the tables is established with a WHERE clause, both tables can include the outer join operator.
To include unmatched records in the results, the record is paired with a NULL record in the deficient table.
The RIGHT, LEFT, and FULL keywords are equivalent.
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);
line 1
line 3
line 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?
10
155
255
30
Answer: D. The table alias can be maximum of 30 characters.
105. Which of the following SQL statements is valid?
SELECT books.title, orderitems.quantity FROM books b, orderitems o WHERE b.isbn= o.ibsn;
SELECT title, quantity FROM books b JOIN orderitems o;
SELECT books.title, orderitems.quantity FROM books JOIN orderitems ON books.isbn = orderitems.isbn;
none of the above
Answer: C.
