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

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

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

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

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

sql> select Count(*) as Total from Orders;
+-------+
| Total |
+-------+
|  100  |
+-------+

sql> select Count(*) as Cust123Total from Orders where customer_id = '123'
+--------------+
| Cust123Total |
+--------------+
|      15      |
+--------------+

sql> select Count(*) from Orders where customer_id <> '123'

select case when null = null then 'Yup' else 'Nope' end as Result;

select case when null is null then 'Yup' else 'Nope' end as Result;

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     |
+----+----------------+-----------+

select * from runners where id not in (select winner_id from races)

select * from runners where id not in (select winner_id from races where winner_id is not null)

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)

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)

idnum	pageseq	doctext
1	5	5
2	6	6
NULL	0	NULL

SELECT Id, YEAR(BillingDate) AS BillingYear 
FROM Invoices
WHERE BillingYear >= 2010;

SELECT Id, YEAR(BillingDate) AS BillingYear
FROM Invoices
WHERE YEAR(BillingDate) >= 2010;

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;

Select * From Emp, Dept

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

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

26. Get all employee details from the employee table order by First_Name Ascending

Select * from employee order by FIRST_NAME asc

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

Select * from EMPLOYEE where FIRST_NAME='John'

Select * from EMPLOYEE where FIRST_NAME in ('John','Roy')

Select * from EMPLOYEE where FIRST_NAME not in ('John','Roy')

Select * from EMPLOYEE where FIRST_NAME like 'J%'

Select * from EMPLOYEE where FIRST_NAME like '%o%'

Select * from EMPLOYEE where FIRST_NAME like '%n'

Select * from EMPLOYEE where FIRST_NAME like '___n' (Underscores)

Select * from EMPLOYEE where FIRST_NAME like 'J___' (Underscores)

Select * from EMPLOYEE where Salary >600000

Select * from EMPLOYEE where Salary <800000

Select * from EMPLOYEE where Salary between 500000 and 800000

Select * from EMPLOYEE where FIRST_NAME in ('John','Michael')

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'

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'

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

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

Select FIRST_NAME,INCENTIVE_DATE - JOINING_DATE from employee a inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

SQL Queries in Oracle, select sysdate from dual

SQL Queries in SQL Server, select getdate()

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

Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by department

Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT order by Total_Salary descending

Select DEPARTMENT,count(FIRST_NAME),sum(SALARY) Total_Salary from employee group by DEPARTMENT order by Total_Salary descending

select DEPARTMENT,avg(SALARY) AvgSalary from employee group by DEPARTMENT order by AvgSalary asc

select DEPARTMENT,max(SALARY) MaxSalary from employee group by DEPARTMENT order by MaxSalary asc

select DEPARTMENT,min(SALARY) MinSalary from employee group by DEPARTMENT order by MinSalary asc

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)

Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT having sum(SALARY) >800000 order by Total_Salary desc