Database Programming

1
Database Programming

• Sections 7Multi-row sub queries, IN, ANY, ALL,
  Data Manipulation Language (DML) transaction,
  INSERT, implicit, explicit, USER, UPDATE, DELETE,
  integrity constraint, Parent record, Child record
  

2
Multi-row subquery
3
7.1.4 Example

• Write a query to find the name of the Global Fast
  Foods employee who makes the most money?
• Solution next slide

4
Solution using subquery
5
Multiple-Row Subqueries

• Subqueries that return more than one value are
  called multiple-row subqueries
• When used in a WHERE clause, the multiple-row
  subquery must use a multiple-row operator, such
  as IN, ANY, or ALL.
• The multiple-row operators areIN ANY ALL
• The NOT operator can be used with any of these
  three operatorsSee example on next slide

6
Multiple-Row Subqueries contd

• SELECT employee_id, last_name, salary,
  department_idFROM employeesWHERE salary NOT IN
  (SELECT salary FROM employees
  WHERE last_name King)

7
IN

• SELECT last_name,salary, department_idFROM
  employeesWhere salary IN(SELECT MIN(salary)
  FROM employees GROUP BY department_id)

8
ANY

• The ANY operator compares a value to each value
  returned by a subquery.
• SELECT employee_id, last_name, job_id,
  salaryFROM employeesWHERE salary lt ANY
  (SELECT salary FROM employees WHERE job_id
  IT_PROG)
• Results next slide

9
Results of ANY
10
All

• Used when the outer-query WHERE clause is
  designed to restrict rows based on all values
  returned from the inner query.
• the inner query will return a list of values.
• The ALL operator compares a value to every value
  returned by the inner query.

11
NULL Values in Subqueries

• SELECT emp.last_nameFROM employees empWHERE
  emp.employee_id NOT IN (SELECT
  mgr.manager_id FROM employees mgr)SQL
  Query ResultsNo DataOne of the values returned
  by the inner query is a null value, and hence the
  entire query returns no rows. King does not have
  a manager.

12
NULL Example

• SELECT department_id, department_nameFROM
  departmentsWHERE department_id (SELECT
  department_id FROM employees WHERE
  salary gt60000)
• SQL Query Results. No data found.
• No employee has a salary gt60000
• There is no such thing as NULL as NULL is not
  zero!

13
Null Values in Subqueries, contd

• SELECT emp.last_nameFROM employees empWHERE
  emp.employee_id NOT IN (SELECT
  mgr.manager_id FROM employees mgr
  WHERE mgr.manager_id IS NOT NULL)
• View results of inner query on next slide.
• Now, none of the values returned by the inner
  query is a null value, thus it works.

14
Results of Inner Query
15
Examples 7.2.4

• 1. Select all the employees (name, job ID,
  salary) in departments 10 and 30. Write two
  different queries that will produce the desired
  result.
• 2. Select the name of all the departments
  (department name and location id) and order by
  department number. Do not display the department
  number in the output.

16
Possible Answers 7.2.4

• SELECT last_name,job_id, salary FROM employees
  WHERE department_id in (10,30)
• SELECT last_name,job_id, salary FROM employees
  WHERE department_id 10 or department_id30
• This is not the most efficient answer, but it
  does return the correct resultsSELECT
  last_name,job_id,salary FROM employees WHERE
  department_id IN(            SELECT
  department_id             FROM departments
             WHERE department_id IN (10,30))
• SELECT department_name, location_id FROM
  departments ORDER BY department_id

17
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

18
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
• the integrity rules (primary keys, default values
  are not passed to the copy, only the column
  datatype definitions.)

19
Explicitly inserting data

• 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)

20
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.
• INSERT INTO copy_departmentsVALUES
  (100,Education, 100, 1700)
• using VALUES adds on row at a time

21
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)

22
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 (HTML DB 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)

23
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)

24
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

25
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

26
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 113

27
Updating one column

• Specific row or rows are modified if you specify
  the WHERE clause.
• UPDATE copy_employeesSET department_id
  70WHERE employee_id 113(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)

28
Update using subquery

• UPDATE copy_employeesSET department_id
  (SELECT department_id FROM employees
  WHERE last_name Ernest)WHERE
  employee_id 113

29
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
  jobt_id FROM employees
  WHERE employee_id 205)
  salary (SELECT salary
  FROM employees WHERE
  employee_id 205)WHERE employee_id 114

30
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)

31
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

32
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

33
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 LIKE REP)

34
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

35
Default Values Examples

• Default Values Specified as 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)

36
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

37
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)

38
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)

39
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

40
Using Data Types

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

41
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

42
Creating Tables contd

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

43
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

44
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 YEARS(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

45
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

46
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