Unknown

Oracle 11g -(Class 2)-Oracle 11g -SQL Statement





****==========REPLACE****==========





UserName: HR

SQL> select first_name from employees;





First_Name Ellen Sundar Mozhe David Hermann Shelli Amit Elizabeth Sarah David Laura Harrison Alexis Anthony

Change with

select replace(first_name, 'S', 'F') from employees where employee_id=100

SQL> select replace(first_name, 'E', 'F') from employees;

replace(first_name, 'E', 'F') Fllen Sundar Mozhe David Hermann Shelli Amit Flizabeth Sarah David

****==========TRIM****==========



Trim - is to take away.



TRIM ('H' from HelloWorld') = elloWorld

select trim ('S' from first_name)from employees where employee_id=100





SQL>

select trim('E' from first_name) from employees;







TRIM('E'FROMFIRST_NAME llen Sundar Mozhe David Hermann Shelli Amit lizabeth Sarah David

****==========Number Function*****==========

round(45.926, 2) = 45.93 - if it positive it will affect afte decimale

round(45.926, -2) = 45.93 - if it negative it will effect whole number

trunc (45.926, 2) = 45.92

mod (1600, 300) = 100 Which is divide by and the remaining

***=======DUAL is for function and calculation and its a dummy table***======

SQL>

select round(45.926, 2) from dual;







ROUND(45.926,2)

---------------

45.93

SQL> select trunc(45.926, 2) from dual;



TRUNC(45.926,2)

---------------

45.92

SQL> select round(45.926,-1)from dual;



ROUND(45.926,-1)

----------------

50

SQL> select round(33.926,-1)from dual;



ROUND(33.926,-1)

----------------

30

SQL> select mod(1600, 300) from dual;



MOD(1600,300)

-------------

100

****==========Date****===========

sysdate - current system date and time

SQL> select sysdate from dual;



SYSDATE

---------

18-AUG-12

SQL> alter session set nls_date_format='dd-mon-yy hh24:mi:ss';



Session altered.

SQL> select sysdate from dual; //--database time



SYSDATE

------------------

18-aug-12 03:46:44

nls = national language standard

*****=======More functions******=========





MONTHS_BETWEEN--(SYSDATE, hire_date)

ADD_MONTHS--(hire_date, 6) REVIEW

NEXT_DAY--(hire_date, 'FRIDAY')

LAST_DAY--(hire_date)

ROUND

TRUNC

SQL> Connect HR

Connected





SQL> show user

USER is " HR "

TENURE??? - is an allias --how many days



--Below display the employee number, hire date, number of months employed, six-month review date, first Friday after hire date, and the last day of the hire month for

all employees who have been employed for fewer than 150 monthsl

SELECT employee_id, hire_date, MONTHS_BETWEEN (SYSDATE, hire_date) TENURE, ADD_MONTHS (hire_date, 6) REVIEW, NEXT_DAY (hire_date, 'FRIDAY'), LAST_DAY (hire_date) FROM employees;

SQL> SELECT employee_id, hire_date, MONTHS_BETWEEN (SYSDATE, hire_date) TENURE, ADD_MONTHS (hire_date, 6) REVIEW, NEXT_DAY (hire_date, 'FRIDAY'), LAST_DAY (hire_date)

FROM employees;

EMPLOYEE_ID HIRE_DATE TENURE REVIEW NEXT_DAY(HIRE_DATE LAST_DAY(HIRE_DATE 100 17-Jun-03 170.1431205 17-Dec-03 20-Jun-03 30-Jun-03 101 21-Sep-05 143 21-Mar-06 23-Sep-05 30-Sep-05 102 13-Jan-01 199.2721528 13-Jul-01 19-Jan-01 31-Jan-01 103 03-Jan-06 139.5947334 03-Jul-06 06-Jan-06 31-Jan-06 104 21-May-07 123 21-Nov-07 25-May-07 31-May-07 105 25-Jun-05 145.885056 25-Dec-05 01-Jul-05 30-Jun-05 106 05-Feb-06 138.5302173 05-Aug-06 10-Feb-06 28-Feb-06 107 07-Feb-07 126.4657012 07-Aug-07 09-Feb-07 28-Feb-07 108 17-Aug-02 180.1431205 17-Feb-03 23-Aug-02 31-Aug-02 109 16-Aug-02 180.1753786 16-Feb-03 23-Aug-02 31-Aug-02

*****========Every column has data type****========

Date type: Implicit data type/ Explicit data type

number - 0-9 and .(decimal)

varchar2 - variable character(char, number, special char)

date - any date format

char - A to Z

SQL> desc employees



Name Null? Type ** this is data type

----------------------------------------------------- -------- ------------------------------------

EMPLOYEE_ID NOT NULL NUMBER(6)

FIRST_NAME VARCHAR2(20)

LAST_NAME NOT NULL VARCHAR2(25)

HIRE_DATE NOT NULL DATE

***for salary 9000 to $9,000.00 (it is done by conversion function)

Conversion Functions: To_char, to_number, and To_date)

Digit is number 0-9.

Character is alphabe A - Z.

SQL> select to_number(22+2) sum from dual;

SQL> select to_number(22/2) mod from dual;

SQL> select to_number(22-2) substract from dual;

Reactions:

**************Thank You************