API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Sep 27th, 2015
128
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.21 KB | None | 0 0
raw download clone embed print report
  1. -- HW4.json.txt -- Homework 4
  2. --
  3. -- David Parker Partner: Spencer Shadley (srs3742)
  4. -- UT EID: dp24559, UTCS username: dp24559
  5. -- CS 347, Fall 2015, Dr. P. Cannata
  6. -- Department of Computer Science, The University of Texas at Austin
  7.  
  8. -- 04-01
  9. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 1, "email_address": "allan.sherwood@yahoo.com", "password": "650215acec746f0e32bdfff387439eefc1358737", "first_name": "Allan", "last_name": "Sherwood", "shipping_address_id": 1, "billing_address_id": 2, "orders": { "identifier": 1, "customer_id": 1, "order_date": "28-MAR-12", "SHIP_AMOUNT": 5, "TAX_AMOUNT": 32.32, "SHIP_DATE": "30-MAR-12", "SHIP_ADDRESS_ID": 1, "CARD_TYPE": "Visa", "CARD_NUMBER": 4111111111111111, "CARD_EXPIRES": "04/2014", "BILLING_ADDRESS_ID": 2}, "addresses": { "identifier": 1, "customer_id": 1, "line1": "100 East Ridgewood Ave.", "line2": "", "city": "Paramus", "state": "NJ", "zip_code": "07652", "phone": "201-653-4472", "disabled": 0}}}');
  10. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 1, "email_address": "allan.sherwood@yahoo.com", "password": "650215acec746f0e32bdfff387439eefc1358737", "first_name": "Allan", "last_name": "Sherwood", "shipping_address_id": 1, "billing_address_id": 2, "orders": { "identifier": 3, "customer_id": 1, "order_date": "29-MAR-12", "SHIP_AMOUNT": 10, "TAX_AMOUNT": 89.92, "SHIP_DATE": "31-MAR-12", "SHIP_ADDRESS_ID": 1, "CARD_TYPE": "Visa", "CARD_NUMBER": 4111111111111111, "CARD_EXPIRES": "04/2014", "BILLING_ADDRESS_ID": 2}, "addresses": { "identifier": 1, "customer_id": 1, "line1": "100 East Ridgewood Ave.", "line2": "", "city": "Paramus", "state": "NJ", "zip_code": "07652", "phone": "201-653-4472", "disabled": 0}}}');
  11. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 1, "email_address": "allan.sherwood@yahoo.com", "password": "650215acec746f0e32bdfff387439eefc1358737", "first_name": "Allan", "last_name": "Sherwood", "shipping_address_id": 1, "billing_address_id": 2, "orders": { "identifier": 1, "customer_id": 1, "order_date": "28-MAR-12", "SHIP_AMOUNT": 5, "TAX_AMOUNT": 32.32, "SHIP_DATE": "30-MAR-12", "SHIP_ADDRESS_ID": 1, "CARD_TYPE": "Visa", "CARD_NUMBER": 4111111111111111, "CARD_EXPIRES": "04/2014", "BILLING_ADDRESS_ID": 2}, "addresses": { "identifier": 2, "customer_id": 1, "line1": "21 Rosewood Rd.", "line2": "", "city": "Woodcliff Lake", "state": "NJ", "zip_code": "07677", "phone": "201-653-4472", "disabled": 0}}}');
  12. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 1, "email_address": "allan.sherwood@yahoo.com", "password": "650215acec746f0e32bdfff387439eefc1358737", "first_name": "Allan", "last_name": "Sherwood", "shipping_address_id": 1, "billing_address_id": 2, "orders": { "identifier": 3, "customer_id": 1, "order_date": "29-MAR-12", "SHIP_AMOUNT": 10, "TAX_AMOUNT": 89.92, "SHIP_DATE": "31-MAR-12", "SHIP_ADDRESS_ID": 1, "CARD_TYPE": "Visa", "CARD_NUMBER": 4111111111111111, "CARD_EXPIRES": "04/2014", "BILLING_ADDRESS_ID": 2}, "addresses": { "identifier": 2, "customer_id": 1, "line1": "21 Rosewood Rd.", "line2": "", "city": "Woodcliff Lake", "state": "NJ", "zip_code": "07677", "phone": "201-653-4472", "disabled": 0}}}');
  13. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 2, "email_address": "barryz@gmail.com", "password": "3f563468d42a448cb1e56924529f6e7bbe529cc7", "first_name": "Barry", "last_name": "Zimmer", "shipping_address_id": 3, "billing_address_id": 3, "orders": { "identifier": 2, "customer_id": 2, "order_date": "28-MAR-12", "SHIP_AMOUNT": 5, "TAX_AMOUNT": 0, "SHIP_DATE": "29-MAR-12", "SHIP_ADDRESS_ID": 3, "CARD_TYPE": "Visa", "CARD_NUMBER": 4012888888881881, "CARD_EXPIRES": "08/2016", "BILLING_ADDRESS_ID": 3}, "addresses": { "identifier": 3, "customer_id": 2, "line1": "16285 Wendell St.", "line2": "", "city": "Omaha", "state": "NE", "zip_code": "68135", "phone": "402-896-2576", "disabled": 0}}}');
  14. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 3, "email_address": "christineb@solarone.com", "password": "ed19f5c0833094026a2f1e9e6f08a35d26037066", "first_name": "Christine", "last_name": "Brown", "shipping_address_id": 4, "billing_address_id": 4, "orders": { "identifier": 4, "customer_id": 3, "order_date": "30-MAR-12", "SHIP_AMOUNT": 5, "TAX_AMOUNT": 0, "SHIP_DATE": "03-APR-12", "SHIP_ADDRESS_ID": 4, "CARD_TYPE": "American Express", "CARD_NUMBER": 378282246310005 , "CARD_EXPIRES": "04/2013", "BILLING_ADDRESS_ID": 4}, "addresses": { "identifier": 4, "customer_id": 3, "line1": "19270 NW Cornell Rd.", "line2": "", "city": "Beaverton", "state": "OR", "zip_code": "97006", "phone": "503-654-1291", "disabled": 0}}}');
  15. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 4, "email_address": "david.goldstein@hotmail.com", "password": "b444ac06613fc8d63795be9ad0beaf55011936ac", "first_name": "David", "last_name": "Goldstein", "shipping_address_id": 5, "billing_address_id": 6, "orders": { "identifier": 9, "customer_id": 4, "order_date": "03-APR-12", "SHIP_AMOUNT": 5, "TAX_AMOUNT": 0, "SHIP_DATE": "", "SHIP_ADDRESS_ID": 5, "CARD_TYPE": "Visa", "CARD_NUMBER": 4111111111111111, "CARD_EXPIRES": "04/2016", "BILLING_ADDRESS_ID": 6}, "addresses": { "identifier": 5, "customer_id": 4, "line1": "186 Vermont St.", "line2": "Apt. 2", "city": "San Francisco", "state": "CA", "zip_code": "94110", "phone": "415-292-6651", "disabled": 0}}}');
  16. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 4, "email_address": "david.goldstein@hotmail.com", "password": "b444ac06613fc8d63795be9ad0beaf55011936ac", "first_name": "David", "last_name": "Goldstein", "shipping_address_id": 5, "billing_address_id": 6, "orders": { "identifier": 5, "customer_id": 4, "order_date": "31-MAR-12", "SHIP_AMOUNT": 5, "TAX_AMOUNT": 0, "SHIP_DATE": "02-APR-12", "SHIP_ADDRESS_ID": 5, "CARD_TYPE": "Visa", "CARD_NUMBER": 4111111111111111, "CARD_EXPIRES": "04/2016", "BILLING_ADDRESS_ID": 6}, "addresses": { "identifier": 5, "customer_id": 4, "line1": "186 Vermont St.", "line2": "Apt. 2", "city": "San Francisco", "state": "CA", "zip_code": "94110", "phone": "415-292-6651", "disabled": 0}}}');
  17. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 4, "email_address": "david.goldstein@hotmail.com", "password": "b444ac06613fc8d63795be9ad0beaf55011936ac", "first_name": "David", "last_name": "Goldstein", "shipping_address_id": 5, "billing_address_id": 6, "orders": { "identifier": 9, "customer_id": 4, "order_date": "03-APR-12", "SHIP_AMOUNT": 5, "TAX_AMOUNT": 0, "SHIP_DATE": "", "SHIP_ADDRESS_ID": 5, "CARD_TYPE": "Visa", "CARD_NUMBER": 4111111111111111, "CARD_EXPIRES": "04/2016", "BILLING_ADDRESS_ID": 6}, "addresses": { "identifier": 6, "customer_id": 4, "line1": "1374 46th Ave.", "line2": "", "city": "San Francisco", "state": "CA", "zip_code": "94129", "phone": "415-292-6651", "disabled": 0}}}');
  18. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 4, "email_address": "david.goldstein@hotmail.com", "password": "b444ac06613fc8d63795be9ad0beaf55011936ac", "first_name": "David", "last_name": "Goldstein", "shipping_address_id": 5, "billing_address_id": 6, "orders": { "identifier": 5, "customer_id": 4, "order_date": "31-MAR-12", "SHIP_AMOUNT": 5, "TAX_AMOUNT": 0, "SHIP_DATE": "02-APR-12", "SHIP_ADDRESS_ID": 5, "CARD_TYPE": "Visa", "CARD_NUMBER": 4111111111111111, "CARD_EXPIRES": "04/2016", "BILLING_ADDRESS_ID": 6}, "addresses": { "identifier": 6, "customer_id": 4, "line1": "1374 46th Ave.", "line2": "", "city": "San Francisco", "state": "CA", "zip_code": "94129", "phone": "415-292-6651", "disabled": 0}}}');
  19. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 5, "email_address": "erinv@gmail.com", "password": "109f4b3c50d7b0df729d299bc6f8e9ef9066971f", "first_name": "Erin", "last_name": "Valentino", "shipping_address_id": 7, "billing_address_id": 7, "orders": { "identifier": 6, "customer_id": 5, "order_date": "31-MAR-12", "SHIP_AMOUNT": 5, "TAX_AMOUNT": 0, "SHIP_DATE": "", "SHIP_ADDRESS_ID": 7, "CARD_TYPE": "Discover", "CARD_NUMBER": 6011111111111117, "CARD_EXPIRES": "04/2016", "BILLING_ADDRESS_ID": 7}, "addresses": { "identifier": 7, "customer_id": 5, "line1": "6982 Palm Ave.", "line2": "", "city": "Fresno", "state": "CA", "zip_code": "93711", "phone": "559-431-2398", "disabled": 0}}}');
  20. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 6, "email_address": "frankwilson@sbcglobal.net", "password": "3ebfa301dc59196f18593c45e519287a23297589", "first_name": "Frank Lee", "last_name": "Wilson", "shipping_address_id": 8, "billing_address_id": 8, "orders": { "identifier": 7, "customer_id": 6, "order_date": "01-APR-12", "SHIP_AMOUNT": 15, "TAX_AMOUNT": 0, "SHIP_DATE": "03-APR-12", "SHIP_ADDRESS_ID": 8, "CARD_TYPE": "MasterCard", "CARD_NUMBER": 5555555555554444, "CARD_EXPIRES": "04/2016", "BILLING_ADDRESS_ID": 8}, "addresses": { "identifier": 8, "customer_id": 6, "line1": "23 Mountain View St.", "line2": "", "city": "Denver", "state": "CO", "zip_code": "80208", "phone": "303-912-3852", "disabled": 0}}}');
  21. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 7, "email_address": "gary_hernandez@yahoo.com", "password": "1ff2b3704aede04eecb51e50ca698efd50a1379b", "first_name": "Gary", "last_name": "Hernandez", "shipping_address_id": 9, "billing_address_id": 10, "orders": { "identifier": 8, "customer_id": 7, "order_date": "02-APR-12", "SHIP_AMOUNT": 5, "TAX_AMOUNT": 0, "SHIP_DATE": "", "SHIP_ADDRESS_ID": 9, "CARD_TYPE": "Visa", "CARD_NUMBER": 4012888888881881, "CARD_EXPIRES": "04/2016", "BILLING_ADDRESS_ID": 10}, "addresses": { "identifier": 9, "customer_id": 7, "line1": "7361 N. 41st St.", "line2": "Apt. B", "city": "New York", "state": "NY", "zip_code": "10012", "phone": "212-335-2093", "disabled": 0}}}');
  22. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 7, "email_address": "gary_hernandez@yahoo.com", "password": "1ff2b3704aede04eecb51e50ca698efd50a1379b", "first_name": "Gary", "last_name": "Hernandez", "shipping_address_id": 9, "billing_address_id": 10, "orders": { "identifier": 8, "customer_id": 7, "order_date": "02-APR-12", "SHIP_AMOUNT": 5, "TAX_AMOUNT": 0, "SHIP_DATE": "", "SHIP_ADDRESS_ID": 9, "CARD_TYPE": "Visa", "CARD_NUMBER": 4012888888881881, "CARD_EXPIRES": "04/2016", "BILLING_ADDRESS_ID": 10}, "addresses": { "identifier": 10, "customer_id": 7, "line1": "3829 Broadway Ave.", "line2": "Suite 2", "city": "New York", "state": "NY", "zip_code": "10012", "phone": "212-239-1208", "disabled": 0}}}');
  23. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 8, "email_address": "heatheresway@mac.com", "password": "911ddc3b8f9a13b5499b6bc4638a2b4f3f68bf23", "first_name": "Heather", "last_name": "Esway", "shipping_address_id": 11, "billing_address_id": 12, "orders": { "identifier": 0, "customer_id": 8, "order_date": "", "SHIP_AMOUNT": 0, "TAX_AMOUNT": 0, "SHIP_DATE": "", "SHIP_ADDRESS_ID": 0, "CARD_TYPE": "", "CARD_NUMBER": 0, "CARD_EXPIRES": "", "BILLING_ADDRESS_ID": 0}, "addresses": { "identifier": 11, "customer_id": 8, "line1": "2381 Buena Vista St.", "line2": "", "city": "Los Angeles", "state": "CA", "zip_code": "90023", "phone": "213-772-5033", "disabled": 0}}}');
  24. insert into dept_json(dept_json) values('{ "customers" : {"identifier": 8, "email_address": "heatheresway@mac.com", "password": "911ddc3b8f9a13b5499b6bc4638a2b4f3f68bf23", "first_name": "Heather", "last_name": "Esway", "shipping_address_id": 11, "billing_address_id": 12, "orders": { "identifier": 0, "customer_id": 8, "order_date": "", "SHIP_AMOUNT": 0, "TAX_AMOUNT": 0, "SHIP_DATE": "", "SHIP_ADDRESS_ID": 0, "CARD_TYPE": "", "CARD_NUMBER": 0, "CARD_EXPIRES": "", "BILLING_ADDRESS_ID": 0}, "addresses": { "identifier": 12, "customer_id": 8, "line1": "291 W. Hollywood Blvd.", "line2": "", "city": "Los Angeles", "state": "CA", "zip_code": "90024", "phone": "213-391-2938", "disabled": 0}}}');
  25.  
  26. -- 04-02-01
  27. select distinct
  28. d.dept_json.customers.orders.identifier as ORDER_ID,
  29. d.dept_json.customers.orders.customer_id as CUSTOMER_ID,
  30. d.dept_json.customers.orders.order_date as ORDER_DATE,
  31. d.dept_json.customers.orders.SHIP_AMOUNT as SHIP_AMOUNT,
  32. d.dept_json.customers.orders.TAX_AMOUNT as TAX_AMOUNT,
  33. d.dept_json.customers.orders.SHIP_DATE as SHIP_DATE,
  34. d.dept_json.customers.orders.SHIP_ADDRESS_ID as SHIP_ADDRESS_ID,
  35. d.dept_json.customers.orders.CARD_TYPE as CARD_TYPE,
  36. d.dept_json.customers.orders.CARD_NUMBER as CARD_NUMBER,
  37. d.dept_json.customers.orders.CARD_EXPIRES as CARD_EXPIRES,
  38. d.dept_json.customers.orders.BILLING_ADDRESS_ID as BILLING_ADDRESS_ID
  39. from dept_json d
  40. where d.dept_json.customers.orders.identifier != 0
  41. order by 1;
  42.  
  43. -- 04-02-02
  44. select distinct
  45. d.dept_json.customers.first_name as FIRST_NAME,
  46. d.dept_json.customers.last_name as LAST_NAME
  47. from dept_json d
  48. where d.dept_json.customers.identifier != 0
  49. order by 1;
  50.  
  51. -- 04-02-03
  52. select distinct
  53. d.dept_json.customers.first_name as FIRST_NAME,
  54. d.dept_json.customers.last_name as LAST_NAME,
  55. d.dept_json.customers.addresses.city as CITY,
  56. d.dept_json.customers.addresses.state as STATE
  57. from dept_json d
  58. where d.dept_json.customers.identifier != 0;
  59.  
  60. -- 04-02-04
  61. select distinct
  62. d.dept_json.customers.first_name as FIRST_NAME,
  63. d.dept_json.customers.last_name as LAST_NAME,
  64. d.dept_json.customers.orders.order_date as ORDER_DATE
  65. from dept_json d
  66. WHERE d.dept_json.customers.orders.identifier != 0 AND
  67. d.dept_json.customers.identifier != 0
  68. order by 1;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!