1.How to generate row number in SQL Without ROWNUM
Generating a row number – that is a running sequence of numbers for each row is not easy using plain SQL. In fact, the method I am going to show below is not very generic either. This method only works if there is at least one unique column in the table. This method will also work if there is no single unique column, but collection of columns that is unique. Anyway, here is the query:
SELECT name, sal, (SELECT COUNT(*) FROM EMPLOYEE i WHERE o.name >= i.name) row_num FROM EMPLOYEE o order by row_num
NAME | SAL | ROW_NUM |
---|---|---|
Anno | 80 | 1 |
Bhuti | 60 | 2 |
Darl | 80 | 3 |
Hash | 100 | 4 |
Inno | 50 | 5 |
Meme | 60 | 6 |
Pete | 70 | 7 |
Privy | 50 | 8 |
Robo | 100 | 9 |
Tomiti | 70 | 10 |
The column that is used in the row number generation logic is called “sort key”. Here sort key is “name” column. For this technique to work, the sort key needs to be unique. We have chosen the column “name” because this column happened to be unique in our Employee table. If it was not unique but some other collection of columns was, then we could have used those columns as our sort key (by concatenating those columns to form a single sort key).
Also notice how the rows are sorted in the result set. We have done an explicit sorting on the row_num column, which gives us all the row numbers in the sorted order. But notice that name column is also sorted (which is probably the reason why this column is referred as sort-key). If you want to change the order of the sorting from ascending to descending, you will need to change “>=” sign to “<=” in the query.
As I said before, this method is not very generic. This is why many databases already implement other methods to achieve this. For example, in Oracle database, every SQL result set contains a hidden column called ROWNUM. We can just explicitly select ROWNUM to get sequence numbers.
2,How to select first 5 records from a table?
This question, often asked in many interviews, does not make any sense to me. The problem here is how do you define which record is first and which is second. Which record is retrieved first from the database is not deterministic. It depends on many uncontrollable factors such as how database works at that moment of execution etc. So the question should really be – “how to select any 5 records from the table?” But whatever it is, here is the solution:
In Oracle,
SELECT * FROM EMP WHERE ROWNUM <= 5;
In SQL Server,
SELECT TOP 5 * FROM EMP;
Generic solution,
I believe a generic solution can be devised for this problem if and only if there exists at least one distinct column in the table. For example, in our EMP table ID is distinct. We can use that distinct column in the below way to come up with a generic solution of this question that does not require database specific functions such as ROWNUM, TOP etc.
SELECT name FROM EMPLOYEE o WHERE (SELECT count(*) FROM EMPLOYEE i WHERE i.name < o.name) < 5
name |
---|
Inno |
Anno |
Darl |
Meme |
Bhuti |
I have taken “name” column in the above example since “name” is happened to be unique in this table. I could very well take ID column as well.
In this example, if the chosen column was not distinct, we would have got more than 5 records returned in our output.
Do you have a better solution to this problem? If yes, post your solution in the comment.
3.What is the difference between ROWNUM pseudo column and ROW_NUMBER() function?
ROWNUM is a pseudo column present in Oracle database returned result set prior to ORDER BY being evaluated. So ORDER BY ROWNUM does not work.
ROW_NUMBER() is an analytical function which is used in conjunction to OVER() clause wherein we can specify ORDER BY and also PARTITION BY columns.
Suppose if you want to generate the row numbers in the order of ascending employee salaries for example, ROWNUM will not work. But you may use ROW_NUMBER() OVER() like shown below:
SELECT name, sal, row_number() over(order by sal desc) rownum_by_sal FROM EMPLOYEE o
name | Sal | ROWNUM_BY_SAL |
---|---|---|
Hash | 100 | 1 |
Robo | 100 | 2 |
Anno | 80 | 3 |
Darl | 80 | 4 |
Tomiti | 70 | 5 |
Pete | 70 | 6 |
Bhuti | 60 | 7 |
Meme | 60 | 8 |
Inno | 50 | 9 |
Privy | 50 | 10 |
4.What are the differences among ROWNUM, RANK and DENSE_RANK?
ROW_NUMBER assigns contiguous, unique numbers from 1.. N to a result set.
RANK does not assign unique numbers—nor does it assign contiguous numbers. If two records tie for second place, no record will be assigned the 3rd rank as no one came in third, according to RANK. See below:
SELECT name, sal, rank() over(order by sal desc) rank_by_sal FROM EMPLOYEE o
name | Sal | RANK_BY_SAL |
---|---|---|
Hash | 100 | 1 |
Robo | 100 | 1 |
Anno | 80 | 3 |
Darl | 80 | 3 |
Tomiti | 70 | 5 |
Pete | 70 | 5 |
Bhuti | 60 | 7 |
Meme | 60 | 7 |
Inno | 50 | 9 |
Privy | 50 | 9 |
DENSE_RANK, like RANK, does not assign unique numbers, but it does assign contiguous numbers. Even though two records tied for second place, there is a third-place record. See below:
SELECT name, sal, dense_rank() over(order by sal desc) dense_rank_by_sal FROM EMPLOYEE o
name | Sal | DENSE_RANK_BY_SAL |
---|---|---|
Hash | 100 | 1 |
Robo | 100 | 1 |
Anno | 80 | 2 |
Darl | 80 | 2 |
Tomiti | 70 | 3 |
Pete | 70 | 3 |
Bhuti | 60 | 4 |
Meme | 60 | 4 |
Inno | 50 | 5 |
Privy | 50 | 5 |
What does UNION
do? What is the difference between UNION
and UNION ALL
?
UNION
merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION
and UNION ALL
is that UNION
will omit duplicate records whereas UNION ALL
will include duplicate records.
Consider the following two query results:
sql> select Count(*) as Total from Orders;
+-------+
| Total |
+-------+
| 100 |
+-------+
sql> select Count(*) as Cust123Total from Orders where customer_id = '123'
+--------------+
| Cust123Total |
+--------------+
| 15 |
+--------------+
Given the above query results, what will be the result of the query below?
sql> select Count(*) from Orders where customer_id <> '123'
Answering that the result will be 85 (i.e., 100 – 15) is not necessarily correct. Specifically, any records with a customer_id
of NULL
willnot be included in either count (i.e., they won’t be included in the count of records that do have a customer_id
of 123, nor will they be included in the count of records that do not have a customer_id
of 123).
5.What will be the result of the query below? Explain your answer and provide a version that behaves correctly.
select case when null = null then 'Yup' else 'Nope' end as Result;
This query will actually yield “Nope”, seeming to imply that null
is not equal to itself! The reason for this is that the proper way to compare a value to null
in SQL is with the is
operator, not with =
.
Accordingly, the correct version of the above query that yields the expected result (i.e., “Yup”) would be as follows:
select case when null is null then 'Yup' else 'Nope' end as Result;
Given the following tables:
sql> SELECT * FROM runners;
+----+--------------+
| id | name |
+----+--------------+
| 1 | John Doe |
| 2 | Jane Doe |
| 3 | Alice Jones |
| 4 | Bobby Louis |
| 5 | Lisa Romero |
+----+--------------+
sql> SELECT * FROM races;
+----+----------------+-----------+
| id | event | winner_id |
+----+----------------+-----------+
| 1 | 100 meter dash | 2 |
| 2 | 500 meter dash | 3 |
| 3 | cross-country | 2 |
| 4 | triathalon | NULL |
+----+----------------+-----------+
6.What will be the result of the query below?
select * from runners where id not in (select winner_id from races)
Explain your answer and also provide an alternative version of this query that will avoid the issue that it exposes.
Surprisingly, given the sample data provided, the result of this query will be an empty set. The reason for this is as follows: If the set being evaluated by the SQL NOT IN
condition contains any values that are null, then the outer query here will return an empty set, even if there are many runner ids that match winner_ids in the races
table.
Knowing this, a query that avoids this issue would be as follows:
select * from runners where id not in (select winner_id from races where winner_id is not null)
7.Given two tables created and populated as follows:
create table dbo.envelope(id int, user_id int)
create table dbo.docs(idnum int,pageseq int,doctext varchar(100))
Insert into dbo.envelope values(1,1),(2,2),(3,3)
insert into dbo.docs(idnum,pageseq) values(1,5),(2,6),(null,0)
What will the result be from the following query:
update docs set doctext=pageseq from docs inner join envelope on envelope.id=docs.idnum
where exists (select 1 from dbo.docs where id=envelope.id)
Explain your answer.
The result of the query will be as follows:
idnum pageseq doctext
1 5 5
2 6 6
NULL 0 NULL
The EXISTS
clause in the above query is a red herring. It will always be true since ID
is not a member of dbo.docs
. As such, it will refer to the envelope
table comparing itself to itself!
The idnum
value of NULL
will not be set since the join of NULL
will not return a result when attempting a match with any value of envelope
.
8.What is wrong with this SQL query? Correct it so it executes properly.
SELECT Id, YEAR(BillingDate) AS BillingYear
FROM Invoices
WHERE BillingYear >= 2010;
The expression BillingYear in the WHERE clause is invalid. Even though it is defined as an alias in the SELECT phrase, which appears before the WHERE phrase, the logical processing order of the phrases of the statement is different from the written order. Most programmers are accustomed to code statements being processed generally top-to-bottom or left-to-right, but T-SQL processes phrases in a different order.
The correct query should be:
SELECT Id, YEAR(BillingDate) AS BillingYear
FROM Invoices
WHERE YEAR(BillingDate) >= 2010;
9.Considering the database schema displayed in the SQLServer-style diagram below, write a SQL query to return a list of all the invoices. For each invoice, show the Invoice ID, the billing date, the customer’s name, and the name of the customer who referred that customer (if any). The list should be ordered by billing date.
SELECT i.Id, i.BillingDate, c.Name, r.Name AS ReferredByName
FROM Invoices i
JOIN Customers c ON i.CustomerId = c.Id
LEFT JOIN Customers r ON c.ReferredBy = r.Id
ORDER BY i.BillingDate;
This question simply tests the candidate’s ability take a plain-English requirement and write a corresponding SQL query. There is nothing tricky in this one, it just covers the basics:
- Did the candidate remember to use a LEFT JOIN instead of an inner JOIN when joining the customer table for the referring customer name? If not, any invoices by customers not referred by somebody will be left out altogether.
- Did the candidate alias the tables in the JOIN? Most experienced T-SQL programmers always do this, because repeating the full table name each time it needs to be referenced gets tedious quickly. In this case, the query would actually break if at least the Customer table wasn’t aliased, because it is referenced twice in different contexts (once as the table which contains the name of the invoiced customer, and once as the table which contains the name of the referring customer).
- Did the candidate disambiguate the Id and Name columns in the SELECT? Again, this is something most experienced programmers do automatically, whether or not there would be a conflict. And again, in this case there would be a conflict, so the query would break if the candidate neglected to do so.
10.Assume a schema of Emp ( Id, Name, DeptId ) , Dept ( Id, Name).
If there are 10 records in the Emp
table and 5 records in the Dept
table, how many rows will be displayed in the result of the following SQL query:
Select * From Emp, Dept
Explain your answer.
The query will result in 50 rows as a “cartesian product” or “cross join”, which is the default whenever the ‘where’ clause is omitted.
11.Given a table SALARIES, such as the one below, that has m = male
and f = female
values. Swap all f
and m
values (i.e., change all f
values to m
and vice versa) with a single update query and no intermediate temp table.
Id Name Sex Salary
1 A m 2500
2 B f 1500
3 C m 5500
4 D f 500
UPDATE SALARIES SET sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END
Employee_id | First_name | Last_name | Salary | Joining_date | Department |
---|---|---|---|---|---|
1 | John | Abraham | 1000000 | 01-JAN-13 12.00.00 AM | Banking |
2 | Michael | Clarke | 800000 | 01-JAN-13 12.00.00 AM | Insurance |
3 | Roy | Thomas | 700000 | 01-FEB-13 12.00.00 AM | Banking |
4 | Tom | Jose | 600000 | 01-FEB-13 12.00.00 AM | Insurance |
5 | Jerry | Pinto | 650000 | 01-FEB-13 12.00.00 AM | Insurance |
6 | Philip | Mathew | 750000 | 01-JAN-13 12.00.00 AM | Services |
7 | TestName1 | 123 | 650000 | 01-JAN-13 12.00.00 AM | Services |
8 | TestName2 | Lname% | 600000 | 01-FEB-13 12.00.00 AM | Insurance |
Employee_ref_id | Incentive_date | Incentive_amount |
---|---|---|
1 | 01-FEB-13 | 5000 |
2 | 01-FEB-13 | 3000 |
3 | 01-FEB-13 | 4000 |
1 | 01-JAN-13 | 4500 |
2 | 01-JAN-13 | 3500 |
SQL Queries Interview Questions and Answers on “SQL Select”
Select * from employee
Select first_name, Last_Name from employee
Select first_name Employee Name from employee
Select upper(FIRST_NAME) from EMPLOYEE
Select lower(FIRST_NAME) from EMPLOYEE
select distinct DEPARTMENT from EMPLOYEE
18. Select first 3 characters of FIRST_NAME from EMPLOYEE
Oracle Equivalent of SQL Server SUBSTRING is SUBSTR, Query : select substr(FIRST_NAME,0,3) from employee SQL Server Equivalent of Oracle SUBSTR is SUBSTRING, Query : select substring(FIRST_NAME,0,3) from employee
19. Get position of ‘o’ in name ‘John’ from employee table
Oracle Equivalent of SQL Server CHARINDEX is INSTR, Query : Select instr(FIRST_NAME,'o') from employee where first_name='John' SQL Server Equivalent of Oracle INSTR is CHARINDEX, Query: Select CHARINDEX('o',FIRST_NAME,0) from employee where first_name='John'
20. Get FIRST_NAME from employee table after removing white spaces from right side
select RTRIM(FIRST_NAME) from employee
21. Get FIRST_NAME from employee table after removing white spaces from left side
select LTRIM(FIRST_NAME) from employee
22. Get length of FIRST_NAME from employee table
Oracle,MYSQL Equivalent of SQL Server Len is Length , Query :select length(FIRST_NAME) from employee SQL Server Equivalent of Oracle,MYSQL Length is Len, Query :select len(FIRST_NAME) from employee
23. Get First_Name from employee table after replacing ‘o’ with ‘$’
select REPLACE(FIRST_NAME,'o','$') from employee
24. Get First_Name and Last_Name as single column from employee table separated by a ‘_’
Oracle Equivalent of MySQL concat is '||', Query : Select FIRST_NAME|| '_' ||LAST_NAME from EMPLOYEE SQL Server Equivalent of MySQL concat is '+', Query : Select FIRST_NAME + '_' +LAST_NAME from EMPLOYEE
25. Get FIRST_NAME ,Joining year,Joining Month and Joining Date from employee table
SQL Queries in Oracle, Select FIRST_NAME, to_char(joining_date,'YYYY') JoinYear , to_char(joining_date,'Mon'), to_char(joining_date,'dd') from EMPLOYEE SQL Queries in SQL Server, select SUBSTRING (convert(varchar,joining_date,103),7,4) , SUBSTRING (convert(varchar,joining_date,100),1,3) , SUBSTRING (convert(varchar,joining_date,100),5,2) from EMPLOYEE
“SQL Order By” Interview Questions
26. Get all employee details from the employee table order by First_Name Ascending
Select * from employee order by FIRST_NAME asc
27. Get all employee details from the employee table order by First_Name descending
Select * from employee order by FIRST_NAME desc
28. Get all employee details from the employee table order by First_Name Ascending and Salary descending
Select * from employee order by FIRST_NAME asc,SALARY desc
“SQL Where Condition” Interview Questions
Select * from EMPLOYEE where FIRST_NAME='John'
30. Get employee details from employee table whose employee name are “John” and “Roy”
Select * from EMPLOYEE where FIRST_NAME in ('John','Roy')
31. Get employee details from employee table whose employee name are not “John” and “Roy”
Select * from EMPLOYEE where FIRST_NAME not in ('John','Roy')
“SQL Wild Card Search” Interview Questions
32. Get employee details from employee table whose first name starts with ‘J’
Select * from EMPLOYEE where FIRST_NAME like 'J%'
33. Get employee details from employee table whose first name contains ‘o’
Select * from EMPLOYEE where FIRST_NAME like '%o%'
34. Get employee details from employee table whose first name ends with ‘n’
Select * from EMPLOYEE where FIRST_NAME like '%n'
“SQL Pattern Matching” Interview Questions
35. Get employee details from employee table whose first name ends with ‘n’ and name contains 4 letters
Select * from EMPLOYEE where FIRST_NAME like '___n' (Underscores)
36. Get employee details from employee table whose first name starts with ‘J’ and name contains 4 letters
Select * from EMPLOYEE where FIRST_NAME like 'J___' (Underscores)
37. Get employee details from employee table whose Salary greater than 600000
Select * from EMPLOYEE where Salary >600000
38. Get employee details from employee table whose Salary less than 800000
Select * from EMPLOYEE where Salary <800000
39. Get employee details from employee table whose Salary between 500000 and 800000
Select * from EMPLOYEE where Salary between 500000 and 800000
40. Get employee details from employee table whose name is ‘John’ and ‘Michael’
Select * from EMPLOYEE where FIRST_NAME in ('John','Michael')
Interview Questions on “SQL DATE Functions”
41. Get employee details from employee table whose joining year is “2013”
SQL Queries in Oracle, Select * from EMPLOYEE where to_char(joining_date,'YYYY')='2013' SQL Queries in SQL Server, Select * from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,103),7,4)='2013'
42. Get employee details from employee table whose joining month is “January”
SQL Queries in Oracle, Select * from EMPLOYEE where to_char(joining_date,'MM')='01' or Select * from EMPLOYEE where to_char(joining_date,'Mon')='Jan' SQL Queries in SQL Server, Select * from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,100),1,3)='Jan'
SQL Queries in Oracle, Select * from EMPLOYEE where JOINING_DATE <to_date('01/01/2013','dd/mm/yyyy') SQL Queries in SQL Server (Format - “MM/DD/YYYY”), Select * from EMPLOYEE where joining_date <'01/01/2013'
44. Get employee details from employee table who joined after January 31st
SQL Queries in Oracle, Select * from EMPLOYEE where JOINING_DATE >to_date('31/01/2013','dd/mm/yyyy') SQL Queries in SQL Server and MySQL (Format - “MM/DD/YYYY”), Select * from EMPLOYEE where joining_date >'01/31/2013' SQL Queries in MySQL (Format - “YYYY-DD-MM”), Select * from EMPLOYEE where joining_date >'2013-01-31'
45. Get Joining Date and Time from employee table
SQL Queries in Oracle, select to_char(JOINING_DATE,'dd/mm/yyyy hh:mi:ss') from EMPLOYEE SQL Queries in SQL Server, Select convert(varchar(19),joining_date,121) from EMPLOYEE
46. Get Joining Date,Time including milliseconds from employee table
SQL Queries in Oracle, select to_char(JOINING_DATE,'dd/mm/yyyy HH:mi:ss.ff') from EMPLOYEE . Column Data Type should be “TimeStamp” SQL Queries in SQL Server, select convert(varchar,joining_date,121) from EMPLOYEE
47. Get difference between JOINING_DATE and INCENTIVE_DATE from employee and incentives table
Select FIRST_NAME,INCENTIVE_DATE - JOINING_DATE from employee a inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID
48. Get database date
SQL Queries in Oracle, select sysdate from dual SQL Queries in SQL Server, select getdate()
“SQL Escape Characters” Interview Questions
49. Get names of employees from employee table who has ‘%’ in Last_Name. Tip : Escape character for special characters in a query.
SQL Queries in Oracle, Select FIRST_NAME from employee where Last_Name like '%?%%' SQL Queries in SQL Server, Select FIRST_NAME from employee where Last_Name like '%[%]%'
50. Get Last Name from employee table after replacing special character with white space
SQL Queries in Oracle, Select translate(LAST_NAME,'%',' ') from employee SQL Queries in SQL Server and MySQL, Select REPLACE(LAST_NAME,'%',' ') from employee
“SQL Group By Query” Interview Questions and Answers
Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by department
52. Get department,total salary with respect to a department from employee table order by total salary descending
Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT order by Total_Salary descending
SQL Queries Interview Questions and Answers on “SQL Mathematical Operations using Group By”
53. Get department,no of employees in a department,total salary with respect to a department from employee table order by total salarydescending
Select DEPARTMENT,count(FIRST_NAME),sum(SALARY) Total_Salary from employee group by DEPARTMENT order by Total_Salary descending
54. Get department wise average salary from employee table order by salaryascending
select DEPARTMENT,avg(SALARY) AvgSalary from employee group by DEPARTMENT order by AvgSalary asc
55. Get department wise maximum salary from employee table order by salaryascending
select DEPARTMENT,max(SALARY) MaxSalary from employee group by DEPARTMENT order by MaxSalary asc
56. Get department wise minimum salary from employee table order by salary ascending
select DEPARTMENT,min(SALARY) MinSalary from employee group by DEPARTMENT order by MinSalary asc
57. Select no of employees joined with respect to year and month from employee table
SQL Queries in Oracle, select to_char (JOINING_DATE,'YYYY') Join_Year,to_char (JOINING_DATE,'MM') Join_Month,count(*) Total_Emp from employee group by to_char (JOINING_DATE,'YYYY'),to_char(JOINING_DATE,'MM')
SQL Queries in SQL Server, select datepart (YYYY,JOINING_DATE) Join_Year,datepart (MM,JOINING_DATE) Join_Month,count(*) Total_Emp from employee group by datepart(YYYY,JOINING_DATE), datepart(MM,JOINING_DATE)
SQL Queries in MySQL, select year (JOINING_DATE) Join_Year,month (JOINING_DATE) Join_Month,count(*) Total_Emp from employee group by year(JOINING_DATE), month(JOINING_DATE)
58. Select department,total salary with respect to a department from employee table where total salary greater than 800000 order by Total_Salary descending
Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT having sum(SALARY) >800000 order by Total_Salary desc