Notes

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BOOKS TABLE
DEPARTMENT TABLE
EMPLOYEE TABLE
ISSUE TABLE
RECEIVED TABLE
ST1 TABLE
ST2 TABLE
STUD TABLE
STUDENTS TABLE

9 rows selected.

SQL> desc st1;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
ROLL NUMBER(10)
MARKS NUMBER(3)
CGPA NUMBER(6,2)

SQL> select * from st1;

NAME ROLL MARKS CGPA
---------- ---------- ---------- ----------
Pradeepta 50 159 6.23
Aman 51 259 7.23
Aman 52 269 7.29
Aman 53 279 8.29
Aasan 54 289 8.27
Basan 55 299 8.25
Balan 56 259 9.25
Byean 57 254 7.25
Cyean 58 274 4.25
Dyean 59 264 8.25

10 rows selected.

SQL> create view Rollno as select * from st1 where roll=53;

View created.

SQL> select * from Rollno;

NAME ROLL MARKS CGPA
---------- ---------- ---------- ----------
Aman 53 279 8.29

SQL> create view Rollono as select * from st1;

View created.

SQL> select * from Rollono;

NAME ROLL MARKS CGPA
---------- ---------- ---------- ----------
Pradeepta 50 159 6.23
Aman 51 259 7.23
Aman 52 269 7.29
Aman 53 279 8.29
Aasan 54 289 8.27
Basan 55 299 8.25
Balan 56 259 9.25
Byean 57 254 7.25
Cyean 58 274 4.25
Dyean 59 264 8.25

10 rows selected.

SQL> desc rollno
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
ROLL NUMBER(10)
MARKS NUMBER(3)
CGPA NUMBER(6,2)

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BOOKS TABLE
DEPARTMENT TABLE
EMPLOYEE TABLE
ISSUE TABLE
RECEIVED TABLE
ROLLNO VIEW
ROLLONO VIEW
ST1 TABLE
ST2 TABLE
STUD TABLE
STUDENTS TABLE

11 rows selected.

SQL> desc employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(15)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(6)
COMM NUMBER(5)
DEPTNO NUMBER(5)

SQL> desc department;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(15)
LOC VARCHAR2(10)

SQL> select * from department;

no rows selected

SQL> select * from employee;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH 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


EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------------- ---------- --------- ---------- ----------
DEPTNO
----------
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


EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 09-NOV-81 3000
20

7839 KING PESIDENT 17-NOV-81 5000
10


EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 23-SEP-81 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30


EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10


14 rows selected.

SQL> setline size 120;
SP2-0734: unknown command beginning "setline si..." - rest of line ignored.
SQL> set linesize 120;
SQL> select * from employee;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------------- ---------- --------- ---------- ---------- ----------
7369 SMITH 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 09-NOV-81 3000 20
7839 KING PESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-SEP-81 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------------- ---------- --------- ---------- ---------- ----------
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 count(job) from employee;

COUNT(JOB)
----------
14

SQL> select count(distinct job) from employee;

COUNT(DISTINCTJOB)
------------------
5

SQL> select sum(sal) from employee;

SUM(SAL)
----------
29025

SQL> select avg(sal) from employee;

AVG(SAL)
----------
2073.21429

SQL> select max(sal) from employee;

MAX(SAL)
----------
5000

SQL> select min(sal) from employee;

MIN(SAL)
----------
800

SQL> select last_day(sysdate) from dual;

LAST_DAY(
---------
29-FEB-20

SQL> select next_day from dual;
select next_day from dual
*
ERROR at line 1:
ORA-00904: "NEXT_DAY": invalid identifier


SQL> select abs(5),abs(-100) from dual;

ABS(5) ABS(-100)
---------- ----------
5 100

SQL> select ceil(-5.2),ceil(5.7) from dual;

CEIL(-5.2) CEIL(5.7)
---------- ----------
-5 6

SQL> select floor(-5.2),floor(5.7) from dual;

FLOOR(-5.2) FLOOR(5.7)
----------- ----------
-6 5

SQL> select exp(5) from dual;

EXP(5)
----------
148.413159

SQL> ln(2);
SP2-0042: unknown command "ln(2)" - rest of line ignored.
SQL> select ln(2) from dual;

LN(2)
----------
.693147181

SQL> select log(2,10) from dual;

LOG(2,10)
----------
3.32192809

SQL> select mod(15,4) from dual;

MOD(15,4)
----------
3

SQL> select pow(15,4) from dual;
select pow(15,4) from dual
*
ERROR at line 1:
ORA-00904: "POW": invalid identifier


SQL> select power(15,4) from dual;

POWER(15,4)
-----------
50625

SQL> select sqrt(25) from dual;

SQRT(25)
----------
5

SQL> select round(15.19,1),round(15.19) from dual;

ROUND(15.19,1) ROUND(15.19)
-------------- ------------
15.2 15

SQL> select truncate(15.19,1),round(15.19) from dual;
select truncate(15.19,1),round(15.19) from dual
*
ERROR at line 1:
ORA-00904: "TRUNCATE": invalid identifier


SQL> select trun(15.19,1),round(15.19) from dual;
select trun(15.19,1),round(15.19) from dual
*
ERROR at line 1:
ORA-00904: "TRUN": invalid identifier


SQL> select truncate(15.19,1),round(15.19) from dual;
select truncate(15.19,1),round(15.19) from dual
*
ERROR at line 1:
ORA-00904: "TRUNCATE": invalid identifier


SQL> select truncate(15.19,1) from dual;
select truncate(15.19,1) from dual
*
ERROR at line 1:
ORA-00904: "TRUNCATE": invalid identifier


SQL> select Truncate(15.19,1) from dual;
select Truncate(15.19,1) from dual
*
ERROR at line 1:
ORA-00904: "TRUNCATE": invalid identifier


SQL> select TRUNCATE(15.19,1) from dual;
select TRUNCATE(15.19,1) from dual
*
ERROR at line 1:
ORA-00904: "TRUNCATE": invalid identifier


SQL> select TRUNCATE(15.19,1) from dual;
select TRUNCATE(15.19,1) from dual
*
ERROR at line 1:
ORA-00904: "TRUNCATE": invalid identifier


SQL> select TRUNC(15.19,1) from dual;

TRUNC(15.19,1)
--------------
15.1

SQL> select lpad('oracle',10,'*') from dual;

LPAD('ORAC
----------
****oracle

SQL> select rpad('oracle',10,'*') from dual;

RPAD('ORAC
----------
oracle****

SQL> select initcap('hello') from dual;

INITC
-----
Hello

SQL> select lower('heLLo') from dual;

LOWER
-----
hello

SQL> select upper('heLLo') from dual;

UPPER
-----
HELLO

SQL> select ltrim('heLLo') from dual;

LTRIM
-----
heLLo

SQL> select ltrim('heLLo',2) from dual;

LTRIM
-----
heLLo

SQL> select ltrim('heLLo','L') from dual;

LTRIM
-----
heLLo

SQL> select rtrim('heLLo','L') from dual;

RTRIM
-----
heLLo

SQL> select replace('oracle','rac','v') from dual;

REPL
----
ovle

SQL> select substr('database',3,2) from dual;

SU
--
ta

SQL> desc department;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(15)
LOC VARCHAR2(10)

SQL> insert into department values(10,'ACCOUNTING','NEW YORK');

1 row created.

SQL> insert into department values(20,'RESEARCH','DALLAS');

1 row created.

SQL> insert into department values(30,'SALES','CHICAGO');

1 row created.

SQL> insert into department values(40,'OPERATIONS','BOSTON');

1 row created.

SQL> SELECT * FROM department;

DEPTNO DNAME LOC
---------- --------------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> CREATE TABLE SALGRADE
2 (
3 GRADE NUMBER(1),
4 LOSAL NUMBER(5
5
SQL> CREATE TABLE SALGRADE
2 (
3 GRADE NUMBER(1),
4 LOSAL NUMBER(5),
5 HISAL NUMBER(5)
6 );

Table created.

SQL> DESC SALGRADE;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
GRADE NUMBER(1)
LOSAL NUMBER(5)
HISAL NUMBER(5)

SQL> insert into salgrade values(1,700,1200);

1 row created.

SQL> insert into salgrade values(2,1201,1400);

1 row created.

SQL> insert into salgrade values(3,1401,2000);

1 row created.

SQL> insert into salgrade values(4,2001,3000);

1 row created.

SQL> insert into salgrade values(5,3001,9999);

1 row created.

SQL> select * from salgrade;

GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

SQL> commit;

Commit complete.

SQL>