Data Management Commands in SQL

1. Chapter 5Updating Data

2. Objectives • Create a new table from an existing table • Change data using the UPDATE command • Add new data using the INSERT command • Use the COMMIT and ROLLBACK commands to make permanent data updates or to reverse updates

3. Objectives • Understand transactions and the role of COMMIT and ROLLBACK in supporting transactions • Delete data using the DELETE command • Use nulls in UPDATE commands • Change the structure of an existing table • Drop a table

4. Creating a New Table from an Existing Table • It is possible to create a new table from data in an existing table • Problem: • Create a new table named LEVEL1_CUSTOMER containing the following columns from the CUSTOMER table: CUSTOMER_NUM, CUSTOMER_NAME, BALANCE, CREDIT_LIMIT, and REP_NUM. The columns in the new LEVEL1_CUSTOMER table should have the same characteristics as the corresponding columns in the CUSTOMER table.

5. Creating a New Table from an Existing Table • Solution: CREATE TABLE LEVEL1_CUSTOMER(CUSTOMER_NUM CHAR(3) PRIMARY KEY,CUSTOMER_NAME CHAR(35),BALANCE DECIMAL(8,2),CREDIT_LIMIT DECIMAL(8,2),REP_NUM CHAR(2) );

6. Creating a New Table from an Existing Table • A new table can be described with the CREATE TABLE command • A SELECT command selects data from the CUSTOMER table • By placing this SELECT command in an INSERT command, the query results are added to the new table

7. Creating a New Table from an Existing Table • Problem: • Insert into the LEVEL1_CUSTOMER table the customer number, customer name, balance, credit limit, and rep number for customers with credit limits of $7,500.

8. Creating a New Table from an Existing Table • Solution: INSERT INTO LEVEL1_CUSTOMERSELECT CUSTOMER_NUM, CUSTOMER_NAME, BALANCE, CREDIT_LIMIT, REP_NUMFROM CUSTOMERWHERE CREDIT_LIMIT = 7500;

9. Changing Existing Data in a Table • The data stored in a database is subject to constant change • The UPDATE command is used to change rows on which a specific condition is true • Format for the UPDATE command: • UPDATE <table name> SET <column name> = <new value> • A WHERE clause can be included to indicate the row(s) on which the change is to take place

10. Adding New Rows to an Existing Table • The INSERT command can be used to: • Add data to the tables • Update table data • Problem: • 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.

11. Adding New Rows to an Existing Table • Solution: • INSERT INTO LEVEL1_CUSTOMERVALUES('895','Peter and Margaret''s', 0, 8000, '20');

12. Commit and Rollback • Updates to data are only temporary • Updates can be reversed (cancelled) at any time during the current work session • Updates become permanent automatically when the DBMS is exited • Updates can be saved immediately by executing the COMMIT command

13. Commit and Rollback • Updates can be cancelled by executing the ROLLBACK command • Updates since the last COMMIT command will be reversed • The ROLLBACK command reverses only changes made to the data, not the table’s structure

14. Transactions • A transaction is a logical unit of work • A transaction can be viewed as a sequence of steps that accomplishes a single task • It is essential that the entire sequence is completed successfully

15. Transactions • The COMMIT and ROLLBACK commands are used with transactions as follows: • Before beginning the updates for a transaction, execute the COMMIT command • Complete the updates for the transaction. If any update cannot be completed, execute the ROLLBACK command and discontinue the updates for the current transaction • Execute the COMMIT command after completing the final update

16. Deleting Existing Rows from a Table • The DELETE command is used to delete data from the database • The format for the DELETE command is: • DELETE <table name> WHERE <column name> = <value>

17. Deleting Existing Rows from a Table • Problem: • In the LEVEL1_CUSTOMER table, change the name of customer 356 to Smith Sport, and then delete customer 895.

18. Deleting Existing Rows from a Table • Solution: UPDATE LEVEL1_CUSTOMERSET CUSTOMER_NAME = 'Smith Sport‘WHERE CUSTOMER_NUM = '356';DELETE FROM LEVEL1_CUSTOMERWHERE CUSTOMER_NUM = '895';SELECT *FROM LEVEL1_CUSTOMER;

19. Executing a Rollback • Problem: • Execute a rollback and then display the data in the LEVEL1_CUSTOMER table • Solution: ROLLBACK;SELECT *FROM LEVEL1_CUSTOMER;

20. Changing a Value in a Column to Null • The value in a column in an existing row can be changed to null • To make this type of change, the affected column must accept nulls • If NOT NULL was specified for the column when it was created, then changing a value to null is prohibited

21. Changing a Value in a Column to Null • The command for changing the value to null is the same as it would be for changing any other value • The value NULL is used as the replacement value

22. Changing Table Structures • A table’s structure can be changed by using the ALTER TABLE command • To add a new column, the ADD clause of the ALTER TABLE command is used • The format for adding a new column is: • ALTER TABLE <table name> ADD <column name> <characteristics>

23. Changing Table Structures • Problem: • Premiere Products decides to maintain a customer type for each customer in the database. These types are R for regular customers, D for distributors, and S for special customers. Add this information in a new column in the LEVEL1_CUSTOMER table.

24. Changing Table Structures • Solution: ALTER TABLE LEVEL1_CUSTOMERADD CUSTOMER_TYPE CHAR(1);

25. Changing Table Structures • The characteristics of existing columns can be changed by using the MODIFY clause of the ALTER TABLE command • Problem: • The length of the CUSTOMER_NAME column in the LEVEL1_CUSTOMER table is too short. Increase its length to 50 characters. In addition, change the CREDIT_LIMIT column so that it cannot accept nulls.

26. Changing Table Structures • Solution: ALTER TABLE LEVEL1_CUSTOMERMODIFY CUSTOMER_NAME CHAR(50);ALTER TABLE LEVEL1_CUSTOMERMODIFY CREDIT_LIMIT NOT NULL;

27. Making Complex Changes • Some changes to a table’s structure are beyond the capabilities of some DBMSs • Examples include: • Eliminate a column • Change the column order • Combine data from two tables into one

28. Making Complex Changes • To make complex changes: • Use the CREATE TABLE command to describe the new table • Insert values into the new table using the INSERT command combined with an appropriate SELECT command

29. Dropping a Table • A table that is no longer needed can be deleted by using the DROP TABLE command • Problem: • Delete the LEVEL1_CUSTOMER table because it is no longer needed in the Premiere Products database. • Solution: DROP TABLE LEVEL1_CUSTOMER;

30. Summary • To create a new table from an existing table: • Create the new table by using the CREATE TABLE command • Use an INSERT command containing a SELECT command to select the desired data from the existing table • UPDATE command: changes existing data in a table • INSERT command: adds new rows to a table

31. Summary • COMMIT command: saves updates • ROLLBACK command: reverses updates • DELETE command: deletes existing rows • To add a column to a table, use the ALTER TABLE command with an ADD clause • To change the characteristics of a column, use the ALTER TABLE command with a MODIFY clause

32. SQL Project Five Completed Good Luck H. Zamanzadeh