Data Manipulation Language DML

1
Data Manipulation Language (DML)
2
Modification of the Database - Deletion

• Delete all account records at the Nablus
  branch delete from account where branch-name
  Nablus
• Conceptually, delete is done in two steps
• find the tuples you want to deleteselect
  from accountwhere branch-name Nablus
• delete the tuples you found.

3
Modification of the Database - Deletion

• Delete all accounts at every branch located in
  Needham.delete from accountwhere branch-name
  in (select branch-name from branch where
  branch-city Needham)

4
Example Query

• Delete the records of all accounts with balances
  below the average at the bank delete from
  account where balance lt (select avg
  (balance) from account)

5
Modification of the database - Insertion

• Add a new tuple to accountinsert into account
  values (Perryridge, A-9732, 1200)To reorder
  attributes, specify attribute names
  explicitlyinsert into account (branch-name,
  balance, account-number) values (Perryridge,
  1200, A-9732)
• Add a new tuple to account with balance set to
  nullinsert into account values ( Perryridge,
  A-777, null)

6
Modification of the database - Updates

• Increase all accounts with balance over 10,000
  by 6, all other accounts receive 5.
• Write two update statements update
  account set balance balance 1.06 where
  balance gt10000 update account set balance
  balance 1.05 where balance ? 10000
• the order is important
• Solution use case

7
Case Statement for Conditional Updates

• Same query as before Increase all accounts with
  balances over 10,000 by 6, all other accounts
  receive 5.
• update account set balance case
  when balance lt
  10000 then balance 1.05
  else balance 1.06
  end

8
Correct Update Procedure

• update works-on
• set pid ( select id from project where
  nameAI )
• where eid ( select id from employee where name
  Dik Lee )
• and pid ( select id from project where name
  database )

9
SQL Data Definition Language
10
Data Definition Language(DDL)

• including
• The schema for each relation.
• The domain of values associated with each
  attribute.
• Integrity constraints.
• Security and authorization information for each
  relation.

11
Domain Types in SQL

• char(n) Fixed length character string, with
  user-specified length n.
• varchar(n) Variable length character string, with
  user-specified maximum length n.
• int integer.
• smallint Small integer (subset of the integer
  domain type).
• Numeric(p,d) Fixed point number, with
  user-specified precision of p digits, with d
  digits to the right of decimal point.

12
Domain types in SQL (cont.)

• real, double precision Floating point and
  double-precision floating point numbers.
• float(n) Floating point number, with
  user-specified precision of at least n digits.
• date Dates, containing a (4 digits) year, month
  and date.
• time Time of day, in hours, minutes and seconds.

• Null values are allowed in all the domain types.