callicon +1 210-504-8191
whatsAppIcon +1 210-504-8191
emailIcon info@koenig-solutions.com
koenig-logo
logo-30-years
Login
koenig-logo
+1 210-504-8191
+1 210-504-8191

Oracle Database: Introduction to SQL Quiz Questions and Answers

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