1. Updating Data A Guide to SQL – Chapter 6

2. Instructional Objectives • Create a table from an existing table • Commands: • UPDATE • INSERT INTO • DELETE • NULL • ALTER TABLE

3. Create a table from an existing table • First, create a table: • Create a new table named LEVEL1_CUSTOMER with following columns from CUSTOMER table: Customer_Num, Customer_Name, Balance, Credit_Limit, and Rep_Num • Then take data from CUSTOMER table and insert into LEVEL1_CUSTOMER for those customers with a credit limit of $7,500.

4. Solution…

5. Taking data from an existing table…

6. UPDATE • Change the name of customer 842 in the Level1_Customer table to All Season Sport. • Solution:

7. Another example… • For each customer represented by sales rep 20 in the Level1_Customer table and that also has a balance that does not exceed the credit limit; increase the customer’s credit limit to $8,000.

8. Solution…

9. Using Insert Into to add a new record • Add Customer number 895 to the Level1_Customer table. The name is Peter and Margaret’s, the balance is 0, the credit limit is $8,000, and the rep number is 20. • Solution:

10. DELETE • Used to delete data from a database • Syntax: DELETE FROM <tablename> WHERE condition;

11. DELETE example • Delete any row in the OrderLine table in which the part number is BV06. • Solution:

12. NULL • Change the balance of customer 725 in the Level1_Customer table to null • Solution:

13. ALTER TABLE clause • Used to change a table’s structure • Syntax: ALTER TABLE <tablename> alteration; • Examples: • Add a column ‘CustType’ to the customer table Solution:

14. Additional examples • Change the CustomerName field size to 50 • ALTER TABLE Customer Modify CustomerName char(50); • Delete the Warehouse column from the Part table • ALTER TABLE Part DELETE Warehouse;