API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 22nd, 2018
534
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 14.33 KB | None | 0 0
raw download clone embed print report
  1. /* FILE:  FLIX2YOU_data-load.txt                                            */
  2. /* Script to populate tables for FLIX2YOU .. current schema before revision */
  3. /* Written by Gary Heberling on July 2, 2012                                */
  4. /* For IST210 world campus Penn State University                            */
  5.  
  6. /* This script is provided to students in IST210 for Lab # 7                */
  7.  
  8. /* Modifications:
  9.    (1) Removed DELETE FROM and RESEED commands from script;  1/3/13 by glh
  10.                                                                             */
  11.  
  12. /* PLEASE READ THE INSTRUCTIONS FOR LAB # 7 BEFORE EXECUTING                */
  13.  
  14. /* Insert data for table genre_codes */
  15.  
  16.  
  17. INSERT INTO genre_codes VALUES ('Action & Adventure');
  18. INSERT INTO genre_codes VALUES ('Comedy');
  19. INSERT INTO genre_codes VALUES ('Documentary');
  20. INSERT INTO genre_codes VALUES ('Drama');
  21. INSERT INTO genre_codes VALUES ('Education');
  22. INSERT INTO genre_codes VALUES ('Foreign');
  23. INSERT INTO genre_codes VALUES ('Horror');
  24. INSERT INTO genre_codes VALUES ('Romance');
  25. INSERT INTO genre_codes VALUES ('Thriller');
  26. INSERT INTO genre_codes VALUES ('Western');
  27.  
  28. /* Insert data for table format_types */
  29.  
  30. INSERT INTO format_types VALUES ('DVD');
  31. INSERT INTO format_types VALUES ('Blu-Ray');
  32. INSERT INTO format_types VALUES ('MiniDV');
  33. INSERT INTO format_types VALUES ('VHS');
  34.  
  35. /* Insert data for table video_stores */
  36.  
  37. INSERT INTO video_stores VALUES ('FLIX2YOU Headquarters', '1225 W Linden Street', 'Scranton',
  38.    'PA', '18501', 'INFO_SCRANTON@FLIX2YOU.COM', 'Headquarters');
  39. INSERT INTO video_stores VALUES ('Pittsburgh Shadyside', '4125 S Highland Ave', 'Pittsburgh',
  40.    'PA', '15232', 'INFO_PITTSBURGH@FLIX2YOU.COM', 'Main Pittsburgh Store');
  41.  
  42. /* Insert data for table condition_codes */
  43.  
  44. INSERT INTO condition_codes VALUES ('rental');
  45. INSERT INTO condition_codes VALUES ('used for sale');
  46. INSERT INTO condition_codes VALUES ('damaged');
  47.  
  48. /* Insert data for moives, actors and move_cast */
  49.  
  50. /* FORMAT of the insert command --->   INSERT INTO movies VALUES (condition, format, genre, store, year,
  51. 'Title', 'Desc', num, rental/sale/both, daily, sale); */
  52.  
  53. /* Amazing Spider-Man */
  54. INSERT INTO movies VALUES (1, 1, 1, 1, 2012, 'The Amazing Spider-Man',
  55. 'The story of Peter Parker, an outcast high schooler who was abandoned by his parents as a boy, leaving him to be raised by his Uncle Ben and Aunt May. Like most teenagers, Peter is trying to figure out who he is and how he got to be the person he is today. Peter is also finding his way with his first high school crush, Gwen Stacy, and together, they struggle with love, commitment, and secrets. As Peter discovers a mysterious briefcase that belonged to his father, he begins a quest to understand his parents disappearance - leading him directly to Oscorp and the lab of Dr. Curt Connors, his fathers former partner. As Spider-Man is set on a collision course with Connors alter-ego, The Lizard, Peter will make life-altering choices to use his powers and shape his destiny to become a hero.',
  56.  100, 1, 1.99, 25.39);
  57. INSERT INTO actors VALUES ('M', 'Andrew', 'Garfield',
  58. 'Although born in Los Angeles, Andrew Garfield grew up in England; his mother is English and they moved back there when he was three years old. He went to a private school, the City of London Freemen School, and began acting in youth theatre productions while he was still at school. At 19, he went to the Central School of Speech and Drama.');
  59. INSERT INTO actors VALUES ('F', 'Emma', 'Stone',
  60. 'Stone began acting as a child as a member of the Valley Youth Theatre in Phoenix, Arizona, where she made her stage debut in a production of Kenneth Grahames The Wind in the Willows. She appeared in many more productions through her early teens until, at the age of 15, she decided that she wanted to make acting her career.');
  61. INSERT INTO movie_cast VALUES (1,1);
  62. INSERT INTO movie_cast VALUES (1,2);
  63.  
  64. /* Superbad */
  65. INSERT INTO movies VALUES (1, 1, 2, 2, 2007, 'Superbad',
  66. 'Two co-dependent high school seniors are forced to deal with separation anxiety after their plan to stage a booze-soaked party goes awry.',
  67.  25, 3, .99, 10.99);
  68. INSERT INTO actors VALUES ('M', 'Michael', 'Cera',
  69. 'Canadian actor Michael Cera is the middle child of a Canadian mother and Italian father, both of whom worked for Xerox. He has two sisters. He was educated at Conestoga Public School, Robert H. Lagerquist Senior Public School and Heart Lake Secondary School until the ninth grade. Cera then completed his high school education via correspondence.');
  70. INSERT INTO actors VALUES ('M', 'Jonah', 'Hill',
  71. 'Hill grew up in Los Angeles, the son of a tour accountant for Guns N Roses. He graduated from Crossroads School in Santa Monica and went on to The New School in New York to study drama.')
  72. INSERT INTO actors VALUES ('M', 'Christopher', 'Mintz-Plasse',
  73. 'Attended El Camino Real High School from 2003 to 2007. Because he was only 17 at the time of filming Superbad (2007), his mother was required to be on set during the filming of his sex scene.');
  74. INSERT INTO movie_cast VALUES (2,3);
  75. INSERT INTO movie_cast VALUES (2,4);
  76. INSERT INTO movie_cast VALUES (2,5);
  77.  
  78.  
  79. /* Gone with the wind */
  80. INSERT INTO movies VALUES (2, 4, 4, 2, 1939, 'Gone with the Wind',
  81. 'American classic in which a manipulative woman and a roguish man carry on a turbulent love affair in the American south during the Civil War and Reconstruction.',
  82.  4, 2, .99, 4.99);
  83. INSERT INTO actors VALUES ('M', 'Clark', 'Gable',
  84. 'Clark Gables mother died when he was seven months old. At 16 he quit high school, went to work in an Akron Ohio tire factory and decided to become an actor after seeing the play The Bird of Paradise.');
  85. INSERT INTO actors VALUES ('F', 'Vivien', 'Leigh',
  86. 'If a film were made of the life of Vivien Leigh, it would open in India just before World War I, where a successful British businessman could live like a prince. In the mountains above Calcutta, a little princess is born. Because of the outbreak of World War I, she is six years old the first time her parents take her to England.');
  87. INSERT INTO actors VALUES ('M', 'Thomas', 'Mitchell',
  88. 'Thomas Mitchell was one of the great American character actors, whose credits read like a list of the greatest films of the 20th century');
  89. INSERT INTO movie_cast VALUES (3,6);
  90. INSERT INTO movie_cast VALUES (3,7);
  91. INSERT INTO movie_cast VALUES (3,8);
  92.  
  93. /* Fahrenheit 9/11 */
  94. INSERT INTO movies VALUES (1, 1, 3, 1, 2004, 'Fahrenheit 9/11',
  95. 'Michael Moores view on what happened to the United States after September 11; and how the Bush Administration allegedly used the tragic event to push forward its agenda for unjust wars in Afghanistan and Iraq.',
  96.  10, 3, .99, 11.99);
  97. INSERT INTO actors VALUES ('M', 'Michael', 'Moore',
  98. 'Michael Moore was born in Flint, Michigan April 23, 1954, but was not raised there. Contrary to popular belief, he was actually raised in Davison, Michigan. He studied journalism at the University of Michigan-Flint, and also pursued other hobbies such as gun shooting, for which he even won a competition.');
  99. INSERT INTO actors VALUES ('M', 'George', 'Bush',
  100. 'The 43rd President of the United States of America, George Walker Bush was born two days after the national holiday of the Fourth of July, 1946 in New Haven, Connecticut where his father was attending Yale College in the Class of 1949.');
  101. INSERT INTO actors VALUES ('M', 'Ben', 'Affleck',
  102. 'Benjamin Geza Affleck was born on August 15, 1972 in Berkeley, California, USA but raised in Cambridge, Massachusetts, USA. He was born to parents Tim Affleck, a social worker, who is now divorced from Bens mother Chris Affleck, a school teacher.');
  103. INSERT INTO movie_cast VALUES (4,9);
  104. INSERT INTO movie_cast VALUES (4,10);
  105. INSERT INTO movie_cast VALUES (4,11);
  106.  
  107. /* The following command will cause the server to wait 5 seconds in order to be sure the server has committed the previous commands */
  108. waitfor delay '0:0:5'
  109.  
  110.  
  111. /* Good Will Hunting */
  112. INSERT INTO movies VALUES (1, 1, 4, 2, 1997, 'Good Will Hunting',
  113. 'Will Hunting, a janitor at MIT, has a gift for mathematics but needs help from a psychologist to find direction in his life.',
  114.  6, 3, .59, 9.99);
  115. INSERT INTO actors VALUES ('M', 'Robin', 'Williams',
  116. 'Williams briefly studied political science before enrolling at Juilliard to study theatre. After he left Juilliard, he performed in night clubs where he was discovered for the role of Mork on an episode of Happy Days in 1974 and the subsequent Mork & Mindy TV series in 1978.');
  117. INSERT INTO actors VALUES ('M', 'Matt', 'Damon',
  118. 'Matt Damon was born in 1970. His father, Kent Damon, a tax preparer, and his mother, Nancy Carlsson-Paige, a college professor, are now divorced.');
  119. INSERT INTO movie_cast VALUES (5,12);
  120. INSERT INTO movie_cast VALUES (5,13);
  121. INSERT INTO movie_cast VALUES (5,11);
  122.  
  123.  
  124. /* After all data is inserted,  execute the following SELECT commands to review the data that has been inserted */
  125. /* select to view movie data
  126. SELECT movie_id, movie_title, movie_description, genre_code_description, condition_code_description, format_type_description,
  127. store_name, release_year, number_in_stock, rental_daily_rate, sales_price
  128. FROM movies, condition_codes, genre_codes, format_types, video_stores
  129. WHERE movies.condition_code = condition_codes.condition_code
  130. AND   movies.format_Type_code = format_types.format_type_code
  131. AND   movies.genre_type_code = genre_codes.genre_code
  132. AND   movies.store_id = video_stores.store_id;
  133. */
  134.  
  135. /* select to view the movie_cast data
  136. SELECT movies.movie_title, actor_first_name+' '+actor_last_name AS NAME, other_actor_details
  137. FROM movie_cast, movies, actors
  138. WHERE movie_cast.movie_id = movies.movie_id
  139. AND   movie_cast.actor_id = actors.actor_id
  140. ORDER BY movie_title, actor_last_name;
  141. */
  142.  
  143. /*  END OF SCRIPT */
  144.  
  145.  
  146. (1 ROW(s) affected)
  147.  
  148. (1 ROW(s) affected)
  149.  
  150. (1 ROW(s) affected)
  151.  
  152. (1 ROW(s) affected)
  153.  
  154. (1 ROW(s) affected)
  155.  
  156. (1 ROW(s) affected)
  157.  
  158. (1 ROW(s) affected)
  159.  
  160. (1 ROW(s) affected)
  161.  
  162. (1 ROW(s) affected)
  163.  
  164. (1 ROW(s) affected)
  165.  
  166. (1 ROW(s) affected)
  167.  
  168. (1 ROW(s) affected)
  169.  
  170. (1 ROW(s) affected)
  171.  
  172. (1 ROW(s) affected)
  173.  
  174. (1 ROW(s) affected)
  175.  
  176. (1 ROW(s) affected)
  177.  
  178. (1 ROW(s) affected)
  179.  
  180. (1 ROW(s) affected)
  181.  
  182. (1 ROW(s) affected)
  183.  
  184. (1 ROW(s) affected)
  185.  
  186. (1 ROW(s) affected)
  187.  
  188. (1 ROW(s) affected)
  189.  
  190. (1 ROW(s) affected)
  191.  
  192. (1 ROW(s) affected)
  193.  
  194. (1 ROW(s) affected)
  195.  
  196. (1 ROW(s) affected)
  197.  
  198. (1 ROW(s) affected)
  199.  
  200. (1 ROW(s) affected)
  201.  
  202. (1 ROW(s) affected)
  203.  
  204. (1 ROW(s) affected)
  205.  
  206. (1 ROW(s) affected)
  207.  
  208. (1 ROW(s) affected)
  209.  
  210. (1 ROW(s) affected)
  211.  
  212. (1 ROW(s) affected)
  213.  
  214. (1 ROW(s) affected)
  215.  
  216. (1 ROW(s) affected)
  217.  
  218. (1 ROW(s) affected)
  219.  
  220. (1 ROW(s) affected)
  221.  
  222. (1 ROW(s) affected)
  223.  
  224. (1 ROW(s) affected)
  225.  
  226. (1 ROW(s) affected)
  227.  
  228. (1 ROW(s) affected)
  229.  
  230. (1 ROW(s) affected)
  231.  
  232. (1 ROW(s) affected)
  233.  
  234. (1 ROW(s) affected)
  235.  
  236. (1 ROW(s) affected)
  237.  
  238. (1 ROW(s) affected)
  239.  
  240. (1 ROW(s) affected)
  241.  
  242. (1 ROW(s) affected)
  243.  
  244. (1 ROW(s) affected)
  245.  
  246. (1 ROW(s) affected)
  247.  
  248.  
  249. Part 2
  250.  
  251. Inserting Customers:
  252. INSERT INTO customers VALUES(0, NULL, NULL, 'Tom', 'Smith', '700 N Blakely St, Dunmore, PA 18512', NULL, 'Tom18512@hotmail.com', NULL)
  253. INSERT INTO customers VALUES(0, NULL, NULL, 'Denise', 'Jones', '649 Penn Ave, Pittsburgh, PA 15222', NULL, 'jones_denise@gmail.com', NULL)
  254. INSERT INTO customers VALUES(0, NULL, NULL, 'Beth', 'Ryder', '2525 Monroeville Blvd, Monroeville, PA 15146', NULL, 'crazy_beth@yahoo.com', NULL)
  255. INSERT INTO customers VALUES(0, NULL, NULL, 'Mike', 'Walker', '309 N Washington Ave, Scranton, PA 18503', NULL, 'mikew@dundermifflin.com
  256. ', NULL)
  257. INSERT INTO customers VALUES(0, NULL, NULL, 'Jonah', 'Murphy', '100 Perry Hwy, Zelienople, PA 16063', NULL, 'jlm5@cmu.edu', NULL)
  258.  
  259. (1 ROW(s) affected)
  260.  
  261. (1 ROW(s) affected)
  262.  
  263. (1 ROW(s) affected)
  264.  
  265. (1 ROW(s) affected)
  266.  
  267. (1 ROW(s) affected)
  268.  
  269.  
  270. ADD rental STATUS codes
  271. INSERT INTO rental_status_codes VALUES('Reserved')
  272. INSERT INTO rental_status_codes VALUES('Active')
  273. INSERT INTO rental_status_codes VALUES('Overdue')
  274. INSERT INTO rental_status_codes VALUES('Returned')
  275.  
  276.  
  277.  
  278. (1 ROW(s) affected)
  279.  
  280. (1 ROW(s) affected)
  281.  
  282. (1 ROW(s) affected)
  283.  
  284. INSERT Customer Rentals
  285.  
  286. INSERT INTO customer_rentals VALUES(1, 2, 5, '2017-10-02', NULL, 4.59, NULL)
  287. INSERT INTO customer_rentals VALUES(4, 3, 4, NULL, NULL, NULL, NULL)
  288. INSERT INTO customer_rentals VALUES(3, 1, 6, '2017-08-03', NULL, 28.78, NULL)
  289. INSERT INTO customer_rentals VALUES(6, 4, 7, '2017-06-10', '2017-06-20', 8.60, NULL)
  290. INSERT INTO customer_rentals VALUES(5, 5, 5, '2017-07-10', NULL, 3.56, NULL)
  291.  
  292.  
  293. (1 ROW(s) affected)
  294.  
  295. (1 ROW(s) affected)
  296.  
  297. (1 ROW(s) affected)
  298.  
  299. (1 ROW(s) affected)
  300.  
  301. (1 ROW(s) affected)
  302.  
  303.  
  304. ADD payment methods
  305. INSERT INTO payment_methods VALUES('Cash')
  306. INSERT INTO payment_methods VALUES('Credit Card')
  307. INSERT INTO payment_methods VALUES('Check')
  308. INSERT INTO payment_methods VALUES('PayPal')
  309.  
  310. (1 ROW(s) affected)
  311.  
  312. (1 ROW(s) affected)
  313.  
  314. (1 ROW(s) affected)
  315.  
  316. (1 ROW(s) affected)
  317.  
  318. ADD TRANSACTION types
  319. INSERT INTO transaction_types VALUES('Payment')
  320. INSERT INTO transaction_types VALUES('Deposit')
  321. INSERT INTO transaction_types VALUES('Refund')
  322.  
  323. (1 ROW(s) affected)
  324.  
  325. (1 ROW(s) affected)
  326.  
  327. (1 ROW(s) affected)
  328.  
  329.  
  330.  
  331. Populate Accounts
  332. INSERT INTO accounts VALUES(1, 1, 'Smith', 'Account of Tom Smith')
  333. INSERT INTO accounts VALUES(3, 2, 'Jones', 'Account of Denise Jones')
  334. INSERT INTO accounts VALUES(4, 4, 'Ryder', 'Account of Beth Ryder')
  335. INSERT INTO accounts VALUES(5, 3, 'Walker', 'Account of Mike Walker')
  336. INSERT INTO accounts VALUES(6, 1, 'Murphy', 'Account of Jonah Murphy')
  337.  
  338. (1 ROW(s) affected)
  339.  
  340. (1 ROW(s) affected)
  341.  
  342. (1 ROW(s) affected)
  343.  
  344. (1 ROW(s) affected)
  345.  
  346. (1 ROW(s) affected)
  347.  
  348.  
  349. INSERT Financial Transactions
  350. INSERT INTO financial_transactions VALUES(1, 2, 0, 1, '2017-10-02', 'no comment')
  351. INSERT INTO financial_transactions VALUES(2, 1, 0, 2, '2017-08-03', 'no comment')
  352. INSERT INTO financial_transactions VALUES(3, 2, 0, 1, '2017-06-02', 'no comment')
  353. INSERT INTO financial_transactions VALUES(4, 2, 0, 1, '2017-10-02', 'no comment')
  354. INSERT INTO financial_transactions VALUES(4, 2, 0, 3, '2017-07-30', 'overpayment')
  355.  
  356. (1 ROW(s) affected)
  357.  
  358. (1 ROW(s) affected)
  359.  
  360. (1 ROW(s) affected)
  361.  
  362. (1 ROW(s) affected)
  363.  
  364. (1 ROW(s) affected)
  365.  
  366.  
  367. Part 3
  368. 1.
  369. UPDATE customers
  370. SET customer_address = '2525 Monroeville Blvd, Monroeville, PA 15144'
  371. WHERE customer_last_name LIKE 'Ryder'
  372.  
  373. (1 ROW(s) affected)
  374.  
  375. 2.
  376. UPDATE customer_rentals
  377. SET movie_id = 5
  378. WHERE customer_id = 3 AND movie_id = 1
  379.  
  380. (1 ROW(s) affected)
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!