What are the frequently asked basic and complex queries ( SQL) during a tech interview?

1) joining more than 3 tables (Solution)
2) finding duplicate records from a table (Solution)
select COUNT(*),* from testDuplicateRds group by FirstName,LastName,Salary  having COUNT(*)>1
3) second highest salary of employee using rownum (Solution)


select min(Salary) from  (select distinct top  3 Salary,EmpName  from Employee order by Salary desc) as emp

 select top 1* from ( select RANK() over(order by Salary desc)as rn, Salary,EmpName from Employee ) as emp where rn=3

http://www.w3resource.com/sql-exercises/sql-subqueries-exercises.php
http://www.interviewquestionspdf.com/2014/08/ms-sql-server-trickycomplex-interview.html
http://www.bullraider.com/database/sql-tutorial/7-complex-queries-in-sql
http://www.complexsql.com/complex-sql-queries-examples-with-answers/
http://www.interviewquestionspdf.com/2014/07/sql-queries-interview-questions-answers.html
http://narendra86.blogspot.in/2013/10/top-80-sql-query-interview-questions.html
http://www.programmerinterview.com/index.php/database-sql/advanced-sql-interview-questions-continued-part-2/

1. Reverse a string without using REVERSE function. [reverse a string without using TSQL's REVERSE() function]

declare @str1 varchar(max) ='abcdui'

declare @len int= LEN(@str1)
declare @count int =LEN(@str1)
declare @str2 varchar(max)=''
--select SUBSTRING('abcdui',6,1)
while(@count>=1)
begin
set @str2=@str2+ SubString(@str1,@count,1)
select @str2;
set @count=@count-1;
end
select @str2

2. Generate hierarchy of Manager & Employee in Employee table with EmpID, EmpName, MgrID columns.

3. How do you sync rows in 2 tables, rows in first table being INSERTED/UPDATED/DELETED, and no modifications on the second table.

4. Generate Running total (sum) for goods sold by a Sales Rep for every day month wise for a given year (SalesRepID, Year, Month, Day, TotalSale).[Calculating Running Totals]

5. Get 5 Employees from every Department getting highest salary (EmpID, DeptID, Salary)
;with cte as
(select DENSE_RANK() over(PARTITION by DeptId order by  Salary desc) r, DeptId,Salary  from Employee E ) 

select distinct DeptId,Salary from cte where r in (1,2,3)

6. Get Employee with 2nd or nth highest salary [SQL Trivia - Find second (or nth) Highest Salary or Marks]

7. How to DELETE duplicate records from a table with a single SELECT stmt.[Identify & Delete Duplicate records from a table]
;with cte
as
(select ROW_NUMBER() over(partition by FirstName,lastname order by Salary ) rno ,* from testDuplicateRds )

delete from cte where rno>1

8. Allocate position to Students of a class based upon their marks, if same marks same position should be applied. [RANKING FUNCTIONS | ROW_NUMBER, RANK, DENSE_RANK, NTILE]

9. For these 2 tables: Product(ProductID, ProductName), Sales(SalesOrderID, ProductID, SaleDate)
- Get the list of Products that were sold last year.
- Get the list of Products that were NOT sold last year.

10. For any given date, get the Last Date of: Previous Month & Current Month & Next Month.

11. Difference Between:
- WHERE, GROUP BY and HAVING clause
- EXECUTE (or EXEC) vs sp_executesql
- ISNULL vs COALESCE
- PIVOT and UNPIVOT
- TRUNCATE, DELETE and DROP
- Stored Procedures vs Functions
- UNION ALL vs UNION
- Temporary Tables vs Table Variables
- THROW vs RAISERROR
- NOLOCK and READPAST table hints
- Decimal and Numeric datatypes
- Clustered vs NonClustered Indexes
- CURSORS vs WHILE loops
- CROSS APPLY vs OUTER APPLY operators
- Scalar, Table Valued (ITV), MultiSelect (MTV) - UDFs (User Defined Functions)
https://www.quora.com/What-are-the-frequently-asked-basic-and-complex-queries-SQL-during-a-tech-interview

INTRODUCTION

SQL queries most asked in .NET/SQL Server job interviews. These tricky queries may be required in your day to day database usage.

BACKGROUND

This article demonstrates some commonly asked SQL queries in a job interview. I will be covering some of the common but tricky queries like:- 

(i) Finding the nth highest salary of an employee.
(ii) Finding TOP X records from each group.
(iii) Deleting duplicate rows from a table.

NOTE : All the SQL mentioned in this article has been tested under SQL Server 2005. 

(i) Finding the nth highest salary of an employee.

Create a table named Employee_Test and insert some test data as:-

Hide   Copy Code

CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);

It is very easy to find the highest salary as:-

Hide   Copy Code

--Highest Salary
select max(Emp_Sal) from Employee_Test

Now, if you are asked to find the 3rd highest salary, then the query is as:-

Hide   Copy Code

--3rd Highest Salary
select min(Emp_Sal) from Employee_Test where Emp_Sal in
(select distinct top 3 Emp_Sal from Employee_Test order by Emp_Sal desc)

The result is as :- 1200 
To find the nth highest salary, replace the top 3 with top n (n being an integer 1,2,3 etc.)

Hide   Copy Code

--nth Highest Salary
select min(Emp_Sal) from Employee_Test where Emp_Sal in
(select distinct top n Emp_Sal from Employee_Test order by Emp_Sal desc)

(ii) Finding TOP X records from each group

Create a table named photo_test and insert some test data as :-

Hide   Copy Code

create table photo_test
(
pgm_main_Category_id int,
pgm_sub_category_id int,
file_path varchar(MAX)
)

insert into photo_test values
(17,15,'photo/bb1.jpg');     
                                                 
insert into photo_test values(17,16,'photo/cricket1.jpg');                                                    
insert into photo_test values(17,17,'photo/base1.jpg');                                                       
insert into photo_test values(18,18,'photo/forest1.jpg');                                                       
insert into photo_test values(18,19,'photo/tree1.jpg');                                                           
insert into photo_test values(18,20,'photo/flower1.jpg');                                                     
insert into photo_test values(19,21,'photo/laptop1.jpg');                                                       
insert into photo_test values(19,22,'photo/camer1.jpg');                                                 

insert into photo_test values(19,23,'photo/cybermbl1.jpg');                                                    
insert into photo_test values
(17,24,'photo/F1.jpg');

There are three groups of pgm_main_category_id each with a value of 17 (group 17 has four records),18 (group 18 has three records) and 19 (group 19 has three records). 
Now, if you want to select top 2 records from each group, the query is as follows:-

Hide   Copy Code

select pgm_main_category_id,pgm_sub_category_id,file_path from
(
select pgm_main_category_id,pgm_sub_category_id,file_path,
rank() over (partition by pgm_main_category_id order by pgm_sub_category_id asc) as rankid
from photo_test
) photo_test
where rankid < 3 -- replace 3 by any number 2,3 etc for top2 or top3.
order by pgm_main_category_id,pgm_sub_category_id

The result is as:-

Hide   Copy Code

pgm_main_category_id pgm_sub_category_id file_path
17                    15                    photo/bb1.jpg
17                    16                    photo/cricket1.jpg
18                    18                    photo/forest1.jpg
18                    19                    photo/tree1.jpg
19                    21                    photo/laptop1.jpg
19                    22                    photocamer1.jpg

(iii) Deleting duplicate rows from a table

A table with a primary key doesn’t contain duplicates. But if due to some reason, the keys have to be disabled or when importing data from other sources, duplicates come up in the table data, it is often needed to get rid of such duplicates. 
This can be achieved in tow ways :- 
(a) Using a temporary table. 
(b) Without using a temporary table. 

(a) Using a temporary or staging table

Let the table employee_test1 contain some duplicate data like:-

Hide   Copy Code

CREATE TABLE Employee_Test1
(
Emp_ID INT,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test1 VALUES (1,'Anees',1000);
INSERT INTO Employee_Test1 VALUES (2,'Rick',1200);
INSERT INTO Employee_Test1 VALUES (3,'John',1100);
INSERT INTO Employee_Test1 VALUES (4,'Stephen',1300);
INSERT INTO Employee_Test1 VALUES (5,'Maria',1400);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);

Step 1: Create a temporary table from the main table as:-

Hide   Copy Code

select top 0* into employee_test1_temp from employee_test1

Step2 : Insert the result of the GROUP BY query into the temporary table as:-

Hide   Copy Code

insert into employee_test1_temp
select Emp_ID,Emp_name,Emp_Sal
from employee_test1
group by Emp_ID,Emp_name,Emp_Sal

Step3: Truncate the original table as:-

Hide   Copy Code

truncate table employee_test1

Step4: Fill the original table with the rows of the temporary table as:-

Hide   Copy Code

insert into employee_test1
select * from employee_test1_temp

Now, the duplicate rows from the main table have been removed.

Hide   Copy Code

select * from employee_test1

gives the result as:-

Hide   Copy Code

Emp_ID Emp_name   Emp_Sal
1 Anees    1000
2 Rick    1200
3 John    1100
4 Stephen    1300
5 Maria    1400
6 Tim    1150

(b) Without using a temporary table

Hide   Copy Code

;with T as
(
 select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank
 from employee_test1
)

delete
from T
where rank > 1

The result is as:-

Hide   Copy Code

Emp_ID Emp_name   Emp_Sal
1 Anees    1000
2 Rick    1200
3 John    1100
4 Stephen    1300
5 Maria    1400
6 Tim    1150

What does UNION do? What is the difference between UNION and UNION ALL?



View the answer →











List and explain the different types of JOIN clauses supported in ANSI-standard SQL.



View the answer →











Consider the following two query results:
SELECT count(*) AS total FROM orders;

+-------+
| total |
+-------+
|  100  |
+-------+

SELECT count(*) AS cust_123_total FROM orders WHERE customer_id = '123';

+----------------+
| cust_123_total |
+----------------+
|       15       |
+----------------+


Given the above query results, what will be the result of the query below?
SELECT count(*) AS cust_not_123_total FROM orders WHERE customer_id <> '123'




View the answer →






Find top SQL talent today. Toptal can match you with the best developers to finish your project.
HIRE TOPTAL’S SQL DEVELOPERS









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;




View the answer →











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


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.



View the answer →











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.



View the answer →











What is wrong with this SQL query? Correct it so it executes properly.
SELECT Id, YEAR(BillingDate) AS BillingYear 
FROM Invoices
WHERE BillingYear >= 2010;




View the answer →











Given these contents of the Customers table:
Id Name   ReferredBy
1 John Doe  NULL
2 Jane Smith  NULL
3 Anne Jenkins  2
4 Eric Branford  NULL
5 Pat Richards  1
6 Alice Barnes  2


Here is a query written to return the list of customers not referred by Jane Smith:
SELECT Name FROM Customers WHERE ReferredBy <> 2;


What will be the result of the query? Why? What would be a better way to write it?



View the answer →











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.





View the answer →











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.



View the answer →











Given a table SALARIES, such as the one below, that has m = male and f = femalevalues. 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




View the answer →











Given two tables created as follows
create table test_a(id numeric);

create table test_b(id numeric);

insert into test_a(id) values
  (10),
  (20),
  (30),
  (40),
  (50);

insert into test_b(id) values
  (10),
  (30),
  (50);


Write a query to fetch values in table test_a that are and not in test_b without using the NOT keyword.



View the answer →











Given a table TBL with a field Nmbr that has rows with the following values:

1, 0, 0, 1, 1, 1, 1, 0, 0, 1, 0, 1, 0, 1, 0, 1

Write a query to add 2 where Nmbr is 0 and add 3 where Nmbr is 1.



View the answer →











Write a SQL query to find the 10th highest employee salary from an Employee table. Explain your answer.

(Note: You may assume that there are at least 10 records in the Employee table.)



View the answer →











Write a SQL query using UNION ALL (not UNION) that uses the WHERE clause to eliminate duplicates. Why might you want to do this?



View the answer →











Given the following tables:
SELECT * FROM users;

user_id  username
1        John Doe                                                                                            
2        Jane Don                                                                                            
3        Alice Jones                                                                                         
4        Lisa Romero

SELECT * FROM training_details;

user_training_id  user_id  training_id  training_date
1                 1        1            "2015-08-02"
2                 2        1            "2015-08-03"
3                 3        2            "2015-08-02"
4                 4        2            "2015-08-04"
5                 2        2            "2015-08-03"
6                 1        1            "2015-08-02"
7                 3        2            "2015-08-04"
8                 4        3            "2015-08-03"
9                 1        4            "2015-08-03"
10                3        1            "2015-08-02"
11                4        2            "2015-08-04"
12                3        2            "2015-08-02"
13                1        1            "2015-08-02"
14                4        3            "2015-08-03"


Write a query to to get the list of users who took the a training lesson more than once in the same day, grouped by user and training lesson, each ordered from the most recent lesson date to oldest date.



View the answer →











What is an execution plan? When would you use it? How would you view the execution plan?



View the answer →











List and explain each of the ACID properties that collectively guarantee that database transactions are processed reliably.



View the answer →











What is a key difference between Truncate and Delete?



View the answer →











Given a table dbo.users where the column user_id is a unique identifier, how can you efficiently select the first 100 odd user_id values from the table?

(Assume the table contains well over 100 records with odd user_id values.)



View the answer →











How can you select all the even number records from a table? All the odd number records?



View the answer →











What are the NVL and the NVL2 functions in SQL? How do they differ?



View the answer →











What is the difference between the RANK() and DENSE_RANK() functions? Provide an example.



View the answer →











What is the difference between the WHERE and HAVING clauses?





https://www.toptal.com/sql/interview-questions