SQL - Structured Query Language (continued)

To delete a table:

• DROP TABLE table-name
  

Example:

DROP TABLE S912345.DEPT

Note: If you own the table, you do not need to prefix the table name with the owner. This is generally true of all SQL statements.

To insert a row into a table:

• INSERT INTO table-name (column-names) VALUES(values for columns)
  

Example:

INSERT INTO S912345.EMPMAST

(SSN,NAME,TITLE,DEPTCODE,SALARY)

VALUES('123456789','LUGNUT,SLUGGO','PROGRAMMER','05300',

43500.00)

Notes:

1) If the column names are omitted, all columns are assumed in the order they appeared in the CREATE TABLE.

2) If only some of the columns are specified, the unspecified ones will receive a null value, assuming nulls are allowed for those columns.

3) The row will be inserted in a location selected by SQL/DS. If indexes exist, these may determine the physical location of the new row

To update the specified columns in the rows of a table:

• UPDATE table-name SET column-name = value, column-name = value, . . . . . . . . . ., column-name = value WHERE condition
  

Example:

UPDATE S912345.EMPMAST

SET TITLE = 'SENIOR PROGRAMMER',

DEPTCODE = '73100',

SALARY = 53425.00

WHERE SSN = '123456789'

Notes:

1. One or more columns may be updated.
2. All rows that satisfy the condition are updated.
3. If no rows are selected, then UPDATE has no effect.
   
   

To delete rows from a table:

• DELETE FROM table-name WHERE condition
  

Example:

DELETE FROM S912345.EMPMAST

WHERE SSN = '123456789'

Notes:

1) All rows that satisfy the condition will be deleted.

2) If no rows are selected, then DELETE has no effect.

Logical Unit of Work (LUW)

Any time you attempt to alter data in a table, a Logical Unit of Work is begun.

The changes made do not actually take effect until the LUW is "committed", ending the LUW.

Two possible ways to end an LUW:

COMMIT WORK - This statement ends the LUW and any changes made are actually applied ("committed").

ROLLBACK WORK - This statement ends the LUW and any changes made are "rolled back". The data remains unchanged by the LUW.