DML SQL

1
DML SQL

• Query (read-only)
• SELECT to query data in the database
• Modification (write)
• INSERT inserts new rows of data into a table
• UPDATE modifies existing data in a table
• DELETE deletes rows of data from a table

2
Insert a Single Row

• Format of INSERT statement
• INSERT INTO TableName (columnList)
• VALUES (dataValueList)

3
Insert a Single Row (cont.)

• columnList
• A list of one or more column names separated by
  commas
• Optional
• If omitted, SQL assumes all columns in order
• If specified, any columns that are omitted from
  the list are assumed to have NULL values
• dataValueList must match columnList
• Have same number of items
• Have direct correspondence in the position of
  items in the two lists
• Data type must be compatible

4
INSERT without Column List

• insert a new row to TeachingAssistant table

INSERT INTO TeachingAssistant VALUES (93,
642930758, 389, 0.00)
Result
5
INSERT Using Column List

• insert a new row into Course table supplying
  data for the following columns courseNo, title,
  sectionNo, units, deptCode, prerequisite

INSERT INTO Course (prerequisite, deptCode,
units, sectionNo, title, courseNo) VALUES
(MATH105, math, 3, 1, Calculus, MATH210)
Result
6
INSERT Using Column List (cont.)

• An equivalent INSERT statement
• INSERT INTO Course
• VALUES(MATH210, Calculus, 1, 3, math,
  NULL, NULL, MATH105)
• What are the differences between this statement
  and the previous statement?
• Hints
• Column names
• NULL
• Order of values

7
UPDATE Statement

• Format of UPDATE statement
• UPDATE TableName
• SET columnName1 dataValue1 , columnName2
  dataValue2
• WHERE searchCondition

8
UPDATE Statement (cont.)

• Processing order UPDATE ? WHERE ? SET
• SET clause specifies the names of one or more
  columns that are to be updated
• WHERE clause is optional
• If omitted, all rows are updated for the named
  columns
• If specified, only those rows that satisfy the
  searchCondition are updated
• dataValue(s) must have compatible data type(s)
  for the corresponding column(s)

9
UPDATE All Rows

• give all teaching assistant 10 salary
  increase

UPDATE TeachingAssistant SET salary salary
1.1
Result
10
UPDATE Specific Rows Multiple Columns

• give all assistant professors 10 bonus
  increase and change their positions to associate
  professors

UPDATE Instructor SET salary salary 1.1,
position associate WHERE position
assistant
Result
11
DELETE Statement

• Format of DELETE statement
• DELETE FROM TableName
• WHERE searchCondition
• WHERE clause is optional
• If omitted, all rows are deleted from the table
  however, this doesnt delete the table itself
  (i.e., tables column names are still there). To
  delete a table, use DROP TALBE statement.
• If specified, only those rows that satisfy the
  searchCondition are deleted.

12
DELETE Specific Rows

• delete all associate professors

DELETE FROM Instructor WHERE position
associate
Result
13
DELETE All Rows

• delete all rows from TeachingAssistant table

DELETE FROM TeachingAssistant
Result