1.以下查询的结果是什么？
SELECT ROUND(144.23，-1)FROM dual;
140
144
150
100
答案:A. ROUND函数将根据值144.23舍入到指定的精度-1并返回140.
检查给定的EMPLOYEES表的结构并回答下面的问题2和3.
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)
2.您目前位于新泽西州，已连接到圣地亚哥的远程数据库.您发出以下命令.
SELECT ROUND(sysdate-hire_date，0)FROM employees WHERE(sysdate-hire_date)/180 = 2;
此查询的结果是什么？
错误，因为ROUND函数不能与Date参数一起使用.
错误，因为WHERE条件表达式无效.
根据当前圣地亚哥的日期和时间雇用员工以来的天数.
根据当前新泽西州的日期和时间雇用员工以来的天数.
答案:C. SYSDATE函数将获取远程连接的数据库的当前时间.您必须执行基本算术运算来调整时区.
3.您需要显示姓氏中带有字母's'和姓氏中第二个字母't'的员工姓名.哪个查询会提供所需的输出？
SELECT first_name，last_name FROM employees WHERE INSTR(first_name，'s')<> 0 AND SUBSTR(last_name，2,1)='t';
SELECT first_name，last_name FROM employees WHERE INSTR(first_name，'s')<>''AND SUBSTR(last_name，2,1)='t';
SELECT first_name，last_name FROM employees WHERE INSTR(first_name，'e')IS NOT NULL AND SUBSTR(last_name，2,1)='t';
SELECT first_name，last_name FROM employees WHERE INSTR(first_name，'e')<> 0 AND SUBSTR(last_name， LENGTH(first_name)，1)= 't';
答案:A. INSTR函数返回所需字符串中给定字符的位置. SUBSTR函数返回给定起始位置和结束位置的字符串中的字符集.
4.关于COUNT函数，以下哪项陈述是正确的？
COUNT(*)计算重复任何数据类型的列中的值和NULL值.
COUNT函数不能用于DATE数据类型.
COUNT(DISTINCT job_id)返回的数量不包括在job_id列中包含重复项和NULL值的行的行.
使用带有DISTINCT关键字的COUNT函数的SELECT语句不能包含WHERE子句.
答案:A. COUNT(*)函数返回表中满足SELECT语句条件的行数，包括重复的行和包含的行任何列中的空值.如果SELECT语句中包含WHERE子句，则COUNT(*)返回满足WHERE子句中条件的行数.相反，COUNT(expr)返回由expr标识的列中的非空值的数量. COUNT(DISTINCT expr)返回由expr标识的列中唯一的非空值的数量.
5.以下哪个命令用于计算Oracle数据库中的行数和非NULL值？
NOT NULL
INSTR
SUBSTR
COUNT
答案:D. COUNT(ALL column_name)用于计算除NULL之外的行数.类似地，COUNT(*)用于计算包括NULL的列值.
6.下面给出的查询的结果是什么？
SELECT 100 + NULL + 999 FROM dual;
100
999
NULL
1099
答案:C.任何带NULL的算术运算都会导致NULL.
7.关于单行函数，下列哪一项陈述是正确的？
他们只接受一个参数.
它们只能嵌套到两个级别.
参数只能是列值或常量.
他们可以返回与引用的数据类型值不同的数据类型值.
答案:D.单行函数可以使用多个参数，返回类型可以与输入的数据类型不同.
8.以下哪个查询会将值1680格式化为$ 16,80.00？
SELECT TO_CHAR(1680.00，'$ 99G99D99')FROM dual;
SELECT TO_CHAR(1680.00 ，'$ 9,999V99')FROM dual;
SELECT TO_CHAR(1680.00，'$ 9,999D99')FROM dual;
SELECT TO_CHAR(1680.00，'$ 99G999D99')FROM dual;
答案:A，D.格式型号$ 99G999D99将数字格式化为数字，组分隔符和小数.其他格式元素可以是前导零，小数位，逗号位置，本地货币，科学记数法和符号.
9.确定以下查询的输出.
SELECT RPAD(ROUND('78945.45')，10，'*')FROM dual;78945 *****
** 78945.45
函数RPAD不能与其他函数嵌套
78945.45 ****
答案:A. LPAD(字符串，数字，字符)和RPAD(字符串，数字，字符)函数在给定字符串的左侧或右侧添加一个字符，直到它到达填充后指定的长度(num). ROUND函数将值78945.45舍入到78945，然后用'*'填充它，直到达到10的长度.
10.每当在SQL查询中遇到NULL或非NULL值时，以下哪个命令允许您替换值？
NVL
NVLIF
NVL2
LNNVL
答案:C. NVL2函数至少需要三个参数. NVL2函数检查第一个表达式.如果它不为null，则NVL2函数返回第二个参数.如果第一个参数为null，则返回第三个参数.
11.以下哪种类型的单行函数不能合并到Oracle DB中？
字符
数字
转换
以上都不是
答案:D.单行函数的类型，如字符，数字，日期，转换和杂项，以及程序员编写的可以是
Oracle DB.
12.在以下条款中，可以使用单行函数吗？
SELECT
WHERE
ORDER BY
以上所有
答案:D.可以在SELECT语句，WHERE子句和ORDER BY子句中使用单行函数.
13.关于Oracle DB中的NVL函数有什么用？
NVL的语法是NVL( exp1，exp2)其中exp1和exp2是表达式.
如果表达式exp1为NULL，则NVL(exp1，exp2)将返回exp2的值.
<如果exp1为NOT NULL，则NVL(exp1，exp2)将返回表达式exp2的值.
如果表达式exp2为NULL，则NVL(exp1，exp2)将返回exp1./li>
答案:B. NVL函数用替代值替换空值.数据类型日期，字符和数字的列可以使用NVL提供
备用值.列的数据类型及其替代方法必须匹配.
14.检查给定的EMPLOYEES表的结构.
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 last_name, NVL(job_id, 'Unknown') FROM employees WHERE last_name LIKE 'A%' ORDER BY last_name;
执行时会抛出ORA错误.
它将列出来自EMPLOYEES表的所有员工的工作ID.
它将列出所有员工的工作ID，并用文字"未知"替换空工作ID.
它将显示所有员工的姓氏及其职务ID，包括职位ID中的NULL值.
答案:C. NVL函数用空值替换空值.数据类型日期，字符和数字的列可以使用NVL提供备用值.列的数据类型及其替代方法必须匹配.
15.以下查询的结果是什么？
SELECT NVL(NULL，'1')FROM dual;
NULL
1
0
给出错误，因为无法将NULL明确指定给NVL函数
答案:B. NVL会将NULL视为值并返回备用参数，即结果为1.
16.以下查询的结果是什么？ (考虑给定的EMPLOYEES表的结构)
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 employee_id，NVL(salary，0)FROM employees WHERE first_name like'P％' ORDER BY first_name;
它将在名字的所有员工的工资栏中显示0以'P'开头
它将显示姓名以'P'开头的员工的工资，如果工资为NULL，则显示0.
它会抛出一个ORA错误，因为ORDER BY子句也应该包含salary列.
NVL f unction应正确用作NVL(0，工资)
答案:B. NVL函数用空格替换空值替代价值.数据类型日期，字符和数字的列可以使用NVL提供
备用值.列的数据类型及其替代方法必须匹配.
17.关于NVL语句，以下哪项陈述是正确的？
SELECT NVL(arg1，arg2)FROM dual;
两个表达式arg1和arg2只能是VARCHAR2或NUMBER数据类型格式.
参数arg1和arg2应该具有相同的数据类型
如果arg1是VARCHAR2，那么Oracle DB会在比较它们之前将arg2转换为arg1的数据类型，并在字符集中返回VARCHAR2 arg1.
NVL函数不能与DATE数据类型的参数一起使用.
答案:C .如果arg1是VARCHAR2数据类型，则Oracle对arg2进行隐式类型转换，而arg2的数据类型为NUMBER.在所有其他情况下，两个参数必须具有相同的数据类型.
18.以下查询的结果是什么？ (考虑给定的EMPLOYEES表的结构)
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 NVL2(job_id，'Regular Employee'，'New Joinee')FROM employees;
它将为所有拥有NULL作业ID的员工返回值'Regular Employee'
它将为所有拥有NULL作业ID的员工返回值'New Joinee'
如果作业ID，它将返回'Regular Employee'是NULL
执行时会抛出ORA错误.
答案:B. NVL2功能ion检查第一个表达式.如果第一个表达式不为null，则NVL2函数返回第二个表达式.如果第一个表达式为null，则返回第三个表达式.
19.对于下面给出的声明，以下哪一项是正确的.
NVL2(arg1，arg2，arg3)
Arg2和Arg3可以有任何数据类型
Arg1不能拥有LONG数据类型
Oracle将根据Arg1转换expr2的数据类型
如果Arg2是NUMBER，则Oracle确定数字优先级，隐式转换另一个参数到该数据类型，并返回该数据类型.
答案:D. arg2和arg3参数的数据类型必须是兼容，并且它们不能是LONG类型.它们必须属于同一类型，或者必须能够将arg3转换为arg2参数的类型. NVL2函数返回的数据类型与arg2参数的数据类型相同.
20.检查给定的EMPLOYEES表的结构.
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 first_name，salary，NVL2(commission_pct，salary +(salary * commission_pct)，salary)"Income" FROM employees WHERE first_name like'P ％' ORDER BY first_name;
如果委员会将退还工资员工不是空.
如果员工委员会不是NULL，将返回Commission_pct.
名字以'P开头的员工如果员工获得佣金，将返回'和工资+(salary * commission_pct).
查询会因为在NVL2中写入数学表达式而抛出错误.
答案:C. NVL2函数检查第一个表达式.如果第一个表达式不为null，则NVL2函数返回第二个表达式.如果第一个表达式为null，则返回第三个表达式.
21. Oracle DB中的NULLIF函数有什么用？
NULLIF(expr1，expr2)将如果两个表达式都是非NULL，则返回expr2.
如果两个表达式为NULL，则NULLIF(expr1，expr2)将返回0.
NULLIF(如果两个表达式相等，则expr1，expr2)将返回NULL.
在NULLIF中，Expr1可以为NULL(expr1，expr2)
答案:C. NULLIF函数测试两个术语是否相等.如果它们相等，则函数返回null，否则返回测试的两个术语中的第一个. NULLIF函数接受任何数据类型的两个必需参数.语法是NULLIF(arg1，arg2)，其中参数arg1和arg2进行比较.如果它们相同，则返回NULL.如果它们不同，则返回arg1.
22.选择在下面显示的语句后给出的正确答案.
NULLIF(arg1，arg2)
Arg1和Arg2可以是不同的数据类型.
Arg1和Arg2必须相等在NULLIF函数中使用.
如果使用NULLIF，则没有内部转换数据类型，如NVL和NVL2一样.
这相当于CASE WHEN Arg1 = Arg22 THEN NULL ELSE Arg1 END.
答案:D.
23.检查给定的EMPLOYEES表的结构.
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)
您需要从HR模式创建一个报告，显示自雇用以来已更改工作的员工.您执行下面给出的查询.
SELECT e.last_name，NULLIF(e.job_id，j.job_id，"Old Job ID") FROM employees e，job_history j WHERE e.employee_id = j.employee_id ORDER BY last_name;
查询的结果是什么如上所述？
当新作业ID为NULL时，它将显示旧作业ID.
它将成功执行并产生所需的输出.
如果新作业ID等于旧作业，它将显示新作业ID ID
执行时会抛出ORA错误.
答案:D.
24.以下哪项不是函数的属性？
执行数据计算
转换列数据类型
修改单个数据项
以上都不是
答案:D.函数可以执行计算，执行大小写转换和类型转换.
25.单行函数最合适的是什么？
它们没有返回值
它们每行返回一个结果并对表的所有行进行操作.
它们每行返回一个带有输入参数的结果
他们每组行返回一个结果并对多行进行操作.
答案:B.单行函数总是每行返回一个结果，它们只在单行上运行;因此，他们会给出名称‘ Single Row'.
26.以下是一种Oracle SQL函数？
多行函数
单列函数
单值函数
多列函数
答案:A.基本上有两种类型的功能 - 单行和多行功能.
27 .以下是一种单行函数？
VARCHAR2
字符
LONG
NULLIF
答案:B和D. As Character和NULLIF是单行函数，rest是数据类型.
28.多行函数最合适的是什么？
它们每行返回多个值.
他们为每组行返回一个结果并可以操作行组.
他们每行返回一个结果并可以操作行组.
他们为每组行返回多个值.
答案:B.多行函数始终对一组行起作用，并为每组行返回一个值.
29.以下哪项也称为组功能？
单行功能
多组功能
多行功能
单组功能.
答案:C.组功能与多行功能和聚合功能相同.
30.关于单行函数，以下哪一项是正确的？
它们可以嵌套
他们接受参数并返回多个值.
他们无法修改数据类型
他们无法接受表达式作为参数.
答案:A.单行函数可以嵌套到多个级别.
31.单行函数接受的参数数量是多少？
0
仅1
仅2
1或超过1
答案:D.单行函数可以接受一个或多个参数，具体取决于它们所服务的目标.
32.以下哪一项可以作为单行函数的参数？
数据类型
SELECT语句
表达式
表名
答案:C.用户提供的常量，变量值，列值和表达式是单行函数的参数类型.
33.字符函数的真实情况是什么？
它们只返回字符值
他们接受NUMBER个值
他们接受字符参数并且可以返回字符和数字值
他们接受所有数据类型的值
答案:C.字符函数INSTR接受字符串值但返回字符串中字符的数字位置.
34.关于数字函数的真实情况是什么？
它们同时返回Character和Number值
他们不能接受表达式作为输入
数字函数不能嵌套.
他们接受数字参数和仅返回数字值.
答案:D.
35.以下哪项是DATE类型单行函数返回值的例外？
TO_DATE
SYSDATE
MONTHS_BETWEEN
TO_NUMBER
答案:C.所有DATE数据类型函数都返回DATE作为返回值，MONTHS_BETWEEN除外，它返回一个数字.
36.以下哪项不是转换类型单行功能？
TO_CHAR
TO_DATE
NVL
TO_NUMBER
答案:C.转换函数将值从一种数据类型转换为另一种数据类型. NVL函数用替代值替换空值.
37.以下哪一项是案例转换字符函数？
CONCAT
SUBSTR
INITCAP
REPLACE
答案:C. CONCAT，SUBSTR和REPLACE是字符操作字符函数，而INITCAP，LOWER和UPPER是转换字符函数
.
38.以下查询的结果是什么？
SELECT lower('HI WORLD !!!')FROM dual;Hi World !!!
Hi WORLD !!!
hi world !!!
HI WORLD !!!
答案:C. LOWER函数将字符串转换为小写字符.
39.以下查询的结果是什么？
SELECT lower(upper(initcap('Hello World')))FROM dual;Hello World
HELLO world
你好世界
你好世界
答案:D.大小写转换字符可以嵌套在SELECT查询中.
检查给定的EMPLOYEES表的结构，并回答下面的问题40到42.
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)
40.以下哪些查询将给出与下面给出的查询中给出的结果相同的结果？
SELECT CONCAT(first_name，last_name)FROM employees;
SELECT first_name || last_name FROM employees;
SELECT first_name ||''|| last_name FROM employees;
SELECT last_name ||'，'|| first_name FROM employees;
SELECT first_name ||'，'|| last_name FROM员工;
答案:A. CONCAT函数连接两个字符串，中间没有任何空格.
41.以下查询的结果是什么？
SELECT 'The job id for '||upper(last_name) ||' is a '||lower(job_id) FROM employees;
ABEL的作业ID是sa_rep
作业ID forABEL是sa_rep
abel的作业ID是SA_REP
abel的作业ID是sa_rep
答案:A.
42.假设员工的姓氏在表员工中处于适当的情况，以下查询的结果是什么？
SELECT employee_id ，last_name，department_id FROM employees WHERE last_name ='smith';
它将显示详细信息姓氏为史密斯的员工
没有结果.
它会给出姓氏为'的员工的详细信息所有小写字母中的史密斯.
它将在所有INITCAP案例中提供姓氏为"史密斯"的员工的详细信息.
答案:B.如果雇员表中的姓氏属于正确的情况，则条件WHERE last_name ='smith'将不会令人满意，因此没有结果显示.
43. Oracle DB中的CONCAT功能是什么？
它只能输入字符作为输入.
它只能有2个输入参数.
它可以有2个或更多输入参数
默认情况下，它通过在连接字符串之间放置一个空格来连接值.
答案:B. CONCAT函数接受只有两个NUMBER或VARCHAR2数据类型的参数.
44. Oracle DB中的SUBSTR函数有什么用？
它提取一个确定长度的字符串
它将字符串的长度显示为数值
它找到命名字符的数字位置
从字符串中修剪一侧(或两侧)的字符
答案:A. SUBSTR( string，x，y)函数接受三个参数，并返回一个字符串，该字符串由从源字符串中提取的字符数组成，从指定的起始位置(x)开始.当position为正数时，函数从string的开头开始计算以找到第一个字符.当position为负数时，该函数从字符串结尾开始向后计数.
45. What will be the outcome of the following query?
SELECT length(’hi’) FROM dual;
2
3
1
hi
Answer: A. the LENGTH function simply gives the length of the string.
46. What is the difference between LENGTH and INSTR functions in Oracle DB?
They give the same results when operated on a string.
LENGTH gives the position of a particular character in a string
INSTR gives the position of a particular character in a string while LENGTH gives the length of the string.
LENGTH and INSTR can be used interchangeably.
Answer: C.
47. Examine the structure of the EMPLOYEES table 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)
What will be the outcome of the following query?
SELECT upper(&jobid) FROM employees;
It results in an error as substitution variables cannot be used with single row functions
It prompts the user to input the jobid on each execution and then displays the job id in UPPER case
It gives the jobid as it is present in the table EMPLOYEES without making any change
It will not ask the user to input the job id and will convert all the job IDs in the table in UPPER case
Answer: B. Substitution variables can be used with the UPPER and LOWER functions.
48. What is false about the table DUAL in Oracle database?
It is owned by the user SYS and can be access by all the users.
It contains only one column and one row.
The value in the DUMMY column of the DUAL table is ’X’
The DUA L table is useful when you want to return a value only once
Answer: C. The DUAL table has one column named DUMMY and one row which has a value ’X’.
49. What will be the result of the following query?
SELECT sysdate+4/12 FROM dual;
The query produces error.
No of hours to a date with date as the result.
Sysdate arithmetic is ignored.
Returns the system date as result.
Answer: B. Arithmetic operations can be performed on dates in the Oracle DB.
50. What will be the outcome of the following query?
SELECT lower (100+100) FROM dual;
100
100+100
ORA error
200
Answer: D. Arithmetic expressions can be specified within case conversion functions.
51. What will be the outcome of the following query if the SYSDATE = 20-MAY-13?
SELECT upper (lower (sysdate)) FROM dual;
20-may-2013
ORA error as LOWER and UPPER cannot accept date values.
20-MAY-13
20-May-13
Answer: C. The functions UPPER and LOWER can accept date type inputs and will yield the same result as they do on Strings.
52. What is the result of the following query?
SELECT INITCAP (24/6) FROM dual;
4
24
24/6
No result
Answer: A. Arithmetic expressions can be specified within case conversion functions.
53. Examine the structure of the EMPLOYEES table as given here.
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)
You need to display the last name of all employees which starts with the letter ’A’. Which of the following queries will yield the required result?
SELECT INITCAP (last_name||’ works as a ’||job_id "Job Description" FROM employees WHERE initcap (last_name) like ’A%’;
SELECT INITCAP (last_name) ||INITCAP(’ works as a: ’)|| INITCAP(job_id) "Job Description" FROM employees WHERE initcap (last_name) like ’A %’;
SELECT INITCAP (last_name||’ works as a ’||INITCAP(job_id)) "Job Description" FROM employees WHERE initcap (last_name) = ’A’;
SELECT UPPER (LOWER (last_name||’ works as a ’||job_id)) "Job Description" FROM employees WHERE lower (last_name) = ’A’;
Answer: A, B.
54. Assuming the SYSDATE is 20-FEB-13, What will be the outcome of the following query?
SELECT CONCAT (’Today is :’, SYSDATE) FROM dual;
Today is : 20-feb-13
The query throws error of incompatible type arguments.
Today is : 20-Feb-13
Today is : 20-FEB-13
Answer: D. The CONCAT function accepts arguments of all types.
55. What will be the result pattern of the following query?
SELECT CONCAT(first_name, CONCAT (last_name, job_id)) FROM dual;
First_namelast_namejob_id
First_name, last_name, job_id
Error as CONCAT cannot be nested
First_namelast_name, job_id
Answer: A. The CONCAT function can be nested with self or other character function.
56. Examine the structure of the EMPLOYEES table as given here.
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)
You need to generate a report which shows the first name, last name and the salary for all the employees in the department 100. The report should show the results in the form ’Andy Smith earns 50000’. Which of the following queries will give the required output?
SELECT concat (first_name,concat (’ ’, concat(last_name, concat(’ earns ’, SALARY)))) Concat_String FROM employees WHERE department_id = 100;
SELECT concat (first_name, last_name||’ ’|| salary) FROM employees WHERE department_id = 100;
SELECT concat (first_name, concat(last_name, ’ ’))||earns||salary FROM employees WHERE department_id = 100;
SELECT concat (first_name, concat(last_name, ’earns salary’) FROM employees WHERE department_id = 100;
Answer: A. The CONCAT function can be nested with self or other character function.
57. What will the following query show as a result?
SELECT LENGTH(’It is a lovely day today!’) FROM dual;
25
19
20
0
Answer: A. The LENGTH functions counts blank spaces, tabs and special characters too.
58. You need to display the country name from the COUNTRIES table. The length of the country name should be greater than 5 characters. Which of the
following queries will give the required output?
SELECT country_name FROM countries WHERE LENGTH (country_name)= 5;
SELECT country_name FROM countries WHERE length (country_name)> 5;
SELECT SUBSTR(country_name, 1,5) FROM countries WHERE length (country_name)<5;
SELECT country_name FROM countries WHERE length (country_name) <> 5;
Answer: B. The LENGTH function can be used in WHERE clause.
59. How does the function LPAD works on strings?
It aligns the string to the left hand side of a column
It returns a string padded with a specified number of characters to the right of the source string
It aligns character strings to the left and number strings to right of a column
It returns a string padded with a specified number of characters to the left of the source string
Answer: D. The LPAD(string, length after padding, padding string) and RPAD(string, length after padding, padding string) functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.
60. Which of the following options is true regarding LPAD and RPAD functions?
The character strings used for padding include only characters.
The character strings used for padding include only literals
The character strings used for padding cannot include expressions.
The character strings used for padding include literals, characters and expressions.
Answer: D.
61. What is the maximum number of input arguments in LPAD and RPAD functions?
1
2
3
0
Answer: C. LPAD and RPAD take maximum of 3 arguments. If there are 2 arguments given, the padding happens by spaces.
62. What will be the outcome of the following query?
SELECT lpad (1000 +300.66, 14, ’*’) FROM dual;
*******1300.66
1300*******
1300.66
****1300.66
Answer: A. To make the total length of 14 characters, the return value 1300.66 is padded with 7 asterisks (*) on the left.
63. What is true regarding the TRIM function?
It is similar to SUBSTR function in Oracle
It removes characters from the beginning or end of character literals, columns or expression
TRIM function cannot be applied on expressions and NUMBERS
TRIM function can remove characters only from both the sides of a string.
Answer: B. The TRIM function literally trims off leading or trailing (or both) character strings from a given source string. TRIM function when followed by TRAILING or LEADING keywords, can remove characters from one or both sides of a string.
64. You need to remove the occurrences of the character ’.’ and the double quotes ’"’ from the following titles of a book present in the table MAGAZINE.
"HUNTING THOREAU IN NEW HAMPSHIRE" THE ETHNIC NEIGHBORHOOD."
Which of the following queries will give the required result?
SELECT LTRIM(Title,’"’) FROM MAGAZINE;
SELECT LTRIM(RTRIM(Title,’."’),’"’) FROM MAGAZINE;
SELECT LTRIM (Title,’"THE’) FROM MAGAZINE;
SELECT LTRIM(RTRIM(Title,’."THE’),’"’) FROM MAGAZINE;
Answer: B. The LTRIM and RTRIM functions can be used in combination with each other.
65. What will be returned as a result of the following query?
SELECT INSTR(’James’,’x’) FROM dual;
1
2
0
3
Answer: C. INSTR function returns a 0 when the search string is absent in the given string.
66. What will be the outcome of the following query?
SELECT INSTR(’1$3$5$7$9$’,’$’,3,4)FROM dual;
2
10
7
4
Answer: B. INSTR function search for the 4th occurrence of ’$’ starting from the 3rd position.
67. What will be the result of the following query?
SELECT INSTR(’1#3#5#7#9#’, -3,2) FROM dual;
#5
#3
#7
#9
Answer: D. SUBSTR function will search 3 places starting from the end of string and will give 2 characters in the forward direction giving #9.
Examine the structure of the EMPLOYEES table as given below and answer the questions 68 and 69 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)
68. You need to extract a consistent 15 character string based on the SALARY column in the EMPLOYEES table. If the SALARY value is less than 15 characters long, zeros must be added to the left of the value to yield a 15 character string. Which query will fulfill this requirement?
SELECT rpad(salary, 15,0) FROM employees;
SELECT lpad(salary,15,0) FROM employees;
SELECT ltrim(salary,15,0) FROM employees;
SELECT trim(salary,15,0) FROM employees;
Answer: B. The LPAD and RPAD functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.
69. You need to display the last 2 characters from the FIRST_NAME column in the EMPLOYEES table without using the LENGTH function. Which of the
following queries can fulfill this requirement?
SELECT SUBSTR(first_name, 2) FROM employees;
SELECT SUBSTR(first_name, -2) FROM employees;
SELECT RTRIM(first_name, 2) FROM employees;
SELECT TRIM(first_name, 2) FROM employees;
Answer: B. The SUBSTR(string, x, y) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position (x). When position is positive, then the function counts from the beginning of string to find the first character. When position is negative, then the function counts backward from the end of string.
70. Assuming the SYSDATE is 13-JUN-13, what will be the outcome of the following query?
SELECT SUBSTR(sysdate,10,7) FROM dual;
3
N-13
0
NULL
Answer: D. The query will give a NULL as the position 10 to start with in the SYSDATE doesn’t exist.
71. Which of the following is used to replace a specific character in a given string in Oracle DB?
LTRIM
TRIM
TRUNC
REPLACE
Answer: D.
72. What will be the outcome of the following query?
SELECT replace(9999.00-1,’8’,88) FROM dual;
999
9998
99988
9999.88
Answer: C. The REPLACE function searches for ’8’ in 9998 and replaces it with ’88’.
73. Examine the structure of the EMPLOYEES table as given here.
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)
You need to retrieve the first name, last name (separated by a space) and the formal names of employees where the combined length of the first name and last name exceeds 15 characters. A formal name is formed by the first letter of the First Name and the first 14 characters of the last name. Which of the following queries will fulfill this requirement?
SELECT first_name, last_name ,SUBSTR(first_name, 1,1)||’ ’||SUBSTR(last_name, 1,14) formal_name FROM employees;
SELECT first_name, last_name ,SUBSTR(first_name, 1,14)||’ ’||SUBSTR(last_name, 1,1) formal_name FROM employees WHERE length (first_name) + length(last_name)<15;
SELECT first_name, last_name ,SUBSTR(first_name, 1,1)||’ ’||SUBSTR(last_name, 1,14) formal_name FROM employees WHERE length (first_name) + length(last_name) =15;
SELECT first_name, last_name ,SUBSTR(first_name, 1,1)||’ ’||SUBSTR(last_name, 1,14) formal_name FROM employees WHERE length (first_name) + length(last_name)> 15;
Answer: D.
74. What will be the outcome of the following query?
SELECT round(148.50) FROM dual;
148.50
140
150
149
Answer: D. if the decimal precision is absent, the default degree of rounding is 0 and the source is rounded to the nearest whole number.
75. Assuming the sysdate is 10-JUN-13, What will be the outcome of the following query?
SELECT trunc (sysdate,’mon’) FROM dual;
10-JUN-13
1-JUN-13
ORA error as the TRUNC function can’t have an input parameter when used with dates.
31-JUN-13
Answer: B. The date is truncated to the first day of the month. Similarly, it can be done for year also.
76. What will be the result of the following query?
SELECT trunc(1902.92,-3) FROM dual;
2000
1000
1901
1901.00
Answer: B.
77. What is the syntax of the MOD function in Oracle DB?
Mod(divisor,dividend)
MOD(divisor,1)
MOD(dividend,divisor)
None of the above
Answer: C. The MOD function is used to get the remainder of a division operation.
78. What will be outcome of the following query?
SELECT mod(100.23,-3) FROM dual;
ORA error
1.23
100
0
Answer: B. The MOD function gives the same answer for a positive divisor as well as a negative divisor.
79. Which of the following functions are used to differentiate between even or odd numbers in Oracle DB?
ROUND
TRUNC
MOD
REPLACE
Answer: C. The MOD function can be used to check whether a given number is even or odd. If MOD (num,2) returns zero, the number ’num’ is an even. If MOD (num,2) returns 1, the number ’num’ is odd.
80. Examine the structure of the EMPLOYEES table as 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)
You need to allocate the first 12 employees to one of the four teams in a round-robin manner. The employee IDs start with a 100. Which of the following queries will fulfill the requirement?
SELECT * FROM employees WHERE employee_id between 100 and 111 ORDER BY employee_id;
SELECT first_name, last_name, employee_id, mod(employee_id, 4) Team# FROM employees WHERE employee_id between 100 and 111 ORDER BY employee_id;
SELECT first_name, last_name,mod(employee_id, 2) Team# FROM employees WHERE employee_ID <> 100;
SELECT first_name, last_name, mod(employee_id, 4) Team# FROM employees WHERE employee_ID = 100;
Answer: B.
81. What will be the outcome of the following query?
SELECT SUBSTR(’Life is Calling’,1) FROM dual;
ORA error as there should be minimum 3 arguments to the SUBSTR function.
Life is Calling
NULL
Life
Answer: B. Calling the SUBSTR function with just the first two parameters results in the function extracting a string from a start position to the end of the given source string.
82. What is the default data format for the sysdate in SQL Developer?
DD-MON-YY
DD-MON-RR
DD/MON/RR
DD/MON/YYYY
Answer: C. For SQL*PLUS the default date format is DD-MON-RR.
83. Assuming the SYSDATE to be 10-JUN-2013 12:05pm, what value is returned after executing the below query?
SELECT add_months(sysdate,-1) FROM dual;
09-MAY-2013 12:05pm
10-MAY-2013 12:05pm
10-JUL-2013 12:05pm
09-JUL-2013 12:05pm
Answer: B. The ADD_MONTHS(date, x) function adds ’x’ number of calendar months to the given date. The value of ’x’ must be an integer and can be negative.
84. What value will be returned after executing the following statement? Note that 01-JAN-2013 occurs on a Tuesday.
SELECT next_day(’01-JAN-2013’,’friday’) FROM dual;
02-JAN-2013
Friday
04-JAN-2013
None of the above
Answer: C. The NEXT_DAY(date,’day’) finds the date of the next specified day of the week (’day’) following date. The value of char may be a number representing a day or a character string.
85. What is the maximum number of parameters the ROUND function can take?
0
1
2
3
Answer: C. If there is only one parameter present, then the rounding happens to the nearest whole number
86. Assuming the present date is 02-JUN-2007, what will be the century returned for the date 24-JUL-2004 in the DD-MON-RR format?
19
21
20
NULL
Answer: C. If the two digits of the current year and the specified year lie between 0 and 49, the current century is returned.
87. Assuming the present date is 02-JUN-2007, what will be the century returned for the date 24-JUL-94 in the DD-MON-RR format?
19
21
20
NULL
Answer: A. If the two digits of the current year lie between 0 and 49 and the specified year falls between 50 and 99, the previous century is returned.
88. Assuming the present date is 02-JUN-1975, what will be the century returned for the date 24-JUL-94 in the DD-MON-RR format?
19
21
20
NULL
Answer: A. if the two digits of the current and specified years lie between 50 and 99, the current century is returned by default.
89. Assuming the present date is 02-JUN-1975, what will be the century returned for the date 24-JUL-07 in the DD-MON-RR format?
19
21
20
NULL
Answer: C. if the two digits of the current year lie between 50 and 99 and the specified year falls between 0 and 49, the next century is returned.
90. How many parameters does the SYSDATE function take?
1
2
4
0
Answer: D. The SYSDATE is a pseudo column in Oracle.
91. What is true about the SYSDATE function in Oracle DB?
It returns only the system date
It takes 2 parameters at least.
The default format is DD-MON-YY
The default format of SYSDATE is DD-MON-RR and it returns the date and time of the system according to the database server.
Answer: D.
92. What will be the datatype of the result of the following operation?
"Date3 = Date1-Date2"
Date
Num1
0
NULL
Answer: B. Subtraction of two dates results in number of days.
93. What will be the datatype of the result of the following operation?
"Date2 = Date1-Num1"
Date
Num1
0
NULL
Answer: A. Subtraction of a number from a date value results in date.
94. What does a difference between two dates represent in Oracle DB?
The number of days between them
Difference in dates in not possible in Oracle DB
A date
NULL
Answer: A.
95. What will be the outcome of the following query?
SELECT months_between(’21-JUN-13’,’19-JUN-13’) FROM dual;
ORA error
A positive number
A negative number
0
Answer: C. If the first parameter is less than the second parameter, the MONTHS_BETWEEN returns a negative number.
96. What can be deduced if the result of MONTHS_BETWEEN (start_date,end_date) function is a fraction?
It represents the difference in number between the start date and end date.
The result cannot be a fractional number, it has to be a whole number.
NULL
It represents the days and the time remaining after the integer difference between years and months is calculated and is based on a 31-day month.
Answer: D.
97. You are connected to a remote database in Switzerland from India. You need to find the Indian local time from the DB. Which of the following will give the required result?
SELECT sysdate FROM dual;
SELECT round(sysdate) FROM dual;
SELECT trunc (sysdate) FROM dual;
SELECT current_date FROM dual;
Answer: D.
98. What will be the outcome of the following query?
SELECT months_between (to_date (’29-feb-2008’), to_date (’29-feb-2008 12:00:00’,’dd-mon-yyyy hh24:mi:ss’))*31 FROM dual;
Approximately 0
1
The query will throw an ORA error
0.5 days
Answer: D. The MONTHS_BETWEEN(date1, date2) finds the number of months between date1 and date2. The result can be positive or negative. If
date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.
99. What will be the outcome of the following query?
SELECT add_months (’31-dec-2008’,2.5) FROM dual;
31-feb-2009
28-feb-2009
31-mar-2009
15-jan-2009
Answer: B. the fractional part of 2.5 will be ignored and 2 months will be added to 31-dec-2012 which is 31-feb-2013 but as it is not a valid date, the result is 28-feb-2009.
100. You need to identify the date in November when the staff will be paid. Bonuses are paid on the last Friday in November. Which of the following will fulfill the requirement?
SELECT next_day (’30-nov-2012’ , ’Friday’) FROM dual;
SELECT next_day (’30-nov-2012’ , ’Friday’) -7 FROM dual;
SELECT last_day (’01-nov-2012’ ) FROM dual;
SELECT next_day (’30-nov-2012’ , ’sat’) -1 FROM dual;
Answer: B. The NEXT_DAY(date,’day’) and LAST_DAY (date,’day’) functions find the date of the next or last specified day of the week (’day’) following date. The value of char may be a number representing a day or a character string.
