Manipulating Data

Insert

• Inserting new rows with NULL values

  INSER INTO s_dept
  VALUES ( 100,'Developer', NULL);
  

• Inserting special values

  INSER INTO s_dept(id,first_name, user_id, start_date)
  VALUES ( 13,'Somchai', USER, SYSDATE);
  

• • The USER function records the current user name.
  • The SYSDATE function records the current date and time.
• Inserting specific date and time values
• • TO_DATE function

    	INSER INTO s_dept(id,first_name, user_id, start_date)
    	VALUES ( 13,'Somchai', USER, TO_DATE('01/02/2007', 'DD/MM/YYYY'));
    	

  • Default century is the current century.
  • Default time is midnight.
• Copying rows from another table

  INSER INTO history(id,first_name, salary, start_date)
  SELECT  id,first_name, salary, start_date
  FROM s_dept
  WHERE salary < 100000;
  

Update

UPDATE s_dept
SET dept_id = 31, salary= 20000
where id = 1;

Delete

DELETE s_dept
where salary < 2000;

Data Modeling and Database Design

Entity Relationship Model

Entity

• A thing of significance about which information needs to be known
• Examples : Employees, customers

Attribute

• Something that describes or qualifies an entity
• Examples : name, id

Relationship

• An association between two entities
• Examples : customer and product.

Relationship Types

One-to-one

• Example : Husband and wife

Many-to-one

• Example : Passenger and plane.

Many-to-many

• Example : employees and skill.

Primary Key

• No duplicate value and cannot be NULL.
• Each row is uniquely identified by a column or set of columns(composite primary key).

Foreign Key

• A column or combination of columns in one table that refers to a PK or unique key (UK) in the same table or in another table.
• The value must either match the value in the related column or be NULL.
• If an FK is part of a PK, then it cannot be NULL.

Indexes

What is an index?

• An index is a database structure that provides quick lookup of data in a column or columns of a table.

When is an Index Used?

• To speed up access to rows in a table.

Guidelines to create an index

• The column is used frequently in the WHERE clause or in a join condition.
• The column contains a wide range of values.
• The column contains a large number of null values.
• Two or more columns are frequently used together in a WHERE clause or a join condition.
• The table is large and most queries are expected to retrieve less than 2-4% of the rows.
• More indexes do not always speed up queries.
• Do not create an index if
• • The table is small.
  • The columns are not often used as a condition in the query.
  • Most queries are expected retrieve more than 2-4% of the rows.
  • The table is update frequently.