SQL Server Interview Questions for 2-5 Years Experienced

If you're looking for SQL Server Interview Questions for Experienced or Freshers, you are in the right place. There are many opportunities from reputable companies around the world. According to research, the average salary for SQL Server ranges from approximately $69,682 per annum. So, you still have the opportunity to advance your career in SQL Server.

MindMajix offers Advanced SQL Server Interview Questions for 2-3 Years of Experience to help you crack your interview & acquire a dream career as an SQL Server Developer. 

We have categorized SQL Server Interview Questions - 2024 (Updated) into 4 levels they are:

• For 2 - 3 Years Experienced
• For 2 - 5 Years Experienced
• SQL Server Joins
• SQL Server Query

If you want to enrich your career and become a professional in SQL, enroll in "SQL Server Training." This course will help you achieve excellence in this domain.

SQL Server Interview Questions For 2-3 Years Experienced

Hands-on Query-Based Questions

Schema Design and Performance Tuning Problems

1. Write the DDL to create an Employees table with a primary key, a foreign key to the Departments table, a NOT NULL constraint, a CHECK constraint on Salary, and a DEFAULT value.

CREATE TABLE Employees (
 EmpID INT IDENTITY(1,1) PRIMARY KEY,
 Name VARCHAR(100) NOT NULL,
 DeptID INT FOREIGN KEY REFERENCES Departments(DeptID),
 Salary DECIMAL(10,2) CHECK (Salary > 0),
 IsActive BIT DEFAULT 1,
 HireDate DATE DEFAULT GETDATE()
);

2. Write a query to find and report all Foreign Key constraints in the current database, along with their parent and referenced tables.

SELECT
 fk.name AS ForeignKeyName,
 tp.name AS ParentTable,
 tr.name AS ReferencedTable
FROM sys.foreign_keys fk
JOIN sys.tables tp ON fk.parent_object_id = tp.object_id
JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id;

3. Write a query to find all missing index suggestions from SQL Server's DMVs.

SELECT
 mid.statement AS TableName,
 migs.avg_user_impact,
 mid.equality_columns,
 mid.inequality_columns,
 mid.included_columns
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle =
migs.group_handle
ORDER BY migs.avg_user_impact DESC;

4. Write a stored procedure that safely transfers funds between two accounts using a transaction, with rollback on error.

CREATE PROCEDURE TransferFunds
 @FromID INT, @ToID INT, @Amount DECIMAL(10,2)
AS
BEGIN
 BEGIN TRY
 BEGIN TRANSACTION;
 UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @FromID;
 UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ToID;
 IF (SELECT Balance FROM Accounts WHERE AccountID = @FromID) < 0
 THROW 50001, 'Insufficient funds', 1;
 COMMIT TRANSACTION;
 END TRY
 BEGIN CATCH
 ROLLBACK TRANSACTION;
 THROW;
 END CATCH
END;

5. Write a query to pivot monthly sales totals into columns (Jan, Feb, Mar...) per employee.

SELECT EmpID, [1] AS Jan, [2] AS Feb, [3] AS Mar
FROM (
 SELECT EmpID, MONTH(OrderDate) AS OrderMonth, Amount
 FROM Orders
 WHERE MONTH(OrderDate) IN (1,2,3)
) src
PIVOT (
 SUM(Amount) FOR OrderMonth IN ([1],[2],[3])
) AS pvt;

6. Write a query to find gaps in a sequence of OrderIDs (missing IDs).

WITH Seq AS (
 SELECT OrderID, LEAD(OrderID) OVER (ORDER BY OrderID) AS NextID
 FROM Orders
)
SELECT OrderID + 1 AS GapStart, NextID - 1 AS GapEnd
FROM Seq
WHERE NextID - OrderID > 1;

7. Write a query that returns 'Yes'/'No' for whether each employee earns more than their manager.

SELECT e.Name AS Employee, m.Name AS Manager,
 CASE WHEN e.Salary > m.Salary THEN 'Yes' ELSE 'No' END AS
EarnsMoreThanManager
FROM Employees e
JOIN Employees m ON e.ManagerID = m.EmpID;

8. Write a query that flags employees whose salaries are outliers (more than 2 standard deviations from the department mean).

SELECT Name, DeptID, Salary
FROM (
 SELECT Name, DeptID, Salary,
 AVG(Salary) OVER (PARTITION BY DeptID) AS DeptAvg,
 STDEV(Salary) OVER (PARTITION BY DeptID) AS DeptStdDev
 FROM Employees
) t
WHERE ABS(Salary - DeptAvg) > 2 * DeptStdDev;

Analytics and Windows Function Problems

9. Calculate a 3-month moving average of sales revenue.

SELECT OrderMonth, Revenue,
 AVG(Revenue) OVER (ORDER BY OrderMonth ROWS BETWEEN 2 PRECEDING AND CURRENT
ROW) AS MovingAvg3
FROM (
 SELECT FORMAT(OrderDate, 'yyyy-MM') AS OrderMonth, SUM(Amount) AS Revenue
 FROM Orders GROUP BY FORMAT(OrderDate, 'yyyy-MM')
) t;

10. Find the difference between each employee's salary and the highest salary in their department.

SELECT Name, DeptID, Salary,
 MAX(Salary) OVER (PARTITION BY DeptID) - Salary AS DiffFromTop
FROM Employees;

11. Find customers whose most recent order amount exceeds their average order amount.

WITH Ranked AS (
 SELECT CustomerID, Amount,
 ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS
rn,
 AVG(Amount) OVER (PARTITION BY CustomerID) AS AvgAmount
 FROM Orders
)
SELECT CustomerID, Amount AS LastOrderAmount, AvgAmount
FROM Ranked
WHERE rn = 1 AND Amount > AvgAmount;

12. Find the employee(s) with the second-highest salary in EACH department (handle ties properly).

SELECT Name, DeptID, Salary FROM (
 SELECT Name, DeptID, Salary,
 DENSE_RANK() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS rnk
 FROM Employees
) t
WHERE rnk = 2;

Aggregation, Sub-Queries, and Joins Problems

13. Find the top 3 customers by total order amount.

SELECT TOP 3 c.Name, SUM(o.Amount) AS TotalSpent
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.Name
ORDER BY TotalSpent DESC;

14. Find the most popular product (by total quantity sold).

SELECT TOP 1 p.ProductName, SUM(od.Quantity) AS TotalSold
FROM Products p
JOIN OrderDetails od ON p.ProductID = od.ProductID
GROUP BY p.ProductName
ORDER BY TotalSold DESC;

15. Find the month with the highest total sales revenue.

SELECT TOP 1 MONTH(OrderDate) AS OrderMonth, SUM(Amount) AS Revenue
FROM Orders
GROUP BY MONTH(OrderDate)
ORDER BY Revenue DESC;

16. Find the average order value per customer, only for customers with more than 7 orders.

SELECT CustomerID, AVG(Amount) AS AvgOrderValue, COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 7;

17. Find products whose prices are above the average price for their category.

SELECT p.ProductName, p.Price, p.CategoryID
FROM Products p
WHERE p.Price > (
 SELECT AVG(p2.Price) FROM Products p2 WHERE p2.CategoryID = p.CategoryID
);

18. Find all pairs of employees who share the same manager.

SELECT e1.Name AS Emp1, e2.Name AS Emp2, e1.ManagerID
FROM Employees e1
JOIN Employees e2 ON e1.ManagerID = e2.ManagerID AND e1.EmpID < e2.EmpID
WHERE e1.ManagerID IS NOT NULL;

Foundational Problems

19. Find the Nth highest salary (parameterized) using a window function.

SELECT Salary FROM (
 SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
 FROM Employees
) t
WHERE rnk = @N;

20. Find duplicate rows in the Employees table based on Name and DeptID.

SELECT Name, DeptID, COUNT(*) AS Occurrences
FROM Employees
GROUP BY Name, DeptID
HAVING COUNT(*) > 1;

21. Find all departments that have no employees.

SELECT d.DeptName
FROM Departments d
WHERE NOT EXISTS (
 SELECT 1 FROM Employees e WHERE e.DeptID = d.DeptID
);

22. Can we write a distributed query and get some data that is located on another server and on Oracle Database?

SQL Server can be linked to any server provided it has an OLE-DB provider from Microsoft to allow a link.

For E.g. Oracle has an OLE-DB provider for Oracle that Microsoft provides to add it as a linked server to the SQL Server group.

23. If we drop a table, does it also drop related objects like constraints, indexes, columns, defaults, Views, and Stored Procedures?

YES, SQL Server drops all related objects, which exist inside a table like constraints, indexes, columns, defaults, etc. BUT dropping a table will not drop Views and Stored Procedures as they exist outside the table.

How would you determine the time zone under which a database was operating?

24. Can we add an identity column to the decimal datatype?

YES, SQL Server support this

25. What is the difference between LEFT JOIN with WHERE clause & LEFT JOIN with nowhere clause?

OUTER LEFT/RIGHT JOIN with WHERE clause can act like an INNER JOIN if not used wisely or logically.

26. What are the multiple ways to execute a dynamic query?

• EXEC sp_executesql,
• EXECUTE()

27. What is the Difference between COALESCE() & ISNULL()?

ISNULL accepts only 2 parameters. The first parameter is checked for a NULL value, if it is NULL then the second parameter is returned, otherwise, it returns the first parameter.

COALESCE accepts two or more parameters. One can apply 2 or as many parameters, but it returns only the first non NULL parameter,

28. By Mistake, Duplicate records exists in a table, how can we delete the copy of a record? 

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

delete
from T
where rank > 1

29. WHAT OPERATOR PERFORMS PATTERN MATCHING?

The pattern matching operator is LIKE, and it has to be used with two attributes

1. %  means matches zero or more characters and 

2. _ ( underscore ) means matching exactly one character

30. What’s the logical difference, if any, between the following SQL expressions?

-- Statement 1 
SELECT COUNT ( * ) FROM Employees

-- Statement 2
SELECT SUM ( 1 ) FROM Employees

They’re the same unless table Employee table is empty, in which case the first yields a one-column, a one-row table containing zero, and the second yields a one-column, one-row table "containing a null."

31. Is it possible to update the Views? If yes, How, If Not, Why?

Yes, We can modify views but a DML statement on a join view can modify only one base table of the view (so even if the view is created upon a join of many tables, only one table, the key preserved table can be modified through the view).

32. Could you please name different kinds of Joins available in SQL Server?

• OUTER JOIN – LEFT, RIGHT, CROSS, FULL ;
• INNER JOIN

33. How important do you consider cursors or while loops for a transactional database?

would like to avoid cursor in the OLTP database as much as possible, Cursors are mainly only used for maintenance or warehouse operations.

34. What is a correlated subquery?

When a subquery is tied to the outer query. Mostly used in self joins.

35. What is faster, a correlated subquery or an inner join?

Correlated subquery.

36. You are supposed to work on SQL optimization and given a choice which one runs faster, a correlated subquery or an exists?

Exists

37. Can we call. DLL from the SQL server?

YES, We can call. Dll from SQL Server. 

38. What are the pros and cons of putting a scalar function in a queries select list or in the where clause?

Should be avoided if possible as Scalar functions in these places make the query slow down dramatically.

39. What are user-defined data types and when you should go for them?

User-defined data types let you extend the base SQL Server data types by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case, you could create a user-defined data type called Flight_num_type of varchar(8) and use it across all your tables.

See sp_addtype, sp_droptype in books online.

40. What are Index, cluster index, and non-cluster index?

Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index.

Non-NonClustered Index:- A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk. The leaf nodes of a non-clustered index do not consist of the data pages. instead, the leaf node contains index rows.

41. Write down the general syntax for a SELECT statement covering all the options.

Here’s the basic syntax: (Also checkout SELECT in books online for advanced syntax)

SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by__expression]
[HAVING search_condition]
[ORDER BY order__expression [ASC | DESC] ]

42. What is a join and explain different types of joins?

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.

Types of joins:

INNER JOINs,

OUTER JOINs,

CROSS JOINs

OUTER JOINs are further classified as

LEFT OUTER JOINS,

RIGHT OUTER JOINS and

FULL OUTER JOINS.

For more information see pages from books online titled: "Join Fundamentals" and "Using Joins".

43. What is the OSQL utility?

OSQL is a command-line tool that is used to execute the query and display the result the same as a query analyzer but everything is in the command prompt.

44. What Is Cascade delete/update?

CASCADE allows deletions or updates of key values to cascade through the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed.

SQL Server Interview Questions For 2-5 Years Experienced

45. What are some of the join algorithms used when SQL Server joins tables.

1. Loop Join (indexed keys unordered)
2. Merge Join (indexed keys ordered)
3. Hash Join (non-indexed keys)

46. What is the maximum number of tables that can join in a single query?

256, check SQL Server Limits

47. What are Magic Tables in SQL Server?

The MAGIC tables are automatically created and dropped, in case you use TRIGGERS. SQL Server has two magic tables named, INSERTED and DELETED

These are maintained by the SQL server for their Internal processing. When we use update insert or delete on tables these magic tables are used. These are not physical tables but are Internal tables. Whenever we use insert statement is fired the Inserted table is populated with newly inserted Row and whenever delete statement is fired the Deleted table is populated with the deleted row. 

But in case of update statement is fired both Inserted and Deleted tables used for records the Original row before updating get stored in the Deleted table and the new row Updated gets store in Inserted table.

48. Can we disable a trigger? if yes HOW?

YES, we can disable a single trigger on the database by using  “DISABLE TRIGGER triggerName ON <>”

we also have an option to disable all the triggers by using, “DISABLE Trigger ALL ON ALL SERVER”

49. Why do you need indexing? where is Stored and what do you mean by schema object? For what purpose we are using view?

We can’t create an Index on an Index... The index is stored in the user_index table. Every object that has been created on Schema is Schema Object like Table, View etc. If we want to share the particular data to various users we have to use the virtual table for the Base table. So that is a view.

Indexing is used for faster search or to retrieve data faster from the various tables. Schema containing a set of tables, basically schema means logical separation of the database. The view is crated for faster retrieval of data. It’s a customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data.

50. What is the difference between UNION and UNION ALL?

Union will remove the duplicate rows from the result set while Union all doesn't.

51. Which system table contains information on constraints on all the tables created?

USER_CONSTRAINTS,

system table contains information on constraints on all the tables created

SQL Server Joins Interview Questions

52. What are the different Types of Join?

Below are the different types of SQL Server Joins:

1. Cross Join: A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. A common example is when a company wants to combine each product with a pricing table to analyze each product at each price.
2. Inner Join: A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.
3. Outer Join: A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer joins to specify the unmatched rows to be included:
   • Left Outer Join: In Left Outer Join all rows in the first-named table i.e. "left" table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.
   • Right Outer Join: In Right Outer Join all rows in the second-named table i.e. "right" table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.
   • Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.
4. Self Join: This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self-join can be of any type, as long as the joined tables are the same. A self-join is rather unique in that it involves a relationship with only one table. A common example is when a company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join.

53. What is Data-Warehousing? 

1. Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
2. Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
3. Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting.
4. Integrated, meaning that the database contains data from most or all of an organization’s operational applications and that this data is made consistent.

54. What is a live lock?

A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

55. How SQL Server executes a statement with nested subqueries?

When SQL Server executes a statement with nested subqueries, it always executes the innermost query first. This query passes its results to the next query and so on until it reaches the outermost query. It is the outermost query that returns a result set.

56. How do you add a column to an existing table?

ALTER TABLE Department ADD (AGE, NUMBER);

57. Can one drop a column from a table?

YES, to delete a column in a table, use  ALTER TABLE table_name DROP COLUMN column_name

58. Which operator do you use to return all of the rows from one query except rows are returned in a second query?

You use the EXCEPT operator to return all rows from one query except where duplicate rows are found in a second query. The UNION operator returns all rows from both queries minus duplicates. The UNION ALL operator returns all rows from both queries including duplicates. The INTERSECT operator returns only those rows that exist in both queries. 

59. How will you create a column alias?

The AS keyword is optional when specifying a column alias.

60. In what sequence SQL statements are processed?

The clauses of the subselect are processed in the following sequence (DB2):

1. FROM clause
2. WHERE clause 
3. GROUP BY clause
4. HAVING clause 
5. SELECT clause 
6. ORDER BY clause 
7. FETCH FIRST clause

61. How can we determine what objects a user-defined function depends upon?

sp_depends system stored procedure or query the says depends on system table to return a list of objects that a user-defined function depends upon

SELECT DISTINCT so1.name, so2.name FROM sysobjects so1
INNER JOIN sysdepends sd
ON so1.id = sd.id
INNER JOIN sysobjects so2
ON so2.id = sd.depid
WHERE so1.name = '<>'

62. What is lock escalation?

A query first takes the lowest level lock possible with the smallest footprint (row-level). When too many rows are locked (requiring too much RAM) the lock is escalated to a range or page lock. If too many pages are locked, it may escalate to a table lock.

63. What are the main differences between #temp tables and @table variables and which one is preferred? 

1. SQL Server can create column statistics on #temp tables
2. Indexes can be created on #temp tables
3. @table variables are stored in memory up to a certain threshold.

64. What are Checkpoint In SQL Server?

When we did the operation on SQL SERVER that is not committed directly to the database. All operations must be logged in to Transaction Log files after that they should be done on to the main database.CheckPoint is the point that alerts SQL Server to save all the data to the main database if no checkpoint is there then log files get full we can use the Checkpoint command to commit all data in the SQL SERVER. When we stop the SQL Server it will take a long time because Checkpoint is also fired.

Read these latest SQL Interview Questions For 5+ Years Experienced that helps you grab high-paying jobs

65. Why we use the OPEN XML clause?

OPENXML parses the XML data in SQL Server in an efficient manner. Its primary ability is to insert XML data into the DB.

66. Can we store PDF files inside the SQL Server table?

YES, we can store this sort of data using a blob datatype.

67. Can we store Videos inside the SQL Server table?

YES, we can store Videos inside SQL Server by using FILESTREAM data type, which was introduced in SQL Server 2008.

68. Can we hide the definition of a stored procedure from a user?

YES, while creating stored procedure we can use WITH ENCRYPTION which will convert the original text of the CREATE PROCEDURE statement to an encrypted format.

69. What have included columns when we talk about SQL Server indexing?

Indexed with included columns were developed in SQL Server 2005 that assists in covering queries. Indexes with Included Columns are non clustered indexes that

have the following benefits:

• Columns defined in the include statement, called non-key columns, are not counted in the
  a number of columns by the Database Engine.
• Columns that previously could not be used in queries, like nvarchar(max), can be included
  as a non-key column.
• A maximum of 1023 additional columns can be used as non-key columns.

70. What is an execution plan? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query.

From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If this option is turned on it will display the query execution plan in a separate window when the query is run again.

→ Explore SQL Server Sample Resumes Download & Edit, Get Noticed by Top Employers!

SQL Server Query Interview Questions with Answers

SQL Server DATEADD() Function

71. Write a Query to display the date after 12 months?

SELECT DATEADD(mm, 2, getdate())

72. Write a Query to display the date before 15 days? 

SELECT DATEADD(dd, -15, getdate())

SQL Server DATEDIFF() Function

73. Write a Query to display employee details along with exp?

SELECT *
DATEDIFF(yy, doj, getdate()) AS ‘Exp’ FROM employee

74. Write a Query to display employee details who is working in ECE department & who his having more than 3 years of exp?

SELECT *
DATEDIFF(yy, doj, getdate()) AS ‘Exp’ 
FROM employee 
WHERE DATEDIFF(yy, doj, getdate())>3 AND dept_name=’ECE’

75. Write a Query to display employee details along with age?

SELECT *
DATEDIFF(yy, dob, getdate()) AS ‘Age’ FROM employee

76. Write a Query to display employee details whose age >18?

SELECT *
DATEDIFF(yy, dob, getdate()) AS ‘Age’ FROM employee
WHERE DATEDIFF(yy, dob, getdate())>18

SQL Server Multi-Row Functions

77. Write a Query to display the minimum salary of an employee?

SELECT MIN (salary)
FROM employee

78. Write a Query to display the maximum salary of an employee?

SELECT MAX(salary)
FROM employee

79. Write a Query to display the total salary of all employees?

SELECT SUM(salary) FROM employee

80. Write a Query to display the average salary of an employee?

SELECT AVG(salary) FROM employee

81. Write a Query to count the number of employees working in the company?

SELECT COUNT(*) FROM employee

82. Write a Query to display the minimum & maximum salary of the employee?

SELECT MIN(salary) AS ‘min sal’, MAX(salary) AS ‘max sal’ FROM employee

83. Write a Query to count the number of employees working in the ECE department?

SELECT COUNT(*) FROM employee WHERE dept_name=’ECE’

84. Write a Query to display the second max salary of an employee?

SELECT MAX(salary)
FROM employee
WHERE salary < (SELECT MAX(salary) FROM emp)

85. Write a Query to display the third max salary of an employee?

SELECT MAX(salary)
FROM employee
WHERE salary < (SELECT MAX(salary) FROM emp where salary < (SELECT MAX(salary) FROM emp))

SQL SERVER: GROUP BY Clause

86. Write a Query to display the total salary of employees based on the city?

SELECT city, SUM(salary) 
FROM employee 
GROUP BY city;

87. Write a Query to display a number of employees based on the city?

SELECT city, COUNT(emp_no) 
FROM employee
GROUP BY city;

(OR)

SELECT city, COUNT(emp_no) AS ‘no.of employees’ 
FROM employee 
GROUP BY city;

88. Write a Query to display the total salary of employees based on region?

SELECT region, SUM(salary) AS ‘total_salary’ 
FROM employee
GROUP BY region;

89. Write a Query to display the number of employees working in each region?

SELECT region, COUNT(gender) 
FROM employee
GROUP BY region;

(OR) 

SELECT region, COUNT(gender) AS ‘no.of males’
FROM employee
GROUP BY region;

90. Write a Query to display minimum salary & maximum salary based on dept_name?

SELECT dept_name, MIN(salary) AS ‘min sal’, MAX(salary) AS ‘max sal’ 
FROM employee
GROUP BY dept_name

91. Write a Query to display the total salary of employees based on dept_name?

SELECT dept_name, SUM(salary) AS ‘total_sal’ 
FROM employee
GROUP BY dept_name

92. Write a Query to display no. of males in each department?

SELECT dept_name, COUNT(gender) 
FROM employee
GROUP BY dept_name 
WHERE gender=’male’

(OR)

SELECT dept_name, COUNT(gender) AS ‘no.of males’ 
FROM employee
WHERE gender=’male’ 
GROUP BY dept_name;

Note: We cannot apply where condition in GROUP BY CLAUSE if we want to apply use having clause.

We have to use WHERE condition before GROUP BY but cannot apply where condition after GROUP BY.

SQL SERVER: Having Clause

93. Write a Query to display the total salary of employees based on whose total salary > 12000?

SELECT city, SUM(salary) AS ‘total_salary’ 
FROM employee
GROUP BY city
HAVING SUM(salary)>12000;

94. Write a Query to display the total salary of all employees based on a city whose average salary >= 23000?

SELECT city, SUM(salary) AS ‘total_salary’ 
FROM employee
GROUP BY city
HAVING AVG(salary)  >= 23000;

SQL SERVER: SUB QUERIES

95. Write a Query to display employee details whose employee numbers are 101, 102?

SELECT *
FROM employee
WHERE Emp_No in (101, 102)

(OR)

SELECT * FROM employee
WHERE Emp_No in (select emp_no from emp)

96. Write a Query to display employee details belongs to the ECE department?

SELECT Emp_No, Emp_Name, Salary
FROM employee
WHERE dept_no in (select dept_no from dept where dept_name = ‘ECE’)

SQL SERVER TOP Clause

97. Write a Query to display the first record from the table?

SELECT TOP 1 * 
FROM employee

98. Write a Query to display the top 3 records from the table?

SELECT TOP 3 *
FROM employee

99. Write a Query to display the last record from the table?

SELECT TOP 1 *
FROM employee 
ORDER BY emp_no descending

SQL SERVER: Ranking Functions

Student Details Table:

Student_No	Student_Name	Percentage	Row_ID	Rank_ID	DenseRank_ID
105	James	87	1	1	1
106	John	83	2	2	2
101	Anil	83	3	2	2
104	Vijay	83	4	2	2
108	Rakesh	76	5	5	3
102	Sunil	76	6	5	3
103	Ajay	76	7	5	3
107	Ram	75	8	8	4

100. Write a Query to display student details along with the row_no order by student name?

SELECT *, ROW_NUMBER() OVER (ORDER BYstudent_name) AS ‘Row_ID’
FROM employee

101. Write a Query to display even records from the table?

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY student_no) AS ‘ Row_ID’ FROM student)
WHERE row_id %2=0

List of Related Microsoft Certification Courses:

SSIS	Power BI
SSRS	SharePoint
SSAS	SQL Server DBA
SCCM	BizTalk Server
Team Foundation Server	BizTalk Server Administrator