SQL: Case Statement

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: Reindex a Database

Indexes in databases work kind of like an index in a book. Instead of having to look at each page in a book for something, you can just go to the index – find your topic in an alphabetized list, and go to the page number indicated. Databases use indexes so they do not have to look at every single row in a table that could contain hundreds of thousands up to billions of rows.

The problem is, with all the constant reading and writing to a live database, the indexes quickly become fragmented as they try to keep up will all the new data coming and going. After a while this fragmentation can start to have an effect on your database’s performance.

Rebuild an Index

***Don’t attempt this on a production database while it is in use. Make sure the database is not being used before trying anything in this lesson.

The act of defragging an index in SQL Server is known as rebuilding. You can do it which just a few mouse clicks.

First, let’s find our indexes. You can find Indexes nested under tables in the Object Explorer. I won’t go in depth on Clustered vs Non-Clustered, only know the each table can only have 1 Clustered Index. You can think of it as the master index for that table if it helps.

reindex.jpg

Right click on an index and go to Properties

reindex1.jpg

Select Fragmentation from the Select a page window. Note my index is 66.67% fragmented.

reindex2.jpg

Click out of that window and right click on your index again. This time click Rebuild

reindex1

Click Okay and the window and your Index will be rebuilt. Simple enough.

reindex3.jpg

Rebuild All Indexes in a Table

 

If you want to rebuild all the indexes in a table, you can click on the Index folder and click Rebuild All

reindex6.jpg

Then click okay

reindex5.jpg

Or you can use the follow SQL Code

Reindex Code

DBCC stands for Database Console Commands. It is a list of useful tools you can use to administer a SQL Server. The syntax below is as follows: DBCC DBREINDEX(TABLE NAME, Index you want to rebuild (‘ ‘ = all indexes) , fillfactor)

DBCC DBREINDEX([HumanResources].[Employee],' ',90)

A quick note on fill factor. A fill factor of 0 or 100 tells SQL to fill every index page completely – leave no extra room. If the data was stagnant that could work, but when data is constantly being written and deleted, the indexes need room for correction. That is why you will often see 80 or 90 used as a fill factor. It gives a little wiggle room for the real life functionality of the database.

Reindex All the Tables

If you want to Reindex all the tables in a database, you can do it using a Cursor and While loop. If you do not know cursors in SQL, check out my previous lesson on cursors: SQL: Learn to use Cursors – List table names

The only new elements you will notice here is that I am combining TABLE_SCHEMA+’.’+TABLE_NAME. I give an example below to show you how it works.

Note, this query takes a few seconds (or minutes depending on speed of machine and database size) to run. You will not see anything until the query is completed.

use AdventureWorks2012
go

declare @tableName nvarchar(255)

declare myCursor CURSOR FOR
select TABLE_SCHEMA+'.'+TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'base table'
open myCursor
Fetch next from myCursor into @tableName
While @@FETCH_STATUS = 0
Begin
print 'Working on: '+@tableName
DBCC DBREINDEX(@TableName,' ',90)
Fetch next from myCursor into @tableName
end
close myCursor
Deallocate myCursor

TABLE_SCHEMA+’.’+TABLE_NAME

First result set, schema and table name are different columns. Second result set has them concatenated with a . in between.

reindex7.jpg

 

SQL: Learn to use Cursors – List table names

In this lesson we are going learn to use cursors to print a list of main tables in a database.

As always, I am using Adventure2012 as my database. If you do not have it, and you would like to play along, please refer to the following lessons to get up and running:

  1. MS SQL Server: Installation
  2. SQL: SELECT Statement

First let me show you where to go to find a list tables. SQL Server maintains a list of tables in a table called Information_Schema.Tables. Running the code below will give you results seen below.

Use AdventureWorks2012
go

SELECT * FROM INFORMATION_SCHEMA.TABLES
go

cursor1.jpg

The last column, TABLE_TYPE lets you know what kind of table you are working with: BASE TABLE is the tables you generally refer to as Tables in your database. The other type  you will see in this database is VIEW.

Cursor

In SQL, cursors are a way of letting your step through a result set one row at a time. While the mere mention of cursors can cause many DBA’s and database programmers to twitch and convulse, I still think you should learn about them to have a well rounded SQL education. Also, cursors have come in handy for me more times than I can count.

Look at the code below:

declare @tableName nvarchar(255)

select @tableName = TABLE_NAME
from INFORMATION_SCHEMA.TABLES

print @tableName

First I declare a variable “@tableName” and set it with a select statement. I assign @tableName the values in column TABLE_NAME from INFORMATION_SCHEMA.TABLES.

However when I run print @tableName, you will see the variable only holds the last value. Each previous value was replaced by the one after it.

I want to print each value in the column. So we going to use a cursor

— indicates a REM comment

declare @tableName varchar(255)
declare myCursor CURSOR -- name your cursor
FOR                     -- set cursor to a result set
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'base table'
open myCursor                    -- get our cursor ready to use
Fetch next from myCursor into @tableName -- Fetch gets first  
                                         -- row in cursor results
While @@FETCH_STATUS = 0         --@@FETCH_STATUS is 0 while you still
                                 -- have rows in your cursor set. It changes
                                 -- to -1 when your cursor hits the final row
Begin                  -- Begin while loop
print @tableName       -- prints table name from row cursor is currently on
Fetch next from myCursor into @tableName  -- go to next row
end                   -- end While loop
close myCursor        -- close cursor for use
Deallocate myCursor   -- delete values from cursor

 

 

 

SQL: Working with Date/Time Functions

SQL has a pretty extensive list of date time functions that come in handy when writing queries. I am going to cover a handful of the most common ones today.

GETDATE()

Getdate() returns the current datetime

select getdate()

sqlDate6

Date Parts

use AdventureWorks2012
go

SELECT *
 FROM [AdventureWorks2012].[Sales].[ShoppingCartItem]

sqlDate.jpg

First, looking at the data above. The data columns are in a format known as datetime in SQL (you may also hear many refer to it as a time stamp). It contains a date and time. What if you only want the date?

Convert()

SELECT convert(date, ModifiedDate)
 FROM [AdventureWorks2012].[Sales].[ShoppingCartItem]

sqlDate1.jpg

To select the time only

SELECT convert(time, ModifiedDate)
 FROM [AdventureWorks2012].[Sales].[ShoppingCartItem]

sqlDate2.jpg

YEAR(), MONTH(), DAY

SELECT year(ModifiedDate) as Year, month(modifieddate) as Month,
day(modifieddate) as day
from sales.SalesOrderDetail

sqlDate3

You can filter by date elements too.

SELECT year(ModifiedDate) as Year, month(modifieddate) as Month,
day(modifieddate) as day
from sales.SalesOrderDetail
where day(modifieddate) like '28'

sqlDate4.jpg

Datepart()

You can extract time elements using Datepart()

SELECT datepart(hour, modifieddate) as hour, datepart(Minute, ModifiedDate) as minute
 FROM [AdventureWorks2012].[Sales].[ShoppingCartItem]

sqlDate7.jpg

DateAdd()

dateadd() lets you add or subtract days or months or years to a date. In the example below I add 10 days. The syntax is dateadd(interval(d, m, y), amount, date)

select dateadd(d,10,getdate())

sqlDate8.jpg

DateDiff()

Datediff() gives you the difference between two dates. Look at the dates in the query below:

sqlDate5.jpg

We want to know how many days difference between DueDate and ModifiedDate

SELECT datediff(d,duedate, ModifiedDate)
 FROM [AdventureWorks2012].[Purchasing].[PurchaseOrderDetail]

sqlDate9.jpg

 

 

 

SQL: Create a View

I am working with MS SQL Server and the AdventureWorks2012 database in this lesson. If you want to play along, but do not have either installed on your computer, check out the following lessons to get you up and running:

Views

A view is a virtual table created in SQL that pulls information from existing tables in the database. To understand the need for a view, imagine you are the DBA for AdventureWorks constantly being asked to produce email addresses for employees. You are being asked this because whoever designed the front end of the database that your users interact with, left this vital piece of information out.

Finding an employee’s email address is simple enough. The SQL query below is all you need.

SELECT p.[FirstName]
 ,p.[MiddleName]
 ,p.[LastName]
 ,e.EmailAddress
 
 FROM [Person].[Person] as p join [Person].[EmailAddress] as e
 on p.BusinessEntityID = e.BusinessEntityID

Just add a where statement depending on if you are searching by first or last name.

But what if you are getting tired of having to create this query over and over every day. Well, you could make this query a permanent part of your database by turning it into a view.

The syntax for creating a view is simple. Just add Create View [NAME] and AS

create view HumanResources.vEmail
as 
SELECT p.[FirstName]
 ,p.[MiddleName]
 ,p.[LastName]
 ,e.EmailAddress
 
 FROM [Person].[Person] as p join [Person].[EmailAddress] as e
 on p.BusinessEntityID = e.BusinessEntityID

go

After you run this query, you can now find your view in the Object Explorer

view.jpg

You can now query the view just like you would any other table.

 select *
 from HumanResources.vEmail
 where LastName like 'Ken%'

Views are great especially when dealing with very complex joins. Once you have figured out the proper query, you can just save it as a join and you won’t have to reinvent the wheel next time you need it.

 

SQL: Stored Procedures

Stored procedures are basically executable scripts you store in your database. You can then execute them as needed. Instead of discussing what they are, let’s just make one and see how it works.

Example

We are going to create a stored procedure that returns an employee’s email address based on their first name.

I am using AdventureWorks2012 in MS SQL Server for this lesson. If you do not have either installed and would like to follow along, check out my first two SQL lessons to get up and running:

  1. MS SQL Server: Installation
  2. SQL: SELECT Statement

If you are already up and running, let’s consider the SQL Query below

SELECT p.[FirstName]
 ,p.[MiddleName]
 ,p.[LastName]
 ,e.EmailAddress
 
 FROM [Person].[Person] as p join [Person].[EmailAddress] as e
 on p.BusinessEntityID = e.BusinessEntityID
 where p.FirstName like 'Ken'

I am using two tables, Person and EmailAddress from the schema Person

From my query, I am asking for the FirstName, MiddleName, LastName, and EmailAddress for any employee with the first name Ken.

While this works, the problem is if someone asks you to look for people named Kevin tomorrow, you would have to create this query all over again. Or, we can build a Stored Procedure

Stored Procedure

Let’s create a stored procedure. In this example, we are going to use a variable. In SQL you must declare you variables.

Variables are designated in SQL with the @ symbol in front. So our first line of code below translates to: create procedure dbo.getemail (create a stored procedure name dbo.getemail)  – @name nvarchar(20) – (use a variable named @name which is a character variable limited to 20 characters)

as – (states the code following “as” will be the code executed by the stored procedure)

finally – note the where statement was altered to include the @name variable

create procedure dbo.getemail @name nvarchar(20)
as
SELECT p.[FirstName]
 ,p.[MiddleName]
 ,p.[LastName]
 ,e.EmailAddress
 
 FROM [Person].[Person] as p join [Person].[EmailAddress] as e
 on p.BusinessEntityID = e.BusinessEntityID
 where p.FirstName = @name
go

Once you execute the code above, your stored procedure can be found in your database

storedProcedure

Execute the Stored Procedure

To execute – exec “procedure name” “variable” = “value”

exec dbo.getmail @name=’Kevin’

storedProcedure1.jpg

Now change the value of @name to ‘Mary’

storedProcedure2

Python: Accessing a SQL database

If you really want to do data work, you need to be able to connect to a database. In this example I will show you how to connect to and query data from MS SQL Server with the AdventureWorks2012 database installed.

This lesson assumes some very basic knowledge of SQL. If SQL is a complete mystery, head over to my SQL page: SQL  If you check out the first 4 intro lessons, you will know everything about SQL you need to know for this lesson.

Install pyodbc

To connect to the database, we need to install pyodbc. Go to your Anaconda terminal and type: pip install pyodbc

sqlpython.jpg

Now open up your jupyter notebook and start a new notebook

Connect to Database

import pyodbc

cnxn is our variable – it is commonly used as a shorten version of connection

syntax: pyodbc.connect(‘DRIVER={SQL Server}; SERVER=server name; DATABASE=database name;UID = user name; PWD = password’)

finally cursor =cnxn.cursor() creates a cursor for us. In SQL, a cursor is used to step through your results one row at a time.

sqlpython1.jpg

cursor.execute(place sql query here)  – this is how you pass a sql query – note query goes in quotes

tables = cursor.fetchall() – fetch all the rows in your query results

sqlpython2

We can now iterate through the rows in tables.

sqlpython3.jpg

I don’t like the layout of this. Also we can’t really work with the data.

Pandas Dataframe

first import pandas

  • d= [] – create empty dictionary
  • d.dappend({‘Name’:row.Name, ‘Class’: row.GroupName}) – fill dictionary 1 row at a time
  • df = pd.DataFrame(d) – convert your dictionary to a dataframe

sqlpython4.jpg


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

Follow this link for more Python content: Python

SQL: Join Multiple Tables

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: 4 Types of Joins

SQL Joins come in 4 major types. In T-SQL – Microsoft’s SQL language, unless otherwise specified, a Join defaults to Inner Join. The other three are Right Outer Join, Left Outer Join, and Full Outer Join.

Inner Join

Inner joins match up data that exists in both tables. In the example below, only A and C found in the result table since B,D don’t exist in TableB and E,F don’t exist in TableA.

Syntax

Select *
from TableA as A Join TableB as B
on A.ID = B.ID

innerjoin

Full Outer Join

A Full Outer Join puts all elements in both tables together. Where information is missing, Nulls will appear

Select *
from TableA as A Full Outer Join TableB as B
on A.ID = B.ID

fullouterjoin

Left Outer Join

Left Outer Join takes all data from the Left Table and joins up matching data from the Right table. Left and Right is determined based on the table’s position in the Where clause

Select *
from TableA as A Left Outer Join TableB as B
on A.ID = B.ID

leftouterjoin.jpg

Right Outer Join

Right Outer Join is the exact opposite of the Left Outer Join, with all elements from the Right Table being used and only matching elements from the Left Table.

Select *
from TableA as A Right Outer Join TableB as B
on A.ID = B.ID

rightouterjoin.jpg

SQL: Intro to Joins

More often than not, the information you need does not all reside in one table. To query information from more than one table, use the Join command.

In our example, we are going to be using AdventureWorks2012. If you want to follow along, but do not have SQL Server or AdventureWorks2012 installed, click in the following links:

We are going to be using the following Tables for our Join

Notice they both have a matching field – BusinessEntityID – This field will be important when creating a join.

sqlJoin7

We can call up both tables individually through separate SQL Queries

sqljoin9

However, to combine the two tables into a single result, you will need a Join.

Inner Join

SQL uses Inner Joins by default. In this lesson, I am only going to focus on inner joins. Inner joins work by focusing on columns with matching information. In the example below, the columns with matching information are the Name columns.

The combined result only contains rows who have a match in both source tables. Notice that Sally and Sarah do not appear in the result table.

sqljoin10.jpg

The syntax is as follows:

Select *
from Table A join Table B
on TableA.Match = TableB.Match

sqljoin11.jpg

In our example, the matching column is BusinessEntityID in both tables.

To clean the code up a little bit, we can assign aliases to our tables using the “as” keyword. We are using E and S as our aliases. We can now use those aliases in our “on” clause.

sqlJoin12

And, just as in a regular select statement, we can choose which columns we want. You do however, have to identify which table the columns you are requesting are from. Notice how I did this using the E and S aliases.

sqljoin13.jpg