Scribd
Upload
14 views7 pages

Row Date Functions

The document contains SQL commands demonstrating various date and table operations in SQL*Plus, including date functions like SYSDATE, ADD_MONTHS, and MONTHS_BETWEEN. It also shows how to create tables, insert data, and perform queries such as MINUS and FULL JOIN. The results of these operations are displayed, illustrating the functionality of SQL in managing and retrieving data.

Uploaded by

Kulasekara Pandian
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
14 views7 pages

Row Date Functions

The document contains SQL commands demonstrating various date and table operations in SQL*Plus, including date functions like SYSDATE, ADD_MONTHS, and MONTHS_BETWEEN. It also shows how to create tables, insert data, and perform queries such as MINUS and FULL JOIN. The results of these operations are displayed, illustrating the functionality of SQL in managing and retrieving data.

Uploaded by

Kulasekara Pandian
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SQL*Plus: Release [Link].

0 Production on Wed May 11 [Link] 2022


SQL> select sysdate from dual;
SYSDATE
---------
11-MAY-22

SQL> select add_months('14-Apr-1977',12) from dual;


ADD_MONTH
---------
14-APR-78

SQL> select greatest ('14-Apr-1977','15-Apr-1977','16-Apr-1977') from dual;


GREATEST('1
-----------
16-Apr-1977

SQL> select least ('14-Apr-1978','14-Apr-1977','16-Apr-1979') from dual;


LEAST('14-A
-----------
14-Apr-1977

SQL> select next_day(sysdate,'Wednesday')from dual;


NEXT_DAY(
---------
18-MAY-22

SQL> select next_day(sysdate,'Friday')from dual;


NEXT_DAY(
---------
13-MAY-22

SQL> select months_between('14-Apr-1978','14-Apr-1977')from dual;


MONTHS_BETWEEN('14-APR-1978','14-APR-1977')
-------------------------------------------
12

SQL> select months_between('14-Apr-1977','14-Apr-1978')from dual;


MONTHS_BETWEEN('14-APR-1977','14-APR-1978')
-------------------------------------------
-12
SQL> create table R(A number(1), B number(1), C number(1));
Table created.

SQL> Insert into R values(4,2,1);


1 row created.

SQL> Insert into R values(3,2,4);


1 row created.

SQL> Insert into R values(2,1,3);


1 row created.

SQL> Insert into R values(1,3,5);


1 row created.

SQL> select*from R;

A B C
---------- ---------- ----------
4 2 1
3 2 4
2 1 3
1 3 5
SQL> select*from R where(C>3);

A B C
---------- ---------- ----------
3 2 4
1 3 5

SQL> select A,B from R where (c=3);

A B
---------- ----------
2 1

SQL> create table S(U number(1), V number(1), W number(1));


Table created.

SQL> Insert into S values(4,2,1);


1 row created.

SQL> Insert into S values(3,4,2);


1 row created.

SQL> Insert into S values(2,1,3);


1 row created.
SQL> Insert into S values(3,1,5);
1 row created.

SQL> select*from S;

U V W
---------- ---------- ----------
4 2 1
3 4 2
2 1 3
3 1 5

SQL> select A,B,C from R MINUS select U,V,W from S;

A B C
---------- ---------- ----------
1 3 5
3 2 4

FULL JOIN: FULL JOIN creates the result-set by combining result of both
LEFT JOIN and RIGHT JOIN.
The result-set will contain all the rows from both the tables. The rows for which
there is no matching,
the result-set will contain NULL values.

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

select R.A, R.B, R.C from R full join S on R.A=S.U;

A B C
---------- ---------- ----------
4 2 1
3 2 4
2 1 3
3 2 4
1 3 5

You might also like