/******************************************************
 *
 * Name:         basic-training-null.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     12-21-2012
 *     Blog;     www.craftydba.com
 *
 *     Purpose:  Create a series of articles on
 *               database developer (70-433) topics.
 *
 *     Topic:    To be null or not null.
 *
 ******************************************************/


--
-- Select training database
--

-- Which database to use.
USE [TRAINING]
GO


--
-- Create publishers table 
--

-- Delete existing table
IF  EXISTS (
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[PUBS].[PUBLISHERS]') AND
    type in (N'U'))
DROP TABLE [PUBS].[PUBLISHERS]
GO

-- Create new table
CREATE TABLE [PUBS].[PUBLISHERS]
(
   pub_id char(4) NOT NULL,
   pub_name varchar(40) NULL,
   city varchar(20) NULL,
   state char(2) NULL,
   country varchar(30) NULL
)
GO

-- Fails due to not null constraint
INSERT [PUBS].[PUBLISHERS] VALUES (NULL, 'New Moon Books', 'Boston', 'MA', 'USA')

-- Add five records
INSERT [PUBS].[PUBLISHERS] VALUES ('0736', NULL, NULL, NULL, NULL)
INSERT [PUBS].[PUBLISHERS] VALUES ('0877', 'Binnet & Hardley', 'Washington', 'DC', 'USA')
INSERT [PUBS].[PUBLISHERS] VALUES ('1389', 'Algodata Infosystems', 'Berkeley', 'CA', 'USA')
INSERT [PUBS].[PUBLISHERS] VALUES ('9952', 'Scootney Books', 'New York', 'NY', 'USA')
INSERT [PUBS].[PUBLISHERS] VALUES ('1622', 'Five Lakes Publishing', 'Chicago', 'IL', 'USA')

-- Skips null row
SELECT * FROM [PUBS].[PUBLISHERS] WHERE [state] <> 'DC'

-- Shows one row
SELECT * FROM [PUBS].[PUBLISHERS] WHERE [state] = 'DC'

-- Find row with null
SELECT * FROM [PUBS].[PUBLISHERS] WHERE [state] IS NULL