SampleDBCreateNLoad.sql (SampleDB)

--'------------------------------------------------------------------------------------------
--'           Notice of My Copyright and Intellectual Property Rights
--'
--' Any intellectual property contained within the program by Joseph L. Bolen remains the
--' intellectual property of the Joseph L. Bolen. This means that no person may distribute,
--' publish or provide such intellectual property to any other person or entity for any
--' reason, commercial or otherwise, without the express written permission of Joseph L. Bolen.
--'
--'                 Copyright © 2015. All rights reserved.
--'        All trademarks remain the property of their respective owners.
--'-------------------------------------------------------------------------------------------
--' Name:       SampleDBCreateNLoad.sql (SampleDB)
--'
--' Author:     Joseph L. Bolen
--' Date Created:   Jul 2015
--'
--' Description:    Simple two table database.
--'
--'                 Documentation is at:

--'                   App's Visual Basic .NET code is at http://pastebin.com/ajgYQYMc
--'                   Video tutorial at YouTube: http://www.youtube.com/user/bolenpresents
--'-------------------------------------------------------------------------------------------


USE master;
GO

IF  EXISTS (
    SELECT name
        FROM sys.databases
        WHERE name = N'SampleDB'
)
DROP DATABASE SampleDB;
GO

--Modify location of database for your environment.
CREATE DATABASE SampleDB
ON (NAME=SampleDB, FILENAME='U:\Databases\SQLDatabases\SampleDB.mdf')
LOG ON (NAME=SampleDB_log, FILENAME='U:\Databases\SQLDatabases\SampleDB.ldf');
GO

USE SampleDB;
GO

CREATE TABLE Customers
(
CustomerId int NOT NULL IDENTITY(1,1),
CustomerName nvarchar(35) NOT NULL,
CustomerContact nvarchar(35),
Phone nvarchar(15),
CONSTRAINT PK_CustomerId PRIMARY KEY (CustomerId)
);
GO

CREATE TABLE Orders
(
OrderId int NOT NULL IDENTITY(1,1),
CustomerId int NOT NULL,
OrderDate date,
OrderQuantity int,
CONSTRAINT PK_OrderId PRIMARY KEY (OrderId),
CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerId)
REFERENCES Customers (CustomerId)
);
GO

USE SampleDB;
GO

INSERT INTO Customers
(
CustomerName
,CustomerContact
,Phone
)
VALUES
('Widget Company',     'George Sprocket',   '555-555-1212'),
('Gadget Enterprise',  'Mike Griffin',      '555-555-1234'),
('Thingamajigs R''Us', 'Sally Peterson',    '555-555-9876');
GO

USE SampleDB;
GO

INSERT INTO Orders
(
CustomerId
,OrderDate
,OrderQuantity
)
VALUES
(1, '2015-07-21',   125),
(1, '2015-07-28',   200),
(1, '2015-08-04',   125),
(2, '2015-08-15',   200),
(3, '2015-07-22',   50),
(3, '2015-07-29',   75),
(3, '2015-08-05',   100);
GO