Question

2. Examine the SQL Script for this test.  Explain the purpose of each of the stored procedures listed below.  

Test2SP1

Test2SP2

Test2SP3

Test2SP4

Test2SP5

Test2SP6

DATABASE CODE:
if db_id('Test2PremierProducts') is not null
begin
   use master
   alter database Test2PremierProducts set SINGLE_USER with rollback immediate
   drop database Test2PremierProducts
end

create database Test2PremierProducts
go

USE Test2PremierProducts;
go

/* create the tables */

CREATE TABLE Rep
(RepNum int Identity(10,10),
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

/* 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('New Customer','2837 New Green','Fillmore','FL','33336',6550.00,7500.00,@KaiserNum);

INSERT INTO Customer (CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum)
VALUES('Another New Cust','1 New St','Fillmore','FL','33336',6550.00,7500.00,@KaiserNum);

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
go




create procedure Test2SP1
( @fullname varchar(50),
  @CommissionRate float,
  @RepID int output
)
as
declare @spaceLoc integer;
select @spaceLoc = charindex(' ', @fullname);

insert into Rep(FirstName, LastName, Rate)
values (substring(@fullname, 1, @spaceLoc), 
        substring(@fullname, @spaceLoc, len(@fullname)-@spaceLoc), 
        @CommissionRate)

select @RepID = @@IDENTITY

select count(*) as NumReps
from Rep 
go


create procedure Test2SP2
( @customername varchar(50)
)
as 
select count(*)
from customer, orders
where customer.CustomerNum = orders.CustomerNum and
      CustomerName = @customername
go


create procedure Test2SP3
as
select CustomerNum, count(*)
from orders
group by CustomerNum
go


create procedure Test2SP4
as
select Customer.CustomerNum, CustomerName, Balance , count(ordernum)
from customer, orders
where customer.CustomerNum = orders.CustomerNum
group by customer.CustomerNum, CustomerName, Balance 
go


create procedure Test2SP5
as
select Customer.CustomerNum, CustomerName, Balance , count(ordernum)
from customer left outer join orders on customer.CustomerNum = orders.CustomerNum
group by customer.CustomerNum, CustomerName, Balance 
go


create procedure Test2SP6
as
select Customer.CustomerNum, CustomerName, Balance , OrderNum, billed 
from customer left outer join orders on customer.CustomerNum = orders.CustomerNum

go

Answer 1

Test2SP1

It takes the full name as the parameter and splits the enetered full name into the first name and last name based on the location of space character present in full name.
Then it insertsit into the Rep table alongwith entered comission rate and id.

Test2SP2

It returns the total orders made by the customer name which was passed as a parameter.

Test2SP3

It returns the total orders made by each Customerid.

Test2SP4

It returns the combination of Customer Number, Customer Name, Balance who have placed orders along with the count of orders.

Test2SP5

It returns the combination of Customer Number, Customer Name, Balance irrespective of whether customer has placed an order or not as a left outer join is used. The counts are displayed for the orders which is 0 incase no order was placed by the customer.

Test2SP6

It returns the CustomerNum, CustomerName, Balance , OrderNum, billed details fo the customers to reflect whether a customer has placed an order or not. If yes then he/she has been billed for the corresponding order number or not.