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

Stored Procedures output parameters or returns values

Whenever you execute a stored procedure, it returns the integer position variable. Typically, zero indicates success, and non-zero indicates failure.

output parameters or returns values

Select * from tblemployee

Emp_Name Emp_ID Address

Lalita     2         Noida

Ankit     1         Noida

Ankita     3        Delhi

Anku     4         US

XYZ     5         UK

Read More

Advantages of stored procedures

Following the advantages of stored procedures.

1:- Better Performance –

The process calls are quick and efficient because stored procedures are compiled once and stored in executable form. The response is quick. The executable code is automatically cached, therefore reducing memory requirements.

2:- Higher Productivity –

Since the same piece of code is used repeatedly, this results in higher productivity

3:- Ease of use -

To create a stored procedure, one can use the Java Integrated Development Environment. Then, they can be deployed at any level of network architecture.

4:- Scalability-

Stored procedures increase scalability by isolating the application process on the server.

5:-Maintainability –

It is very easy to maintain a process on a server then maintain copies on different client machines, this is because the scripts are in one place.

Read More

Coalesce function in SQL server

 

Coalesce Function returns the first null value. Let's Understand with an example.

Consider the Employees Table below. Not all employees have their First, and  Last Names filled. Some of the employees have First name missing, and some of them last name.

Emp_ID	Emp_Name	Last_Name	Address
2	lalita	NULL	Noida
1	Ankit	Shukla	Noida
3	Ankita	Panday	Delhi
4	Anku	Shukla	US
5	XYZ	NULL	UK

Now, let's write a query that returns the Name of the Employee. If an employee, has all the columns filled - Emp_Name and Last Names, then we only want the Last_Name.

If the Last_Name is NULL, and if Emp_name column are filled then, we only want the Last Name. For example, Employee row with Epm_Id = 1, has the Last_Name filled, so we want to retrieve his Last_NAme "Shukla". Employee row with Id = 2, has Emp_name filled, but the Last name is missing. Here, we want to retrieve his Emp_name "Lalita". In short, The output of the query should be as shown below.

Query:-  select emp_id, coalesce(last_name,Emp_name)as Name from tblemployee

2	lalita
1	Shukla
3	Panday
4	Shukla
5	XYZ

 

Read More

Union and union all in sql server

 

UNION and UNION ALL operators in SQL Server are used to combine the result-set of two or more SELECT queries. Please consider India and UK customer tables below

Combining the rows of tblIndiaCustomers and tblUKCustomers using UNION ALL
Select Id, Name, Email from tblIndiaCustomers
UNION ALL
Select Id, Name, Email from tblUKCustomers

Query Results of UNION ALL



Combining the rows of tblIndiaCustomers and tblUKCustomers using UNION
Select Id, Name, Email from tblIndiaCustomers
UNION
Select Id, Name, Email from tblUKCustomers

Query Results of UNION



Differences between UNION and UNION ALL (Common Interview Question)
From the output, it is very clear that UNION removes duplicate rows, whereas UNION ALL does not. When use UNION, to remove the duplicate rows, SQL server has to do a distinct sort, which is time-consuming. For this reason, UNION ALL is much faster than UNION. 

Note: If you want to see the cost of DISTINCT SORT, you can turn on the estimated query execution plan using CTRL + L.

Note: For UNION and UNION ALL to work, the Number, Data types, and the order of the columns in the select statements should be the same.

If you want to sort, the results of UNION or UNION ALL, the ORDER BY clause should be used on the last SELECT statement as shown below.
Select Id, Name, Email from tblIndiaCustomers
UNION ALL
Select Id, Name, Email from tblUKCustomers
UNION ALL
Select Id, Name, Email from tblUSCustomers
Order by Name

The following query raises a syntax error
SELECT Id, Name, Email FROM tblIndiaCustomers
ORDER BY Name
UNION ALL
SELECT Id, Name, Email FROM tblUKCustomers
UNION ALL
SELECT Id, Name, Email FROM tblUSCustomers

Difference between JOIN and UNION
JOINS and UNIONS are different things. However, this question is being asked very frequently now. UNION combines the result-set of two or more select queries into a single result-set which includes all the rows from all the queries in the union, whereas JOINS, retrieves data from two or more tables based on logical relationships between the tables. In short, UNION combines rows from 2 or more tables, where JOINS combine columns from 2 or more table.

Read More

LEFT, RIGHT, CHARINDEX and SUBSTRING functions

 

In this post, we will learn about the commonly used built-in string functions in SQL server and finally, a real-time example of using string functions.

LEFT(Character_Expression, Integer_Expression) - Returns the specified number of characters from the left-hand side of the given character expression.

Example: Select LEFT('ABCDE', 3)
Output: ABC

RIGHT(Character_Expression, Integer_Expression) - Returns the specified number of characters from the right-hand side of the given character expression.

Example: Select RIGHT('ABCDE', 3)
Output: CDE

CHARINDEX('Expression_To_Find', 'Expression_To_Search', 'Start_Location') - Returns the starting position of the specified expression in a character string. Start_Location parameter is optional.

Example: In this example, we get the starting position of the '@' character in the email string 'sara@aaa.com'. 
Select CHARINDEX('@','sara@aaa.com',1)
Output: 5

SUBSTRING('Expression', 'Start', 'Length') - As the name, suggests, this function returns substring (part of the string), from the given expression. You specify the starting location using the 'start' parameter and the number of characters in the substring using the 'Length' parameter. All the 3 parameters are mandatory.

Example: Display just the domain part of the given email 'John@bbb.com'.
Select SUBSTRING('John@bbb.com',6, 7)
Output: bbb.com

In the above example, we have hardcoded the starting position and the length parameters. Instead of hardcoding we can dynamically retrieve them using CHARINDEX() and LEN() string functions as shown below.

Example:
Select SUBSTRING('John@bbb.com',(CHARINDEX('@', 'John@bbb.com') + 1), (LEN('John@bbb.com') - CHARINDEX('@','John@bbb.com')))
Output: bbb.com

Real-time example, where we can use LEN(), CHARINDEX() and SUBSTRING() functions. Let us assume we have table as shown below. 


Write a query to find out the total number of emails, by domain. The result of the query should be as shown below.


Query
Select SUBSTRING(Email, CHARINDEX('@', Email) + 1,
LEN(Email) - CHARINDEX('@', Email)) as EmailDomain,
COUNT(Email) as Total
from tblEmployee
Group By SUBSTRING(Email, CHARINDEX('@', Email) + 1,
LEN(Email) - CHARINDEX('@', Email))

Read More