Tag: sql tutorial

SQL: Check if table exists

On By Ben Larson Ph.D.In MS SQL Server5 Comments

Copy, paste and run this code in your SQL Server Management Studio to build the database and table we are using in this exercise:

create database sandbox;

use sandbox;
CREATE TABLE employee_id (
        emp_nm varchar(30) not null,
        emp_id varchar(8),
        b_emp_id varchar(8),
        PRIMARY KEY(emp_nm) );

To check if a table exists in SQL Server, you can use the INFORMATION_SCHEMA.TABLES table.

Running the following code, produces the results below:

USE SANDBOX;

Select *
from INFORMATION_SCHEMA.TABLES;

1

You can use this table with an IF THEN clause do determine how your query responds whether or not a table exists.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = 'employee_id')
BEGIN
  PRINT 'Yes'
END

2

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = 'employee_ids')
BEGIN
  PRINT 'Yes'
END

ELSE

BEGIN
  PRINT 'No'
End

3

One of the more common uses I find for this when I need to create a table in a script. I want to make sure a table with same name doesn’t already exist, or my query will fail. So I write a query like the one below.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = 'employee_id')
BEGIN
  drop table employee_id
END

ELSE

BEGIN
  print 'Table does not exist'
End
 CREATE TABLE employee_id (
        emp_nm nvarchar(30) not null,
             emp_id nvarchar(8),
             b_emp_id nvarchar(8)
             PRIMARY KEY(emp_nm) );

INSERT INTO employee_id
       (emp_nm, emp_id)
VALUES
       ('Bob', 'A1234567'),
       ('Lisa', 'A1234568')

INSERT INTO employee_id
       (emp_nm, b_emp_id)
VALUES
       ('Priyanka', 'B1234567');

SQL: Coalesce

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

Coalesce is a simple but useful SQL function I use quite often. It returns the first non-null value in a list of values.

A common real world application for this function is when you are trying to join data from multiple sources. If you work for a company known for acquiring other companies, your HR tables are bound to be full of all kinds of inconsistencies.

In this example below, you will see a snip-it of an HR table created by merging tables from two companies. The first two employees have an emp_id and the last one has a b_emp_id. The last one, Priyanka, is an employee from a newly acquired company, so her emp_id is from the HR table of the newly acquired company.

c1

So if you just want to merge the 2 emp_id columns into one, you can use the coalesce() function.

The syntax for coalesce is: coalesce (col1,col2,….)  as alias

Coalesce takes the first non-null. In the example below, we check emp_id first, if it has a value, we use that. If emp_id is null, we look for the value in b_emp_id.

select emp_nm,
coalesce (emp_id, b_emp_id) as emp_id
from employee_id

c2

If you want to try this for yourself, here is the code to build the table out for you.

CREATE TABLE employee_id (
        emp_nm nvarchar(30) not null,
             emp_id nvarchar(8),
             b_emp_id nvarchar(8)
             PRIMARY KEY(emp_nm) );
INSERT INTO employee_id
       (emp_nm, emp_id)
VALUES
       ('Bob', 'A1234567'),
       ('Lisa', 'A1234568')
INSERT INTO employee_id
       (emp_nm, b_emp_id)
VALUES
       ('Priyanka', 'B1234567');

 

SQL: Learn to use Cursors – List table names

On By Ben Larson Ph.D.In MS SQL Server1 Comment

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

On By Ben Larson Ph.D.In MS SQL Server, UncategorizedLeave a comment

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: SELECT Statement

On By Ben Larson Ph.D.In MS SQL Server1 Comment

 

SELECT is doubtlessly the most important command in SQL. A database is completely useless unless you can query the data it is holding. SELECT is how we query.

If you want to follow along:

You will need MS SQL Server installed. Here is a link to instructions: InstallSQL

Microsoft provides a great sample database known as Adventure Works. I am working with the 2012 release. You can download it here: AdventureWorksDownload

Once you have Adventureworks downloaded, you will need attach it to SQL Server.

Copy and paste the download file to somewhere you can find it. I recommend:

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA

** the portion in red changes based on what version of SQL Server you downloaded. 

sqlSelect

Open SQL Server Management Studio. Windows Key + R – Type SSMS

sqlinstall2

Login to your server

sqlinstall3

Go to Object Explorer – Right Click Database > Attach…

sqlSelect1

Click Add…

sqlSelect2

Select Adventureworks2012_Data.mdf

sqlSelect3.jpg

In the bottom window, highlight the log file and click Remove. Then Click Okay

sqlSelect4.jpg

Go back to Object Explorer. Expand Databases > AdventureWorks2012 > Tables

sqlSelect6

Just picking a table at random, I selected HumanResources.Employee. Let us see what is in the table. Click the New Query button up top, and a new query window will open.

sqlSelect7

Make sure you are working with the right database. Select AdventureWorks2012 from the drop down:

sqlSelect17

Cut and Paste the following into the new query window.

select *
from HumanResources.Employee
go

select JobTitle, BirthDate, Gender, HireDate
from HumanResources.Employee
go

select JobTitle as Job, BirthDate as DOB, Gender, HireDate as [Start Date]
from HumanResources.Employee
go

select HireDate as [Start Date], JobTitle as Job, BirthDate as DOB, Gender
from HumanResources.Employee
order by HireDate
go
select HireDate as [Start Date], JobTitle as Job, BirthDate as DOB, Gender
from HumanResources.Employee
where gender like ‘F’
order by HireDate
go

 

One cool thing about SQL code is that you can select only the  code you want to execute

Highlight the first group of code and click the ! Execute button

sqlSelect8

Syntax

  • Select * – Select all fields in the table. “*” is a wildcard in SQL
  • from HumanResources.Employee – this is the table we want to work with
  • go – this indicates and end to the code block. It is not needed when executing code block by block (like we are doing here), but it is a good practice to get in the habit of adding it

Notice the results show every field in the table. It displays in a spreadsheet like table

sqlSelect9.jpg

Now Select the next code group  and click ! Execute

sqlSelect10

Syntax

  • select JobTitle, BirthDate, Gender, HireDate – Here we are selecting four specific columns from the table
  • The rest is same as above

Results show the 4 columns selected above

sqlSelect14.jpg

Next block of code – click ! Execute

sqlSelect11.jpg

  • select JobTitle as Job, BirthDate as DOB, Gender, HireDate as [Start Date] – In this case, we use the “as” command to give the field names more User friendly names

Note the column names have changed

sqlSelect15.jpg

Next block of code – click ! Execute

sqlSelect12

syntax

  • select HireDate as [Start Date], JobTitle as Job, BirthDate as DOB, Gender – here we reordered the fields so that HireDate appears first
  • order by HireDate – this SQL for Sort by. The results will now be ordered by HireDate

Notice Start Date(HireDate) is now the first column and the records (rows) are sorted in order from oldest Start Date to newest

sqlSelect16.jpg

FInal block of code – click ! Execute

sqlSelect13

Syntax

  • where gender like ‘F’ – here we are filtering the results to only ones were Gender is F.

Note now only Female employees are in the results

sqlSelect18

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

Follow this link for more SQL content: SQL