Oracle Database FAQs (Page 10)

91. How To Concatenate Two Text Values in Oracle?
There are two ways to concatenate two text values together:

* CONCAT() function.
* '||' operation.

Here is some examples on how to use them:

SELECT 'ggl' || 'Center' || '.com' FROM DUAL;
atoztarget.com

SELECT CONCAT('atoztarget','.com') FROM DUAL;
atoztarget.com

92. How To Increment Dates by 1 in Oracle?
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.

93. How To Calculate Date and Time Differences in Oracle?
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

94. How To Use IN Conditions in Oracle?
An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE.
Some examples are given in the script below:

SELECT CASE WHEN 3 IN (1,2,3,5) THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

SELECT CASE WHEN 3 NOT IN (1,2,3,5) THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
FALSE

SELECT CASE WHEN 'Y' IN ('F','Y','I') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

95. How To Use LIKE Conditions in Oracle?
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 'atoztarget.com' LIKE '%Center%' THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

SELECT CASE WHEN 'atoztarget.com' LIKE '%CENTER%' THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
-- Case sensitive by default
FALSE

SELECT CASE WHEN 'atoztarget.com' 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

96. How To Use Regular Expression in Pattern Match Conditions in Oracle?
If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern patch function: REGEXP_LIKE().
The following script provides you some good examples:

SELECT CASE WHEN REGEXP_LIKE ('atoztarget.com', '.*ggl.*','i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

SELECT CASE WHEN REGEXP_LIKE ('atoztarget.com', '.*com$','i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

SELECT CASE WHEN REGEXP_LIKE ('atoztarget.com', '^F.*','i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

97. What Are DDL Statements in Oracle?
DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are 3 primary DDL statements:
* CREATE - Creating a new database object.
* ALTER - Altering the definition of an existing data object.
* DROP - Dropping an existing data object.

98. How To Create a New Table in Oracle?
If you want to create a new table in your own schema, you can log into the server with your account, and use the CREATE TABLE statement. The following script shows you how to create a table:
>.binsqlplus /nolog
SQL> connect HR/atoztarget
Connected.

SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,
2 subject VARCHAR(80) NOT NULL,
3 description VARCHAR(256) NOT NULL,
4 create_date DATE DEFAULT (sysdate));
Table created.
This scripts creates a testing table called "tip" with 4 columns in the schema associated with the log in account "HR".

99. How To Create a New Table by Selecting Rows from Another Table?
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:
>.binsqlplus /nolog
SQL> connect HR/atoztarget
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.

100. How To Add a New Column to an Existing Table in Oracle?
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/atoztarget
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.

More Questions & Answers :-

Page1  Page2  Page3  Page4  Page5  Page6  
Page7  Page8  Page9  Page10