SQL EXERCISE EDITING DATA 1) Use insert statements to add the following records
ID: 3879261 • Letter: S
Question
SQL EXERCISE
EDITING DATA
1) Use insert statements to add the following records to each table BELOW. You'll need to find the identity value created for new records in tables on the one-side of relationships (for example, Operator ID #1 in the entries for Runs refers to the first operator in the list, not a particular identity value). (8 points)
After completing the above entries:
2.In the Trips table, change Capital Mall to Westfield Shopping Mall using an Update command for all trips with an effective date after 10/1/04. (4 points)
3.Change the model year for all phantoms to 1994 using an Update command. (4 points)
4.Delete the 9/1/04 9:15am departure from Yelm using a Delete command. (4 points)
5.Delete operator 4. Don't forget referential integrity, but do not delete any trips or vehicles. (5 points)
Operators Hire Date 1/1/01 1/10/01 2/15/02 6/15/02 8/19/03 5/5/04 Seniority First Name David Tony Shirley John Tanya Peter Last Name Letterman Bennet Temple Adams Tucker Parker 4 6Explanation / Answer
1.
insert into Operators values(1,'David','Letterman',1/1/01)
insert into Operators values(2,'Tony','Bennet',1/10/01)
insert into Operators values(3,'Shirley','Temple',2/15/02)
insert into Operators values(4,'John','Adams',6/15/02)
insert into Operators values(5,'Tanya','Tucker',8/19/03)
insert into Operators values(6,'Peter','Parker',5/5/04)
//Assuming Vehicle id is an auto increment value
insert into Vehicles values('Gillig','40 foot',1998,3/20/98)
insert into Vehicles values('Gillig','40 foot',1998,3/20/98)
insert into Vehicles values('Gillig','40 foot',1998,3/20/98)
insert into Vehicles values('Gillig','40 foot',1998,3/20/98)
insert into Vehicles values('Gillig','Phantom',1992,11/15/02)
insert into Vehicles values('Gillig','Phantom',1992,11/15/02)
insert into Runs values(1,9/1/04)
insert into Runs values(2,9/1/04)
insert into Runs values(3,9/1/04)
insert into Runs values(4,9/1/04)
insert into Runs values(5,9/1/04)
insert into Runs values(1,1/1/05)
insert into Runs values(2,1/1/05)
insert into Runs values(3,1/1/05)
insert into Runs values(4,1/1/05)
insert into Runs values(5,1/1/05)
insert into Runs values(6,1/1/05)
insert into Schedule values(1,6)
insert into Schedule values(2,5)
insert into Schedule values(3,2)
insert into Schedule values(4,3)
insert into Schedule values(5,4)
insert into Schedule values(6,1)
insert into Schedule values(7,2)
insert into Schedule values(8,3)
insert into Schedule values(9,4)
insert into Schedule values(10,5)
insert into Schedule values(11,6)
insert into Trips values(1,15,'Olympia',8:00 AM,'L&I',8:52 AM,9/1/04)
insert into Trips values(1,15,'L&I',9:00 AM,'Olympia',9:49 AM,9/1/04)
insert into Trips values(2,15,'Olympia',8:30 AM,'L&I',9:22 AM,9/1/04)
insert into Trips values(2,15,'L&I',9:30 AM,'Olympia',10:29 AM,9/1/04)
insert into Trips values(3,94,'Lacey',7:00 AM,'Yelm',8:11 AM,9/1/04)
insert into Trips values(4,94,'Lacey',8:00 AM,'Yelm',9:11 AM,9/1/04)
insert into Trips values(3,94,'Yelm',8:15 AM,'Lacey',9:26 AM,9/1/04)
insert into Trips values(4,94,'Yelm',9:15 AM,'Lacey',10:26 AM,9/1/04)
insert into Trips values(5,44,'Olympia',7:30 AM,'Capital Mall',8:19 AM,9/1/04)
insert into Trips values(6,44,'Olympia',8:00 AM,'Capital Mall',8:49 AM,9/1/04)
insert into Trips values(5,44,'Capital Mall',8:30 AM,'Olympia',9:21 AM,9/1/04)
insert into Trips values(6,44,'Capital Mall',9:00 AM,'Capital Mall',9:51 AM,9/1/04)
insert into Trips values(1,15,'Olympia',8:00 AM,'L&I',8:52 AM,1/1/05)
insert into Trips values(1,15,'L&I',9:00 AM,'Olympia',9:49 AM,1/1/05)
insert into Trips values(2,15,'Olympia',8:30 AM,'L&I',9:22 AM,1/1/05)
insert into Trips values(2,15,'L&I',9:30 AM,'Olympia',10:29 AM,1/1/05)
insert into Trips values(3,94,'Lacey',7:00 AM,'Yelm',8:11 AM,1/1/05)
insert into Trips values(4,94,'Lacey',8:00 AM,'Yelm',9:11 AM,1/1/05)
insert into Trips values(3,94,'Yelm',8:15 AM,'Lacey',9:26 AM,1/1/05)
insert into Trips values(4,94,'Yelm',9:15 AM,'Lacey',10:26 AM,1/1/05)
insert into Trips values(5,44,'Olympia',7:30 AM,'Capital Mall',8:19 AM,1/1/05)
insert into Trips values(6,44,'Olympia',8:00 AM,'Capital Mall',8:49 AM,1/1/05)
insert into Trips values(5,44,'Capital Mall',8:30 AM,'Olympia',9:21 AM,1/1/05)
insert into Trips values(6,44,'Capital Mall',9:00 AM,'Capital Mall',9:51 AM,1/1/05)
2.In the Trips table, change Capital Mall to Westfield Shopping Mall using an Update command for all trips with an effective date after 10/1/04.
update Trips set Starts At='Capital Mall' and Ends at='Westfield Shopping Mall' where Effective>'10/1/04'
3.Change the model year for all phantoms to 1994 using an Update command.
update Vehicles set Year=1994 where Model='Phantom'
4.Delete the 9/1/04 9:15am departure from Yelm using a Delete command.
delete from Trips where Effective=9/1/04 and Starts at='Yelm' and Start Time=9:15 AM
5..Delete operator 4. Don't forget referential integrity, but do not delete any trips or vehicles.
delete from Operators where Seniority=4
If you want to delete the parent, but keep the children, you need to drop the foreign key constraint, or set the constraint to be 'ON DELETE SET NULL'. If you set 'ON DELETE SET NULL', then when you delete the parent record, the child records will remain, but the foreign key column value will be set to NULL.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.