How to join more than two tables in in sql server

Here we will be joining 3 tables , the concept behind this is the relation between any tables joining should have a column related to it from another table.

SQL Script to create the required tables

Create Table Departments

(

     DepartmentID int primary key,

     DepartmentName nvarchar(50)

)

GO

Create Table Genders

(

     GenderID int primary key,

     Gender nvarchar(50)

)

GO

Create Table Employees

(

     EmployeeID int primary key,

     EmployeeName nvarchar(50),

     DepartmentID int foreign key references Departments(DepartmentID),

     GenderID int foreign key references Genders(GenderID)

)

GO

Insert into Departments values (1, 'IT')

Insert into Departments values (2, 'HR')

Insert into Departments values (3, 'Payroll')

GO

Insert into Genders values (1, 'Male')

Insert into Genders values (2, 'Female')

GO

Insert into Employees values (1, 'Mark', 1, 1)

Insert into Employees values (2, 'John', 1, 1)

Insert into Employees values (3, 'Mike', 2, 1)

Insert into Employees values (4, 'Mary', 2, 2)

Insert into Employees values (5, 'Stacy', 3, 2)

Insert into Employees values (6, 'Valarie', 3, 2)

GO

Write a query to join 3 the tables and retrieve EmployeeName, DepartmentName and Gender. 

SELECT DepartmentName, Gender, COUNT(*) as TotalEmployees

FROM Employees

JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID

JOIN Genders ON Employees.GenderID = Genders.GenderID

GROUP BY DepartmentName, Gender

ORDER BY DepartmentName, Gender

How to Delete duplicate rows in sql

SQL Script to create Employees table

Create table Employees

(

     ID int,

     FirstName nvarchar(50),

     LastName nvarchar(50),

     Gender nvarchar(50),

     Salary int

)

GO

Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000)

Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000)

Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000)

Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000)

Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000)

Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000)

Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000)

Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000)

The delete query should delete all duplicate rows except one. 

Here is the SQL query that does the job. PARTITION BY divides the query result set into partitions.

WITH EmployeesCTE AS

(

   SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) ASRowNumber

   FROM Employees

)

DELETE FROM EmployeesCTE WHERE RowNumber > 1

What to choose for performance - SubQueries or Joins in sql

In General Joins work faster than subqueries .

According to MSDN, in sql server, in most cases, there is usually no performance difference between queries that uses sub-queries and equivalent queries using joins. For example, on my machine I have
400,000 records in tblProducts table
600,000 records in tblProductSales tables 

The following query, returns, the list of products that we have sold atleast once.This query is formed using sub-queries. When I execute this query I get 306,199 rows in 6 seconds
Select Id, Name, Description
from tblProducts
where ID IN
(
Select ProductId from tblProductSales
) 

At this stage please clean the query and execution plan cache using the following T-SQL command. 
CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; -- Clears query cache
Go
DBCC FREEPROCCACHE; -- Clears execution plan cache
GO

Now, run the query that is formed using joins. Notice that I get the exact same 306,199 rows in 6 seconds. 

Select distinct tblProducts.Id, Name, Description
from tblProducts
inner join tblProductSales
on tblProducts.Id = tblProductSales.ProductId

Please Note: I have used automated sql script to insert huge amounts of this random data. Please watch Part 61 of SQL Server tutorial, in which we have discussed about this automated script.

According to MSDN, in some cases where existence must be checked, a join produces better performance. Otherwise, the nested query must be processed for each result of the outer query. In such cases, a join approach would yield better results.

The following query returns the products that we have not sold at least once. This query is formed using sub-queries. When I execute this query I get 93,801 rows in 3 seconds

Select Id, Name, [Description]
from tblProducts
where Not Exists(Select * from tblProductSales where ProductId = tblProducts.Id)

When I execute the below equivalent query, that uses joins, I get the exact same 93,801 rows in 3 seconds.

Select tblProducts.Id, Name, [Description]
from tblProducts
left join tblProductSales
on tblProducts.Id = tblProductSales.ProductId
where tblProductSales.ProductId IS NULL 

In general joins work faster than sub-queries, but in reality it all depends on the execution plan that is generated by SQL Server. It does not matter how we have written the query, SQL Server will always transform it on an execution plan. If sql server generates the same plan from both queries, we will get the same result.

I would say, rather than going by theory, turn on client statistics and execution plan to see the performance of each option, and then make a decision. 

Creating a large table with random data in sql

create table demotable (productid int,productname varchar(50))    

declare @id int
select @id =1
while (@id<=300)
begin
insert into demotable values (@id,'product'+cast(@id as nvarchar(50)))
select @id=@id+1
end

select * from demotable

drop table demotable

Different ways to replace NULL in sql server

Consider the Employees table below. 


In Part 14, we have learnt writing a LEFT OUTER SELF JOIN query, which produced the following output. 



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

Replacing NULL value using ISNULL() function: We are passing 2 parameters to IsNULL() function. If M.Name returns NULL, then '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

validate a textbox to enter mobile numbers in xxx-xxx-xxxx format using c#

private void textBox1_KeyDown(object sender, KeyEventArgs e)
        {
           string sVal = textBox1.Text;
         
                if (textBox1.TextLength <= 8)
                {
                    if (!string.IsNullOrEmpty(sVal) && e.KeyCode != Keys.Back)
                    {
                        sVal = sVal.Replace("-", "");
                        string newst = Regex.Replace(sVal, ".{3}", "$0-");
                        textBox1.Text = newst;
                        textBox1.SelectionStart = textBox1.Text.Length;
                    }
                }
                 if(textBox1.TextLength >= 12)
                {
                    e.SuppressKeyPress = true;
                }
         
        }