SQL Server – Sample Schema – Tables – For Practice

Beginners can practice creating new tables, joining them together, using sub-queries, etc. with the schema below. I hope this will be useful to you as well.

To see the whole code, scroll down. Simply copy, paste, and run it in your GUI.

Here is the sample schema.

Information about departments is stored in table “Dept.”
The many branches and territories of the company are stored in the table “Locations.”
The employees’ data from all departments across all areas are stored in table “Emp.”
The joiner and lookup table is “DeptLocations.”

Here is the complete SQL Script. Please correct the single quotes once copied into SSMS.

DROP TABLE IF EXISTS Locaations
DROP TABLE IF EXISTS Emp
DROP TABLE IF EXISTS Dept

— Table definition – Location
CREATE TABLE Locations(
LocationId INT PRIMARY KEY,
LocationName VARCHAR(20));

INSERT INTO Locations VALUES(1010, ‘NEW YORK’);
INSERT INTO Locations VALUES(1020, ‘DALLAS’);
INSERT INTO Locations VALUES(1030, ‘CHICAGO’);
INSERT INTO Locations VALUES(1040, ‘BOSTON’);

— Table definition – Dept
CREATE TABLE Dept(
DeptNo INT,
DName VARCHAR(14),
CONSTRAINT pk_Dept PRIMARY KEY (DeptNo));

INSERT INTO Dept VALUES(10, ‘ACCOUNTING’);
INSERT INTO Dept VALUES(20, ‘RESEARCH’);
INSERT INTO Dept VALUES(30, ‘SALES’);
INSERT INTO Dept VALUES(40, ‘OPERATIONS’);

— Table definition – Dept
CREATE TABLE DeptLocation(
DeptLocationId INT PRIMARY KEY,
DeptNo INT,
LocationId INT,
CONSTRAINT fk_DeptNo FOREIGN KEY (DeptNo) REFERENCES Dept (DeptNo),
CONSTRAINT fk_LocationId FOREIGN KEY (LocationId) REFERENCES Locations(LocationId));

INSERT INTO DeptLocation VALUES(1, 10, 1010);
INSERT INTO DeptLocation VALUES(2, 10, 1020);
INSERT INTO DeptLocation VALUES(3, 20, 1020);
INSERT INTO DeptLocation VALUES(4, 20, 1030);
INSERT INTO DeptLocation VALUES(5, 30, 1010);
INSERT INTO DeptLocation VALUES(6, 30, 1030);
INSERT INTO DeptLocation VALUES(7, 40, 1040);

— Tabe Definition – Emp:
CREATE TABLE Emp(
EmpNo INT,
EName VARCHAR(10),
Job VARCHAR(9),
Mgr INT,
HireDate date,
Sal INT,
Comm INT,
DeptLocationId INT,
CONSTRAINT pk_Emp PRIMARY KEY (EmpNo),
CONSTRAINT fk_DeptLocationId FOREIGN KEY (DeptLocationId) REFERENCES DeptLocation(DeptLocationId));

— Emp Table data:
INSERT INTO Emp VALUES( 7839, ‘KING’, ‘PRESIDENT’, NULL, ‘1981-11-17’, 5000, NULL, 1);
INSERT INTO Emp VALUES( 7698, ‘BLAKE’, ‘MANAGER’, 7839, ‘1981-05-01’, 2850, NULL, 3 );
INSERT INTO Emp VALUES( 7782, ‘CLARK’, ‘MANAGER’, 7839, ‘1981-06-09’, 2450, NULL, 1 );
INSERT INTO Emp VALUES( 7566, ‘JONES’, ‘MANAGER’, 7839, ‘1981-04-02’, 2975, NULL, 3 );
INSERT INTO Emp VALUES( 7788, ‘SCOTT’, ‘ANALYST’, 7566, ‘1987-07-13’, 3000, NULL, 3 );
INSERT INTO Emp VALUES( 7902, ‘FORD’, ‘ANALYST’, 7566, ‘1981-12-03’, 3000, NULL, 3 );
INSERT INTO Emp VALUES( 7369, ‘SMITH’, ‘CLERK’, 7902, ‘1980-12-17’, 800, NULL, 4 );
INSERT INTO Emp VALUES( 7499, ‘ALLEN’, ‘SALESMAN’, 7698, ‘1981-02-20’, 1600, 300, 6 );
INSERT INTO Emp VALUES( 7521, ‘WARD’, ‘SALESMAN’, 7698, ‘1981-02-22’, 1250, 500, 6 );
INSERT INTO Emp VALUES( 7654, ‘MARTIN’, ‘SALESMAN’, 7698, ‘1981-09-28’, 1250, 1400, 6 );
INSERT INTO Emp VALUES( 7844, ‘TURNER’, ‘SALESMAN’, 7698, ‘1981-09-08’, 1500, 0, 6 );
INSERT INTO Emp VALUES( 7876, ‘ADAMS’, ‘CLERK’, 7788, ‘1987-07-13’, 1100, NULL, 3 );
INSERT INTO Emp VALUES( 7900, ‘JAMES’, ‘CLERK’, 7698, ‘1981-12-03’, 950, NULL, 1 );
INSERT INTO Emp VALUES( 7934, ‘MILLER’, ‘CLERK’, 7782, ‘1982-01-23’, 1300, NULL, 1 );

INSERT INTO Emp VALUES( 7666, ‘MIKE’, ‘MANAGER’, 7839, ‘1981-04-02’, 2975, NULL, 2 );
INSERT INTO Emp VALUES( 7688, ‘ALICE’, ‘ANALYST’, 7566, ‘1987-07-13’, 3000, NULL, 4 );
INSERT INTO Emp VALUES( 7966, ‘PAUL’, ‘MANAGER’, 7839, ‘1981-04-02’, 2975, NULL, 2 );

INSERT INTO Emp VALUES( 7988, ‘STACY’, ‘ANALYST’, 7566, ‘1987-07-13’, 3000, NULL, 5 );
INSERT INTO Emp VALUES( 7166, ‘JOSHUA’, ‘MANAGER’, 7839, ‘1981-04-02’, 2975, NULL, 5 );
INSERT INTO Emp VALUES( 7188, ‘NICK’, ‘ANALYST’, 7566, ‘1987-07-13’, 3000, NULL, 3 );

SELECT Ename, Job, Sal, DName, LocationName FROM Emp E
JOIN DeptLocation DL ON DL.DeptLocationId = E.DeptLocationId
JOIN Dept D ON D.DeptNo = DL.DeptNo
JOIN Locations L ON L.LocationId = DL.LocationId