1 / 14

Updating Data

Updating Data. A Guide to SQL – Chapter 6. Instructional Objectives. Create a table from an existing table Commands: UPDATE INSERT INTO DELETE NULL ALTER TABLE. Create a table from an existing table. First, create a table:

polly
Télécharger la présentation

Updating Data

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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;

More Related