another blog :: Fight the fear of SQL with SQLHub.com

please visit

http://www.sqlhub.com  for further new tips and tricks of SQL-Server and .NET. I have booked this domain specially for SQL Server blogging.

Ritesh Shah
http://www.SQLHub.Com
Fight the fear of SQL

Complex Aggregate Function example in SQL-Server 2005 with SubQuery:

Complex Aggregate Function example in SQL-Server 2005 with SubQuery:

In general analysis and reporting purpose, sometime we need to find department name, total salary paid to each department, total number of project department is working for and average salary being paid to each department. This is data administrative department may frequently asked for. Let us have a practical example of the same with GROUP BY clause, AGGREGATE function and SubQuery. For the demonstration purpose, we need to create following three tables and have to insert some data in it.

 

Create Table Department

(

      deptNo INT IDENTITY(1,1) NOT NULL,

      deptName VARCHAR(15) NOT NULL,

      Description VARCHAR(50) NULL

)

 

Create Table Employee

(

      empNo INT IDENTITY(1,1) NOT NULL,

      empName VARCHAR(50) NOT NULL,

      deptNo      INT NOT NULL,

      Salary INT NOT NULL

)

 

Create Table Project

(

      projNo INT IDENTITY(1,1) NOT NULL,

      projName VARCHAR(50) NOT NULL,

      deptNo INT NOT NULL    

)

 

 

Create Table Department

(

      deptNo INT IDENTITY(1,1) NOT NULL,

      deptName VARCHAR(15) NOT NULL,

      Description VARCHAR(50) NULL

)

INSERT INTO Department(deptName,Description)

SELECT ‘MIS1’,‘MIS1’ UNION ALL

SELECT ‘MIS2’,‘MIS2’

 

Create Table Employee

(

      empNo INT IDENTITY(1,1) NOT NULL,

      empName VARCHAR(50) NOT NULL,

      deptNo      INT NOT NULL,

      Salary INT NOT NULL

)

INSERT INTO Employee(empName,deptNo,Salary)

SELECT ‘Ritesh’,1,50000 UNION ALL

SELECT ‘Bihag’,2,29000 UNION ALL

SELECT ‘Rajan’,1,30000 UNION ALL

SELECT ‘Alka’,2,19000

 

Create Table Project

(

      projNo INT IDENTITY(1,1) NOT NULL,

      projName VARCHAR(50) NOT NULL,

      deptNo INT NOT NULL    

)

INSERT INTO Project(projName,deptNo)

SELECT ‘A’,1 UNION ALL

SELECT ‘B’,2 UNION ALL

SELECT ‘C’,1

 

Now, we are going to find department name, salary paid to each department, project – department is working for and last but not least average salary paid to each department in order to find efficiency of department.

SELECT Dept.deptname, emp.salary, prj.[Count],emp.salary/prj.[count] as ‘AverageSalary/DEPT’

FROM Department Dept,

(SELECT deptNo, sum(salary) salary FROM Employee GROUP BY deptNo) emp,

(SELECT deptNo, count(*) [Count] FROM Project GROUP BY deptNo) prj

WHERE Dept.deptNo=emp.deptNo and emp.deptNo=prj.deptNo

Above query has used simple SUM and COUNT aggregate function with subquery as table. As I gave example for Aggregate function, let me write down something more for an Aggregate function for those who are new to it.

Aggregate function will return only single row with computed value which summarizes the original result set. It will not return all row in return set. When you use aggregate function first result set returns the row set based on FROM clause and WHERE condition and then filtered records only will be aggregated.

Aggregate functions are expressions so it is mandatory to give column name to it explicitly or else it will return NULL as a column name, if you observe we gave name to all aggregated column like ‘AverageSalary/DEPT’. If we wouldn’t have done that, it would have return NULL as column name.

Aggregate function will not handle precision of numeric column, it is completely based on the source column. However, you can convert the results to other data type as well.

 

Reference: Ritesh Shah

Subquery with GROUPING as a Table in SQL-Server 2005:

Subquery with GROUPING as a Table in SQL-Server 2005:

Subquery can be used as a derived table in FORM clause of the T-SQL query. Believe me this is really powerful approach and can bring you out of some difficult situation. I have already previously written some articles on Subquery, you can refer those for getting basic idea about subquery, correlated subquery,CTE etc.

Let us create two tables for demonstration and insert some data in it.

–Create first table

CREATE TABLE CricketerDetails

(

ID INT IDENTITY(1,1) CONSTRAINT pk_cricketID PRIMARY KEY NOT NULL,

Name VARCHAR(25) NOT NULL,

Country VARCHAR(10) NOT NULL

)

 

–Insert data into first table

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Sachin Tendulkar’,‘India’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Steve Waugh’,‘Australia’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Saurav Ganguly’,‘India’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Jaisurya’,‘Sri Lanka’)

 

–Create second table

CREATE TABLE PersonalScore

(

MatchName VARCHAR(15) NOT NULL,

Run INT NOT NULL,

CricketerID INT NOT NULL CONSTRAINT fk_cricketid REFERENCES CricketerDetails(id)

)

 

–Insert data into second table

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,100,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,10,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,17,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,0,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,10,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,99,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,137,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,10,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-1998’,190,1)

 

After finishing above task, let us see how we can get cricketer name, country and his score with simple JOIN clause.

SELECT C.Name,c.country,MAX(p.Run)

FROM PersonalScore p join CricketerDetails c

ON p.cricketerid=c.id

GROUP BY C.name,c.country

We have achieved whatever we wanted. If you observed we have to put every column in group by clause which is not included in AGGREGATE function. In this case, those are Name and Country. What if you don’t want to put those two columns in group by clause as this is something messy sometime. Let us see how can achieve the same results with the help of derived subquery as table.

SELECT C.Name,c.country,p.Run

FROM CricketerDetails c

JOIN

(

      SELECT Cricketerid,MAX(run) as Run FROM PersonalScore GROUP BY CricketerID

)  p

ON c.ID=p.CricketerID

See, we have used subqery in JOIN clause rather than any table and achieved the same results. This technique of relational algebra may prove very powerful sometime in difficult situation.

 

Reference: Ritesh Shah

UNION and new feature of SQL Server 2005 (INTERSECTION, EXCEPT) Difference of INTERSECT and INNER JOIN:

UNION and new feature of SQL Server 2005 (INTERSECTION, EXCEPT):

You might have read my previous articles on JOIN as per those articles; JOIN is multiplication of data whereas UNION is addition.

UNION does nothing other than stack the data of multiple result sets in one result sets. While using the UNION you have to consider few things like: column name, number and aliases should be mentioned in first part of the SELECT. ORDER BY clause will sort the complete result sets, it will use the column name of first SELECT statement and should be write down after the last SELECT statement.

Let us create two demo tables to use UNION.

–create first table for demonstration

CREATE TABLE CAR

(

ID int IDENTITY(1,1),

CarName VARCHAR(16)

)

 

–create second table for demo

CREATE TABLE BIKE

(

ID int IDENTITY(1,1),

BikeName VARCHAR(16)

)

 

–Insert records in CAR table

INSERT INTO CAR

SELECT ‘HONDA crv’ UNION ALL

SELECT ‘Mahi. Raunalt’ UNION ALL

SELECT ‘Test’

 

–Isert records in BIKE table.

INSERT INTO BIKE

SELECT ‘HONDA Karishma’ UNION ALL

SELECT ‘Bajaj DTSI’ UNION ALL

SELECT ‘Test’

 

Now, both the select statement of above two tables will be merged and will return single result sets.

–use of union all

SELECT ID,CarName FROM CAR

UNION ALL

SELECT ID,BikeName FROM BIKE

Order by CarName

Above query returns all the records of both tables. Though we gave ORDER BY for CarName but it will sort complete second column which include some data of bikeName as well. You cannot give ORDER BY on BikeName column.

Above query was just an example, you can use UNION for less than or equal to 256 tables. This is not at all small amount.

Intersection Union:  Intersection Union find common row in both data sets.  As soon as you read this statement, you will say, ohh, this could be done by INNER JOIN as well. So, the answer is INNER JOIN matches two table horizontally and INTERSECTION matches two datasets vertically.  There is one more difference in INTERSECTION and INNER JOIN is that, Intersection query will see NULL as common and includes the row in the intersection and INNER JOIN will not even includes two different row with NULL value in result sets.

Now, have a look at INTERSET usage in below query.

–use of intersect union

SELECT ID,CarName FROM CAR

INTERSECT

SELECT ID,BikeName FROM BIKE

Order by CarName

You will get only one row which is same in both the table.

EXCEPT:  Except (a.k.a. DIFFERENCE UNION)  finds records exists in one data sets and not available in another datasets. In above case we have three records in CAR table but third record is exist in BIKE table as well so EXCEPT will display only first two records. Have a look at below query.

–use of EXCEPT

SELECT ID,CarName FROM CAR

EXCEPT

SELECT ID,BikeName FROM BIKE

Order by CarName

Reference:  Ritesh Shah

Business Logic with JOIN – Multiple Join examples in SQL-Server 2005:

Business Logic with JOIN – Multiple Join examples in SQL-Server 2005:


I wrote my previous article about different type of JOIN in SQL-Server 2005. Due to the space constraint in that article, complex example has not been given there, only few simple examples. I felt to explode this topic further by giving solution to some really needed business logic.


If you want to create report for blood testing laboratory, who used to check samples of employee of different companies for different purposes. Following should be the table structure, we will be creating three table. One will store information about Order received from different companies. Another one will be the detail of that order, means which company sent how much samples of how much employee?? And the last one is further more details about how much test should be performs on which sample???


Let us create three tables.


–CREATING FIRST ORDER TABLE


USE [AdventureWorks]


GO


/****** Object: Table [dbo].[Orders] Script Date: 03/08/2009 12:24:39 ******/


SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO


SET ANSI_PADDING ON


GO


CREATE TABLE [dbo].[Orders](


[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,


[OrderDate] [datetime] NOT NULL,


[CompanyName] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,


PRIMARY KEY CLUSTERED


(


[OrderID] ASC


)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


) ON [PRIMARY]



GO


SET ANSI_PADDING ON


Now, child table of above.



USE [AdventureWorks]


GO


/****** Object: Table [dbo].[OrderDetails] Script Date: 03/08/2009 12:37:27 ******/


SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO


SET ANSI_PADDING ON


GO


CREATE TABLE [dbo].[OrderDetails](


[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,


[SampleNumber] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,


[SampleOfEmployee] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,


[SampleReceivedDate] [datetime] NULL,


PRIMARY KEY CLUSTERED


(


[SampleNumber] ASC


)


) ON [PRIMARY]



GO


SET ANSI_PADDING ON


GO


ALTER TABLE [dbo].[OrderDetails] WITH CHECK ADD CONSTRAINT [fk_orderid] FOREIGN KEY([OrderID])


REFERENCES [dbo].[Orders] ([OrderID])


GO


ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [fk_orderid]


One more child table.



USE [AdventureWorks]


GO


/****** Object: Table [dbo].[sampledetails] Script Date: 03/08/2009 12:43:00 ******/


SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO


SET ANSI_PADDING ON


GO


CREATE TABLE [dbo].[sampledetails](


[SampleNumber] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,


[TestType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,



PRIMARY KEY CLUSTERED


(


[SampleNumber] ASC,


[TestType] ASC


)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


) ON [PRIMARY]



GO


SET ANSI_PADDING ON


Now, this is time to insert few records on above tables.


–Received three order from two company


INSERT INTO Orders


SELECT ‘L1000’,‘2009-03-01’,‘Test Inc.’ UNION ALL


SELECT ‘L1001’,‘2009-03-02’,‘Zeel Inc.’ UNION ALL


SELECT ‘L1002’,‘2009-03-04’,‘ABC Inc.’



–received two sample for first order, one sample for second order


–sample for third order yet to receive


INSERT INTO OrderDetails


SELECT ‘L1000’,‘L1000-01’,‘James’,‘2009-03-01’ UNION ALL


SELECT ‘L1000’,‘L1000-02’,‘John’,‘2009-04-01’ UNION ALL


SELECT ‘L1001’,‘L1001-01’,‘Smita’,‘2009-03-05’



–details of sample, which test suppose to be performed


–on which sample


INSERT INTO SampleDetails


SELECT ‘L1000-01’,‘Cancer’ UNION ALL


SELECT ‘L1000-01’,‘AIDS’ UNION ALL


SELECT ‘L1000-02’,‘BP’ UNION ALL


SELECT ‘L1001-01’,‘AIDS’ UNION ALL


SELECT ‘L1001-01’,‘Cancer’


Well, now we are ready to pull data out of three tables with different type of JOIN which we have seen in my previous article.


NOTE: many different logic can be used to perform following tasks, I am giving this just to explain the use of JOIN otherwise there are some more facility which can handle all of the below situation other than join.


If you want to get OrderID, OrderDate, Company (who gave the order??), SampleNumber, EmployeeName (for which employee company has sent the sample??), TestType (which test to be performed in above tables?)


You can use following query which has used one LEFT OUTER JOIN and one INNER JOIN.


SELECT O.OrderID,O.OrderDate,O.CompanyName,


OD.SampleofEmployee,OD.SampleNumber,SD.TestType


FROM


ORDERS AS O


LEFT OUTER JOIN


ORDERDETAILS AS OD


ON


O.OrderID=OD.OrderID


INNER JOIN


SampleDetails AS SD


ON


OD.SampleNumber=SD.SampleNumber



If there is a need to find how many samples came from which company. Use following query with aggregate function COUNT.



SELECT


O.CompanyName,Count(SampleNumber) as ‘TOTAL’


FROM


ORDERS AS O


LEFT OUTER JOIN


ORDERDETAILS AS OD


ON


O.OrderID=OD.OrderID


GROUP BY O.CompanyName


ORDER BY TOTAL DESC


Reference: Ritesh Shah

Complete ref of SQL-Server Join, Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, in SQL-Server 2005:

Complete ref of SQL-Server Join, Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, in SQL-Server 2005:

Merging data is heart of SQL and it shows the depth of relational algebra as well as the power and flexibility of SQL. SQL is excellent at selecting the data and SQL Developer can be master by understanding the relational algebra.

Join seems known technology but it is fun all time. Developer can make very creative query with joins by making data twist and shout. Pulling the data out of join could be the answer of very big code of programming language with iteration. It is much faster than any loop in your code. I promise you will have fun by learning this powerful technique.

SQL-Server 2005 is relational database so you always need to join more than one table in order to get complete information out of data tables. Since, tables are self explanatory itself, order of table doesn’t matter.

This article is moved to following link:

http://blog.extreme-advice.com/2009/03/08/complete-ref-of-sql-server-join-inner-join-left-outer-join-right-outer-join-full-outer-join-in-sql-server-2005/

Delete many (multiple) records in bunch: SQL-Server 2005

 Delete many (multiple) records in bunch: SQL-Server 2005

 

I had one task few months back. I was asked to delete 3 million records from one table containing total of 8 million records. You may think, ohh delete….. simple, execute delete statement with condition and sit back on your chair with relax. J Its not that simple, If I would have done it, I would have lost my database due to many reasons. I don’t want to go in details about those reasons. Let us focus on our main topic.

 

USE adventureworks

GO

— creating one temporary table from AdventureWork database

— to perform delete operation.

SELECT * into AdventureWorks.DBO.TempPurchaseOrderDetail FROM Purchasing.PurchaseOrderDetail

–Above query has created 8788 row in my temp table

GO

 

 

–Wants to delete rocords based on where condition of

–following query….

— it has 6624 RECORDS in my table

SELECT * FROM TempPurchaseOrderDetail WHERE PurchaseOrderID<3000

 

Now, we will create one stored procedure which will delete all records from my table which will meet specific conditions.

 

 

CREATE PROC DeleteInBunch

@NumOfRow INT –number given for this variable, will be used with TOP in DELETE statement

AS

SET NOCOUNT ON

–INFINITE loop to iterate until and unless it will finish deleting records

WHILE(1=1)

BEGIN

      DELETE TOP (@NumOfRow) FROM TempPurchaseOrderDetail WHERE PurchaseOrderID<3000

      –LOOP will not break until we will reach to @@ROWCOUND=0

      IF @@ROWCOUNT=0

      BEGIN

            PRINT(‘Records have been delete!!!!’)

            BREAK

      END

END

 

So, finally we have created stored procedure; now let us see whether it actually works!!!

 

–SP will delete all records for specific condition in the bunch of 100

EXEC DeleteInBunch 100

 

Reference: Ritesh Shah