Font size
WorksheetsFCPC-DBMS2-Prelim Exam
Total questions: 50
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.
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
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;
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 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.
Out of the below clauses, where can the single-row functions be used?
SELECT
WHERE
ORDER BY
All of the above
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
The SQL statement to count distinct rows from a column is,
select COUNT DISTINCT columnname AS total_rows from tablename;
select (COUNT DISTINCT columnname) AS total_rows from tablename;
select DISTINCT columnname AS total_rows from tablename;
select COUNT (DISTINCT) AS total_rows from tablename;
The COUNT() function also counts the NULL values.
True
False
The SUM() function returns - - -
some random values from the given column
the total number of columns in the database
the total number of tables in the given databases
the total sum of a given numeric column
The average value of the given numeric column is calculated by using - - - function.
GETAVG()
FINDAVG()
AVG()
AVGVALUE()
Which one of the following is an Multiple row Functions?
COUNT()
LEN()
AVERAGE()
NOW()
A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.
SELECT COUNT (*) FROM t_count;
12
6
9
Throws exception because COUNT function doesn't works with NULL values
1. To perform condition based on a list, we need to use ____________ clause
between
select
in
1. All aggregate functions ignore the __________________ value.
similar
NULL
column
1. The OR operator displays a record if ANY of the conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true
True
False
Which of the following are single row functions:
SUBSTR( )
SUM( )
TRIM( )
ROUND( )
1. The HAVING clause places conditions on _____________
Columns
Tables
Group
