Sample Database For Demos

Sample database tables are created for the purpose of SQL tutorial’s demonstration at this site. Create the database, tables and populate data in these tables before you learn SQL Server with practical examples.

USE HRDB
GO
CREATE TABLE tblDepartment
(
    DeptID INT PRIMARY KEY NOT NULL,
    DeptName VARCHAR(50)
)
GO
USE HRDB
GO
CREATE TABLE tblGender
(
    GenderID INT PRIMARY KEY NOT NULL,
    Gender VARCHAR(50)
)
GO
USE HRDB
GO
CREATE TABLE tblEmployee
(
    EmpID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    EmpFirstName VARCHAR(20) NOT NULL,
    EmpLastName	VARCHAR(20) NULL,
    EmpUserName VARCHAR(20) UNIQUE NOT NULL,
    EmpGenderID INT FOREIGN KEY REFERENCES tblGender(GenderID),
    EmpDeptID INT FOREIGN KEY REFERENCES tblDepartment(DeptID),
    EmpAge INT CONSTRAINT CHK_tblEmployee_EmpAge CHECK(EmpAge>=18),
    EmpSalary INT NULL,
    EmpJoiningDate DATETIME CONSTRAINT DF_tblEmployee_EmpJoiningDate DEFAULT GETDATE()
)
GO

Let’s insert data in tblDepartment, tblGender and tblEmployee tables.

USE HRDB
GO
INSERT INTO tblDepartment
VALUES(1,'HR')
INSERT INTO tblDepartment
VALUES(2,'Research and Development')
INSERT INTO tblDepartment
VALUES(3,'Purchase')
INSERT INTO tblDepartment
VALUES(4,'Sale')
USE HRDB
GO
INSERT INTO tblGender
VALUES
    (1,'Male'),
    (2,'Female')
GO
USE HRDB
GO
INSERT INTO tblEmployee
    (EmpFirstName, EmpLastName, EmpUserName, EmpGenderID, EmpDeptID, EmpAge, EmpSalary, EmpJoiningDate)
VALUES
    ('Davio','Nancy','Davio Nancy',1,1,25,50000,'2010-01-01'),
    ('Fuller','Andrew','Fuller Andrew',1,1,20,40000,'2010-06-01'),
    ('Leverling','Janet','Leverling Janet',1,2,35,50000,'2010-07-01'),
    ('King','Robert','King Robert',1,3,22,25000,'2010-01-01'),
    ('Ali','Hassan','Ali Hassan',1,1,30,75000,'2010-08-01'),
    ('Zahid','Ali','Zahid Ali',1,2,25,50000,'2011-01-01'),
    ('Sara','Ali','Sara Ali',2,3,25,50000,'2011-09-01'),
    ('Suman','Shah','Suman Shah',2,1,20,30000,'2011-12-01'),
    ('Anne','Andrew','Anne Andrew',2,3,33,35000,'2012-01-01'),
    ('Ammara','Khan','Ammara Khan',2,2,25,50000,'2013-01-01')
GO