Serving Information Simply

Monday 23 July 2012

Example Database


Introduction
This is a simple Microsoft SQL Server database. The code has the ability to check the existence of the database. Besides creating the database, this code creates a table named Employees. The table contains a few columns, including a primary key.
After the table has been created, a few records are added to it.
-- =============================================
-- Database: Exercise
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
 SELECT name 
  FROM sys.databases 
  WHERE name = N'Exercise'
)
DROP DATABASE Exercise
GO
CREATE DATABASE Exercise
GO
-- =========================================
-- Table: Employees
-- =========================================
USE Exercise
GO

IF OBJECT_ID('Employees', 'U') IS NOT NULL
  DROP TABLE Employees
GO

CREATE TABLE dbo.Employees
(
    EmployeeID int Unique Identity(1,1) NOT NULL, 
    EmployeeNumber integer NOT NULL, 
    FirstName varchar(50) NULL,
    LastName varchar(50) NOT NULL,
    HourlySalary decimal(6, 2) 
    CONSTRAINT PKEmployees PRIMARY KEY (EmployeeID)
)
GO

INSERT INTO Employees(EmployeeNumber, FirstName, 
        LastName, HourlySalary)
VALUES(92935, 'Joan', 'Hamilton', 22.50)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
        LastName, HourlySalary)
VALUES(22940, 'Peter', 'Malley', 14.25)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
        LastName, HourlySalary)
VALUES(27495, 'Christine', 'Fink', 32.05)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
        LastName, HourlySalary)
VALUES(50026, 'Leonie', 'Crants', 18.75)
GO

1 comment: