Database Programming

1
Database Programming

• Sections 7 Data Manipulation Language (DML)
  transaction, INSERT, implicit, explicit, USER,
  UPDATE, DELETE, integrity constraint, Parent
  record, Child record

2
Overview of remainder of lesson

• Data Manipulation Language DML
• INSERT
• UPDATE
• DELETE
• MERGE
• Default Values
• Merge Statements
• Creating Tables
• Using Data Types

• Data Definition Language DDL
• ALTER TABLE
• DROP TABLE
• RENAME
• TRUNCATE
• COMMENT
• Define Constraints
• Manage Constraints

3
Using a subquery to copy a table

• In order to experiment with the tables, make a
  copy of themSelect all rows from the EMPLOYEES
  table and insert them into the COPY_EMPLOYEES
  table.
• CREATE TABLE copy_employeesAS (SELECT FROM
  employees)
• Verify bySELECT FROM copy_employees
• DESCRIBE copy_employees
• the integrity rules (primary keys, default values
  are not passed to the copy, only the column
  datatype definitions.)

4
Explicitly inserting data

• Executing one DML statement is considered a
  transaction.
• The INSERT statement is used to add new rows to a
  table. To get the column names and default
  order, use
• The statement requires three values
• the name of the table
• the name of the column in the table to populate
• a corresponding value for the column
• INSERT INTO copy_departments (department_id,
  department_name, manager_id, location_id)VALUES
  (70,Public Relations, 100, 1700)
• INSERT INTO copy_departments (department_id,
  manager_id, location_id, department_name)VALUES
  (99, 100, 1700, Advertising)

5
Implicitly inserting data

• Omit the column names
• Match exactly the default order in which columns
  appear in the table
• Provide a value for each column
• Data types need to match
• Use DESCRIBE to check the table structure before
  adding is a good idea
• INSERT INTO copy_departmentsVALUES
  (100,Education, 100, 1700)
• using VALUES adds on row at a time

6
Insert with NULL values

• Implicit Method omit the column from the column
  list
• Any column that is not listed obtains a null
  value in the new row errors can occur the row
  has been specified NOT NULL, uniqueness, foreign
  key violation
• INSERT INTO copy_departments (department_id,
  department_name)VALUES (30,Purchasing)
• Explicit Method specify the NULL keyword in the
  values clause
• INSERT INTO copy_departmentsVALUES (100,
  Finance, NULL, NULL)

7
Inserting Special Values

• Special values such as SYSDATE and USER can be
  entered in the VALUES list of an INSERT
  statement.
• SYSDATE puts current date in a column
• USER places current username (Oracle Application
  Express will put HTMLDB_PUBLIC_USER)
• INSERT INTO copy_employees (employee_id,
  last_name, email, hire_date,job_id)VALUES(1001,
  USER, Test, SYSDATE, IT_PROG)

8
Inserting Special Values

• Can add functions and calculated expressions as
  values
• Example of inserting a system date and a
  calculated expression
• INSERT INTO copy_f_orders (order_number,
  order_date, order_total ,cust_id,
  staff_id)VALUES(1889,SYSDATE,87.921.08,123,19)

9
Inserting Specific date values

• The default date before Oracle 9i was DD-MON-YY.
• The default format for Oracle 9i is DD-MON-RR
• century defaults to the current century
• default time of midnight (000000)
• formats other than the default format use TO_DATE
  function
• INSERT INTO copy_employeesVALUES (114, Den,
  Raphealy, DRAPHEAL, 515.127.4561,
  03-FEB-49, AC_ACCOUNT, 11000, NULL, 100, 30)

10
Inserting Date values

• The default format model for date is DD-MON-RR.
• Recall the century defaults to the nearest
  century (nearest to SYSDATE) with default time
  midnight (000000)
• TO_CHAR reviewSELECT first_name,
  TO_CHAR(birthdate, Month fmDD, RRRR)FROM
  f_staffsWHERE id12

11
Inserting Date values

• To INSERT a row with a non-defaut format for a
  date column, use the TO_DATE function to convert
  the date value (a character string ) to a date
• INSERT INTO copy_f_staffs (first_name,
  last_name, TO_DATE(birthdate, Month fmDD,
  RRRR), salary, staff_type)VALUES (Sue,
  Jones, July 1, 1980, 25,
• clerk)

12
Date Example

• INSERT INTO copy_employeesVALUES (114, Den,
  Raphealy, DRAPHEAL, 515.127.4561,
  03-FEB-49, AC_ACCOUNT, 11000, NULL, 100, 30)
• SELECT last_name, TO_CHAR(hire_date, Month dd,
  RRRR)FROM copy_employeesWHERE employee_id
  114

13
Example

• INSERT INTO copy_f_staffs (first_name, salary,
  staff_type, TO_DATE(birthdate, 'Month fmDD, RRRR
  HH24MI)VALUES ('Sue', null, null, 'July 1,
  1980 1720')

14
Inserting multiple rows

• INSERT statement can be used to insert one row,
  but to insert multiple rows use a subquery
• Use a subquery within the INSERT command to
  insert multiple rows

15
Using a subquery to copy rows

• Copy values from an existing tableCREATE TABLE
  sales_reps(id number(5), name varchar2(15),
  salary number(10), commission_pct number (8))
• No VALUES clauseINSERT INTO sales_reps(id, name,
  salary, commission_pct) SELECT employee_id,
  last_name, salary,
  commission_pct FROM employees WHERE
  job_id LIKE REP
• NOTE subquery is not in parentheses as in WHERE
  clause previously

16
Copy all the data from a table

• INSERT INTO sales_repsSELECT FROM employees
• This works ONLY if both tables have same number
  of columns, same types, and in the same order

17
UPDATE statements

• The UPDATE statement is used to modify existing
  rows in a table. It requires four values
• the name of the table
• UPDATE copy_employees
• the name of the column in the table to populate
• SET department_id
• a corresponding value or subquery for the column
• SET department_id 70
• a condition that identifies the columns and the
  changes for each column
• WHERE employee_id 103
• New value can be from a single-row subquery

18
Updating one column

• Specific row or rows are modified if you specify
  the WHERE clause.
• UPDATE copy_employeesSET department_id
  70WHERE employee_id 103(One row is updated)
• All rows in the table are modified if you omit
  the WHERE clause.
• UPDATE copy_employeesSET department_id
  110(All rows are updated)

19
UPDATING multiple columns

• Can update one or more rows in a statement
• UPDATE copy_f_customers SET phone_number
  4475582344, city Chicago
  WHERE idlt200
• If no WHERE clause all rows are updated in the
  table

20
Update using subquery

• UPDATE copy_employeesSET department_id
  (SELECT department_id FROM employees
  WHERE last_name Ernst)WHERE employee_id
  103

21
Updating columns with subqueries

• You can update one or more columns in the SET
  clause of an UPDATE statement by writing
  subqueries.
• UPDATE copy_employeesSET job_id (SELECT job_id
  FROM employees
  WHERE employee_id 205) salary
  (SELECT salary FROM
  employees WHERE employee_id
  205)WHERE employee_id 114

22
Updating rows based on another table

• Use a subquery to update data in one table with
  data in another table
• UPDATE copy_f_staffsSET salary (SELECT salary
  FROM f_staffs
  WHERE id 9)WHERE id 9

23
DELETE statement

• The DELETE statement is used to remove existing
  rows in a table. The statement requires two
  values
• the name of the table
• the condition that identifies the rows to be
  deletedDELETE FROM copy_employeesWHERE
  employee_id 200
• If you omit the WHERE clause all rows will be
  deleted

24
Subquery Delete

• Use subqueries in DELETE statements to remove
  rows from a table based on values from another
  table.
• DELETE FROM copy_employeesWHERE department_id
  (SELECT department_id
  FROM departments WHERE
  department_name Shipping

25
Deleting rows based on another table

• A subquery can be used to remove rows from one
  table based on values from another table
• DELETE FROM empWHERE plant_id (SELECT
  plant_id FROM locations WHERE
  plant_loc AnyVilla)

26
Integrity Constraints

• Integrity constraints define certain data quality
  requirements that the data in the database needs
  to meet. If a user tries to insert data that
  doesnt meet these requirements an error will
  occur.
• Types of Integrity constraints
• NOT NULL each row in the column must have a
  value
• PRIMARY KEY unique and not null
• FOREIGN KEY A foreign key constraint (also
  called referential integrity constraint) on a
  column ensures that the value in that column is
  found in the primary key of another table.
• CHECK value meets certain conditions salary
  CHECK lt 50000
• UNIQUE no duplicate values in a column (email
  address)

27
Integrity Constraint Errors

• UPDATE employeesSET department_id 55WHERE
  department_id 110
• ORA-02291 integrity constraint(USCA_INST_SQL03_T
  01.EMP_DEPT_FK) violated parent key not found
• There is no department_id 55 in the departments
  table which is the parent table

28

29

30
Default Values

• A column in a table can be given a default value.
• Assigning default values prevents null values
  from existing in the column.
• Default values can be
• a literal value no commission assigned
• an expression salary1.15
• SQL function, such as SYSDATE or USER
• Default values must match the data type of the
  column

31
Default Values Examples

• Default Values Specified at the time the table is
  createdCREATE TABLE items( part_number
  VARCHAR2(10), description VARCHAR2(10),
  qty_on_hand NUMBER DEFAULT 0)
• INSERT INTO items(part_number, description)VALUES
  (AB154,hammer)

32
Default Values - Examples

• Use DEFAULT when inserting values
• INSERT INTO items(part_number, description,
  qty_on_hand)VALUES (300,Widger, DEFAULT)
• Use DEFAULT when updating values
• UPDATE itemsSET qty_on_hand DEFAULTWHERE
  part_number 200
• Now check the results!
• SELECT FROM items

33
MERGE

• MERGE Statement
• Accomplishes an UPDATE and INSERT at the same
  time the ON clause specifies the match if
  two ids match make the following updates,
  otherwise insert the following as new rows
• MERGE INTO copy_emp c USING employees e
  ON (c.employee_id e.employee_id)WHEN MATCH
  THEN UPDATE SET c.first_name
  e.first_name c.last_name e.last_name
  ..... c.department_id e.department_idWHE
  N NOT MATCHED THEN INSERT VALUES( e.employee_id,
  e.first_name, e.last_name, e.salary,
  e.commission_pct, e.manager_id, e.department_id)

34
MERGE Example

• MERGE INTO copy_items c USING items i
  ON(c.part_number i.part_number)WHEN MATCHED
  THEN UPDATE SETc.description
  i.descriptionc.qty_on_hand i.qty_on_handWHEN
  NOT MATCHED THEN INSERT VALUES(i.part_number,
  i.description, i.qty_on_hand)

35
CREATING TABLES

• Naming Rules
• Must begin with a letter
• Must be 1 to 30 characters in length
• Must only contain alpha/numeric,_,,
• Must be unique
• Must not be an Oracle Server reserved word

36
Using Data Types

• Most Common Data Types
• VARCHAR2
• Examples Name, Address
• NUMBER
• Examples Price, Quatity
• DATE
• Examples DOB, Hire Date

37
Creating Tables

• CREATE TABLE name(column name DATATYPE(specificat
  ions for datatype)
• VARCHAR2(number of characters) variable length
  data 1-4000
• CHAR(size) fixed length data 1-2000
• NUMBER(precision, scale) total number of
  decimal digits(1 to 38), right of decimal
  digits(-84 to 127) For example, a scale of 2
  means the value is rounded to the nearest
  hundredth (3.456 becomes 3.46) a scale of -3
  means the number is rounded to the nearest
  thousand (3456 becomes 3000).
• DATE date and time

38
Creating Tables contd

• CREATE TABLE students(id VARCHAR2(5), lname
  VARCHAR2(15), fname VARCHAR2(15), GPA
  number(6), enroll_date date DEFAULT SYSDATE)

39
ORACLE Data Dictionary

• The data dictionary is a collection of tables
  created and maintained by the Oracle Server and
  contains information about the database.
• All data dictionary tables are owned by the SYS
  user. Because the tables are difficult to
  understand, users access data dictionary includes
  names of the Oracle Server users, privileges
  granted to users, database object names, table
  constrains and auditing information.
• You may browse the Oracle Data Dictionary to show
  objects you own.
• SELECT FROM DICTIONARY - returns 610 items
• SELECT table_nameFROM user_tables
• SELECT DISTINCT object_typeFROM user_objects
• SELECT FROM user_catalog

40
Using Interval Year to Month

• INTERVAL YEAR TO MONTH Stores a period of time
  in years/monthsNote that the argument 3 in the
  create table and insert statements refers to
  precision
• CREATE TABLE time_ex2(school_year_duration
  INTERVAL YEAR (3) TO MONTH)
• INSERT INTO time_ex2(school_year_duration)VALUES(
  INTERVAL 9 Month(3))
• SELECT TO_CHAR(SYSDATE school_year_duration,
  dd-Mon-yyyy)FROM time_ex2
• Returned 9 month from todays date

41
USING INTERVAL DAY TO SECOND

• INTERVAL DAY TO SECOND Stores a more precise
  period of time (days/hours/minutes/seconds)
• CREATE TABLE time_ex3(day_duration INTERVAL
  DAY(3) to SECOND)
• INSERT INTO time_ex3(day_duration)
  VALUES(INTERVAL 180 DAY(3))
• SELECT SYSDATE day_duration Half Year FROM
  time_ex3

42
Using Time Data Types

• TIMESTAMP stores values with respect to
  universal time
• CREATE TABLE time_example (order_date TIMESTAMP
  WITH LOCAL TIME ZONE,ship_date DATE DEFAULT
  SYSDATE)
• INSERT INTO time_exampleVALUES(SYSDATE,
  SYSDATE)
• SELECT FROM time_example