Oracle Database

If you have a date, and you want to increment it by 1. You can do this by adding the date with a date interval. You can also do this by adding the number 1 directly on the date. The tutorial example below shows you how to adding numbers to dates, and take date differences:

SELECT TO_DATE('30-APR-06') + 1 FROM DUAL;

-- Adding 1 day to a date

01-MAY-06

SELECT TO_DATE('01-MAY-06') - TO_DATE('30-APR-06')

FROM DUAL;

-- Taking date differences

1

SELECT SYSTIMESTAMP + 1 FROM DUAL;

-- The number you add is always in days.

08-MAY-06

SELECT TO_CHAR(SYSTIMESTAMP+1,'DD-MON-YYYY HH24:MI:SS.FF3')

FROM DUAL;

-- Error: Adding 1 to a timestamp makes it a date.

If you want to know how many years, months, days and seconds are there between two dates or times, you can use the date and time interval expressions: YEAR ... TO MONTH and DAY ... TO SECOND. The tutorial exercise below gives you some good examples:

SELECT

(TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')

-

TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))

YEAR(4) TO MONTH FROM DUAL;

-- 109 years and 3 months

109-3

SELECT

(TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')

-

TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))

DAY(9) TO SECOND FROM DUAL;

-- 39901 days and some seconds

39901 7:26:7.0

SELECT

(TO_TIMESTAMP('01-MAY-2006 16:52:57.847',

'DD-MON-YYYY HH24:MI:SS.FF3') -

TO_TIMESTAMP('31-JAN-1897 09:26:50.124',

'DD-MON-YYYY HH24:MI:SS.FF3'))

YEAR(4) TO MONTH FROM DUAL;

-- 109 years and 3 months

109-3

SELECT

(TO_TIMESTAMP('01-MAY-2006 16:52:57.847',

'DD-MON-YYYY HH24:MI:SS.FF3') -

TO_TIMESTAMP('31-JAN-1897 09:26:50.124',

'DD-MON-YYYY HH24:MI:SS.FF3'))

DAY(9) TO SECOND

FROM DUAL;

-- 39

LIKE condition is also called pattern patch. There 3 main rules on using LIKE condition:

* '_' is used in the pattern to match any one character.

* '%' is used in the pattern to match any zero or more characters.

* ESCAPE clause is used to provide the escape character in the pattern.

The following script provides you some good pattern matching examples:

SELECT CASE WHEN 'rendc.org' LIKE '%Center%'

THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;

TRUE

SELECT CASE WHEN 'rendc.org' LIKE '%CENTER%'

THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;

-- Case sensitive by default

FALSE

SELECT CASE WHEN 'rendc.org' LIKE '%Center_com'

THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;

TRUE

SELECT CASE WHEN '100% correct' LIKE '100% %' ESCAPE ''

THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;

TRUE

Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the CREATE TABLE...AS SELECT statement to do this. Here is an example script:

>.insqlplus /nolog

SQL> connect HR/globalguideline

Connected.

SQL> CREATE TABLE emp_dept_10

2 AS SELECT * FROM employees WHERE department_id=10;

Table created.

SQL> SELECT first_name, last_name, salary

2 FROM emp_dept_10;

FIRST_NAME   LAST_NAME    SALARY

-------------------- ------------------------- ----------

Jennifer   Whalen     4400

As you can see, this SQL scripts created a table called "emp_dept_10" using the same column definitions as the "employees" table and copied data rows of one department.

This is really a quick and easy way to create a table.

If you have an existing table with existing data rows, and want to add a new column to that table, you can use the ALTER TABLE ... ADD statement to do this. Here is an example script:

SQL> connect HR/globalguideline

Connected.

SQL> CREATE TABLE emp_dept_110

2 AS SELECT * FROM employees WHERE department_id=110;

Table created.

SQL> ALTER TABLE emp_dept_110 ADD (vacation NUMBER);

Table altered.

SQL> SELECT first_name, last_name, vacation

2 FROM emp_dept_110;

FIRST_NAME   LAST_NAME    VACATION

-------------------- ------------------------- ----------

Shelley   Higgins

William   Gietz

This SQL script added a new column called "vacation" to the "emp_dept_110" table. NULL values were added to this column on all existing data rows.

If you have an existing column in a table and you need that column any more, you can delete it with ALTER TABLE ... DROP COLUMN statement. Here is an example SQL script:

SQL> CREATE TABLE emp_dept_90

2 AS SELECT * FROM employees WHERE department_id=90;

Table created.

SQL> SELECT last_name FROM emp_dept_90;

LAST_NAME

-------------------------

King

Kochhar

De Haan

SQL> ALTER TABLE emp_dept_90 DROP COLUMN last_name;

Table altered.

SQL> SELECT last_name FROM emp_dept_90;

ERROR at line 1:

ORA-00904: "LAST_NAME": invalid identifier

As you can see the column "last_name" is gone.

Let's say you have an existing index, and you don't like its name anymore for some reason, you can rename it with the ALTER INDEX ... RENAME TO statement. Here is an example script on how to rename an index:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL,

birth_date DATE NOT NULL,

social_number VARCHAR(80) UNIQUE NOT NULL);

Table created.

SELECT index_name, table_name, uniqueness

FROM USER_INDEXES WHERE table_name = 'STUDENT';

INDEX_NAME   TABLE_NAME  UNIQUENES

----------------------- --------------------- ---------

SYS_C004153   STUDENT   UNIQUE

SYS_C004154   STUDENT   UNIQUE

ALTER INDEX SYS_C004153 RENAME TO student_pk;

Statement processed.

SELECT index_name, table_name, uniqueness

FROM USER_INDEXES WHERE table_name = 'STUDENT';

INDEX_NAME   TABLE_NAME  UNIQUENES

----------------------- --------------------- ---------

STUDENT_PK   STUDENT   UNIQUE

SYS_C004154   STUDENT   UNIQUE

If you don't need an existing index any more, you should delete it with the DROP INDEX statement. Here is an example SQL script:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL,

birth_date DATE NOT NULL,

social_number VARCHAR(80) UNIQUE NOT NULL);

Table created.

CREATE INDEX student_birth_date ON student(birth_date);

Index created.

SELECT index_name, table_name, uniqueness

FROM USER_INDEXES WHERE table_name = 'STUDENT';

INDEX_NAME   TABLE_NAME  UNIQUENES

----------------------- --------------------- ---------

SYS_C004129   STUDENT   UNIQUE

SYS_C004130   STUDENT   UNIQUE

STUDENT_BIRTH_DATE STUDENT   NONUNIQUE 

DROP INDEX STUDENT_BIRTH_DATE;

Index dropped.

You can create a new view based on one or more existing tables by using the CREATE VIEW statement as shown in the following script:

CREATE VIEW employee_department AS

SELECT e.employee_id, e.first_name, e.last_name,

e.email, e.manager_id, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id;

View created.

SELECT first_name, last_name, department_name

FROM employee_department WHERE manager_id = 101;

FIRST_NAME   LAST_NAME   DEPARTMENT_NAME

-------------------- ------------------- ----------------

Nancy   Greenberg   Finance

Jennifer   Whalen   Administration

Susan   Mavris   Human Resources 

Hermann   Baer   Public Relations

Shelley   Higgins   Accounting