CS820: ADVANCED DATABASE CONCEPTS

MSIT 2K23
Contents
• Transforming Data

• Data Definition Language

• Create Alter Drop

• Data Manipulation language

• Insert Update Delete

2
Transforming Data

3
 Data Transforming: IF
• The IF() function returns a value if a condition is
TRUE, or another value if a condition is FALSE.
 Data Transforming: Case
• The CASE statement goes through conditions
and return a value when the first condition is
met (like an IF-THEN-ELSE statement). So, once
a condition is true, it will stop reading and return
the result.
• If no conditions are true, it will return the value
in the ELSE clause.
• If there is no ELSE part and no conditions are
true, it returns NULL.
Data Transforming: Case
 Data Transforming: IFNULL
• The IFNULL() function returns a specified value if
the expression is NULL.
• If the expression is NOT NULL, this function
returns the expression.
 Data Transforming: IFNULL
• The IFNULL() function returns a specified value if
the expression is NULL.
• If the expression is NOT NULL, this function
returns the expression.
 Data Transforming: NULLIF
• The NULLIF() function compares two expressions
and returns NULL if they are equal. Otherwise,
the first expression is returned.
 Data Transforming: NULLIF
• The NULLIF() function compares two expressions
and returns NULL if they are equal. Otherwise,
the first expression is returned.
 Data Transforming: COALESCE()
• The COALESCE() function returns the first non-
null value in a list.
Creating Data Storage Objects

12
Database Objects

Object Description
Table Basic unit of storage; composed of rows
and columns

View Logically represents subsets of data from

one or more tables

Sequence Numeric value generator

Index Improves the performance of some queries
Synonym Gives alternative names to objects
Naming Rules
Table names and column names:
• Must begin with a letter
• Must be 1–30 characters long
• Must contain only A–Z, a–z, 0–9, _, $, and #
• Must not duplicate the name of another object
owned by the same user
• Must not be an Oracle server reserved word
The CREATE TABLE Statement

• You must have:

• CREATE TABLE privilege
• A storage area
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);

• You specify:
• Table name
• Column name, column data type, and column size
Referencing Another User’s Tables

• Tables belonging to other users are not in the user’s

schema.
• You should use the owner’s name as a prefix to those
tables.
The DEFAULT Option
• Specify a default value for a column during an
insert.

... hire_date DATE DEFAULT SYSDATE, ...

• Literal values, expressions, or SQL functions are legal

values.
• Another column’s name or a pseudocolumn are illegal
values.
• The default data type must match the column data
type.
Creating Tables
• Create the table.
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
Table created.

• Confirm table creation.

DESCRIBE dept
Tables in the Oracle Database

• User Tables:
• Are a collection of tables created and maintained by
the user
• Contain user information
• Data Dictionary:
• Is a collection of tables created and maintained by
the Oracle Server
• Contain database information
Querying the Data Dictionary

•See the names of tables owned by the user

SELECT table_name
FROM user_tables ;

•View distinct types owned by the user

SELECT DISTINCT object_type
FROM user_objects ;

•View tables, views, synonyms, and sequences

owned by the user
SELECT *
FROM user_catalog ;
Data Types
Data Type Description
VARCHAR2(size) Variable-length character data
CHAR(size) Fixed-length character data
NUMBER(p,s) Variable-length numeric data
DATE Date and time values
LONG Variable-length character data
up to 2 gigabytes
CLOB Character data up to 4
gigabytes
RAW and LONG RAW Raw binary data
BLOB Binary data up to 4 gigabytes
BFILE Binary data stored in an external
file; up to 4 gigabytes
ROWID A 64 base number system representing
the unique address of a row in its table.
Creating a Table
by Using a Subquery Syntax
• Create a table and insert rows by combining the
CREATE TABLE statement and the AS subquery
option.
CREATE TABLE table
[(column, column...)]
AS subquery;
• Match the number of specified columns to the number
of subquery columns.
• Define columns with column names and
default values.
Creating a Table by Using a
Subquery
CREATE TABLE dept80
AS
SELECT employee_id, last_name,
salary*12 ANNSAL,
hire_date
FROM employees
WHERE department_id = 80;
Table created.
DESCRIBE dept80
The ALTER TABLE Statement
Use the ALTER TABLE statement to:
• Add a new column
• Modify an existing column
• Define a default value for the new column
• Drop a column
The ALTER TABLE Statement
Use the ALTER TABLE statement to add, modify, or
drop columns.
ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype]...);

ALTER TABLE table

MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);

ALTER TABLE table

DROP (column);
Adding a Column
New column
DEPT80

“Add a new
column to
the DEPT80
table.”

DEPT80
Adding a Column
• You use the ADD clause to add columns.
ALTER TABLE dept80
ADD (job_id VARCHAR2(9));
Table altered.

• The new column becomes the last column.

Modifying a Column
• You can change a column’s data type, size, and
default value.
ALTER TABLE dept80
MODIFY (last_name VARCHAR2(30));
Table altered.

• A change to the default value affects only

subsequent insertions to the table.
Dropping a Column
Use the DROP COLUMN clause to drop columns you no
longer need from the table.

ALTER TABLE dept80

DROP COLUMN job_id;
Table altered.
Dropping a Table
• All data and structure in the table is deleted.
• Any pending transactions are committed.
• All indexes are dropped.
• You cannot roll back the DROP TABLE statement.

DROP TABLE dept80;

Table dropped.
Loading Data to Storage

31
Data Manipulation Language

• A DML statement is executed when you:

• Add new rows to a table
• Modify existing rows in a table
• Remove existing rows from a table
• A transaction consists of a collection of DML
statements that form a logical unit of work.
Adding a New Row to a Table
New
DEPARTMENTS row

…insert a new row

into the
DEPARMENTS table…
The INSERT Statement Syntax
• Add new rows to a table by using the INSERT statement.

INSERT INTO table [(column [, column...])]

VALUES (value [, value...]);

• Only one row is inserted at a time with this syntax.

Inserting New Rows
• Insert a new row containing values for each column.
• List values in the default order of the columns in the
table.
• Optionally, list the columns in the INSERT clause.
INSERT INTO departments(department_id, department_name,
manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);
1 row created.

• Enclose character and date values within single

quotation marks.
Inserting Rows with Null Values
• Implicit method: Omit the column from the
column list.
INSERT INTO departments (department_id,
department_name )
VALUES (30, 'Purchasing');
1 row created.

• Explicit method: Specify the NULL keyword in the

VALUES clause.
INSERT INTO departments
VALUES (100, 'Finance', NULL, NULL);
1 row created.
Inserting Special Values
The SYSDATE function records the current date
and time.
INSERT INTO employees (employee_id,
first_name, last_name,
email, phone_number,
hire_date, job_id, salary,
commission_pct, manager_id,
department_id)
VALUES (113,
'Louis', 'Popp',
'LPOPP', '515.124.4567',
SYSDATE, 'AC_ACCOUNT', 6900,
NULL, 205, 100);
1 row created.
Inserting Specific Date Values
• Add a new employee.
INSERT INTO employees
VALUES (114,
'Den', 'Raphealy',
'DRAPHEAL', '515.127.4561',
TO_DATE('FEB 3, 1999', 'MON DD, YYYY'),
'AC_ACCOUNT', 11000, NULL, 100, 30);
1 row created.
• Verify your addition.
Copying Rows
from Another Table
• Write your INSERT statement with a subquery.

INSERT INTO sales_reps(id, name, salary, commission_pct)

SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

4 rows created.

• Do not use the VALUES clause.

• Match the number of columns in the INSERT clause
to those in the subquery.
Changing Data in a Table
EMPLOYEES

Update rows in the EMPLOYEES table.

The UPDATE Statement Syntax

• Modify existing rows with the UPDATE

statement.
UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];

• Update more than one row at a time, if required.

Updating Rows in a Table
• Specific row or rows are modified if you specify the
WHERE clause.
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;
1 row updated.

• All rows in the table are modified if you omit the

WHERE clause.

UPDATE copy_emp
SET department_id = 110;
22 rows updated.
Problem
Update employee 114’s job and salary to match that of employee 205.
Updating Two Columns with a
Subquery
Update employee 114’s job and salary to match that of
employee 205.
UPDATE employees
SET job_id = (SELECT job_id
FROM employees
WHERE employee_id = 205),
salary = (SELECT salary
FROM employees
WHERE employee_id = 205)
WHERE employee_id = 114;
1 row updated.
Problem
Updates the COPY_EMP table based on the values from the EMPLOYEES table. It
changes the department number of all employees with employee 200’s job ID to
employee 100’s current department number.
Updating Rows Based
on Another Table
Use subqueries in UPDATE statements to update
rows in a table based on values from another table.
UPDATE copy_emp
SET department_id = (SELECT department_id
FROM employees
WHERE employee_id = 100)
WHERE job_id = (SELECT job_id
FROM employees
WHERE employee_id = 200);
1 row updated.
Updating Rows:
Integrity Constraint Error

UPDATE employees
SET department_id = 55
WHERE department_id = 110;

UPDATE employees
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EMP_DEPT_FK)
violated - parent key not found
Removing a Row from a Table
DEPARTMENTS

Delete a row from the DEPARTMENTS table.

The DELETE Statement
You can remove existing rows from a table by using
the DELETE statement.

DELETE [FROM] table

[WHERE condition];
Deleting Rows from a Table
• Specific rows are deleted if you specify the WHERE
clause.
DELETE FROM departments
WHERE department_name = 'Finance';
1 row deleted.
• All rows in the table are deleted if you omit the
WHERE clause.

DELETE FROM copy_emp;

22 rows deleted.
Deleting Rows Based
on Another Table
Use subqueries in DELETE statements to remove
rows from a table based on values from another table.
DELETE FROM employees
WHERE department_id =
(SELECT department_id
FROM departments
WHERE department_name LIKE '%Public%');
1 row deleted.
52
Happy
Learning!