Access SQL 36. Create a table, called Dealership2, which has the same column nam

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 LOCK

Explanation / Answer

--36

SELECT *

INTO Dealership2

FROM Dealership

WHERE 1 = 2

--37

CREATE INDEX PrimaryKey

ON Dealership2 (dcode);

--38

SELECT *

INTO Vehicle2

FROM Vehicle

WHERE 1 = 2;

ALTER Table Vehicle2

ADD CONSTRAINT ForeignKey_to_Dealership2 FOREIGN KEY (vdcode)

REFERENCES Dealership2(dcode);

--39

ALTER TABLE Vehicle2

ADD CONSTRAINT PK_Vehicle PRIMARY KEY (vvin);

--40

CREATE INDEX FK_to_Dealership2 ON Vehicle2(vdcode);

--41

INSERT INTO Dealership2

SELECT * FROM Dealership;

--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 TABLE Vehicle2 SET vweight=3210 WHERE vvin='A1B2C3WXYZ0123456';

--45

UPDATE TABLE Dealership2 SET dcity ='Albany' WHERE dstate ='GA' AND dzip is NULL;

--46

DELETE FROM Vehicle2

WHERE vvin='A1B2C3WXYZ0123456';

--47

ALTER TABLE Vehicle2

DROP FOREIGN KEY ForeignKey_to_Dealership2;

--48

DROP table Vehicle2;

--49

TRUNCATE TABLE Dealership2;

--50

DROP TABLE Dealership2;

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---