SQL: Using Single-Row Functions

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;