SQL - Structured Query Language Sorting and Limiting Results Questions and Answers 1 — Questions and Answers
Question 1: 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?
- SELECT * FROM employees ORDER BY hire_date ASC LIMIT 10;
- SELECT TOP 10 * FROM employees ORDER BY hire_date;
- SELECT * FROM employees ORDER BY hire_date DESC LIMIT 10; (Correct answer)
- SELECT * FROM employees FETCH FIRST 10 ROWS ONLY;
Correct 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.
Question 2: 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?
- SELECT * FROM products ORDER BY product_name LIMIT 20 OFFSET 20;
- SELECT * FROM products ORDER BY product_name LIMIT 40, 20;
- SELECT * FROM products ORDER BY product_name LIMIT 20 OFFSET 40; (Correct answer)
- SELECT * FROM products ORDER BY product_name FETCH FIRST 20 ROWS ONLY OFFSET 40;
Correct 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.
Question 3: 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`?
- FETCH FIRST
- LIMIT (Correct answer)
- ROWNUM
- STOP AFTER
Correct 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.
Question 4: 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?
- ORDER BY Country, TotalSales;
- ORDER BY Country ASC, TotalSales DESC; (Correct answer)
- ORDER BY Country DESC, TotalSales ASC;
- ORDER BY Country, TotalSales BOTH DESC;
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.
Question 5: What is the primary reason to always use an `ORDER BY` clause when using a `LIMIT` or `FETCH` clause?
- To improve query execution speed.
- To ensure the query syntax is valid.
- To guarantee consistent and predictable results. (Correct answer)
- To allow the use of aggregate functions on the limited set.
Correct 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.
Question 6: 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?
- They are always treated as the lowest possible values and appear first.
- They are always treated as the highest possible values and appear last.
- The behavior is not defined by the SQL standard, and it varies by database system. (Correct answer)
- They are excluded from the result set automatically.
Correct 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).
SQL - Structured Query Language Sorting and Limiting Results Questions and Answers 1
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?