ICSI410 Assignment 5 Solved

Description

Write 15 separate SQL DDL or update DML queries, named “Query36” through “Query50”. Each query will contain a SQL DDL or update DML statement that is your solution for the translation of a “plain English” DDL or DML request. Each query is worth one point. The 15 “plain English” queries are:

36. Create a table, called Dealership2, which has the same column names and data types as the Dealership 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 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
49. Erase all the data in Dealership2.
50. Drop the Dealership2