Restricting and Sorting Data Flashcards

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

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.

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

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.

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

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.

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

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.

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

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.