Sorting and Limiting Results Flashcards

A developer needs to write a query that retrieves the top 10 most recently hired employees from an `employees` table. Which query correctly accomplishes this, assuming `hire_date` is the column storing the hiring date?

Answer: SELECT * FROM employees ORDER BY hire_date DESC LIMIT 10;

To find the most recently hired employees, the results must be sorted by `hire_date` in descending order (`DESC`), which places the latest dates first. The `LIMIT 10` clause then restricts the output to only the top 10 rows from this sorted result set.

You are implementing pagination for a list of products. You need to display the third page of results, with each page containing 20 products. The products should be sorted by `product_name`. Which query correctly fetches the records for the third page?

Answer: SELECT * FROM products ORDER BY product_name LIMIT 20 OFFSET 40;

To get the third page of 20 items, you must first skip the items from the first two pages (2 * 20 = 40). The `OFFSET 40` clause accomplishes this by skipping the first 40 records. The `LIMIT 20` clause then retrieves the next 20 records, which represent the third page. The `ORDER BY` clause is essential to ensure consistent pagination.

Which of the following SQL clauses is a non-standard, database-specific way to limit results, commonly found in MySQL and PostgreSQL, while its equivalent in SQL Server is `TOP`?

Answer: LIMIT

`LIMIT` is a clause used by MySQL, PostgreSQL, and SQLite to restrict the number of rows returned by a query. SQL Server uses the `TOP` keyword for the same purpose. `FETCH FIRST` is part of the SQL standard, and `ROWNUM` is specific to Oracle.

A data analyst wants to sort a `Customers` table first by `Country` in ascending order, and then by `TotalSales` in descending order for customers within the same country. Which `ORDER BY` clause is correct?

Answer: ORDER BY Country ASC, TotalSales DESC;

To sort by multiple columns, you list them in the `ORDER BY` clause in the desired order of precedence. The query first sorts by `Country` in ascending order (ASC is the default). Then, for rows with the same country, it sorts by `TotalSales` in descending order, as specified by the `DESC` keyword.

What is the primary reason to always use an `ORDER BY` clause when using a `LIMIT` or `FETCH` clause?

Answer: To guarantee consistent and predictable results.

Without an `ORDER BY` clause, the database does not guarantee the order in which rows are returned. When using `LIMIT` or `FETCH`, this can lead to an unpredictable and inconsistent subset of rows being returned each time the query is run. `ORDER BY` provides a stable sorting order, ensuring the same subset is returned consistently.

In a database system that follows the SQL standard, how does the `ORDER BY` clause treat `NULL` values by default when sorting in ascending (`ASC`) order?

Answer: The behavior is not defined by the SQL standard, and it varies by database system.

The SQL standard does not explicitly define a default sorting order for `NULL` values. Consequently, the behavior differs between database systems. For example, PostgreSQL and Oracle treat `NULL`s as larger than non-NULL values (appearing last in ASC order), while SQL Server and MySQL treat them as smaller (appearing first in ASC order).