We have put together list of practice SQL Queries for Interview. Before you start practicing these SQL Queries, use the provided code to Create required Database tables. Fell free to modify these queries to find alternate methods to get the same answers. Use our forum to post questions related to Database programming and one of our admins or registered users will reply back to your query.

All these practice SQL Queries have been tested successfully on Microsoft SQL Server 2014 and Microsoft SQL Server 2017. You would need to run the provided script code in New Query Window, within Microsoft SQL Server Management Studio to Create the Database and Require tables. Under the Code Create Sample Data Section, I have provided the Script Code to create sample data for all tables. Run the this code after you have created the required tables.

Practice these Frequently Asked SQL Queries once before your Interview to get ready to perform them in front of the interviewer.  Please Like, Share and Comment my work to motivate me to produce  more stuff for you guys.

CODE TO CREATE DATABASE AND TABLES

CREATE DATABASE [Practice_SQL]

Create Table [dbo].[Department] (
[Department_ID] [int] Not Null,
[Name] [nvarchar] (50) Not null,
[Location] [nvarchar] (50) Not Null
)

Create Table [dbo].[Employee] (
[Employee_ID] [int] Not Null,
[Employee_Name] [nvarchar] (50) Not null,
[Job] [nvarchar] (50) Not Null,
[Manager_ID] [int] Null,
[Hiredate] [Date] Not Null,
[Salary] [int] Not Null,
[Commission] [int] Null,
[Department_ID] [int] Not Null
)

CODE TO CREATE SAMPLE DATA

INSERT [dbo].[Department] ([Department_ID], [Name], [Location]) VALUES (10, N’ACCOUNTING’, N’NEW YORK’)

INSERT [dbo].[Department] ([Department_ID], [Name], [Location]) VALUES (20, N’FINANCE’, N’DALLAS’)

INSERT [dbo].[Department] ([Department_ID], [Name], [Location]) VALUES (30, N’SALES’, N’SEATTLE’)

INSERT [dbo].[Department] ([Department_ID], [Name], [Location]) VALUES (40, N’OPERATIONS’, N’CHICAGO’)

INSERT [dbo].[Department] ([Department_ID], [Name], [Location]) VALUES (1, N’HEADQUARTERS’, N’BERLIN’)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6369, N’MILLER’, N’CLERK’, 6682, CAST(N’2000-01-17′ AS Date), 25000, 1550, 10)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6499, N’FORD’, N’SENIOR ANALYST’, 6698, CAST(N’1999-10-25′ AS Date), 60000, 2000, 20)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6500, N’WINSTON’, N’MANAGER’, NULL, CAST(N’2001-01-20′ AS Date), 50000, 2000, 30)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6501, N’MARK’, N’SALESMAN’, 6968, CAST(N’1980-05-17′ AS Date), 30000, 2000, 40)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6502, N’RAUL’, N’MANAGER’, 7839, CAST(N’1993-06-18′ AS Date), 50000, 2000, 30)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6503, N’STEVEN’, N’SALESMAN’, 6968, CAST(N’2015-02-28′ AS Date), 30000, 2000, 20)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6504, N’KERRY’, N’MANAGER’, 7902, CAST(N’1981-02-22′ AS Date), 50000, 2000, 30)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6505, N’SONIA’, N’MANAGER’, 7839, CAST(N’1981-08-21′ AS Date), 50000, 2000, 30)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6506, N’JAMAL’, N’CLERK’, 6902, CAST(N’1993-01-17′ AS Date), 25000, 2000, 20)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6507, N’HIZA’, N’CLERK’, 6902, CAST(N’1997-04-22′ AS Date), 25000, 2000, 40)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6508, N’ROYSTON’, N’CLERK’, 6682, CAST(N’2007-06-30′ AS Date), 25000, 2000, 40)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6509, N’KEVIN’, N’PRESIDENT’, 1111, CAST(N’2005-01-21′ AS Date), 500000, 2000, 1)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6510, N’TURNER’, N’VICE PRESIDENT’, 1111, CAST(N’2009-03-10′ AS Date), 200000, 2000, 1)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6511, N’ADAMS’, N’CEO’, 1111, CAST(N’2011-02-15′ AS Date), 1000000, 2000, 1)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6512, N’BLAKES’, N’JUNIOR ANALYST’, 6699, CAST(N’2008-02-20′ AS Date), 30000, 2000, 20)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6513, N’JONES’, N’IT CONSULTANT’, 6566, CAST(N’1998-11-26′ AS Date), 35000, 2000, 30)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6514, N’MARSHALL’, N’JUNIOR ANALYST’, 6699, CAST(N’2000-10-25′ AS Date), 38000, 2000, 40)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6515, N’LAURA’, N’IT CONSULTANT’, 6566, CAST(N’2001-07-20′ AS Date), 59000, 2000, 30)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6516, N’WARD’, N’JUNIOR ANALYST’, 6698, CAST(N’2005-03-15′ AS Date), 30000, 2000, 20)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6517, N’COUTLER’, N’SENIOR ANALYST’, 6721, CAST(N’2006-05-18′ AS Date), 65000, 2000, 40)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6518, N’LOPES’, N’SENIOR ANALYST’, 6722, CAST(N’2001-09-13′ AS Date), 75000, 2000, 30)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6519, N’RALPH’, N’SENIOR ANALYST’, 6725, CAST(N’2004-10-07′ AS Date), 65000, 2000, 20)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6520, N’ROZA’, N’SENIOR ANALYST’, 6698, CAST(N’1982-11-09′ AS Date), 90000, 2000, 40)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6521, N’RONNIE’, N’BUSINESS CONSULTANT’, 6566, CAST(N’1994-03-02′ AS Date), 55000, 2000, 20)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6522, N’MICHAEL’, N’JUNIOR ANALYST’, 6566, CAST(N’1999-01-30′ AS Date), 30000, 2000, 20)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6523, N’ROCKY’, N’BUSINESS CONSULTANT’, 6698, CAST(N’2011-02-06′ AS Date), 55000, 2000, 30)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6524, N’BELLA’, N’JUNIOR ANALYST’, 6698, CAST(N’2017-04-11′ AS Date), 30000, 2000, 20)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6525, N’RUSSO’, N’IT CONSULTANT’, 6721, CAST(N’2010-06-17′ AS Date), 65000, 2000, 30)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (6526, N’AMIR’, N’IT CONSULTANT’, 6722, CAST(N’1992-08-19′ AS Date), 60000, 2000, 20)

INSERT [dbo].[Employee] ([Employee_ID], [Employee_Name], [Job], [Manager_ID], [Hiredate], [Salary], [Commission], [Department_ID]) VALUES (1111, N’TECHHOWDY’, N’FOUNDER’, NULL, CAST(N’1980-01-01′ AS Date), 100000000, 100000, 1

LIST OF QUESTIONS TO PRACTICE

  1. Display all the Employees who are getting 35000 and excess salaries in department 20
  2. Display all the Managers working in 20 & 30 department.
  3. Display all the Managers who don’t have a Manager
  4. Display all the employees who are getting some commission with their designation is neither MANANGER nor SENIOR ANALYST
  5. Display all the JUNIOR ANALYST whose name doesn’t ends with ‘S’
  6. Display all the Employees whose naming is having letter ‘E’ as the last but one character
  7. Display all the employees who total salary is more than 60000.
  8. Display all the employees who are getting some commission in department 30 & 40.
  9. Display all the managers whose name doesn’t start with A & S
  10. Display all the employees who earning salary not in the range of 25000 and 50000 in department 10 & 20
  1. Fetch All Records of Employee Table with Row ID.
  2. Fetch ALTERNATE records from Employee Table. (EVEN NUMBERED)
  3. Fetch ALTERNATE records from Employee Table. (ODD NUMBERED)
  4. Find the Employee with the 3rd Maximum Salary 
  5. Find the Employee with the 3rd Minimum Salary 
  6. Select the First Record From Table
  7. How to Get the Nth MAX Salary
  8. How to Delete Duplicate Rows From Employee
  9. Display the Number Of Employees in Each Department.
  10. Annual Salary for each Employee is provided – Now Display the Monthly and Annual Salary For each Employee.

ANSWERS FOR ABOVE QUESTIONS

ANSWERS FOR QUERIES BASED ON OPERATORS

  1. SELECT * FROM EMPLOYEE WHERE SALARY > 35000 AND DEPARTMENT_ID = 20;
  2. SELECT * FROM EMPLOYEE WHERE DEPARTMENT_ID IN(20,30) AND JOB = ‘MANAGER’;
  3. SELECT * FROM EMPLOYEE WHERE JOB = ‘MANAGER’ AND MANAGER_ID IS NULL;
  4. SELECT * FROM EMPLOYEE WHERE JOB NOT IN(‘MANAGER’, ‘SENIOR ANALYST’) AND COMMISSION > 0
  5. SELECT * FROM EMPLOYEE WHERE JOB = ‘JUNIOR ANALYST’ AND EMPLOYEE_NAME NOT LIKE ‘%S’
  6. SELECT * FROM EMPLOYEE WHERE EMPLOYEE_NAME LIKE ‘%E_’
  7. SELECT EMPLOYEE_NAME, SALARY, COMMISSION, JOB, (SALARY + COMMISSION) AS “TOTAL SALARY > 60K” FROM EMPLOYEE WHERE (SALARY + COMMISSION) > 60000
  8. SELECT EMPLOYEE_NAME, DEPARTMENT_ID, COMMISSION FROM EMPLOYEE WHERE COMMISSION > 0 AND DEPARTMENT_ID IN (30,40)
  9. SELECT * FROM EMPLOYEE WHERE JOB = ‘MANAGER’ AND EMPLOYEE_NAME NOT LIKE ‘A%’ AND EMPLOYEE_NAME NOT LIKE ‘S%’
  10. SELECT * FROM EMPLOYEE WHERE (SALARY NOT BETWEEN 25000 AND 50000) AND DEPARTMENT_ID IN(20,30)

ANSWERS COMPLEX SQL QUERIES

  1. SELECT EMPLOYEE_NAME, EMPLOYEE_ID, ROW_NUMBER() OVER(ORDER BY EMPLOYEE_ID DESC) AS ‘ROW ID’ FROM EMPLOYEE
  2. SELECT * FROM (SELECT EMPLOYEE_NAME, EMPLOYEE_ID, ROW_NUMBER() OVER(ORDER BY EMPLOYEE_ID DESC) AS ‘ROW NUMBER’ FROM EMPLOYEE) D WHERE D.[ROW NUMBER] % 2 = 0;
  3. SELECT * FROM (SELECT EMPLOYEE_NAME, EMPLOYEE_ID, ROW_NUMBER() OVER(ORDER BY EMPLOYEE_ID DESC) AS ‘ROW NUMBER’ FROM EMPLOYEE) D WHERE D.[ROW NUMBER] % 2 = 1;
  4. SELECT DISTINCT TOP 1 SALARY FROM (SELECT TOP 3 SALARY FROM EMPLOYEE ORDER BY SALARY DESC) A ORDER BY SALARY
  5. SELECT DISTINCT TOP 1 SALARY FROM (SELECT TOP 3 SALARY FROM EMPLOYEE) A ORDER BY SALARY DESC
  6. SELECT TOP 1 * FROM EMPLOYEE
  7. SELECT DISTINCT SALARY FROM EMPLOYEE E WHERE 8 = (SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEE B WHERE E.SALARY>= B.SALARY);
  8. WITH TEMP (EMPLOYEE_NAME, DUPLICATERECORDCOUNT)
    AS
    (
    SELECT EMPLOYEE_NAME, ROW_NUMBER() OVER(PARTITION BY EMPLOYEE_NAME ORDER BY EMPLOYEE_NAME) AS DUPLICATERECORDCOUNT FROM EMPLOYEE
    )
    DELETE FROM TEMP WHERE DUPLICATERECORDCOUNT > 1
  9. SELECT D.[NAME] AS DEPARTMENT_NAME, D.DEPARTMENT_ID, (SELECT COUNT(*) FROM EMPLOYEE E WHERE E.Department_ID = D.DEPARTMENT_ID) AS EMPLOYEE_COUNT FROM DEPARTMENT D
  10. SELECT EMPLOYEE_NAME, (SALARY/12) AS “MONTHLY SALARY”, SALARY AS “ANNUAL SALARY” FROM EMPLOYEE

LIKE SHARE COMMENT

Facebook
Google+
Twitter
LinkedIn

ALSO READ

List of 200 SQL Queries for Practice Before Interview Part -1

List of 200 SQL Queries for Practice Before Interview Part -2

List of 200 SQL Queries for Practice Before Interview Part -3

List of 200 SQL Queries for Practice Before Interview Part -4

TAGS

http://techhowdy.com/wp-content/uploads/2018/08/List-of-Practice-SQL-Server-Queries-for-Interview-2018.pnghttp://techhowdy.com/wp-content/uploads/2018/08/List-of-Practice-SQL-Server-Queries-for-Interview-2018-150x150.pngDemonDatabase Programmingbest practices in sql server query writing,best practices sql server queries,complex sql server queries for practice,complex sql server queries for practice with answers,practice of sql server queries,practice queries in sql server,practice sql server queries 2018,practice sql server queries online,practise sql server queries,sample sql server queries for practice before interview,sql queries practice sql server,sql server 2014 practice queries,sql server 2014 queries for practice,sql server 2017 queries for practice,sql server 2017 queries for practice pdf,sql server 2017queries practice,sql server practice queries pdf,sql server queries best practices,sql server queries examples for practice,sql server queries for practice,sql server queries for practice pdf,sql server queries for practice with answers,sql server queries for practice with answers pdf,sql server queries practice exercises,sql server queries to practiceWe have put together list of practice SQL Queries for Interview. Before you start practicing these SQL Queries, use the provided code to Create required Database tables. Fell free to modify these queries to find alternate methods to get the same answers. Use our forum to post questions related...Latest technology news