subject

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). It 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 sum 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, Email) PURCHASE (InvoiceNumber, InvoiceDate, PreTaxAmount, CustomerID) PURCHASE_ITEM (InvoiceNumber, ItemNumber, RetailPrice) ITEM (ItemNumber, Description, Cost, ArtistName) 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, InvoiceNumber of PURCHASE, and ItemNumber of PURCHASE_ITEM are all surrogate keys with values as follows: CustomerID Start at 1 Increment by 1 InvoiceNumber Start at 1 Increment by 1 ItemNumber Start at 1 Increment by 1 Write SQL statements and answer questions for this database as follows: A. Write SQL CREATE TABLE statements for each of these tables. B. Write foreign key constraints for the relationships in each of these tables. Make your own assumptions regarding cascading deletions and justify those assumptions. (Hint: You can combine the SQL for your answers to parts A and B.) C. Write SQL statements to insert at least three rows of data into each of these tables. Assume that surrogate key column values will be supplied by the DBMS. D. Write SQL statements to list all columns for all tables. E. Write an SQL statement to list ItemNumber and Description for all items that cost more than $100. F. Write an SQL statement to list ItemNumber and Description for all items that cost more than $100 and were produced by an artist with a last name ending with the letters son. 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. H. Answer part G but use a join using JOIN…ON syntax. 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. J. Answer part I but use a join using JOIN…ON syntax 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. L. Answer part K but use a join using JOIN…ON syntax M. Write an SQL statement to show the Name and sum of PreTaxAmount for each customer using JOIN…ON syntax. 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 in ascending order. O. Write an SQL statement to show the sum of PreTaxAmount for each ArtistName but exclude any items that were part of purchases with PreTaxAmount less than $25. Use a join using JOIN…ON syntax, and sort the results by ArtistLastName in descending order. P. Write an SQL statement to show which customers bought which items, and include any items which have not been sold. Includes 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. Q. Write an SQL statement to modify all ITEM rows with an artist last name of Baxter to an artist first name of Rex. 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.

ansver
Answers: 1

Other questions on the subject: Computers and Technology

image
Computers and Technology, 21.06.2019 22:40, hePandaKing3689
State the parts of a variable declaration?
Answers: 2
image
Computers and Technology, 22.06.2019 10:00, danthehero56
Which of the following is true of operations within a spreadsheet program’s built-in functions? a. operations within parentheses, then multiplication and division, and then addition and subtraction are computed. b. operations within parentheses, then addition and subtraction, and then multiplication and division are computed. c. multiplication and division, then addition and subtraction, and then operations within parentheses are computed. d. addition and subtraction, then multiplication and division, and then operations within parentheses are computed
Answers: 2
image
Computers and Technology, 22.06.2019 16:20, Aleja9
It policy compliance and emerging technologies respond to the following: propose at least three control measures that organizations need to put in place to ensure that they remain complaint with emerging technologies and in a continually changing it environment. examine the correlation of effective configuration management and change control procedures to remain compliant with emerging technologies and it security changes.
Answers: 2
image
Computers and Technology, 22.06.2019 17:40, math31343
Gabe wants to move text from one document to another document. he should copy the text, paste the text, and open the new document highlight the text, select the cut command, move to the new document, make sure the cursor is in the correct location, and select the paste command select the save as command, navigate to the new document, and click save highlight the text, open the new document, and press ctrl and v
Answers: 1
You know the right answer?
James River Jewelry is a small jewelry shop. While James River Jewelry does sell typical jewelry pur...

Questions in other subjects:

Konu
Mathematics, 19.06.2020 22:57
Konu
Mathematics, 19.06.2020 22:57