How to import and export data in Oracle-11g and 12 c

Step by step procedure to the impdp/expdp utilities 

Step1-Login into the system

username : Sys@database_name as sysdba 
password : manager

Step 2-check the session status

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session WHERE status ='INACTIVE';

Step 3-drop the user  
drop user user1 cascade;
drop user user1_log cascade;

Step 4-Create the users user1 and user1_log  

create user user1 identified by database_password;
create user user1_log identified by database_password;

step 5-grant the permission 

grant dba to user1,user1_log;
grant read, write on directory database_name_DUMP to user1,user1_log;
alter system set sec_case_sensitive_logon=false;

Step6-Check the directories and set the path 

SELECT * FROM DBA_DIRECTORIES;

DUMP PATH: D:\database_name\DUMP

Command Prompt:

Step7-for import using the impdp utilities

impdp user1@database_name/database_password directory=database_name_DUMP dumpfile= EXPDP_user1.DMP schemas=user1 logfile=user1imp

impdp user1_log@database_name/database_password directory=database_name_DUMP dumpfile= EXPDP_user1_LOG.DMP schemas=user1_log logfile=user1_logimp



       *************************
 Step8-for export using the expdp utilities

expdp user1@database_name/database_password directory=database_name_DUMP dumpfile=user1.DMP schemas=user1  logfile=user1EXP

expdp user1_log@database_name/database_password directory=database_name_DUMP dumpfile=user1_log.dmp schemas=user1_LOG logfile=user1_logEXP

SQL-soundex function

set serveroutput on;
set headsep !;
ttitle 'Employee Information ' ;
btitle 'From Sarat Chand';
set linesize 5000;
set pagesize 10;
set newpage 0;

select * from employees where employee_id in(100,101,102,201,202,103);

Neena
select * from employees where soundex(last_name)=soundex('King');

SQL-Working With Dates 1-Example exercise based on date data type of employee table for HR schema

--1. sysdate,hiredate  as general format
================================

SQL> select sysdate from dual;

SYSDATE
---------
14-JUL-17

SQL> select hire_date from employees where employee_id=100;

HIRE_DATE
---------
17-JUN-87

SQL>

--2 Display the only year of hire date  for employee_id 100,101,102;
===============================================================

SQL> select employee_id,to_char(hire_date,'YYYY')year from employees where employee_id in (100,101,102);

EMPLOYEE_ID YEAR
-----------               ----
        100               1987
        101               1989
        102               1993

SQL> select to_char(sysdate,'YYYY')year from dual;

YEAR
----
2017

SQL>

--3 Display the only month in full word of hire date  for employee_id 100,101,102;
===============================================================

SQL> select employee_id,to_char(hire_date,'MONTH')MONTH from employees where employee_id in (100,101,102);

EMPLOYEE_ID  MONTH
-----------               ---------
        100               JUNE
        101               SEPTEMBER
        102              JANUARY

--4 Display the only DAY_of_week of hire date for employee_id 100,101,102;
==========================================================

SQL> select employee_id,to_char(hire_date,'DAY')DAY_of_week from employees where employee_id in (100,101,102);

EMPLOYEE_ID    DAY_OF_WE
-----------                 ---------
        100                WEDNESDAY
        101                THURSDAY
        102                WEDNESDAY

SQL>

--5 Display the only DAY_of_week in three letter  of hire date  for employee_id 100,101,102;
=====================================================================

SQL> select employee_id,to_char(hire_date,'DY')DAY_of_week from employees where employee_id in (100,101,102);

EMPLOYEE_ID       DAY
-----------                      ---
        100                    WED
        101                    THU
        102                    WED

--6 Display the only month in three letters word  of hire date  for employee_id 100,101,102;
==================================================================

SQL> select employee_id,to_char(hire_date,'MON')MON from employees where employee_id in (100,101,102);

EMPLOYEE_ID MON
-----------                ---
        100               JUN
        101               SEP
        102               JAN

--7 Display the only year in words of hire date for employee_id 100,101,102;
==============================================================
SQL> select employee_id,to_char(hire_date,'YEAR')year from employees where employee_id in (100,101,102);

EMPLOYEE_ID                 YEAR
-----------                   ------------------------------------------
        100                   NINETEEN EIGHTY-SEVEN
        101                   NINETEEN EIGHTY-NINE
        102                  NINETEEN NINETY-THREE

SQL>

--8 Write a query to find the list of employees who were hire before 1990.
=======================================================

SQL> select last_name,to_char(hire_date,'DD-Mon-yyyy') from employees where to_date(hire_date,'DD-Mon-RR')<'01-Jan-1990';

LAST_NAME                 TO_CHAR(HIR
-------------------------     -----------
King                                 17-Jun-1987
Kochhar                           21-Sep-1989
Whalen                             17-Sep-1987

SQL>

--9  find the numetic day of month whose last_name are King,Austin,Lorentz;
=========================================================

SQL> select last_name,hire_date,to_char(hire_date,'DD')"Numeric Day of Month" from employees where last_name in('King','Austin','Lorentz');

LAST_NAME           HIRE_DATE     Numeric Day Of Month
------------------------- ---------                     --
Austin                    25-JUN-97                 25
King                      30-JAN-96                  30
King                      17-JUN-87                  17
Lorentz                   07-FEB-99                 07


10-find two digit of  value of month whose last_name are King,Austin,Lorentz;
=========================================================
LAST_NAME  HIRE_DATE Two Digit Value Of Month
Austin 25-JUN-97      06
King 30-JAN-96      01
King 17-JUN-87      06
Lorentz 07-FEB-99      02

PL/SQL Procedure- Create the procedure add_jobs on the jobs table of HR schema.

PL/SQL Procedure-Insert data into Table
======================================================================

create or replace procedure add_jobs(v_job_id  jobs.job_id%type,v_job_title   jobs.job_title%type,v_max_salary   jobs.max_salary%type,v_min_salary  jobs.min_salary%type)
is
begin
insert into jobs(job_id, job_title,min_salary,max_salary) values(v_job_id ,v_job_title ,v_min_salary ,v_max_salary );
end;
/

=======================================================================
--Method 1-call the procedure  
========================================================================

begin
add_jobs('DM','Dupty Manager',11000,1000);
end;
/

--Verify the data inserted into the table jobs

SQL> select * from jobs where job_id='DM';

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
DM         Dupty Manager                             1000      11000

SQL>
--========================================================================
Method 2 :Ask user to enter the  values  into jobs  table and verify the data
=============================================================

SQL> begin
  2  add_jobs( '&v_job_id' ,'&v_job_title' ,&v_min_salary ,&v_max_salary);
  3  end;
  4  /
Enter value for v_job_id: HR1
Enter value for v_job_title: HR Dept
Enter value for v_min_salary: 500
Enter value for v_max_salary: 3500
old   2: add_jobs( '&v_job_id' ,'&v_job_title' ,&v_min_salary ,&v_max_salary);
new   2: add_jobs( 'HR1' ,'HR Dept' ,500 ,3500);

PL/SQL procedure successfully completed.

--check the inserted data into the table 

SQL> select * from jobs where job_id='HR1';

JOB_ID     JOB_TITLE                           MIN_SALARY    MAX_SALARY
---------- ----------------------------------- ----------                       ----------
HR1        HR Dept                                    500                            3500

SQL>

Program6-Write a program to find the area of Square in PL/SQL

Method-1:
======================================================================
Suppose Square have length and width of equal size .so area of Square =side*side
Input-let its  side is a
Output-Area of the Square
Operation- Square_area=a*a

Code-
SQL> set serveroutput on;
SQL> declare
  2   a  number;
  3   Begin
  4  a :=20;
  5  Dbms_output.put_line('Area of the Square='||a*a);
  6  End;
  7  /
Area of the Square=400

PL/SQL procedure successfully completed.

======================================================================
Method-2:
======================================================================
Input-Ask user to enter side of the square and store the result into third variable result.
Output- Area of the square
Operation- result=a*a

SQL> declare
  2   a  number;
  3  result number;
  4   Begin
  5  a :=&a;
  6  result:=a*a;
  7  Dbms_output.put_line('Area of the Square='||result);
  8  End;
  9  /
Enter value for a: 10
old   5: a :=&a;
new   5: a :=10;
Area of the Square=100

PL/SQL procedure successfully completed.

Program5-Write a program to calculate the simple interest in PL/SQL

Method-1:
==========================================================
Input-let p=1000 ,r=10 and t=10
Output- Simple interest (si) =1000
Calculation -Let Principal(P),Rate (r% per annum) and Time = t years Then
Simple Interest, si = P*r*t/100
===========================================================
Code-
SQL> set serveroutput on;
SQL> declare
  2  p number;
  3  r number;
  4  t number;
  5  si number;
  6  Begin
  7  p :=1000;
  8  r :=10;
  9  t :=10;
 10  si:=(p*r*t)/100;
 11  Dbms_output.put_line('The Simple Interest='||si);
 12  End;
 13  /
The Simple Interest=1000

PL/SQL procedure successfully completed.
======================================================================

Method-2:
======================================================================
Input-Ask user to enter the principal amount, rate of interest and time take and store the result into third variable si .
Output- simple interest
Operation-Simple Interest, si = P*r*t/100
========================================
Code-
SQL> declare
  2  p number;
  3  r number;
  4  t number;
  5  si number;
  6  Begin
  7  p :=&p;
  8  r :=&r;
  9  t :=&t;
 10  si :=(p*r*t)/100;
 11  Dbms_output.put_line('The Simple Interest='||si);
 12  End;
 13  /
Enter value for p: 1000
old   7: p :=&p;
new   7: p :=1000;
Enter value for r: 10
old   8: r :=&r;
new   8: r :=10;
Enter value for t: 10
old   9: t :=&t;
new   9: t :=10;
The Simple Interest=1000

PL/SQL procedure successfully completed.

SQL>

Program4-Write a program to find the area of rectangle in PL/SQL

Method-1:
=================
Suppose rectangle have length and breadth .so area of rectangle is multiplication of length and width.
Input-length=20 meters and width=10 meters
Output-200 meter square
Operation- area of rectangle=length*width

Code-
SQL> set serveroutput on;
SQL> declare
  2      length  number;
  3      width  number;
  4      Begin
  5         length  :=20;
  6         width :=10;
  7     Dbms_output.put_line('Area of the Rectangle='||length*width);
  8     End;
  9  /
Area of the Rectangle=200

PL/SQL procedure successfully completed.
========================================================================

Method-2:
=======================
Input-Take two variables length and width  and store the result into third variable result.
Output- Area of the rectangle
Operation- result=length*width

SQL> declare
  2      length  number;
  3      width  number;
  4     result number;
  5      Begin
  6         length  :=20;
  7         width :=10;
  8        result:=length*width;
  9     Dbms_output.put_line('Area of the Rectangle='||result);
 10     End;
 11  /
Area of the Rectangle=200

PL/SQL procedure successfully completed.

SQL>
===============================================================
Method-3:
====================
Input-Ask user to enter two number  length and width  and store the result into third variable rec_area.
Output- rec_area
Operation-Area of rectangle

Code-
SQL> declare
  2      length  number;
  3      width  number;
  4      rec_area number;
  5      Begin
  6         length :=&length ;
  7         width :=&width;
  8      rec_area :=length*width;
  9
 10     Dbms_output.put_line('Area of the Rectangle='||rec_area);
 11     End;
 12  /
Enter value for length: 20
old   6:        length :=&length ;
new   6:        length :=20 ;
Enter value for width: 5
old   7:        width :=&width;
new   7:        width :=5;
Area of the Rectangle=100

PL/SQL procedure successfully completed.

SQL>