Can anyone please help me out about this lab? What I have to do is LAB #7. I have done lab #6.

Upon completion of the previous lab (lab #6), all 14 tables should exist in your folder schema in VLABS SQL Server. It is in your schema where you will be inserting and updating data for this lab (lab #7).

PART 1

You will use the script provided and execute the script in your own folder in VLABS SQL Server. The txt file is located in the folder for Lab #7. You will need to copy/paste the resulting displays from the execution of the script into a document that you will submit for this lab.

PART 2

You will insert data into 2nd set of 7 tables. These are the tables that you wrote the SQL to create the 2nd set of 7 tables in Lab #6. The values that you will insert are provided below. Some data fields are not provided. Please create the necessary values for data that has not been provided. Note: you will need to create the data in the background tables so that foreign key constraints will be satisfied. ALL 7 RIGHT HAND TABLES WILL NEED DATA INSERTED. You will copy/paste the commands you execute along with the messages displayed from the execution of all your commands.

Five Customers:

Name	Address	Email
Jerry Smith	200 Reilly St, Dunmore, PA 18152	JSmithy@hotmail.com
Trisha Jones	600 Grant Street, Pittsburgh, PA 15219	jones_trisha@yahoo.com
Mary Ann Ryder	4006 Berger Ln, Monroeville, PA 15146	crazy_mary@yahoo.com
Jack Walker	349 Adams Av3e, Scranton, PA 18503	jackw@dundermifflin.com
Peter Murphy	230 Mercer St., Zelienople, PA 16063	plm5@cmu.edu

Five Customer Rentals:

Customer	Movie	Status	Date Out	Date Returned	Amount Due
Smith	Superbad	Active	11/2/2017		6.95
Walker	Spider-Man	Reserved
Ryder	Good Will	Overdue	10/2/2017		5.78
Jones	Fahr 9/11	Returned	6/1/2017	6/21/2017	3.60
Murphy	Gone w/ Wind	Active	7/10/2017		2.56

Five Financial Transactions:

Customer	Movie	Type	Pay Method	Amount	Date
Smith	Superbad	Payment	Cash	6.95	11/2/2017
Jones	Fahr 9/11	Deposit	Credit card	3.60	6/1/2017
Ryder	Good Will	Payment	PayPal	5.78	10/2/2017
Walker	Spider-Man	Payment	Check	15.60	5/10/2017
Walker	Spider-Man	Refund	Check	10.00	6/30/2017

PART 3

In this part, you will be updating data to correct some errors. Note that the update may not be a single simple command but rather a series of commands if a field identified as a primary key needs modified. You will copy/paste the commands you execute along with the messages displayed from the execution of all your commands.

Write the SQL code to fix the following errors:

Murphy's zip code should be 16061.

Smith did not rent Superbad but rather Good Will Hunting. The mistake was made by the clerk in the store so there is no change in the amount of the rental.

YOUR LAB ASSIGNMENT

You will need to run the script in your own personal database. Attach a document to the drop box for this lab that contains the copy/pasted SQL commands that you executed AND ALL the messages returned from SQL Server from the execution of the commands. Be sure to title each section of your document with the appropriate PART... that is PART 1, PART 2, and PART 3.

SCRIPT

/* FILE: FLIX2YOU_data-load.txt *//* Script to populate tables for FLIX2YOU .. current schema before revision *//* Written by Gary Heberling on July 2, 2012 *//* For IST210 world campus Penn State University */ /* This script is provided to students in IST210 for Lab # 7 */ /* Modifications: (1) Removed DELETE FROM and RESEED commands from script; 1/3/13 by glh */ /* PLEASE READ THE INSTRUCTIONS FOR LAB # 7 BEFORE EXECUTING *//* Insert data for table genre_codes */ INSERT INTO genre_codes VALUES ('Action & Adventure');INSERT INTO genre_codes VALUES ('Comedy');INSERT INTO genre_codes VALUES ('Documentary');INSERT INTO genre_codes VALUES ('Drama');INSERT INTO genre_codes VALUES ('Education');INSERT INTO genre_codes VALUES ('Foreign');INSERT INTO genre_codes VALUES ('Horror');INSERT INTO genre_codes VALUES ('Romance');INSERT INTO genre_codes VALUES ('Thriller');INSERT INTO genre_codes VALUES ('Western'); /* Insert data for table format_types */ INSERT INTO format_types values ('DVD');INSERT INTO format_types values ('Blu-Ray');INSERT INTO format_types values ('MiniDV');INSERT INTO format_types values ('VHS'); /* Insert data for table video_stores */ INSERT INTO video_stores values ('FLIX2YOU Headquarters', '1225 W LindenStreet', 'Scranton', 'PA', '18501', 'INFO_SCRANTON@FLIX2YOU.COM', 'Headquarters');INSERT INTO video_stores values ('Pittsburgh Shadyside', '4125 S Highland Ave','Pittsburgh', 'PA', '15232', 'INFO_PITTSBURGH@FLIX2YOU.COM', 'Main Pittsburgh Store'); /* Insert data for table condition_codes */ INSERT INTO condition_codes VALUES ('rental');INSERT INTO condition_codes VALUES ('used for sale');INSERT INTO condition_codes VALUES ('damaged'); /* Insert data for moives, actors and move_cast */ /* FORMAT of the insert command ---> INSERT INTO movies VALUES (condition,format, genre, store, year,'Title', 'Desc', num, rental/sale/both, daily, sale); */ /* Amazing Spider-Man */INSERT INTO movies VALUES (1, 1, 1, 1, 2012, 'The Amazing Spider-Man','The story of Peter Parker, an outcast high schooler who was abandoned by hisparents as a boy, leaving him to be raised by his Uncle Ben and Aunt May. Likemost teenagers, Peter is trying to figure out who he is and how he got to be theperson he is today. Peter is also finding his way with his first high schoolcrush, Gwen Stacy, and together, they struggle with love, commitment, andsecrets. 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. AsSpider-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 tobecome a hero.', 100, 1, 1.99, 25.39);INSERT INTO actors values ('M', 'Andrew', 'Garfield','Although born in Los Angeles, Andrew Garfield grew up in England; his mother isEnglish and they moved back there when he was three years old. He went to aprivate school, the City of London Freemen School, and began acting in youththeatre productions while he was still at school. At 19, he went to the CentralSchool of Speech and Drama.');INSERT INTO actors values ('F', 'Emma', 'Stone','Stone began acting as a child as a member of the Valley Youth Theatre inPhoenix, Arizona, where she made her stage debut in a production of KennethGrahames The Wind in the Willows. She appeared in many more productions throughher early teens until, at the age of 15, she decided that she wanted to makeacting her career.');INSERT INTO movie_cast VALUES (1,1);INSERT INTO movie_cast VALUES (1,2); /* Superbad */INSERT INTO movies VALUES (1, 1, 2, 2, 2007, 'Superbad','Two co-dependent high school seniors are forced to deal with separation anxietyafter their plan to stage a booze-soaked party goes awry.', 25, 3, .99, 10.99);INSERT INTO actors values ('M', 'Michael', 'Cera','Canadian actor Michael Cera is the middle child of a Canadian mother andItalian father, both of whom worked for Xerox. He has two sisters. He waseducated at Conestoga Public School, Robert H. Lagerquist Senior Public Schooland Heart Lake Secondary School until the ninth grade. Cera then completed hishigh school education via correspondence.');INSERT INTO actors values ('M', 'Jonah', 'Hill','Hill grew up in Los Angeles, the son of a tour accountant for Guns N Roses. Hegraduated from Crossroads School in Santa Monica and went on to The New Schoolin New York to study drama.')INSERT INTO actors values ('M', 'Christopher', 'Mintz-Plasse','Attended El Camino Real High School from 2003 to 2007. Because he was only 17at the time of filming Superbad (2007), his mother was required to be on setduring the filming of his sex scene.');INSERT INTO movie_cast VALUES (2,3);INSERT INTO movie_cast VALUES (2,4);INSERT INTO movie_cast VALUES (2,5); /* Gone with the wind */INSERT INTO movies VALUES (2, 4, 4, 2, 1939, 'Gone with the Wind','American classic in which a manipulative woman and a roguish man carry on aturbulent love affair in the American south during the Civil War andReconstruction.', 4, 2, .99, 4.99);INSERT INTO actors values ('M', 'Clark', 'Gable','Clark Gables mother died when he was seven months old. At 16 he quit highschool, went to work in an Akron Ohio tire factory and decided to become anactor after seeing the play The Bird of Paradise.');INSERT INTO actors values ('F', 'Vivien', 'Leigh','If a film were made of the life of Vivien Leigh, it would open in India justbefore World War I, where a successful British businessman could live like aprince. In the mountains above Calcutta, a little princess is born. Because ofthe outbreak of World War I, she is six years old the first time her parentstake her to England.');INSERT INTO actors values ('M', 'Thomas', 'Mitchell','Thomas Mitchell was one of the great American character actors, whose creditsread like a list of the greatest films of the 20th century');INSERT INTO movie_cast VALUES (3,6);

INSERT INTO movie_cast VALUES (3,7);INSERT INTO movie_cast VALUES (3,8); /* Fahrenheit 9/11 */INSERT INTO movies VALUES (1, 1, 3, 1, 2004, 'Fahrenheit 9/11','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 forwardits agenda for unjust wars in Afghanistan and Iraq.', 10, 3, .99, 11.99);INSERT INTO actors values ('M', 'Michael', 'Moore','Michael Moore was born in Flint, Michigan April 23, 1954, but was not raisedthere. Contrary to popular belief, he was actually raised in Davison, Michigan.He studied journalism at the University of Michigan-Flint, and also pursuedother hobbies such as gun shooting, for which he even won a competition.');INSERT INTO actors values ('M', 'George', 'Bush','The 43rd President of the United States of America, George Walker Bush was borntwo 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.');INSERT INTO actors values ('M', 'Ben', 'Affleck','Benjamin Geza Affleck was born on August 15, 1972 in Berkeley, California, USAbut 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 schoolteacher.');INSERT INTO movie_cast VALUES (4,9);INSERT INTO movie_cast VALUES (4,10);INSERT INTO movie_cast VALUES (4,11); /* The following command will cause the server to wait 5 seconds in order to besure the server has committed the previous commands */waitfor delay '0:0:5' /* Good Will Hunting */INSERT INTO movies VALUES (1, 1, 4, 2, 1997, 'Good Will Hunting','Will Hunting, a janitor at MIT, has a gift for mathematics but needs help froma psychologist to find direction in his life.', 6, 3, .59, 9.99);INSERT INTO actors values ('M', 'Robin', 'Williams','Williams briefly studied political science before enrolling at Juilliard tostudy theatre. After he left Juilliard, he performed in night clubs where he wasdiscovered for the role of Mork on an episode of Happy Days in 1974 and thesubsequent Mork & Mindy TV series in 1978.');INSERT INTO actors values ('M', 'Matt', 'Damon','Matt Damon was born in 1970. His father, Kent Damon, a tax preparer, and hismother, Nancy Carlsson-Paige, a college professor, are now divorced.');INSERT INTO movie_cast VALUES (5,12);INSERT INTO movie_cast VALUES (5,13);INSERT INTO movie_cast VALUES (5,11); /* After all data is inserted, execute the following SELECT commands to reviewthe data that has been inserted *//* select to view movie dataSELECT movie_id, movie_title, movie_description, genre_code_description,condition_code_description, format_type_description,store_name, release_year, number_in_stock, rental_daily_rate, sales_priceFROM movies, condition_codes, genre_codes, format_types, video_storesWHERE movies.condition_code = condition_codes.condition_codeAND movies.format_Type_code = format_types.format_type_codeAND movies.genre_type_code = genre_codes.genre_codeAND movies.store_id = video_stores.store_id;*/ /* select to view the movie_cast data

SELECT movies.movie_title, actor_first_name+' '+actor_last_name AS NAME,other_actor_detailsFROM movie_cast, movies, actorsWHERE movie_cast.movie_id = movies.movie_idAND movie_cast.actor_id = actors.actor_idORDER BY movie_title, actor_last_name;*/ /* END OF SCRIPT */