DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to apply SQL CREATE TABLE statement?

Execute the following Microsoft SQL Server T-SQL script to demonstrate the use of the CREATE TABLE command.


USE tempdb;
-- Create banking data model - SQL CREATE TABLE
-- Bank database table definitions for home equity and automobile loans  
-- SQL table definition- T-SQL primary key, foreign key constraint - PK, FK 
-- MSSQL data modelling: 1 (PK) to many(FK) relationship
CREATE TABLE Branch (
  BranchID     INT    IDENTITY ( 1 , 1 )   PRIMARY KEY,
  BranchName   CHAR(32)    NOT NULL    UNIQUE,
  BranchCity   CHAR(32)    NOT NULL CHECK ( LEN(BranchCity)>0),
  Assets       MONEY    NOT NULL,
  ModifiedDate DATETIME    DEFAULT (getdate()));
 
CREATE TABLE ACCOUNT (
  AccountID     INT    IDENTITY ( 1 , 1 )   PRIMARY KEY,
  BranchID      INT    NOT NULL references Branch(BranchID),
  AccountNumber CHAR(20)    NOT NULL    UNIQUE,
  AccountType   CHAR(12)    NOT NULL,
  Balance       MONEY    NOT NULL,
  ModifiedDate  DATETIME    DEFAULT (getdate()));
 
CREATE TABLE Customer (
  CustomerID   INT    IDENTITY ( 1 , 1 )   PRIMARY KEY,
  Customer     CHAR(64)    NOT NULL    UNIQUE,
  [Type]       CHAR(20)    NOT NULL,
  Street       VARCHAR(32)    NOT NULL CHECK (Street !=''),
  City         CHAR(32)    NOT NULL,
  [State]      CHAR(32)    NOT NULL,
  Zip          CHAR(10)    NOT NULL,
  Country      CHAR(32)    NOT NULL,
  ModifiedDate DATETIME    DEFAULT (getdate()));
 
CREATE TABLE Loan (
  LoanID       INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,
  LoanNumber   CHAR(20)    NOT NULL    UNIQUE,
  LoanType     VARCHAR(30)    NOT NULL,
  BranchName   CHAR(32)    NOT NULL,
  Amount       MONEY    NOT NULL,
  ModifiedDate DATETIME    DEFAULT (getdate()));
 
-- SQL junction table - many to many relationship
-- Composite PRIMARY KEY
CREATE TABLE Depositor (
  CustomerID   INT    NOT NULL references Customer(CustomerID),
  AccountID    INT    NOT NULL  references Account(AccountID),
  ModifiedDate DATETIME    DEFAULT (getdate()),
     PRIMARY KEY ( CustomerID,AccountID ));
 
CREATE TABLE Borrower (
  CustomerID   INT    NOT NULL references Customer(CustomerID),
  LoanID       INT    NOT NULL references Loan(LoanID),
  ModifiedDate DATETIME    DEFAULT (getdate()),
     PRIMARY KEY ( CustomerID,LoanID ));
 
 
CREATE TABLE [Transaction] (
  TransactionID INT    IDENTITY ( 1 , 1 ) PRIMARY KEY,
  AccountID     INT references Account(AccountID),
  TranType      CHAR(1),
  Amount        MONEY,
  ModifiedDate  DATETIME    DEFAULT (getdate()));
GO
 
-- Population script
INSERT INTO [dbo].[Branch]
           ([BranchName],
            [BranchCity],
            [Assets])
VALUES     ('Long Beach Main St','Los Angeles',50000000)
 
GO
 
SELECT *
FROM   Branch
GO
 
/*
 
BranchID BranchName BranchCity Assets ModifiedDate
1 Long Beach Main St               Los Angeles                      50000000 3/18/09 6:30
 
*/
 
INSERT INTO [dbo].[Account]
           ([BranchID],
            [AccountNumber],
            [AccountType],
            [Balance])
VALUES     (1,'1000030045235','PERSONAL',6000.0)
GO
SELECT *
FROM   ACCOUNT
GO
/*
 
AccountID BranchID AccountNumber AccountType Balance ModifiedDate
1 1 1000030045235 PERSONAL     6/4/16 0:00 3/18/09 6:30
 
*/
 
 
INSERT INTO [dbo].[Customer]
           ([Customer],
            [Type],
            [Street],
            [City],
            [State],
            [Zip],
            [Country])
VALUES     ('Robert Bullmore','Business Proprietor','113 Broadway',
             'New York City','New York','10001','USA')
 
GO
 
SELECT *
FROM   Customer
GO
 
/*
 
CustomerID Customer Type Street City State Zip Country ModifiedDate
1 Robert Bullmore                  Business Proprietor  113 Broadway New York City                    New York                         10001 USA                              3/18/09 6:30
 
*/
 
-- Cleanup - FOREIGN KEY tables first
DROP TABLE Borrower
DROP TABLE Depositor
DROP TABLE Loan
DROP TABLE Customer
DROP TABLE [Transaction]
DROP TABLE ACCOUNT
DROP TABLE Branch

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE