SQL interview questions on restricting and sorting data with WHERE and ORDER BY clauses.

6.What is the purpose of the BETWEEN operator in data filtering?

BETWEEN is used to filter data in a certain range thus being more acceptable, easyand readable than using >= or <=.
Think of it as a filter on a shopping site, when you are looking for a product of a price range, all you have to do is key in the minimum and maximum price and the website shows products only in that range.
So, you're looking to find products whose prices are between ₹50 and ₹100. The SQL code will be written like this:
SELECT * FROM PRODUCTS WHERE price BETWEEN 50 AND 100
This will fetch all products priced between ₹50 and ₹100, including both ₹50 and ₹100.
It is a very compact and user friendly way to filter data when working with ranges such as dates, prices, or ages.

7.What is the LIKE operator used for pattern matching in the WHERE clause?

The LIKE operator has an excellent capacity for pattern searching wherein the pattern may not be identical. It is like one searching for information on a cell phone, upon searching for "John", the cell phone lists names like "John", "Johnny", "Johnathan", etc. This operator assists in locating various permutations of names (or any other data) that follow a certain pattern.
Wildcards that can be used:
%  for any number of characters.
_   for a single character.
As you are looking for employees' names that start with "John", you will write:

SELECT * FROM EMPLOYEES WHERE name LIKE 'John%'

This will give you all the names that start with John, such as John, Johnny, Johnathan, and so on. It is a powerful tool to use when exact matches are simply not possible or are impractical.

8. What is the IN operator used for while filtering data?

The IN operator is helpful for filtering a value against any number in a given list, simply put, it presents a cleaner way than writing many OR conditions.
Assume that you are looking for employees who belong to a targeted department — let us say the department IDs 101, 102 and 103. Rather than writing:

SELECT * FROM EMPLOYEES WHERE department_id = 101 OR department_id = 102 OR department_id = 103

The IN operator gives a more straightforward format to the previous clause:
SELECT * FROM EMPLOYEES WHERE department_id IN (101, 102, 103)
This makes your query a lot neater and easier to read, especially if you have a long list of values to check for. It becomes easier and a more efficient way to filter records based on multiple options.

9. How do logical operators like AND, OR and NOT help in combining filtering conditions?

While filtering data, it is usually necessary to apply more than one condition simultaneously.
These logical operators AND, OR and NOT are employed to combine these conditions so you can obtain more refined results.
The AND operator tells that all conditions must be matched. Let us say that you would like to find employees that belong to department ID 101 and earn more than ₹50,000.
Here, you would write:

SELECT * FROM epmloyees WHERE department_id = 101 AND salary > 50000

Only employees from department 101 with a salary greater than ₹50,000 will be returned here.
On the contrary, the OR operator gets any result for which at least one condition is satisfied. For instance:

SELECT * FROM epmloyees WHERE department_id = 102 OR department_id = 103

This will return employees from either department 102 or 103.
Use NOT when you wish to exclude certain results.
For example, to find the employees who do not belong to department 104:

SELECT * FROM epmloyees WHERE NOT department_id = 104

These logical operators are a way for you to create more flexible and powerful queries by combining together different filtering rules according to your needs.

10. Can aggregate functions be used in the WHERE clause? Why or why not?

No, as you don't put aggregate functions such as SUM(), AVG(), and COUNT() directly into the WHERE clause. This is because the WHERE clause filters rows before any grouping or aggregation.
If you are filtering individual employees - let's say those with a salary above ₹50,000 then WHERE is good:

SELECT * FROM employees WHERE salary > 50000

But if you are filtering grouped data- for example departments with an average salary greater than ₹50,000 then you would use the HAVING clause because it works after the grouping.
The expression would look like this:

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000

To sum up, WHERE filters rows, HAVING filters groups that have been created through GROUP BY and aggregate functions.

11. How does SQL handle NULLs in an ORDER BY?

SQL considers NULL as a different data type when sorting since it represents an unknown or missing value and thus cannot be treated like any other numeric or text value.
In most SQL implementations, the usual treatment is:
Sorting in ascending order by the column (ORDER BY column ASC) will generally place all NULL values at the end of the list.
Sorting in descending order (ORDER BY column DESC) will generally see NULLs come in first.
Considering an example, consider an employee table, where all employees with no commission (i.e., value is NULL) should be sorted by commission:
COMMISSION. ORDER BY will sort on ASC; Thus, those employees with a commission in amounts like 500 or 1000 will be displayed first, while employees with (NULL) would be displayed at the end.
Some other databases will allow you to specify keywords like NULLS FIRST or NULLS LAST that control the display of NULLs:

SELECT * FROM employees ORDER BY commission ASC NULLS FIRST;

This would bring the NULL values to the top, even in ascending order.
Understanding NULL behavior during sorting is very important in working with incomplete data so that missing values are not accidentally ignored or misread during the analysis.

12. What is the default position of NULL values in an ascending or descending sort?

NULLs are lowest values in an ascending sorting scheme, but they are the highest values in a descending order. Hence, when an ORDER BY clause is set as ORDER BY column ASC, NULLs come first. On the other hand, when an ORDER BY clause is set as ORDER BY column DESC, NULLs come last. If you want to change this setting you will have to set NULLS FIRST or NULLS LAST in your select statement to control the placement of NULLs.

13. What statements can you make to SQL that make explicit that NULLs appear first or last in the sort?

NULLS FIRST or NULLS LAST in the ORDER BY clause, in case you want to control NULLs positions for sorting in SQL. For instance, in case you wish NULL values appear at last while sorting in ascending order, then you have ORDER BY column ASC NULLS LAST. This will explicitly move the NULL values to the last of the sorted data. NULLS FIRST can be used for the NULLs come first in the result.

14. Can you sort on a column that contains NULL values and another valued column together? Explain.

Yes, SQL gives you that option. You can sort your records using a column that has NULL values along with another column that has actual data. This kind of thing is very common in the real world, where some fields are optional or left blank.
Consider This Example:
Consider that a list of employees has an additional record for a column called bonus. Not all employees get a bonus, so for some of them, this column has NULL.
So you would like to sort the employees list as follows:
By the amount of bonus received (from low to high), and:
Then, according to their first name in alphabetical order.

Here is how you present the SQL query:

SELECT employee_id, first_name, bonus
FROM employees
ORDER BY bonus ASC, first_name ASC;

How Does SQL Handle This?

1. It first goes to column bonus. It prefers the NULL values to be in the topmost place by default when you sort in ascending order.
2. Further, it sorts all records that have the same bonus (or NULL) by first_name, A to Z.
So whether or not the bonus column has missing values (NULL), SQL will still present a neat-ordered result. This second column comes in handy in breaking ties or organizing rows in the event where the bonus is the same or missing.
Want to Move NULLs to the Bottom?
In instances where you don't want NULL values to show up at the top, you can use:

SELECT * FROM employees ORDER BY bonus ASC NULLS LAST, first_name ASC;

It makes the list more valuable, especially if you want those employees with bonuses to be on top and those without bonuses go down.

15.What if the ORDER BY clause contains an unselected column in the SELECT statement?

It is quite possible that any column can be put in the ORDER BY clause which might not at all appear in the SELECT list. This is one of the important things in SQL allowing to decide what an output should look like but not what is sorted out on the result set.
For Example:
An employee table now has an employee_id, a first_name, a department_id, and a hire_date.
Next, you want to display employee ID and name but want to add sorting by the date each employee was hired (hire_date). You may not want to put hire_date in your output at the end, but you can sort by it.
Here is the SQL query:

SELECT employee_id, first_name
FROM employees
ORDER BY hire_date ASC;

What is happening here?
This means employee_id and first_name are selected from the table.
However, the resulting output is sorted by the hire_date column.
Although the hire_date is not possessed by the output, SQL makes use of it behind the scenes to order the data.
It keeps your output clean and focused while benefiting from an additional layer of sorting.

16.Write an SQL query that gets the list of employees sorted by their department ID in ascending manner with NULLs last.

SELECT *
FROM employees
ORDER BY department_id ASC NULLS LAST;
The entire Employee table can be accessed by this query the data returned will be sorted in ascending order according to department_id. NULL has the first position in the order, thus, it is not very readable output. To fix this, the NULLS LAST clause pushes the rows with NULL values to the end of an ordered list.
For instance, if the department_id has the values 101, 102, NULL, 103 then the result will be sorted as 101, 102, 103, NULL. They are especially important when dealing with incomplete data, making the output a little bit cleaner and more intuitive.

17.Write one SQL query that gives all transactions for which the Amount is more than ₹1000.

The results have to be sorted by the TransactionDate in ascending order, but, where applicable, if the TransactionDate is NULL, those rows should appear at the end. In cases of having multiple transactions sharing the same date, further sorting them must be done by the Amount in descending order, thereby bringing higher-value transactions first.

The following SQL query:
SELECT TransactionID, CustomerID, Amount, TransactionDate
FROM Transactions
WHERE Amount > 1000
ORDER BY TransactionDate ASC NULLS LAST, Amount DESC;

Explanation:
WHERE Amount > 1000
This filters out the records to have then that only transactions with the amount greater than ₹1000 are included into the result set. Other, smaller transactions are excluded.

ORDER BY TransactionDate ASC NULLS LAST
This has the transactions arranged in order by their date in ascending order (oldest to newest). Any transactions that have no date (i.e., the date is NULL) will be at the end of the list-thus reserving the meaningful records upfront.

Amount DESC
When two or more transactions share the same date, this part ensures they are sorted by the transaction amount, starting with the highest value. This is helpful when you want to quickly identify the most valuable transactions for each date.

18.Write an SQL query that will return a list of students who have grades between 70 and 90 ordered by grade in descending order.

SELECT * FROM students
WHERE GRADE BETWEEN 70 AND 90
ORDER BY grade DESC;
This SQL statement fetches all rows in the students table whose grade is between 70 and 90 inclusive. The predicate forces the server to return only those rows that contain grades within this range. Following this filtering process the query orders the results by the grade column in descending order ORDER BY grade DESC. This will mean students whose grades are nearer to 90 appear first in the result set thus making it easier to view top performers within this grade range.

19.What is the Difference between GROUP BY and ORDER BY in SQL.

Basically, grouping and ordering are the two types of organizing data in SQL. However, their purposes totally differ. GROUP BY groups together rows that have identical values in specified columns so that aggregate functions can be used on them.
For example, to find out the total salary in each department, GROUP BY gives the result with the department-wise salary information that is added up for that department.
After that, the ORDER BY clause sorts the modified result set either in ascending or descending order based on one or more columns. Therefore, ORDER BY comes into action after the aggregation has been done, thus making the final output readable and meaning-giving.

The core difference between GROUP BY and ORDER BY is in their application and function on data.
GROUP BY caters to the aggregation of data, ORDER BY caters to the sorting of data.
For instance, GROUP BY will group employees on a departmental basis, whereas ORDER BY will sort those groups by total salary descending.
GROUP BY works by reducing the number of rows in the result set for the groups, whereas ORDER BY leaves all rows in the alphabetized format. They can run parallel to each other in complex queries in which GROUP BY aggregates some information and ORDER BY sorts the result set to produce meaningful and well-defined output.

20.What is the COUNT() aggregate function in SQL?

The COUNT() function counts the number of rows for a table or non NULL values for a specific column. For example, SELECT COUNT(*) FROM employees; will count the number of employees present in the table. You can count some conditions too. For example, SELECT COUNT(salary) FROM employees WHERE department = 'IT'; would count the number of employees from the IT department with some salary. Most commonly it is used along with GROUP BY to count number of records in each group.

21.How can you optimize a query that uses ORDER BY on an enormous dataset?

Use an index on the column being sorted in an ORDER BY statement. This causes the database to return your query faster because it needs to only sort and find the data. If you have a query that frequently orders by employees by salary an index on the salary column can speed that query up. In general, avoid ordering large result sets without filtering because such a query is going to slow you down. You can also limit the number of rows returned if only a small amount of sorted data is needed.

22.Explain the importance of knowing the difference between ORDER BY and GROUP BY.?

Knowing the difference between ORDER BY and GROUP BY is important mainly because they serve very different purposes. ORDER BY sets the order of the resulting set of rows, that is either ascending or descending, based on one or more selected columns. This means it is more or less only concerned about ordering the data.
Whereas GROUP BY refers to the action of grouping records based on a particular common property usually used together with aggregate functions like SUM(), AVG(), or COUNT() to summarize the data for example by total sales per region or average salary per department. ORDER BY and GROUP BY, when applied correctly, create very efficient and useful query writing. However, if misused, one can get severely misguided with the incorrect results or overly complex queries.

23. What is the significance of NULL in the ORDER BY clause?

Special rule applies in the case of NULL while ordering values in ORDER BY clause. By default, NULLs are considered the smallest value and sorted before other values in ascending order (ASC). On the other hand, NULLs are treated as the greatest value and sorted after other values in case of descending order (DESC). You can enforce the ordering of NULLs yourself, as to whether they should appear at the start or at the end of the sorting. You can track NULLs explicitly into your result set with the use of NULLS FIRST and NULLS LAST keywords. These keywords can be helpful in the presentation of your data in a way that reflects missing or undefined variables of interest.

24. How to use DISTINCT to remove duplicates from one column but keep values from other columns?

The DISTINCT keyword in SQL eliminates duplicate values in the result set.
If one wants to get a list of unique values from a column, say, from the employees table, different department_ids can be queried:

SELECT DISTINCT department_id FROM employees;

A unique list of department IDs is fetched from the employees table. Normally, if more than one employee belongs to the same department, the department IDs will be repeated. But with the use of the DISTINCT keyword, the query ensures that every department ID is only shown once in the results. This is useful if you want to see all the distinct departments in your organization without duplicates. Thus your data is cleaner and organized, especially while dealing with large sets of information. Hence, DISTINCT helps simplify your data, summarizes it, and only displays unique values.
This can be worked out using the GROUP BY clause. By grouping the rows based on one column, you may apply useful functions such as COUNT() or MAX() onto the other columns.
For instance, to count how many employees there are for each department, one can write:

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

This query will fetch one row per department and count the employees without repeating any department ID.