SQL Commands (Part – 4)

SQL Server Inbuilt Functions

String Concatenation: See inner join in the last post… Linking strings together

Select PC.Name as ProductCategory,
PS.Name as ProductSubCategory,
PRD.Name + ‘ ‘ + ‘||’ + ‘ ‘ +  ProductNumber as Combined, ListPrice, StandardCost
FROM Production.Product as PRD
INNER JOIN Production.ProductSubcategory PS
ON PRD.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
ORDER BY PC.NAME, PS.NAME

However, if you try to use + for ListPrice and StandarCost it will return a sum. To fix this use CAST function. See below! Also, combining an integer and NULL value will result in NULL.

CASE Statements:

Select PC.Name as ProductCategory,
PS.Name as ProductSubCategory,
 PRD.Name + ‘ ‘ + ‘||’ + ‘ ‘ +  ProductNumber as Combined, ListPrice, StandardCost,
cast(ListPrice as varchar(10))+ ”+ cast(StandardCost as varchar(10)),

CASE WHEN ListPrice>100 THEN PRD.Name+ ‘ ‘ + ‘||’ + ‘ ‘ + ProductNumber
WHEN ListPrice<100 THEN ‘Less’
END AS Combined2

FROM Production.Product as PRD
INNER JOIN Production.ProductSubcategory PS
ON PRD.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
ORDER BY PC.NAME, PS.NAME

Case statement can also accompanied by where and  else statement!

Select PC.Name as ProductCategory, PS.Name as ProductSubCategory,
PRD.Name + ‘ ‘ + ‘||’ + ‘ ‘ +  ProductNumber as Combined, ListPrice, StandardCost,

cast(ListPrice as varchar(10))+ ”+ cast(StandardCost as varchar(10)) as xx,

CASE WHEN ListPrice>100 THEN PRD.Name+ ‘ ‘ + ‘||‘ + ‘ ‘ + ProductNumber
ELSE PRD.Name
END AS Combined2

FROM Production.Product as PRD
INNER JOIN Production.ProductSubcategory PS
ON PRD.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
CASE WHEN PS.Name In (‘Helmets’,’Bike Racks’, ‘Bike Stands’) then ‘Accessories‘ end = PC.Name
ORDER BY PC.NAME, PS.NAME

You might be wondering what is = PC.Name doing? Just ask yourself how does WHERE clause work?

Select PC.Name as ProductCategory, PS.Name as ProductSubCategory,
 PRD.Name + ‘ ‘ + ‘||’ + ‘ ‘ +  ProductNumber as Combined, ListPrice, StandardCost,
cast(ListPrice as varchar(10))+ ”+ cast(StandardCost as varchar(10)) as xx,

CASE WHEN PC.Name= ‘Components’ and PS.Name = ‘Brakes‘ then ‘Label1‘
ELSE
      CASE WHEN PC.Name LIKE ‘COMPONENTS’ and PS.Name not like ‘ Brakes’ then ‘Label2‘
      ELSE ‘Label3‘ END
END AS NewColumn

FROM Production.Product as PRD
INNER JOIN Production.ProductSubcategory PS
ON PRD.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
ORDER BY PC.NAME, PS.NAME

 

Custom Sorting

In the output above if you want  to order your items by Clothing, Accessories, Bikes and only want three of those products how can you accomplish it?

ORDER BY CASE WHEN PC.Name = ‘Clothing‘ THEN 1
WHEN PC.Name = ‘Accessories’ THEN 2
WHEN PC.Name = ‘Bikes’ THEN 3 END

New Table: How can you put an ouput of select statement directly in a table?

SELECT Name, rowguid, ‘test‘ as TEST into mytest
from AdventureWorks2008R2.Production.ProductModel

The keyword here is into. Notice I will have third column named TEST with values test. If I don’t give column a name then this method does not work.

Convert: Converts one data type to other like cast but also allows flexibility for style.

SELECT ModifiedDate,
CAST(ModifiedDate as DATE) as DateOnly,
CAST(CAST(ModifiedDate as DATE) as Datetime) as LostTime,
CAST(ModifiedDate as time) as TimeOnly,
CAST(ModifiedDate as varchar(20)) as VarcharDate,
CONVERT(VARCHAR(30),ModifiedDate,101) as DDMMYYYY
from Production.Product

In the covert function 101 yields date in DD-MM-YYYY format, there are other options as well. Check out
http://technet.microsoft.com/en-us/library/ms174450(v=sql.110).aspx . By the way, if we have our dates in varchar format, it allows us to use a number of string functions.

Commonly Used Aggregate Functions:

SELECT max(ReorderPoint) as MAX,
MIN(ReorderPoint)as MIN,
AVG(ReorderPoint)AS Average,
sum(ReorderPoint)AS Sum,
count(ReorderPoint) as TotalCount
from Production.Product

In order to use aggregate functions you will need a group by clause.

Select PC.Name as ProductCategory,PS.Name, SUM(StandardCost)as TotalCost
FROM Production.Product as PRD
INNER JOIN Production.ProductSubcategory PS
ON PRD.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
GROUP By PC.Name, PS.Name
ORDER By 1

I am interested in total count of product subcategories greater than 2. How would you find total count of product sub-category for Bib-Shorts and Bottle and Cages only?

Select PS.Name as ProductSubCategory,
count(Prd.Name) as TotalNumber
FROM Production.Product as PRD
INNER JOIN Production.ProductSubcategory PS
ON PRD.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
WHERE PS.Name IN (‘Bib-Shorts’, ‘Bottles and Cages’)
GROUP by PS.Name
HAVING COUNT(prd.name)>=2
ORDER BY 1

Remember Where comes before Group By and Having comes after. How do you find duplicates in table?
SELECT EmployeeID, count(*) FROM tablename GROUP BY EmployeeID HAVING count(*)>1

COMMON DATE-TIME FUNCTIONS:

It is a great idea to understand what input a  function takes in and what output it returns. Why? Sometimes you may want to concatenate three things by using CAST statements if the output are integers. What if there’s another function that simply outputs what you need in varchar. Here’s how you can quickly see what a function takes as an input and what it returns. Parameters are user driven input.

Examples:

SELECT GETDATE(), GETUTCDATE(), SYSDATETIME(), CURRENT_TIMESTAMP

SELECT DAY(GETDATE()),MONTH(getdate()),YEAR(GETDATE()),
                 DATENAME(MONTH,GETDATE()), DATENAME(WEEKDAY,GETDATE()),
                 DATENAME(W,GETDATE()), DATEPART(WEEKDAY,GETDATE())

SELECT DATEDIFF(HOUR,GETDATE(),GETUTCDATE()), DATEADD(DAY,5,GETDATE()),
                 DATEADD(WEEK,-10,GETDATE()), ISDATE(CURRENT_TIMESTAMP),
                 ISDATE(‘02/22/0D3‘)

Some questions you might consider asking is.

1. Why use UTC?
   It takes care of differences in timezones  if you are a part of global company.
2. What’s the difference between YEAR and YEAR of DateTime function? The output type!
3. If you are importing data from Mysql to SQL SERVER and if it(SQL SERVER) doesn’t incorporate that date range, how can you handle such case. In other words, how can you replace a junk stuff like 01/22/0001 to 01/01/9999 ?
   SELECT CASE WHEN ISDATE(Mydate)=0 then ‘01/22/9999‘ ELSE Mydate
4. If you want to create a table for users in JAPAN and you are in USA (EasternTime)? How can you achieve this?  USA time to UTC to JAPAN time.

STRING FUNCTIONS:

String functions perform an operation on a string input value and return a string or numeric value. Some commonly used string functions along with their examples are as follows:
SELECT TOP 5
[Name]as Name,
UPPER(Name) as UpperName,
LEN(Name) as A,
LTRIM(Name) as B,
REPLACE(Name,’ ‘,’**’) as C,
STUFF(Name,1,4,’^%^$’) as D,
SUBSTRING(CAST (Name AS Varchar(12)),2,LEN(Name)) as E, /**You don’t have to use                                                                                    CAST for SQL Server 2008 and Beyond**/
CHARINDEX(‘a’,Name ) as F ,
CHARINDEX(‘a’,Name,3 ) as G ,
REPLICATE(‘C’,3) as H, /**Adds specific character**/
[ProductNumber] as ProductNumber,
REPLICATE(’00’,20-LEN(Name))+ Name,
LEFT(Name,3)

FROM [AdventureWorks2008R2].[Production].[Product]

/**IF YOU EVER GET AN ERROR THAT SAYS STRING OR BINARY DATA WILL BE TRUNCATED ***IT HAS SOMETHING TO DO WITH THE LENGTH OF YOUR DESTINATION Column!*****USE Alter Table to alter the length***/

Note:  STUFF function is very similar to REPLACE but a start location and an end location is                                 specified for replacement.

RANKING FUNCTIONS:

Four commonly used ranking functions are Rank , Dense_Rank, Row_Number and Ntile. They all             have the same syntax.

SELECT FrenchProductSubcategoryName,
ProductCategoryKey,
SpanishProductSubcategoryName,
RANK() Over( Order by [ProductCategoryKey])
  FROM [AdventureWorksDW2008R2].[dbo].[DimProductSubcategory]

SELECT FrenchProductSubcategoryName,
ProductCategoryKey,
SpanishProductSubcategoryName,
RANK() Over( Order by [ProductCategoryKey], [SpanishProductSubcategoryName])
 FROM [AdventureWorksDW2008R2].[dbo].[DimProductSubcategory]

SELECT FrenchProductSubcategoryName,
ProductCategoryKey,SpanishProductSubcategoryName,
Dense_RANK() Over( Order by [ProductCategoryKey])
FROM [AdventureWorksDW2008R2].[dbo].[DimProductSubcategory]

         Select * From
(
         SELECT
FrenchProductSubcategoryName,
ProductCategoryKey,
         Row_number() Over( Partition by ProductCategoryKey
         Order by [ProductCategoryKey]) as random
FROM [AdventureWorksDW2008R2].[dbo].[DimProductSubcategory]
) a
         Where a.random = 1
         /**Row_Number can identify duplicates **/