/******************************************************
 *
 * Name:         basic-training-tables.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:    Ways to alter, create & drop tables.
 *
 ******************************************************/


--
-- Create training database
--

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

-- Delete existing databases.
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TRAINING')
    DROP DATABASE [TRAINING]
GO

-- Add new databases.
CREATE DATABASE [TRAINING] ON
PRIMARY
    ( NAME = N'TRAINING_DAT', FILENAME = N'C:\MSSQL\DATA\TRAINING.MDF' ,
    SIZE = 32MB , MAXSIZE = UNLIMITED, FILEGROWTH = 4MB)
LOG ON
    ( NAME = N'TRAINING_LOG', FILENAME = N'C:\MSSQL\LOG\TRAINING.LDF' ,
    SIZE = 8MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2MB );
GO 


--
-- Create pubs schema
--

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

-- Delete existing schema.
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'PUBS')
DROP SCHEMA [PUBS]
GO

-- Add new schema.
CREATE SCHEMA [PUBS] AUTHORIZATION [dbo]
GO


--
-- Create authors table 
--

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

-- Create new table
CREATE TABLE [PUBS].[AUTHORS]
(
   au_id varchar(11) not null,
   au_lname varchar(40),
   au_fname varchar(20),
   phone char(12),
   address varchar(40),
   city varchar(20),
   state char(2),
   zip char(5),
   contract bit
);

-- Alter the authors table
ALTER TABLE [PUBS].[AUTHORS] 
   ADD CONSTRAINT PK_AUTHORS_ID PRIMARY KEY CLUSTERED (au_id);


-- Add five records
INSERT INTO  [PUBS].[AUTHORS]
   VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',
   '6223 Bateman St.', 'Berkeley', 'CA', '94705', 1);

INSERT INTO  [PUBS].[AUTHORS]
   VALUES('213-46-8915', 'Green', 'Marjorie', '415 986-7020',
   '309 63rd St. #411', 'Oakland', 'CA', '94618', 1);

INSERT INTO  [PUBS].[AUTHORS]
   VALUES('238-95-7766', 'Carson', 'Cheryl', '415 548-7723',
   '589 Darwin Ln.', 'Berkeley', 'CA', '94705', 1);

INSERT INTO  [PUBS].[AUTHORS]
   VALUES('998-72-3567', 'Ringer', 'Albert', '801 826-0752',
   '67 Seventh Av.', 'Salt Lake City', 'UT', '84152', 1);

INSERT INTO  [PUBS].[AUTHORS]
   VALUES('899-46-2035', 'Ringer', 'Anne', '801 826-0752',
   '67 Seventh Av.', 'Salt Lake City', 'UT', '84152', 1);


-- Create new table using SELECT INTO
SELECT    
   au_id as author_id,
   au_lname AS last_name,
   au_fname AS first_name
INTO [PUBS].[NAMES] 
FROM [PUBS].[AUTHORS];