Group By with Having Clause

Hi Folks,

Today we are going to understand one more important clause of SQL: “GROUP BY” clause.

Group By :  The SQL group by clause is normally used with select statement, to collect all similar data in a group. with group by clause can be applied on one or more columns.

and it also allowed to add aggregate functions on select statement.with this we can find

Having: Specifies a search condition for a group or an aggregate. without group by having is just similar as Where clause. the difference between where and having is where clause is always use before group by and Having is used after the group by clause.

Syntax:

Select Column1 , SUM(Column2)

FROM Table

Where column3 = ‘value’

Group by Column1

Having SUM(Column2) > value

So We have two tables work_place and Home _Place: (create and insert table scripts: https://flamboyencewithsql.wordpress.com/2014/03/13/joins-in-sql-server/ )

Home_Place

image

Work_Place

image

Example:

by this two tables we are going to find out the total salary of IT office, for the guys who are resides in home_place.

SELECT w.Office,
SUM(w.Salary) AS Total_Salary
FROM Home_Place H
JOIN work_Place w
ON H.Name = W.Name
WHERE w.Office = ‘IT’
GROUP BY w.Office
Having SUM(w.Salary) > 2500

So here group by clause is combine the salary for “IT” Office and who resides in Home_Place also,

order of use the clauses with select statement is :

Select

FROM

Where

GROUP BY

HAVING

ORDER BY

in next post we’ll discuss on “OREDER BY“ clause.

Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

linkedIn | Google+ | WordPress |

Advertisements

How to use Dr.XL DBA Dashboard -One stop Solution for SQL Server Database Analysis in Excel

Hi Folks,

Here we have launched a very useful tool for all SQL developer and data analysist,

Link for all the tool: drdba.codeplex.com

The main features of this tool is:

  • Use DBADashboard Sheet to DBA Analysis
  • Write your own query in Query Writing Execution SheetPerform Analysis on Analyze Data SheetGet Stored Procedure information in Procedure analysis SheetSave your own query to save time to write query alwaysExecute Saved Queries instantly in ExecuteSavedQueries sheet

Using instruction of this tool is as follows:

Open DrXLDBA

clip_image002

Click on “OK” to save DB Details.

After Open the DrXLDBA excel file. Fill the DB Details on Popup window. We can check the current login details at “Save DB Detail” sheet.

clip_image004

After fill the DB details, Read the instruction from opened “SetupDetail”sheet.

clip_image006

Execute attached “Run this script” in your logged database.

Follow the instruction for every tab (sheet) as mention below.

DBADashboard: This is the sheet where you can analyze your database or server.

clip_image008

Query Writing Execution Sheet: This is the sheet where you can write your own query to execute and fetch data from the database.

clip_image009

Analyse Data Sheet: This sheet can be used for fetch the data with or without writing the query, the first button “Write Query” is perform same work as “Query Writing Execution”, or if you don’t want to write query, just click on the buttons on this sheet you’ll get the desired table result for just few selections of dropdown list.

clip_image011

Procedure Analysis Sheet: this sheet is used for analyze the procedures by fetch the procedure information like procedure’s all parameters, parameter’s data types and length, parameter is passed as default or not, and also provide the crated date and modified date for the selected proc.

clip_image013

SavedQueries & ExecuteSavedQueries: This is the sheet where you can insert and save your query into the database, and use this query from the next sheet dropdown list.

clip_image015

 

clip_image017

 

Hope you will like this post on a very useful tool DrXLDBA,

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

Operator in SQL

Hi Folks,

Today we will go for understanding of operators in SQL server.

Operator are used to specify a condition in SQL statement’s Where clause.

normally three types of operators are available in SQL

1. Arithmetic operators

2. Comparison Operators

3. SQL Keyword Operator

Arithmetic operator are used to perform arithmetic operations:

image

Comparison Operators are used to compare the values in where clause:

image

SQL Keyword Operators are

1. LIKE

2.AND

3.IN

4.BETWEEN

5.OR

6.NOT

Here we are going to use same table Home_Place and Work_Place  which we create in previous blog.

SELECT * FROM Home_Place

image

SELECT * FROM Work_Place

image

LIKE Operator: The LIKE operator is used when we search a string for which we know only subpart of the string. Like operator is normally used with where clause to compare a value with its part. This is also known as wildcard operator.

Example 1:

SELECT * FROM Home_Place

WHERE NAME LIKE ‘A%’

image

it shows the result in which Name starts with “A”.

Example 2:

SELECT * FROM Home_Place

WHERE NAME LIKE ‘%A%’

image

this query shows the results where the column NAME contain “A”.

AND Operator: The AND operator allows to use multiple conditions in WHERE clause.

Example:

SELECT * FROM Work_Place
WHERE Salary = 1500 AND Name LIKE ‘%e%’

image

IN Operator: IN operator is allow you to match multiple value in where clause, instead of “=”  we can use “IN” operator to match multiple values.

Example:

SELECT *
FROM Home_Place
WHERE NAME
IN
(SELECT NAME  FROM Work_Place WHERE Salary >= 1500)

image

BETWEEN Operator: The BETWEEN operator is used to fetch for values within a minimum value and the maximum value.

Example:

SELECT * FROM Work_Place
WHERE Salary
BETWEEN 1500 AND 10000

image

OR Operator: The OR operator is used to fetch the result set for satisfy one or more condition from multiple added conditions in WHERE clause.

Example:

SELECT * FROM Work_Place
WHERE Salary = 1500 OR Name LIKE ‘%e%’

image

NOT Operator: The NOT operator is just fetch vice versa results of BETWEEN , IN etc.

NOT BETWEEN:

SELECT * FROM Work_Place
WHERE Salary
NOT BETWEEN 1500 AND 10000

image

NOT IN :

SELECT *
FROM Home_Place
WHERE NAME
NOT IN
(SELECT NAME  FROM Work_Place WHERE Salary >= 1500)

image

Hope you will like this post on Microsoft SQL server,

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

What is Sub Query, Function, Stored Procedure

Hi Folks,

Today we are going discuss the heart of T-SQL the Sub Query, Function and Stored Procedure.

SubQuery: Sub query is a query which is nested within a SQL statement (Insert , Update , Select , Delete statement), usually a sub query is used in where clause of a SQL statement,

Subqueries are an alternate way of joining the tables and returning data from multiple tables.

E.g. if we need all the name from Work_place table which are present in Home_Place, we can use both the queries

1. JOIN Statement

2. SubQuery

Like:

SELECT Work_Place.*
FROM Work_Place
JOIN Home_Place
ON Work_Place.Name = Home_Place.Name

image

SELECT * FROM Work_Place
WHERE Name IN (SELECT Name FROM Home_Place)

 

image

from the above queries we get the similar result set.

Function: Two types of functions present in SQL server

1. System Functions
a.Scalar Functions
b.Aggregate Function

2. User Defined Functions
a.Scalar Functions
b.In-Line table valued Functions
c.Multi-statement table valued Function

System Functions: This functions are defined by SQL server or this are in built function in SQL.

Scalar functions: Scalar function are function which accept the single value and return single value.

e.g.       Upper(), Lower(), Convert()

Aggregate function: This functions are accept multiple value and return single value as output.

e.g.      Max(), Min(), Avg()

User Defined Function: This type of functions are defined by user. three type of functions are belongs this category.

Scalar Functions: user defined Scalar function also return one value.

e.g.

CREATE FUNCTION getJobName (@Name Varchar(50))
Returns VARCHAR(50)
AS
BEGIN
RETURN (SELECT Job FROM Work_Place WHERE Name = @Name)
END

In-Line table valued Functions: This type of function return a table in output, and accept a single select statement.

e.g.

CREATE FUNCTION getJobName (@Name Varchar(50))
Returns Table
AS
RETURN (SELECT * FROM Work_Place WHERE Name = @Name)

Multi-statement table valued Function: This type of function return the explicitly created table by with manipulation into original table.

CREATE FUNCTION getJobName_MultiLine()
Returns @Work TABLE(
name Varchar(20),
Salary Int,
Office Varchar(20),
Bonus Int
)
AS
BEGIN
INSERT  @Work  SELECT Name , Salary ,Office, 0 as Bonus FROM Work_Place

— Giving the bonus amount for All the member who works in IT
Update @work Set Bonus = (Salary /10) * 2 Where Office = ‘IT’
RETURN
END

Stored Procedure: Stored procedure are nothing but batch of SQL statements.after create a stored procedure(bunch of SQL Statement), it can be useable for any user who can access the database.

e.g.

CREATE PROCEDURE Work_Place_Proc
AS
BEGIN
INSERT INTO Home_Place
VALUES(‘Meera’, ‘IT’, 1)
Update Work_Place
SET JOB = ‘Project Architect’
END

 

Hope you will like this post on Microsoft SQL Server,

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

JOINS in SQL server

Hi Folks,

Today we are going to understand the joins in SQL server with the examples of each,

SQL joins are used to combine the result of two or more table. Combine the results of tables based on a common field between the two tables. In a single statement we can combine multiple tables by using join multiple times.

Types of join:

  1. Inner join
  2. Cross join aka Cartesian join
  3. Equi join
  4. Outer join
    1. Left outer join
    2. Right outer join
    3. Full outer join

Like we have two tables

CREATE TABLE [dbo].[HomePlace](

[Name] [varchar](10) NULL,

[Job] [varchar](100) NULL,

[Family_member] [int] NULL

)

CREATE TABLE [dbo].[WorkPlace](

[Name] [varchar](10) NULL,

[Job] [varchar](100) NULL,

[Office] [varchar](10) NULL,

[Salary] [int] NULL

)

–Insert Statement for Table [dbo].[WorkPlace]

INSERT [dbo].[WorkPlace] ([Name], [Job], [Office], [Salary]) VALUES (N’Mark’, N’Sofware Engineer’, N’IT’, 3500)

INSERT [dbo].[WorkPlace] ([Name], [Job], [Office], [Salary]) VALUES (N’John’, N’Marcketing Manager’, N’Marcketing’, 2500)

INSERT [dbo].[WorkPlace] ([Name], [Job], [Office], [Salary]) VALUES (N’steve’, N’HR Manager’, N’IT’, 1500)

INSERT [dbo].[WorkPlace] ([Name], [Job], [Office], [Salary]) VALUES (N’Dan’, N’CEO’, N’Marcketing’, 24000)

INSERT [dbo].[WorkPlace] ([Name], [Job], [Office], [Salary]) VALUES (N’Andrew’, N’Trainee’, N’IT’, 1100)

–Insert Statement for Table [dbo].[HomePlace]

INSERT [dbo].[HomePlace] ([Name], [Job], [Family_member]) VALUES (N’Andrew’, N’IT’, 3)

INSERT [dbo].[HomePlace] ([Name], [Job], [Family_member]) VALUES (N’Steve’, N’IT’, 2)

INSERT [dbo].[HomePlace] ([Name], [Job], [Family_member]) VALUES (N’John’, N’Marcketing’, 4)

INSERT [dbo].[HomePlace] ([Name], [Job], [Family_member]) VALUES (N’Shawn’, N’Manufacture’, 4)

INSERT [dbo].[HomePlace] ([Name], [Job], [Family_member]) VALUES (N’Crag’, N’Marcketing’, 4)

INSERT [dbo].[HomePlace] ([Name], [Job], [Family_member]) VALUES (N’Stuart’, N’marcketing’, 3)

INSERT [dbo].[HomePlace] ([Name], [Job], [Family_member]) VALUES (N’David’, N’IT’, 1)

HomePlace

  Untitled

Work_Place

Untitled

Inner join:  This join will display the rows which are matched in both the tables. Normally we can use only join keyword which default behave is like inner join.

So here is the query for join the result of above two tables we can:

SELECT * FROM HomePlace

INNER JOIN  WorkPlace

ON HomePlace.Name = WorkPlace.Name

Untitled

Cross join aka Cartesian join: Cross join is produce the results of all possible combination from both the tables. It has no “ON” clause to match the column value, for e.g. a table has 3 rows and other table has 2 rows this join will return 6 rows. Take an example of our above tables HomePlace and WorkPlace

SELECT * FROM HomePlace

CROSS JOIN  WorkPlace

Untitled

This query wil gives us the result of 7 rows (Home_Place) * 5 rows (Work_Place) = 35  rows

Equi join: Equi join is a special type of join in which we use only equality operator. Hence, when you make a query for join using equality operator then that join query comes under Equi join.

We can follow here same example as INNER JOIN.

SELECT * FROM HomePlace

JOIN  WorkPlace

ON HomePlace.Name = WorkPlace.Name

Untitled

Left outer join: The LEFT JOIN returns all rows from the left table (First table of query), even if there are no matches in the right table (Second table of query).  Notice that in the results where there are no matching records in Second table, the row contains a null value in the second table’s column.

SELECT * FROM dbo.WorkPlace

LEFT JOIN dbo. HomePlace

ON WorkPlace.name = HomePlace.name

Untitled

Right outer join: The RIGHT JOIN is exact opposite of LEFT JOIN  it will returns all rows from the right table (Second table of query) even if there are no matches in the left table (First table of query).  Notice that in the results where there are no matching records in first table, the row contains null value in the first table’s column.

SELECT * FROM dbo.WorkPlace

RIGHT JOIN dbo. HomePlace

ON WorkPlace.name = HomePlace.name

Untitled

Full outer join: Full outer join or is just combination of LEFT JOIN and RIGHT JOIN. Means matched records from both the tables and mismatched of Left Join + mismatched of right join.

SELECT * FROM dbo.WorkPlace

FULL OUTER JOIN dbo. HomePlace

ON WorkPlace.name = HomePlace.name

Image

Hope you will like this post on Microsoft SQL Server,

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

Constraints in SQL Server

Constraints are used to avoid the insertion of unwanted data, or the constraints are rules which enforced to insert or update the data within rules. Constraints can be specified when the table is created within CREATE TABLE statement or after the table is created within ALTER TABLE statement.

In SQL Server 6 types of constraints are available:

NOT NULL, CHECK, DEFAULT, UNIQUE KEY, PRIMARY KEY, FOREIGN KEY

Not Null constraint: Null Constraint is use to make sure that a column cannot have NULL value. We      cannot drop this constraint. For removing this constraint we can only do is alter the column not null to null.

 

Create Table NotNULL(

ID INT NOT NULL,

Name INT

)

 

–Drop Not Null Constraint

ALTER TABLE NotNULL ALTER COLUMN ID int NULL

 

–Add Not NULL Constraint by altering the table

ALTER TABLE NotNULL ALTER COLUMN ID INT NOT NULL

Check constraint:  Check constraint is use to follow certain condition. Like for driving license table there is in column age value must be greater than 18 years.

Create Table CheckCons(

Vehical_Number INT ,

Owner_Name VARCHAR(90),

Owner_Age INT CHECK (Owner_Age > 18)

)

 

–Drop CHECK Constraint

ALTER TABLE CheckCons DROP CONSTRAINT CK__CheckCons__Owner__24927208

 

–Add CHECK Constraint by altering the table

ALTER TABLE CheckCons ADD CHECK (Owner_Age > 18)

Default constraint: Default constraint is use to set a default value for a column. So at insertion time doesn’t need to insert into the column which has default constraint, the column automatically get the value by this constraint.

Create Table DefaultCons(

student_id int ,

Name varchar(90),

Class varchar(20) DEFAULT ‘9th’

)

–Drop DEFAULT Constraint

ALTER TABLE DefaultCons DROP CONSTRAINT DF__DefaultCo__Class__21B6055D

–Add DEFAULT Constraint by altering the table

ALTER TABLE DefaultCons ADD DEFAULT ‘9th’ FOR Class

Unique Key constraint: Unique key constraint is use to make sure the column has no duplicate key, even Unique key constraint support “NULL” only one time, means we can insert null value in the column at once.

Queries for Unique Key:

 

Create Table uniquekey(

id int unique ,

Name varchar(90)

)

 

–Drop unique key Constraint

ALTER TABLE uniquekey DROP CONSTRAINT UQ__uniqueke__3213E83E1BFD2C07

–Add unique KEY Constraint by altering the table

ALTER TABLE uniquekey ADD UNIQUE (Id)

Primary key constraint: Primary key is just combination of NULL Constraint and Unique key Constraint. Means it ensures that the column has only unique and not null value. (Unique key constraint can have one null value but primary key don’t have null value).

Queries for primary key:

Create Table ClassRoom

(

RollNumber Int Primary key

,StudentName Varchar(60)

 

)

–Drop Primary key Constraint

ALTER TABLE ClassRoom DROP CONSTRAINT PK_RollNu_34520G75

–Add Primary KEY Constraint by altering the table

ALTER TABLE ClassRoom ADD CONSTRAINT PK_RollNumber PRIMARY KEY (ROLLNUMBER)

Foreign Key constraint:  Foreign key constraint is used create\maintain the relationship between the tables. Means a table can connect to another table by the foreign key.

Queries for foreign key:

Create Table ResultsList(

ResultID int,

RollNumber int foreign key references ClassRoom(RollNumber)

)

–Drop foreign key Constraint

ALTER TABLE ResultsList DROP CONSTRAINT FK__ResultsLi__RollN__15502E78

–Add foreign KEY Constraint by altering the table

ALTER TABLE ResultsList ADD CONSTRAINT FK_RollNumber FOREIGN KEY (RollNumber) REFERENCES ClassRoom(RollNumber)

 

Hope you will like this post on Microsoft SQL Server,

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

MERGE Statement

Many times in our database application we need to perform insert update or delete from a table according to matched or no match data with the second table. We can easily perform all this operation by joins and Subquery. But for this we have to write multiple Statement one for Matched and one for not matched, one for insert another for update  or for delete,  but from SQL server 2003 (expanded in SQL server 2008) we can perform all this operation in a single statement called Merge statement. By this we can perform all the operation in a single statement. Merge is improve the performance of database because this statement read and process data only one time instead of multiple time for insert, update  and delete statement.

Syntax for merge:

;MERGE Table1 t

USING Table2  S

ON t.id = s.id

WHEN MATCHED then

UPDATE SET t.name = s.name — Update into Target Table

WHEN NOT MATCHED BY TARGET THEN

INSERT(name,lname,gender)

VALUES(name,lname,gender)  — Insert into Target Table

WHEN NOT MATCHED BY SOURCE THEN

DELETE      — Delete from Target Table

OUTPUT $action,

INSERTED.*,

DELETED.*;

So here we are just join with id from both the tables and then depends on matched and not found (matched) for individual table. Operation can be performed.

In above query syntax if ID is matched then we are updating the name in table1 by source table (table2). And when not matched by target means the records which are not in Target table(table1) are inserted into Target Table(table1) from Source table(table2), and the record which are not in Source table but present in target table those records are deleted by this statement.

And finally the OUTPUT clause is only for display the change records from both the tables with what actions are performed with that row.

For Example:

If we want to make the changes in target table  with compare by source table just excute this full script

 

 

–Create Target Table

IF OBJECT_ID(‘dbo.First_Table’) IS NOT NULL

DROP TABLE First_Table

 

CREATE TABLE First_Table

( id int, Name Varchar(20) )

 

GO

–Create Source Table

IF OBJECT_ID(‘dbo.Second_Table’) IS NOT NULL

DROP TABLE Second_Table

 

CREATE TABLE Second_Table

( id int, Name Varchar(20) )

GO

 

— INSERT data into Target Table

Insert Into First_Table (id, Name)

Values(1,’Jack’),(2,’John’)

 

— INSERT data into Source table

Insert Into Second_Table (id, Name)

Values(1,’Harry’),(3,’Bruce’)

 

GO

 

–Select data from both the tables

SELECT ‘Before Merge’

SELECT * FROM First_Table

SELECT * FROM Second_Table

 

— Here Merge Statement to make target table very much similar to source table

 

BEGIN TRAN

;MERGE First_table T

USING Second_Table S

ON T.id = S.id

WHEN MATCHED THEN

UPDATE SET T.Name = S.name

WHEN NOT MATCHED BY TARGET THEN

INSERT(id,Name)

VALUES(s.id, s.name)

WHEN NOT MATCHED BY SOURCE THEN

DELETE

OUTPUT $ACTION,

INSERTED.*,

DELETED.*;

 

 

–Select data from both the tables

SELECT ‘After Merge’

SELECT * FROM First_Table

SELECT * FROM Second_Table

 

–Drop Tables

DROP TABLE First_Table

DROP TABLE Second_Table

 

So here we are join both the tables with id, and if the id are same then we just update the name of target table by the source table’s matched id’s name, and if the data is not in target table but present in source table then we are inserting the data into target table, and finally the data present in target table but not in source table are deleted from the target table.

Output cluase shows the change made by this statement with “$action” sub cluase to show what action is performed on the specific data.

 


Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |