Free 1Z0-071 Restricting and Sorting Data Questions and Answers

Question 1

Which SQL clause is used to filter rows returned by a query based on a specified condition?

Accepted Answer

WHERE

Answer

The `WHERE` clause in SQL is specifically used to filter the rows returned by a query. It applies a specified condition to each row, and only those rows that satisfy the condition are included in the final result set. This allows users to retrieve a subset of data based on specific criteria.

Question 2

How would you retrieve all rows from a table named employees where the salary is greater than 50000?

Accepted Answer

SELECT * FROM employees WHERE salary > 50000;

Answer

To retrieve all rows from a table that meet a specific condition, the `SELECT * FROM table_name WHERE condition;` syntax is used. In this case, `SELECT * FROM employees` retrieves all columns from the `employees` table, and `WHERE salary > 50000` filters these rows to include only those where the salary is greater than 50000. This is the standard and correct SQL syntax for conditional data retrieval.

Question 3

What does the ORDER BY clause do in a SQL query?

Accepted Answer

Sorts the result set based on one or more columns.

Answer

The `ORDER BY` clause in a SQL query is used to sort the result set based on the values in one or more specified columns. You can sort in ascending (ASC) or descending (DESC) order. This allows for presenting query results in a meaningful and organized sequence, making data easier to analyze.

Question 4

Which SQL statement will return all employees in the employees table sorted by their last_name in ascending order?

Accepted Answer

SELECT * FROM employees ORDER BY last_name ASC;

Answer

To sort query results, the `ORDER BY` clause is used, followed by the column name(s) and the desired sort order. `ASC` specifies ascending order, which is also the default if no order is specified. Therefore, `SELECT * FROM employees ORDER BY last_name ASC;` correctly retrieves all employees and sorts them alphabetically by their last name.

Question 5

How can you retrieve only the top 5 highest-paid employees from the employees table?

Accepted Answer

SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY;

Answer

To retrieve a limited number of rows, such as the top N, you first need to sort the data in the desired order using `ORDER BY`. For the highest-paid, this means `ORDER BY salary DESC`. Oracle SQL then uses `FETCH FIRST N ROWS ONLY` (or `ROWNUM` in older versions) to limit the output to the specified number of rows from the sorted set. This combination ensures you get the top records based on the sorting criteria.

1Z0-071 - Oracle Database SQL Certified Associate Practice Test

1Z0-071 - Oracle Database SQL Certified Associate Practice Test

Free 1Z0-071 Restricting and Sorting Data Questions and Answers