Question

Overview This lab provides you the opportunity to insert and update data with the use of SQL commands. The lab will utilize the FLIX2YOU problem, the current schema. In order to start this lab, you must have successfully completed Lab # 6. In Lab # 6, you executed a script that was provided to you. This script created 7 of the FLIX2YOU tables as documented in the Entity Relationship Diagram in the FLIX2YOU problem document. The second part of lab # 6 required you to write and execute SQL commands to create the remaining 7 tables along with primary and foreign keys for those 7 tables. 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. 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
John Smith	1235 Main Street, Dunmore, PA 18512	[email protected]
Mary Jones	235 Ardmore Blvd, Pittsburgh, PA 15221	jones_mary@ g mail.com
Sally Ryder	725 Lincoln Way, White Oak, PA 15132	[email protected]
Paul Walker	2 Clay Ave, Scranton, PA 18503	[email protected]
Daniel Murphy	5125 Forbes Ave, Pittsburgh, PA 15222	[email protected]

Five Customer Rentals:

Customer	Movie	Status	Date Out	Date Returned	Amount Due
Smith	Superbad	Active	7/2/2012		2.59
Jones	Spider-Man	Reserved
Ryder	Gone w/ Wind	Overdue	6/2/2012		15.78
Walker	Fahr 9/11	Returned	5/1/2012	5/20/2012	5.60
Murphy	Good Will	Active	7/10/2012		4.56

Five Financial Transactions:

Customer	Movie	Type	Pay Method	Amount	Date
Smith	Superbad	Payment	Cash	2.59	7/2/2012
Jones	Spider-Man	Deposit	Credit Card	10.00	7/10/2012
Ryder	Gone w/ Wind	Payment	PayPal	15.78	6/2/2012
Walker	Fahr 9/11	Payment	Check	15.60	5/10/2012
Walker	Fahr 9/11	Refund	Check	10.00	6/30/2012

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":

1. Murphy's zip code should be 15213.
2. Walker did not rent Fahrenheit 9/11 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 PROVIDED*

/* 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 Linden Street', 'Scranton',
   'PA', '18501', '[email protected]', 'Headquarters');
INSERT INTO video_stores values ('Pittsburgh Shadyside', '4125 S Highland Ave', 'Pittsburgh',
   'PA', '15232', '[email protected]', '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 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.',
 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 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.');
INSERT INTO actors values ('F', 'Emma', 'Stone', 
'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.');
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 anxiety after 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 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.');
INSERT INTO actors values ('M', 'Jonah', 'Hill', 
'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.')
INSERT INTO actors values ('M', 'Christopher', 'Mintz-Plasse',
'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.');
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 a turbulent love affair in the American south during the Civil War and Reconstruction.',
 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 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.');
INSERT INTO actors values ('F', 'Vivien', 'Leigh', 
'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.');
INSERT INTO actors values ('M', 'Thomas', 'Mitchell',
'Thomas Mitchell was one of the great American character actors, whose credits read 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 forward its 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 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.');
INSERT INTO actors values ('M', 'George', 'Bush', 
'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.');
INSERT INTO actors values ('M', 'Ben', 'Affleck',
'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.');
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 be sure 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 from a 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 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.');
INSERT INTO actors values ('M', 'Matt', 'Damon', 
'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.');
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 review the data that has been inserted */
/* select to view movie data
SELECT 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_price 
FROM movies, condition_codes, genre_codes, format_types, video_stores
WHERE movies.condition_code = condition_codes.condition_code 
AND   movies.format_Type_code = format_types.format_type_code 
AND   movies.genre_type_code = genre_codes.genre_code
AND   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_details
FROM movie_cast, movies, actors
WHERE movie_cast.movie_id = movies.movie_id
AND   movie_cast.actor_id = actors.actor_id
ORDER BY movie_title, actor_last_name;
*/

/*  END OF SCRIPT */

Answer 1

• Part-1:

  
  drop table genre_codes; drop table format_types; drop table video_stores; drop table condition_codes; drop table actors; drop table movies; drop table movie_cast; CREATE TABLE genre_codes( genre_code int IDENTITY(1,1) NOT NULL, genre_code description varchar (32) NOT NULL); * format_types */ CREATE TABLE format types ( format type_code int IDENTITY (1,1) NOT NULL, format type_description varchar (32) NOT NULL); /*video stores / CREATE TABLE video stores ( store_id int IDENTITY (1,1) NOT NULL, store_name varchar (32) NOT NULL, store_address varchar (128) NOT NULL, store_city varchar (32) NOT NULL, store_state varchar (2) NOT NULL, store_zip varchar (12) NOT NULL, store_email varchar (128) NOT NULL, other_store details varchar (512) NOT NULL); /* condition_code */ CREATE TABLE condition_codes ( condition_code int IDENTITY (1,1) NOT NULL, condition_code_description varchar (32) NOT NULL); /* actors / CREATE TABLE actors( actor_id int IDENTITY (1,1) NOT NULL, actor_gender char (1) NOT NULL, actor_first_name varchar (32) NOT NULL, actor_last_name varchar (32) NOT NULL, other_actor_details varchar (512) NOT NULL); /* movies/
  

  
  CREATE TABLE movies( movie_id int IDENTITY (1,1) NOT NULL condition_code int NOT NULL, format tvpe code int NOT NULL genre type code int NOT NULL store_id int NOT NULL, release_year int NOT NULL, movie_title varchar (128) NOT NULL, movie_description varchar (1024) NOT NULL, number in stock int NOT NULL rental_or_sale_or_both tinyint NOT NULL, rental_daily_rate money NOT NULL, sales_price money NOT NULL); /* movie_cast / CREATE TABLE movie_cast( movie id int NOT NULL actor_id int NOT NULL);
  

  Sample Outout:

  
  Command (s) completed successfully
  

  
  1 / create primary keys with ALTER TABLE statement / 2 EALTER TABLE genre_codes ADD CONSTRAINT pk_genre_codes PRIMARY KEY (genre_code); 3 ALTER TABLE format types ADD CONSTRAINT pk format types PRIMARY KEY (format type code; 4 ALTER TABLE video stores ADD CONSTRAINT pk video stores PRIMARY KEY (store id); ALTER TABLE condition_codes ADD CONSTRAINT pk condition_codes PRIMARY KEY (condition_code); 6 ALTER TABLE actors ADD CONSTRAINT pk actors PRIMARY KEY (actor id); 7 ALTER TABLE movies ADD CONSTRAINT pk_movies PRIMARY KEY (movie_id); 8 ALTER TABLE movie-cast ADD CONSTRAINT pk-movie-cast PRIMARY KEY (movie-id, actor-id); 9 Eend of primary key creation / 10 11 create foreign keys/ 12 EALTER TABLE movie cast ADD CONSTRAINT fk Movie cast actors FOREIGN KEY (actor id) 13 REFERENCES actors (actorid); 14 EALTER TABLE movie cast ADD CONSTRAINT fk movie cast movies FOREIGN KEY (movie id) 15 REFERENCES movies (movie_id); 16白ALTER TABLE movies ADD CONSTRAINT fk movies condition codes FOREIGN KEY(condition code) 17 REFERENCES condition_codes (condition_code); 18 ALTER TABLE movies ADD CONSTRAINT fk movies format types FOREIGN KEY(format type code) 19 REFERENCES format types (format_type_code); 20 EALTER TABLE movies ADD CONSTRAINT fk movies_genre_codes FOREIGN KEY (genre_type_code) 21 REFERENCES genre_codes (genre_code); 22 EALTER TABLE movies ADD CONSTRAINT fk movies_video Stores FOREIGN KEY (store_id) 23 REFERENCES video_Stores (store_id); T-SQL 國Message! (1 row (s) affected) (1 row (s) affected) (1 row (s) affected) (1 row (s) affected) (1 row (s) affected) (1 row (s) affected) (1 row (s) affected) (1 row (s) affected)
  

  
  /* 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 Linden Street', 'Scranton', PA 18501 INFO_SCRANTON@FLIX2YOU. COM', Headquarters'); INSERT INTO video_stores values (Pittsburgh Shadyside, '4125 S Highland Ave, 'Pittsburgh [email protected], Main Pittsburgh Store') /Insert data for table condition codes * INSERT INTO condition_codes VALUES ("rental'); INSERT INTO condition_ r sale"); INSERT INTO condition_codes VALUES ('damaged') codes VALUES ('used fo '. Insert data for moives, actors and move-cast /* FORMAT of the insert command INSERT INTO movies VALUES (condition, format, genre, store, year, Title', "Desc', nun, 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 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. 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 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.); INSERT INTO actors values (F Emma 'Stone 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.; 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 anxiety after 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 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.; INSERT INTO actors values (M, 'Jonah 'Hill', 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. ') INSERT INTO actors values ('M', 'Christopher', 'Mintz-Plasse', 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. ') 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 a turbulent love affair in the American south during the Civil War and Reconstruction. 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 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. ); INSERT INTO actors values F, 'Vivien', 'Leigh' '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.); INSERT INTO actors values ('M 'Thomas', 'Mitchell' Thomas Mitchell was one of the great American character actors, whose credits read 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 forward its 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 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.'; INSERT INTO actors values 'M, 'George, 'Bush', 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. '); INSERT INTO actors values 'M Ben, 'Affleck, 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.) 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 be sure 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 from a psychologist to find direction in his life., 6, 3, .59, 9.99); INSERT INTO actors values ("М., . Robin., ·Williams', 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 Morlk on an episode of Happy Days in 1974 and the subsequent 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 his mother, 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 review the data that has been inserted */ /* select to view movie data*/ SELECT 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_price FROM movies, condition_codes, genre_codes, format_types, video_stores WHERE movies.condition_code condition_codes.condition_code AND movies.format_Type_code format_types.format_type_code AND movies.genre_typecode genre_codes.genre_code AND movies.store id -video_stores.store id;
  

  
  / After all data is inserted, execute the following SELECT commands to review the data that has been inserted */ /* select to view movie data*/ SELECT 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_price FROM movies, condition_codes, genre_codes, format_types, video_stores WHERE movies.condition_code condition_codes.condition_code AND movies.format_Type_code format_types.format_type_code AND movies.genre_typecode genre_codes.genre_code AND movies.store id -video_stores.store id;
  

  
  release year number in tock rerta_daiy ele eles_price The Ameing Spider-MaThe tory of Peler Perker, an outcast hich schooler Action & Adventure DVD DVD FLIXZYDU Headquaters 2012 Prtsburgh Shadyside 2007 Pmtsburgh Shadyside 1939 FLIX2YDU Headouters 2004 Pntaburgh Shadyaide 1997 100 1.59 0.55 0.99 0.99 0.59 25.39 10.99 4 99 11.99 Two co-dependert high schcol sericrs are forced to... Comedy ental 25 Gore wth the Wnd Fahrerhet 9/11 Good WIl Hunting used for ssle Mchael Macres view on what happened to the Unt MI Hrong. apritor at MIT. haa ฮ gftfor mathemat Dccumertay Drama DVD 10 DVD
  

  
  /*select to view the movie cast data/ SELECT movies .movie-title, actor-first-name+· 'tactor-last-name AS NAME, other-actor, details FROM movie_cast, movies, actors WHERE movie cast.movie id movies.movie id AND movie cast . actor id = actors . actor id ORDER BY movie title, actor last name: /* END OF SCRIPT /
  

  
  movie_title Fahrenheit 9/11 Fahrenheit 9/11 George Bush The 43rd President of the United States of Amenica, Fahrenheit 9/11 Michael Moore Michael Moore was bom in Flint, Michigan April 23. Gone with the Wind Clark Gable Gone with the Wind Vivien Leigh If a film were made of the life of Vivien Leigh, it woul. Gone with the Wind Thomas Mitchell Thomas Mitchell was one of the great American cha. Good Will Hunting Ben Affleck min Geza Affleck was bom on August 15, 197 Good Will Hunting Matt DamonMatt Damon was bom in 1970. His father. Kent Da. Good Will Hunting Robin Williams Williams briefly studied political science before enroll. Superbad Superbad Superbad The Amazing Spid... Andrew Garfield Although bom in Los Angeles, Andrew Garfield grew.. The Amazing Spid... Emma StoneStone began acting as a child as a member of the V.. NAME other_actor _details Ben Affleck Benjamin Geza Affleck was bom on August 15, 197 Clark Gables mother died when he was seven mont.. Michael Cer Canadian actor Michael Cera is the middle child of a Jonah Hill Christopher Mi.. AttendedEl Camino Real High School from 2003 to Hill grew up in Los Angeles, the son of a tour accou..
  

  Query to copy:

  drop table genre_codes;
  drop table format_types;
  drop table video_stores;
  drop table condition_codes;
  drop table actors;
  drop table movies;
  drop table movie_cast;
  CREATE TABLE genre_codes(
          genre_code int IDENTITY(1,1) NOT NULL,
          genre_code_description varchar(32) NOT NULL);

  /* format_types */
  CREATE TABLE format_types(
          format_type_code int IDENTITY(1,1) NOT NULL,
          format_type_description varchar(32) NOT NULL);
  /* video_stores */
  CREATE TABLE video_stores(
          store_id int IDENTITY(1,1) NOT NULL,
          store_name varchar(32) NOT NULL,
          store_address varchar(128) NOT NULL,
          store_city varchar(32) NOT NULL,
          store_state varchar(2) NOT NULL,
          store_zip varchar(12) NOT NULL,
          store_email varchar(128) NOT NULL,
          other_store_details varchar(512) NOT NULL);
  /* condition_code */
  CREATE TABLE condition_codes(
          condition_code int IDENTITY(1,1) NOT NULL,
          condition_code_description varchar(32) NOT NULL);
  /* actors */
  CREATE TABLE actors(
          actor_id int IDENTITY(1,1) NOT NULL,
          actor_gender char(1) NOT NULL,
          actor_first_name varchar(32) NOT NULL,
          actor_last_name varchar(32) NOT NULL,
          other_actor_details varchar(512) NOT NULL);
  /* movies */
  CREATE TABLE movies(
          movie_id int IDENTITY(1,1) NOT NULL,
          condition_code int NOT NULL,
          format_type_code int NOT NULL,
          genre_type_code int NOT NULL,
          store_id int NOT NULL,
          release_year int NOT NULL,
          movie_title varchar(128) NOT NULL,
          movie_description varchar(1024) NOT NULL,
          number_in_stock int NOT NULL,
          rental_or_sale_or_both tinyint NOT NULL,
          rental_daily_rate money NOT NULL,
          sales_price money NOT NULL);
  /* movie_cast */
  CREATE TABLE movie_cast(
          movie_id int NOT NULL,
          actor_id int NOT NULL);

  
  /* create primary keys with ALTER TABLE statement */
  ALTER TABLE genre_codes ADD CONSTRAINT pk_genre_codes PRIMARY KEY (genre_code);
  ALTER TABLE format_types ADD CONSTRAINT pk_format_types PRIMARY KEY (format_type_code);
  ALTER TABLE video_stores ADD CONSTRAINT pk_video_stores PRIMARY KEY (store_id);
  ALTER TABLE condition_codes ADD CONSTRAINT pk_condition_codes PRIMARY KEY (condition_code);
  ALTER TABLE actors ADD CONSTRAINT pk_actors PRIMARY KEY (actor_id);
  ALTER TABLE movies ADD CONSTRAINT pk_movies PRIMARY KEY (movie_id);
  ALTER TABLE movie_cast ADD CONSTRAINT pk_movie_cast PRIMARY KEY (movie_id, actor_id);
  /* end of primary key creation */

  /* create foreign keys */
  ALTER TABLE movie_cast ADD CONSTRAINT fk_Movie_cast_actors FOREIGN KEY(actor_id)
  REFERENCES actors (actor_id);
  ALTER TABLE movie_cast ADD CONSTRAINT fk_movie_cast_movies FOREIGN KEY(movie_id)
  REFERENCES movies (movie_id);
  ALTER TABLE movies ADD CONSTRAINT fk_movies_condition_codes FOREIGN KEY(condition_code)
  REFERENCES condition_codes (condition_code);
  ALTER TABLE movies ADD CONSTRAINT fk_movies_format_types FOREIGN KEY(format_type_code)
  REFERENCES format_types (format_type_code);
  ALTER TABLE movies ADD CONSTRAINT fk_movies_genre_codes FOREIGN KEY(genre_type_code)
  REFERENCES genre_codes (genre_code);
  ALTER TABLE movies ADD CONSTRAINT fk_movies_video_Stores FOREIGN KEY(store_id)
  REFERENCES video_Stores (store_id);

  
  /* 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 Linden Street', 'Scranton',
     'PA', '18501', '[email protected]', 'Headquarters');
  INSERT INTO video_stores values ('Pittsburgh Shadyside', '4125 S Highland Ave', 'Pittsburgh',
     'PA', '15232', '[email protected]', '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 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.',
  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 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.');
  INSERT INTO actors values ('F', 'Emma', 'Stone',
  '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.');
  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 anxiety after 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 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.');
  INSERT INTO actors values ('M', 'Jonah', 'Hill',
  '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.')
  INSERT INTO actors values ('M', 'Christopher', 'Mintz-Plasse',
  '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.');
  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 a turbulent love affair in the American south during the Civil War and Reconstruction.',
  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 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.');
  INSERT INTO actors values ('F', 'Vivien', 'Leigh',
  '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.');
  INSERT INTO actors values ('M', 'Thomas', 'Mitchell',
  'Thomas Mitchell was one of the great American character actors, whose credits read 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 forward its 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 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.');
  INSERT INTO actors values ('M', 'George', 'Bush',
  '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.');
  INSERT INTO actors values ('M', 'Ben', 'Affleck',
  '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.');
  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 be sure 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 from a 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 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.');
  INSERT INTO actors values ('M', 'Matt', 'Damon',
  '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.');
  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 review the data that has been inserted */
  /* select to view movie data*/
  SELECT 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_price
  FROM movies, condition_codes, genre_codes, format_types, video_stores
  WHERE movies.condition_code = condition_codes.condition_code
  AND   movies.format_Type_code = format_types.format_type_code
  AND   movies.genre_type_code = genre_codes.genre_code
  AND   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_details
  FROM movie_cast, movies, actors
  WHERE movie_cast.movie_id = movies.movie_id
  AND   movie_cast.actor_id = actors.actor_id
  ORDER BY movie_title, actor_last_name;

  /* END OF SCRIPT */

  ----------------------------------------

  Part-2:

  

  drop table customer_rentals; drop table financial_transactions; drop table customers; drop table accounts; drop table rental_status_codes; drop table transaction types; drop table payment_methods; /* Create customer_rentals Table*/ CREATE TABLE customer_rentals ( item_rental_id int IDENTITY (1,1) NOT NULL, customer_id int NOT NULL, movie id int NOT NULL, rental status_code int NOT NULL, rental_date out Date NOT NULL, rental_date_returned date NOT NULL, rental_amount_due money NOT NULL, other_rental_details varchar (512) NOT NULL); /* Create financial_transactions Table / CREATE TABLE financial_transactions ( transaction_id int IDENTITY (1,1) NOT NULL, account id int NOT NULL, item_rental_id int NOT NULL, previous_transaction_id int NOT NULL, transaction type code int NOT NULL transaction_date date NOT NULL, transaction_amount money NOT NULL, transaction_comment varchar (512) NOT NULL); /Create Customer Table / CREATE TABLE customers ( customer id int IDENTITY (1,1) NOT NULL, member_vn int NOT NULL, membership_number int NOT NULL, date_became member date NOT NULL, customer first_name varchar (32) NOT NULL, customer_last_name varchar (32) NOT NULL, customer_address varchar (128) NOT NULL, customer phone int NOT NULL, customer_email varchar (128) NOT NULL, customer_dob date NOT NULL);

  

  /Create accounts Table / CREATE TABLE accounts( account_id int IDENTITY (1,1) NOT NULL, customer_id int NOT NULL payment_method_code int NOT NULL account_name varchar (32) NOT NULL, account_details varchar (128) NOT NULL) /* Create rental status_codes Table/ CREATE TABLE rental status_codes ( rental_status_code int IDENTITY (1,1) NOT NULL, rental status_description varchar (32) NOT NULL); /* Create transaction_types Table */ CREATE TABLE transaction_types ( transaction_type_code int IDENTITY (1,1) NOT NULL, transaction type description varchar (32) NOT NULL): /CREATE payment_methods Table / CREATE TABLE payment_methods( payment method_code int IDENTITY (1,1) NOT NULL, payment method_description varchar (32) NOT NULL); /* create primary keys with ALTER TABLE statement */ ALTER TABLE customer_rentals ADD CONSTRAINT pkitem1_ rental id PRIMARY KEY (item_rental id); ALTER TABLE financial_transactions ADD CONSTRAINT pk_transaction_id PRIMARY KEY (transaction_id) ALTER TABLE customers ADD CONSTRAINT pk_customer id PRIMARY KEY (customer_id); ALTER TABLE accounts ADD CONSTRAINT pk account id PRIMARY KEY (account id): ALTER TABLE rental status codes ADD CONSTRAINT pk rental status code PRIMARY KEY (rental status code): ALTER TABLE transaction types ADD CONSTRAINT pk transaction type code PRIMARY KEY (transaction type code); ALTER TABLE payment methods ADD CONSTRAINT pk payment method code PRIMARY KEY (payment method code)

  

  INSERT INTO customers VALUES (e, NULL, NULL, Tom Smith, "700 N Blakely St, Dunmore, PA 18512 NULL, [email protected] NULL) INSERT INTO customers VALUES (0, NULL, NULL, Denise Jones649 Penn Ave, Pittsburgh, PA 15222, NULL, '[email protected], NULL) INSERT INTO customers VALUES(0, NULL, NULL, Beth Rydr '2525 Monroeville Blvd, Monroeville, PA 15146 NULL, [email protected] NULL) INSERT INTO customers VALUES(0, NULL, NULL, 'Mike Walker' 309 N Washington Ave, Scranton, PA 18503, NULL, '[email protected] NULL) INSERT INTO customers VALUES (e, NULL, NULL Jonah Murphy 100 Perry Hwy, Zelienople, PA 16863 NULL, [email protected], NULL) INSERT INTO rental_status_codes VALUES Reserved) INSERT INTO rental_status_codes VALUES('Active) INSERT INTO rental_status_codes VALUES( 'Overdue) INSERT INTO rental status_codes VALUES( Returned) INSERT INTO customer_rentals VALUES (1, 2, 5 '2017-10-02, NULL, 4.59, NULL) INSERT INTO customer_rentals VALUES (4, 3, 4, NULL, NULL, NULL, NULL) INSERT INTO customer_rentals VALUES (3, 1, 6, '2017-08-03, NULL, 28.78, NULL) INSERT INTO customer-rentals VALUES(6, 4, 7, '2017-06-10', '2017-06-20., 8.60, NULL) INSERT INTO customer_rentals VALUES (5, 5, 5, 2017-07-10, NULL, 3.56, NULL) INSERT INTO payment methods VALUES( Cash') INSERT INTO payment methods VALUES( Credit Card) INSERT INTO payment methods VALUES( Check) INSERT INTO payment methods VALUES( PayPal') INSERT INTO transaction types VALUES Payment') INSERT INTO transaction_types VALUES( 'Deposit' INSERT INTO transaction types VALUES ( 'Refund) INSERT INTO accounts VALUES (1, 1, Smith 'Account of Tom Smith) INSERT INTO accounts VALUES (3, 2, Jones 'Account of Denise Jones) INSERT INTO accounts VALUES (4, 4, Ryder Account of Beth Ryder) INSERT INTO accounts VALUES (5, 3, Walker, Account of Mike Walker) INSERT INTO accounts VALUES (6, 1, Murphy 'Account of Jonah Murphy)

  

  INSERT INTO financial transactions VALUES (1, 2, 0, 1, '2017-10-02 "no comment' 'no comment 4 INSERT INTO financial_transactions VALUES (2, 1, 0, 2, '2017-08-03 INSERT INTO financial_transactions VALUES (3, 2, 0, 1, '2017-06-02 no comment) INSERT INTO financial_transactions VALUES (4, 2, , 1 2017-10-02 'no comment) INSERT INTO financial_transactions VALUES (4, 2, 0, 3, '2017-07-30, 'overpayment) tt / EBResults) Message (1 row(s) affected) (1 row (s) affected) (1 row(s) affected) (1 row (s) affected) (1 row(s) affected) (1 row(s) affected)

  Query to copy:

  part-2:

  drop table customer_rentals ;
  drop table financial_transactions;
  drop table customers;
  drop table accounts;
  drop table rental_status_codes;
  drop table transaction_types;
  drop table payment_methods;
  /* Create customer_rentals Table */
  CREATE TABLE customer_rentals (

                  item_rental_id int IDENTITY(1,1) NOT NULL,

                  customer_id int NOT NULL,

                  movie_id int NOT NULL,

                  rental_status_code int NOT NULL,

                  rental_date_out Date NOT NULL,

                  rental_date_returned date NOT NULL,

                  rental_amount_due money NOT NULL,

                  other_rental_details varchar(512) NOT NULL);

  /* Create financial_transactions Table */

  CREATE TABLE financial_transactions (

                  transaction_id int IDENTITY(1,1) NOT NULL,

                  account_id int NOT NULL,

                  item_rental_id int NOT NULL,

                  previous_transaction_id int NOT NULL,

                  transaction_type_code int NOT NULL,

                  transaction_date date NOT NULL,

                  transaction_amount money NOT NULL,

                  transaction_comment varchar(512) NOT NULL);

  /* Create Customer Table */

  CREATE TABLE customers (

                  customer_id int IDENTITY(1,1) NOT NULL,

                  member_vn int NOT NULL,

                  membership_number int NOT NULL,

                  date_became_member date NOT NULL,

                  customer_first_name varchar(32) NOT NULL,

                  customer_last_name varchar(32) NOT NULL,

                  customer_address varchar(128) NOT NULL,

                  customer_phone int NOT NULL,

                  customer_email varchar(128) NOT NULL,

                  customer_dob date NOT NULL);

  /* Create accounts Table */

  CREATE TABLE accounts (

                  account_id int IDENTITY(1,1) NOT NULL,

                  customer_id int NOT NULL,

                  payment_method_code int NOT NULL,

                  account_name varchar(32) NOT NULL,

                  account_details varchar(128) NOT NULL);

  /* Create rental_status_codes Table */

  CREATE TABLE rental_status_codes (

                  rental_status_code int IDENTITY(1,1) NOT NULL,

                  rental_status_description varchar(32) NOT NULL);

  /* Create transaction_types Table */

  CREATE TABLE transaction_types (

                  transaction_type_code int IDENTITY(1,1) NOT NULL,

                  transaction_type_description varchar(32) NOT NULL);

  /* CREATE payment_methods Table */

  CREATE TABLE payment_methods (

                  payment_method_code int IDENTITY (1,1) NOT NULL,

                  payment_method_description varchar(32) NOT NULL);

  /* create primary keys with ALTER TABLE statement */

  ALTER TABLE customer_rentals ADD CONSTRAINT pk_item1_rental_id PRIMARY KEY (item_rental_id);

  ALTER TABLE financial_transactions ADD CONSTRAINT pk_transaction_id PRIMARY KEY (transaction_id);

  ALTER TABLE customers ADD CONSTRAINT pk_customer_id PRIMARY KEY (customer_id);

  ALTER TABLE accounts ADD CONSTRAINT pk_account_id PRIMARY KEY (account_id);

  ALTER TABLE rental_status_codes ADD CONSTRAINT pk_rental_status_code PRIMARY KEY (rental_status_code);

  ALTER TABLE transaction_types ADD CONSTRAINT pk_transaction_type_code PRIMARY KEY (transaction_type_code);

  ALTER TABLE payment_methods ADD CONSTRAINT pk_payment_method_code PRIMARY KEY (payment_method_code);

  
  INSERT INTO customers VALUES(0, NULL, NULL, 'Tom', 'Smith', '700 N Blakely St, Dunmore, PA 18512', NULL, 'Tom18512hotmail.com', NULL)
  INSERT INTO customers VALUES(0, NULL, NULL, 'Denise', 'Jones', '649 Penn Ave, Pittsburgh, PA 15222', NULL, 'jones_denisegmrail.com', NULL)
  INSERT INTO customers VALUES(0, NULL, NULL, 'Beth', 'Ryder', '2525 Monroeville Blvd, Monroeville, PA 15146', NULL, 'crazy_bethyahoo.com', NULL)
  INSERT INTO customers VALUES(0, NULL, NULL, 'Mike', 'Walker', '309 N Washington Ave, Scranton, PA 18503', NULL, '[email protected]', NULL)
  INSERT INTO customers VALUES(0, NULL, NULL, 'Jonah', 'Murphy', '100 Perry Hwy, Zelienople, PA 16063', NULL, '[email protected]', NULL)

  INSERT INTO rental_status_codes VALUES('Reserved')
  INSERT INTO rental_status_codes VALUES('Active')
  INSERT INTO rental_status_codes VALUES('Overdue')
  INSERT INTO rental_status_codes VALUES('Returned')

  INSERT INTO customer_rentals VALUES(1, 2, 5, '2017-10-02', NULL, 4.59, NULL)
  INSERT INTO customer_rentals VALUES(4, 3, 4, NULL, NULL, NULL, NULL)
  INSERT INTO customer_rentals VALUES(3, 1, 6, '2017-08-03', NULL, 28.78, NULL)
  INSERT INTO customer_rentals VALUES(6, 4, 7, '2017-06-10', '2017-06-20', 8.60, NULL)
  INSERT INTO customer_rentals VALUES(5, 5, 5, '2017-07-10', NULL, 3.56, NULL)

  INSERT INTO payment_methods VALUES('Cash')
  INSERT INTO payment_methods VALUES('Credit Card')
  INSERT INTO payment_methods VALUES('Check')
  INSERT INTO payment_methods VALUES('PayPal')

  INSERT INTO transaction_types VALUES('Payment')
  INSERT INTO transaction_types VALUES('Deposit')
  INSERT INTO transaction_types VALUES('Refund')

  INSERT INTO accounts VALUES(1, 1, 'Smith', 'Account of Tom Smith')
  INSERT INTO accounts VALUES(3, 2, 'Jones', 'Account of Denise Jones')
  INSERT INTO accounts VALUES(4, 4, 'Ryder', 'Account of Beth Ryder')
  INSERT INTO accounts VALUES(5, 3, 'Walker', 'Account of Mike Walker')
  INSERT INTO accounts VALUES(6, 1, 'Murphy', 'Account of Jonah Murphy')

  INSERT INTO financial_transactions VALUES(1, 2, 0, 1, '2017-10-02', 'no comment')
  INSERT INTO financial_transactions VALUES(2, 1, 0, 2, '2017-08-03', 'no comment')
  INSERT INTO financial_transactions VALUES(3, 2, 0, 1, '2017-06-02', 'no comment')
  INSERT INTO financial_transactions VALUES(4, 2, 0, 1, '2017-10-02', 'no comment')
  INSERT INTO financial_transactions VALUES(4, 2, 0, 3, '2017-07-30', 'overpayment')

  ----------------------------------

  Part-3:

  

  UPDATE customers SET customer-address = '309 N Washington Ave, Scranton, PA 18502. WHERE customer last name LIKE 'Walker" 차 國Message! w()affected)

  UPDATE customers
  SET customer_address = '309 N Washington Ave, Scranton, PA 18502'
  WHERE customer_last_name LIKE 'Walker'