Restricting and Sorting Data Questions and Answer

Restricting and Sorting Data Questions

 1. Which of the following clause is used to limit the number of rows retrieved from a SELECT query?

1. LIMIT
2. WHERE
3. AND
4. FROM

Answer: B. The WHERE clause is used to restrict the number of rows returned from a SELECT query.

2. Choose the database elements whose values can be compared in a WHERE clause of a SELECT query.

1. Column
2. Sequence
3. Procedure
4. Literal

Answer: A, D. The WHERE clause can be used to compare the values from columns, literals, arithmetic functions and functions.

3. What are the elements NOT contained in the WHERE clause predicate of the SELECT query?

1. Comparison operator
2. Comparison condition
3. Column Name
4. Table Name

Answer: D. Table Name is not required in the WHERE clause predicate.

4. Which of the following values can NOT be returned after evaluation of WHERE clause condition?

1. UNKNOWN
2. TRUE
3. FALSE
4. NULL

Answer: A. If the result of the condition in WHERE clause is not known, NULL is returned. In all other scenarios, either TRUE or FALSE is returned.

5. What is the minimum number of WHERE clauses that must be present in a SELECT query?

1. 1
2. 2
3. 0
4. 3

Answer: C. The WHERE clause is an optional clause in the SELECT query which is only used to restrict the number of rows.

6. What is the maximum number of WHERE clauses that can be included in a SELECT query?

1. 1
2. 2
3. 0
4. 3

Answer: A. The WHERE clause is an optional clause in the SELECT query which can be used only once to restrict the number of rows.

7. Which of the following statements are correct about the WHERE clause?

1. Column Alias can be used in WHERE clause to refer a column
2. Comparison operator is an optional element in WHERE clause condition
3. Functions can be used as operands in the WHERE clause
4. There can be multiple WHERE clause in a SELECT query

Answer: C. The WHERE clause must have comparison operator to evaluate the condition. It can use function as one of the operand. Only one WHERE clause is allowed in a SELECT query.

8. Write a SELECT query to list down unique departments from EMP table?

A.  SELECT deptno FROM emp;

B.  SELECT DISTINCT deptno FROM emp;

C.  SELECT DISTINCT (deptno) FROM emp;

D.  SELECT empno, DISTINCT deptno FROM emp;

Answer: B & C. The keyword DISTINCT is used to filter out the duplicate rows from the SELECT query.

9. Which of the following operations are permitted for date and timestamp columns?

1. Division
2. Addition
3. Subtraction
4. Concatenation

Answer: B, C, and D. Addition, subtraction and Concatenation are the operations permitted for date and timestamp columns.

10. From the below operators, which one of them holds the highest precedence level?

1. Division (/)
2. Multiplication (*)
3. Brackets ( () )
4. Subtraction

Answer: C. Expressions within the brackets hold the highest precedence level.

11. Interpret the output returned by the below SELECT query

SELECT ename, (sysdate - hiredate)

FROM emp;

1. Number of days in the current year
2. Number of days in the year when an employee was hired
3. Number of days spent by employee with the company
4. The query raises exception "ORA-00932: inconsistent datatypes: expected NUMBER got DATE"

Answer: C. The expression (sysdate-hiredate) returns the number of employment days of an employee with the company.

12. Which of the below statements correctly describle the DUAL table in Oracle?

1. DUAL table is a temporary table in Oracle database
2. DUAL table contains only one character type column known as DUMMY
3. DUAL table owned by SYS cannot be dropped
4. A table with the name DUAL can be created by a user in its own schema

Answer: B, C, D. The DUAL table in Oracle is owned by SYS and contains one column DUMMY of type VARCHAR2(1).

13. Determine the type of output returned by the below query

SELECT sysdate - hiredate

FROM emp

WHERE empno=7369;

1. DATE data type
2. NUMBER data type
3. VARCHAR2 data type
4. The query raises error since arithmetic operations cannot be performed on date columns

Answer: B. Subtraction between two dates results in numeric difference of days between the two dates

14. Which expressions do NOT return NULL values?

A.  SELECT ((10 + 20) * 50) + null from dual;

B.  SELECT 'this is a '||null||'test with nulls' from dual;

C.  SELECT null/0 from dual;

D.  SELECT null||'test'||null as “Test” from dual;

Answer: B, D. Any arithmetic operation with NULL results in NULL.

15. Determine the output of the below query

SELECT 'Tutorial''s Point compiles technical tutorials' FROM DUAL;

1. Tutorial''s Point compiles technical tutorials
2. Tutorial's Point compiles technical tutorials
3. 'Tutorial''s Point compiles technical tutorials'
4. Raises exception "ORA-01756: quoted string not properly terminated"

Answer: B.

16. Examine the TRAINING table as given below:

 Name                                      Null?    Type

 ----------------------------------------- -------- -------------

 TRAINING_ID                               NOT NULL NUMBER(5)

 TRAINING_LOCATION                                  NUMBER(7,2)

 START_DATE                                         DATE

 END_DATE                                           DATE

Which two SQL would execute successfully? (Choose two)

A.  SELECT NVL (ADD_MONTHS (END_DATE,1),SYSDATE) FROM training;

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

C.  SELECT NVL(MONTHS_BETWEEN(START_DATE,END_DATE),’In Progress’) FROM training;

D.  SELECT NVL(TO_CHAR(MONTHS_BETWEEN(START_DATE,END_DATE)),’In Progress’) FROM training;

Answer: A, D. Use NVL function to provide an alternate value to a column when NULL.

17. What does the selection of columns in a SELECT statement known as?

1. Retrieval
2. Selection
3. Projection
4. Limiting

Answer: C. Projection is the ability to select only the required columns in SELECT statement.

18. What does the restriction of rows returned by a SELECT statement known as

1. Retrieval
2. Projection
3. Restricting
4. Selection

Answer: C. Restricting is the ability to limit the number of rows by putting certain conditions.

19. Which of the following is true about the query given below?

SELECT col1, col2

FROM tab1

ORDER BY col1;

1. All the rows for the column COL1 will be sorted in the Descending order.
2. All the rows for the column COL1 will be sorted in the Ascending order.
3. The query will give an error as there is no WHERE clause in the query.
4. The query will give an error as the ORDER BY clause should include all the columns in the SELECT clause.

Answer: B. By default, the ORDER BY clause sorts the values in ascending order.

20. Which of the following is true about the SQL query given below?

SELECT col1,col2

FROM tab1

WHERE col1 = 'A'

ORDER BY col2 DESC, col1;

1. It will display the row which has the col1 value as 'A' ordered by the col1 in the descending order and then col2 in the descending order.
2. The ORDER BY clause will not work as the keyword DESC should be always written in the end of the ORDER BY clause and not in between as given in the query.
3. The above query will be sorted in descending order on the basis of col2 only and the use of col1 in the ORDER BY clause will be discarded.
4. It will display the row which has the col1 value as 'A' ordered by the col1 and then followed by col2 as the execution of the ORDER BY clause happens from the order of columns in the SELECT statement.

Answer: C. Since the COL1 is already filtered and fixed in the query as a scalar value, no sorting will happen on the basis of COL1.

21. What is true regarding the query given below?

SELECT col1, col2

FROM  tab1

ORDER BY col1,col2

WHERE col2 = 'B';

1. It executes successfully
2. It gives the required result with the COL2 value as 'B' but no ordering by the columns COL1 and COL2 because ORDER BY clause appears before the WHERE clause.
3. It will display the row which has the COL2 value as 'B' ordered by the COL1, COL2.
4. It throws an error as the ORDER BY clause cannot be written before the WHERE clause in Oracle.

Answer: D. The ORDER BY clause must appear after the WHERE clause in the SELECT statement

22. Which two clauses of the SELECT statement are necessary for Selection and Projection?

1. SELECT, FROM
2. ORDER BY, WHERE
3. SELECT, WHERE
4. SELECT, ORDER BY

Answer: C.

23. Which of the following WHERE clauses will NOT fit in the below SELECT query?

SELECT ename, deptno, sal

FROM emp;

1. WHERE HIREDATE IN ('02-JUN-2004');
2. WHERE SAL IN ('1000','4000','2000');
3. WHERE JOB IN (SALES,CLERK);
4. WHERE COMM BETWEEN 0.1 AND 0.5;

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

24. Choose the WHERE clause that extracts the DNAME values containing the character literal “er” from the DEPT table.

1. WHERE DNAME IN ('%e%r');
2. WHERE DNAME LIKE '%er%';
3. WHERE DNAME BETWEEN 'e' AND 'r';
4. WHERE DNAME CONTAINS 'e%r';

Answer: B. The LIKE operator is used to perform wild card search in SQL queries.

25. Which two of the following conditions are equivalent to each other?

1. WHERE comm IS NULL
2. WHERE comm = NULL
3. WHERE comm IN (NULL)
4. WHERE NOT(comm IS NOT NULL)

Answer: A, D. The NOT operator can be used to negate the effect of its operand. Therefore (COMM IS NULL) is equivalent to (NOT (COMM IS NOT NULL)).

26. Which of the following clauses are mandatory in an SQL query?

1. SELECT, FROM
2. SELECT,FROM,WHERE
3. SELECT,WHERE
4. SELECT,WHERE,ORDER BY

Answer: A. SELECT and FROM are the mandatory clauses in a SELECT query.

27. Which three of the following WHERE clause conditions are equivalent to each other?

1. WHERE SAL <=5000 AND SAL >=2000
2. WHERE SAL IN (2000,3000,4000,5000)
3. WHERE SAL BETWEEN 2000 AND 5000
4. WHERE SAL > 1999 AND SAL < 5001

Answer: A, C, D. The conditions can be made equivalent with the use of IN, BETWEEN and relational operators

28. Which of the following is true with respect to the below query?

SELECT empno, ename, job

FROM emp

WHERE ename like '_ith%';

1. It fetches the employee id, name and job of those employees who have 'ith' appearing anywhere in their name.
2. It fetches the employee id, name and job of those employees whose name starts with 'ith'.
3. The query throws an error as two expressions for string matching cannot be written together.
4. It fetches the employee id, name and job of those employees whose name starts with any alphanumeric character followed by 'ith' and any alphanumeric characters after 'ith'.

Answer: D.

29. Which of the following is used to end a SQL query?

1. :
2. ;
3. .
4. /

Answer: B, D. A semicolon (;) or backslash (/) is used to terminate a query in SQL* Plus and SQL Developer.

30. The employees JAMES and MILLER want to know their department id by querying the database. Which of the following queries will give the required result?

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

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

C.  SELECT ename, deptno FROM dept

D.  SELECT ename, deptno FROM emp WHERE ename = 'JAMES' OR ename = 'MILLER'

Answer: D. Multiple conditions can be joined using OR clause. Query execution is successful if either of the two is true.

31. Which of the following is false regarding the WHERE clause?

1. The WHERE can compare values in columns, literal, arithmetic expressions, or functions.
2. The WHERE clause contains column name
3. Column aliases can be used in the WHERE clause.
4. The WHERE clause cannot contain list of values or constants.

Answer: C, D.

32. What is the default date format in Oracle?

1. DD-MON-YY
2. DD-MON-YYYY
3. DD-MM-RR
4. DD-MON-RR

Answer: D. DD-MON-RR is the default date format in Oracle.

33. Predict the output of the below SQL query.

SELECT ename, deptno, sal, comm

FROM emp

WHERE job = 'SALES'

AND hiredate = ”01-JAN-97”;

1. It fetches the employee data for all SALES employees
2. It throws an error "ORA-00904: "01-JAN-13": invalid identifier"
3. Query executes successfully but no results are returned
4. It fetches the data for all SALES employees who were hired on 01st Jan, 1997

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

34. You need to display the names of all the employees having the first name as "GARRY" from the EMPLOYEES table. Which of the following queries will fulfill the requirement?

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

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

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

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

Answer: C. Wild Cards can be used if certain characters of the search string are unknown.

35. You need to display the employee ID of all the employees who contain a letter 's' in their last name at second position and department ID as 100. Which of the following queries will fetch the required results?

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

B.  SELECT emp_id FROM employees WHERE dept_id = 100 AND last_name LIKE '%s_';

C.  SELECT emp_id FROM employees WHERE dept_id = 100 AND last_name LIKE '_s_%';

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

Answer: D. The wildcard character underscore (_) is used to substitute a single character.

36. What will be the outcome of the below query?

SELECT first_name, last_name, dept_id

FROM employees

WHERE hire_date LIKE '%98';

1. The first name, last name and the department ID for all the employees who joined in the year 1998 will be displayed.
2. The first name, last name and the department ID for all the employees who joined in the year 2098 will be displayed.
3. No results will be returned.
4. The first name, last name and the department ID for all the employees who joined in the year 1998 between 1st January, 1998 and 31st December, 1998 will be displayed.

Answer: D. The LIKE operator is used to perform wild card search on character and date literals.

37. Which of the following is used to get rows based on a range of values?

1. UNION ALL
2. IN
3. BETWEEN
4. LIKE

Answer: C. The BETWEEN operator is used to retrieve rows based on range of values.

38. You need to display the employee IDs of the employees who have their salaries between 20000 (inclusive) and 50000(inclusive). Which of the following queries will fetch the required results?

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

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

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

D.  SELECT emp_id FROM employees WHERE salary between 20000 AND 50000;

Answer: A, D. For larger ranges of values, BETWEEN and relational operators are best suited in the queries. IN operator is not recommended for large range of values.

39. What is true with respect to the below query?

SELECT first_name, last_name

FROM employees

WHERE last_name BETWEEN 'B%' AND 'E%';

1. It will display all the employees having last names starting with the alphabets 'B' till 'E' inclusive of B and exclusive of E.
2. It will throw an error as BETWEEN can only be used for Numbers and not strings.
3. It will display all the employees having last names starting from 'B' and ending with 'E'.
4. It will display all the employees having last names in the range of starting alphabets as 'B' and 'E' excluding the names starting with 'B' and 'E'.

Answer: A. The BETWEEN operator works with the range of character values also.

40. What will be the outcome of the query mentioned below?

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. It will show all the employees who are under the managers having IDs in the range starting from 100 to 300.
2. It will show all the employees who are under the managers having IDs 100, 200 or 300.
3. It will throw an error as the manager IDs should be put in quotes.
4. It will throw an error as the sorting of manager_id in the WHERE clause conflicts with the ORDER BY clause.

Answer: B. The IN operator can be used to provide small and limited number of range.

41. Which of the following clause defines a Membership condition?

1. BETWEEN
2. LIKE
3. IS NULL
4. IN (NOT IN)

Answer: D. The IN operator defines a Membership condition which may use a range of values or a subquery.

42. Which of the following data types can be used within IN operator?

1. VARCHAR2
2. NUMBER
3. DATE
4. ALL

Answer: D. The IN operator works with all types of values.

43. You need to display the list of all the employees whose first name starts with “Bryan” or “Jason”. Which of the following queries will fulfill the requirement?

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

B.  SELECT emp_id, last_name, first_name FROM employees WHERE first_name BETWEEN 'Bryan' and 'Jason' ;

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

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

Answer: C, D. The IN operator checks for ANY values defined as membership condition.

44. You need to extract details of those departments whose name contains the string '_DXX'. Which of the below WHERE clauses could be used in the SELECT statement to get the required output?

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 '\_'

Answer: B.

45. Which statement is true regarding the default behavior of the ORDER BY clause?

1. In a character sort, the values are case-sensitive.
2. NULL values are not considered at all by the sort operation.
3. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause.
4. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.

Answer: A. The ORDER BY clause does a case sensitive sorting with character values.

46. You need to generate a report of all employees from the EMPLOYEES table based on the following conditions: 1. The Employee first name should not begin with 'T' or 'N'. 2. The Employee's salary should be more than 20000. 3. The Employee should have been hired after 1st January 2010. Which WHERE clause would give the required result?

1. WHERE first_name NOT LIKE 'T%' OR first_name NOT LIKE 'N%' AND salary > 20000 AND hire_date > '1-JAN-10'
2. WHERE (first_name NOT LIKE 'T%' AND first_name NOT LIKE 'N%')OR salary > 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 NOT LIKE '%T%' OR first_name NOT LIKE '%N%') AND(salary > 20000 AND hire_date > '1-JAN-10')

Answer: C.

47. Using the EMPLOYEES table, you need to display the names of all employees hired after January 1, 2013, starting with the freshers. Which query would give the required result? (Choose all that apply.)

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

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

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

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

Answer: A, D.

48. Using the EMPLOYEES table, you need to find out the names and salaries of all the employees hired in departments 100 and 101 in the time interval 15th March '12 to 15th October '13. Which two queries would give the required result? (Choose two.)

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

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

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

D.  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');

Answer: A, D.

49. Using the EMPLOYEES table, you issue the following query to generate the names, current salary and the salary increased after an appraisal by 25%. The increased salary for all the employees should be above 30000.

SELECT first_name, salary,

salary + (salary *0.25) "INCREASED_SALARY"

FROM employees

WHERE increased_salary >30000;

The query throws an error ORA-00904. What is the reason for the error?

1. The parenthesis is missing in the expression used in SELECT statement.
2. Single quotes must be used to define a column alias.
3. Column alias cannot be used in the WHERE clause.
4. Column alias in the WHERE clause must be enclosed within double quotation marks.

Answer: C. A column alias cannot be used in WHERE clause conditions but can be used in SELECT statement and ORDER BY clause.

50. You need to display employee names from the EMPLOYEES table that belong to the Department id 100 with minimum salary as either 2000 or 4000 and no job_id. You issue the following query.

SELECT first_name, dept_id, salary

FROM employees

WHERE dept_id = 100 AND (salary = 2000 OR salary = 4000)

AND job_id <> '';

Which statement is true regarding the above query?

1. It executes successfully but returns no result.
2. It executes successfully and returns the required result.
3. It generates an error because the condition specified for job_id is not valid.
4. It generates an error because the condition specified for the salary column is not valid.

Answer: A. The condition (salary = 2000 OR salary = 4000) results in FALSE because an employee cannot held multiple salaries at a time.

51. Which three tasks can be performed using SQL functions built into Oracle Database? (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                        01-JAN-10                          1000

22                 01-FEB-10                        2000

33                 01-MAR-10                        3000

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

A.  SELECT start_date + '10' FROM trainings;

B.  SELECT * FROM trainings WHERE start_date = '01-01-10';

C.  SELECT training_cost FROM trainings WHERE training_id > '11';

D.  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.

A.  SELECT ename, sal FROM emp WHERE sal GREATER THAN 1000

B.  SELECT ename, sal FROm emp WHERE sal > 1000

C.  SELECT ename, sal FROM emp WHERE sal >= 1000

D.  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?

A.  SELECT ename, deptno FROM emp WHERE hiredate > '31-DEC-1982';

B.  SELECT ename, deptno FROM emp WHERE hiredate > to_date('31-DEC-1982','DD-MM-YYYY');

C.  SELECT ename, deptno FROM emp WHERE hiredate > to_char('31-DEC-1982','DD-MM-YYYY');

D.  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.

Thanks (comment Please)