Replace function in SQL

Replace function is use to replace a string to the string that we want. Replace function takes three parameter

Syntax of the replace function is:-

REPLACE ('STRING1','STRING2','STRING3');

Here:-

STRING1 is the values from which you need to replace a string.

STRING2 is the value by which you need to replace a string.

STRING3 is the values which is going to be replaced.

Example is:-

SELECT OREPLACE ('MYSELF','MY','');

O_REPLACE
----------------
SELF

SQL> SELECT OREPLACE ('MYSELF123','123',' CHANCHAL.');

O_REPLACE
----------------
MYSELF CHANCHAL.




Read Also:-  Replace function in Teradata

OREPLACE Function in Teradata

Replace function is use to replace a string to the string that we want. Replace function takes three parameter
In Teradata, there is no such function called replace but there is a function called OREPLACE, but we need to install the function.

You can download and install the function from below link.

DOWNLOAD OREPLACE FUNCTION

Syntax of the replace function is:-

OREPLACE ('STRING1','STRING2','STRING3');

Here:-

STRING1 is the values from which you need to replace a string.

STRING2 is the value by which you need to replace a string.

STRING3 is the values which is going to be replaced.

Example is:-

SEL OREPLACE ('MYSELF','MY','') ;

O_REPLACE
----------------
SELF

SEL OREPLACE ('MYSELF123','123',' CHANCHAL.') ;

O_REPLACE
----------------
MYSELF CHANCHAL.




Read Also:-  Replace function in MySql

Replace Function in MySql

Replace function is use to replace a string to the string that we want. Replace function takes three parameter

Syntax of the replace function is:-

REPLACE ('STRING1','STRING2','STRING3');

Here:-

STRING1 is the values from which you need to replace a string.

STRING2 is the value by which you need to replace a string.

STRING3 is the values which is going to be replaced.

Example is:-

SELECT REPLACE ('MYSELF','MY','') O_REPLACE FROM DUAL;

O_REPLACE
----------------
SELF

SQL> SELECT REPLACE ('MYSELF123','123',' CHANCHAL.') O_REPLACE FROM DUAL;

O_REPLACE
----------------
MYSELF CHANCHAL.




Read Also:- Replace in Oracle

Replace Function in Oracle

Replace function is use to replace a string to the string that we want. Replace function takes three parameter

Syntax of the replace function is:-

REPLACE ('STRING1','STRING2','STRING3');

Here:-

STRING1 is the values from which you need to replace a string.

STRING2 is the value by which you need to replace a string.

STRING3 is the values which is going to be replaced.

Example is:-

SQL> COLUMN O_REPLACE FORMAT A10

SQL> SELECT REPLACE ('MYSELF','MY','') O_REPLACE FROM DUAL;

O_REPLACE
----------
SELF

SQL> SELECT REPLACE ('MYSELF123','123',' CHANCHAL.') O_REPLACE FROM DUAL;

O_REPLACE
----------------
MYSELF CHANCHAL.




Read Also:-  Replace in MySql

Auto Increment Column in Greenplum

We can create auto increment column in Greenplum which insert the value in the column automatically.

Syntax is:-

CREATE TABLE TABLE_NAME
(
COLUMN_NAME DATA_TYPE NULL || NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH [INTEGER], INCREMENT BY [INTEGER], MAXVALUE [INTEGER]),
COLUMN_NAME DATA_TYPE 
COLUMN_NAME DATA_TYPE,
....
);

While creating IDENTITY or AUTO INCREMENT column, make sure you have specified number families data type like int, number, decimal etc.

Let's have an example:-

CREATE TABLE EMPLOYEE
(
EMPNO INT PRIMARY KEY DEFAULT nextval('serial'),
EMPNAME TEXT(20),
SALARY INT,
DEPTNO INT
);

When you will insert a value into the table, it will insert 1 in the EMPNO column and further increment by 1.

When you insert a value into the table you would need to mention the column list. If you want to insert data into
the above table then you will have to use the statement something like below:-

INSERT INTO EMPLOYEE (EMPNAME,SALARY,DEPTNO)
VALUES ('CHANCHAL',1000,10);

Read Also:-  Auto Increment in SQL

Auto Increment Column in Teradata

We can create auto increment column in Teradata which insert the value in the column automatically.

Syntax is:-

CREATE SET || MULTISET TABLE TABLE_NAME
(
COLUMN_NAME DATA_TYPE NULL || NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH [INTEGER], INCREMENT BY [INTEGER], MAXVALUE [INTEGER]),
COLUMN_NAME DATA_TYPE 
COLUMN_NAME DATA_TYPE,
....
);

While creating IDENTITY column, make sure you have specified number families data type like int, number, decimal etc.

Here:-

START WITH is the value from which we need to start to insert into the table.

INCREMENT BY is the value by which we need to increment the value of the column.

MAXVALUE is the value until sequence will keep generating the value.


Let's have an example:-

CREATE MULTISET TABLE EMPLOYEE
(
EMPNO INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, MAXVALUE 999999999999),
EMPNAME VARCHAR(20),
SALARY INT,
DEPTNO INT
);

When you will insert a value into the table, it will insert 1 in the EMPNO column and further increment by 1.

When you insert a value into the table you would need to mention the column list. If you want to insert data into
the above table then you will have to use the statement something like below:-

INSERT INTO EMPLOYEE (EMPNAME,SALARY,DEPTNO)
VALUES ('CHANCHAL',1000,10);

Read Also:-  Auto Increment Column in MySql

Auto Increment Column in Oracle

Before Oracle 11G, we could not been able to use auto increment column as such, but we would have to create a sequence and then we have to use trigger to insert the unique number. But in Oracle 11G, We can directly use sequence in the column, but still its not auto increment column. In Oracle 12C, Oracle have provide the actual auto increment column.

So we will see how to do it in Oracle 11G and Oracle 12C.

Let's try to do it in Oracle 11G:-

In oracle 11G, we need to first create a sequence, then we can use nextval psuedo columnn to insert the value.

Simple example is:-

CREATE SEQUENCE SEQUENCE_NAME 
MINVALUE MINIMUM_VALUE,
START WITH START_VALUE,
INCREMENT BY INCREMENT_VALUE,
CYCLE || NOCYCLE,
CACHE || NOCACHE;

Here:-

MINVALUE is the value from oracle will store as minimum value for that particular sequence.

START WITH is the value which is going to get inserted at the first time.

INCREMENT BY is the values by which you have to increase the next value to insert.

CYCLE  Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.

CACHE Specify how many values of the sequence the database pre-allocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers.

Let's see the example:-

CREATE SEQUENCE SEQ_TEST
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE,
NORECYCLE;

Now we have a sequence, let's try to create a table in Oracle 11G using the above sequence:-

CREATE TABLE EMPLOYEE
(
EMPNO NUMBER DEFAULT SEQ_TEST.NEXTVAL,
EMPNAME VARCHAR2(20),
SALARY NUMBER,
DEPTNO NUMBER
);

You need to mention DEFAULT clause while creating a table.

When you will insert a value into the table, it will insert 1 in the EMPNO column and further increment by 1.

When you insert a value into the table you would need to mention the column list. If you want to insert data into
the above table then you will have to use the statement something like below:-

INSERT INTO EMPLOYEE (EMPNAME,SALARY,DEPTNO)
VALUES ('CHANCHAL',1000,10);


In Oracle 12C, we create directly create auto increment column. We don't need to use sequence there.




Read Also:-  Auto Increment Column in Teradata

Auto Increment Column in MySql

We can create auto increment column in MySql which insert the value in the column automatically.

Syntax is:-

CREATE TABLE TABLE_NAME
(
COLUMN_NAME DATA_TYPE AUTO_INCREMENT NOT NULL || CONSTRAINT TYPE,
COLUMN_NAME DATA_TYPE 
COLUMN_NAME DATA_TYPE,
....
);


While creating IDENTITY column, make sure you have specified number families data type like int, number, decimal etc.

Let's have an example:-

CREATE TABLE EMPLOYEE
(
EMPNO INTEGER AUT_INCREMENT PRIMARY KEY,
EMPNAME VARCHAR(20),
SALARY INT,
DEPTNO INT
);

When you will insert a value into the table, it will insert 1 in the EMPNO column and further increment by 1.

When you insert a value into the table you would need to mention the column list. If you want to insert data into
the above table then you will have to use the statement something like below:-

INSERT INTO EMPLOYEE (EMPNAME,SALARY,DEPTNO)
VALUES ('CHANCHAL',1000,10);

Read Also:-  Auto Increment Column in Oracle

Auto Increment Column in SQL

We can create auto increment column in SQL which insert the value in the column automatically.

Syntax is:-

CREATE TABLE TABLE_NAME
(
COLUMN_NAME DATA_TYPE IDENTITY ([START WITH],[INCREMENT BY]),
COLUMN_NAME DATA_TYPE,
.....
);

Here:-

START WITH is the values in number from which you need to start the sequence.

INCREMENT BY is the value in number by which you need to increment the sequence.


While creating IDENTITY column, make sure you have specified number families data type like int, number, decimal etc.


Let's have an example:-

CREATE TABLE EMPLOYEE
(
EMPNO INT IDENTITY (1,1),
EMPNAME VARCHAR(20),
SALARY INT,
DEPTNO INT
);

When you will insert a value into the table, it will insert 1 in the EMPNO column and further increment by 1.

When you insert a value into the table you would need to mention the column list. If you want to insert data into
the above table then you will have to use the statement something like below:-

INSERT INTO EMPLOYEE (EMPNAME,SALARY,DEPTNO)
VALUES ('CHANCHAL',1000,10);




Read Also:-  Auto Increment Column in Greenplum

DBMS_LOCK.SLEEP in Oracle

In Oracle, the DBMS_LOCK.SLEEP procedure is used to make a session sleep for a specified period of time. This can be useful in various scenarios such as delaying the execution of certain tasks or implementing wait logic in PL/SQL code. The syntax for using DBMS_LOCK.SLEEP is as follows:

DBMS_LOCK.SLEEP(seconds);

Here, seconds is the number of seconds for which the session will sleep. The session will remain inactive for the specified duration before resuming execution. It's important to note that the DBMS_LOCK.SLEEP procedure is typically used in PL/SQL code rather than in SQL queries.

here's a real-life example of how you might use DBMS_LOCK.SLEEP in Oracle:

Let's say you have a scenario where you need to implement a delay between certain operations in a PL/SQL procedure. For instance, you're developing a batch processing system where you need to wait for a specific amount of time between processing each record to avoid overwhelming a downstream system.

Here's how you might use DBMS_LOCK.SLEEP in this scenario:

DECLARE

    v_record_count NUMBER := 100; -- Number of records to process

    v_processing_delay_seconds NUMBER := 5; -- Delay between processing each record (in seconds)

BEGIN

    FOR i IN 1..v_record_count LOOP

        -- Process the record

        -- Some processing logic here

        

        -- Wait for the specified delay before processing the next record

        DBMS_LOCK.SLEEP(v_processing_delay_seconds);

    END LOOP;

END;

/

In this example, before processing each record within the loop, the PL/SQL code calls DBMS_LOCK.SLEEP to pause the execution for 5 seconds. This delay ensures that each record is processed with a gap of 5 seconds between them, allowing for better control over the processing rate. This can be useful in scenarios where you need to avoid overloading external systems or resources.

PRIMARY INDEX in Teradata

Primary index is used to distribute the data on all the AMP’s.

While creating table, PRIMARY INDEX is mandatory in Teradata. When we try to creating a table in Teradata, Teradata will look whether primary index specified. If the index is not specified, secondly it will look for Primary key constraint to make the primary index. If you have not specified primary index or primary constraints then Teradata always take first column as a primary index. It’s always good to provide primary index while creating a table as its helps Teradata to spread the data on all the AMP’s.

CREATE MULTISET TABLE EMPLOYEE

(

EMPNO INT,

EMPNAME VARCHAR(500)

DEPT VARCHAR(20)

)

PRIMARY INDEX(EMPNO); 

please note:- Above we have created multiset table.

Read Also:-  

Difference between YYYY and RRRR

The difference between YYYY and RRRR is, YYYY Is used to show/fetch/store current year in 4 digit whereas RRRR assumes 2 digit year in the range of 0 to 49 in the current century and in the range of 50 to 99 are assume to be in the previous century.

YYYY format may not work for in below case:-

SELECT TO_CHAR(TO_DATE(’21-08-14’,’DD-MM-YYYY’),’DD-MM-YYYY’) DT FROM DUAL;

SELECT TO_CHAR(TO_DATE('21-08-51','DD-MM-YYYY'),'DD-MM-YYYY') DT FROM DUAL;

Let’s try to use RRRR format:-

SELECT TO_CHAR(TO_DATE(’21-08-14’,’DD-MM-RRRR’),’DD-MM-YYYY’) DT FROM DUAL;

SELECT TO_CHAR(TO_DATE('21-08-51','DD-MM-RRRR'),'DD-MM-YYYY') DT FROM DUAL;

But when you want to use only with YYYY format then you should not provide 4 digit year instate you can provide only 2 digit number as shown below:-

SELECT TO_CHAR(TO_DATE(’21-AUG-14’,’DD-MM-YY’),’DD-MM-YYYY’) DT FROM DUAL;

And below statement will not work for you:-

SELECT TO_CHAR(TO_DATE(’21-08-14’,’DD-MM-YYYY’),’DD-MM-YYYY’) DT FROM DUAL;

Read Also:-  

Data types in MySql

There are many data types in MySql. Some of them are listed below:-

1.       INT

2.       TINYINT 

3.       SMALLINT 

4.       MEDIUMINT 

5.       BIGINT 

6.       FLOAT(M,D) 

7.       DOUBLE(M,D)

8.       DECIMAL(M,D)

9.       DATE 

10.   DATETIME 

11.   TIMESTAMP

12.   TIME 

13.   YEAR(M)

14.   CHAR(M) 

15.   VARCHAR(M)

16.   BLOB or TEXT

Read Also:-  Data types in Greenplum

Data types in Greenplum

There are many data types in Greenplum. Some of them are:-

Name	Length	description
bigint	8 bytes	large range integer
boolean	1 byte	logical Boolean (true/false)
character [ (n) ]	1 byte + n	fixed-length, blank padded
character varying [ (n) ]	1 byte + string size	variable-length with limit
date	4 bytes	calendar date (year, month, day)
decimal [ (p, s) ]	variable	user-specified precision, exact
integer	4 bytes	usual choice for integer
real	4 bytes	variable-precision, inexact
serial	4 bytes	auto incrementing integer
smallint	2 bytes	small range integer
text	1 byte + string size	variable unlimited length
time [ (p) ] [ without time zone ]	8 bytes	time of day only
time [ (p) ] with time zone	12 bytes	time of day only, with time zone
timestamp [(p)] [without time zone ]	8 bytes	both date and time
timestamp [ (p) ] with time zone	8 bytes	both date and time, with time zone
xml	1 byte + xml size	variable unlimited length
money	4 bytes	currency amount

Here are five frequently asked questions (FAQs) about data types in Greenplum:-

1. What are the primary data types supported by Greenplum?

   Greenplum supports a wide range of data types including basic types like integer, character, and boolean, as well as more complex types such as arrays, JSON, XML, and spatial data types.

2. How does Greenplum handle data type conversion?

   Greenplum performs implicit data type conversion when possible, but explicit casting may be required in some cases to ensure accurate results. It's important to be aware of potential data loss or errors when converting between different data types.

3. Are there any limitations on data type sizes in Greenplum?

   Yes, Greenplum imposes limits on the size of certain data types. For example, the maximum size for a character varying type is 1 GB, and the maximum precision for numeric types is 1000 digits.

4. Can I define custom data types in Greenplum?

   Yes, Greenplum allows users to create custom data types using the `CREATE TYPE` statement. This feature can be useful for representing complex data structures or domain-specific data.

5. How does Greenplum handle data types across distributed systems?

   Greenplum's MPP (Massively Parallel Processing) architecture distributes data across multiple segments, each running on separate nodes in a cluster. Data types are managed consistently across these segments to ensure compatibility and efficient processing of queries. However, it's important to consider data distribution and localization when designing queries involving complex data types in distributed environments.

These FAQs should provide a foundational understanding of data types in Greenplum, but for more detailed information, consulting the official documentation or seeking assistance from experienced users is recommended.

Read Also:-  Data types in oracle

View in MySql

View is a virtual table which does not have it's own data, it's derived it's data from other table called Underline or Base table.

We can create view on a table or another views.

Syntax of the view is:-

CREATE VIEW VIEW_NAME AS SELECT STATEMENT;

Below is example :-

CREATE VIEW VW_EMP AS SELECT  EMP_ID,EMP_NAME FROM EMPLOYEE;





Read Also:-  Create view in Oracle

Foreign Key Constraint in MySql

Foreign Key is a referential kind of constraint in any database. whenever you will try to insert a records
into a column which is foreign key, it will first check whether the value is present in a parent key (Primary Key) Without which it will not allow you to insert a records into the table.

To create a foreign key, there must be a primary key. We cannot create foreign key constraint referencing any other constraint.

Syntax of the Foreign key is:-

CREATE TABLE TABLE_NAME
(
COLUMN_NAME DATA_TYPE
COLUMN_NAME DATA_TYPE
.,
.,
(N),
FOREIGN KEY (COLUMN_NAME) REFERENCES REF_TABLE_NAME(COLUMN_NAME)
);

Below is an example:-

Creating a Primary Key :-

CREATE TABLE EMPLOYEE
(
EMP_ID INT,
EMP_NAME VARCHAR(20),
EMP_ADR VARCHAR(20),
EMP_DEPT_NO INT NOT NULL
PRIMARY KEY(EMP_DEPT_NO) 
);

Creating Foreign Key :-

CREATE TABLE DEPARTMENT
(
DEPT_NO INT NOT NULL,
DEPT_NAME VARCHAR(20)
FOREIGN KEY (DEPT_NO) REFERENCES EMPLOYEE(EMP_DEPT_NO)
);

Read Also:-  Constraint in Oracle

Index in MySql

Index helps queries to fetch data quickly. Index optional structure associated with the database to have direct access to the table row which in term improves the performance of the query.

Syntax of the Index is :-

CREATE INDEX INDEX_NAME on TABLE_NAME (COLUMN_NAME | [LIST]);

Below is the example:-

CREATE TABLE EMPLOYEE
(
EMP_ID INT,
EMP_NAME VARCHAR(20),
EMP_ADR VARCHAR(20), 
);

CREATE INDEX IND_EMP ON EMPLOYEE (EMP_ID);






Read Also:-  Create index in Oracle

ADD_MONTHS function in Teradata

ADD_MONTHS function takes two parameters, one is date from which you need to fetch the month and the number as how many months after you wan to fetch the data.

Syntax is :-

ADD_MONTHS( DATE, N)

Example is :-

SELECT ADD_MONTHS('2014-08-21',1) AFT_MNT;

2014-08-21SELECT ADD_MONTHS('2014-08-21',-1) BFR_MNT;

2014-07-21





Read Also:-  ADD_MONTHS function in Oracle 

Primary Key Constraint in MySql

We can create Primary Key constraint in MySql with the help of follwoing syntax:-

create table table_name
(
column_name data_type,
column_name data_type,
....
PRIMARY KEY (COLUMN_NAME)
);

Below is the example:-

CREATE TABLE EMPLOYEE
(
EMP_ID INT,
EMP_NAME VARCHAR(20),
EMP_ADR VARCHAR(20),
PRIMARY KEY(EMP_ID) 
);

Read also:- Constraint in Oracle

Create table in MySql

We can create table in MySql with the help of following syntax:-

create table table_name
(
column_name data_type,
column_name data_type
.....
(n)
);

Below is the example:-

CREATE TABLE EMPLOYEE
(
EMP_ID INT,
EMP_NAME VARCHAR(20),
EMP_ADR VARCHAR(20), 
);




Read Also:- Create table in Oracle

Trunc function with dates

Oracle Trunc function is used to return a specified period. Trunc function can be use to return beginning period of DAY, MONTH, YEAR, QUARTER.

If you want to fetch the current year then you can use trunc.

Syntax is :-

TRUNC (DATE,'DAY' | 'MONTH' | 'YEAR' | 'Q')

Example is :-


SELECT SYSDATE FROM DUAL;

SELECT TRUNC(SYSDATE,'DAY') WEEK_START_DAY FROM DUAL;

SELECT TRUNC(SYSDATE,'MONTH') WEEK_START_MONTH FROM DUAL;

SELECT TRUNC(SYSDATE,'YEAR') WEEK_START_YEAR FROM DUAL;

SELECT TRUNC(SYSDATE,'Q') WEEK_START_QUARTER FROM DUAL;

 

Here are 5 frequently asked questions (FAQs) about using the TRUNC function with dates in Oracle:-

1. What is the TRUNC function in Oracle?

   - The TRUNC function in Oracle is used to truncate a date value to a specified unit of time (such as year, month, day, hour, etc.). It removes the time portion of the date and retains only the specified part. For example, truncating a date to 'MONTH' would remove the day and time portion, keeping only the year and month.

2. How do I use the TRUNC function with dates in Oracle?

   - To use the TRUNC function with dates, you specify the date expression and the desired truncation unit as arguments. For example:

     

     SELECT TRUNC(SYSDATE, 'MONTH') FROM DUAL;

     

     This query truncates the current date to the beginning of the month.

3. Can I use the TRUNC function to truncate to different units of time?

   - Yes, the TRUNC function allows you to truncate dates to different units of time, such as year, month, day, hour, minute, etc. You specify the desired unit as the second argument. For example:

     

     SELECT TRUNC(SYSDATE, 'YEAR') FROM DUAL;

     

     This query truncates the current date to the beginning of the year.

4. What happens if I don't specify a truncation unit with TRUNC?

   - If you don't specify a truncation unit with the TRUNC function, it truncates the date to the nearest day. For example:

     

     SELECT TRUNC(SYSDATE) FROM DUAL;

     

     This query truncates the current date to midnight of the same day.

5. Can I use TRUNC with custom formats in Oracle?

   - No, the TRUNC function in Oracle does not support custom date formats. You can only truncate dates to predefined units of time such as year, month, day, etc. If you need custom formatting, you may need to use other date manipulation functions or formatting options available in Oracle.

These FAQs should provide a good understanding of how to use the TRUNC function with dates in Oracle and its various capabilities.

ADD_MONTHS Function

The ADD_MONTHS functions is use to fetch the date plus the months. Month is the number you have provided. In simple words it will fetch the data after (n) months. However we can fetch the data before (n) months by specifying - (Minus) before the number.

ADD_MONTHS function takes two parameters, one is date from which you need to fetch the month and the number as how many months after you wan to fetch the data.

Syntax is :-

ADD_MONTHS( DATE, N)

Example is :-

SELECT ADD_MONTHS(SYSDATE,1) AFT_MNT FROM DUAL;

SELECT ADD_MONTHS(TO_DATE('21-08-2014','DD-MM-YYYY'),1) AFT_MNT FROM DUAL;

SELECT ADD_MONTHS('21-AUG-2014',1) AFT_MNT FROM DUAL;

Using Minus :-

SELECT ADD_MONTHS(SYSDATE,-1) AFT_MNT FROM DUAL;

SELECT ADD_MONTHS(TO_DATE('21-08-2014','DD-MM-YYYY'),-1) AFT_MNT FROM DUAL;

SELECT ADD_MONTHS('21-AUG-2014',-1) AFT_MNT FROM DUAL;

Read Also:-  

Extract Function

We can extract Year, Month, Day  from a Date. We can also extract Hour, Minute and Second from Timestamp with the help of EXTRACT function.

Below are some examples:-

SELECT EXTRACT(YEAR FROM SYSDATE) EXT_YEAR FROM DUAL;

SELECT EXTRACT(MONTH FROM SYSDATE) EXT_MNTH FROM DUAL;

SELECT EXTRACT(DAY FROM SYSDATE) EXT_DAY FROM DUAL;

Remember, the data format should be DD-MON-YYYY or DD/MM/YYYY.

We can Extract Hour, Minute, Seconds from timestamp only :-

SELECT EXTRACT (HOUR FROM SYSTIMESTAMP) EXT_HR FROM DUAL;

SELECT EXTRACT (MINUTE FROM SYSTIMESTAMP) EXT_MIN FROM DUAL;

SELECT EXTRACT (SECOND FROM SYSTIMESTAMP) EXT_SEC FROM DUAL;

Read Also:-  

CEIL function

CEIL returns smallest integer greater than or equal to n.

Ceil (n)

Where n is a number.

This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.

select CEIL(10.40) from dual;

select CEIL(10.50) from dual;

select CEIL(10.60) from dual;