Font size
WorksheetsSQL: Using Single-Row Functions
Total questions: 100
Worksheet time: 50mins
What will be the outcome of the following query?
SELECT ROUND(144.23,-1) FROM dual;
140
144
150
100
You are currently located in New Jersey and have connected to a remote database in San Diego. You issue the following command.
SELECT ROUND (sysdate-hire_date,0) FROM employees WHERE (sysdate-hire_date)/180 = 2;
What is the outcome of this query?
An error because the ROUND function cannot be used with Date arguments.
An error because the WHERE condition expression is invalid.
Number of days since the employee was hired based on the current San Diego date and time.
Number of days since the employee was hired based on the current New Jersey date and time.
You need to display the names of the employees who have the letter 's' in their first name and the letter 't' at the second position in their last name. Which query would give the required output?
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';
Which of the following statements is true regarding the COUNT function? (Select all that apply)
COUNT (*) counts duplicate values and NULL values in columns of any data type.
COUNT function cannot work with DATE datatypes.
COUNT (DISTINCT job_id) returns the number of rows excluding rows containing duplicates and NULL values in the job_id column.
A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
Examine the structure of the EMPLOYEES table as given. What will be the outcome of the following query?
SELECT last_name, NVL(job_id, 'Unknown') FROM employees WHERE last_name LIKE 'A%' ORDER BY last_name;
It will throw an ORA error on execution.
It will list the job IDs for all employees from EMPLOYEES table.
It will list the job IDs of all employees and substitute NULL job IDs with a literal 'Unknown'.
It will display the last names for all the employees and their job IDs including the NULL values in the job ID.
What will be the outcome of the following query? (Consider the structure of the EMPLOYEES table as given)
SELECT employee_id , NVL(salary, 0) FROM employees WHERE first_name like 'P%' ORDER BY first_name;
It will display 0 in the salary column for all the employees whose first name starts with a 'P'
It will display the salaries for the employees whose name start with a 'P' and 0 if the salaries are NU
It will throw an ORA error as the ORDER BY clause should also contain the salary column.
The NVL function should be correctly used as NVL (0, salary)
What will be the outcome of the following query? (Consider the structure of the EMPLOYEES table as given)
SELECT NVL2(job_id,'Regular Employee','New Joinee') FROM employees;
It will return the value 'Regular Employee' for all the employees who have NULL job IDs
It will return the value 'New Joinee' for all the employees who have NULL job IDs
It will return 'Regular Employee' if the job ID is NULL
It will throw an ORA error on execution.
Examine the structure of the EMPLOYEES table as given.
What will be the outcome of the query mentioned below?
SeLECT first_name, salary, NVL2(commission_pct, salary + (salary * commission_pct), salary) "Income" FROM employees WHERE first_name like 'P%' ORDER BY first_name;
Salary will be returned if the Commission for the employee is NOT NULL.
Commission_pct will be returned if the Commission for the employee is NOT NULL.
Employees with the first name starting with 'P' and salary+(salary*commission_pct) will be returned if the employee earns a commission.
The query throws an error because a mathematical expression is written inside NVL2.
Examine the structure of the EMPLOYEES table as given. You need to create a report from the HR schema displaying employees who have changed jobs since they were hired. You execute the query given below.
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;
What will be the outcome of the query given above?
It will display the old job ID when the new job ID is NULL.
It will execute successfully and produce the required output.
It will display the new job ID if the new job ID is equal to the old job ID
It will throw an ORA error on execution.
Which of the following queries will give the same result as given in the query given below?
SELECT CONCAT(first_name, last_name) FROM employees;
(Select all that apply)
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 employees;
What will be the outcome of the following query?
SELECT 'The job id for '||upper(last_name) ||' is a '||lower(job_id) FROM employees;
The job id for ABEL is a sa_rep
The job id forABEL is a sa_rep
The job id for abel is SA_REP
The job id for abel is sa_rep
Assuming the last names of the employees are in a proper case in the table employees, what will be the outcome of the following query?
SELECT employee_id, last_name, department_id FROM employees WHERE last_name = 'smith';
It will display the details of the employee with the last name as Smith
It will give no result.
It will give the details for the employee having the last name as 'Smith' in all Lower case.
It will give the details for the employee having the last name as 'Smith' in all INITCAP case.
Examine the structure of the EMPLOYEES table as given. 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
Examine the structure of the EMPLOYEES table as given here. 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 all that apply)
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';
Examine the structure of the EMPLOYEES table as given here. 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 all that apply)
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;
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;
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 all that apply)
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;
Examine the structure of the EMPLOYEES table as given here. 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 all that apply)
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;
Examine the structure of the EMPLOYEES table as given below. 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 all that apply)
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;
Which of the following commands is used to count the number of rows and non-NULL values in Oracle database? (Select all that apply)
NOT NULL
INSTR
SUBSTR
COUNT
Which of the following statements are true regarding the single row functions? (Select all that apply)
They accept only a single argument.
They can be nested only to two levels.
Arguments can only be column values or constants.
They can return a data type value different from the one that is referenced.
Which of the below queries will format a value 1680 as $16,80.00? (Select all that apply)
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;
Which of the following commands allows you to substitute a value whenever a NULL or non-NULL value is encountered in an SQL query? (Select all that apply)
NVL
NVLIF
NVL2
LNNVL
Which of the following type of single-row functions cannot be incorporated in Oracle DB? (Select all that apply)
Character
Numeric
Conversion
None of the above
Out of the below clauses, where can the single-row functions be used?
SELECT
WHERE
ORDER BY
All of the above
What is true regarding the NVL function in Oracle DB? (Select all that apply)
The syntax of NVL is NVL (exp1, exp2) where exp1 and exp2 are expressions.
NVL (exp1, exp2) will return the value of exp2 if the expression exp1 is NULL.
NVL (exp1, exp2) will return the value of the expression exp2 if exp1 is NOT NULL.
NVL (exp1, exp2) will return exp1 if the expression exp2 is NULL.
Which of the following statements is true regarding the NVL statement? SELECT NVL (arg1, arg2) FROM dual; (Select all that apply)
The two expressions arg1 and arg2 should only be in VARCHAR2 or NUMBER data type format.
The arguments arg1 and arg2 should have the same data type
If arg1 is VARCHAR2, then Oracle DB converts arg2 to the datatype of arg1 before comparing them and returns VARCHAR2 in the character set of arg1.
An NVL function cannot be used with arguments of DATE datatype.
Which of the following is true for the statement given as under. NVL2 (arg1, arg2, arg3) (Select all that apply)
Arg2 and Arg3 can have any data type
Arg1 cannot have the LONG data type
Oracle will convert the data type of expr2 according to Arg1
If Arg2 is a NUMBER, then Oracle determines the numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.
What is true about the NULLIF function in Oracle DB? (Select all that apply)
NULLIF(expr1,expr2) will return expr2 if the two expressions are NOT NULL.
NULLIF(expr1,expr2) will return 0 if the two expressions are NULL.
NULLIF(expr1,expr2) will return NULL if the two expressions are equal.
Expr1 can be NULL in NULLIF(expr1, expr2)
Pick the correct answer(s) given after the statement shown as under. NULLIF (arg1,arg2) (Select all that apply)
Arg1 and Arg2 can be of different data types.
Arg1 and Arg2 have to be equal in order to be used in the NULLIF function.
There is no internal conversion of data types if NULLIF used as in the case of NVL and NVL2.
This is equivalent to CASE WHEN Arg1 = Arg22 THEN NULL ELSE Arg1 END.
Which of the following is not a property of functions? (Select all that apply)
Perform calculations on data
Convert column data types
Modify individual data items
None of the above
