Sample Tables

Sample "Employee" and "Department" tables script

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))
DROP TABLE [dbo].[Employee]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]') AND type in (N'U'))
DROP TABLE [dbo].[Department]
GO

CREATE TABLE [dbo].[Department]
(DEPTNO INT PRIMARY KEY ,
DNAME VARCHAR(20),
LOC VARCHAR(20) );
GO

INSERT INTO [dbo].[Department] VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO [dbo].[Department] VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO [dbo].[Department] VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO [dbo].[Department] VALUES (40, 'OPERATIONS', 'BOSTON');


CREATE TABLE [dbo].[Employee]
(EMPNO INT PRIMARY KEY,
ENAME VARCHAR(20),
JOB VARCHAR(20),
MGR INT,
HIREDATE DATE,
SAL MONEY,
COMM MONEY,
DEPTNO INT FOREIGN KEY REFERENCES Department(DEPTNO) );
GO

INSERT INTO [dbo].[Employee] VALUES
(7369, 'SMITH', 'CLERK', 7902, '17-DEC-1980', 800, NULL, 20);
INSERT INTO [dbo].[Employee] VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30);
INSERT INTO [dbo].[Employee] VALUES
(7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981', 1250, 500, 30);
INSERT INTO [dbo].[Employee] VALUES
(7566, 'JONES', 'MANAGER', 7839, '2-APR-1981', 2975, NULL, 20);
INSERT INTO [dbo].[Employee] VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30);
INSERT INTO [dbo].[Employee] VALUES
(7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-1981', 2850, NULL, 30);
INSERT INTO [dbo].[Employee] VALUES
(7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981', 2450, NULL, 10);
INSERT INTO [dbo].[Employee] VALUES
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20);
INSERT INTO [dbo].[Employee] VALUES
(7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981', 5000, NULL, 10);
INSERT INTO [dbo].[Employee] VALUES
(7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981', 1500, 0, 30);
INSERT INTO [dbo].[Employee] VALUES
(7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, NULL, 20);
INSERT INTO [dbo].[Employee] VALUES
(7900, 'JAMES', 'CLERK', 7698, '3-DEC-1981', 950, NULL, 30);
INSERT INTO [dbo].[Employee] VALUES
(7902, 'FORD', 'ANALYST', 7566, '3-DEC-1981', 3000, NULL, 20);
INSERT INTO [dbo].[Employee] VALUES
(7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10);

BOOKS TABLE

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Books]') AND type in (N'U'))
DROP TABLE [dbo].[Books]
GO
CREATE TABLE [dbo].[Books](
[BookID] [int] IDENTITY(1001,1) NOT NULL,
[BookName] [varchar](50) NULL,
[Category] [varchar](50) NULL,
[Price] [numeric](18, 2) NULL,
[Price_Range] [varchar](20) NULL,
PRIMARY KEY CLUSTERED ( [BookID] ASC )
) ON [PRIMARY]
GO

INSERT INTO dbo.Books
(BookName, Category, Price, Price_Range)
VALUES
('Computer Architecture', 'Computers', 125.6, '100-150'),
('Advanced Composite Materials', 'Science', 172.56, '150-200'),
('Asp.Net 4 Blue Book', 'Programming', 56.00, '50-100'),
('Stategies Unplugged', 'Science', 99.99, '50-100'),
('Teaching Science', 'Science', 164.10, '150-200'),
('Challenging Times', 'Business', 150.70, '150-200'),
('Circuit Bending', 'Science', 112.00, '100-150'),
('Popular Science', 'Science', 210.40, '200-250'),
('ADOBE Premiere', 'Computers', 62.20, '50-100')