Correlated Subquery with CASE in SQL Server 2005

Correlated subquery is always a powerful tool in developer’s toolkit. It has been proved very efficient many time against CURSOR and give good performance as well. Let me show you one good example of correlated subquery with CASE expression in Microsoft SQL Server 2005.

–create table1 for demo

CREATE TABLE CustInfo

(

CustId INT Identity(1,1),

Name VARCHAR(10)

)

GO

–insert records in above table

INSERT INTO CustInfo

SELECT ‘RITSEH’ UNION ALL

SELECT ‘RAJAN’

GO

–create table2 for demo

CREATE TABLE OrderMasters

(

OrderMasterId INT IDENTITY(1,1),

CustId INT,

Quantity INT

)

–insert records in above table

INSERT INTO OrderMasters

SELECT 1,10 UNION ALL

SELECT 1,14 UNION ALL

SELECT 1,2

–co-related subquery with CASE

SELECT C.CustId,C.Name,

CASE(SELECT count(CustId) FROM OrderMasters O WHERE O.CustId=C.CustID)

WHEN 0 THEN ‘No Order’

ELSE ‘Has Order’

END as Status

FROM

CustInfo C

Above, correlated query will return “No Order” if it will not find any records in OrderMasters table and it will return “Has Order” if there is an entry in OrderMasters table.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

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