if db_id('TestPremierProducts') is not null begin use master alter database TestPremierProducts set SINGLE_USER with rollback immediate drop database TestPremierProducts end create database TestPremierProducts go USE TestPremierProducts; go /* create the tables */ CREATE TABLE Rep (RepNum int Identity(1,1), LastName VARCHAR(15), FirstName VARCHAR(15), Street VARCHAR(15), City VARCHAR(15), State VARCHAR(2), Zip VARCHAR(5), Commission MONEY, Rate DECIMAL(3,2), PRIMARY KEY (RepNum)); go CREATE TABLE Customer (CustomerNum int Identity(1,1) PRIMARY KEY, CustomerName VARCHAR(35) NOT NULL, Street VARCHAR(15), City VARCHAR(15), State VARCHAR(2), Zip VARCHAR(5), Balance MONEY, CreditLimit MONEY, RepNum INT, FOREIGN KEY (RepNum) REFERENCES Rep) ; go CREATE TABLE Orders (OrderNum int Identity(10000,1) PRIMARY KEY, OrderDate DATETIME, CustomerNum INT references Customer, Billed BIT) ; go CREATE TABLE Part (PartNum int Identity(100,5) PRIMARY KEY, Description VARCHAR(15), OnHand INT, Class VARCHAR(2), Warehouse VARCHAR(1), Price MONEY ) ; go CREATE TABLE OrderLine (OrderNum INT references Orders, PartNum INT references Part, NumOrdered INT, NumShipped INT, QuotedPrice MONEY, PRIMARY KEY (OrderNum, PartNum) ) ; go /* Populate the database with initial data */ INSERT INTO Rep (LastName, FirstName, Street, City, State, Zip, Commission, Rate) VALUES ('DaMan','Foncey','121 Palindrome','TacocaT','LA','10101',5202.50,0.11); Declare @FonceyNum int select @FonceyNum = @@Identity INSERT INTO Rep (LastName, FirstName, Street, City, State, Zip, Commission, Rate) VALUES ('Kaiser','Valerie','624 Randall','Grove','FL','33321',20542.50,0.05); Declare @KaiserNum int select @KaiserNum = @@Identity INSERT INTO Rep (LastName, FirstName, Street, City, State, Zip, Commission, Rate) VALUES ('Hull','Richard','532 Jackson','Sheldon','FL','33553',39216.00,0.07); Declare @HullNum int select @HullNum = @@Identity INSERT INTO Rep (LastName, FirstName, Street, City, State, Zip, Commission, Rate) VALUES ('Perez','Juan','1626 Taylor','Fillmore','FL','33336',23487.00,0.05); Declare @PerezNum int select @PerezNum = @@Identity INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum) VALUES('Al''s Appliance and Sport','2837 Greenway','Fillmore','FL','33336',6550.00,7500.00,@KaiserNum); Declare @AlApplNum int select @AlApplNum = @@Identity INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum) VALUES('Brookings Direct','3827 Devon','Grove','FL','33321',431.50,10000.00,@HullNum); Declare @BrookingsNum int select @BrookingsNum = @@Identity INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum) VALUES('Ferguson''s','382 Wildwood','Northfield','FL','33146',5785.00,7500.00,@PerezNum); Declare @FergusonNum int select @FergusonNum = @@Identity INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum) VALUES('The Everything Shop','1828 Raven','Crystal','FL','33503',5285.25,5000.00,@HullNum); Declare @EverythingNum int select @EverythingNum = @@Identity INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum) VALUES('Bargains Galore','3829 Central','Grove','FL','33321',3412.00,10000.00,@PerezNum); Declare @BargainsNum int select @BargainsNum = @@Identity INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum) VALUES('Kline''s','838 Ridgeland','Fillmore','FL','33336',12762.00,15000.00,@KaiserNum); Declare @KlinesNum int select @KlinesNum = @@Identity INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum) VALUES('Johnson''s Department Store','372 Oxford','Sheldon','FL','33553',2106.00,10000.00,@PerezNum); Declare @JohnsonNum int select @JohnsonNum = @@Identity INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum) VALUES('Lee''s Sport and Appliance','282 Evergreen','Altonville','FL','32543',2851.00,5000.00,@HullNum); Declare @LeesNum int select @LeesNum = @@Identity INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum) VALUES('Deerfield''s Four Seasons','282 Columbia','Sheldon','FL','33553',248.00,7500.00,@HullNum); Declare @DeerfieldsNum int select @DeerfieldsNum = @@Identity INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum) VALUES('All Season','28 Lakeview','Grove','FL','33321',8221.00,7500.00,@KaiserNum); Declare @AllSeasonNum int select @AllSeasonNum = @@Identity INSERT INTO ORDERS (OrderDate, CustomerNum, Billed) VALUES('20-OCT-2003',@AlApplNum, 1); Declare @O1Num int select @O1Num = @@Identity INSERT INTO ORDERS (OrderDate, CustomerNum, Billed) VALUES('20-OCT-2003',@FergusonNum, 1); Declare @O2Num int select @O2Num = @@Identity INSERT INTO ORDERS (OrderDate, CustomerNum, Billed) VALUES('21-OCT-2003',@EverythingNum, 0); Declare @O3Num int select @O3Num = @@Identity INSERT INTO ORDERS (OrderDate, CustomerNum, Billed) VALUES('21-OCT-2003',@BrookingsNum, 1); Declare @O4Num int select @O4Num = @@Identity INSERT INTO ORDERS (OrderDate, CustomerNum, Billed) VALUES('23-OCT-2003',@JohnsonNum, 0); Declare @O5Num int select @O5Num = @@Identity INSERT INTO ORDERS (OrderDate, CustomerNum, Billed) VALUES('23-OCT-2003',@AlApplNum, 0); Declare @O6Num int select @O6Num = @@Identity INSERT INTO ORDERS (OrderDate, CustomerNum, Billed) VALUES('23-OCT-2003',@JohnsonNum, 0); Declare @O7Num int select @O7Num = @@Identity INSERT INTO PART (Description, OnHand, Class, Warehouse, Price) VALUES('Iron',50,'HW','3',24.95); Declare @P1Num int select @P1Num = @@Identity INSERT INTO PART VALUES('Home Gym',45,'SG','2',794.95); Declare @P2Num int select @P2Num = @@Identity INSERT INTO PART VALUES('Microwave Oven',32,'AP','1',165.00); Declare @P3Num int select @P3Num = @@Identity INSERT INTO PART VALUES('Cordless Drill',21,'HW','3',129.95); Declare @P4Num int select @P4Num = @@Identity INSERT INTO PART VALUES('Gas Range',8,'AP','2',495.00); Declare @P5Num int select @P5Num = @@Identity INSERT INTO PART VALUES('Washer',12,'AP','3',399.99); Declare @P6Num int select @P6Num = @@Identity INSERT INTO PART VALUES('Stand Mixer',22,'HW','3',159.95); Declare @P7Num int select @P7Num = @@Identity INSERT INTO PART VALUES('Dryer',12,'AP','1',349.95); Declare @P8Num int select @P8Num = @@Identity INSERT INTO PART VALUES('Dishwasher',8,'AP','3',595.00); Declare @P9Num int select @P9Num = @@Identity INSERT INTO PART VALUES('Treadmill',9,'SG','2',1390.00); Declare @P10Num int select @P10Num = @@Identity INSERT INTO OrderLine VALUES(@O1Num,@P1Num,13,10,21.95); INSERT INTO OrderLine VALUES(@O2Num,@P5Num,2,2,495.00); INSERT INTO OrderLine VALUES(@O2Num,@P6Num,2,2,399.99); INSERT INTO OrderLine VALUES(@O3Num,@P8Num,4,4,329.95); INSERT INTO OrderLine VALUES(@O4Num,@P9Num,2,2,595.00); INSERT INTO OrderLine VALUES(@O5Num,@P2Num,2,1,794.95); INSERT INTO OrderLine VALUES(@O5Num,@P3Num,4,3,150.00); INSERT INTO OrderLine VALUES(@O6Num,@P5Num,1,1,495.00); INSERT INTO OrderLine VALUES(@O7Num,@P5Num,1,1,495.00); INSERT INTO OrderLine VALUES(@O7Num,@P10Num,2,1,1290.00); go --****************************************************************************** --****************************************************************************** --****************************************************************************** --****************************************************************************** --Example code showing how to use variables in SQL Server --declare a variable named @var (variable names must begin with @ in SQL Server) declare @var int --assignment statement, calculate expression & store result in variable select @var = 100 - 80 --display what is stored in a variable for the DBA to see (in Message window) print 'Variable stores' print @var --using the variable select * from part where onhand  
 
  1. At the bottom of the SOL Script posted on Moodle for this test, fill in the body of the procedure below which is designed to add an order for a single item to the database. This procedure should add records to all necessary tables and make all necessary updates to include the order in the system. In addition to simply adding the order to the system, this procedure will subtract the quantity ordered from the OnHand field of the part table for the part being ordered (to indicate that it is no longer available for sale). Note that the OnHand field should not be allowed to be below zero. If the customer orders more than we have available, we will only be able to send them what we have on hand at the moment. Also, record a value for the NumShipped field. If we have enough on hand, we will ship all that is being requested. Otherwise, we will only ship what we have available (based on the OnHand field). create procedure AddorderItem name of the customer placing the order @custname varchar (50), @orderdate datetime, @part int, @qty int, eprice money --date associated with the order --ID of part being ordered -how many they desire --price that we quoted them as