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>

Program3- Advance concept : Write a program to add two number using the function and procedure.

Write a program to add two number using the function  named as find_sum  and procedure.

Method-1

Input-Suppose two input values are n1 and n2
Output-Add of two numbers in third variable

Code-
SQL> create or replace function find_sum
  2  (n1 in number, n2 in number)
  3  return number
  4  as
  5  v_sum number;
  6  begin
  7  v_sum := n1+n2;
  8  return v_sum;
  9  end;
 10  /

Function created.

--Perform the sum using function find_sum

SQL> select find_sum (10, 30) sum from dual;

      SUM
----------
        40

SQL> select find_sum (10, 30)  from dual;

FIND_SUM(10,30)
---------------
             40

SQL> select find_sum (10, 30) "Sum of two numbers" from dual;

Sum of two numbers
------------------
                40


=================================================================
Method-2 :Procedure
==================================================================

SQL> create or replace procedure find_sum_procedure
  2  (n1 in number, n2 in number)
  3  as
  4  v_sum number;
  5  begin
  6  v_sum := n1+n2;
  7  DBMS_OUTPUT.PUT_LINE('Sum of two numbers='||v_sum);
  8  end;
  9  /

Procedure created.

SQL> execute find_sum_procedure(10,20);

Sum of two numbers=30

PL/SQL procedure successfully completed.

SQL>

Program2-Write a program to add two number in PL/SQL

This program can be solved in many ways. So Let us understand how the add is performed
Method-1:
Suppose we have two constant value 2 and 3 .So we need the addition operator(+) to add these two numbers
Input-2 and 3
Output-5
Operation- addition so use addition operator (+)

Code-
SQL> set serveroutput on
SQL>
SQL>
SQL> Begin
  2  Dbms_output.put_line(2+3);
  3  End;
  4  /
5

PL/SQL procedure successfully completed.

SQL>


Method-2:
Suppose you want to add two values taken at run time
Input-Take two variable and store two value into it.Assume two variable are n1 and n2.
Output- addition a+b
Operation- addition (+)
Code-
SQL> declare
  2  n1 number;
  3  n2 number;
  4  Begin
  5  n1 :=2;
  6  n2 :=3;
  7  Dbms_output.put_line(n1+n2);
  8  End;
  9  /
5

PL/SQL procedure successfully completed.

SQL>



Method-3:
Take two number n1 and n2 and store it into third variable result.
Input-Take two variable and store the result into third variable result.
Output- Result of addition
Operation- addition (+)

SQL> declare
  2    n1 number;
  3    n2 number;
  4    result number;
  5    Begin
  6     n1 :=2;
  7     n2 :=3;
  8     result:=n1+n2;
  9    Dbms_output.put_line(result);
 10    End;
 11    /
5

PL/SQL procedure successfully completed.

SQL>
Method-4:
Ask user to enter two number n1 and n2 and store it into third variable result.
Input-Take two variable and store the result into third variable result.
Output- Result of addition
Operation- addition (+)

Code-
SQL> declare
  2    n1 number;
  3    n2 number;
  4    result number;
  5    Begin
  6     n1 :=&n1;
  7     n2 :=&n2;
  8     result:=n1+n2;
  9    Dbms_output.put_line(result);
 10    End;
 11    /
Enter value for n1: 10
old   6:    n1 :=&n1;
new   6:    n1 :=10;
Enter value for n2: 20
old   7:    n2 :=&n2;
new   7:    n2 :=20;
30

PL/SQL procedure successfully completed.

SQL>

Note- This program can be solved in many ways .I have taken from very basic to often used method.
I hope you can get the idea how to approach the problem to solve in real-time in PL/SQL.

SQL-Primary key and foreign key Relation/Parent-child relationship between tables/ On delete cascade/foreign key constraint with Example

SQL-Primary key and foreign key Relation/Parent-child relationship between tables/ On delete cascade/foreign key constraint with Example

Suppose we have two table customer and supplier
CREATE TABLE supplier
( supplier_id numeric(10) not null,
 supplier_name varchar2(50) not null,
 contact_name varchar2(50),
 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

insert into supplier values(1,'Sarat','Chand');
insert into supplier values(2,'Sartaj','Ahmed');
insert into supplier values(3,'Saral','Chand');


CREATE TABLE products
( product_id numeric(10) not null,
 supplier_id numeric(10) not null,
 CONSTRAINT fk_supplier
   FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id)
  ON DELETE CASCADE
);

insert into products values(101,1);
insert into products values(102,2);
insert into products values(103,3);

--Check/verify the entered data into the table using below command

select * from products;
select * from supplier ;

--not Allowed to add the product into the supplier table because the supplier id is not exist in the parent table name supplier

insert into products values(103,4);

--Error Message
--ORA-02291: integrity constraint (HR.FK_SUPPLIER) violated - parent key not found

So we can say that the table relation between table is established

--The data can be deleted from the supplier table because it is parent table but cannot delete the data into child table
--delete the data from supplier table whose supplier_id is 1;
delete from supplier where supplier_id=1;

delete from products where supplier_id=1;
--check the constraints
select * from user_cons_columns;
select * from user_constraints;

Program1- Write a program to print the Hello Message to the terminal

Solution-often we start writing the first program as hello Message.


So let us write the first hello program using various methods.

Method-1:Using Anonymous block 
Code-
SQL> begin
  2  dbms_output.put_line('HELLO');
  3  end;
  4  /
HELLO
PL/SQL procedure successfully completed.
SQL>

Method-2:Using PROCEDURE 
code-
SQL> SET SERVEROUTPUT ON
SQL> CREATE OR REPLACE PROCEDURE hello
  2  AS
  3  BEGIN
  4     dbms_output.put_line('Hello World!');
  5  END;
  6  /

Procedure created.

SQL>

Note- we can execute the procedure in two way -
Use SET SERVEROUTPUT ON  command to view the output on terminal-
1.
SQL> EXECUTE hello;
Hello World!

PL/SQL procedure successfully completed.
SQL>

2.
SQL> BEGIN
  2    hello;
  3  END;
  4  /
Hello World!

PL/SQL procedure successfully completed.
SQL>

Oracle-Concatenation Operator(||)

Concatenation Operator(||)

Concatenation operator is used to concatenate the column or string to another column or string and it is represented by two vertical bar (||).

SQL Statement-

select 'The employee Name '||Ename||' and employee id is '||empno As   "Employee Information" from emp

Result-
             Employee Information
The employee Name KING and employee id is 7839
The employee Name BLAKE and employee id is 7698
The employee Name CLARK and employee id is 7782
The employee Name JONES and employee id is 7566
The employee Name SCOTT and employee id is 7788
The employee Name FORD and employee id is 7902
The employee Name SMITH and employee id is 7369

Arithmatic Expression in MySQL-Addition,subtraction Multiplication and modulo operator

Arithmatic Expression in MySQL

Arithmatic Expression are the combination of arithmatic operator,constant numeric value and column name  in the SQL statements.
A list of arithmatic opertors are given below -
1.Addition(+)
2.Subtraction(-)
3.Multiplication(*)
4.Division(/)
5.Remainder or Modulo operator(%)

Note-
1.You can use arithmatic operator in any clause in SQL statement except FROM cluase.
2.The operator priority list is given below-
  * / + and -
 Priority Rule -Multiplication and division take the priority over Addition and Division.operators of same priority can be evaluated from left to right.
Parentheses are used to define the priority explicitly to remove the ambiguity.

Example: SQL Arithmetic Operators
1.SELECT 150+10-5*5/5;

Result:
Number of Records: 1
150+10-5*5/5
155

=======

Suppose we have the empsal table with five record and its details are given below-
Create table empsal ( id nuber , name varchar2(30),salary number(8,2));
insert into empsal(id,name, salary) values(1,'Ajay', 10000);
insert into empsal (id,name, salary) values(2,'Amit', 11000);
insert into empsal (id,name, salary) values(3,'Ashok', 7000);
insert into empsal (id,name, salary) values(4,'Aarif', 6000);
insert into empsal (id,name, salary) values(5,'Vinod', 5000);
2. verify the record
Result:
Number of Records: 5
id name salary
1 Ajay 10000
2 Amit 11000
3 Ashok 7000
4 Aarif 6000
5 Vinod 5000

SQL plus (+) operator:It is used to add two or more numbers/expressions
Example-
1.For Number computation
SELECT 150+10;

2.Write a query to find the the increment of the salary of employee by Rs 300.
SELECT Id, name , salary+300 as " Salary" from empsal;
Result:
Number of Records: 5
id name Salary
1 Ajay 10300
2 Amit 11300
3 Ashok 7300
4 Aarif 6300
5 Vinod 5300

2.SQL minus (-) operator -subtract one expression /number from another expression /number.

Example-
1.select 150-5;

Number of Records: 1
150-5
145

2.Write a query to display the Ashok monthly salary after PF deduction of Rs 1500.
 SELECT name, salary-1500 "Ashok In hand Salary" FROM empsal where id=3;

Result:
Number of Records: 1
name Ashok In hand Salary
Ashok 5500

3.SQL multiply ( * ) operator  used to multiply two or more expressions or numbers.

Example-

1.Display the Ashok's Annual salary.
SELECT Id, name , salary as "Monthly Salary", 12*salary as "Yearly Salary" from empsal

Result:
Number of Records: 1
id name Monthly Salary Yearly Salary
3 Ashok 7000         84000

4.SQL divide ( / ) operator-divide one expressions /numbers by another.
1.Write a query to share the Ashok's Annual salary among his four friends equally.
SELECT Id, name ,(12*salary)/4 as "Ashok's friend share" from empsal where id=3

Result:
Number of Records: 1
id name Ashok's friend share
3 Ashok 21000

Sorting ASC,DESC-ORDER BY clause display the rows in ASC|DESC order for an expression,column position or an alias.

ORDER BY Clause-
The ORDER BY clause is used to sort the data in ascending(its default ) or 
descending order of one or more columns and use the key word ORDER BY.
ORDER BY clause display the rows in ASC|DESC order for an expression,column position or an alias. 

Syntax-

SELECT   *|[DISTINCT]Column|expression [Alias]
From Table_Name
[WHERE <Condition(s)>]
[ORDER BY Column|Expression [ASC|DESC]];

SELECT -a list of columns
* -Select the all columns 
DISTINCT-suppresses the duplicates 
alias-alternate name of the colunm(s) that is/are used as the heading of that column(s).  
Table_name-specifies the table name containing the column(s). 
WHERE<Condition(s)>- the condition must meet to retrieve the data from the database.
ORDER BY Column|Expression -order the rows in Acending or descending order 

Example- 

1.Write the query to find employee information order by name.

Query-    SELECT * FROM employee order by name;
Output-
Number of Records: 5
ID Name email          address phone_number salary
2 Ajay ajay@gmail.com Hyderbad 16756890 51000
4 Aman Aman@gmail.com Lucknow         26756890 51000
3 Amit amit@gmail.com Delhi         16756890 51000
5 Hari Hari@gmail.com Lucknow         36756890 15000
1 sharat sarat@gmail.com Hyderbad 123456890 5000

2.Write the query to selects all employees from the "employee" table, sorted by the "Name" and the "Salary" column.

Query- SELECT * FROM employee order by name, salary;
Output-
Number of Records: 5
ID Name email          address phone_number salary
2 Ajay ajay@gmail.com Hyderbad 16756890 51000
4 Aman Aman@gmail.com Lucknow         26756890 51000
3 Amit amit@gmail.com Delhi         16756890 51000
5 Hari Hari@gmail.com Lucknow          36756890 15000
1 sharat sarat@gmail.com Hyderbad 123456890 5000

3.Display the name and the salary of the employees from the "employee" table, sorted ascending by the name and descending by the salary. 

Query- SELECT name,salary FROM employee order by name, salary DESC;
Output-
Number of Records: 5
Name salary
Ajay 51000
Aman 51000
Amit 51000
Hari 15000
sharat 5000

4.Display the name and the yearly salary of the employees from the employee table and sort by Annual_salary in ascending. 

Query-SELECT Name , salary*12  Annual_salary FROM employee  order by Annual_salary;
 Alternate -SELECT Name , salary*12  Annual_salary FROM employee  order by 2;
Output-
Number of Records: 5
Name Annual_salary
sharat 60000
Hari 180000
Ajay 612000
Amit 612000
Aman 612000

5.Display the name as Employee Name  and the yearly salary  as Annual_salary of the employees from the employee table and 
sorted descending order by the Annual_salary

Query-SELECT Name "Employee Name", salary*12  Annual_salary FROM employee  order by 2 DESC;
Output-
Number of Records: 5
Employee Name Annual_salary
Ajay           612000
Amit           612000
Aman           612000
Hari           180000
sharat           60000

WHERE Clause-To limit the retrieval of the data from the database for given condition

WHERE Clause
Where clause is used to limit the retrieval of the data from the database for given condition 
and use the key word WHERE.Where clause follows the FROM clause in the SQL Statement.

Syntax-

SELECT   *|[DISTINCT]Column|expression [Alias]
From Table_Name;
[WHERE <Condition(s)>]

SELECT -a list of columns
* -Select the all columns 
DISTINCT-suppresses the duplicates 
alias-alternate name of the column(s) that is/are used as the heading of that column(s).  
Table_name-specifies the table name containing the column(s). 
WHERE<Condition(s)>- the condition must meet to retrieve the data from the database.

Note- Condition is combination of column names,constants,variables,expressions and comparison operator.

Example- 

1.Write the query to find the id,name,address of employee whose address is Lucknow.

Query-    select ID,Name,Address from employee where address='Lucknow';
Output-
Number of Records: 2
ID   Name  address
4    Aman  Lucknow
5    Hari  Lucknow

2.Write the query to retrieve employees details whose salary is more than 10000 and less than 50000. 

Query-  SELECT * FROM employee where salary>10000 and salary<50000
Output-
Number of Records: 1
ID  Name      email             address  phone_number   salary
5   Hari   Hari@gmail.com        Lucknow   36756890      15000

3.Write the query to display the id,name and the yearly salary of the employees whose annual salaries are less than 200000.

Query-SELECT ID , Name , salary*12  Annual_salary FROM employee where Annual_salary<200000;
Output-
Number of Records: 2
ID Name     Annual_salary
1 sharat    60000
5 Hari      180000

4.Write the query to display the id,name and the yearly salary of the employees and also label the column heading id as Emp_Id,name as Employee Name and salary as 
Yearly Salary Package.

Query-  SELECT ID as Emp_Id, Name as "Employee Name", salary*12 as "Yearly Salary Package" FROM employee;
Output-
Number of Records: 5
Emp_Id Employee Name     Yearly Salary Package
1         sharat         60000
2         Ajay           612000
3         Amit           612000
4         Aman           612000
5         Hari           180000

SELECT Statement-To retrieve the data from the database

SELECT Statement

Select statement is used to retrieve the data from the database.We use the key word SELECT.

Using the Select statement, we can choose the column(s)of the same table or different tables using the join and 

also can select the number of the rows either same or different tables.

Syntax-

SELECT   *|[DISTINCT]Column|expression [Alias]
From Table_Name;

SELECT -a list of columns
* -Select the all columns 
DISTINCT-suppresses the duplicates 
alias-alternate name of the column(s) that is/are used as the heading of that column(s).  
Table_name-specifies the table names containing the column(s). 

Example- 

1.Write the query to select the all columns of the table employee.
Query-    select * from employee;
Output-
Number of Records: 5
ID Name email                 address       phone_number salary
1 sharat sarat@gmail.com          Hyderabad 123456890 5000
2 Ajay ajay@gmail.com          Hyderabad 16756890 51000
3 Amit amit@gmail.com          Delhi         16756890 51000
4 Aman Aman@gmail.com          Lucknow 26756890 51000
5 Hari Hari@gmail.com          Lucknow 36756890 15000

2.Write the query to find the distinct address in the employee table. 
Query-  SELECT distinct address FROM employee
Output-
Number of Records: 3
address
Hyderabad
Delhi
Lucknow

3.Write the query to display the id,name and the yearly salary of the employees and also 

label the column heading id as Employee_Id,name as Name and salary as Annual Salary.
Query-  SELECT ID as Employee_Id, Name as "Name", salary*12 as "Annual salary" FROM employee;
Output-
Number of Records: 5
Employee_Id Name Annual salary
1         sharat 60000
2         Ajay 612000
3         Amit 612000
4         Aman 612000
5         Hari 180000

4.Write the query to display the id,name and the yearly salary of the employees  whose annual salaries are greater 200000 and also 

label the column heading id as Employee_Id,name as Name and salary as Annual_Salary.
Query-SELECT ID as Employee_Id, Name as "Name", salary*12  Annual_salary FROM employee where Annual_salary>200000;
Output-
Number of Records: 3
Employee_Id Name Annual_salary
2        Ajay 612000
3        Amit 612000
4        Aman 612000

Developing the mySQL interview skills- For beginner to expert

1.How to set MySQL Root password?(Let user name is root).
Ans-Use mysqladmin command to set the password for root and 

set the password Myroot@1234 with below command- 
mysqladmin -u root password MYroot@1234

2. How to Change MySQL Root password?

(let you want to change from password MYroot@1234 to MadeINindia@2017).
Ans-Syntax: mysqladmin -u root -p<old password> password 'New Password'
Example-
mysqladmin -u root -pMYroot@1234 password 'MadeINindia@2017'

3. How to check MySQL Server is running?
Ans-mysqladmin -u root -p ping

4.How to Check which MySQL version I am running?
Ans-mysqladmin -u root -p version

5.How to create a Database in MySQL server?(assume the database name is Mydatabase).
Ans-Syntax: mysqladmin -u root -p create <databasename>
Example-
mysqladmin -u root -p create Mydatabase
Enter password:

6.How to create the table named as employee with ID,Name,email,address,phone number,salary.

Note-ID is the primary key of the employee table and assume the data type as well.
Ans-Create table employee( ID   varchar2(20),
Name  varchar2(20),
email varchar2(20),
address  varchar2(20),
phone_number number,
salary number,
primary key (ID));

7.View the detail for the table employee.
Ans-desc employee

8.Write a query to insert the 5 record into the table names as employee.
Ans-
insert into employee(ID,Name,email,address,phone_number,salary) values("1","sharat","sarat@gmail.com","Hyderbad",123456890,5000);
insert into employee(ID,Name,email,address,phone_number,salary) values("2","Ajay","ajay@gmail.com","Hyderbad",16756890,51000);
insert into employee(ID,Name,email,address,phone_number,salary) values("3","Amit","amit@gmail.com","Delhi",16756890,51000);
insert into employee(ID,Name,email,address,phone_number,salary) values("4","Aman","Aman@gmail.com","Lucknow",26756890,51000);
insert into employee(ID,Name,email,address,phone_number,salary) values("5","Hari","Hari@gmail.com","Lucknow",36756890,15000);

9. How to verify the data entered into table named employee?
Ans-SELECT * from employee.

Output-
Number of Records: 5
ID Name email         address       phone_number salary
1 sharat sarat@gmail.com Hyderbad 123456890 5000
2 Ajay ajay@gmail.com Hyderbad 16756890 51000
3 Amit amit@gmail.com Delhi         16756890 51000
4 Aman Aman@gmail.com Lucknow         26756890 51000
5 Hari Hari@gmail.com Lucknow         36756890 15000

10.What are the  Data defintion language (DDL) commands used in SQL.
Ans-CREATE, ALTER,DROP,RENAME,TRUNCATE

11.What are the Data Manipulation Language (DML) commands used in SQL.
Ans-DELETE, UPDATE,INSERT

12.What are the Data Control Language (DCL) commands used in SQL.
Ans-GRANT, REVOKE

13.What are the Transaction Control Language (TCL) commands used in SQL.
Ans-SAVE POINT, ROLLBACK, COMMIT

14.Which command is used to retrived the data from the database.
Ans-SELECT

15.What is SQL?
SQL( Structured Query Language) is an ANSI (American National Standards Institute) standard.
it is used to access, modification and update the data into the database.

16-How SQL is used in the database.
SQL can execute queries that retrieve the data,insert,delete and modify the data into database. 
SQL can  create the new databases,new tables, new view, new function, new procedure etc.

17-Can  we use SQL to build the Website? What knowledge we need to build the web site?
Ans Yes. You need the following knowledge to build the website
1-A RDBMS data base such as MS Access, MySQL,SQL Server etc.
2-A Server-side Scripting language like ASP,PHP
3-A SQL to retrieve the data that you want
4- HTML/CSS 

18.Does SQL support programming features?
No, SQL doesn't have programming feature such as loop or Conditional statement etc. 

19.What do mean by key words , clauses and statements in oracle?
Ans-Key words are the reserve word  such as SELECT, DELETE, INSERT etc

Clause-it is part of SQL Statement.
for example SELECT clause as SELECT emp_name ,salary . ...as a clause

Statements- it is the combination of two or more clauses.
for example- select emp_name, salary from EMP.

20.What are the different clauses for the SQL statement.
Ans-SELECT ...
FROM ...
WHERE...
GROUP BY ...
HAVING....
ORDER BY...

21.What are the mandatory clause in oracle database
Ans.SELECT and FROM are mandatory clause