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

Installation of Silverlight for Visual Studio 2008 and NET Framework 3.5 along with small example:

Installation of Silverlight for Visual Studio 2008 and NET Framework 3.5 along with small example:

Microsoft is promoting Silverlight aggressively. I really impressed by its functionality at first glance so I decided to write on article on that. When I wanted to start using Silverlight, I tried to search the web but I didn’t find sure shot and perfect tutorial or article. You can find few things somewhere and another few things somewhere else. This was just my personal experience.

Mr. Rushik Shah has really done an excellent job for this article. He spent his precious personal time for this article so I would like to thanks Mr. Rushik Shah, my cool minded subordinate and real gentleman.

Step 1: Download and install Visual Studio 2008 SP1 form Microsoft web site. Here is the link for the same. http://www.microsoft.com/downloads/details.aspx?FamilyId=FBEE1648-7106-44A7-9649-6D9F6D58056E&displaylang=en

You will have to hold your horses for a while as it may take approx half an hour, based on your internet speed. It will ask you to restart your computer also after successfully completion of installation.

Step 2: Download and install Silverlight Toolkit from Microsoft website for SDK support. Please follow the below link. http://www.microsoft.com/downloads/details.aspx?FamilyId=c22d6a7b-546f-4407-8ef6-d60c8ee221ed&displaylang=en

Step 3: Once you download & install above utilities, you will need some more controls which you would not find in Toolkit downloaded in Step 2. You can download one binary file along with few examples from CodePlex site with below link.

http://www.codeplex.com/Silverlight

Don’t need to install codeplex’s library, it will be used later in step 7

Step 4: After installing all three utilities given above, you will be ready to create Silverlight enable website from your visual studio 2008. Simply create New Website from your Visual Studio 2008 like any other simple website you create from File->New->Website.

Step 5: Now, you will get one tab “Silverlight” in your tool box of your website. Please double click on the control named “Silverlight” in your control tool box. That will draw one silverlight control on your Default.aspx webform. Its ID will be “Silverlight1” in your source of Default.aspx. You can also observer some binary files will be automatically added to your Bin folder.

Make sure you have script manager in your Default.aspx page under the <Form> tag.

<asp:ScriptManager ID=”ScriptManager1″ runat=”server”>

        </asp:ScriptManager>

Step 6: Now let your web application as it is and click on menu File->Add->New Project. From here, you have to select “Silverlight Application” as your new application. Let default selection as it is in dialog box and click on “OK”. You will see one project is added to your web application and one page named “SilverlightApplication1TestPage.aspx” and another page named “SilverlightApplication1TestPage.html” will be added to your web application.

Step 7: If you observe Page.XMAL in your application “SilverLightApplication1” which you have added to your web application, you will get your playing area. You can also see tool box and silverlight tools. I am going to introduce you with “Auto Complete Textbox” which is not available in Microsoft toolkit, you have to use CODEPLEX’s tool kit which we have downloaded in Step 3. We have to add the reference of “Microsoft.Windows.Controls.dll” found in “Binaries” folder of CODEPLEX’s kit.

Step 8: Right click on “SilverlightApplication1” and click on “Add Reference”.  Once you see dialog box, go to “Browse” tab, select directory when you have downloaded CODEPLEX kit and select “Microsoft.Windows.Controls.dll” from “Binaries” folder than click on “OK” button of dialog box.

Step 9: after adding the reference to the project, we have to add controls to our tool box of “SilverLightApplication1” by right clicking on Toolbox and selecting “Choose Item” option. Once dialog box appear, go to “Silverlight” tab in that dialog box.  Now click on “Browse” button and you will have to locate the same “Microsoft.Windows.Controls.dll” file. As soon as you will give the reference, you will see many new controls with selected check box. Click on “OK” button and you will see some new control added to your tool box.

Step 10:  Drag “AutoCompleteBox” from tool kit to your “PAGE.XAML” and set its name, heigh and width like:

<controls:AutoCompleteBox x:Name=”txtAuto” Height=”25″ Width=”150″></controls:AutoCompleteBox>

Step 11: Now, this is a time to go to “Page.XAML.cs” for some programming stuff. You will see public page() constructor there in .CS page. Please add one string array as source of our “txtAuto” below “InitializeComponent()” like:

public Page()

        {

            InitializeComponent();

            txtAuto.ItemsSource = new string[] { “Ritesh”, “Shah”, “Rushik”, “Chirag”, “Abhijit”, “Monica”, “Rashmika”, “Krunal”, “Jatin”, “Zodiac”, “Yatin”, “Prakash” };

    

        }

Step 12: Once you will done with this, you are ready to launch your web application with silver light power, but before you do that, you have to add reference of your “SilverLightApplication1” in your Default.aspx page you have in your web application. Remember, we have drawn one tool in default.aspx with name “Silverlight1” you have to add source of your “SilverLightAPplication1” in that. See below script, I added the .XAP project as a source. You can simply copy that source from your web application. You might not have forgotten “SilverlightApplication1TestPage.aspx” which has automatically added to your web application.

 

<asp:Silverlight ID=”Silverlight1″ runat=”server” Source=”~/ClientBin/SilverlightApplication1.xap” Height=”100px” Width=”100px”>

        </asp:Silverlight>

 

Now you are ready to launch your application. Run your web application’s default.aspx page.

Note: As per my personal experience, Silverlight is not working with its full feature in other browsers than IE. I tried it in opera, chrome and firefox along with IE. May be I am mistaken or don’t have all plug in.

 

Reference: Ritesh Shah

“WHERE” Condition with LIKE in ACCESS:

“WHERE” Condition with LIKE in ACCESS:

Generally we used to work with SQL-Server or ORACLE database so we are very well aware with its SQL syntaxes but when sometime we have to deal with ACCESS database, we don’t care to learn it and use our SQL knowledge in ACCESS database. This might lead to logical or syntax error sometime. Once, I have been asked by one of my colleague that simple LIKE is not working in ACCESS database. I had a quick look of it and found one syntax problem in his SQL statement.

Unlike SQL-Server ACCESS database won’t accept”‘%” wildcard. Rather it will accept “*” (asterisk) sign. So if you want to select data from EMPLOYEE table whose first name starts with “R” then you should go for:

SELECT * FROM Employee WHERE FirstName like ‘R*’

Rather than SELECT * FROM Employee WHERE FirstName like ‘R%’

Second SQL statement is wrong in ACCESS database. It’s a logical error.

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