DML: Data Manipulation Language

1
DML Data Manipulation Language

• Murach Chapters 3 and 7

2
DML Data Manipulation Language

• SELECT Shows real virtual data
• INSERT
• UPDATE Modifies physical data
• DELETE
• Select is also known as DQL (Q for query)
• Insert, Update and Delete are also known as the
  Action commands

3
DML Data Manipulation Language

• SELECT Shows real virtual data
• INSERT
• UPDATE Modifies physical data
• DELETE
• Relative Speed? Select -vs- Action
• How can we make them faster?
• Frequency of use?

4
Select Syntax (elementary version)

• SELECT
• FROM
• WHERE
• ORDER BY
• Note SELECT and FROM are mandatory clauses
• WHERE and ORDER BY are optional
• Relative sequence of clauses is fixed

• fields
• tables
• row conditions
• fields

5
Show everything in the table

• SELECT
• FROM
• Note The database engine will display the
  records
• in the fastest possible row and
  column order

• tblEmployee

6
Alphabetized list of programmers

• SELECT
• FROM
• WHERE
• ORDER BY
• Note 1 No explicit reference to secondary index
• Note 2 programmer Programmer proGRAMmer

• tblEmployee
• JobTitle 'programmer'
• LastName, FirstName

7
Employees named Jones or Smith

• SELECT
• FROM
• WHERE
• ORDER BY

• FirstName, LastName
• tblEmployee
• LastName 'Jones'
• OR LastName 'Smith'
• LastName, FirstName

Improper Syntax WHERE LastName 'Jones' OR
'Smith'
8
Programmers named Jones or Smith

• SELECT
• FROM
• WHERE
• ORDER BY

• FirstName, LastName
• tblEmployee
• ( LastName 'Jones'
• OR LastName 'Smith' )
• AND JobTitle 'programmer'
• LastName, FirstName

9
Annual salaries of programmers

• SELECT
• FROM
• WHERE
• ORDER BY

• FirstName, LastName, AnnualSalary
• tblEmployee
• JobTitle 'programmer'
• LastName, FirstName

10
Monthly salaries of programmers

• SELECT
• FROM
• WHERE
• ORDER BY

• FirstName, LastName, AnnualSalary/12
• tblEmployee
• JobTitle 'programmer'
• LastName, FirstName

11
Programmers earning over 8K/month

• SELECT
• FROM
• WHERE
• ORDER BY

• FirstName, LastName
• tblEmployee
• JobTitle 'programmer'
• AND AnnualSalary/12 gt 8000
• LastName, FirstName

12
Insert Syntax (external source)

• INSERT INTO
• VALUES

• table ( fields )
• ( values )

13
New employee

• INSERT INTO tblEmployee ( SSN, FirstName,
• LastName, JobTitle, AnnualSalary, DOB )
• VALUES ( '123-45-6789', 'John', 'Adams',
• 'Assistant Manager', 68500, '7/4/1976' )

Equivalent INSERT INTO tblEmployee ( DOB,
AnnualSalary, JobTitle, LastName,
FirstName, SSN ) VALUES ('7/4/1976', 68500,
'Assistant Manager', 'Adams', 'John',
'123-45-6789')
14
Insert Syntax (internal source)

• INSERT INTO
• SELECT STATEMENT

• table ( fields )

15
Management Only

• INSERT INTO tblManagement ( SSN, FN, LN )
• SELECT SSN, FirstName, LastName
• FROM tblEmployee
• WHERE JobTitle 'Manager'
• OR JobTitle 'Assistant Manager'
• Note FN and FirstName, LN and LastName

16
Update Syntax

• UPDATE
• SET
• WHERE

• table
• replacement statements
• row condition

17
Promote the new guy

• UPDATE
• SET
• WHERE

• tblEmployee
• JobTitle 'Manager' ,
• AnnualSalary 92000
• SSN '123-45-6789'

18
Give everyone a 15 raise

• UPDATE
• SET

• tblEmployee
• AnnualSalary 1.15 AnnualSalary

19
Give everyone except management a raise

• UPDATE
• SET
• WHERE
• AND
• Alternatives ltgt ! NOT( )

• tblEmployee
• AnnualSalary 1.15 AnnualSalary
• JobTitle ltgt 'Manager'
• JobTitle ltgt 'Assistant Manager'

20
Give everyone except management a raise

• UPDATE
• SET
• WHERE
• AND
• Alternatives ltgt ! NOT( )

• tblEmployee
• AnnualSalary 1.15 AnnualSalary
• JobTitle ! 'Manager'
• JobTitle ! 'Assistant Manager'

21
Give everyone except management a raise

• UPDATE
• SET
• WHERE NOT
• OR
• Alternatives ltgt ! NOT( )

• tblEmployee
• AnnualSalary 1.15 AnnualSalary
• ( JobTitle 'Manager'
• JobTitle 'Assistant Manager' )

22
Cut management salaries

• UPDATE
• SET
• WHERE
• OR

• tblEmployee
• AnnualSalary 0.95 AnnualSalary
• JobTitle 'Manager'
• JobTitle 'Assistant Manager'

23
Delete Syntax

• DELETE
• WHERE
• Note 1 Delete and Drop are not the same
• Note 2 Don't forget the orphans
• Note 3 Dont forget cascading

• table
• row condition

24
Fire the new guy

• DELETE tblEmployee
• WHERE SSN '123-45-6789'

25
Fire management

• DELETE tblEmployee
• WHERE JobTitle 'Manager'
• OR JobTitle 'Assistant Manager'

26
Fire everyone

• DELETE tblEmployee

27
SQL Views (Virtual Tables)

• Murach Chapter 12

28
SQL Structured Query Language

• DDL
• CREATE
• ALTER
• DROP
• DCL
• GRANT
• REVOKE
• DENY

• DML
• SELECT
• INSERT
• UPDATE
• DELETE

VIEW
29
Tables Real and Virtual

• Table
• real data, no computations
• primary keys for uniqueness
• foreign keys for relationships
• secondary indices for speed
• Virtual Table
• data on demand, computations on demand
• select query
• view - stored select query

30
View Syntax

• CREATE VIEW ViewName AS
• SELECT STATEMENT
• DROP VIEW ViewName

31
Sample View

• CREATE VIEW qryManagement AS
• SELECT FirstName, LastName, AnnualSalary
• FROM tblEmployee
• WHERE JobTitle 'Manager'
• OR JobTitle 'Assistant Manager'

32
Using Views

• SELECT FirstName, LastName
• FROM qryManagement
• WHERE AnnualSalary gt 100000
• ORDER BY LastName, FirstName