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