Tag: T-SQL

SQL: Case Statement

On By Ben Larson Ph.D.In MS SQL ServerLeave a comment

The Case Statement is SQL’s version of IF/THEN logic statements.

In this example, I am using the AdventureWorks2012 database, which is free to download. You do not need this to follow the lesson, but if you want to play along, follow this link: SQL: SELECT Statement

 

Let’s start by looking at our data, using the following Select Statement:

SELECT 
 [NationalIDNumber]
 ,[LoginID]
 ,[JobTitle]
 ,[BirthDate]
 ,[MaritalStatus]
 ,[Gender]
 ,[HireDate] 
 FROM [AdventureWorks2012].[HumanResources].[Employee]

Here is the output

sqlagg3

Now what if you don’t really care about the particular employee birthday, you simply want to know who is over 40 and who is 40 or younger.

To do that, we need a mechanism to determine an age above or below 40 from the birth date. In most programming languages, you would simply use an If / Then statement. In SQL, we need to use a Case Statement.

The Case Syntax is simple. Let’s assume a variable A, set to either 1 or 0 representing True or False.

Case
     when A = 1 Then "True"
     when B = 0 Then "False"
End

Note the statement is started with the keyword Case and closed with the keyword End.

In the middle, the logic is controlled using When and Then. When <condition> Then  <Action>

If needed, you can append an Else to your statement to cover conditions not specified in your statements.

Case
     when A = 1 Then "True"
     when B = 0 Then "False"
     else "Neither" 
End

 

 

Now, let’s apply it to our case above. Notice the Case statement is put right into my Select statement just as if it were a common field.

select loginID,
 [NationalIDNumber]
 ,[LoginID]
 ,[JobTitle]
 ,
 case 
    when BirthDate < '1977-01-01' then 'Over40'
    when BirthDate >= '1977-01-01' then 'Under40'
 end
 ,[MaritalStatus]
 ,[Gender]
 ,[HireDate]
 FROM [AdventureWorks2012].[HumanResources].[Employee]

Here are my results. You will note a new column with values Over40 or Under40

2017-01-09_11-29-42.jpg

But what does that column mean? Let’s name it to make it clear.  After the End keyword add as Over40

 select loginID,
 [NationalIDNumber]
 ,[LoginID]
 ,[JobTitle],
 case 
        when BirthDate < '1977-01-01' then 'Over40'
         when BirthDate >= '1977-01-01' then 'Under40'
 end as Over40,
 [MaritalStatus]
 ,[Gender]
 ,[HireDate]
 FROM [AdventureWorks2012].[HumanResources].[Employee]

Here, now the column has a name.

2017-01-09_11-32-09.jpg

 

 

SQL: Join Multiple Tables

On By Ben Larson Ph.D.In MS SQL ServerLeave a comment

If you know how to join 2 tables, I have good news for you. Joining more than 2 is a piece of cake.

Let’s take the example below. We have three tables. JobTitle holds a list of Job Titles. Table JobType holds a list of Job Titles (Supervisor, Technical, Hourly, etc). Finally table Employee holds employee names, addresses and emails.

These tables are related through primary keys(PK) and foreign keys(FK). It is through these PK and FK that we will connect these tables.

3join.jpg

If we want a list of Employees like this : Name, Address, Email, JobTitle and JobType, we would use the syntax below:

select Name, Address, Email, JobTitle, JobType
from Employee as e join JotTitle as j 
          on e.jobid = j.jobid join JobType as jt
          on j.jobTypeID = jt.jobTypeID

**remember MS SQL Server is not case sensitive by default

So as you can see, literally all we had to do is repeat the steps we did to join our first two tables.

If you would like to try an example in the AdventureWorks2012 database, try this:

 select loginid, name, StartDate
 from HumanResources.Employee as e 
 join HumanResources.EmployeeDepartmentHistory as h
 on e.BusinessEntityID = h.BusinessEntityID join HumanResources.Department as d
 on d.DepartmentID = h.DepartmentID

SQL: Where Clause

On By Ben Larson Ph.D.In MS SQL ServerLeave a comment

The Where clause in SQL works as a filter. In this lesson we will be using MS SQL Server and Microsoft’s free database example Adventure Works 2012.

If you want to follow along with the exercises:

MS SQL Server installation instructions here: MS SQL Server: Installation

Instructions for loading Adventure Works: SQL: SELECT Statement

We are going to be using some selected fields from the table HumanResouces.Employee in the AdventureWorks2012 database.

sqlwelcome.jpg

Find single value

Let’s use Where to find the record with the BusinessEntityID of 6:

SELECT [BusinessEntityID]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[SalariedFlag]
FROM [AdventureWorks2012].[HumanResources].[Employee]
Where BusinessEntityID = 6

To find a single matching numeric field, use Where Field = Number

You can also use the following operators

  • = equals
  • > greater  than
  • < less than
  • != not

Look for String

To find a string value, we are going to use the Like keyword: Where Field like ‘String’

SELECT [BusinessEntityID]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[SalariedFlag]
FROM [AdventureWorks2012].[HumanResources].[Employee]
Where JobTitle like ‘Design Engineer’

Using a Wildcard

When trying to search by part of a string, we use the % wildcard.

Consider the sentence “Dogs love chasing cats”

  • ‘Dog%’ = “Dogs love chasing cats”
  • ‘%cats’ = “Dogs love chasing cats”
  • ‘%love%’ = “Dogs love chasing cats”

Below we use a wildcard to find all job titles that end in Engineer

SELECT [BusinessEntityID]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[SalariedFlag]
FROM [AdventureWorks2012].[HumanResources].[Employee]
Where JobTitle like ‘%Engineer’

Between

The Between keyword can used to find records that fall between 2 values

SELECT [BusinessEntityID]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[SalariedFlag]
FROM [AdventureWorks2012].[HumanResources].[Employee]
Where BusinessEntityID between 5 and 20

And, Or

This query returns all Married employees who are Female

SELECT [BusinessEntityID]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[SalariedFlag]
FROM [AdventureWorks2012].[HumanResources].[Employee]
Where MaritalStatus = ‘M’ and Gender = ‘F’

This returns all employees who are married or employees who are F regardless of martial status

SELECT [BusinessEntityID]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[SalariedFlag]
FROM [AdventureWorks2012].[HumanResources].[Employee]
Where MaritalStatus = ‘M’ or Gender = ‘F’

()

Parenthesis are used just like your remember from Algebra class. They state that the items inside must performed first.

This query returns all Married Males as well as all Tool Designers regardless of gender or marital status.

 

SELECT [BusinessEntityID]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[SalariedFlag]
FROM [AdventureWorks2012].[HumanResources].[Employee]
Where (MaritalStatus = ‘M’ and Gender = ‘M’) or JobTitle like ‘Tool Designer’

If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT. 

Follow this link for more SQL content: SQL