How to use SQL pagination using LIMIT and OFFSET

 How to extract just a portion of a larger result set from a SQL SELECT.

LIMIT n is an alternative syntax to the FETCH FIRST n ROWS ONLY.

The OFFSET clause specifies the number of rows of the result table to skip before any rows are retrieved,
and must be used with the LIMIT clause.

The OFFSET clause instructs the server where to start returning rows within the query result.
For example, if a query returns 100 rows, specifying OFFSET 10 instructs the server to skip
the first 10 rows of the query results:

Why do we need SQL functions when stored procedures can do all what SQL function does?

A function can be used inline in SQL statements while stored procedures cannot.

you cannot call a stored procedure in a SELECT statement. I guess this could be a reason for using functions.

Write a query to Update the Male <->Female in SQL server

 update tableA set Gender = (case gender ='Male' then 'Female' else 'Male' end)

Create Stored Procedure with output parameter and how to call stored procedure with output.

 Create proc with input parameter:

CREATE PROCEDURE Usp_GetEmployee

(

   @EmpId int

)

as

begin

select * from Emp where EmpId=@EmpId

end

 Create proc with Output parameter:

CREATE PROC Usp_OutPutEmp

(

  @EmpId int ,

  @empName varchar output

)

as 

begin

SELECT @empName= EmpName  FROM Emp WHERE EmpId=@EmpId

end

---------------------Call Proc with output Parameter------------

DECLARE @EmpName varchar(30)

exec Usp_OutPutEmp 1,@EmpName output

print @EmpName

How to remove dublicate record in a table

 DELETE P1 FROM ProductId AS P1

INNER JOIN PRODUCTID AS P2 ON

P1.ProductId>P2.ProductId AND P1.PName=P2.PName

Can a TABLE have primary key without Clustered Index? - Yes | Can a TABLE have Clustered Index without primary key? - Yes

 A primary key is a unique index that is clustered by default. By default means that when you create a primary key, if the table is not clustered yet, the primary key will be created as a clustered unique index. Unless you explicitly specify the nonclustered option.

A clustered index stores all columns at the leaf level. That means a clustered index contains all data in the table. A table without a clustered index is called a heap.

SQL Server Function

 STUFF :

The STUFF() function deletes a part of a string and then inserts another part into the string, starting at a specified position.

Note: Also look at the REPLACE() function.

Syntax:

STUFF(string, start, length, new_string)

Example: 

SELECT STUFF('SQL Tutorial!', 13, 1, ' is fun!');

CharIndex()

The CHARINDEX() function searches for a substring in a string, and returns the position.

If the substring is not found, this function returns 0.

Note: This function performs a case-insensitive search.

Syntax:

CHARINDEX(substring, string, start).

Example:

SELECT CHARINDEX('OM', 'Customer') AS MatchPosition

What is Difference between Cross join and Full Join in SQL?

 Cross Join:-

CROSS JOIN is also known as the Cartesian product / Cartesian join.

Cross join defines where the number of rows in the first table multiplied by a number of rows in the second table.

Cross Join applies to all columns.

Syntax:

Select * from TableName1

cross join TableName2 ;

Query:

Select * from Table_1

cross join Table_2 ;

Full outer join:

FULL OUTER JOIN combines the results of both left and right outer joins and returns all matched or unmatched rows from the tables on both sides of the join clause.

Syntax:

SELECT *FROM table_1

FULL OUTER JOIN table_2

ON table_1.ColumnName=table_2.ColumnName;

https://medium.com/@patilpoojaif/difference-between-cross-join-vs-full-outer-join-in-sql-server-a882dfc7a3d3

What is SQL Jobs?

 SQL Job?

A Job in SQL Server is a container that allows packaging one or more steps in process that need to be scheduled and executed. We can say, it a series of query actions that SQL Server performs.

https://www.c-sharpcorner.com/UploadFile/ff0d0f/jobs-in-sql-server/

what is SQL Profiler

What is SQL Server Profiler:

The SQL Server Profiler is a performance tool which included with any version of SQL Server. It generally shows how SQL Server accesses the database internally. This tool is used by developers as well as a performance engineering team to analyze which SQL statements take more time to execute. It shows information like, how many reads and writes happened in a transaction, the duration it takes to complete the transaction and information such as that is displayed to show complete information of events occurring in the database end.

Why SQL Server Profiler:

Consider a scenario like, you have made a request to an aspx page, it took 20 seconds to load the page. So it's a performance issue here. Here we need to find out what takes the most amount of time, either in the front end or in the back end. In a single request of an aspx page, there may be n number of database hits based on the functionality; here we need to find out what are all the SQL statements executed in this page request. There the SQL Server Profiler helps you to trace the information on the page request.

How to Use SQL Server Profiler:

Enter the profiler keyword in run command; it will launch the profiler window to connect to the appropriate database. Once the database is connected, the trace will be started; now click the page where you want to trace the information. Stop the trace once the page request is completed; now you can see all the transactions that are performed in that single page request.

the trace information will be displayed in the screen like below.

EventClass - Event Name occurred

TextData - Stored Procedure name or the SQL Statement that is executed

Application Name - Which application invoked this SQL statement

Like that various information will be tracked using the profiler.

Different Scenarios for Using SQL Server Profiler

1. To find the worst performing queries or stored procedures
2. To monitor Transact- SQL activity per user
3. To view the connections involved in a Deadlock

Step By Step to Use Profiler

1.Launch Profiler Tool from Run Command

2.Connect to the appropriate Database

3.Configure the Properties

4.Select the Events that you need

5.

 https://www.c-sharpcorner.com/UploadFile/babu_2082/using-sql-server-profiler/

How to transfer data from one database table to other database table in sql

 INSERT INTO DestinationDB..MyDestinationTable 

SELECT * FROM SourceDB..MySourceTable

What is difference between Having Clause and Where Clause in SQL?

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

 

Example:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

https://www.javatpoint.com/where-vs-having

Comparison Basis	WHERE Clause	HAVING Clause
Definition	It is used to perform filtration on individual rows.	It is used to perform  filtration on groups.
Basic	It is implemented in row operations.	It is implemented in column operations.
Data fetching	The WHERE clause fetches the specific data from particular rows based on the specified condition	The HAVING clause first fetches the complete data. It then separates them according to the given condition.
Aggregate Functions	The WHERE clause does not allow to work with aggregate functions.	The HAVING clause can work with aggregate functions.
Act as	The WHERE clause acts as a pre-filter.	The HAVING clause acts as a post-filter.
Used with	We can use the WHERE clause with the SELECT, UPDATE, and DELETE statements.	The HAVING clause can only use with the SELECT statement.
GROUP BY	The GROUP BY clause comes after the WHERE clause.	The GROUP BY clause comes before the HAVING clause.

What is Group By in SQL

 The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

Example:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

Format Query Results as JSON in SQL

 When you use the FOR JSON clause, you can specify the structure of the JSON output explicitly, or let the structure of the SELECT statement determine the output.

• To maintain full control over the format of the JSON output, use FOR JSON PATH. You can create wrapper objects and nest complex properties.

• To format the JSON output automatically based on the structure of the SELECT statement, use FOR JSON AUTO.

Example: 

SELECT name, surname

FROM emp

FOR JSON AUTO;

Result:

[{

    "name": "John"

}, {

    "name": "Jane",

    "surname": "Doe"

}]

How to Work SQL Indexes? How index actually sort the data.

 In this Blog we will discuss how index actually work and help improve the performance of the database.

 Clustered Index Structure:

EmployeeId is the primary key, so by default a clusterd index on the EmployeeId column is created. This means employee data is sorted by EmployeeId column and physically stored in a series of data pages in a tree like structure that looks like the following.

• The nodes at the bottom of the tree are called data pages or leaf nodes and contain the actual data rows, in our case employee rows.
• These employee rows are sorted by EmployeeId column, because EmployeeId is the primary key and by default a clusterd index on this column is created.

• For our example, let's say in Employees table we have 1200 rows and let's assume in each data page we have 200 rows.
• So, in the first data page we have 1 to 200 rows, in the second 201 to 400, in the third 401 to 600, so on and so forth.
• The node at the top of the tree is called Root Node.
• The nodes between the root node and the leaf nodes are called intermediate levels.
• The root and and the intermediate level nodes contain index rows.
• Each index row contains a key value (in our case Employee Id) and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf node. 
• So this tree like structure has a series of pointers that helps the query engine find data quickly.

How SQL Server finds a row by ID:

For example, let's say we want to find Employee row with EmployeeId = 1120

So the database engine starts at the root node and it picks the index node on the right, because the database engine knows it is this node that contains employee IDs from 801 to 1200.

From there, it picks the leaf node that is present on the extreme right, because employee data rows from 1001 to 1200 are present in this leaf node.

The data rows in the leaf node are sorted by Employee ID, so it's easy for the database engine to find the employee row with Id = 1120.

Notice in just 3 operations, SQL Server is able to find the data we are looking for. It's making use of the clustered index we have on the table. Let's look at this in action.

Non-Clustered Index in SQL Server:

This is when we create a non-clustered index on the Name column.

CREATE NONCLUSTERED INDEX IX_Employees_Name

ON [dbo].[Employees] ([Name])

In an non-clusterd index we do not have table data. We have key values and row locators.

We created a non-clustered index on the Name column, so the key values, in this case Employee names are sorted and stored in alphabetical order.

The row locators at the bottom of the tree contain Employee Names and cluster key of the row. In our example, Employee Id is the cluster key.

SQL Query interview Question.

 

1. Query to find the highest salary.

SELECT MAX(SALARY) FROM EMPLOYEE ORDER BY SALARY DESC

----------------------------------------------------------------------------------------------------

Nth number of highest salary using DENSE_RANK function

SELECT SALARY,

           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS SALARY_RANK

FROM EMPLOYEE

TO FIND NTH HIGHEST SALARY USING CTE:

select * from(
select ename, sal, dense_rank() 
over(order by sal desc)r from Employee) 
where r=&n;

---------------------------------------------------

How to Get the Highest Salary of Each Department?

Please use the SQL Query below in order to get the highest salary of department:

SELECT DepartmentID, MAX(Salary) 
FROM Employee  
GROUP BY DepartmentID


Now for Printing the name, you must join the Employee table with Department table using key DepartmentID,


SELECT DepartmentName, MAX(Salary) 
FROM Employee e RIGHT JOIN Department d ON e.DepartmentId = d.DepartmentID 
GROUP BY DepartmentName


------------------------------------------------------------------------------

 Write a query to get the current date.
SELECT GETDATE();

-----------------------------------------------------

Write a query to retrieve the first four characters of  EmpLname from the EmployeeInfo table.

SELECT SUBSTRING(EmpLname, 1, 4) FROM EmployeeInfo;

--------------------------------------------------------------

Write a query to find the names of employees that begin with ‘S’

SELECT * FROM EmployeeInfo WHERE EmpFname LIKE 'S%';

------------------------------------------------------
Write a query to fetch the department-wise count of employees sorted by department’s count in ascending order.

SELECT Department, count(EmpID) AS EmpDeptCount 
FROM EmployeeInfo GROUP BY Department 
ORDER BY EmpDeptCount ASC;

-----------------------------------------------------

Write a query to retrieve duplicate records from a table.

SELECT EmpID, EmpFname, Department COUNT(*) 
FROM EmployeeInfo GROUP BY EmpID, EmpFname, Department 
HAVING COUNT(*) > 1;

--------------------------------------------------------------


Write a query to fetch 50% records from the EmployeeInfo table.

SELECT * 
FROM EmployeeInfo WHERE
EmpID <= (SELECT COUNT(EmpID)/2 from EmployeeInfo);



------------------------------------------------------

 Write an SQL query to remove duplicates from a table without using a temporary table.

DELETE E1 FROM EmployeeDetails E1
INNER JOIN EmployeeDetails E2 
WHERE E1.EmpId > E2.EmpId 
AND E1.FullName = E2.FullName 
AND E1.ManagerId = E2.ManagerId
AND E1.DateOfJoining = E2.DateOfJoining
AND E1.City = E2.City;


-----------------------------------------------------------------

Ques.35. Write an SQL query to fetch only even rows from the table.
Ans. In case we have an auto-increment field e.g. EmpId then we can simply use the below query-
SELECT * FROM EmployeeDetails 
WHERE MOD (EmpId, 2) = 0;

----------------------------------------------------------------------