Most SQL Queries Interview

Here we will explain some important sql query basically interviewer can ask in interview . so i will discuss sql query with the help of company database and employee table.

Now Create Database Name----"Company"

Create Table Name---------"Employee"
Note :- field name  Employee_id-Primary Key

Employee_Id	First_Name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

'Employee' Table name

(i)

***GET  ALL EMPLOYEE DETAILS FROM THE EMPLOYEE TABLE-

select * from Employee

Result:Here Output all employee details

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

(ii)

***GET  FIRST_NAME FROM EMPLOYEE TABLE IN UPPERCASE-

Table Name---------"Employee"

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

Use a  UPPER function for query...

select UPPER(First_name) from Employee

Result: Here Output all upper alphabets with the help of UPPER function..

(No column name)

PANKAJ

RANCHIKI

GAURAV

VIJAY

(iii)

***GET  FIRST_NAME FROM EMPLOYEE TABLE IN LOWERCASE-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

Note:- for use Lower function  in this query...

select LOWER(First_name) from Employee

Output :All alphabets show in lower

(No column name)

pankaj

ranchiki

gaurav

vijay

(iv)

***GET  UNIQUE DEPARTMENT FROM EMPLOYEE TABLE-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

Use distinct keyword for this query...

select distinct Department from employee

Result: Output unique Department from employee table...

Department

CA

HR Manager

SALES MANAGER

Sr. Programmer

***SELECT FIRST 3 CHARACTERS OF FIRST NAME FROM EMPLOYEE-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select SUBSTRING(First_name,0,3) from Employee

Output :

Pa

Ra

Ga

Vi

***GET POSTION OF ‘k’ IN NAME ‘PANKAJ’ FROM EMPLOYEE TABLE-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select CHARINDEX('k', First_name,0) from Employee where First_name='pankaj'

Output :

(No column name)

4

***GET FIRST NAME FROM EMPLOYEE TABLE AFTER REMOVING WHITE SPACES FROM RIGHT SIDE-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select RTRIM(First_name) from Employee

Output :

(No column name)

Pankaj

Ranchiki

Gaurav

Vijay

***GET FIRST NAME FROM EMPLOYEE TABLE AFTER REMOVING WHITE SPACES FROM LEFT SIDE-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select LTRIM(First_name) from Employee

Output :

(No column name)

Pankaj

Ranchiki

Gaurav

Vijay

***GET LENGTH OF FIRST NAME FROM EMPLOYEE TABLE-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select LEN(First_name) from Employee

Output :

(No column name)

6

8

6

5

***GET  FIRST NAME FROM  EMPLOYEE TABLE AFTER REPLACING ‘K’ WITH ‘&’-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select REPLACE(First_name,'k','&')from Employee

Output :

(No column name)

Pan&aj

Ranchi&i

Gaurav

Vijay

***GET FIRST NAME AND LAST NAME AS SINGLE COLUMN FROM EMPLOYEE TABLE SEPRATED BY A ‘     ‘-Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select First_name +''+ Last_name from Employee

Output :

(No column name)

PankajKumar

RanchikiSingh

GauravSingh

VijaySingh

**GET FIRST NAME , JOINING YEAR, JOINING MONTH, AND JOINING DATE FROM EMPLOYEE TABLE-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

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

Output :

(No column name)	(No column name)	(No column name)
2013	Jan	1
2013	Jan	1
2013	Feb	1
2013	Feb	1

**GET ALL EMPLOYEE DETAILS FROM THE EMPLOYEE TABLE ORDER BY FIRST NAME ASCENDING-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select * from Employee order by First_name asc

Output :

Employee_id	First_name	Last_name	Salary	Joining_date	Department
3	Gaurav	Singh	700000	00:00.0	CA
1	Pankaj	Kumar	1000000	00:00.0	Sr. Programmer
2	Ranchiki	Singh	800000	00:00.0	HR Manager
4	Vijay	Singh	600000	00:00.0	SALES MANAGER

**GET ALL EMPLOYEE DETAILS FROM THE EMPLOYEE TABLE ORDER BY FIRST NAME DESCENDING-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select * from Employee order by First_name desc

Output :

Employee_id	First_name	Last_name	Salary	Joining_date	Department
4	Vijay	Singh	600000	00:00.0	SALES MANAGER
2	Ranchiki	Singh	800000	00:00.0	HR Manager
1	Pankaj	Kumar	1000000	00:00.0	Sr. Programmer
3	Gaurav	Singh	700000	00:00.0	CA

**GET ALL EMPLOYEE DETAILS FROM THE EMPLOYEE TABLE ORDER BY FIRST NAME  ASCENDING AND SALARY DESCENDING-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select * from Employee order by First_name asc,Salary desc

Output :

Employee_id	First_name	Last_name	Salary	Joining_date	Department
3	Gaurav	Singh	700000	00:00.0	CA
1	Pankaj	Kumar	1000000	00:00.0	Sr. Programmer
2	Ranchiki	Singh	800000	00:00.0	HR Manager
4	Vijay	Singh	600000	00:00.0	SALES MANAGER

**GET EMPLOYEE DETAILS FROM EMPLOYEE TABLE WHOSE EMPLOYEE NAME IS ‘RANCHIKI’

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select * from Employee where First_name='ranchiki'

Output :

Employee_id	First_name	Last_name	Salary	Joining_date	Department
2	Ranchiki	Singh	800000	00:00.0	HR Manager

**GET EMPLOYEE DETAILS FROM EMPLOYEE TABLE WHOSE EMPLOYEE NAME ARE ‘PANKAJ’ AND ‘RANCHIKI’

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select * from Employee where First_name in ('pankaj','ranchiki')

Output :

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	00:00.0	Sr. Programmer
2	Ranchiki	Singh	800000	00:00.0	HR Manager

**GET EMPLOYEE DETAILS FROM EMPLOYEE TABLE WHOSE EMPLOYEE NAME ARE NOT ‘PANKAJ’ AND ‘RANCHIKI’

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select * from Employee where First_name not in ('pankaj','ranchiki')

Output :

Employee_id	First_name	Last_name	Salary	Joining_date	Department
3	Gaurav	Singh	700000	00:00.0	CA
4	Vijay	Singh	600000	00:00.0	SALES MANAGER

**GET EMPLOYEE DETAILS FROM EMPLOYEE TABLE WHOSE FIRST  NAME STARTS WITH ‘P’

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select * from Employee where First_name like 'p%'

Output :

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	00:00.0	Sr. Programmer

**GET EMPLOYEE DETAILS FROM EMPLOYEE TABLE WHOSE FIRST  NAME CONTAINS ‘K’

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select* from Employee where First_name like '%k%'

Output :

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	00:00.0	Sr. Programmer
2	Ranchiki	Singh	800000	00:00.0	HR Manager

**GET EMPLOYEE DETAILS FROM EMPLOYEE TABLE WHOSE FIRST  NAME ENDS WITH ‘I’

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select* from Employee where First_name like '%i'

Output :

Employee_id	First_name	Last_name	Salary	Joining_date	Department
2	Ranchiki	Singh	800000	00:00.0	HR Manager

**GET EMPLOYEE DETAILS FROM EMPLOYEE TABLE WHOSE FIRST  NAME ENDS WITH ‘I’  AND NAME CONTAINS 8 LETTERS

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select* from Employee where First_name like '_______i'

Output :

Employee_id	First_name	Last_name	Salary	Joining_date	Department
2	Ranchiki	Singh	800000	00:00.0	HR Manager

**GET EMPLOYEE DETAILS FROM EMPLOYEE TABLE WHOSE SALARY BETWEEN 500000 AND 800000

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select * from Employee where Salary between 500000 and 800000

Output :

Employee_id	First_name	Last_name	Salary	Joining_date	Department
2	Ranchiki	Singh	800000	00:00.0	HR Manager
3	Gaurav	Singh	700000	00:00.0	CA
4	Vijay	Singh	600000	00:00.0	SALES MANAGER

**GET EMPLOYEE DETAILS FROM EMPLOYEE TABLE WHOSE JOINING YEAR IS “2013”

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select * from Employee where SUBSTRING(convert (varchar,Joining_date,103),7,4)='2013'

Output :

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	00:00.0	Sr. Programmer
2	Ranchiki	Singh	800000	00:00.0	HR Manager
3	Gaurav	Singh	700000	00:00.0	CA

**GET EMPLOYEE DETAILS FROM EMPLOYEE TABLE WHOSE JOINING MONTH IS ‘JANUARY’

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select * from Employee whereSUBSTRING(convert(varchar,joining_date,100),1,3)='Jan'

Output :

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	00:00.0	Sr. Programmer
2	Ranchiki	Singh	800000	00:00.0	HR Manager

**GET JOINING_DATE  AND TIME FROM EMPLOYEE TABLE

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select CONVERT(varchar(19),Joining_date,121) from Employee

Output :

(No column name)

01/01/2013 00:00

01/01/2013 00:00

02/01/2013 00:00

02/01/2013 00:00

**GET  LAST_NAME FROM EMPLOYEE TABLE AFTER REPLACING SPECIAL CHARACTER WITH WHITE SPACE-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select  REPLACE(Last_name, '%','') from Employee

Output :

(No column name)

Kumar

Singh

Singh

Singh

**GET  DEPARTMENT ,TOTAL SALARY WITH RESPECT TO A DEPARTMENT FROM EMPLOYEE TABLE-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select department,SUM(salary) totalsalary from Employee group by department

Output :

department	totalsalary
CA	700000
HR Manager	800000
SALES MANAGER	600000
Sr. Programmer	1000000

**GET  DEPARTMENT ,TOTAL SALARY WITH RESPECT TO A DEPARTMENT FROM EMPLOYEE TABLE ORDER BY TOTAL  SALARY DESCENDING-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select department,SUM(salary) totalsalary from Employee group by Department order by totalsalary desc

Output :

department	totalsalary
Sr. Programmer	1000000
HR Manager	800000
CA	700000
SALES MANAGER	600000