Sql Certificate 简明教程

SQL - Restricting and Sorting Data Questions

1. Which of the following clause is used to limit the number of rows retrieved from a SELECT query?

Answer: B. WHERE 子句用于限制 SELECT 查询返回的行数。

2. Choose the database elements whose values can be compared in a WHERE clause of a SELECT query.

*答案：A、D。*WHERE 子句可用于比较列、字面值、算术运算和函数中的值。

3. What are the elements NOT contained in the WHERE clause predicate of the SELECT query?

*答案：D。*WHERE 子句谓词中不需要表名。

4. Which of the following values can NOT be returned after evaluation of WHERE clause condition?

*答案：A。*如果不知道 WHERE 子句中条件的结果，则返回 NULL。在所有其他情况下，返回 TRUE 或 FALSE。

5. What is the minimum number of WHERE clauses that must be present in a SELECT query?

*答案：C。*WHERE 子句是 SELECT 查询中的可选子句，仅用于限制行数。

6. What is the maximum number of WHERE clauses that can be included in a SELECT query?

*答案：A。*WHERE 子句是 SELECT 查询中的可选子句，只能使用一次来限制行数。

7. Which of the following statements are correct about the WHERE clause?

*答案：C。*WHERE 子句必须具有用于计算条件的比较运算符。它可以使用函数作为其中一个运算数。在一个 SELECT 查询中只允许使用一个 WHERE 子句。

8. Write a SELECT query to list down unique departments from EMP table?

Answer: B & C. DISTINCT 关键字用于过滤出 SELECT 查询中的重复行。

9. Which of the following operations are permitted for date and timestamp columns?

*答案：B、C 和 D。*加、减和连接是允许用于日期和时间戳列的操作。

10. From the below operators, which one of them holds the highest precedence level?

Answer: C. 括号内的表达式具有优先级最高的级别。

11. Interpret the output returned by the below SELECT query

SELECT ename, (sysdate - hiredate)
FROM emp;

*答案：C。*表达式 (sysdate-hiredate) 返回员工在公司中的任职天数。

12. Which of the below statements correctly describle the DUAL table in Oracle?

*答案：B、C、D。*Oracle 中的 DUAL 表由 SYS 拥有，并包含一个类型为 VARCHAR2(1) 的列 DUMMY。

13. Determine the type of output returned by the below query

SELECT sysdate - hiredate
FROM emp
WHERE empno=7369;

*答案：B。*两个日期之间的减法得出两个日期之间的数字日期差

14. Which expressions do NOT return NULL values?

Answer: B, D. 任何用 NULL 进行的算术运算都会得到 NULL。

15. Determine the output of the below query

SELECT 'Tutorial''s Point compiles technical tutorials' FROM DUAL;

回答：B。

16. Examine the TRAINING table as given below:

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------

 TRAINING_ID                               NOT NULL NUMBER(5)
 TRAINING_LOCATION                                  NUMBER(7,2)
 START_DATE                                         DATE
 END_DATE                                           DATE

Answer: A, D. 在 NULL 时使用 NVL 函数向列提供备用值。

17. What does the selection of columns in a SELECT statement known as?

Answer: C. 投影是仅在 SELECT 语句中选择所需列的能力。

18. What does the restriction of rows returned by a SELECT statement known as

Answer: C. 限制是指通过设置特定条件来限制行数的能力。

19. Which of the following is true about the query given below?

SELECT col1, col2
FROM tab1
ORDER BY col1;

Answer: B. 默认情况下，ORDER BY 子句按升序对值进行排序。

20. Which of the following is true about the SQL query given below?

SELECT col1,col2
FROM tab1
WHERE col1 = 'A'
ORDER BY col2 DESC, col1;

*答案：C。*由于 COL1 已经在查询中作为标量值进行了筛选和固定，因此不会根据 COL1 进行排序。

21. What is true regarding the query given below?

SELECT col1, col2
FROM  tab1
ORDER BY col1,col2
WHERE col2 = 'B';

Answer: D. ORDER BY 子句必须出现在 SELECT 语句中的 WHERE 子句之后

22. Which two clauses of the SELECT statement are necessary for Selection and Projection?

Answer: C.

23. Which of the following WHERE clauses will NOT fit in the below SELECT query?

SELECT ename, deptno, sal
FROM emp;

*答案：C。*字符文本必须用单引号引起来

24. Choose the WHERE clause that extracts the DNAME values containing the character literal “er” from the DEPT table.

答案：B. LIKE 运算符用于在 SQL 查询中执行通配符搜索。

25. Which two of the following conditions are equivalent to each other?

答案：A，D. NOT 运算符可用来否定其操作数的影响。因此 (COMM IS NULL) 等效于 (NOT (COMM IS NOT NULL)).

26. Which of the following clauses are mandatory in an SQL query?

Answer: A. SELECT 和 from 是 SELECT 查询中的强制性子句。

27. Which three of the following WHERE clause conditions are equivalent to each other?

Answer: A, C, D. 可以使用 IN、BETWEEN 和关系运算符使条件等效

28. Which of the following is true with respect to the below query?

SELECT empno, ename, job
FROM emp
WHERE ename like '_ith%';

*回答：D. *

29. Which of the following is used to end a SQL query?

Answer: B, D. 在 SQL*Plus 和 SQL Developer 中，使用分号 (;) 或反斜杠 (/) 来终止查询。

30. The employees JAMES and MILLER want to know their department id by querying the database. Which of the following queries will give the required result?

Answer: D. 可以使用 OR 子句连接多个条件。如果两个条件中任何一个为真，则查询执行成功。

31. Which of the following is false regarding the WHERE clause?

Answer: C, D.

32. What is the default date format in Oracle?

答案：D. DD-MON-RR 是 Oracle 中的默认日期格式。

33. Predict the output of the below SQL query.

SELECT ename, deptno, sal, comm
FROM emp
WHERE job = 'SALES'
AND hiredate = ”01-JAN-97”;

答案：B. 日期文本必须用单引号引起来。

34. You need to display the names of all the employees having the first name as "GARRY" from the EMPLOYEES table. Which of the following queries will fulfill the requirement?

Answer: C. 如果搜索字符串的某些字符未知，可以使用通配符。

35. You need to display the employee ID of all the employees who contain a letter 's' in their last name at second position and department ID as 100. Which of the following queries will fetch the required results?

Answer: D. 通配符下划线 (_) 用于替换单个字符。

36. What will be the outcome of the below query?

SELECT first_name, last_name, dept_id
FROM employees
WHERE hire_date LIKE '%98';

Answer: D. LIKE 运算符用于对字符和日期文字执行通配符搜索。

37. Which of the following is used to get rows based on a range of values?

Answer: C. BETWEEN 运算符用于基于值范围检索行。

38. You need to display the employee IDs of the employees who have their salaries between 20000 (inclusive) and 50000(inclusive). Which of the following queries will fetch the required results?

*答案：A、D。*对于较大的值范围，BETWEEN 和关系运算符最适合在查询中使用。不建议将 IN 运算符用于较大的值范围。

39. What is true with respect to the below query?

SELECT first_name, last_name
FROM employees
WHERE last_name BETWEEN 'B%' AND 'E%';

*答案：A。*BETWEEN 运算符也可以使用字符值范围。

40. What will be the outcome of the query mentioned below?

SELECT employee_id, last_name, first_name, salary, manager_id
FROM employees
WHERE manager_id IN (200,100,300);

Answer: B. IN 运算符可用于提供范围小且数量有限的范围。

41. Which of the following clause defines a Membership condition?

*答案：D。*IN 运算符定义了一个成员资格条件，该条件可以使用值范围或子查询。

42. Which of the following data types can be used within IN operator?

*答案：D。*IN 运算符适用于所有类型的值。

43. You need to display the list of all the employees whose first name starts with “Bryan” or “Jason”. Which of the following queries will fulfill the requirement?

Answer: C, D. IN 运算符检查定义为成员资格条件的任意值。

44. You need to extract details of those departments whose name contains the string '_DXX'. Which of the below WHERE clauses could be used in the SELECT statement to get the required output?

Answer: B.

45. Which statement is true regarding the default behavior of the ORDER BY clause?

Answer: A. ORDER BY 子句对字符值进行大小写敏感排序。

46. You need to generate a report of all employees from the EMPLOYEES table based on the following conditions: 1. The Employee first name should not begin with 'T' or 'N'. 2. The Employee’s salary should be more than 20000. 3. The Employee should have been hired after 1st January 2010. Which WHERE clause would give the required result?

回答：C。

47. Using the EMPLOYEES table, you need to display the names of all employees hired after January 1, 2013, starting with the freshers. Which query would give the required result? (Choose all that apply.)

Answer: A, D.

48. Using the EMPLOYEES table, you need to find out the names and salaries of all the employees hired in departments 100 and 101 in the time interval 15th March '12 to 15th October '13. Which two queries would give the required result? (Choose two.)

*答案：A，D. *

49. Using the EMPLOYEES table, you issue the following query to generate the names, current salary and the salary increased after an appraisal by 25%. The increased salary for all the employees should be above 30000.

SELECT first_name, salary,
salary + (salary *0.25) "INCREASED_SALARY"
FROM employees
WHERE increased_salary >30000;

查询抛出错误 ORA-00904。错误的原因是什么？

*答案：C*列别名不能用于 WHERE 子句条件，但可以用于 SELECT 语句和 ORDER BY 子句。

50. You need to display employee names from the EMPLOYEES table that belong to the Department id 100 with minimum salary as either 2000 or 4000 and no job_id. You issue the following query.

SELECT first_name, dept_id, salary
FROM employees
WHERE dept_id = 100 AND (salary = 2000 OR salary = 4000)
AND job_id <> '';

Which statement is true regarding the above query?

*答案：A*条件 (salary = 2000 OR salary = 4000) 导致 FALSE，因为一个员工不能同时持有多项薪资。

51. Which three tasks can be performed using SQL functions built into Oracle Database? (Choose three.)

*答案：A、B、C*使用格式化函数 (TO_CHAR、TO_DATE) 和字符函数 (LENGTH、REPLACE) 来实现目标。

52. You need to generate a report that displays the IDs of all employees in the EMPLOYEES table whose salary is at least 25% more than the value 20000. The details should be displayed in the descending order of the salary. You issue the following query.

SELECT emp_id
FROM employees
WHERE salary>=20000*0.25
ORDER BY salary*0.25 DESC;

Which statement is true regarding the above query?

*答案：A*ORDER BY 子句可以包含列表达式。

53. Examine the structure and data of the TRAININGS table:

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------

 TRAINING_ID                               NOT NULL NUMBER(5)
 TRAINING_LOCATION                                  NUMBER(7,2)
 START_DATE                                         DATE
 END_DATE                                           DATE

TRAINING_ID      START_DATE                      TRAINING_COST
------ ---------------- -------------------------------------------------
11 	                 01-JAN-10 			1000
22 		  01-FEB-10 			2000
33 		  01-MAR-10 			3000

Dates are stored in the default date format dd-mon-rr in the TRAININGS table. Which three SQL statements would execute successfully? (Choose three.)

Answer: A, C, D.

54. Which of the following statements is/are true with respect to the below query?

SELECT emp_id, first_name
FROM employees
ORDER BY dept_id;

Answer: C. ORDER BY 子句可以使用不在列列表中选择的一列来对数据进行排序，但包含在 FROM 子句中使用的表中。

55. Which feature of ORDER BY clause is demonstrated in the below query?

SELECT emp_id, first_name “EmpName”
FROM employees
ORDER BY "EmpName";

*回答：C. *ORDER BY 子句与 SELECT 语句中使用的列别名配合良好。

56. What is true about the query given below?

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY 2;

Answer: A. 数值位置的列可以在 ORDER BY 子句中使用。

57. You need to list the employees details for different jobs but only one at a time.

SELECT emp_id, first_name, last_name FROM employees WHERE job_id....;

Which of the following is an easier way to achieve the same in SQL 加号？*

Answer: C. &X 符号暂停查询执行，并在每次执行查询时提示用户输入。

58. Which of the following statements is true regarding substitution variables in SQL?

答案：A。

59. Which of the following data type is assigned to Substitution variables?

*回答：C. *替代变量本身没有数据类型，但遵守与它们一起使用的列的数据类型。

60. Which among the following is true about substitution variables?

*回答：D. *

61. Which of the following is a correct syntax for Substitution variables in SQL 加号？*

Answer: C, D.

62. Which of the following Substitution variables will take the entered value once and then keeps it for the rest of the session?

*回答：A. *具有双连字符的替代变量重复使用用户提供的值一次。

63. Which of the following is true about substitution variables?

回答：C。

64. What is true about the query given below?

SELECT first_name, last_name, employee_id, salary
FROM employees
WHERE employee_id = &eid;

答案：B、D。

65. Choose the statements which hold true about the query given below.

SELECT first_name, last_name, &&prompt_col
FROM employees
ORDER BY &&promp_col;

Answer: D. 可以在 SQL 查询的所有子句中使用替换变量。

66. Which of the following commands is used to create and assign a value to a substitution variable in SQL 加号？*

Answer: D. 在 SQL*Plus 中使用 DEFINE 命令在会话中声明替换变量。

67. What will be the outcome of the below activity in SQL 加号？*

DEFINE eid = 117

SELECT first_name, last_name, employee_id, salary
FROM employees
WHERE employee_id = &eid;

回答：C。

68. What is the command to remove the value of the substitution variable set by the command DEFINE?

*答案：A。*使用 UNDEFINE 命令从会话中删除替换变量

69. Which of the following commands is used to check the substitution variables values before and after execution of an SQL query?

*答案：D。*在 SQL*Plus 和 SQL Developer 中使用 VERIFY 命令检查使用替换变量替换值。

70. Which of the following are valid operators for the WHERE clause?

答案：A、B、C。

71. Evaluate the following query:

SELECT ename || q'{'s salary is }' || sal
AS "Salary"
FROM emp;

Answer: C.

72. Which of the below WHERE clause predicates will correctly list the employees from department 20?

Answer: C, D. 等于运算符 (=) 用于比较条件中运算式的相等性。

73. Write a SELECT query to list the employees whose salary is greater than 1000.

Answer: B. 大于运算符 (>) 用于比较条件中的运算式。

74. What would happen when the below query is executed in SQL 更妙的是？*

SELECT ename, sal, deptno
FROM emp
WHERE sal/10 > deptno*10;

Answer: A. WHERE 子句可以包含表达式。

75. Determine the error in the below SELECT statement

SELECT ename, deptno, sal
FROM emp
WHERE job=CLERK;

Answer: B. 字符串文字必须用单引号括起来

76. Interpret the output of the below SQL query

SELECT ename, deptno, sal
FROM emp
WHERE sysdate-hiredate > 100;

Answer: A, B. 日期表达式可用于 WHERE 子句

77. Which of the following query will display the employees which are hired after 31st Decemeber, 1982?

Answer: A, B. 日期文字必须用单引号括起来。

78. Which of the following WHERE conditions will list employees who were hired on current date?

Answer: C, D. 条件 SYSDATE=HIREDATE 将不起作用，因为 SYSDATE 包含动态时间戳组件，而 hiredate 是数据库中的静态值。

79. What of the following are the valid formats of date literals which can be used in WHERE clause?

Answer: A, C. 日期文字的默认格式为 DD-MON-RR。