Question

We use the WMCRM database and here is the summary of the database schema (where schema is used in its meaning of a summary of the database structure):

VEHICLE (InventoryID, Model, VIN)

SALESPERSON (NickName, LastName, FirstName, HireDate, WageRate, CommissionRate, OfficePhone, EmailAddress, InventoryID)

CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, EmailAddress, NickName)

PHONE_NUMBER (CustomerID, PhoneNumber, PhoneType)

CONTACT(ContactID, CustomerID,ContactDate,ContactType,Remarks)

Where

InventoryID in SALESPERSON must exist in InventoryID in VEHICLE

NickName in CUSTOMER must exist in NickName in SALESPERSON

CustomerID in PHONE_NUMBER must exist in CustomerID in CUSTOMER

CustomerID in CONTACT must exist in CustomerID in CUSTOMER

To update the values we use UPDATE…SET statement as follows:

update customer

set LastName = 'Griffey'

where CustomerID = 3;

QUESTION 1: if you use update statement without WHERE clause, what can happen?

If we want to delete the record you use the following script:

delete

from customer

where CustomerID = 8;

QUESTION 2: If you use delete statement without WHERE clause, what can happen?

The SQL Drop Table Statement

The SQL DROP TABLE statement drops the table’s structure along with all of the table’s data. For example, to drop the contact table and all its data, you use the following SQL statement:

drop table contact

However, you can not drop customer table before drop contact table, because the SQL DROP TABLE statement does not work if the table contains or could contain values needed to fulfill referential integrity constraints.

QUESTION 3: Can you drop table salesperson? If yes, how do you do it? If no, why?

The SQL Alter Table Statement

To drop the customer table, you must first drop the phone_number table or at least delete the foreign key constraint PHONE_CUS_FK. This is one place where the ALTER TABLE command is useful. You use the SQL ALTER TABLE statement to add, modify, and drop columns and constraints. For example, you can use it to drop the PHONE_CUS_FK constraint with the statement:

alter table phone_number

drop constrain PHONE_CUS_FK;

After either dropping the phone_number table or the PHONE_CUS_FK foreign key constraint, you can then successfully drop the customer table.

Question 4: do you have another method to drop table salesperson?

Answer 1

Solution:

QUESTION 1: if you use update statement without WHERE clause, what can happen?

Answer : This will update all the records in the Customer table and set the last name of each customer to "'Griffey'".

QUESTION 2: If you use delete statement without WHERE clause, what can happen?

Answer : All the records will get deleted if we omit the WHERE condition.

QUESTION 3: Can you drop table salesperson? If yes, how do you do it? If no, why?

Answer: No, as there is a foreign key constraint existing. Following given constraint will prevent the dropping of the salesperson table as the nick name in CUSTOMER table must exist in the SALESPERSON Table.

Given : NickName in CUSTOMER must exist in NickName in SALESPERSON

Without dropping the CUSTOMER , the SALESPERSON cannot be dropped.

Question 4: do you have another method to drop table salesperson?

Answer : The SALESPERSON table can be dropped by first dropping the Foreign key Constraint. The foreign key constraint can be dropped by running the ALTER statement

alter table CUSTOMER

drop constrain SALESPERSON_CUS_FK;