DROP DATABASE newdb
GO

CREATE DATABASE newdb
GO

USE newdb
GO

CREATE TABLE toptab(id INT identity, name VARCHAR(10))
GO

INSERT INTO toptab VALUES('bob1')
INSERT INTO toptab VALUES('bob2')
INSERT INTO toptab VALUES('bob3')
INSERT INTO toptab VALUES('bob4')
INSERT INTO toptab VALUES('bob5')
GO

CREATE TABLE categories(
 catid INT primary key,
 parentid INT null foreign key references categories(catid),
 description VARCHAR(50)
)
GO

INSERT INTO categories VALUES(1, null, 'courses')
INSERT INTO categories VALUES(2, 1, '.NET courses')
INSERT INTO categories VALUES(3, 1, 'database courses')
INSERT INTO categories VALUES(4, 3, 'database administration courses')
INSERT INTO categories VALUES(5, 3, 'database developer courses')
INSERT INTO categories VALUES(6, 2, '.NET security courses')
INSERT INTO categories VALUES(7, 2, '.NET developer courses')
INSERT INTO categories VALUES(8, 2, '.NET INTernals courses')
GO

CREATE TABLE salespeople(
  id INT IDENTITY PRIMARY KEY,
  first_name VARCHAR(20),
  last_name  VARCHAR(20),
  territory  VARCHAR(10),
  sales_ytd  DECIMAL(9,2) DEFAULT 0,
  sales_ytd_previous DECIMAL(9,2) DEFAULT 0)
GO

INSERT INTO salespeople VALUES('Bill', 'Williams', 'East', 10000, 80000)
INSERT INTO salespeople VALUES('Mike', 'Williams', 'West', 166000, 800000)
INSERT INTO salespeople VALUES('Fred', 'Williams', 'West', 70000, 33000)
INSERT INTO salespeople VALUES('Peg', 'Bennett', 'East', 200000, 124500)
INSERT INTO salespeople VALUES('Erica', 'Oransky', 'East', 66000, 50000)
INSERT INTO salespeople VALUES('Jay', 'Smith', 'West', 200000, 33000)
GO

CREATE TABLE current_year_sales (
  sales_id INT IDENTITY PRIMARY KEY, 
  salespeople_id INT REFERENCES salespeople (id),
  salesamt DECIMAL(9,2)
)
GO


CREATE TABLE hardware_sales
(
	hardware_salesID INT IDENTITY PRIMARY KEY CLUSTERED,
	Product nVARCHAR(30),
	Quantity INT,
	Quarter INT
)
GO

-- INSERT 12 rows of data
INSERT INTO hardware_sales VALUES ('Swish', 15, 1)
INSERT INTO hardware_sales VALUES ('Swish', 5, 2)
INSERT INTO hardware_sales VALUES ('Attach-It', 36, 1)
INSERT INTO hardware_sales VALUES ('Attach-It', 32, 2)
INSERT INTO hardware_sales VALUES ('Attach-It', 41, 3)
INSERT INTO hardware_sales VALUES ('Attach-It', 36, 4)
INSERT INTO hardware_sales VALUES ('Paint', 12, 3)
INSERT INTO hardware_sales VALUES ('Paint', 35, 4)
INSERT INTO hardware_sales VALUES ('Rug', 15, 1)
INSERT INTO hardware_sales VALUES ('Rug', 28, 2)
INSERT INTO hardware_sales VALUES ('Rug', 34, 3)
INSERT INTO hardware_sales VALUES ('Rug', 40, 4)
GO

-- PARTS and ORDERS
CREATE TABLE t_parts (partNumber INT, partdesc VARCHAR(6))
GO

CREATE TABLE t_orderLineItem ([order] INT, partNumber INT)
GO

INSERT INTO t_orderLineItem VALUES (100, 333)
INSERT INTO t_orderLineItem VALUES (200, 444)
INSERT INTO t_orderLineItem VALUES (200, 333)
GO

INSERT INTO t_parts VALUES (99,'gear')
INSERT INTO t_parts VALUES (333,'wheel')
INSERT INTO t_parts VALUES (444,'bolt')
GO


  


