Group by in MS sql server

Group By In SQL Server we have got many aggregate functions. Examples

1. Count()

2. Sum() 3. avg() 4. Min() 5. Max()

The GROUP BY statement groups rows that have the same values into summary rows,. It is often used in conjunction with one or more aggregate functions.

I want an MS SQL query, which gives total salaries paid by City. show below

Query for display total salaries by city: We are getting apply SUM() aggregate function on Salary column, and grouping by city column.

Select City, SUM(Salary) as TotalSalary from tblEmployee Group by City

Note: If you leave, the group by clause and try to execute the query, then you get an error - Column 'tblEmployee. City' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Read More

Joins in SQL server

Joins in SQL servers are used to query for retrieve data from 2 or more related tables. A Join clause is used to combine rows from one , two or more tables, based on a related column.

In SQL, a join operation combines rows from two or more tables based on a related column between them. The result of a join is a new table that contains only the rows where the join condition is met. There are several types of joins in SQL, including:

There are three types of JOINS.

1. CROSS JOIN

2. INNER JOIN 

3. OUTER JOIN 

Outer Joins are  3 types

1. Left Join or Left Outer Join

2. Right Join or Right Outer Join

3. Full Join or Full Outer Join

• INNER JOIN: Returns only the rows where there is a match in both tables.

• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values will be returned for the right table's columns.

• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and the matching rows from the left table. If there is no match, NULL values will be returned for the left table's columns.

• FULL OUTER JOIN: Returns all rows from both tables, and for the non-matching rows, NULL values will be returned for the columns of the table that does not have a matching row.

The join condition is specified in the ON clause of the join statement, and it determines which rows from the two tables are combined.

Read More

Advanced or intelligent joins in sql server,

Considers Employees (tblEmployee) and Departments (tblDepartment) tables

Employee Table (tblEmployee)


Departments Table (tblDepartment)


How to retrieve only the non-matching rows from the left table. The output should be as  below:


Query:
SELECT       Name, Gender, Salary, DepartmentName
FROM           tblEmployee E
LEFT JOIN   tblDepartment D
ON                 E.DepartmentId = D.Id
WHERE        D.Id IS NULL



How to retrieve only non-matching rows from the right table


Query:
SELECT         Name, Gender, Salary, DepartmentName
FROM             tblEmployee E
RIGHT JOIN    tblDepartment D
ON                   E.DepartmentId = D.Id
WHERE          E.DepartmentId IS NULL



How to retrieve only non-matching rows from both the left and right table. Matching rows should be eliminated.


Query:
SELECT         Name, Gender, Salary, DepartmentName
FROM              tblEmployee E
FULL JOIN      tblDepartment D
ON                   E.DepartmentId = D.Id
WHERE          E.DepartmentId IS NULL
OR                   D.Id IS NULL

Read More

Self join in sql server

Before post, we have seen joining 2 different tables - tblEmployees and tblDepartments. Have you ever thought of a need to join a table by itself? Consider tblEmployees table shown below.

Write a query that gives the following result.



Self Join Query:
A MANAGER is also an EMPLOYEE. Both the EMPLOYEE and MANAGER rows are present in the same table. Here we are joining tblEmployee with itself using different alias names, E for Employee and M for Manager. We are using LEFT JOIN, to get the rows with ManagerId NULL. You can see in the output TODD's record is also retrieved, but the MANAGER is NULL. If you replace LEFT JOIN with INNER JOIN, you will not get TODD's record.
Select E.Name as Employee, M.Name as Manager
from tblEmployee E
Left Join tblEmployee M
On E.ManagerId = M.EmployeeId


In short, joining a table with itself is called SELF JOIN. SELF JOIN is not a different type of JOIN. It can be classified under any type of JOIN - INNER, OUTER or CROSS Joins. The above query is, LEFT OUTER SELF Join.

Inner Self Join tblEmployee table:
Select E.Name as Employee, M.Name as Manager
from tblEmployee E
Inner Join tblEmployee M
On E.ManagerId = M.EmployeeId

Cross Self Join tblEmployee table:
Select E.Name as Employee, M.Name as Manager
from tblEmployee
Cross Join tblEmployee

Read More

Different ways to replace NULL in sql server

 

Consider the Employees table below. 

Next POst, we have learned to write a LEFT OUTER SELF JOIN query, which produced the following output.



In the output, the MANAGER column, for Todd's rows is NULL. I want to replace the NULL value, with 'No Manager'

Replacing NULL value using ISNULL() function: We are passing 2 parameters to IsNull() function. If M.Name returns NULL, then the 'No Manager' string is used as the replacement value.
SELECT E.Name as Employee, ISNULL(M.Name,'No Manager') as Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID

Replacing NULL value using CASE Statement:
SELECT E.Name as Employee, CASE WHEN M.Name IS NULL THEN 'No Manager' 
ELSE M.Name END as Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID

Replacing NULL value using COALESCE() function: COALESCE() function, returns the first NON NULL value.
SELECT E.Name as Employee, COALESCE(M.Name, 'No Manager') as Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID

We will discuss COALESCE() function in detail, in the next postEmail ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest

Read More

Stored procedures in MS Sql server

Stored Procedures:- A stored Procedures is a group of a T-SQL statement. If you have a situation,  where you write the same query over and over again, You can save that specific query as a Stored Procedure and call it just by its name.

Syntax:- Create procedure procedures name 

As begin

SQL statement 

End

Execute a Stored Procedures:- 

exec procedure name

Stored Procedures Example:- 

The following SQL statement creates a stored procedure named "SelectAllEmployee" that selects all records from the "tblemployee" table

Create proc SelectAllEmployee 

as

Begin

Select * from tblemployee

End

Emp_Name Emp_ID Address

Lalita     2         Noida

Ankit     1         Noida

Ankita     3        Delhi

Anku     4         US

XYZ     5         UK

Stored Procedure With Multiple Parameters:-

Multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below.

The following SQL statement creates a stored procedure that selects Tblemployee from a particular address with a particular PostalCode from the "tblemployee" table:

Example

CREATE PROC SelectAllEmployee @address nvarchar(30), @Emp_Id nvarchar(10)

AS

SELECT * FROM tblemployeeWHERE address = @address AND Emp_Id = @Emp_Id 

GO;

Execute the stored procedure above as follows:

Example

EXEC SelectAllEmployee @address = 'London', @Emp_Id  = '01';

To view the stored procedure in text:

sp_helptext stored_proc name

To change the stored procedure use,

alter procedure proc_name 

as

begin

SQL statement 

end

Drop Procedure 

Drop procedure proc_name

To Encrypt the text of the  Store Procedure 

CREATE PROC SelectAllEmployee @address nvarchar(30), @Emp_Id nvarchar(10)

with encryption 

AS

begin

SELECT * FROM tblemployeeWHERE address = @address AND Emp_Id = @Emp_Id 

GO;

Read More

Stored procedures with output parameters

 

Creating and execute Stored Procedure with output parameters

create procedure getemployeecoutbyaddress

@address varchar(20),

@Empcount int output

as begin

select @Empcount=count(Emp_ID) from tblemployee where Address=@address

end

To Execute the stored procedure with output parameters

declare @totalemployee int

exec getemployeecoutbyaddress 'noida',@totalemployee output

print @totalemployee

if you don't specified the output keyword, when execute the stored procedure @totalemployee value will be null

Sp_help procedure name:- View the information

 About the stored procedure like parameters name, their data type etc. Sp_help can be use with database object like table view etc. Alternative you can also press Art+F1 

Read More