HW 4

CREATE TABLE CUSTOMER( CustomerID Int NOT NULL, LastName Char(35) NOT NULL, FirstName Char(25) NOT NULL, Phone Char(12) NULL, EmailAddress VarChar(100) NULL, CONSTRAINT CUSTOMER_PK PRIMARY KEY(CustomerID) );

CREATE TABLE ITEM( ItemNumber Int NOT NULL, ItemDescription VarChar(255) NOT NULL, Cost Numeric NOT NULL, ArtistLastName Char(35) NULL, ArtistFirstName Char(25) NULL, CONSTRAINT ITEM_PK PRIMARY KEY(ItemNumber) );

CREATE TABLE PURCHASE( InvoiceNumber Int NOT NULL, InvoiceDate DateTime NOT NULL, PreTaxAmount Numeric NOT NULL, CustomerID Int NOT NULL, CONSTRAINT PURCHASE_PK PRIMARY KEY(InvoiceNumber), CONSTRAINT PURCHASE_CUSTOMER_FK FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID) );

CREATE TABLE PURCHASE_ITEM ( InvoiceNumber Int NOT NULL, InvoiceLineNumber Int NOT NULL, ItemNumber Int NOT NULL, RetailPrice Numeric NOT NULL, CONSTRAINT PURCHASE_ITEM_PK PRIMARY KEY (InvoiceNumber, InvoiceLineNumber), CONSTRAINT PURCHASE_ITEM_PURCHASE_FK FOREIGN KEY (InvoiceNumber) REFERENCES PURCHASE(InvoiceNumber), CONSTRAINT PURCHASE_ITEM_ITEM_FK FOREIGN KEY (ItemNumber) REFERENCES ITEM(ItemNumber) );

SELECT * FROM viewCustomerPurchase ORDER BY LastName, FirstName;

SELECT * FROM viewPurchaseItemItem ORDER BY InvoiceNumber, ItemNumber;

SELECT InvoiceNumber, SUM(Cost) AS TotalItemCost, SUM(RetailPrice) AS TotalRetailSales FROM viewPurchaseItemItem GROUP BY InvoiceNumber;

SELECT C.CustomerID, LastName, FirstName, InvoiceNumber, InvoiceDate, PreTaxAmount FROM CUSTOMER AS C INNER JOIN PURCHASE AS P ON C.CustomerID=P.CustomerID;

SELECT P.InvoiceNumber, PI.ItemNumber, ArtistLastName, ArtistFirstName, ItemDescription, Cost, RetailPrice FROM (PURCHASE AS P INNER JOIN PURCHASE_ITEM AS PI ON P.InvoiceNumber=PI.InvoiceNumber) INNER JOIN ITEM AS I ON PI.ItemNumber=I.ItemNumber;

ALTER TABLE CUSTOMER ADD ReferredBy Int NULL;

ALTER TABLE CUSTOMER ADD CONSTRAINT CUST_FK_CUST FOREIGN KEY(ReferredBy) REFERENCES CUSTOMER (CustomerID);

UPDATE CUSTOMER SET ReferredBy = 1 WHERE CustomerID = 2;

UPDATE CUSTOMER SET ReferredBy = 2 WHERE CustomerID = 4;

UPDATE CUSTOMER SET ReferredBy = 3 WHERE CustomerID = 5;

ALTER TABLE ITEM ADD ArtistHasBeenPaid Varchar(25) NULL;

UPDATE ITEM SET ArtistHasBeenPaid = 'Paid';

UPDATE ITEM SET ArtistHasBeenPaid = 'In process' WHERE ItemNumber IN (11,13,14,16);

UPDATE ITEM SET ArtistHasBeenPaid = 'Waiting for invoice' WHERE ItemNumber IN (15,17,18,19);

ALTER TABLE ITEM ALTER COLUMN ArtistHasBeenPaid Varchar(25) NOT NULL;

SELECT C.FirstName AS CustomerFirstName, C.LastName AS CustomerLastName, R.FirstName AS ReferrerFirstName, R.LastName AS ReferrerLastName FROM CUSTOMER AS C INNER JOIN CUSTOMER AS R ON C.ReferredBy = R.CustomerID;

SELECT C.FirstName AS CustomerFirstName, C.LastName AS CustomerLastName, R.FirstName AS ReferrerFirstName, R.LastName AS ReferrerLastName FROM CUSTOMER AS C LEFT JOIN CUSTOMER AS R ON C.ReferredBy = R.CustomerID;

SELECT C1.CustomerID, C1.FirstName, C1.LastName FROM CUSTOMER AS C1 WHERE EXISTS (SELECT * FROM CUSTOMER C2 WHERE C1.FirstName = C2.FirstName AND C1.LastName = C2.LastName AND C1.CustomerID <> C2.CustomerID);

ALTER TABLE APPRAISAL ADD CONSTRAINT APPRAISAL_PK PRIMARY KEY (ItemNumber, AppraisalDate);

ALTER TABLE APPRAISAL ADD CONSTRAINT APPR_ITEM_FK FOREIGN KEY(ItemNumber) REFERENCES ITEM(ItemNumber);

?½??ļ???/April 23 James River.accdb

ItemNumber	Appraiser	AppraisalDate	Value	Comments
1	Ralph Isaacson	3/17/17	¤ 200.00	Underpriced
5	Donna Jackson	4/22/16	¤ 175.00	Exquisite workmanship
9	James Johnson	9/1/15	¤ 180.00	Unique piece
13	James Johnson	9/2/15	¤ 220.00	Not very exciting
13	Ralph Isaacson	3/17/17	¤ 300.00
18	Donna Jackson	11/3/16	¤ 210.00

CustomerID	LastName	FirstName	Phone	EmailAddress
1	Stanley	Elizabeth	555-236-7789	[email protected]
2	Price	Fred	555-236-0091	[email protected]
3	Becky	Linda	555-236-0392	[email protected]
4	Birch	Pamela	555-236-4493	[email protected]
5	Romez	Ricardo	555-236-3334	[email protected]
6	Jackson	Samantha	555-236-1095	[email protected]

ItemNumber	ItemDescription	Cost	ArtistLastName	ArtistFirstName
1	Gold Bracelet	120.0	Josephson	Mary
2	Gold Necklace	160.0	Baker	Samantha
3	Bead Earrings	50.0	Josephson	Mary
4	Gold Bracelet	180.0	Baker	Samantha
5	Silver Necklace	135.0	Baxter	Sam
6	Bead Earrings	25.0	Josephson	Mary
7	Bead Earrings	22.5	Josephson	Mary
8	Gold Earrings	50.0	Lintz	John
9	Gold Necklace	160.0	Lintz	John
10	Bead Earrings	20.0	Josephson	Mary
11	Bead Earrings	35.0	Josephson	Mary
12	Bead Earrings	45.0	Josephson	Mary
13	Gold Necklace	225.0	Lintz	John
14	Silver Earrings	55.0	Lintz	John
15	Gold Bracelet	200.0	Lintz	John
16	Bead Earrings	25.0	Josephson	Mary
17	Bead Earrings	45.0	Josephson	Mary
18	Gold Bracelet	210.0	Baker	Samantha
19	Silver Necklace	165.0	Baxter	Sam

InvoiceNumber	InvoiceDate	PreTaxAmount	CustomerID
1001	5/5/19	155.0	1
1002	5/7/19	203.0	2
1003	5/11/19	75.0	3
1004	5/15/19	67.0	4
1005	5/15/19	330.0	5
1006	5/16/19	25.0	1
1007	5/25/19	45.0	3
1008	6/6/19	445.0	1
1009	6/7/19	72.0	6

InvoiceNumber	InvoiceLineNumber	ItemNumber	RetailPrice
1001	1	1	155.0
1002	1	2	203.0
1003	1	3	75.0
1004	1	6	35.0
1004	2	7	32.0
1005	1	4	240.0
1005	2	8	90.0
1006	1	10	25.0
1007	1	11	45.0
1008	1	5	175.0
1008	2	9	215.0
1008	3	12	55.0
1009	1	14	72.0