Access SQL 36. Create a table, called Dealership2, which has the same column nam
ID: 3706442 • Letter: A
Question
Access SQL
36. Create a table, called Dealership2, which has the same column names and data types as the Dealership table. Do not include a primary key constraint. Do not include an input mask for the dzip column.
37. Create a primary index, called PrimaryKey, on the dcode column in Dealership2 Use “CREATE INDEX...” syntax.
38. Create a table, called Vehicle2, which has the same column names and data types as the Vehicle table. Do not include a primary key constraint. Do include a foreign key constraint to Dealership2 and name it ForeignKey_to_Dealership2.
39. Add a primary key constraint, called PrimaryKey, on the vvin column in Vehicle2. Use “ALTER TABLE...” syntax.
40. Create an index, called FK_to_Dealership2, on the foreign key column in Vehicle2. Use “CREATE INDEX...” syntax.
41. Using “INSERT INTO...SELECT” syntax, insert all the rows from Dealership into Dealership2.
42. Using “INSERT INTO... VALUES” syntax, insert into the Dealership2 table: 193, Asg 5 Test Dealership, 10 Main St, Albany, NY, 12203
43. Using “INSERT INTO... VALUES” syntax, insert into the Vehicle2 table: A1B2C3WXYZ0123456, 193, 2015, SUBA, sedan, blue, 2310, gas, 6
44. Change vweight to 3,210 in the row you inserted in Query43, identifying this row by using its primary key value.
45. For all Dealership2 rows with a dcity value of “Albany”, change dstate to “GA” and dzip to null.
46. Erase the single row of data inserted in Query43. Use the primary key value to pick the right row.
47. Remove the foreign key constraint in Vehicle2 which was created by Query38.
48. Drop the Vehicle2 table.
49. Erase all the data in Dealership2.
50. Drop the Dealership2 table.
» Relationships Invoice inum ieid vin icontract isold iprice Purchaser pinum pcid Employee eid edcode elast efirst emi ehire esalary estreet ecity estate ezip Vehicle win 3 vdcode Customer Dealership dcode dname dstreet dcity dstate dzip vmake cid clast cfirst cmi cstreet ccity cstate czip vweight vcylinders Relationship rdcode rcid Ready NUM LOCKExplanation / Answer
//hope this helps you. Except 37,40 and 47 i have answered other questions
36)query for creating table same as dealership table excluding primary key and dzip.
Create table dealership2 as
Select dcode, dname, dstreet, dcity, dstate, dzip from Dealership ;
38)create table vehicle2 as select vvin,vdcode, vyear, vmake, vbody, vcolor, vweight, vfuel, vcylinder from vehicle ;
39)alter table vehicle2 add primary key vvin;
41)insert into dealership2 (dcode dname, dstreet dcity, dstate, dzip ) values (select dcode , dname, dstreet dcity, dstate, dzip from Dealership group by dname) ;
42) insert into dealership2 values (193, "Asg 5 Test Dealership" , "10 Main St" , "Albany" ,"NY" , 12203) ;
43)insert into vehicle2 values ("A1B2C3WXYZ0123456" , 193, 2015, "SUBA" ," sedan" , "blue" , 2310," gas" , 6) ;
44)update vehicle2 set vweight =3210 where vvin ="A1B2C3WXYZ0123456" ;
45)update dealership2 set dstate ="GA" and dzip =null where dcity ="Albany" ;
46)delete from vehicle2 where vvin="A1B2C3WXYZ0123456 "
48)drop vehicle2 ;
49)truncate table vehicle2 ;
50)drop vehicle2