Modify Table Lab Create the following tables in SQL Server (code is supplied CRE

Question

Modify Table Lab Create the following tables in SQL Server (code is supplied CREATE TABLE Customer (cust id INT PRIMARY KEY company_name VARCHAR (30), company age INT c address VARCHAR (30). c_city VARCHAR (25), c state VARCHAR (25), c zip INT ave earnings NUMERIC (8, 2), poc last _name VARCHAR (30), poc phone VARCHAR (20), poc_pin INT) CREATE TABLE sales (sales id INT PRIMARY KEY sale date DATETIME quantity INT) Do the following: 1. Add a field to the sales table (customer) and add a foreign key constraint to it. The FK is required! 2. Modify the c zip field to be a 15 character length text field 3. Change the length of the c state field to make it a length of 2 (as it should be), and make it a fixed length data type. 4. Change ave earnings to a money data type. 5. Rename the quantity field to qty bought. 6. Delete the poc_pin field 7. Drop the sales id primary key constraint (sales table). 8. Drop the sales table.

Explanation / Answer

Answers)

1. Add a field customer to sales table.

alter table sales add customer int;

Add a foreign key constraint to it.

ALTER TABLE sales
ADD FOREIGN KEY (customer) REFERENCES customer(cust_id);

Yes, the FK is required for a relationship between the customer and the sales table.

2. Modify c_zip to 15 char field.

ALTER TABLE customer
ALTER COLUMN c_zip varchar(15);

3. Change length of c_state field to length 2 and make fixed length data type.

ALTER TABLE customer
ALTER COLUMN c_state varchar(2);

4. Change ave_earnings to a money data type.

ALTER TABLE customer
ALTER COLUMN ave_earnings money;

5. Rename quantity to qty_bought.

sp_rename 'sales.quantity', 'qty_bought', 'COLUMN';

6. Delete the poc_pin field.

ALTER TABLE customer
DROP COLUMN poc_pin;

7. Drop the sales_id primary key constraint.

ALTER TABLE sales
DROP PRIMARY KEY;

8. Drop the sales table.

drop table sales;

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.