[email protected]
Interview Questions, Answers and Tutorials

Sorting Data with ORDER BY

Sorting Data with ORDER BY

4. Data Retrievaltestinganswers.com

Welcome to today’s SQL course! In this post, we’ll learn how to sort data in SQL using the ORDER BY clause. Sorting helps us organize the results of our queries in a way that makes sense, whether we want to arrange data in ascending or descending order.

Let’s start by breaking it down:

What is ORDER BY?

The ORDER BY clause in SQL is used to sort the results of a query. We can order the results based on one or more columns. By default, it sorts the data in ascending order (ASC), but we can also sort it in descending order (DESC).

Here’s how it works:

  • Ascending Order (ASC): Sorts from the smallest to the largest (e.g., A to Z, 1 to 100).
  • Descending Order (DESC): Sorts from the largest to the smallest (e.g., Z to A, 100 to 1).

Syntax of ORDER BY

The basic syntax ORDER BY is:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

  • SELECT: The columns you want to retrieve.
  • FROM: The table from which you’re retrieving data.
  • ORDER BY: The column(s) by which to sort the data.
  • ASC or DESC: Whether to sort in ascending or descending order.

Sorting Data in SQL

Let’s look at some examples.

Example 1: Sorting Data in Ascending Order

Let’s say we have a table named Students that contains the following columns: ID, Name, and Age.

SELECT * FROM Students
ORDER BY Age ASC;

This will sort all the students by their age, starting from the youngest to the oldest.

Example 2: Sorting Data in Descending Order

If you want to sort the students in descending order, from the oldest to the youngest, you can use the DESC keyword.

SELECT * FROM Students
ORDER BY Age DESC;

Example 3: Sorting by Multiple Columns

Sometimes, we need to sort by more than one column. For example, let’s sort the students by their Age first, and then by their Name.

SELECT * FROM Students
ORDER BY Age ASC, Name ASC;

This will first sort by age (ascending order) and then, if two students have the same age, it will sort them by name (also in ascending order).

Practice Questions

Now that you understand the basics, let’s test your knowledge with some practice questions.

Practice Question 1:

You have a table Products with columns ProductName and Price. Write a query to display all products sorted by their price in descending order.

Answer:

SELECT * FROM Products
ORDER BY Price DESC;

Practice Question 2:

You have a table Employees with columns EmployeeID, FirstName, LastName, and Salary. Write a query to display all employees sorted by their LastName in ascending order, and then by their FirstName in descending order.

Answer:

SELECT * FROM Employees
ORDER BY LastName ASC, FirstName DESC;

Practice Question 3:

You have a table Sales with columns SaleID, ProductName, and SaleAmount. Write a query to display all sales sorted by SaleAmount in ascending order.

Answer:

SELECT * FROM Sales
ORDER BY SaleAmount ASC;

Practice Question 4:

You have a table Books with columns Title, Author, and PublicationYear. Write a query to display all books sorted by their PublicationYear in descending order.

Answer:

SELECT * FROM Books
ORDER BY PublicationYear DESC;

Interview Questions

Here are some interview questions related to sorting data in SQL.

Interview Question 1:

Question: What is the default sorting order when using ORDER BY?

Answer: The default sorting order is ascending (ASC). If no sorting order is specified, the data will be sorted in ascending order.

Interview Question 2:

Question: Can you sort data by more than one column in SQL?

Answer: Yes, you can sort data by multiple columns. For example:

SELECT * FROM TableName
ORDER BY Column1 ASC, Column2 DESC;

This will first sort the data by Column1 in ascending order and then by Column2 in descending order.

Interview Question 3:

Question: If a column contains NULL values, where will they appear in the sorted result?

Answer: NULL values are usually sorted at the end of the result when using ascending order (ASC) and at the beginning when using descending order (DESC).

For example, in an ascending order sort, NULL values will appear after all non-null values.

Interview Question 4:

Question: How would you sort a table by a column but limit the number of rows returned?

Answer: You can sort the table using ORDER BY and limit the number of rows using LIMIT (in MySQL) or TOP (in SQL Server). For example:

In MySQL:

SELECT * FROM TableName
ORDER BY Column1 ASC
LIMIT 5;

This will return only the top 5 rows sorted by Column1.

Interview Question 5:

Question: Is it possible to sort data in descending order by default in SQL?

Answer: SQL does not have a global setting to change the default sorting order. You must specify DESC for each query where you want descending order.

Great job! You now know how to use ORDER BY to sort data in SQL, both in ascending and descending order, as well as how to sort by multiple columns. This is an essential skill in querying databases to organize data in meaningful ways.

Keep practicing with more queries, and you’ll get even better at SQL!