Oracle Database: Introduction to SQL Quiz Questions and Answers

Which SQL statements do not give an error? (Choose all that apply.)

Answer :
  • SELECT last_name, e.hire_date, department_id FROM employees e JOIN (SELECT max(hire_date) max_hire_date FROM employees ORDER BY 1) me ON (e.hire_date = me.max_hire_date)
  • SELECT last_name, e.hire_date, department_id FROM employees e WHERE (department_id, hire_date) IN (SELECT department_id, max(hire_date) hire_date FROM employees GROUP BY department_id)

Explanation :

Options A and B have an ORDER BY clause used in the subquery. An ORDER BY clause can be used in the subquery appearing in the FROM clause, but not in the WHERE clause. Options C and D use the GROUP BY clause in the subquery, and its use is allowed in FROM as well as WHERE clauses. Option D will give an error because the DEPARTMENT_ ID in the SELECT clause is ambiguous and, therefore, doesn’t need to be qualified as e.DEPARTMENT_ID. Another issue with option D is that since you used the USING clause to join, the column used in the USING clause cannot be qualified; e.hire_date in the SELECT clause should be hire_date.

Which SQL statement will replace the last two characters of last_name with 'XX' in the employees table when executed?

Answer :
  • SELECT CONCAT(SUBSTR(last_name, 1,LENGTH(last_name)-2), 'XX') new_col FROM employees;

Explanation :

The SUBSTR function in option A would return the last two characters of the last name. These two characters are right-trimmed using the RTRIM function. The result would be the first portion of the last name and is concatenated to 'XX' only if the last two characters are not repeating (for example, Pululul will be PXX ). Option B would replace all the occurrences of the last two characters. Option C would choose only the last two characters.

The EMPLOYEES table has EMPLOYEE_ID, DEPARTMENT_ID, and FULL_NAME columns. The DEPARTMENTS table has DEPARTMENT_ID and DEPARTMENT_NAME columns. Which two of the following queries return the department ID, name, and employee name, listing department names even if there is no employee assigned to that department? (Choose two.)

Answer :
  • SELECT department_id, department_name, full_name FROM departments NATURAL LEFT JOIN employees;
  • SELECT d.department_id, d.department_name, e.full_name FROM departments d LEFT OUTER JOIN employees eON (d.department_id =e.department_id);

Explanation :

Option A does not work because you cannot qualify column names when using a natural join. Option B works because the only common column between these two tables is DEPARTMENT_ID. The keyword OUTER is optional. Option C does not work, again because you cannot qualify column names when specifying the USING clause. Option D works because it specifies the join condition explicitly in the ON clause.

Which one of the following Oracle SQL*Plus command lines is not valid?

Answer :
  • sqlplus @< connect_string >

Explanation :

SQL*Plus can be invoked with no parameters or with connection attributes. When connection attributes are provided, the < username > is mandatory. The < password > and < connect_string > commands are optional. When connection attributes are not provided, sqlplus prompts for username and password. If username is included in the command line, sqlplus prompts for password.

What will be the result of the following query? SELECT c.cust_id, c.cust_name, o.ord_date, o.prod_id FROM customers c, orders o WHERE c.cust_id = o.cust_id (+);

Answer :
  • List all the customer names in the CUSTOMERS table and the orders they made from the ORDERS table, even if the customer has not placed an order.

Explanation :

An outer join operator, (+), indicates an outer join and is used to display the records, even if there are no corresponding records in the table mentioned on the other side of the operator. Here, the outer join operator is next to the ORDERS table, so even if there are no corresponding orders from a customer, the result set will have the customer ID and name.

Which assertion about the following queries is true? SELECT COUNT(DISTINCT mgr), MAX(DISTINCT salary) FROM emp; SELECT COUNT(ALL mgr), MAX(ALL salary) FROM emp;

Answer :
  • They may return different numbers in column 1 but will always return the same number in column 2

Explanation :

The first column in the first query counts the distinct MGR values in the table. The first column in the second query counts all MGR values in the table. If a manager appears twice, the first query will count her one time, but the second will count her twice. Both the first query and the second query select the maximum salary value in the table.

Sara wants to update the SALARY column in the OLD_EMPLOYEES table with the value from the EMPLOYEES table for employees in department 90. Which SQL code will accomplish the task?

Answer :
  • UPDATE old_employees a SET salary = (SELECT salary FROM employees b WHERE a.employee_id = b.employee_id) WHERE department_id = 90;

Explanation :

Option A uses a correlated subquery to match the correct employee. Option B selects all the rows in the subquery and, therefore, will generate an error. Option C is not valid syntax. Option D will update all the rows in the table because the UPDATE statement does not have a WHERE clause. The WHERE clause preset belongs to the subquery.

Which SQL statement will query the EMPLOYEES table for FIRST_NAME , LAST_NAME , and SALARY of all employees in DEPARTMENT_ID 40 in the alphabetical order of last name?

Answer :
  • SELECT first_name, last_name, salary FROM employees WHERE department_id = 40 ORDER BY last_name;

Explanation :

In the SELECT clause, the column names should be separated by commas. An alias name may be provided for each column with a space or by using the keyword AS . The FROM clause should appear after the SELECT clause. The WHERE clause appears after the FROM clause. The ORDER BY clause comes after the WHERE clause.

Which clause in a query restricts the rows selected?

Answer :
  • Where

Explanation :

The WHERE clause is used to filter the rows returned from a query. The WHERE clause condition is evaluated, and rows are returned only if the result is TRUE. The ORDER BY clause is used to display the result in a certain order. The OFFSET and FETCH clauses are used to limit the rows returned.

Consider the following two SQL statements, and choose the best option: 1. SELECT TO_DATE('30-SEP-07','DD-MM-YYYY') from dual; 2. SELECT TO_DATE('30-SEP-07','DD-MON-RRRR') from dual;

Answer :
  • The resulting date value from the two statements will be different.

Explanation :

Statement 1 will result in 30-SEP-0007 , and statement 2 will result in 30-SEP-2007 . The RR and RRRR formats derive the century based on the current date if the century is not specified. The YY format will use the current century, and the YYYY format expects the century in the input.