James River Jewelry is a small jewelry shop. While James River Jewelry does sell
ID: 3904200 • Letter: J
Question
James River Jewelry is a small jewelry shop. While James River Jewelry does sell typical jewelry purchased form jewelry vendors, including such items as rings, necklaces, earrings, and watches, it specializes in hard-to-find Asian jewelry. Although some Asian jewelry is manufactured jewelry purchased from vendors in the same manner as the standard jewelry is obtained, many of the Asian jewelry pieces are often unique single items purchased directly from the artisan who created the piece (the term “manufactured” would be an inappropriate description of these pieces). James River Jewelry has a small but loyal clientele, and it wants to further increase customer loyalty by creating a frequent buyer program. In this program, after every 10 purchases, a customer will receive a credit equal to 50 percent of the average of his or her 10 most recent purchases. This credit must be applied to the next (or 11th) purchase.
Assume that James River designs a database with the following tables.
CUSTOMER (CustomerID, LastName, FirstName, Phone, EmailAddress)
PURCHASE (InvoiceNumber, InvoiceDate, PreTaxAmount, CustomerID)
PURCHASE_ITEM (InvoiceNumber, InvoiceLineNumber, ItemNumber,
RetailPrice)
ITEM (ItemNumber, ItemDescription, Cost, ArtistLastName,
ArtistFirstName)
The referential integrity constraints are:
CustomerID in PURCHASE must exist in CustomerID in CUSTOMER
InvoiceNumber in PURCHASE_ITEM must exist in InvoiceNumber in PURCHASE
ItemNumber in PURCHASE_ITEM must exist in ItemNumber in ITEM
Assume that CustomerID of CUSTOMER, ItemNumber of ITEM, and InvoiceNumber of PURCHASE are all surrogate keys with values as follows:
CustomerID Start at 1 Increment by 1
InvoiceNumber Start at 1001 Increment by 1
ItemNumber Start at 1 Increment by 1
Use data you have generated as part of completing the other Assignments. Name your database JRJ.
Using Access Write SQL statements and answer questions for this database as follows:
{C}A. Write SQL CREATE TABLE statements for each of these tables.
{C}B. Write foreign key constraints for the relationships in each of these tables.
{C}C. Write SQL statements to insert some sample data into these tables. Assume that surrogate key column values will be supplied by the DBMS.
{C}D. Write SQL statements to list all columns for all tables.
{C}E. Write an SQL statement to list ItemNumber and ItemDescription for all items that cost more than $100.
{C}F. Write an SQL statement to list ItemNumber and ItemDescription for all items that cost more than $100 and were produced by an artist with a last name ending with the letters son.
{C}G. Write an SQL statement to list LastName and FirstName of customers who have made at least one purchase with PreTaxAmount greater than $200. Use a subquery.
{C}H. Answer part G but use a join using JOIN ON syntax.
{C}I. Write an SQL statement to list LastName and FirstName of customers who have purchased an item that costs more than $50. Use a subquery.
{C}J. Answer part I but use a join using JOIN ON syntax.
{C}K. Write an SQL statement to list LastName and FirstName of customers who have purchased an item that was created by an artist with a last name that begins with the letter J. Use a subquery.
{C}L. Answer part K but use a join using JOIN…ON syntax
{C}M. Write an SQL statement to show the Name and sum of PreTaxAmount for each customer. Use a join using JOIN ON syntax.
{C}N. Write an SQL statement to show the sum of PreTaxAmount for each artist (hint: the result will have only one line per each artist). Use a join using JOIN ON syntax, and sort the results by ArtistLastName then ArtistFirstName in ascending order. Note this should include the full PreTaxAmount for any purchase in which the artist had an item.
{C}O. Write an SQL statement to show the sum of PreTaxAmount for each artist but exclude any items that were part of purchases with PreTaxAmount over $25. Use a join using JOIN ON syntax, and sort the results by ArtistLastName and ArtistFirstName in descending order.
{C}P. Write an SQL statement to show which customers bought which items, and include any items that have not been sold. Include CUSTOMER.LastName, CUSTOMER.FirstName, InvoiceNumber, InvoiceDate, ItemNumber, ItemDescription, ArtistLastName, and ArtistFirstName. Use a join using JOIN ON syntax, and sort the results by ArtistLastName and ArtistFirstName in ascending order.
{C}Q. Write an SQL statement to modify all ITEM rows with an artist last name of Baxter to an artist first name of Rex.
{C}R. Write SQL statements to switch the values of ArtistLastName so that all rows currently having the value Baker will have the value Baxter and all rows currently having the value Baxter will have the value Baker.
{C}S. Given your assumptions about cascading deletions in your answer to part B, write the fewest number of DELETE statements possible to remove all the data in your database but leave the table structures intact. Do not run these statements if you are using an actual database!
/***** CUSTOMER DATA ********************************************************/
INSERT INTO CUSTOMER VALUES(
'Stanley', 'Elizabeth','555-236-7789', 'Elizabeth.Stanley@somewhere.com');
INSERT INTO CUSTOMER VALUES(
'Price', 'Fred', '555-236-0091', 'Fred.Price@somewhere.com');
INSERT INTO CUSTOMER VALUES(
'Becky', 'Linda', '555-236-0392', 'Linda.Becky@somewhere.com');
INSERT INTO CUSTOMER VALUES(
'Birch', 'Pamela', '555-236-4493', 'Pamela.Birch@somewhere.com');
INSERT INTO CUSTOMER VALUES(
'Romez', 'Ricardo', '555-236-3334', 'Ricardo.Romez@somewhere.com');
INSERT INTO CUSTOMER VALUES(
'Jackson', 'Samantha', '555-236-1095', 'Samantha.Jackson@somewhere.com');
/***** ITEM DATA ************************************************************/
INSERT INTO ITEM VALUES('Gold Bracelet', 120.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES('Gold Necklace', 160.00, 'Baker', 'Samantha');
INSERT INTO ITEM VALUES('Bead Earrings', 50.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES('Gold Bracelet', 180.00, 'Baker', 'Samantha');
INSERT INTO ITEM VALUES('Silver Necklace', 135.00, 'Baxter', 'Sam');
INSERT INTO ITEM VALUES('Bead Earrings', 25.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES('Bead Earrings', 22.50, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES('Gold Earrings', 50.00, 'Lintz', 'John');
INSERT INTO ITEM VALUES('Gold Necklace', 160.00, 'Lintz', 'John');
INSERT INTO ITEM VALUES('Bead Earrings', 20.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES('Bead Earrings', 35.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES('Bead Earrings', 45.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES('Gold Necklace', 225.00, 'Lintz', 'John');
INSERT INTO ITEM VALUES('Silver Earrings', 55.00, 'Lintz', 'John');
INSERT INTO ITEM VALUES('Gold Bracelet', 200.00, 'Lintz', 'John');
INSERT INTO ITEM VALUES('Bead Earrings', 25.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES('Bead Earrings', 45.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES('Gold Bracelet', 210.00, 'Baker', 'Samantha');
INSERT INTO ITEM VALUES('Silver Necklace', 165.00, 'Baxter', 'Sam');
/***** PURCHASE DATA *******************************************************/
INSERT INTO PURCHASE VALUES('05-MAY-17', 155.00, 1);
INSERT INTO PURCHASE VALUES('07-MAY-17', 203.00, 2);
INSERT INTO PURCHASE VALUES('11-MAY-17', 75.00, 3);
INSERT INTO PURCHASE VALUES('15-MAY-17', 67.00, 4);
INSERT INTO PURCHASE VALUES('15-MAY-17', 330.00, 5);
INSERT INTO PURCHASE VALUES('16-MAY-17', 25.00, 1);
INSERT INTO PURCHASE VALUES('25-MAY-17', 45.00, 3);
INSERT INTO PURCHASE VALUES('06-JUN-17', 445.00, 1);
INSERT INTO PURCHASE VALUES('07-JUN-17', 72.00, 6);
/***** PURCHASE_ITEM DATA ***************************************************/
INSERT INTO PURCHASE_ITEM VALUES(1001, 1, 1, 155.00);
INSERT INTO PURCHASE_ITEM VALUES(1002, 1, 2, 203.00);
INSERT INTO PURCHASE_ITEM VALUES(1003, 1, 3, 75.00);
INSERT INTO PURCHASE_ITEM VALUES(1004, 1, 6, 35.00);
INSERT INTO PURCHASE_ITEM VALUES(1004, 2, 7, 32.00);
INSERT INTO PURCHASE_ITEM VALUES(1005, 1, 4, 240.00);
INSERT INTO PURCHASE_ITEM VALUES(1005, 2, 8, 90.00);
INSERT INTO PURCHASE_ITEM VALUES(1006, 1, 10, 25.00);
INSERT INTO PURCHASE_ITEM VALUES(1007, 1, 11, 45.00);
INSERT INTO PURCHASE_ITEM VALUES(1008, 1, 5, 175.00);
INSERT INTO PURCHASE_ITEM VALUES(1008, 2, 9, 215.00);
INSERT INTO PURCHASE_ITEM VALUES(1008, 3, 12, 55.00);
INSERT INTO PURCHASE_ITEM VALUES(1009, 1, 14, 72.00);
Explanation / Answer
If you have any doubts, please give me comment...
-- {C}A.Write SQL CREATE TABLE statements for each of these tables.
CREATE TABLE CUSTOMER(
CustomerID INT NOT NULL PRIMARY KEY ,
LastName VARCHAR(50),
FirstName VARCHAR(50),
Phone CHAR(15),
EmailAddress VARCHAR(100)
) AUTO_INCREMENT=1;
CREATE TABLE PURCHASE(
InvoiceNumber INT NOT NULL PRIMARY KEY,
InvoiceDate DATE,
PreTaxAmount REAL(10,2),
CustomerID INT
) AUTO_INCREMENT=1001;
CREATE TABLE PURCHASE_ITEM(
InvoiceNumber INT,
InvoiceLineNumber INT,
ItemNumber INT,
RetailPrice REAL(10,2)
PRIMARY KEY(InvoiceNumber, ItemNumber),
);
CREATE TABLE ITEM(
ItemNumber INT NOT NULL PRIMARY KEY,
ItemDescription VARCHAR(255),
Cost REAL(10,2),
ArtistLastName VARCHAR(50),
ArtistFirstName VARCHAR(50)
) AUTO_INCREMENT=1;
-- {C}B. Write foreign key constraints for the relationships in each of these tables.
ALTER TABLE PURCHASE ADD FOREIGN KEY(CustomerID) REFERENCES CUSTOMER(CustomerID);
ALTER TABLE PURCHASE_ITEM ADD FOREIGN KEY(InvoiceNumber) REFERENCES PURCHASE(InvoiceNumber);
ALTER TABLE ItemNumber ADD FOREIGN KEY(ItemNumber) REFERENCES ITEM(ItemNumber);
-- {C}C.Write SQL statements to insert some sample data into these tables. Assume that surrogate key column values will be supplied by the DBMS.
INSERT INTO CUSTOMER VALUES('Stanley', 'Elizabeth','555-236-7789', 'Elizabeth.Stanley@somewhere.com');
INSERT INTO CUSTOMER VALUES('Price', 'Fred', '555-236-0091', 'Fred.Price@somewhere.com');
INSERT INTO CUSTOMER VALUES('Becky', 'Linda', '555-236-0392', 'Linda.Becky@somewhere.com');
INSERT INTO CUSTOMER VALUES('Birch', 'Pamela', '555-236-4493', 'Pamela.Birch@somewhere.com');
INSERT INTO ITEM VALUES('Gold Bracelet', 120.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES('Gold Necklace', 160.00, 'Baker', 'Samantha');
INSERT INTO ITEM VALUES('Bead Earrings', 50.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES('Gold Bracelet', 180.00, 'Baker', 'Samantha');
INSERT INTO PURCHASE VALUES('05-MAY-17', 155.00, 1);
INSERT INTO PURCHASE VALUES('07-MAY-17', 203.00, 2);
INSERT INTO PURCHASE VALUES('11-MAY-17', 75.00, 3);
INSERT INTO PURCHASE VALUES('15-MAY-17', 67.00, 4);
INSERT INTO PURCHASE VALUES('15-MAY-17', 330.00, 5);
INSERT INTO PURCHASE_ITEM VALUES(1001, 1, 1, 155.00);
INSERT INTO PURCHASE_ITEM VALUES(1002, 1, 2, 203.00);
INSERT INTO PURCHASE_ITEM VALUES(1003, 1, 3, 75.00);
-- {C}D. Write SQL statements to list all columns for all tables.
DESC CUSTOMER;
DESC PURCHASE;
DESC PURCHASE_ITEM;
DESC ITEM;
-- {C}E. Write an SQL statement to list ItemNumber and ItemDescription for all items that cost more than $100.
SELECT ItemNumber, ItemDescription
FROM ITEM
WHERE Cost>100;
-- {C}F. Write an SQL statement to list ItemNumber and ItemDescription for all items that cost more than $100 and were produced by an artist with a last name ending with the letters son.
SELECT ItemNumber, ItemDescription
FROM ITEM
WHERE Cost>100 AND ArtistLastName LIKE '%son';
-- {C}G. Write an SQL statement to list LastName and FirstName of customers who have made at least one purchase with PreTaxAmount greater than $200. Use a subquery.
SELECT LastName, FirstName
FROM CUSTOMER
WHERE CustomerID IN(
SELECT CustomerID
FROM PURCHASE
WHERE PreTaxAmount>200
);
-- {C}H. Answer part G but use a join using JOIN ON syntax.
SELECT LastName, FirstName
FROM CUSTOMER C JOIN PURCHASE P ON C.CustomerID = P.CustomerID
WHERE PreTaxAmount >200;
-- {C}I. Write an SQL statement to list LastName and FirstName of customers who have purchased an item that costs more than $50. Use a subquery.
SELECT LastName, FirstName
FROM CUSTOMER
WHERE CustomerID IN (
SELECT CustomerID
FROM PURCHASE
WHERE InvoiceNumber IN(
SELECT InvoiceNumber
FROM PURCHASE_ITEM
WHERE ItemNumber IN(
SELECT ItemNumber
FROM ITEM
WHERE Cost>50
)
)
);
-- {C}J. Answer part I but use a join using JOIN ON syntax.
SELECT LastName, FirstName
FROM (((CUSTOMER C JOIN PURCHASE P ON C.CustomerID = P.CustomerID) JOIN PURCHASE_ITEM PI ON P.InvoiceNumber = PI.InvoiceNumber) JOIN ITEM I ON PI.ItemNumber = I.ItemNumber)
WHERE Cost>50;
-- {C}K. Write an SQL statement to list LastName and FirstName of customers who have purchased an item that was created by an artist with a last name that begins with the letter J. Use a subquery.
SELECT LastName, FirstName
FROM CUSTOMER
WHERE CustomerID IN (
SELECT CustomerID
FROM PURCHASE
WHERE InvoiceNumber IN(
SELECT InvoiceNumber
FROM PURCHASE_ITEM
WHERE ItemNumber IN(
SELECT ItemNumber
FROM ITEM
WHERE ArtistLastName LIKE 'J%'
)
)
);
-- {C}L. Answer part K but use a join using JOIN…ON syntax
SELECT LastName, FirstName
FROM (((CUSTOMER C JOIN PURCHASE P ON C.CustomerID = P.CustomerID) JOIN PURCHASE_ITEM PI ON P.InvoiceNumber = PI.InvoiceNumber) JOIN ITEM I ON PI.ItemNumber = I.ItemNumber)
WHERE ArtistLastName LIKE 'J%';
-- {C}M. Write an SQL statement to show the Name and sum of PreTaxAmount for each customer. Use a join using JOIN ON syntax.
SELECT CONCAT(LastName,' ', FirstName) AS Name, SUM(PreTaxAmount)
FROM CUSTOMER C JOIN PURCHASE P ON C.CustomerID = P.CustomerID
GROUP BY C.CustomerID;
-- {C}N. Write an SQL statement to show the sum of PreTaxAmount for each artist (hint: the result will have only one line per each artist). Use a join using JOIN ON syntax, and sort the results by ArtistLastName then ArtistFirstName in ascending order. Note this should include the full PreTaxAmount for any purchase in which the artist had an item.
SELECT CONCAT(ArtistFirstName, '' ,ArtistLastName) AS Name, SUM(PreTaxAmount)
FROM (PURCHASE P JOIN PURCHASE_ITEM PI ON P.InvoiceNumber = PI.InvoiceNumber) JOIN ITEM I ON PI.ItemNumber = I.ItemNumber
GROUP BY ArtistFirstName, ArtistLastName
ORDER BY ArtistLastName ASC, ArtistFirstName ASC;
-- {C}O. Write an SQL statement to show the sum of PreTaxAmount for each artist but exclude any items that were part of purchases with PreTaxAmount over $25. Use a join using JOIN ON syntax, and sort the results by ArtistLastName and ArtistFirstName in descending order.
SELECT CONCAT(ArtistFirstName, '' ,ArtistLastName) AS Name, SUM(PreTaxAmount)
FROM (PURCHASE P JOIN PURCHASE_ITEM PI ON P.InvoiceNumber = PI.InvoiceNumber) JOIN ITEM I ON PI.ItemNumber = I.ItemNumber
WHERE PreTaxAmount >25
GROUP BY ArtistFirstName, ArtistLastName
ORDER BY ArtistLastName, ArtistFirstName;
-- {C}P. Write an SQL statement to show which customers bought which items, and include any items that have not been sold. Include CUSTOMER.LastName, CUSTOMER.FirstName, InvoiceNumber, InvoiceDate, ItemNumber, ItemDescription, ArtistLastName, and ArtistFirstName. Use a join using JOIN ON syntax, and sort the results by ArtistLastName and ArtistFirstName in ascending order.
SELECT C.LastName, C.FirstName, InvoiceNumber, InvoiceDate, ItemNumber, ItemDescription, ArtistLastName, ArtistFirstName
FROM (((CUSTOMER C JOIN PURCHASE P ON C.CustomerID = P.CustomerID) JOIN PURCHASE_ITEM PI ON P.InvoiceNumber = PI.InvoiceNumber) JOIN ITEM I ON PI.ItemNumber = I.ItemNumber)
ORDER BY ArtistLastName ASC, ArtistFirstName ASC;
-- {C}Q. Write an SQL statement to modify all ITEM rows with an artist last name of Baxter to an artist first name of Rex.
UPDATE ITEM SET ArtistFirstName = 'Rex' WHERE ArtistLastName ='Baxter';
-- {C}S. Given your assumptions about cascading deletions in your answer to part B, write the fewest number of DELETE statements possible to remove all the data in your database but leave the table structures intact.
DELETE FROM PURCHASE_ITEM;
DELETE FROM PURCHASE;
DELETE FROM CUSTOMER;
DELETE FROM ITEM;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.