Free 1Z0-071 Restricting and Sorting Data Questions and Answers — Questions and Answers
Question 1: Which SQL clause is used to filter rows returned by a query based on a specified condition?
- SELECT
- FROM
- WHERE (Correct answer)
- ORDER BY
Correct answer: WHERE
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?
- SELECT * FROM employees WHERE salary > 50000; (Correct answer)
- SELECT * FROM employees ORDER BY salary > 50000;
- SELECT * WHERE salary > 50000 FROM employees;
- SELECT * ORDER BY salary > 50000 FROM employees;
Correct answer: SELECT * FROM employees WHERE salary > 50000;
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?
- Filters rows based on a condition.
- Sorts the result set based on one or more columns. (Correct answer)
- Combines rows from multiple tables.
- Limits the number of rows returned by the query.
Correct answer: Sorts the result set based on one or more columns.
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?
- SELECT * FROM employees SORT BY last_name;
- SELECT * FROM employees WHERE last_name ORDER BY ASC;
- SELECT * FROM employees ORDER BY last_name ASC; (Correct answer)
- SELECT * FROM employees WHERE ORDER BY last_name;
Correct answer: SELECT * FROM employees ORDER BY last_name ASC;
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?
- SELECT * FROM employees WHERE salary DESC LIMIT 5;
- SELECT TOP 5 * FROM employees ORDER BY salary DESC;
- SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY; (Correct answer)
- SELECT * FROM employees SORT BY salary LIMIT 5 DESC;
Correct answer: SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY;
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.
Free 1Z0-071 Restricting and Sorting Data Questions and Answers
Which SQL clause is used to filter rows returned by a query based on a specified condition?