SQL Interview Questions

SQL QUERIES :-

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

* How to create a copy of table from original table in Oracle? 

create table emp2 as select * from emp;

* How to find 2nd highest salary ?

select max(sal) from emp where sal < (select max(sal) from emp);

                                               or

select * from emp where sal = (select max(sal) from emp where sal < (select max(sal) from   emp));

* How to find 3rd highest salary ?

select max(sal) from emp where sal < (select max(sal) from emp where sal < (select  max  (sal) from emp));

                                                   or

select * from emp where sal = (select max(sal) from emp where sal < (select max(sal) from   emp where sal < (select max  (sal) from emp)));

* How to find 2nd lowest salary ?

select min(sal) from emp where sal > (select min(sal) from emp);

                                                  or

select * from emp where sal = (select min(sal) from emp where sal > (select min(sal) from emp));

* How to find 3rd lowest salary ?

(select min(sal) from emp where sal > (select min(sal) from emp where sal > (select min(sal) from emp));

                                                     or

select * from emp where sal = (select min(sal) from emp where sal > (select min(sal) from emp where sal > (select min(sal)   from emp)));

* How to find first half rows in table ?

select * from emp where rownum <= (select count(*)/2 from emp);

* How to find last half rows in table ?

select * from emp minus select * from emp where rownum <= (select count(*)/2 from emp);

* How to find first 3 highest salaries from emp table ?

select * from (select * from emp order by sal desc) where rownum<=3;

* How to find first 3 lowest salaries from emp table ?

select * from (select * from emp order by sal) where rownum<=3;

* Display the employees who joined in the year 1981.
SELECT * FROM EMP
WHERE
TO_CHAR(HIREDATE,'YYYY')='1981';


* Display the employees who joined before 1981.
SELECT * FROM EMP
WHERE
TO_CHAR(HIREDATE,'YYYY') < '1981';


* Display the employees who does not belong to department 10.
SELECT * FROM EMP
WHERE
DEPTNO NOT IN (10);


* Display the average salaries of all the employees who works as ANALYST.
SELECT AVG(SAL) FROM EMP
WHERE
JOB='ANALYST';


* Display all the details of the employees whose commission is more than their salary
SELECT * FROM EMP
WHERE
COMM > SAL;


* Display the employees whose salary is more than 3000 after giving 25% increment.
SELECT * FROM EMP
WHERE
SAL+SAL*25/100 > 3000;


* Display the name of the employees, those having six characters to their name.
SELECT * FROM EMP
WHERE
LENGTH(ENAME)=6;


* Display the employees who joined in the month January.
SELECT * FROM EMP
WHERE
TO_CHAR(HIREDATE,'MON')='MAY';


* Display the name of employees and their manager separated by the string 'works for'.
SELECT E.ENAME || ' WORKS FOR ' || M.ENAME
FROM EMP E, EMP M
WHERE
E.MGR = M.EMPNO;

* Display the employees whose experience is more than 27 years.
SELECT * FROM EMPLOYEES
WHERE
HIRE_DATE - SYSDATE < 27

* Employees who earn more than 100 as daily salary
SELECT * FROM EMP WHERE SAL/30 > 100;

* Write a query in SQL to list all the employees joined on 1st may 91
SELECT * FROM EMP WHERE HIREDATE = '1-MAY-91';

* Write a query in SQL to list the employees who are retiring after 31-Dec-99 after completion of 8 years of service period.
SELECT emp_name FROM employees
WHERE hire_date + interval '96 months' > '1999-12-31';

* Write a query in SQL to list those employees whose salary is an odd value.
SELECT * FROM EMP WHERE MOD(SAL,2)=1;

* Display all the records but common records only once from both the table EMP and EMP1
SELECT * FROM EMP
UNION
SELECT * FROM EMP1;


* Fetch only common records from two tables emp and emp1
SELECT * FROM EMP
INTERSECT
SELECT * FROM EMP1;


* Display all records of emp1 those should not present in emp2
SELECT * FROM EMP
MINUS
SELECT * FROM EMP1;

SQL SUB-QUERIES :-

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

* Query to Display only duplicates from a table ?

SELECT * FROM EMP

WHERE

EMPNO IN

(SELECT COUNT(EMPNO) FROM EMP

GROUP BY EMPNO

HAVING

COUNT(EMPNO)>1);

* How do you delete duplicate records ?

DELETE FROM EMP

WHERE

EMPNO IN

(SELECT COUNT(EMPNO) FROM EMP

GROUP BY EMPNO

HAVING

COUNT(EMPNO)>1);

* Query to delete duplicates only single instance should be present.

SELECT * FROM EMP 

WHERE EMPNO NOT IN 

(SELECT MIN(EMPNO) FROM EMP 

GROUP BY EMPNO)

* Write a query in SQL to display all the details of managers.
SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID IN
(SELECT MANAGER_ID FROM EMPLOYEES);

* Write a query in SQL to display the employee ID, name, job name, hire date, and experience of all the managers.

SELECT EMPNO, ENAME, JOB, HIREDATE FROM EMP 

WHERE EMPNO IN

(SELECT EMPNO FROM EMP WHERE JOB = 'MANAGER')

* Write a query in SQL to list all the employees of grade 2 and 3.
SELECT * FROM EMPLOYEES, SALARY_GRADE
WHERE GRADE IN (2,3);

* Write a query in SQL to list all the employees where salary between min & min salary, grade is 2 and 3.
SELECT * FROM EMPLOYEES, SALARY_GRADE
WHERE SALARY BETWEEN MIN_SAL AND MAX_SAL
AND GRADE IN (2,3);

(OR)

SELECT * FROM EMPLOYEES E, SALARY_GRADE S
WHERE E.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL
AND S.GRADE IN (2,3);

* Write a query in SQL to display all the employees of grade 4 and 5 who are working as ANALYST or MANAGER.
SELECT * FROM EMPLOYEES E, SALARY_GRADE S
WHERE GRADE IN (4,5)
AND JOB_NAME IN ('ANALYST','MANAGER');

* Write a query in SQL to list the details of the employees whose salary is more than the salary of JONES.

SELECT * FROM EMP WHERE SAL > 

(SELECT SAL FROM EMP WHERE ENAME = 'JONES')

* Write a query in SQL to list the employees who works in the same designation as CLARK.

SELECT * FROM EMP WHERE JOB =

(SELECT JOB FROM EMP WHERE ENAME = 'CLARK')

* List the employees who are senior to KING

SELECT * FROM EMP WHERE HIREDATE >

(SELECT HIREDATE FROM EMP WHERE ENAME = 'KING')

 

SQL INTERVIEW QUESTIONS :-

---------------------------------------------
* What are the different comparison conditions in oracle ? What is the importance of them ?
- ALL, ANY and SOME are different Comparison Conditions in SQL.
- ALL comparison condition is used to compare to a subquery, It should be preceded by =,>,<,>=,<=, operators in sql.
- ALL operator returns true, if all of the subquery values meet the condition.
 

* How do you tune or optimize sql queries ?
- select field name instead of using select *
- avoid using distinct command

- Use Order by at the end of statement or comment it (--)

- User Indexes for make data retrieval faster

- User View or Materialized views     
- create joins using inner joins not by where clause

* What is the difference between Order by and Group by ?
Group by is used with Aggregate functions and arrange the data into columns.
Order by is used to sort data in either ascending and decending order.


* What is the difference between Having clause and Where clause ?
- Where clause cannot be used with aggregates, but the having clause can be used.
- Apart from SELECT queries, you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query.
- WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL.
- One syntax level difference between WHERE and HAVING clause is that, former is used before GROUP BY clause, while later is used after GROUP BY clause


* What is the difference between Aggregate and Analytical Functions ?
- The functions SUM, COUNT, AVG, MIN, MAX are the common aggregate functions, the result of which does not depend on the order of the records, where as Analytical functions like LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE, LAST, LAST VALUE depends on order of records.
- Aggregate functions Return a single output by calculating values from the table, where as Analytic functions Return the same number of rows as the input. 


* What is a Partitioning clause ?
- It is used to breakup or divide table data into many partitions or parts.
- The PARTITION BY clause is a sub clause of the OVER clause.




* What is difference between index and bitmap index ?

* How do you join two tables with SQL inner join ?
SELECT EMPNO, ENAME FROM EMP
INNER JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO;

or

SELECT * FROM EMP E, DEPT D

WHERE E.DEPTNO=D.DEPTNO 

AND DNAME ='ACCOUNTING'

* How do you join two tables with mulitple conditions with SQL inner join ?
SELECT EMPNO, ENAME FROM EMP
INNER JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO
AND EMP.DNAME=DEPT.DNAME; 

* How do you join more than two tables with SQL inner join ?
Select * from Emp 
Inner Join Dept
on Emp.deptno = Dept.deptno
Inner join Emp1
On Dept.deptno = Emp1.deptno;


* How to get Nth Max Salary?

SELECT * FROM

(SELECT *, DENSE_RANK() OVER (ORDER BY SAL DESC) AS HSAL

FROM EMP ) AS E

WHERE HSAL=5;

OR 

SELECT * FROM

(SELECT E.*, DENSE_RANK() OVER (ORDER BY SAL DESC) AS HSAL

FROM EMP E) AS E

WHERE HSAL=5;

* Query to display unique Job from Emp table.
SELECT DISTINCT JOB FROM EMP;

* Query to display unique Job from Emp table without using Distinct keyword
SELECT JOB FROM EMP GROUP BY JOB;

* Difference between delete and truncate command ?
We can rollback the data, when we use delete command but in the case of truncate its not possible.
Delete removes only the rows from table but it cannot delete space containing the table where as truncate deletes all the rows and free the space containing the table
We can use where clause in delete but in the case of truncate its not possible.

* Subquery or join is better based on performance ?
JOINs are faster than sub-queries and it is very rare for a sub-query to be faster.

* What is the functional difference between oracle and sql ?
- Language used by Oracle is PL/SQL (Procedural Language/ SQL), where as Language used by SQL Server is T-SQL (Transact-SQL).
- In Oracle, no transaction is committed until DBA explicitly issues COMMIT command, where as If BEGIN TRANSACTION and COMMIT commands are not specified, it executes and commit each command individually.
- The syntax of the commands used by Oracle and SQL also differs.
- In Oracle, the procedures, functions and variables are encapsulated into Packages. However, SQL does not have Packages.

* Difference between stop and abort function ?
When you issue a stop command on a session, the integration service first stops reading the data from the sources. It continues processing and writing data to the targets and then commits the data.
Abort command is handled the same way as the stop command, except that the abort command has timeout period of 60 seconds. If the Integration Service cannot finish processing and committing data within the timeout period, it kills the DTM process and terminates the session.


* All types of Joins and their outputs
 
Example 1:-

    EMP_NO EMP_NAME             JOB_NAME       MGR_ID    DEPT_NO
    ----------     -------------------- ---------- ---------- ----------
      1234 Alex                 Clerk            4567         15
      2345 Jack                 Consultant       3456         25
      3456 Paul                 Manager          1234         15
      4567 Jenefer              Engineer         2345         45

   DEPT_NO DEP_NAME   LOCATION
    ---------- ---------- ----------
        15 FINANCE    PARIS
        25 MARKETING  LONDON
        35 HR         DELHI

Innerjoin: 2
Left join: 4
Rightjoin: 3
Full join: 5

Example 2:-

TableX    TableY
------    ------
A    C
B    D
C    E
D    F

Innerjoin output:

X    Y
--    --
C    C
D    D

Leftjoin output:

X    Y
--    --
A    NULL
B    NULL
C    C
D    D

Rightjoin output:

X    Y
--    --
C    C
D    D
NULL    E
NULL    F 

* HOW DO YOU SWAP GENDER IN ORACLE ?
UPDATE GENDER
SET GENDER = CASE GENDER WHEN 'MALE' THEN 'FEMALE'
ELSE 'MALE'
END;

* What the different Operator available in SQL?

Arithmetic Operators 

Comparison Operators

Logical Operators

" + - * / % " are the list of Arithmetic Operators

" = > < >= <= !< !> " are the list of Comparison Operators

" ALL AND ANY SOME LIKE IN BETWEEN NOT EXISTS OR NULL " are the list of Logical Operators.

* How to combine two commands in SQL?

UNION, EXCEPT and INTERSECT Clause are used to combine the results of two SELECT statements.

Triggers in PLSQL

Triggers in PL/SQL:-
-----------------
Trigger is one of the Database Objects which is saved in database. Triggers are automatically or implicitly executed for any DML operation on the table. 
Trigger has two functions ':Old' is used to get old values from the column and ':New' is used to get new values from the column

Trigger of are used for two purposes :-
------------------------------------
To maintain the data uniformity in a business :-
Data is inserted into the database server in any case as upper, lower and mixed case. But the business people will maintain the business data in only one case. Solution is before the data enters into the table as a record this data is taken by a trigger program by our defined trigger logic. 

To maintain audit information of table data :-
A table contains millions of records its difficult to keep the track on all inserted, updated and deleted rows. Inorder to maintain the track of all the changes in data an audit table is maintained as a solution. Audit tables will be automatically updated at the time of inserts or deletes. Audit tables are maintained seperately for inserts and updates happen in a table.  

Syntax :

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END; 


Triggers are of 3 types :-

- DDL Triggers : Whenever we perform any ddl operations such as create, drop   table then we use ddl triggers.
- DML Triggers : Whenever we perform any dml operations such as insert,     update and delete then we use dml triggers.
- Instead of Triggers : If we want to perform any dml operations on view then   we use instead of triggers.

Triggers are further classified as  :-
- Row Level Trigger : If 100 records are updated in a table then the trigger will be executed 100 number of times then its a Row Level Trigger.
- Statement Level Trigger : If you delete records and if the trigger executed only one time then its a Table Level Trigger.


DML TRIGGERS :-

ROW LEVEL TRIGGER :-
-------------------------
CREATE A TRIGGER TO UPDATE AUTOMATICALLY THE DELETED ROWS INTO TARGET 

CREATE OR REPLACE TRIGGER UPDATE_DELETE
BEFORE DELETE ON EMP1
FOR EACH ROW
BEGIN
INSERT INTO TRIGGERED VALUES (:OLD.EMPNO, :OLD.ENAME,:OLD.JOB,:OLD.MGR,:OLD.HIREDATE,:OLD.SAL,:OLD.COMM,:OLD.DEPTNO,:OLD.DATEOFENTRY);
END;
/


CREATE A TRIGGER TO UPDATE AUTOMATICALLY THE INSERTED ROWS INTO TARGET 

CREATE OR REPLACE TRIGGER UPDATE_INSERT
AFTER INSERT ON EMP1
FOR EACH ROW
BEGIN
INSERT INTO TRIGGERED VALUES (:NEW.EMPNO, :NEW.ENAME,:NEW.JOB,:NEW.MGR,:NEW.HIREDATE,:NEW.SAL,:NEW.COMM,:NEW.DEPTNO,:NEW.DATEOFENTRY);
END;
/


CREATE A TRIGGER TO UPDATE AUTOMATICALLY THE DELETED ROWS INTO TARGET 

CREATE OR REPLACE TRIGGER UPDATE_DELETE
BEFORE DELETE ON CUSTOMERS
FOR EACH ROW
BEGIN
INSERT INTO OLDCUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (:OLD.ID, :OLD.NAME, :OLD.AGE, :OLD.ADDRESS, :OLD.SALARY);
END;
/


CREATE OR REPLACE TRIGGER SALARY_UPDATE_CHECK
BEFORE UPDATE OF SAL ON EMP
FOR EACH ROW
BEGIN
IF
:NEW.SAL < :OLD.SAL
THEN
RAISE_APPLICATION_ERROR (-20215,'UPDATED SALARY CANNOT BE LESSER THAN CURRENT SALARY');
END IF;
END;

/



STATEMENT LEVEL TRIGGER :-
---------------------------

CREATE TRIGGER STMT_LEVEL_TRIGGER
AFTER UPDATE ON HIGHSCHOOLER
BEGIN
UPDATE HIGHSCHOOLER
SET GRADE = NULL
WHERE GRADE = 13;
END;


CREATE OR REPLACE TRIGGER T1
AFTER INSERT ON STUDENT
BEGIN
UPDATE STUDENT
SET TOT       =   M1+M2+M3,
       PCT      = ( M1+M2+M3)/3,
       RESULT = CASE WHEN M1 >=35 AND M2 >=35 AND M3 >=35 THEN 'PASS' ELSE 'FAIL' END;
END;


CREATE OR REPLACE TRIGGER RESTRICTED_INSERT
 BEFORE INSERT ON EMP
 BEGIN
 IF
 (TO_CHAR(SYSDATE, 'HH24:MI') NOT BETWEEN '10:00' AND '18:00')
 THEN
 RAISE_APPLICATION_ERROR (-20123, 'YOU CAN ADD EMPLOYEE ONLY BETWEEN 10:00 AM AND 6:00 PM.');
 END IF;
 END;

 /


DDL TRIGGERS :-

STATEMENT LEVEL TRIGGER :-
---------------------------------

CREATE OR REPLACE TRIGGER RESTRICT_DROP_TABLE
BEFORE DROP ON DATABASE
BEGIN
RAISE_APPLICATION_ERROR (-20125, 'CANNOT DROP TABLE FROM THIS DATABASE');
END;
/


CREATE OR REPLACE TRIGGER LOGIN_TRIGGER
AFTER LOGON ON DATABASE
 BEGIN
 INSERT INTO LOGIN DETAILS VALUES (USER, SYSDATE, 'LOGIN');
 END;
 /


CREATE OR REPLACE TRIGGER LOGOFF_TRIGGER
BEFORE LOGOFF ON DATABASE
BEGIN
INSERT INTO LOGOFF_DETAILS VALUES (USER, SYSDATE, 'LOGOUT');
END;

/

Procedures in PL/SQL

If a program or query that is stored as a procedure permanently in the database is called "Stored Procedure". It is saved in database as Pre-compiled object, once its compiled it will not be compiled again and again like a program until the logic changes. Procedures are executed explicitly, to execute it we have to use execute command. 

A Procedure is a subprogram unit that consists of a group of PL/SQL statements. Each procedure in Oracle has its own unique name by which it can be referred. 
It contains declaration part (optional), execution part, exception handling part (optional).
The values can be passed into the procedure or fetched from the procedure through parameters.
Keyword 'IS' will be used, when the procedure is nested into some other blocks. If the procedure is standalone then 'AS' will be used. Other than this coding standard, both have the same meaning.


SYNTAX :-
----------
CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 

END procedure_name; 


THERE ARE 3 TYPES OF PARAMETERS :-
-------------------------------------------
- IN parameter lets you pass a value to the subprogram
- OUT represents the parameter that will be used to return a value outside of       the procedure
- IN OUT parameter passes an initial value to a subprogram and returns an     updated value to the caller.


PROCEDURES ARE OF TWO TYPES :-
---------------------------------------
- Dynamic Procedure
- Static Procedure


STATIC PROCEDURE :-
-----------------
CREATE A PROCEDURE TO DISPLAY NUMBER OF EMPLOYEES FROM DEPTNO 10.

CREATE OR REPLACE PROCEDURE PRO_DEPT_10
IS
O_EMPCOUNT INT;
BEGIN
SELECT COUNT(*) INTO O_EMPCOUNT FROM EMP WHERE DEPTNO=10;
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEES FROM DEPARTMENT NO 10 IS' || O_EMPCOUNT);
END PRO_DEPT_10;
/



CREATE A PROCEDURE TO DISPLAY NUMBER OF EMPLOYEES FROM DEPTNO 20

CREATE OR REPLACE PROCEDURE PROC_DEPT_20
IS
O_VARIABLE INT;
BEGIN
SELECT COUNT(*) INTO O_VARIABLE FROM EMP WHERE DEPTNO=20;
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEES FROM DEPTNO 20 IS'|| O_VARIABLE);
END PROC_DEPT_20;
/


CREATE A PROCEDURE TO DISPLAY NUMBER OF EMPLOYEES FROM DEPTNO 30

CREATE OR REPLACE PROCEDURE PROC_DEPTNO_30
IS
O_VARIABLE INT;
BEGIN
SELECT COUNT(*) INTO O_VARIABLE FROM EMP WHERE DEPTNO = 30;
DBMS_OUTPUT.PUT_LINE ('NUMBER OF EMPLOYEES FROM DEPTNO 30 IS ' || O_VARIABLE);
END PROC_DEPTNO_30;
/


WRITE A PROCEDURE TO DISPLAY 'MY NAME IS PRASAN KUMAR '

CREATE PROCEDURE PROC_NAME
IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('MY  NAME IS PRASAN KUMAR');
END PROC_NAME;
/

SET SERVEROUTPUT ON 

EXECUTE PROC_NAME



DYNAMIC PROCEDURE :-
---------------------
CREATE A PROCEDURE TO DISPLAY NUMBER OF EMPLOYEES FROM VARIABLE DEPTNO'S.

CREATE OR REPLACE PROCEDURE PROC_EMP_COUNT (V_DEPTNO INT)
IS
EMP_COUNT INT;
BEGIN
SELECT COUNT(*) INTO EMP_COUNT FROM EMP WHERE DEPTNO = V_DEPTNO;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EMPLOYEES FROM V_DEPTNO IS ' || EMP_COUNT );
END PROC_EMP_COUNT;
/

SET SERVEROUTPUT ON
EXECUTE PROC_EMP_COUNT(10)

 
CREATE OR REPLACE PROCEDURE PROC_VARIABLE_DEPT (IN_VARIABLE INT)
IS
O_VARIABLE INT;
BEGIN
SELECT COUNT(*) INTO O_VARIABLE FROM EMP WHERE IN_VARIABLE=DEPTNO;
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEES FROM VARIABLE DEPT IS' || O_VARIABLE);
END PROC_VARIABLE_DEPT;
/
SET SERVEROUTPUT ON
EXECUTE PROC_VARIABLE_DEPT(20)
 
 
CREATE OR REPLACE PROCEDURE PROC_VAR_JOB (IN_VARIABLE VARCHAR)
IS
OUT_VARIABLE INT;
BEGIN
SELECT COUNT(*) INTO OUT_VARIABLE FROM EMP WHERE JOB=IN_VARIABLE;
DBMS_OUTPUT.PUT_LINE('COUNT FROM VARIABLE JOBS IS' || OUT_VARIABLE );
END PROC_VAR_JOB;

/
SET SERVEROUTPUT ON
EXECUTE PROC_VAR_JOB (CLERK)

CREATE A PROCEDURE TO POPULATE TOTAL SALARY OF AN EMPLOYEE.

CREATE OR REPLACE PROCEDURE PROC_SAL_COMM (EMP_ID IN NUMBER, TOTAL OUT NUMBER)
IS 
BEGIN
SELECT SAL+(SAL*10/100) INTO TOTAL FROM EMP
WHERE EMP_ID = EMPNO;
END PROC_SAL_COMM;
/
SET SERVEROUTPUT ON
EXECUTE PROC_SALHIKE(7934)


CREATE A PROCEDURE TO GET MIN, MAX, AVG, SUM OF SALARIES OF EMPLOYEES.

CREATE OR REPLACE PROCEDURE PROC_SAL_CAL (
IN_DEPTNO IN NUMBER,
MAX_SAL OUT NUMBER,
MIN_SAL OUT NUMBER,
SUM_SAL OUT NUMBER,
AVG_SAL OUT NUMBER )
IS 
BEGIN
SELECT MAX(SAL), MIN(SAL), SUM(SAL), AVG(SAL) 
INTO MAX_SAL, MIN_SAL, SUM_SAL, AVG_SAL
FROM EMP
WHERE DEPTNO = IN_DEPTNO GROUP BY DEPTNO;
END PROC_SAL_CAL;
/

CREATE A PROCEDURE TO INSERT ROWS INTO THE DEPT TABLE.

CREATE OR REPLACE PROCEDURE ADD_DEPT (
P_DNO IN DEPT.DEPTNO%TYPE,
P_DNM IN DEPT.DNAME%TYPE,
P_LOC IN DEPT.LOC%TYPE
)
IS 
BEGIN
INSERT INTO DEPT VALUES (P_DNO, P_DNM, P_LOC);
DBMS_OUTPUT.PUT_LINE('DEPARTMENT ADDED');
END ADD_DEPT;
/

SET SERVEROUTPUT ON
EXECUTE ADD_DEPT (50,'BISCUIT','OUR LOCATION');


WRITE A PROCEDURE TO FIND MINIMUM VALUE.

CREATE OR REPLACE PROCEDURE FINDMIN 
( X IN NUMBER, Y IN NUMBER, Z OUT NUMBER )
IS
BEGIN
IF X < Y THEN
Z:= X;
ELSE
Z:=Y;
END IF;
END;
/
DECLARE
A NUMBER;
B NUMBER;
C NUMBER;
BEGIN
A:=23;
B:=45;
FINDMIN (A,B,C);
DBMS_OUTPUT.PUT_LINE ('MINIMUM OF (23,45):' || C);
END;

/



WRITE A PROCEDURE TO DISPLAY DEPARTMENT NAME FROM DEPT TABLE.

CREATE OR REPLACE PROCEDURE ADD_DEPT
(P_DNO IN DEPT.DEPTNO%TYPE,
 P_DN OUT DEPT.DNAME%TYPE)
IS
BEGIN
SELECT DNAME INTO P_DN
FROM DEPT
WHERE DEPTNO = P_DNO;
END;
/
DECLARE
P_DN VARCHAR2(20);
BEGIN
ADD_DEPT(30,P_DN);
DBMS_OUTPUT.PUT_LINE(P_DN);
END;

/

* Write a procedure to get min, max, avg, sum of salaries of employees.

Create procedure Proc_Get_Salaries

(

in_deptno in number,

min_sal out number, 

max_sal out number,

avg_sal out number 

sum_sal out number)

AS

begin

Select  min(sal), max(sal), avg(sal), sum(sal) into 

min_sal, max_sal, avg_sal, sum_sal from emp where deptno=in_deptno group by deptno;

End;

Introduction to PLSQL

PLSQL is the combination of SQL statements. To increase the capabilities of SQL PLSQL is introduced. Full form of PLSQL is 'Procedural Structured query language'. 

* PL/SQL programs are divided and written in logical blocks of code. Each block consists of three sub-parts

- Declarations

- Executable Commands

- Exception Handling

* Following is the basic structure of a PL/SQL block

DECLARE 

<declarations section> 

BEGIN 

<executable command(s)>

EXCEPTION 

<exception handling> 

END;

* The PL/SQL single-line comments start with the delimiter -- (double hyphen) and multi-line comments are enclosed by /* and */.

 Example : 

    DECLARE 

   -- variable declaration 

   message  varchar2(20):= 'Hello, World!'; 

   BEGIN 

   /* 

   *  PL/SQL executable statement(s) 

   */ 

   dbms_output.put_line(message); 

   END; 

   /  

* A PL/SQL unit is any one of the following −

 PL/SQL block

 Function

 Package

 Package body

 Procedure

 Trigger

 Type

 Type body

SQL Functions

TO_DATE Function :
---------------------

The Oracle TO_DATE function converts a string to a date.

select to_date('20020315','yyyymmdd') from dual;
select to_date('07112003','mmddyyyy') from dual;


LPAD, RPAD, LTRIM, RTRIM, INSTR, SUBSTR, LOWER, UPPER, INITCAP Functions

SQL> SELECT * FROM EMP;

EMPNO ENAME                  SAL  COMM DEPTNO
----- -------------------- ----- ----- ------
 7369 SMITH                  900   100     20
 7499 ALLEN                 1600   300     30
 7521 WARD                  1250   500     30
 7566 JONES                 2975   100     20
 7654 MARTIN                2975   100     20
 7412 STONECOLD             1244   200     20
 7412 STONECOLD             1244   200     20

SQL> select substr(ename,2) from emp;

(OR)

SELECT E.*, SUBSTR(ENAME,2) SUBSTR FROM EMP E;



SUBSTR(ENAME,2)
-------------------
MITH
LLEN
ARD
ONES
ARTIN
TONECOLD
TONECOLD


SQL> select instr(ENAME,'M') from emp;

INSTR(ENAME,'M')
----------------
               2
               0
               0
               0
               1
               0
               0



SQL> SELECT UPPER(ENAME) from emp;

UPPER(ENAME)
--------------------
SMITH
ALLEN
WARD
JONES
MARTIN
STONECOLD
STONECOLD


SQL> SELECT LOWER(ENAME) FROM EMP;

LOWER(ENAME)
--------------------
smith
allen
ward
jones
martin
stonecold
stonecold


SQL> SELECT INITCAP(ENAME) FROM EMP3;

INITCAP(ENAME)
--------------------
Balu



SQL> SELECT LTRIM(ENAME,'S') FROM EMP; OR SUBSTR(Field1, INSTR(Field1,':')+1)

LTRIM(ENAME,'S')
--------------------
MITH
ALLEN
WARD
JONES
MARTIN
TONECOLD
TONECOLD



SQL> SELECT LTRIM(ENAME,'CSS-, *^') LTRIM FROM EMP1;

LTRIM
-------
KING
BLAKE
LARK
JONES
OTT
FORD
MITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
KING%$

KING-AXYZ


SQL> SELECT RTRIM(ENAME,'D') FROM EMP;

RTRIM(ENAME,'D')
--------------------
SMITH
ALLEN
WAR
JONES
MARTIN
STONECOL
STONECOL


SQL> SELECT LTRIM(ENAME,'S,M'), RTRIM(ENAME,'H,N') FROM EMP;

LTRIM(ENAME,'S,M')   RTRIM(ENAME,'H,N')
-------------------- --------------------
ITH                  SMIT
ALLEN                ALLE
WARD                 WARD
JONES                JONES
ARTIN                MARTI
TONECOLD             STONECOLD
TONECOLD             STONECOLD


SQL> SELECT LPAD(ENAME,10,'0') FROM EMP;

LPAD(ENAME
----------
00000SMITH
00000ALLEN
000000WARD
00000JONES
0000MARTIN
0STONECOLD
0STONECOLD


SQL> SELECT RPAD(ENAME,10,'0') FROM EMP;

RPAD(ENAME
----------
SMITH00000
ALLEN00000
WARD000000
JONES00000
MARTIN0000
STONECOLD0
STONECOLD0

SQL Joins

If you want to fetch multiple data types of data from multiple tables we can write joins. Joins combines two or more tables. 
There are different types of joins like left, right, inner, outer, full join.

Self-join :-
A Table which is joined by itself is know as self-join. 
Alias names like E1,E2 are given as prefix to column with condition to impliment selfjoin.

(select e1.eno, e2.ename, e1.dno from emp e1, emp e2 where e1.eno = e1.eno) 
                                           or      
(select d1.dno, d2.dname from dept d1, dept d2 where d1.dno=d2.dno)

Result: 

Equi-join :-
Where equal to operator is used. or we select both the table and give condition between both tables.

(select * from emp, dept where dept.dno = emp.dno;)
                                  or
(SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO;) 

Result: 

Non equi join :-
Where equal to operator is not used in the query it is called as Non equi join.

( select * from emp, dept where dept.dno > emp.dno ).

Result: 

Natural join :- 
It return only matching columns

(select * from emp natural join dept)

Result:

Cross Join :-
It multiplies first table by the number of rows in the second table.
(select * from emp cross join dept)

Result:

Inner Join :-
It returns matching rows from both the tables.

(SELECT * FROM EMP 

INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO)
                            or

SELECT * FROM EMP E, DEPT D

WHERE E.DEPTNO=D.DEPTNO AND DNAME ='ACCOUNTING'

Result:

Left outer join :- 
It is  used to get 'all rows from left table and only matching rows from right table'. A condition should be given to execute an left outer join.

(select * from emp  left outer join dept on emp.dno=dept.dno)
                                           or
(SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E
LEFT OUTER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO)

Result:

Right Outer Join :-
It is  used to get 'all rows from Right table and only matching rows from Left table'. A condition should be given to execute an right outer join

(select * from emp right outer join dept on emp.dno=dept.dno)
                                            or
(SELECT * FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO)
                                            or
(SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E
RIGHT JOIN DEPT D
ON E.DEPTNO=D.DEPTNO)

Result:

The FULL OUTER JOIN :-
This keyword return all records when there is a match in either left (table1) or right (table2) table records. A condition should be given to execute an right outer join

(select * from emp right outer join dept on emp.dno=dept.dno)
                                                or
(select * from emp2013 full outer join emp2014 on emp2013.id = emp2014.id)
                                                or
(SELECT * FROM EMP E FULL JOIN DEPT D ON E.DEPTNO = D.DEPTNO)

Result: