only for the grade

html4466.docx

<html><head></head><body><pre style="word-wrap: break-word; white-space: pre-wrap;">--there are four errors in this script

--Create the table for customers

CREATE TABLE PetShopCustomers(

custID varchar(8),

FirstName varchar(20),

LastName varchar(50) not null,

Address varchar(30),

Email varchar(45),

primary key (customerID));

--Create an index on customer last name

CREATE INDEXING custLastName_index ON PetShopCustomers(FirstName);

--Create a table for Products

CREATE TABLE PetShopProducts(

prodID varchar(8),

Name varchar(8) not null,

Price numeric(12,2),

primary key (prodID));

--Create a purchase table

CREATE TABLE PetShopPurchase(

PurchaseID varchar(8) not null,

custID varchar(8) not null,

prodID varchar(8) not null,

AmountSpent numeric(12,2),

PurchaseDate date,

primary key (PurchaseID),

foreign key(custID) references PetShopCustomers,

foreign key(prodID) references PetShopProducts);

--Inserts data into PetShopCustomers

INSERT INTO PetShopCustomers values('1', 'Bob', 'Smith', '123 Main St', '[email protected]');

INSERT INTO PetShopCustomers values('2', 'Susan', 'Smithy', '321 Main St', '[email protected]');

--Inserts data into PetShopProducts

INSERT INTO PetShopProducts values('1', 'Dog Food', '12.99');

INSERT INTO PetShopProducts values('2', 'Cat Food', '14.99');

--Inserts data into PetShopPurchase

INSERT INTO PetShopPurchase values ('1','1', '1', '12.99', '1-1-2017');

/*Selects all customers and displays how much they have spent. Should show everyone regardless of whether

or not they have purchased anything*/

SELECT FirstName, LastName, AmountSpent

FROM PetShopCustomers natural right outer join PetShopPurchase;