0% found this document useful (0 votes)
11 views12 pages

singlerowfunctions

The document contains SQL commands executed in Oracle Database 11g, showcasing various queries on employee and region data. It includes operations such as selecting, concatenating, and manipulating string and date data, as well as extracting specific components from dates. The output demonstrates the structure and contents of the 'emp' and 'regions' tables, along with examples of string functions and date extraction.

Uploaded by

pratikkalaghan1
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
11 views12 pages

singlerowfunctions

The document contains SQL commands executed in Oracle Database 11g, showcasing various queries on employee and region data. It includes operations such as selecting, concatenating, and manipulating string and date data, as well as extracting specific components from dates. The output demonstrates the structure and contents of the 'emp' and 'regions' tables, along with examples of string functions and date extraction.

Uploaded by

pratikkalaghan1
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 12

SQL*Plus: Release 11.2.0.1.

0 Production on Wed Jul 24 14:14:53 2024

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: scott


Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set lines 100 pages 100;


SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
123 vijay actor
7369 SUMANTH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10

15 rows selected.

SQL> connect
Enter user-name: hr
Enter password:
Connected.
SQL> select * from regions;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

SQL> select upper(region_name)


2 from regions;

UPPER(REGION_NAME)
-------------------------
EUROPE
AMERICAS
ASIA
MIDDLE EAST AND AFRICA

SQL> select upper('sql')


2 from dual;

UPP
---
SQL

SQL> select * from tab;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE

8 rows selected.

SQL> describe dual;


Name Null? Type
----------------------------------------------------- --------
------------------------------------
DUMMY VARCHAR2(1)

SQL> select * from dual;

D
-
X

SQL> select upper('java')


2 from dual;

UPPE
----
JAVA

SQL> select lower(REGION_NAME)


2 FROM REGIONS;
LOWER(REGION_NAME)
-------------------------
europe
americas
asia
middle east and africa

SQL> select lower('WATER')


2 FROM DUAL;

LOWER
-----
water

SQL> SELECT INITCAP(REGION_NAME)


2 FROM REGIONS;

INITCAP(REGION_NAME)
-------------------------
Europe
Americas
Asia
Middle East And Africa

SQL> SELECT INITCAP('ENEMY')


2 FROM EMP;
FROM EMP
*
ERROR at line 2:
ORA-00942: table or view does not exist

SQL> SELECT INITCAP('ENEMY')


2 FROM DUAL;

INITC
-----
Enemy

SQL> SELECT * FROM REGIONS;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

SQL> CONN SCOTT/Oracle1234;


Connected.
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
123 vijay actor
7369 SUMANTH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10

15 rows selected.

SQL> select concat(job,hiredate)


2 from emp;n
3
SQL> select concat(job,hiredate)
2 from emp;

CONCAT(JOB,HIREDAT
------------------
actor
CLERK17-DEC-80
SALESMAN20-FEB-81
SALESMAN22-FEB-81
MANAGER02-APR-81
SALESMAN28-SEP-81
MANAGER01-MAY-81
MANAGER09-JUN-81
ANALYST19-APR-87
PRESIDENT17-NOV-81
SALESMAN08-SEP-81
CLERK23-MAY-87
CLERK03-DEC-81
ANALYST03-DEC-81
CLERK23-JAN-82

15 rows selected.

SQL> select concat(ename,'sir')


2 from emp;

CONCAT(ENAME,
-------------
vijaysir
SUMANTHsir
ALLENsir
WARDsir
JONESsir
MARTINsir
BLAKEsir
CLARKsir
SCOTTsir
KINGsir
TURNERsir
ADAMSsir
JAMESsir
FORDsir
MILLERsir

15 rows selected.

SQL> select concat('mahendra','dhoni')


2 from dual;

CONCAT('MAHEN
-------------
mahendradhoni

SQL> select concat('mahendra',' dhoni')


2 from dual;

CONCAT('MAHENDR
---------------
mahendra dhoni

SQL> select concat(upper('mahendra'),upper('dhoni'))


2 from dual;

CONCAT(UPPER(
-------------
MAHENDRADHONI

SQL> select substr('ANIMATIONS',1,4)


2 FROM DUAL;

SUBS
----
ANIM

SQL> select substr('ANIMATIONS',4,3)


2 FROM DUAL;

SUB
---
MAT

SQL> select substr('ANIMATIONS',8,3)


2 FROM DUAL;

SUB
---
ONS

SQL> select substr('ANIMATIONS',5,4)


2 FROM DUAL;

SUBS
----
ATIO

SQL> select substr('ANIMATIONS',1,1)


2 FROM DUAL;

S
-
A

SQL> SELECT SUBSTR(ENAME,1,1)


2 FROM EMP;

S
-
v
S
A
W
J
M
B
C
S
K
T
A
J
F
M

15 rows selected.

SQL> SELECT LENGTH('ANIMATIONS')


2 FROM DUAL;

LENGTH('ANIMATIONS')
--------------------
10

SQL> SELECT LENGTH('ANIMALS')


2 FROM DUAL;

LENGTH('ANIMALS')
-----------------
7

SQL> SELECT LENGTH(JOB)


2 FROM EMP
3
SQL> SELECT JOB,LENGTH(JOB)
2 FROM EMP;
JOB LENGTH(JOB)
--------- -----------
actor 5
CLERK 5
SALESMAN 8
SALESMAN 8
MANAGER 7
SALESMAN 8
MANAGER 7
MANAGER 7
ANALYST 7
PRESIDENT 9
SALESMAN 8
CLERK 5
CLERK 5
ANALYST 7
CLERK 5

15 rows selected.

SQL> SELECT REPLACE('ANIMATIONS','A','$')


2 FROM DUAL;

REPLACE('A
----------
$NIM$TIONS

SQL> SELECT REPLACE('ANIMATIONS','A','@')


2 FROM EMP;

REPLACE('A
----------
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS

15 rows selected.

SQL> SELECT MOD(4,2)


2 FROM DUAL;

MOD(4,2)
----------
0

SQL> SELECT MOD(5,2)


2 FROM DUAL;
MOD(5,2)
----------
1

SQL> SELECT MOD(20,3)


2 FROM DUAL;

MOD(20,3)
----------
2

SQL> SELECT SYSDATE


2 FROM DUAL;

SYSDATE
---------
24-JUL-24

SQL> SELECT SYSDATE-1


2 FROM DUAL;

SYSDATE-1
---------
23-JUL-24

SQL> SELECT SYSDATE+1


2 FROM DUAL;

SYSDATE+1
---------
25-JUL-24

SQL> SELECT * FROM EMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
123 vijay actor
7369 SUMANTH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10

15 rows selected.

SQL> SELECT HIREDATE,EXTRACT(YEAR FROM HIREDATE)


2 FROM EMP;

HIREDATE EXTRACT(YEARFROMHIREDATE)
--------- -------------------------

17-DEC-80 1980
20-FEB-81 1981
22-FEB-81 1981
02-APR-81 1981
28-SEP-81 1981
01-MAY-81 1981
09-JUN-81 1981
19-APR-87 1987
17-NOV-81 1981
08-SEP-81 1981
23-MAY-87 1987
03-DEC-81 1981
03-DEC-81 1981
23-JAN-82 1982

15 rows selected.

SQL> SELECT HIREDATE,EXTRACT(DAY FROM HIREDATE)


2 FROM EMP;

HIREDATE EXTRACT(DAYFROMHIREDATE)
--------- ------------------------

17-DEC-80 17
20-FEB-81 20
22-FEB-81 22
02-APR-81 2
28-SEP-81 28
01-MAY-81 1
09-JUN-81 9
19-APR-87 19
17-NOV-81 17
08-SEP-81 8
23-MAY-87 23
03-DEC-81 3
03-DEC-81 3
23-JAN-82 23

15 rows selected.

SQL> SELECT HIREDATE,EXTRACT(MONTH FROM HIREDATE)


2 FROM EMP;

HIREDATE EXTRACT(MONTHFROMHIREDATE)
--------- --------------------------

17-DEC-80 12
20-FEB-81 2
22-FEB-81 2
02-APR-81 4
28-SEP-81 9
01-MAY-81 5
09-JUN-81 6
19-APR-87 4
17-NOV-81 11
08-SEP-81 9
23-MAY-87 5
03-DEC-81 12
03-DEC-81 12
23-JAN-82 1

15 rows selected.

SQL> SELECT * FROM EMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
123 vijay actor
7369 SUMANTH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10

15 rows selected.
SQL> DELETE FROM EMP WHERE ENAME='vijay';

1 row deleted.

SQL> SELECT * FROM EMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SUMANTH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> select ename,sal,comm, sal+comm


2 from emp;

ENAME SAL COMM SAL+COMM


---------- ---------- ---------- ----------
SUMANTH 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975
MARTIN 1250 1400 2650
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500 0 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300

14 rows selected.

SQL> select ename,sal,comm,sal+nvl(comm,0)


2 from emp;

ENAME SAL COMM SAL+NVL(COMM,0)


---------- ---------- ---------- ---------------
SUMANTH 800 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975 2975
MARTIN 1250 1400 2650
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
TURNER 1500 0 1500
ADAMS 1100 1100
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300

14 rows selected.

SQL> select to_char(sysdate,'yyyy year mm month mon dy day dd')


2 from dual;

TO_CHAR(SYSDATE,'YYYYYEARMMMONTHMONDYDAYDD')
---------------------------------------------------------------------------------
2024 twenty twenty-four 07 july jul wed wednesday 24

SQL>

You might also like