SQL Action Commands

1
SQL Action Commands
2
DML Data Manipulation Language

• Select Viewing the data
• Insert
• Update Maintaining the data
• Delete

3
Insert Syntax (external source)

• INSERT INTO table ( field list )
• VALUES ( value list )
• INSERT INTO table ( field list )
• SELECT STATEMENT

Insert Syntax (internal source)
4
New Employee

• INSERT INTO tblEmployee ( EID, FirstName,
  LastName,
• JobTitle, AnnualSalary, CurrentEmployee,
  Supervisor,
• SSN, Gender, DOB, Phone )
• VALUES ( 'JM7021', 'Jennifer', 'MacDougal',
• 'Assistant Manager', 97500, 'Y', 'C123',
• '458-03-7182', 'F', '11/28/1969', '(512)
  555-2341' )

Referential integrity and validation rules
automatically checked. Secondary indices
automatically updated.
5
New Employee (Parents, then Children)

• INSERT INTO tblFamily ( FID, FirstName,
  LastName, EID,
• Relationship, Gender, DOB )
• VALUES ( 'FW9473', 'Mark', 'MacDougal', 'JM7021',
  
• 'Spouse', 'M', '6/10/1969' )
• INSERT INTO tblFamily ( FID, FirstName,
  LastName, EID,
• Relationship, Gender, DOB )
• VALUES ( 'FJ3911', 'Allison', 'MacDougal',
  'JM7021',
• 'Child', 'F', '3/5/1997' )

Referential integrity and validation rules
automatically checked. Secondary indices
automatically updated.
6
Worked Last Year

• INSERT INTO tblWorkedIn2008 ( EID, FullName )
• SELECT EID, FirstName ' ' LastName
• FROM tblEmployee
• WHERE EID IN ( SELECT Distinct EID
• FROM
  tblPaycheck
• WHERE
  YEAR(PayDate) 2008 )

7
Worked Last Year (virtual table)

• CREATE VIEW qryWorkedIn2008 AS
• SELECT EID, FirstName ' ' LastName AS
  FullName
• FROM tblEmployee
• WHERE EID IN ( SELECT Distinct EID
• FROM
  tblPaycheck
• WHERE
  YEAR(PayDate) 2008 )

8
Worked Last Year (virtual table)

• CREATE VIEW qryWorkedLastYear AS
• SELECT EID, FirstName ' ' LastName AS
  FullName
• FROM tblEmployee
• WHERE EID IN ( SELECT Distinct EID
• FROM
  tblPaycheck
• WHERE
  YEAR(PayDate)
• 
  YEAR(GetDate()) - 1 )

9
Update Syntax

• UPDATE
• SET
• WHERE

• table
• replacement statements
• row condition

Note The SET and WHERE clauses can include
subqueries
10
New Address

• UPDATE
• SET
• WHERE

• tblEmployee
• Street '6423 Fulton Blvd',
• Apt Null,
• City 'Austin',
• State 'Tx',
• Zip '78751',
• Phone '[phone]'
• EID 'JM7021'

Referential integrity and validation rules
automatically checked. Secondary indices
automatically updated.
11
Promote Employees

• UPDATE
• SET
• WHERE
• AND
• AND

• tblEmployee
• JobTitle 'Senior Sales Representative',
• AnnualSalary ROUND(1.03 AnnualSalary, 2)
• CurrentEmployee 'Y'
• JobTitle 'Sales Representative'
• AnnualSalary gt 52000

Referential integrity and validation rules
automatically checked. Secondary indices
automatically updated.
12
10 Year Longevity Pay Raise

• UPDATE
• SET
• WHERE
• AND

• tblEmployee
• AnnualSalary ROUND(1.015 AnnualSalary, 2)
• CurrentEmployee 'Y'
• EID IN ( SELECT Distinct EID
• FROM tblPaycheck
• WHERE PayDate lt '10/26/1999' )

What is wrong with this simplistic approach?
13
Delete Syntax

• DELETE
• WHERE

• table
• row condition

Note The WHERE clause can include subqueries
14
Discard Former Employee Paychecks

• DELETE tblPaycheck
• WHERE CheckNumber 4909
• OR CheckNumber 5053
• OR CheckNumber 5218
• OR CheckNumber 5377
• OR CheckNumber 5525

Paid to Stanley Andrews
15
Discard Former Employee Paychecks
Stanley Andrews

• DELETE tblPaycheck
• WHERE EID 'SA7192'
• DELETE tblPaycheck
• WHERE EID 'RD9000' OR EID 'JG0742'
• OR EID 'FH0441' OR EID 'HL0255'
• DELETE tblPaycheck
• WHERE EID IN ( 'TM1', 'OMP', 'S843', 'CS0369',
  'HBW' )

Dakin, Gale, Hagney and Landon
Mitchell, Potter, Simms, Switzer and Warner
16
Discard Former Employee Paychecks

• DELETE tblPaycheck
• WHERE EID 'SA7192'
• DELETE tblPaycheck
• WHERE EID 'RD9000' OR EID 'JG0742'
• OR EID 'FH0441' OR EID 'HL0255'
• DELETE tblPaycheck
• WHERE EID IN ( SELECT EID
• FROM tblEmployee
• WHERE
  CurrentEmployee 'N' )

ALL former employees
17
Discard Former Employees (Children First)

• DELETE tblPaycheck
• WHERE EID IN ( SELECT EID
• FROM tblEmployee
• WHERE
  CurrentEmployee 'N' )
• DELETE tblFamily
• WHERE EID IN ( SELECT EID
• FROM tblEmployee
• WHERE
  CurrentEmployee 'N' )
• DELETE tblEmployee
• WHERE CurrentEmployee 'N'

18
The Theorem

• If a database is well designed (i.e.,
  normalized), then
• every question can be answered and every task can
  be
• performed with just the four SQL DML commands.