Basic SQL Interview Questions
1. What is the use of DISTINCT in SELECT Statement?
DISTINCT statement is used for eliminating the duplicate rows from the result. When we fire a SELECT statement, it will select all the matching rows from the table. These matching rows may have duplicate entries. When we use DISTINCT it will eliminate all the duplicate rows and retain single entry for such entries.
Consider the DEPARTMENTS table. It has LOCATION_ID which indicates the location of each department.
If we write a simple query to find the locations where departments exist, then it will list all the location ids irrespective of its row and duplicate values.
SELECT LOCATION_ID FROM DEPARTMENTS;
We can notice the duplicate entries of location ids in above result set. But we need not have duplicate values in the result set to know various locations of the departments. We have to eliminate the duplicate values here. Therefore, we use DISTINCT in above query to get different locations.
SELECT DISTINCT LOCATION_ID FROM DEPARTMENTS;
Now we do not have any duplicate values in the result. We can even apply DISTINCT on combination of columns. It need not be on single column like above.
Consider below query to list various MANAGER_ID and DEPARTMENT_ID from the EMPLOYEES table like below.
SELECT MANAGER_ID, DEPARTMENT_ID FROM EMPLOYEES;
We can see duplicate result values in above query. If we want to eliminate the duplicate entries, we can use DISTINCT clause. It will remove the duplicate values on the combination of columns. We can see duplicate values for MANAGER_ID but combination of MANAGER_ID and DEPARTMENT_ID does not have any duplicate values.
SELECT DISTINCT MANAGER_ID, DEPARTMENT_ID FROM EMPLOYEES;
2. Write a Query to find Maximum and Minimum values from the table without using any MAX () or MIN () functions.
Since we cannot use MAX or MIN aggregate functions, we need to think of logic for getting the maximum and minimum values. The logic for getting these values can be anything but it should return right value. Let us first discuss the simplest way of getting minimum and maximum values by sorting the data.
Consider the DEPARTMENTS table from which we need to find the LOCATION_ID with minimum value and maximum value.
SELECT LOCATION_ID FROM DEPARTMENTS;
It has 27 records with different LOCATION_IDs and is displayed in the order they appear in the table. How can we get minimum value of the LOCATION_ID here without using MIN () function? If we sort the LOCATION_ID column in ascending order, then we will get the smallest number at the first row and the biggest number at the last row. Hence if we sort above query in ascending order, then we will get the result starting from smallest number.
SELECT LOCATION_ID FROM DEPARTMENTS ORDER BY LOCATION_ID;
But we do not want all the LOCATION_IDs. We need only the smallest ID alone. Hence we need to restrict the number of rows in the SELECT Statement above to one row by using ROWNUM. But ROWNUM condition will be given in the WHERE clause. If we restrict the number of rows in WHERE clause, then first row is selected first and then it will be sorted. This will not give us the required smallest number.
SELECT LOCATION_ID FROM DEPARTMENTS WHERE ROWNUM
We need to get 1400 as smallest number. Therefore, we need to sort the records first and then we need to select the smallest number by limiting the rows like below. This will give us correct result.
SELECT LOCATION_ID FROM (SELECT LOCATION_ID FROM DEPARTMENTS ORDER BY LOCATION_ID) WHERE ROWNUM <2;
This is how we got smallest number. What should we do to get largest number? We need to sort the column in descending order. Then largest number will come at the top and we can select it by restricting the number of rows to one.
SELECT LOCATION_ID FROM (SELECT LOCATION_ID FROM DEPARTMENTS ORDER BY LOCATION_ID DESC) WHERE ROWNUM <2;
Suppose we need to find minimum and maximum values without using any min, max function or order by clause. Then we have to think of complex logic to get the minimum and maximum values. In this case we need to think little bit more about the logic and query. We need to consider the same table twice and compare each value with rest of them to get max and min values. Let us start step by step to get the values.
As a first step, how to compare the values from same table? We need to have DEPARTMENTS table twice in our query with self-join. But self-join is not equijoin here (values are not compared with equal to, ‘=’). We have to use greater than or lesser than operator to get smaller and larger values. i.e.;
SELECT smaller.LOCATION_ID smaller_loc_id, larger.LOCATION_ID large_loc_id FROM DEPARTMENTS smaller, DEPARTMENTS larger WHERE smaller.LOCATION_ID < larger.LOCATION_ID;
Here each LOCATION_ID is compared against rest of the LOCATION_IDs by using ‘<’ comparison. Hence in the result set we will get all the smaller LOCATION_IDs and corresponding larger location ID combinations like below.
This is not the final. We need to extract the smaller and larger values from this list. But how? What could be the logic to get the values from this list? In above result set, the first row first column will have smallest number. We need to extract it by limiting the number of rows to one. In addition, we do not want combination of smaller and larger values. We need only smaller number. Hence we can eliminate the second column from above list.
SELECT smaller.LOCATION_ID smaller_loc_id FROM DEPARTMENTS smaller, DEPARTMENTS larger WHERE smaller.LOCATION_ID < larger.LOCATION_ID AND ROWNUM <2;
Now how we will get largest number? One method is to extract the last row second column. But we cannot use ORDER BY to sort and get the value. Another method is to use the same logic as above in little different way. In the first column above we will have all the smaller value compared against larger value. The largest value will not have any value to be compared and it will not be listed in the first column. Hence if we extract that number by writing a query on DEPARTMENTS to find which number is not listed in above query, we will get the largest number.
SELECT LOCATION_ID FROM DEPARTMENTS WHERE LOCATION_ID NOT IN (SELECT smaller.LOCATION_ID smaller_loc_id FROM DEPARTMENTS smaller, DEPARTMENTS larger WHERE smaller.LOCATION_ID < larger.LOCATION_ID);
These are the two ways of getting smaller and larger numbers from the table. There will be other methods too depending on your thinking about logic.
3. What is ternary (also known as three-valued) logic in SQL?
In SQL, any column in the table can take some valid value or UNKNOWN value. When the value of the column is UNKNOWN, we represent it by using NULL or some blank column which is not actually blank.