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

限制和排序数据问题

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

1.以下哪个子句用于限制从SELECT查询中检索的行数?

  1. LIMIT

  2. WHERE

  3. AND

  4. FROM

答案:B. WHERE子句用于限制从SELECT查询返回的行数.

2 .选择可在SELECT查询的WHERE子句中比较其值的数据库元素.

  1. 专栏

  2. 序列

  3. 程序

  4. Literal

答案:A,D. WHERE子句可用于比较列,文字,算术函数和函数的值.

3. SELECT查询的WHERE子句谓词中包含的元素是什么?

  1. 比较运算符

  2. 比较条件

  3. 列名

  4. 表名

答案:D. WHERE子句谓词中不需要表名.

4.在评估WHERE子句条件后,不能返回以下哪个值?

  1. UNKNOWN

  2. TRUE

  3. FALSE

  4. NULL

答案:A.如果WHERE子句中的条件结果未知,则返回NULL.在所有其他情况下,返回TRUE或FALSE.

5. SELECT查询中必须存在的最小WHERE子句数是多少?

  1. 1

  2. 2

  3. 0

  4. 3

答案:C. WHERE子句是SELECT查询中的可选子句,仅用于限制行数.

6. SELECT查询中可包含的WHERE子句的最大数量是多少?

  1. 1

  2. 2

  3. 0

  4. 3

答案:A. WHERE子句是SELECT查询中的可选子句,只能使用一次来限制行数.

7.关于WHERE子句,下列哪一项说法是正确的?

  1. 列别名可用于引用列的WHERE子句

  2. 比较运算符是WHERE子句条件中的可选元素

  3. 函数可用作WHERE子句中的操作数

  4. SELECT查询中可以有多个WHERE子句

答案:C. WHERE子句必须有比较运算符来评估条件.它可以使用函数作为操作数之一. SELECT查询中只允许一个WHERE子句.

8.编写SELECT查询以列出EMP表中的唯一部门?

  1.  SELECT deptno FROM emp;


  2.  SELECT DISTINCT deptno FROM emp;


  3.  SELECT DISTINCT(deptno)FROM emp;


  4.  SELECT empno,DISTINCT deptno FROM emp;


答案:B& C. 关键字DISTINCT用于从SELECT查询中过滤掉重复的行.

9.日期和时间戳列允许以下哪些操作?

  1. 分部

  2. 加法

  3. 减法

  4. 连接

答案:B,C和D. 加法,减法和连接是日期和时间戳列允许的操作.

10.从下面的运算符中,哪一个具有最高优先级?

  1. 分部(/)

  2. 乘法(*)

  3. 括号(())

  4. 减法

答案:C.括号内的表达式具有最高优先级.

11.解释以下SELECT查询返回的输出

 SELECT ename,(sysdate  -  hiredate) FROM emp;


  1. 当年的天数

  2. 一年中的天数聘用员工时

  3. 员工与公司共度的天数

  4. 查询引发异常"ORA-00932:不一致的数据类型:预期的NUMBER得到日期"

答案:C.表达式(sysdate-hiredate)返回员工的工作日数公司.

12.以下哪个语句正确描述了Oracle中的DUAL表?

  1. DUAL表是临时的Oracle数据库中的表

  2. DUAL表只包含一个称为DUMMY的字符类型列

  3. SYS拥有的DUAL表无法删除

  4. 用户可以在自己的架构中创建名称为DUAL的表格

答案:B,C, D. Oracle中的DUAL表由SYS拥有,并包含一个类型为VARCHAR2(1)的DUMMY列.

13.确定以下查询返回的输出类型

 SELECT sysdate  -  hiredate  FROM emp  WHERE empno = 7369;


  1. DATE数据类型

  2. NUMBER数据类型

  3. VARCHAR2数据类型

  4. 由于无法对日期列执行算术运算,查询会引发错误

答案:B.两个日期之间的减法会导致两个日期之间的天数差异

14.哪些表达式不返回NULL值?

  1.  SELECT ((10 + 20)* 50)+双重空值;


  2.  SELECT'这是'|| null ||'使用双重'测试';


  3. 从双重中选择null/0;


  4.  SELECT null ||'test'|| null as"test"from dual;


答案:B,D.任何带NULL的算术运算都会产生NULL.

15.确定以下查询的输出

 SELECT'Tutorial''的Point编译技术教程'FROM DUAL;


  1. 教程'点编译技术教程

  2. 教程点编译技术教程

  3. '教程'点编译技术教程'

  4. 引发异常"ORA-01756:引用字符串未正确终止"

答案:B.

16.检查TRAINING表,如下所示:

名称是否为空?输入 ----------------------------------------- ---- ---- -------------  TRAINING_ID NOT NULL NUMBER(5) TRAINING_LOCATION NUMBER(7,2) START_DATE DATE  END_DATE DATE


哪两个SQL会成功执行? (选择两项)

  1.  SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE)FROM培训;


  2.  SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))FROM training;


  3.  SELECT NVL(MONTHS_BETWEEN(START_DATE,END_DATE),'进行中')FROM training;


  4.  SELECT NVL(TO_CHAR(MONTHS_BETWEEN(START_DATE,END_DATE)),'进行中')FROM training;


答案:A,D.使用NVL函数在NULL时为列提供备用值.

17. SELECT语句中列的选择称为什么?

  1. 检索

  2. 选择

  3. 投影

  4. 限制

答案:C.投影是能够在SELECT语句中仅选择所需的列.

18. SELECT语句返回的行限制是什么?

  1. 检索

  2. 投影

  3. 限制

  4. 选择

答案:C.限制是指通过设置某些条件来限制行数的能力.

19.关于下面给出的查询,以下哪一项是正确的?

 SELECT col1,col2  FROM tab1  ORDER BY col1 ;


  1. 列COL1的所有行都将按降序排序.

  2. 列COL1的所有行都将按升序排序.

  3. 查询将给出错误,因为查询中没有WHERE子句.

  4. 查询将给出错误,因为ORDER BY子句应包含SELECT子句中的所有列.

答案:B.默认情况下,ORDER BY子句按升序对值进行排序.



20.关于下面给出的SQL查询,下列哪一项是正确的?

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


  1. 它将显示将col1值作为'A'的行按降序排列col1,然后按降序排列col2.

  2. ORDER BY子句不起作用,因为关键字DESC应该是始终写在ORDER BY子句的末尾,而不是在查询中给出的.

  3. 上面的查询将按照col2的降序排序并使用ORDER BY子句中的col1将被丢弃.

  4. 它将显示由col1排序的col1值为'A'的行,然后在执行ORDER时后跟col2 BY子句从
    SELECT语句中的列顺序发生.

答案:C.自COL1以来一世s已经过滤并在查询中作为标量值修复,不会在COL1的基础上进行排序.

21.关于下面给出的查询是什么?

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


  1. 成功执行

  2. 它给出了所需的结果,COL2值为'B',但COL1和COL2列没有排序,因为ORDER BY子句出现在WHERE子句之前.

  3. 它将显示由COL1,COL2排序的COL2值为"B"的行.

  4. 由于无法在Oracle的WHERE子句之前写入ORDER BY子句,因此抛出错误./li>

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

22.选择和投影需要SELECT语句的哪两个子句?

  1. SELECT,FROM

  2. ORDER BY,WHERE

  3. SELECT,WHERE

  4. SELECT,ORDER BY

答案:C.

23.以下哪个WHERE子句不适合下面的SELECT查询?

 SELECT ename,deptno,sal  FROM emp;


  1. WHERE HIREDATE IN('02 -JUN-2004');

  2. WHERE SAL IN('1000','4000','2000');

  3. 在哪里工作(销售,秘书);

  4. WHERE COMMWEEN 0.1和0.5;

答案:C.字符文字必须用单引号括起来

24.选择WHERE子句,从DEPT表中提取包含字符文字"er"的DNAME值.

  1. 在哪里DNAME IN('%e%r');

  2. 在哪里DNAME像'%er%';

  3. 在'e'和'r'之间的DNAME?

  4. 在哪里DNAME包含'e%r';

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

25.以下两个条件彼此相同?

  1. WHERE comm IS NULL

  2. WHERE comm = NULL

  3. WHERE comm IN(NULL)

  4. WHERE NOT(comm IS NOT NULL)

答案:A,D. NOT运算符可用于否定其操作数的效果.因此(COMM IS NULL)等同于(NOT(COMM IS NOT NULL)).

26. SQL查询中必须使用以下哪些子句?

  1. SELECT,FROM

  2. SELECT,FROM,WHERE

  3. SELECT,WHERE

  4. SELECT,WHERE,ORDER BY

答案:A. SELECT和FROM是SELECT查询中的必需子句.

27.以下哪三个WHERE子句条件相互等同?

  1. WHERE SAL

  2. 在哪里(2000,3000,4000,5000)

  3. 在2000和5000之间的地方

  4. WHERE SAL> 1999 AND SAL<5001

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

28.对于以下查询,以下哪项是正确的?

 SELECT empno,ename,job  FROM emp 在哪里en像'_ith%';


  1. 它获取那些员工的ID,姓名和工作在他们的名字中出现"ith"的员工.

  2. 它获取名称以"ith"开头的员工的员工ID,姓名和工作.

  3. 查询以两个表达式引发错误字符串匹配不能一起写.

  4. 它获取员工的员工ID,姓名和工作,其名称以任何字母数字字符开头,后跟'ith',以及'ith'后面的任何字母数字字符'.

答案:D.

29.以下哪项用于结束SQL查询?

  1. :

  2. ;

  3. .

  4. /

答案:B,D.分号(;)或反斜杠(/)用于终止SQL * Plus和SQL Developer中的查询.

30.员工JAMES和MILLER想通过查询数据库来了解他们的部门ID.以下哪个查询会提供所需的结果?

  1.  SELECT ename,deptno FROM emp WHERE ename ='JAMES';


  2.  SELECT ename,deptno FROM emp WHERE ename ='MILLER';


  3.  SELECT ename,deptno FROM dept
  4.  SELECT ename,deptno FROM emp WHERE ename ='JAMES'或ename ='MILLER'

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


31.关于WHERE子句,以下哪一项是错误的?

  1. WHERE可以比较列中的值,文字,算术表达式或函数.

  2. WHERE子句包含列名

  3. 列别名可以在WHERE子句中使用.

  4. WHERE子句不能包含值或常量列表.

答案:C,D.

32. Oracle中的默认日期格式是什么?

  1. DD-MON-YY

  2. DD-MON-YYYY

  3. DD-MM-RR

  4. DD-MON-RR

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

33.预测下面SQL查询的输出.

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


  1. 它获取所有SALES员工的员工数据

  2. 它抛出错误"ORA-00904:"01-JAN-13":无效标识符"

  3. 查询成功执行但未返回任何结果

  4. 它获取1997年1月1日雇用的所有SALES员工的数据

答案:B.日期文字必须用单引号括起来.

34.您需要从EMPLOYEES表中显示名字为"GARRY"的所有员工的姓名.以下哪些查询符合要求?

  1.  SELECT first_name FROM employees WHERE first_name LIKE'GARRY%';


  2.  SELECT first_name FROM employees WHERE first_name LIKE'%GARRY %';


  3.  SELECT first_name FROM employees WHERE first_name LIKE'GARRY';


  4.  SELECT first_name FROM employees WHERE first_name LIKE'_ARRY%';


答案:C.如果搜索字符串的某些字符未知,可以使用通配符.

35.您需要显示所有员工的员工ID,这些员工在其姓氏中包含第二个位置的字母's'和部门ID为100.以下哪个查询将获取所需的结果?

  1.  SELECT emp_id FROM employees WHERE dept_id = 100 AND last_name LIKE'%s%';


  2.  SELECT emp_id FROM employees WHERE dept_id = 100 AND last_name LIKE'%s _';


  3.  SELECT emp_id FROM employees WHERE dept_id = 100 AND last_name LIKE'_s _%';


  4.  SELECT emp_id FROM employees WHERE dept_id = 100 AND last_name LIKE'_s%';


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

36.以下查询的结果是什么?

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


  1. 所有员工的名字,姓氏和部门ID将于1998年加入.

  2. 将显示2098年加入的所有员工的名字,姓氏和部门ID.

  3. 不会返回任何结果.

  4. 将显示1998年1月1日至1998年12月31日期间加入的所有员工的名字,姓氏和部门编号.

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

37.以下哪项用于根据一系列值获取行?

  1. UNION ALL

  2. IN

  3. BETWEEN

  4. LIKE

答案:C. BETWEEN运算符用于根据值的范围检索行.

38.您需要显示工资在20000(含)和50000(含)之间的员工的员工ID.以下哪个查询会获取所需的结果?

  1.  SELECT emp_id FROM employees WHERE salary> = 20000 AND salary <= 50000;


  2.  SELECT emp_id FROM employees WHERE salary IN(20000,50000);


  3.  SELECT emp_id FROM employees WHERE salary> 20000 AND salary<50000;
  4.  SELECT emp_id FROM employees WHERE薪水在20000到50000之间;


答案:A,D.对于较大的值范围,BETWEEN和关系运算符最适合查询.建议不要在IN运算符中使用大范围的值.

39.对于以下查询是什么情况?

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


  1. 它将显示所有姓氏开头的员工字母'B'直到'E'包括B并且不包括E.

  2. 它会抛出一个错误,因为BETWEEN只能用于Numbers而不是字符串.

  3. 它将显示姓氏从"B"开始到"E"结尾的所有员工.

  4. 它会显示所有姓氏在起始字母范围内的员工为"B"和"E",不包括以"B"和"E"开头的名字.

答案:A. BETWEEN运算符也适用于字符值范围.

40.下面提到的查询的结果是什么?

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


ORDER BY manager_id ASC;

  1. 它将显示管理员身份下的所有员工,其ID在100到300之间.

  2. 它将显示管理人员下的所有员工ID为100,200或300.

  3. 由于经理ID应放在引号中,因此会抛出错误.

  4. 由于WHERE子句中manager_id的排序与ORDER BY子句冲突,因此会抛出错误.

答案:B. IN运算符可用于提供小而有限的范围.


41.以下哪个子句定义了成员资格条件?

  1. BETWEEN

  2. LIKE

  3. IS NULL

  4. IN(NOT IN)

答案:D. IN运算符定义了一个可以使用一系列值或子查询的成员资格条件.

42.在IN运算符中可以使用以下哪种数据类型?

  1. VARCHAR2

  2. NUMBER

  3. DATE

  4. ALL

答案:D. IN运算符适用于所有类型的值.

43.您需要显示名字以"Bryan"或"Jason"开头的所有员工的列表.以下哪些查询符合要求?

  1.  SELECT emp_id,last_name,first_name FROM employees WHERE first_name LIKE'Bryan%'OR first_name LIKE'Jason%';


  2.  SELECT emp_id,last_name,first_name FROM employees WHERE first_name BETWEEN'Bryan'和'Jason';


  3.  SELECT emp_id, last_name,first_name FROM employees WHERE first_name IN('Bryan','Jason');


  4.  SELECT emp_id,last_name, first_name FROM employees WHERE first_name ='Bryan'OR/first_name ='Jason'

答案:C,D. IN运算符检查定义为成员资格条件的任何值.

44.您需要提取名称中包含字符串'_DXX'的那些部门的详细信息.可以在SELECT语句中使用以下哪个WHERE子句来获取所需的输出?

  1. WHERE dept_id LIKE'%_DXX%'ESCAPE'_'

  2. WHERE dept_id LIKE'%\ _DXX%'ESCAPE'\'

  3. WHERE dept_id LIKE'%_ D123%'ESCAPE'%_'

  4. WHERE dept_id LIKE'%\ _D123%'ESCAPE'\ _'

答案:B.

45.关于ORDER BY子句的默认行为,哪个陈述是正确的?

  1. 在字符排序中,这些值区分大小写.

  2. 排序操作根本不考虑NULL值.

  3. 只有那些在SELECT列表可以在ORDER BY子句中使用.

  4. 如果数字值有小数位,则从最大值到最小值显示数值.

答案:A. ORDER BY子句使用字符值进行区分大小写的排序.

46.您需要根据以下条件从EMPLOYEES表生成所有员工的报告:
1.员工名字不应以"T"或"N"开头.
2.员工的工资应该超过20000.
3.员工应该在2010年1月1日之后被雇用.
哪个WHERE子句会给出所需的结果?

  1. WHERE first_name NOT LIKE'T%'或first_name NOT LIKE'N%'AND salary> 20000 AND hire_date> '1-JAN-10'

  2. WHERE(first_name不喜欢'T%'和first_name不喜欢'N%')或工资> 20000 OR hire_date>'1-JAN-10'

  3. WHERE first_name NOT LIKE'T%'AND first_name NOT LIKE'N%'AND salary> 20000 AND hire_date>'1-JAN-10'

  4. WHERE(first_name不喜欢'%T%'或first_name不喜欢'%N%')和(工资> 20000 AND hire_date>'1-JAN-10')

答案:C.

47.使用EMPLOYEES表,您需要显示2013年1月1日之后从新手开始雇用的所有员工的姓名.哪个查询会提供所需的结果? (选择所有适用的选项.)

  1.  SELECT first_name ,hire_date FROM employees WHERE hire_date> '01 -JAN-13'ORDER BY 2 DESC;


  2.  SELECT first_name,hire_date FROM employees WHERE hire_date> '01 -JAN-13'ORDER BY first_name DESC;


  3.  SELECT first_name,hire_date FROM employees在哪里hire_date> '01 -JAN-13'ODER BY 1 DESC;


  4.  SELECT first_name,hire_date"START DATE" FROM employees WHERE hire_date> '01 -JAN-13'ORDDER BY"START DATE"DESC;


答案:A,D.

48.使用EMPLOYEES表,您需要查看在13年3月15日至10月15日的时间间隔内在部门100和101中雇用的所有员工的姓名和工资.哪两个查询会得到所需的结果? (选择两项.)

  1.  SELECT first_name,salary来自员工WHERE dept_id IN(100,101)AND hire_date BETWEEN '15 -MAR-12'''15 -OCT-12';


  2.  SELECT first_name,salary FROM employees WHERE dept_id = 100 OR dept_id = 101 AND hire_date> ='15-MAR-12'ORh hire_date <='15-OCT-12';


  3.  SELECT first_name,salary FROM employees WHERE(dept_id BETWEEN 100 AND 101)AND(hire_date IN('15 -MAR-12','15-OCT- 12'));


  4.  SELECT first_name,salary FROM employees WHERE(dept_id = 100 OR dept_id = 101)AND( hire_date> ='15-MAR-12'AND hire_date <='15-OCT-12');


答案:A,D.

49.使用EMPLOYEES表,您可以发出以下查询以生成名称,当前工资和评估后增加25%的工资.所有员工的工资增长应该在30000以上.

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


查询引发错误ORA-00904.错误的原因是什么?

  1. SELECT语句中使用的表达式中缺少括号.

  2. 必须使用单引号来定义列别名.

  3. 不能在WHERE子句中使用列别名.

  4. WHERE子句中的列别名必须用双引号括起来.

答案:C.列别名不能可以在WHERE子句条件中使用,但可以在SELECT语句和ORDER BY子句中使用.

50.您需要在EMPLOYEES表中显示属于Department id 100的员工姓名,最低工资为2000或4000且没有job_id.您发出以下查询.

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


关于上述查询,哪个陈述是正确的?

  1. 它成功执行但没有返回结果.

  2. 它成功执行并返回

  3. 它会生成错误,因为为job_id指定的条件无效.

  4. 它会生成错误,因为为薪水栏无效.

答案:A.条件(薪水= 2000或薪水= 4000)导致FALSE因为员工一次不能支付多个工资.

51.使用Oracle数据库内置的SQL函数可以执行哪三项任务? (Choose three.)

  1. Displaying a date in a non-default format

  2. Finding the number of characters in an expression

  3. Substituting a character string in a text expression with a specified string

  4. Combining more than two columns or expressions into a single column in the output

Answer: A, B, C. Use formatting functions (TO_CHAR, TO_DATE), and character functions (LENGTH, REPLACE) to achieve the objectives.

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?

  1. It executes and produces the required result.

  2. It produces an error because an expression cannot be used in the ORDER BY clause.

  3. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clause.

  4. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clause.

Answer: A. The ORDER BY clause can contain column expressions.

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 \t                 01-JAN-10 \t\t\t1000  22 \t\t  01-FEB-10 \t\t\t2000  33 \t\t  01-MAR-10 \t\t\t3000


 

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

  1. SELECT start_date + ’10’ FROM trainings;
  2. SELECT * FROM trainings WHERE start_date = ’01-01-10’;
  3. SELECT training_cost FROM trainings WHERE training_id> ’11’;
  4. SELECT * FROM trainings WHERE start_date =’01-JANUARY-10’;

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;

 

  1. ORDER BY clause should contain only those columns which are in the SELECT statement.

  2. The above query will sort the result set in descending order.

  3. ORDER BY clause can contain any column in the related table, not necessarily the columns in the SELECT statement.

  4. It throws an error on execution.

Answer: C. The ORDER BY clause can use a column to sort the data which is not selected in the column list but is contained in the table used in FROM clause.

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

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

 

  1. ORDER BY clause should contain only those columns which are in the SELECT statement.

  2. The above query will sort the result set in descending order of first names of employees.

  3. ORDER BY clause works with column aliases.

  4. The SELECT query throws an error on execution because column alias cannot be used in ORDER BY clause.

Answer: C. The ORDER BY clauses works fine with the column aliases used in SELECT statement.

56. What is true about the query given below?

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

 

  1. It executes successfully sorting the query results based on the JOB_ID.

  2. The ORDER BY clause cannot contain a numeric.

  3. The ORDER BY clause will not function as none of the columns in the SELECT statement are used in the ORDER BY clause.

  4. The query throws an error on execution.

Answer: A. Numeric position of the column can be used in the ORDER BY clause.

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* Plus?

  1. Substitute each Job Id at a time

  2. Use * to list details of all employees

  3. Use &JOB to prompt for user input every time the query is executed

  4. Declare session variables to substitute Job Id values in the query

Answer: C. The &X notation haults the query execution and prompts for user input every time the query is executed.

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

  1. The same query can be executed for different values using the substitution variables.

  2. Using the substitution variables, one needs to alter the WHERE clause every time.

  3. Substitution variables are not supported in Oracle.

  4. There is a limitation that a value is supposed to be entered in the substitution variables every time during the execution of a query.

Answer: A.

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

  1. VARCHAR2

  2. DATE

  3. NO DATA TYPE

  4. NUMBER

Answer: C. Substitution variables do not have the data type of their own but comply with the column’s data type with whom they are used.

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

  1. The value entered in the variables remains constant and the user cannot change the values after the execution of the query for the first time.

  2. The value is stored in the variables after the query executes once.

  3. The substitution variables only support NUMBERS.

  4. The value stored in the substitution variables (using a single ampersand) is used for the first execution and gets discarded.

Answer: D.

61. Which of the following is a correct syntax for Substitution variables in SQL* Plus?

  1. :var

  2. $var

  3. &var

  4. &&var

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?

  1. &&var

  2. &var

  3. :var

  4. ::var

Answer: A. A substitution variable with double ampersand repeatedly uses the value once provided by the user.

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

  1. Only NUMBERS can be entered as values.

  2. Only Character strings can be entered as values.

  3. Both Numbers and Characters can be entered as values.

  4. None of the above.

Answer: C.

64. What is true about the query given below?

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

 

  1. It throws an error "ORA-00904: "&eid": invalid identifier"

  2. It executes successfully.

  3. The WHERE clause can’t have substitution variables.

  4. The query prompts for a value to be entered for the variable &eid and executes successfully taking a valid value of employee_id.

Answer: 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;

 

  1. It throws an error as the use of the substitution variable prompt_col is not allowed.

  2. It executes successfully but the result set is not sorted.

  3. It executes successfully but the variable value entered in the SELECT statement is ignored.

  4. It executes successfully and the value of the substitution variable is maintained throughout the session.

Answer: D. A substitution variable can be used in all the clauses of SQL query.

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

  1. &var

  2. &&var

  3. SET

  4. DEFINE

Answer: D. Use DEFINE command in SQL* Plus to declare a substitution variable in a session.

67. What will be the outcome of the below activity in SQL* Plus?

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

 
 

  1. The SELECT query throws error as substitution variables cannot be defined in the session.

  2. It prompts the user to enter the value for the variable &eid.

  3. It executes successfully with the employee ID substituted as 117.

  4. It ignores the DEFINE command because the substitution variable is declared without ampersand (&) sign.

Answer: C.

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

  1. UNDEFINE

  2. SET OFF

  3. DELETE

  4. CLEAR

Answer: A. Use UNDEFINE command to delete a substitution variable from the session

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

  1. DEFINE

  2. UNDEFINE

  3. SHOW VARIABLE

  4. VERIFY

Answer: D.Use VERIFY command in SQL*Plus and SQL Developer to check the substitution of values using substitution variables.

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

  1. >=

  2. IS NULL

  3. !=

  4. IS LIKE

Answer: A, B, C.

71. Evaluate the following query:

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


What happens when the above query is executed?

  1. Gives an error because braces cannot be used with [q] operator

  2. Gives error due to data type mismatch

  3. Executes successfully and adds an apostrophe (’s) at the end of each employee’s name

  4. Executes successfully and appends the employee name with the literal " {’s start date was } "

Answer: C.

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

  1. WHERE deptno IS 20

  2. WHERE deptno 20

  3. WHERE deptno=20

  4. WHERE 20=deptno

Answer: C, D. The equality operator (=) is used to compare the operands in the condition for equality.

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

  1. SELECT ename, sal FROM emp WHERE sal GREATER THAN 1000
  2. SELECT ename, sal FROm emp WHERE sal> 1000
  3. SELECT ename, sal FROM emp WHERE sal>= 1000
  4. SELECT ename, sal FROM emp WHERE sal MORE THAN 1000

Answer: B. The greater than operator (>) is used to compare the operands in the condition.

74. What would happen when the below query is executed in SQL* Plus?

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


  1. Executes successfully and lists the employees whose 10th part of salary is greater than 10 times his department number

  2. Raises error because expressions must be enclosed with parentheses

  3. Raises error because WHERE clause cannot evaluate expressions

  4. Raises error because WHERE clause cannot use literals

Answer: A. The WHERE clause can contain expressions.

75. Determine the error in the below SELECT statement

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


  1. WHERE clause cannot refer the column JOB since it doesn’t appears in the SELECT column list

  2. Character literal CLERK must be enclosed within single quotes

  3. Character literal CLERK must be enclosed within parentheses

  4. No error in the query

Answer: B. Character literals must be enclosed within single quotes

76. Interpret the output of the below SQL query

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


  1. The query lists the employees whose hiredate is atleast 100 days earlier than the current date

  2. The query lists the employees who have worked more than 100 days in the company

  3. The query lists the employees whose hiredate is after 100 days in that year

  4. The query lists the employees who have spent less than 100 days in the company

Answer: A, B. Dates expressions can be used in WHERE clause

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

  1. SELECT ename, deptno FROM emp WHERE hiredate> ’31-DEC-1982’;
  2. SELECT ename, deptno FROM emp WHERE hiredate> to_date(’31-DEC-1982’,’DD-MM-YYYY’);
  3. SELECT ename, deptno FROM emp WHERE hiredate> to_char(’31-DEC-1982’,’DD-MM-YYYY’);
  4. SELECT ename, deptno FROM emp WHERE hiredate> 31-DEC-1982;

Answer: A, B. Date literals must be enclosed within single quotes.

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

  1. WHERE sysdate-hiredate=0

  2. WHERE sysdate=hiredate

  3. WHERE sysdate-hiredate<1

  4. WHERE to_date (sysdate,’DD-MON-YYYY’) = to_date (hiredate=’DD-MON-YYYY’)

Answer: C, D. The condition SYSDATE=HIREDATE will not work because SYSDATE contains dynamic timestamp component while hiredate is a static value in the database.

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

  1. 24/Mar/95

  2. 02-12-1983

  3. 19-JUN-2001

  4. 31.04.2010

Answer: A, C. Default format for date literals is DD-MON-RR.