Group by in MS sql server

Group By In SQL Server we have got many aggregate functions. Examples1. 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 belowQuery for display total salaries by...

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...

Read More

Advanced or intelligent joins in sql server,

Considers Employees (tblEmployee) and Departments (tblDepartment) tablesEmployee 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, DepartmentNameFROM           tblEmployee...

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...

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...

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 beginSQL statement EndExecute a Stored Procedures:- exec procedure nameStored...

Read More

Stored procedures with output parameters

 Creating and execute Stored Procedure with output parameterscreate procedure getemployeecoutbyaddress@address varchar(20),@Empcount int outputas beginselect @Empcount=count(Emp_ID) from tblemployee where Address=@addressendTo Execute the stored procedure with output parametersdeclare @totalemployee int exec getemployeecoutbyaddress 'noida',@totalemployee outputprint...

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 valuesSelect * from tblemployeeEmp_Name Emp_ID AddressLalita     2         NoidaAnkit     1         NoidaAnkita   ...

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...

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 ...

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 belowCombining the rows of tblIndiaCustomers and tblUKCustomers using UNION ALLSelect Id, Name, Email from tblIndiaCustomersUNION ALLSelect Id, Name, Email from tblUKCustomersQuery Results...

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: ABCRIGHT(Character_Expression,...

Read More