Sql Certificate 简明教程

SQL - Get Data from Multiple Tables Questions

1. 以下哪些内容与关系数据库无关？

*答案：D。*选项 A、B 和 C 是 Oracle 关系数据库的主要功能。

2.Which of the following methods is used for writing a query with columns from multiple tables?

*答案：D。*联结用于连接多张表，并从 Oracle 中的多张表投影列数据。

  • 3. 以下哪种方法是联结多张表的常见方法之一？*

*答案：B。*等值连接是最常见的将多张表联结起来的联结类型之一，也是最简单的一种方法。等值连接也称为简单连接或内部连接。等值连接涉及主键和外键。

  • 4. 如果列值落在使用不等式运算符定义的范围内，以下哪种方法将用于将行与其他表联结？*

*答案：C。*等值连接使用等式运算符来联结行，非等值连接使用不等式运算符。

5.Which of the following statements is true about Oracle joins?

*答案：B。*使用联结时，将排除公共联结列中的 NULL 值和不同的条目。

6.Which of the following can be used to join the rows of a table with other rows of the same table?

*答案：D。*该关联基于在逻辑上彼此通常具有层次关系的列。

7.What is true about a cartesian join of two tables in Oracle DB?

*答案：B。*笛卡尔连接通常是由于缺失或连接条件不足所导致。它只是两个表的笛卡尔积。

8.Which of the following is one of the basic types of joins in Oracle DB ?

*答案：C。*等值连接和非等值连接是 Oracle 数据库中连接的两种基本类型。

9.What is the main condition for using joins between a source table and a target table in Oracle DB for getting a non-cartesian product result?

*答案：B。*这两个表必须通过一个公共列来连接，连接这两个实体。连接在公共列上的表将生成非笛卡尔积。

10. Which of the following can be used to fetch rows from multiple tables in a single SQL query?

*答案：D。*等值连接也称为简单连接或内连接。等值连接包含主键和外键。

11.What is true about the source table and the target table in terms of Oracle Joins?

*答案：D。*源表和目标表可以互换，并且不固定在其位置上。根据查询中使用的连接类型，结果可能不同或保持相同。

12.What is true about Natural joins in Oracle DB?

*答案：C。*关键字 NATURAL JOIN 指示 Oracle 找出源表和目标表中具有相同名称的列。自然连接使用所有具有匹配名称和数据类型的列来连接表。USING 子句可用于指定仅那些应该用于等值连接的列。

13.Assume the tables EMPLOYEES and DEPARTMENT have to be joined using NATURAL JOIN. What is the difference between the following two queries which follow? (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 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 = 'Bacon';

*答案：B。*使用 NATURAL JOIN 时，源表和目标表可以互换，从而产生关系不同的结果集。

14.Which of the following options is true regarding the NATURAL JOIN in Oracle DB?

*答案：D。*使用 NATURAL JOIN 时，隐式地连接源表和目标表中的列。NATURAL JOIN 是一种连接操作，它根据两个要连接表中的通用列为您创建隐式连接子句。通用列是指在两个表中具有相同名称的列。

  • [style="arabic"]1、NATURAL JOIN 和带 JOIN.ON 的连接之间有什么区别？*

*答案：B、C。*自然连接的连接条件基本上是对具有相同名称的所有列进行等值连接。用 ON 子句来指定任意条件或指定要连接的列。连接条件与其他搜索条件分开。ON 子句使得代码变得容易理解。

16.What is true about the JOIN..ON clause in Oracle DB?

*答案：A、C。*自然连接的连接条件基本上是对具有相同名称的所有列进行等值连接。用 ON 子句来指定任意条件或指定要连接的列。连接条件与其他搜索条件分开。ON 子句使得代码变得容易理解。

17. The database designer has named the key (unique) columns from two tables differently.While joining these tables, which among the following will be the best practice?

*答案：A. *在这种情况下使用 NATURAL JOIN 会产生意外的结果，因为会隐式搜索具有相同名称的列，但这种情况下没有这种列。

18.What of the following can be used to fetch non-matching rows along with the matching rows between a source and a target table in Oracle DB?

*答案：D. *当记录需要包含在结果中但没有在联接表中具有对应的记录时，就会创建外部联接。这些记录与 NULL 记录匹配，以便将其包含在输出中。

19. What are Cartesian Joins also known as in Oracle DB?

*答案：C. *两个表之间的笛卡尔联接返回表中每一行所有可能的组合。可以通过不在查询中包含联接操作或使用 CROSS JOIN 来生成笛卡尔联接。

20.What will be the result of a NATURAL JOIN between two tables EMPLOYEES and DEPARTMENT as given in the query below? (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 NATURAL JOIN department;

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

21.What is the difference between a NATURAL JOIN and an EQUI-JOIN in Oracle DB?

*答案：D. *NATURAL JOIN 联接所有具有相同名称的列，而 EQUI-JOIN 要求在 SQL 查询中明确提及这些列。

22.What is an INNER JOIN in Oracle DB?

*答案：C. *联接可以是内部联接，其中只返回在所有表中都有匹配记录的记录，或者可以是外部联接，其中可以返回记录，而不管联接中是否有匹配记录。

23.What is the difference between a INNER JOIN and an EQUI-JOIN in Oracle DB?

*答案：C. *EQUI-JOIN 是一个在联接条件中包含“=”运算符的 INNER JOIN 类型，而 INNER JOIN 可以同时包含相等和不相等运算符

24.What is true about NATURAL JOINS in terms of ANSI SQL: 1999 syntaxes in Oracle DB?

*答案：C. *ANSI SQL 语法不同于在传统方式中使用 (=) 的方式。ANSI SQL 语法中有一些关键字，如 NATURAL JOIN，用于区分使用的联接。

25.What of the following is true with respect to the query given below? (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 first_name, salary
FROM employees e, departments d
WHERE e.department_id  (+) = d.department_id ;

*答案：A，D。*条件 e.department_id (+) = d.department_id 表示它将执行右外部联接，并将显示 department 表中的所有 department_id，无论它们是否匹配

26.Which of the following syntax models is used in extensively in the software systems worldwide?

*答案：C. *ANSI SQL：1999 语法虽然没有传统 Oracle 语法那样用得广泛，但它仍然是 Oracle SQL 中可能使用的语法之一

27.What of the following is true regarding the Cartesian product in Oracle DB?

*答案：A. *两个表之间的笛卡尔连接返回表中每一行的所有可能组合。笛卡尔连接可以通过不在查询中包括 join 操作或通过使用 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 ;

*答案：D. *如果没有表别名，Oracle 将无法得出要连接的列的来源，因此在执行时会抛出 Ambiguity 错误。

29.Which of the following is used to avoid the ambiguous column problem in Oracle DB?

*答案：C. *解决 Ambiguous 列问题的语法是：table_alias.column_name

30.Which of the following is the most appropriate about the following query? (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 employee_id , first_name, last_name
FROM employees e right outer join department d
On e.department_id  = d.department_id ;

*答案：C. *对于外连接的 JOIN 方法，您可以添加 LEFT、RIGHT 或 FULL 关键字。左外联接包括连接中左侧表中的所有记录，即使在连接操作中未找到与另一张表的匹配项。全外连接包含两张表中的所有记录，即使在另一张表中找不到相应的记录。

31.What will be the outcome of the following query? (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, department d
WHERE a.department_id  = b.department_id ;

*答案：C. *相同的别名应在 FROM 子句中用作在 WHERE 子句中

32.Which of the following is true regarding the USING and ON clauses in table joins? (Choose more than one options if applicable)

*答案：A、D. *JOIN …​ USING 方法类似于 NATURAL JOIN 方法，只是在 USING 子句中指定公共列。无法在 USING 子句中包含条件以指示表格之间的关系。此外，不能对 USING 子句中指定的公共列使用列限定符。JOIN…​ ON 方法基于指定的条件连接表。FROM 子句中的 JOIN 关键字指示要连接的表，ON 子句指示两张表之间的关系。如果要连接的表在每张表中没有同名的公共列，则必须使用此方法。

33.How many tables can be joined by using the JOINS in Oracle DB?

*答案：D. *目前对于参与连接的表数量没有限制。

34. What is true when multiple joins are used in an SQL statement?

*答案：A. *当语句中存在多个连接时，它们将从左到右进行评估。

35.What is true with respect to the following query? (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)
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;

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

36.What is true about the clauses JOIN..ON in Oracle DB?

*回答：B。*JOIN …​. ON 方法基于指定条件联接表。FROM 子句中的 JOIN 关键字指示待联接的表，ON 子句指示两张表的关联方式。如果被联接的表在每张表中没有名称相同的公共列，则必须使用此方法。

Examine the table structures as given. Answer the questions 37 and 38 that follow the query given below:

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.What is true with respect to the query given above?

*回答：D。*WHERE 子句可以省略，相关条件可以容纳在给定查询中所示的 JOIN..ON 子句中

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?

回答：C、D。

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

*回答：C。*非等价联接与 JOIN..ON 子句一起使用，但使用不等于运算符。

检查表 EMPLOYEES 和 DEPARTMENTS 的结构如下，并回答随后的问题 40 和 41。

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. 以下查询在 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);

回答：C。

  • 41. 您需要一份报告，列出薪水大于 20000 且位于日内瓦的任何部门的员工的名字和姓氏。下列哪一个查询将给出所需结果？*

回答：A、C。

  • 42. 当一个 EQUI-JOIN 用于联接表时，会在哪种情况下返回一行？*

*回答：A。*等值连接是在来自两个不同表的联接记录的数据完全匹配时创建的（即，等值条件创建关系）。传统方法使用 WHERE 子句中的等号作为比较运算符。JOIN 方法可以使用 NATURAL JOIN、JOIN …​ USING 或 JOIN …​ ON 关键字。

  • 41. 您需要一份报告，列出薪水大于 20000 且位于日内瓦的任何部门的员工的名字和姓氏。下列哪一个查询将给出所需结果？*

回答：A、C。

  • 42. 当一个 EQUI-JOIN 用于联接表时，会在哪种情况下返回一行？*

*回答：A。*等值连接是在来自两个不同表的联接记录的数据完全匹配时创建的（即，等值条件创建关系）。传统方法使用 WHERE 子句中的等号作为比较运算符。JOIN 方法可以使用 NATURAL JOIN、JOIN …​ USING 或 JOIN …​ ON 关键字。

  • 41. 您需要一份报告，列出薪水大于 20000 且位于日内瓦的任何部门的员工的名字和姓氏。下列哪一个查询将给出所需结果？*

回答：A、C。

  • 42. 当一个 EQUI-JOIN 用于联接表时，会在哪种情况下返回一行？*

*回答：A。*等值连接是在来自两个不同表的联接记录的数据完全匹配时创建的（即，等值条件创建关系）。传统方法使用 WHERE 子句中的等号作为比较运算符。JOIN 方法可以使用 NATURAL JOIN、JOIN …​ USING 或 JOIN …​ ON 关键字。

  • 41. 您需要一份报告，列出薪水大于 20000 且位于日内瓦的任何部门的员工的名字和姓氏。下列哪一个查询将给出所需结果？*

回答：A、C。

  • 42. 当一个 EQUI-JOIN 用于联接表时，会在哪种情况下返回一行？*

*回答：A。*等值连接是在来自两个不同表的联接记录的数据完全匹配时创建的（即，等值条件创建关系）。传统方法使用 WHERE 子句中的等号作为比较运算符。JOIN 方法可以使用 NATURAL JOIN、JOIN …​ USING 或 JOIN …​ ON 关键字。

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

*回答：C。*自连接用于在表必须连接到自身以检索所需数据时。FROM 子句中需要表别名来执行自连接。

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 ;

Oracle 将以下哪个表视为源表和目标表？

*回答：A. *第一次出现的员工表被视为源，后续出现的视为 b、c 等。

  • 45.在什么情况下我们可以在 Oracle DB 中很好地使用自连接？*

*回答：D. *

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

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

*47.需要提取一份报告，其中仅提供部门名称、部门 ID、部门城市和部门 100 和 101 的位置 ID。使用 NATURAL JOIN，以下哪一个选项将给出所需结果？ *

DEPARTMENTS
LOCATIONS

*回答：A. *WHERE 可用于连接子句后附加条件。

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

*回答：C、D. *NATURAL JOIN 和 USING 相互排斥，USING 子句应该仅用于匹配多于一个匹配列中的一个列。

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			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER
 JOB_ID 			  VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
SELECT e.name, b.bonus
FROM employees e
JOIN bonus b
USING (job_id)
WHERE e.job_id  like 'SA%');

*回答：D. *USING 子句中使用的列不应在 SQL 语句的任何地方都有限定符（表名或别名）。

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

*回答：A、C. *连接可以是内部连接，其中仅返回的记录在所有表中都有一个匹配记录，或者是一个外部连接，其中可以返回记录，而不管在连接中是否有匹配记录。当需要在结果中包含记录而不在连接表中具有相应记录时，会创建一个外部连接。这些记录与 NULL 记录匹配，以便将它们包含在输出中。

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

*回答：D. *完全外部连接包括来自两个表的所有记录，即使没有找到另一张表中的相应记录。

Examine the given table structures and answer the questions 52 and 53 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)
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 );

此查询导致错误。出错的原因是什么？

*答案：A。*Table1 JOIN table2 JOIN table3 在没有每个 JOIN 之间的 ON 从句的情况下是不允许的

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?

*答案：C。*全外连接返回来自两个表的未匹配行。全外连接包括来自两个表的所有记录，即使没有找到另一个表中的对应记录。

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

*回答：D. *

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

*答案：C。*除了“中心”之外，其余 3 种类型是 Oracle DB 中外连接的格式类型。使用外连接的 JOIN 方法，您可以添加 LEFT、RIGHT 或 FULL 关键字。

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?

*答案：C。*NATURAL JOIN 从句隐式匹配所有相同命名的列。要添加附加条件，可以使用 WHERE 从句。

57.您需要找出上面查询获得的结果，以了解所有薪水高于 20000 的员工。下面哪些从句应该添加到上面的查询中/对其进行修改？

*回答：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?

*答案：D。*可以在 ON 从句之后为更多条件添加等值连接。

59.必须提取一份报告以获取公司“ABC”10 和 20 部门的所有员工的经理。下面哪些查询将提供所需的结果？（考虑给定的表结构）

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)

*答案：D。*选项 C 不正确，因为 WHERE 从句中的非别名 department_id 将引发错误。

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)

*答案：D。*全外连接将提供来自两个表的匹配和不匹配的所有行，但排除重复值。

检查此处给出的表结构并回答问题 61 到 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 ;

答案：B。(+) 在等式的左侧表示是右外连接，反之亦然。

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?

*回答：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?

回答：C。

  • 64.您需要提取一份报告，其中显示尚未分配给城市的所有部门。下列哪个查询将为您提供所需输出？*

*回答：D. *

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

*答案：A、D。*当记录需要包含在结果中且在连接表中没有对应记录时，将创建外连接。这些记录与 NULL 记录匹配，以便将它们包含在输出中。

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

答案：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)

评估以下 SQL 语句：

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 ;

如果您从计算中删除所有括号，将会发生什么？

回答：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?

employees
departments
grade

*答案：A。*非等值连接基于除了相等条件之外的其他任何内容建立关系。与非等值连接一起使用的范围值必须相互排斥。

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

*答案：C、D。*JOIN …​. USING 方法类似于 NATURAL JOIN 方法，只不过通用列指定在 USING 子句中。无法在 USING 子句中包含条件来指示表如何关联。此外，无法对 USING 子句中指定的通用列使用列限定符。

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)

答案：B、C。

*71.检查给定的表结构。 *

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 语句：

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

哪条语句与结果相关？

*回答：D. *

72.您需要生成一份报告，显示所有在 2013 年 1 月 20 日之前聘用的员工姓名以及部门所在位置。

departments
employees
locations

您发出以下查询：

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) ;

关于上述查询，哪种说法是正确的？

回答：B。

73.Examine the structure of the EMPLOYEES table:

employees

您想通过列出所有重复的姓名来找出是否多次使用不同的 EMPLOYEE_ID 输入了任何员工的详细信息。您可以使用哪种方法来获得所需结果？

回答：A。 自联接用于必须将表联接到它自身以检索所需数据时。在 FROM 子句中需要表别名来执行自联接。

检查表 DEPARTMENTS 和 LOCATIONS 的结构，并回答以下第 74 和 75 个问题。

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?

回答：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);

在上方查询中的空白处可以使用哪些两个 JOIN 选项来提供正确的结果？

回答：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)

答案：A。

77. 检查以下实例：

bonus
departments
employees

您想要生成一份报告，列出员工的 ID 及其相应的佣金和部门（如果有的话），无论是否存在佣金。评估以下查询：

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

在上述查询中的空白处使用的哪种联接组合会提供正确的结果？

答案：A。

78.Predict the outcome of the following query.

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

*回答：D. *

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

bonus
departments
employees
grade
locations

79.您需要列出苏黎世市的所有部门。您执行以下查询：

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

预测上述查询的结果。

*答案：D。*仅应在 USING 子句中使用匹配的列名。

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';

您需要提取一份报告，该报告提供一家名为“ABC”的公司员工的姓名、部门编号、薪水和奖金。下列哪种查询能解决此目的？

*答案：C。*查询 A 将抛出一个语法错误，查询 B 将在奖金和部门之间抛出一个无效标识符错误。

查看下面给出的展品并回答随后的问题 81 至 85。

bonus
departments
employees

  • [style="arabic"]1。您需要为那些赚取超过 20000 的员工找到经理的姓名。下列哪种查询将用于获得所需结果？*

*回答：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;

对于此查询的结果，下列哪种说法是正确的？

回答：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 ;

可以在上述查询的空白处使用哪种 JOIN 选项来获得所需输出？

*答案：C。*左外连接包含联接操作中从联接左表中列出的所有记录，即使未找到与联接中的其他表匹配的记录。

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)

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 ' 到上面查询中以满足此要求？*

答案：A。

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

*答案：B。*大括号不是必需的，但是使用它们可以清楚地理解其中的条件。

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

*答案：A、B。*两个表之间的笛卡尔连接返回两个表中每行所有可能的组合。可以通过不在查询中包含联接操作或通过使用 CROSS JOIN 来生成笛卡尔连接。

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

*答案：C。*Oracle 抛出异常“ORA-01719：外部联接运算符 (+) 不允许在或运算或 IN 运算符中”

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

*答案：A。*当两个不同表中的数据相连记录完全匹配时（即，相等的条件创建该关系），就会生成相等连接。

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

*答案：D。*非相等连接基于不相等表达式匹配来自不同表的列值。源表中每行连接列的值都与目标表中的相应值相比较。如果基于不相等运算符在连接中使用的表达式计算结果为真，则会找到匹配。构建此类连接时，将执行非相等连接。使用 JOIN..ON 语法指定非相等连接，但连接条件包含不相等运算符，而不是等号。

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;

*回答：D. *完全外部连接包括来自两个表的所有记录，即使没有找到另一张表中的相应记录。

91. Which of the following queries is valid?

*答案：C。*如果连接表有别名，则所选列必须使用别名而不是实际表名。

92. Given the following query.

SELECT zip, order#
FROM customers NATURAL JOIN orders;

以下哪个查询是等效的？

*答案：B。*自然连接指示 Oracle 识别源表和目标表之间具有相同名称的列。

*93. 检查给定的表结构。以下 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)
1. SELECT e.first_name, d.department_name
2. FROM employees  e, department d
3. WHERE e.department_id=d.department_id

*答案：A。*如果查询在连接条件中使用别名，则其列应使用别名进行引用。

94. Given the following query:

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

以下哪种查询返回相同的结果？

答案：B、C。

95. Which of the below statements are true?

*答案：C。*组函数可用于使用 Oracle 连接的查询。歧义列必须使用限定符引用。

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

*答案：C。*由于表是使用 JOIN 关键字连接的，所以相等条件应使用 USING 子句编写，而不是 WHERE 子句。

97. Given the following query:

SELECT title, gift
FROM books CROSS JOIN promotion;

以下哪个查询是等效的？

*回答：D。*笛卡尔连接与交叉连接相同。

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;

*回答：B。*交叉连接是两个表中包含的行之间的交叉乘积。

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

*回答：A。*具有二义性的列必须使用表限定符引用。

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

*回答：A。*假设列 NAME 不包含在表 BOOKS 中，则查询 A 有效。

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

*回答：B。*查询 B 抛出异常“ORA-01468：谓词只能引用一个外部连接表”。

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

回答：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);

*回答：B。*由于查询中使用的表具有限定符，因此必须使用相同的限定符引用列。

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

*回答：D。*表别名最长可以包含 30 个字符。

105. Which of the following SQL statements is valid?

Answer: C.