All About Oracle (SQL, PL/SQL , Forms and Reports) & UNIX: FAQ

Q1a: How to get Duplicate Records in SQL.

CREATE TABLE CUSTOMER

(FIRSTNAME VARCHAR2(50),

LASTNAME VARCHAR2(50),

MOBILENO VARCHAR2(15)) ;

Insert into CUSTOMER (FIRSTNAME,LASTNAME,MOBILENO) values ('Niraj','Yadav','989898');

Insert into CUSTOMER (FIRSTNAME,LASTNAME,MOBILENO) values ('Chetan','Gadodia','959595');

Insert into CUSTOMER (FIRSTNAME,LASTNAME,MOBILENO) values ('Atul','Kokam','42424242');

Insert into CUSTOMER (FIRSTNAME,LASTNAME,MOBILENO) values ('Vishal','Parte','9394453');

Insert into CUSTOMER (FIRSTNAME,LASTNAME,MOBILENO) values ('Jinendra','Jain','121212');

COMMIT;

SQL> SELECT * FROM CUSTOMER;

FIRSTNAME LASTNAME MOBILENO

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

Niraj Yadav 989898

Chetan Gadodia 959595

Atul Kokam 42424242

Vishal Parte 9394453

Jinendra Jain 121212

10 rows selected.

To get the duplicates :

SQL> SELECT

2 COUNT(*),

3 firstname,

4 lastname,

5 mobileno

6 FROM customer

7 GROUP BY firstname, lastname, mobileno

8 HAVING COUNT(*) > 1;

COUNT(*) FIRSTNAME LASTNAME MOBILENO

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

---

3 Vishal Parte 9394453

2 Jinendra Jain 121212

2 Chetan Gadodia 959595

2 Atul Kokam 42424242

To delete the duplicates :

SQL> DELETE FROM CUSTOMER A

2 WHERE ROWID > (

3 SELECT min(rowid)

4 FROM CUSTOMER B

5 WHERE A.firstname = B.FIRSTNAME

6 AND A.lastname = B.lastname

7 AND A.mobileno = B.MOBILENO);

5 rows deleted.

SQL> select * from customer;

FIRSTNAME LASTNAME MOBILENO

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

Niraj Yadav 989898

Chetan Gadodia 959595

Atul Kokam 42424242

Vishal Parte 9394453

Jinendra Jain 121212

Q-1 : How to delete Duplicate rows in SQL.

DELETE FROM EMP A
WHERE ROWID > (
SELECT min(rowid)
FROM EMP B
WHERE A.empno = B.empno
AND A.ename = B.ename
AND A.deptno = B.deptno);

Let us consider following example:

CREATE TABLE EMP
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10 BYTE),
DEPTNO NUMBER(2)
);

Insert into EMP
(EMPNO, ENAME, DEPTNO)
Values
(7369, 'SMITH', 20);
Insert into EMP
(EMPNO, ENAME, DEPTNO)
Values
(7499, 'ALLEN', 30);
Insert into EMP
(EMPNO, ENAME, DEPTNO)
Values
(7499, 'ALLEN', 30);

Insert into EMP
(EMPNO, ENAME, DEPTNO)
Values
(7521, 'WARD', 30);
Insert into EMP
(EMPNO, ENAME, DEPTNO)
Values
(7566, 'JONESSS', 20);
Insert into EMP
(EMPNO, ENAME, DEPTNO)
Values
(7566, 'JONESSS', 20);
Insert into EMP
(EMPNO, ENAME, DEPTNO)
Values
(7566, 'JONESSS', 20);

COMMIT;

SELECT * FROM EMP;

EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7499 ALLEN 30
7521 WARD 30
7566 JONESSS 20
7566 JONESSS 20
7566 JONESSS 20

7 rows selected.

DELETE FROM EMP A
WHERE ROWID > (
SELECT min(rowid)
FROM EMP B
WHERE A.empno = B.empno
AND A.ename = B.ename
AND A.deptno = B.deptno);
--commit;
3 rows deleted.

SELECT * FROM EMP;

EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONESSS 20

4 rows selected.

**********************************************************************

Using Windowing function:

With Q1 as

(select EMPNO ,ENAME, DEPTNO,row_number() over( partition by EMPNO ,ENAME, DEPTNO order by EMPNO ,ENAME, DEPTNO ) rownumber

from EmpDup )

delete from Q1 where rownumber!=1

Q-2 : Display employee last names, salaries,department number and average salaries for all the employees who earn more than the average salary in their department.

SELECT a.last_name ,a.salary,a.department_id,b.salavg
FROM employees a , ( SELECT department_id, AVG(salary) salavg
FROM employees
GROUP BY department_id) b

WHERE a.department_id =b.department_id
AND a.salary > b.salavg;

38 rows selected.

******************************************************************

/* Formatted on 28-07-2023 12:18:53 (QP5 v5.336) */

WITH

( SELECT department_id, AVG (salary) avg_sal

FROM employees

group by department_id )

SELECT e.employee_id,

e.first_name,

e.department_id,

e.salary,

Q1.avg_sal

FROM employees e, Q1

WHERE e.department_id = Q1.department_id

and e.salary > Q1.avg_sal

Display employee names, salaries,department number and maximum salaries for all the employees who earn maximum salary in their department along with department name.

SELECT a.ename ,a.sal,a.deptno, b.maxsal, c.dname
FROM emp a , ( SELECT deptno, max(sal) maxsal
FROM emp
GROUP BY deptno) b , dept c
WHERE a.deptno =b.deptno
AND a.sal= b.maxsal
AND c.deptno=b.deptno
/

ENAME SAL DEPTNO MAXSAL DNAME

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

BLAKE 2850 30 2850 SALES

SCOTT 3000 20 3000 RESEARCH

KING 5000 10 5000 ACCOUNTING

FORD 3000 20 3000 RESEARCH

Q-3: Using Correlated Subquery find all employees who earn more than the average salary in their department.

SELECT last_name, salary,department_id -- Parent Query
FROM employees outer
WHERE salary > ( SELECT AVG(salary)
FROM employees
WHERE department_id=outer.department_id);

Q-4: Using Correlated subquery display details of those employees who has switched jobs at least twice.

SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= ( SELECT COUNT(*)
FROM job_history
WHERE employee_id=e.employee_id);

Q-5: How to find out 2nd highest salary.

SELECT DISTINCT (a.salary)
FROM employees a
WHERE 2 = (SELECT COUNT (DISTINCT (b.salary))
FROM employees b
WHERE a.salary <= b.salary);

Q-6: Display top three earner name and salaries from EMPLOYEES table .

SELECT ROWNUM AS RANK, last_name, salary
FROM
( SELECT last_name, salary
FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 3;

Q7 : Dispay the employees list who works for which Manager. --Self Join

SELECT worker.last_name || 'works for '|| manager.last_name
from employees worker , employees manager
where worker.manager_id= manager.employee_id;

Q 8: Find employees who have at least one person reporting to them.

SELECT employee_id,last_name, job_id, department_id
FROM employees outer
WHERE EXISTS
(SELECT 'X'
FROM employees
WHERE manager_id= outer.employee_id);

OR

SELECT employee_id,last_name, job_id, department_id
FROM employees
WHERE employee_id IN
(SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL);

Q9: Find all departments that do not have any employees.

SELECT department_id , department_name
FROM departments outer
WHERE NOT EXISTS
(SELECT 'X'
FROM employees
WHERE department_id = outer.department_id);

OR

SELECT department_id , department_name
FROM departments
WHERE department_id NOT IN

(SELECT department_id
FROM employees);
-- No rows selected.

However, NOT IN evaluates to FALSE if any member of the set is a NULL value . Therefore your query will not return any rows even if there are rows in the departments table that satisfy the WHERE condition.

********************************XXXXX*****************************************
Additional :

Q1. What is the diff between Exist and In Operator?

If the conditions are correct the results would be the same, but the queries would be executed in a different way by the database engine. When the appropriate index exists, the EXISTS approach usually performs better.
You could use the autotrace option in SQL*Plus or SQL Command Line to see the execution plan for your queries.In my point of view, EXISTS is better because it requires to specify a join condition, which can invoke an INDEX scan.

However, IN is often better if the results of the sub query are very small. Usually to run the query that returns the smaller set of results first.

Q2. What is the Diff between Delete and Truncate and Drop?

delete –dml explicit commit required,
truncate- ddl implicit commit,but table structure remains.
drop –ddl drop db objects permanently from db.

Q3 .I have executed the Delete command after that I have created table wether deletions will be commit or not, if table is success fully created ?

commit

Q4: What is the difference between CHAR and VARCHAR2 data types?

Both CHAR and VARCHAR2 types are used to store character string values, however, they behave very differently.

CHAR

CHAR should be used for storing fix length character strings.
String values will be space/blank padded before stored on disk.
If this type is used to store varibale length strings, it will waste a lot of disk space.
By default CHAR size is 1.

VARCHAR2

VARCHAR2 is used to store variable length character strings.
The string value's length will be stored on disk with the value itself.
But we have to declare the size for VARCHAR2 else it it will throw error.

Q5: What is RowID ? When it will be created? What is the format? What is the diff between RowID and Rownum?

It is pseudocol associated to each row of table,18 charcters long hexadecimal.

rowid is associated when each row is created while rownum is attached to each row when it is retrieved from db in a query.

Q6: Can we use RowID in the where clause ? if Yes can we use like follows Where rowid = 18?

Yes we can use RowID in the where clause but we can't use numeric val

we have to provide hexadecimal value like whre rowid= WHERE rowid='AAAR5kAAFAAAADPAAA';

The Oracle format is a string encoding known as Base64.

Selecting a ROWID from Oracle will result in a Base64 display of the value.

What is in the encoding? Four things: *
1) The data object number of the object
2) The datafile in which the row resides (first file is 1). The file number is relative to the
3) The data block in the datafile in which the row resides
4) The position of the row in the data block (first row is 0)
tablespace. *

The format is: OOOOOO.FFF.BBBBBB.RRR
OOOOOO is the object ID
FFF is the file number
BBBBBB is the block number
RRR is the row number

Rowid is the physical address of each row it represents in DB , but rownum is the logical address.

Q7: What is decode function in sql ?

The DECODE function is analogous to the "IF THEN ELSE" conditional statement.
DECODE works with values, columns, and expressions of all data types.

Example Syntax:
DECODE (col|expression, search1, result1 [, search2, result2]... [, default])

The DECODE function compares expression against each search value in order.

If a match (equality) is found between the expression and the search argument then it returns the corresponding result.

If there is no match, the default value is returned (if defined), else it returns NULL.

In case of a type compatibility mismatch, Oracle internally does an implicit conversion (if possible) to return the results.

Interestingly, Oracle considers two NULLs to be equivalent while working with the DECODE function.

SQL> SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') FROM DUAL;

DECODE

-----

EQUAL

If expression is NULL, then Oracle returns the result of the first search that is also NULL. The maximum number of components in the DECODE function, including expression, searches, results, and default, is 255.

SQL> SELECT FIRST_NAME, SALARY, DECODE (HIRE_DATE, SYSDATE,'NEW JOINEE','EMPLOYEE')

FROM EMPLOYEES;

SELECT last_name, job_id, salary,

DECODE (job_id,'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary)
REVISED_SALARY

FROM employees;

Q8: What is CASE expression in sql ?

- It facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement.

-- it is basically two types.

a) Simple CASE expression

b) Searched CASE expression

a) Simple CASE expression example:

DESC PROGRAMMING_LANGUAGES;

Name Null? Type
----------------------------------------- -------- ----------------------------
LANGUAGE_NAME VARCHAR2(20)
RATING_CODE VARCHAR2(20)

SQL> SELECT * FROM PROGRAMMING_LANGUAGES;

LANGUAGE_NAME RATING_CODE
-------------------- --------------------
PHYSICS 1
CHEMISTRY 2
MATH 3
BOTANY 4

SQL>

SELECT LANGUAGE_NAME, RATING_CODE AS RATING,
CASE RATING_CODE
WHEN '3' THEN 'DIFFICULT'
WHEN '2' THEN 'FAIRLY DIFFICULT'
WHEN '1' THEN 'EASY'
ELSE 'UNKNOWN'
END AS RATING_DESCRIPTION
FROM PROGRAMMING_LANGUAGES;

LANGUAGE_NAME RATING RATING_DESCRIPTION
-------------------- -------------------- ------------------
PHYSICS 1 EASY
CHEMISTRY 2 FAIRLY DIFFICULT
MATH 3 DIFFICULT
BOTANY 4 UNKNOWN

b) Searched CASE expression Example:

SELECT last_name, job_id, salary,

CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"

FROM employees;

The searched CASE Statement versus simple CASE expression:

In the simple CASE statement above, note that we highlighted the column name RATING_CODE in red,
because that is the major difference between the normal, simpler CASE statement and the Searched CASE Statement.
In the simple CASE statement above, the value in the RATING_CODE column will just be checked to see if it’s equal to the value in the “WHEN” statement.
The searched CASE Statement does not use the value in the column name, but rather offers more flexibility because you can do any sort of comparison you want in the “WHEN” statements.
And that is the primary difference between the two types of CASE statements.

Q9: What are the characteristics of single row functions ?

-Manipulate the data items.
-Accepts arguments and return one value
- Act on each row returned
-Return one result per row
-May modify the data type
-can be nested
-Accepts arguments which can be column or an expression

These are general functions.

- NVL ( expr1, expr2) -Converts NULL value to actual value

NVL:

- Converts NULL value to actual value

- Data types that are used are date,character and number.

- Data type must match :

E.g
-NVL( commission_pct,0)
-NVL (hire_date,'01-JAN-97')
-NVL(job_id, 'No job yet)

-NVL2 ( expr1, expr2, expr3) - If expr1 is not null , returns expr2 & If expr1 is null, returns expr3.

NVL2( string1, value_if_NOT_null, value_if_null )

PARAMETERS OR ARGUMENTS

string1 is the string to test for a null value.

value_if_NOT_null is the value returned if string1 is not null.

value_if_null is the value returned if string1 is null.

-NULLIF ( expr1, expr2) - It compares expr1 and expr2 , returns NULL if they are equal , returns expr1 if not equal.

-COALSECE ( expr1, expr2, ...., exprn) - Returns first non-null expression in the expression list.

expr1 : returns this expression if it not null
expr2 : returns this expression if expr1 is null and expr2 is not null
exprn : returns this expression if preceeding expressions are null.

SELECT last_name, coalesce (commission_pct,salary, 10) comm
from employees
order by commission_pct;

-In the above example if the commsion_pct is not null, it will be shown.

-if the commsion_pct is null,then salary will be shown.

-If commission_pct and salary are NUll, then value 10 will be shown.

-CASE

-DECODE

Q10: What is Join and types of Joins and what is Outer Join?

-- Please refer Wednesday, February 26, 2014 post.

Q11: What is Set operator and what are the types what is the diff between Set operators and Joins?

-These operator are used to join the o/p of 2 queries.

Types: union, union all, intersect, minus.

Difference - Set operator require same structure of both queries while join need not require this.

Q11: What are Pseudocolumns in sql ?

Pseudocolumns are not actual columns in a table, but they behave like columns.
They can be thought of as special-purpose data elements that can be used in SQL statements
just as if they were part of the table, but are not actually contained in a table.
Essentially, a pseudo-column is an Oracle assigned value used in the same context as an Oracle Database column, but not stored on disk.

You can select values from a pseudocolumn but you cannot INSERT INTO, UPDATE, or DELETE from a pseudo-column.
Pseudocolumns are allowed in SQL statements, but not in procedural statements.
SQL and PL/SQL recognize the following SQL pseudocolumns, which return specific data items (this is a partial list):

ROWID
ROWNUM
SYSDATE
SYSTIMESTAMP
UID
USER
LEVEL
CURRVAL
NEXTVAL
ORA_ROWSCN
OBJECT_VALUE
VERSIONS_XID
VERSIONS_OPERATION
VERSIONS_STARTSCN
VERSIONS_ENDSCN
CONNECT_BY_ISCYCLE

Q12 : What is View? What is the advantage? What are the types of views we have?

It is also a db objects .it is virtual table that is not having data of its own.
Easy retrieval of the data from database
Types: updatable, not updateable (or) simple ,complex

There r two types of VIEW in ORACLE.
Simple View and Complex View
simple view derives data from only one table but complex view derives data from many table
simple view contain no functions or group of data but complex view contain function or group of data
simple view always allow DML operation through th view but complex view does not always allow

Q13: When we can not update the view?

When it contains joins, set operators, rownum, group by ,having.

Q14: What is Materialized view or snapshot?

It is replicas of data from remote database, local copy of remote table, used for replication, distributing, computing.
Used in large data like db warehousing.
· It is a view which is associated with a query having any no of joins and any no of group functions
· We can’t perform DML operations in Materialized views
· Snap shot is used for increase the performance by reducing network traffic by reducing i/o
· It is used for replication of data between distributed databases.

Q15 : What are the Inline views?

Please refer earlier post.

Q16 : What is synonym and what are the types and advantages with example?

Synonym is an alternate name for a table, view.
Two types – public, private

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

You generally use synonyms when you are granting access to an object from another schema and you don't want the users to have to worry about knowing which schema owns the object.

CREATE SYNONYM (OR REPLACE)

You may wish to create a synonym so that users do not have to prefix the table name with the schema name when using the table in a query.

SYNTAX

The syntax to create a synonym in Oracle is:

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema .] synonym_name
FOR [schema .] object_name [@ dblink];

OR REPLACE allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command.

PUBLIC means that the synonym is a public synonym and is accessible to all users. Remember though that the user must first have the appropriate privileges to the object to use the synonym.

schema is the appropriate schema. If this phrase is omitted, Oracle assumes that you are referring to your own schema.

object_name is the name of the object for which you are creating the synonym. It can be one of the following:

table
view
sequence
stored procedure
function
package
materialized view
java class schema object
user-defined object
synonym
EXAMPLE

Let's look at an example of how to create a synonym in Oracle.

For example:

CREATE PUBLIC SYNONYM suppliers
FOR app.suppliers;
This first CREATE SYNONYM example demonstrates how to create a synonym called suppliers. Now, users of other schemas can reference the table called suppliers without having to prefix the table name with the schema named app. For example:

SELECT *
FROM suppliers;

Q17 : What is Index? How are Advantage of Index?

An Index is a schema object used by the oracle server to speed up the retrieval of rows by using a pointer.

Can reduce disk I/O by using the rapid path access method to locate data quickly.

Is independent of table it indexes.

Is used and maintained automatically by the oracle server.

-- To be prepared in detail.

Q18 : What is diff between Replace and Translate?

REPLACE and TRANSLATE are character function in Oracle.

Both function looks similar but there are some subtle difference. We can say Translate is an extended version of Replace.

REPLACE TRANSLATE

Replaces entire string at a time Replaces character one-to-one basis

Returns string if no match found Returns string if no match found

SELECT REPLACE('So What', 'o', 'ay') FROM dual;

Say What
Replace only occurance of word with specified word if match found like o with ay.

SELECT TRANSLATE('So What', 'ah', 'e') FROM dual;

So Wet

The a is replaced with an e, the h has no complement and is dropped.

Additional :

Replace:-

The Replace function replaces a sequence of characters in a string with another set of characters (a number of times).

The syntax for the Replace function is:
SQL> select replace('satyam','am','kam') from dual;

REPLACE('
---------
Satykam

Syntax
The syntax for the Replace function is:
Replace ( string1, find, replacement )

string1 is the string to replace a sequence of characters with another set of characters.

find is the string that will be searched for in string1.

replacement will replace find in string1.

In Oracle/PLSQL, the translate function replaces a sequence of characters in a string with another set of characters.
However, it replaces a single character at a time.
For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string.

Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.

The syntax for the translate function is:

translate( string1, string_to_replace, replacement_string )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string - All characters in the string_to_replace will be replaced with the corresponding character in the replacement_string.

For example:

Translate('1tech23', '123', '456); would return '4tech56'

Translate('222tech, '2ec', '3it'); would return '333tith'

SQL> select translate('satyam','aya','ven') from dual;

TRANSL
------
svtevm

Q19 : How can we find out no of Indexes we have create against a Table?

USER_INDEXES

Q20: What is the difference between WHERE and having clause ?

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

When WHERE and HAVING clause are used together in a SELECT query with aggregate function,
WHERE clause is applied first on individual rows and only rows which pass the condition is included for creating groups.
Once group is created, HAVING clause is used to filter groups based upon condition specified.

If WHERE and HAVING clause is used together, first WHERE clause is applied to filter rows and only after grouping HAVING clause is applied.

Q21: What is the syntantical order of sql query ?

Where -> Group by -> Having -> order by

Q22: What are the constraints those we have and how to declare at table level ?

Primary Key

Foreign Key

Q23 : What is the diff between primary key and Unique?

Table can have only one Primary Key

Table can have number of Unique Keys

Q24 : How to display even and odd rows in oracle ?

For even rows
select * from (select rownum rn, tableName.* from tableName) where mod(rn,2) = 0;

For odd rows
select * from (select rownum rn, tableName.* from tableName) where mod(rn,2) = 1;

Q25 : How to display multiple column values per row ?

select
rtrim (xmlagg (xmlelement (e, first_name || ',')).extract ('//text()'), ',') enames
from
employees;

ENAMES
--------------------------------------------------------------------------------
Ellen,Sundar,Mozhe,David,Hermann,Shelli,Amit,Elizabeth,Sarah,David,Laura,Harriso
n,Alexis,Anthony,Gerald,Nanette,John,Kelly,Karen,Curtis,Lex,Julia,Jennifer,Louis
e,Bruce,Alberto,Britney,Daniel,Pat,Kevin,Jean,Tayler,Adam,Timothy,Ki,Girard,Will
iam,Douglas,Kimberely,Nancy,Danielle,Peter,Michael,Shelley,Guy,Alexander,Alyssa,
Charles,Vance,Payam,Alexander,Janette,Steven,Neena,Sundita,Renske,James,David,Ja
ck,Diana,Jason,Steven,James,Mattea,Randall,Susan,Samuel,Allan,Irene,Kevin,Julia,
Donald,Christopher,TJ,Lisa,Karen,Valli,Joshua,Randall,Hazel,Luis,Trenna,Den,Mich
ael,John,Nandita,Ismael,John,Sarath,Lindsey,William,Stephen,Martha,Patrick,Jonat
hon,Winston,Sigal,Peter,Oliver,Jose Manuel,Peter,Clara,Shanta,Alana,Matthew,Jenn
ifer,Eleni

1 row selected.

Q26: what is the difference between cursor and collections ?

cursor
When a query is executed in oracle, a result set is produced and stored in the memory.
Oracle allows the programmer to access this result set in the memory through cursors.

Collection:

A Collection is an ordered group of elements, all of the same type.

Each element has a unique subscript, called an index, that determines its position in the collection

Q27: Can we compare two Null values? Can we insert more than one null value in the unique column?

No, Yes

How to get First Day Of A Month ?

select to_char(to_date('012014','mmyyyy'),'DAY') from dual;

How to get First Date And Last Date Of Previous Month In Oracle ?

select trunc(trunc(sysdate,'MM')-1,'MM') "First Day of Last Month",

trunc(sysdate,'MM')-1 "Last Day of Last Month" from dual;

How Can I Get A Patricular Day Of 6 Year Before ?

SELECT sysdate,add_months(sysdate,-12*6),to_char(add_months(sysdate,-12*6),'Day') FROM dual;

HOW TO GET LAST DAY OF A MONTH FROM A GIVEN DATE?

SELECT LAST_DAY(to_date('07/03/2014','MM/DD/YYYY')) from dual;

SELECT LAST_DAY(SYSDATE) from dual;

***************************************************
****************************************************
1. Increase the salary of employee 115 based on the following conditions:

If experience is more than 10 years, increase salary by 20% If experience is greater than 5 years, increase salary by 10%

Otherwise 5% using case.

DECLARE

v_exp NUMBER;
v_sal emp.sal%TYPE;
v_hiredate emp.hiredate%TYPE;

BEGIN
SELECT sal, hiredate
INTO v_sal, v_hiredate
FROM emp
WHERE empno = 115;

v_exp := ROUND (MONTHS_BETWEEN (SYSDATE, v_hiredate) / 12);

CASE
WHEN v_exp > 10 THEN
UPDATE emp
SET sal = v_sal*1.2
WHERE empno = 115;

WHEN v_exp > 5 THEN
UPDATE emp
SET sal = v_sal*1.1
WHERE empno = 115;
ELSE
UPDATE emp
SET sal = (sal * 1.05)
WHERE empno = 115;

END CASE;
END ;

2.Change commission percentage as follows for employee with ID = 150. If salary is more than 10000 then commission is 0.4%,

if Salary is less than 10000 but experience is more than 10 years then 0.35%, if salary is less than 3000 then commission is

0.25%. In the remaining cases commission is 0.15%.

SQL> CREATE OR REPLACE PROCEDURE SCOTT.proc14
2 IS
3 v_hiredate emp.hiredate%TYPE;
4 v_sal emp.sal%TYPE;
5 BEGIN
6 SELECT hiredate, sal
7 INTO v_hiredate, v_sal
8 FROM emp
9 WHERE empno = 7934;
10
11
12 IF v_sal > 3000
13 THEN
14 UPDATE employee
15 SET comm = (v_sal * .4)
16 WHERE empno = 7934;
17 ELSIF v_sal < 3000 AND MONTHS_BETWEEN (SYSDATE, v_hiredate) / 12 > 10
18 THEN
19 UPDATE employee
20 SET comm = (v_sal * .35)
21 WHERE empno = 7934;
22 ELSIF v_sal < 3000
23 THEN
24 UPDATE employee
25 SET comm = (v_sal * .25)
26 WHERE empno = 7934;
27 ELSE
28 UPDATE employee
29 SET comm = (v_sal * .15)
30 WHERE empno = 7934;
31 END IF;
32 END proc14;
33 /

Procedure created.

3.Display the year in which maximum number of employees joined along with how many joined in each month in that year.

SQL> create or replace procedure proc17 is
2
3 v_year number(4);
4 v_month number(2);
5 begin
6 select to_char(hiredate,'yyyy') into v_year
7 from emp
8 group by to_char(hiredate,'yyyy')
9 having count(*) =
10 ( select max( count(*))
11 from emp
12 group by to_char(hiredate,'yyyy'));
13
14 dbms_output.put_line('Year : ' || v_year);
15
16 for month in 1 .. 12
17 loop
18 select count(*) into v_month
19 from emp
20 where to_char(hiredate,'mm') = month and to_char(hiredate,'yyyy') = v_year;
21
22 dbms_output.put_line('Month : ' || to_char(month) || ' Employees : ' || to_char(v_month));
23
24 end loop;
25
26 end proc17;
27 /

Procedure created.

4.Change salary of employee 130 to the salary of the employee with first name ‘Joe’. If Joe is not found then take average

salary of all employees. If more than one employee with first name ‘Joe’ is found then take the least salary of the employees

with first name Joe.

CREATE OR REPLACE procedure SCOTT.proc21 is
v_sal emp2.sal%type;
begin
select sal into v_sal
from emp2 where ename = 'ALLEN';
update emp2 set sal = v_sal
where empno = 7876;
exception

when no_data_found then
update emp2 set sal = (select avg(sal) from emp2)
where empno = 7876;

when too_many_rows then
update emp2 set sal = (select min(sal) from emp2
where ename ='ALLEN'
group by ename )
where EMPNO = 7876;
end proc21;

5.Update salary of an employee based on department and commission percentage. If department is 40 increase salary by 10%. If

department is 70 then 15%, if commission is more than .3% then 5% otherwise 10%.

SQL> create or replace procedure proc19
2 is
3 cursor employee_cur is
4 select employee_id, department_id, commission_pct
5 from employees;
6
7 v_raise number(2);
8 begin
9
10 for rec in employee_cur
11 loop
12 if rec.department_id = 40 then
13 v_raise := 10;
14 elsif rec.department_id = 70 then
15 v_raise := 15;
16 elsif rec.commission_pct > 0.30 then
17 v_raise := 5;
18 else
19 v_raise := 10;
20 end if;
21
22 update employees set salary = salary + salary * v_raise/100
23 where employee_id = rec.employee_id;
24
25 end loop;
26 end proc19;
27 /

Procedure created.

SQL>

6.Create a procedure that takes department ID and changes the manager ID for the department to the employee in the department

with highest salary. (Use Exceptions).

SQL> CREATE OR REPLACE PROCEDURE chg_deptwise_mgr (
2 deptno employees.department_id%TYPE)
3 IS
4 v_empid employees.employee_id%TYPE;
5 BEGIN
6 SELECT employee_id
7 INTO v_empid
8 FROM employees
9 WHERE salary = (SELECT MAX (salary)
10 FROM employees
11 WHERE department_id = deptno)
12 AND department_id = deptno;
13
14 UPDATE departments
15 SET manager_id = v_empid
16 WHERE department_id = deptno;
17 EXCEPTION
18 WHEN NO_DATA_FOUND
19 THEN
20 DBMS_OUTPUT.put_line ('The department number is not present');
21 END chg_deptwise_mgr;
22
23 /

Procedure created.

SQL>

7.Create a function that takes a manager ID and return the names of employees who report to this manager. The names must be

returned as a string with comma separating names.

SQL> create or replace function get_emp_for_mgr(mgr_id employees.manager_id%type)
2 return varchar2
3 is
4 v_emp varchar2(1000) := '';
5 cursor empcur is
6 select first_name from employees
7 where manager_id = mgr_id;
8 begin
9
10 for emprec in empcur
11 loop
12 v_emp := v_emp || ',' || emprec.first_name;
13 end loop;
14 return ltrim(v_emp,',');
15 end get_emp_for_mgr;
16 /

Function created.

SQL> select get_emp_for_mgr(100) from dual;

GET_EMP_FOR_MGR(100)
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
Michael,Neena,Lex,Den,Matthew,Adam,Payam,Shanta,Kevin,John,Karen,Alberto,Gerald,Eleni

SQL>

**************************************************************XXXXXX************************************

PLSQL practice:

1. create a procedure to accept dept id and IN parameter and OUT parameter as dept name. Display the dept name.

SQL> CREATE OR REPLACE PROCEDURE proc1 (p_dno IN dept.deptno%type , p_dname OUT dept.dname%type) IS
2
3
4 BEGIN
5 SELECT dname into p_dname
6 FROM dept
7 WHERE deptno = p_dno;
8
9 DBMS_OUTPUT.PUT_LINE ('Department Name is :' || p_dname);
10
11 END proc1;
12 /

Procedure created.

SQL> variable name varchar2(20)
SQL> exec proc1(10,:name);
Department Name is :ACCOUNTING

PL/SQL procedure successfully completed.

SQL>

2.Create table similar in structure to emp table(CTAS)
Create a procedure named ADD_EMPLOYEE to hire an employee. Parameters to the procedure are job, mgr, hiredate, salary,

commission and deptno.
Employee number is not taken as a parameter but is auto generated by using a SEQUENCE. if exceptions then throw some

message.

Ans:

SQL> CREATE SEQUENCE add_sqq
2 INCREMENT BY 1
3 START WITH 101
4 MAXVALUE 999;

Sequence created.

1 CREATE OR REPLACE PROCEDURE add_employee
2 (
3 p_ename IN emp.ename%type,
4 p_job IN emp.job%type,
5 p_mgr IN emp.mgr%type,
6 p_hiredate IN emp.hiredate%type,
7 p_sal IN emp.sal%type,
8 p_comm IN emp.comm%type,
9 p_deptno IN emp.deptno%type)
10 IS
11 BEGIN
12 INSERT
13 INTO emp VALUES
14 (
15 add_sqq.NEXTVAL,
16 p_ename,
17 p_job,
18 p_mgr,
19 p_hiredate,
20 p_sal,
21 p_comm,
22 p_deptno
23 );
24 EXCEPTION
25 WHEN OTHERS THEN
26 raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
27* END add_employee;
28 /

Procedure created.

SQL> exec add_employee ('Harish', 'ANALYST',7782,'10-SEP-14', 5000, 300, 10);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from emp
2 where ename='Harish';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
101 Harish ANALYST 7782 10-SEP-14 5000 300 10

2.Give a call to ADD_EMPLOYEE through an anonymous PL/SQL block.

SQL> begin
2 add_employee ('Dipesh', 'ANALYST',7787,'10-SEP-14', 6000, 300, 10);
3 end;
4 /

3.package both the programs above and call know how to call them.

SQL> create or replace package my_pack AS
2 procedure proc1 (p_dno IN dept.deptno%type , p_dname OUT dept.dname%type);
3 procedure add_employee
4 (
5 p_ename IN emp.ename%type,
6 p_job IN emp.job%type,
7 p_mgr IN emp.mgr%type,
8 p_hiredate IN emp.hiredate%type,
9 p_sal IN emp.sal%type,
10 p_comm IN emp.comm%type,
11 p_deptno IN emp.deptno%type);
12 end my_pack ;
13 /

Package created.

SQL> CREATE OR REPLACE PACKAGE body my_pack AS
2 PROCEDURE proc1
3 (p_dno IN dept.deptno%type , p_dname OUT dept.dname%type)
4 IS
5 BEGIN
6 SELECT dname into p_dname
7 FROM dept
8 WHERE deptno = p_dno;
9
10 DBMS_OUTPUT.PUT_LINE ('Department Name is :' || p_dname);
11
12 END proc1;
13
14 PROCEDURE add_employee
15 (
16 p_ename IN emp.ename%type,
17 p_job IN emp.job%type,
18 p_mgr IN emp.mgr%type,
19 p_hiredate IN emp.hiredate%type,
20 p_sal IN emp.sal%type,
21 p_comm IN emp.comm%type,
22 p_deptno IN emp.deptno%type)
23 IS
24 BEGIN
25 INSERT
26 INTO emp VALUES
27 (
28 add_sqq.NEXTVAL,
29 p_ename,
30 p_job,
31 p_mgr,
32 p_hiredate,
33 p_sal,
34 p_comm,
35 p_deptno
36 );
37 EXCEPTION
38 WHEN OTHERS THEN
39 raise_application_error
40 (
41 -20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM );
42 END add_employee;
43 END my_pack ;
44 /

Package body created.

To Call the Package is : package_name.procedure_name ( Parameter_List);

--

4.create a function VALIDATE_EMP which accepts an employee number in a parameter p_eno and returns TRUE if the value of

employee number exists in the EMP table else it returns FALSE.

SQL> CREATE OR REPLACE FUNCTION check_emp (p_empid IN emp.empno%TYPE)
2 RETURN NUMBER
3 AS
4 l_var NUMBER;
5 BEGIN
6 SELECT count(*)
7 INTO l_var
8 FROM emp
9 WHERE empno = p_empid;
10
11 IF l_var = 1
12 THEN
13 RETURN 1;
14 ELSE
15 RETURN 0;
16 END IF;
17 END;
18 /

Function created.

SQL>

/* BOOLEAN type is not supported in SQL. So only case comparison, we get the BOOLEAN result as TRUE of FALSE*/

SQL> SELECT CASE check_emp (7903)
2 WHEN 1
3 THEN 'TRUE'
4 WHEN 0
5 THEN 'FALSE'
6 END
7 FROM DUAL;

CASEC
-----
FALSE

--

5.Create a package named MY_EMP_PACK having two overloaded functions named GET_AVG_SAL. The first function accepts ename as

a parameter while the
second function accepts empno as a parameter and both return the average salary paid in the department to which the

employee belongs.
Both the functions should handle the exception for non-existing employee by displaying an appropriate error message.

SQL> CREATE OR REPLACE PACKAGE mypack1
2 IS
3 FUNCTION get_avg_sal (p_name emp.ename%type)
4 RETURN NUMBER;
5
6 FUNCTION get_avg_sal (p_mpno NUMBER)
7 RETURN NUMBER;
8 END mypack1;
9 /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY mypack1
2 IS
3 FUNCTION get_avg_sal (p_name emp.ename%type)
4 RETURN NUMBER
5 IS
6 salary NUMBER (6);
7
8 CURSOR c1
9 IS
10 SELECT ROUND (AVG (sal))
11 FROM emp
12 WHERE deptno = (SELECT deptno
13 FROM emp
14 WHERE ename = UPPER (p_name));
15 BEGIN
16 OPEN c1;
17
18 FETCH c1 INTO salary;
19
20 RETURN (salary);
21 END get_avg_sal;
22
23
24 FUNCTION get_avg_sal (p_mpno NUMBER)
25 RETURN NUMBER
26 IS
27 salary NUMBER (6);
28
29 CURSOR c1
30 IS
31 SELECT ROUND (AVG (sal))
32 FROM emp
33 WHERE deptno = (SELECT deptno
34 FROM emp
35 WHERE empno = p_mpno);
36 BEGIN
37 OPEN c1;
38
39 FETCH c1 INTO salary;
40
41 RETURN (salary);
42 END get_avg_sal;
43 END mypack1;
44 /

Package body created.

6.Write a PL/SQL block that takes a department number from a user and increases the salary of all the employees belonging

to the department by 10%.
The block should display on the screen how many records are updated.

CREATE OR REPLACE PROCEDURE proc_incr (v_deptno emp.deptno%TYPE)
IS
cnt NUMBER;
new_sal emp.sal%type;
CURSOR c1 (s_deptno emp.deptno%TYPE)
IS
SELECT ename, sal,empno
FROM emp
WHERE deptno = s_deptno
for update;
BEGIN
FOR rec IN c1 (v_deptno)
LOOP

new_sal := ROUND (rec.sal + (rec.sal * .10));
update emp
set sal = new_sal
where empno = rec.empno;

DBMS_OUTPUT.put_line ('Employee Name : ' || rec.ename);
DBMS_OUTPUT.put_line (
'Updated Employee Salary : ' || new_sal);
cnt := c1%ROWCOUNT;

END LOOP;

DBMS_OUTPUT.put_line (cnt || ' no of records updated......');
END proc_incr;
/

SQL Practice:

1. Find the name of the departments with most number of employees

2.Display the average salary, and job title for each job where the average is greater than $35,000.

SQL> SELECT avg(salary),job_id
2 from employees
3 group by job_id
4 HAVING avg(salary) > 35000;

3.List each department that has an average salary less than the company average. Show department number and average salary.

SQL> select department_id, avg(salary)
2 from employees
3 group by department_id
4 HAVING avg(salary) < (SELECT AVG(salary)
5 from employees);

DEPARTMENT_ID AVG(SALARY)
------------- -----------
30 4150
50 3475.55556
10 4400
60 5760

4.Create a query to display the name and hire date of any employee hired after employee Davies.

SQL> SELECT first_name || ' ' || last_name AS Employee_list , hire_date
2 FROM employees
3 WHERE hire_date > (SELECT hire_date
4 FROM employees
5 WHERE last_name = 'Davies');

5.Display the names and hire dates for all employees who were hired before their managers, along with
their manager’s names and hire dates. Label the columns Employee, Emp
Hired, Manager, and Mgr Hired, respectively.

SQL> SELECT e.first_name || ' ' || e.last_name "Employee",
2 e.hire_date "Employee Hired",
3 m.first_name || ' ' || m.last_name "Manager",
4 m.hire_date "Mgr Hired"
5 FROM employees e, employees m
6 WHERE e.manager_id = m.employee_id
7 AND e.hire_date < m.hire_date;

Employee Employee Manager Mgr Hired
---------------------------------------------- --------- ---------------------------------------------- ---------
Payam Kaufling 01-MAY-03 Steven King 17-JUN-03
Den Raphaely 07-DEC-02 Steven King 17-JUN-03
Lex De Haan 13-JAN-01 Steven King 17-JUN-03
Nancy Greenberg 17-AUG-02 Neena Kochhar 21-SEP-05
Shelley Higgins 07-JUN-02 Neena Kochhar 21-SEP-05
Hermann Baer 07-JUN-02 Neena Kochhar 21-SEP-05
Susan Mavris 07-JUN-02 Neena Kochhar 21-SEP-05
Jennifer Whalen 17-SEP-03 Neena Kochhar 21-SEP-05
David Austin 25-JUN-05 Alexander Hunold 03-JAN-06
Daniel Faviet 16-AUG-02 Nancy Greenberg 17-AUG-02
Alexis Bull 20-FEB-05 Adam Fripp 10-APR-05
Nandita Sarchand 27-JAN-04 Adam Fripp 10-APR-05
James Marlow 16-FEB-05 Adam Fripp 10-APR-05
Britney Everett 03-MAR-05 Shanta Vollman 10-OCT-05
Sarah Bell 04-FEB-04 Shanta Vollman 10-OCT-05
Renske Ladwig 14-JUL-03 Shanta Vollman 10-OCT-05
Kevin Feeney 23-MAY-06 Kevin Mourgos 16-NOV-07
Alana Walsh 24-APR-06 Kevin Mourgos 16-NOV-07
Peter Vargas 09-JUL-06 Kevin Mourgos 16-NOV-07
Randall Matos 15-MAR-06 Kevin Mourgos 16-NOV-07
Curtis Davies 29-JAN-05 Kevin Mourgos 16-NOV-07
Trenna Rajs 17-OCT-03 Kevin Mourgos 16-NOV-07
Donald OConnell 21-JUN-07 Kevin Mourgos 16-NOV-07
Allan McEwen 01-AUG-04 Karen Partners 05-JAN-05
Patrick Sully 04-MAR-04 Karen Partners 05-JAN-05
Janette King 30-JAN-04 Karen Partners 05-JAN-05
Elizabeth Bates 24-MAR-07 Gerald Cambrault 15-OCT-07
William Smith 23-FEB-07 Gerald Cambrault 15-OCT-07
Tayler Fox 24-JAN-06 Gerald Cambrault 15-OCT-07
Harrison Bloom 23-MAR-06 Gerald Cambrault 15-OCT-07
Lisa Ozer 11-MAR-05 Gerald Cambrault 15-OCT-07
Charles Johnson 04-JAN-08 Eleni Zlotkey 29-JAN-08
Kimberely Grant 24-MAY-07 Eleni Zlotkey 29-JAN-08
Jack Livingston 23-APR-06 Eleni Zlotkey 29-JAN-08
Jonathon Taylor 24-MAR-06 Eleni Zlotkey 29-JAN-08
Alyssa Hutton 19-MAR-05 Eleni Zlotkey 29-JAN-08
Ellen Abel 11-MAY-04 Eleni Zlotkey 29-JAN-08

37 rows selected.

6.Display the manager number and the salary of the lowest paid employee for that manager.
Exclude anyone whose manager is not known. Exclude any groups where the minimum
salary is less than $6,000. Sort the output in descending order of salary.

SQL> SELECT MIN (salary), manager_id
2 FROM employees
3 WHERE manager_id IS NOT NULL
4 GROUP BY manager_id
5 HAVING MIN (salary) > 6000
6 ORDER BY MIN (salary) DESC;

MIN(SALARY) MANAGER_ID
----------- ----------
9000 102
8300 205
7000 146
7000 145
6900 108
6200 147
6200 149
6100 148

8 rows selected.

***********************************************
***********************************************

Queries

Display details of jobs where the minimum salary is greater than 10000.

SELECT * FROM JOBS WHERE MIN_SALARY > 10000

Display the first name and join date of the employees who joined between 2002 and 2005.

SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES 
WHERE TO_CHAR(HIRE_DATE, 'YYYY') BETWEEN 2002 AND 2005 ORDER BY HIRE_DATE

Display first name and join date of the employees who is either IT Programmer or Sales Man.

SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES WHERE JOB_ID IN ('IT_PROG', 'SA_MAN')

Display employees who joined after 1st January 2008.

SELECT * FROM EMPLOYEES  where hire_date > '01-jan-2008'

Display details of employee with ID 150 or 160.

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID in (150,160)

Display first name, salary, commission pct, and hire date for employees with salary less than 10000.

SELECT FIRST_NAME, SALARY, COMMISSION_PCT, HIRE_DATE FROM EMPLOYEES WHERE SALARY < 10000

Display job Title, the difference between minimum and maximum salaries for jobs with max salary in the range 10000 to 20000.

SELECT JOB_TITLE, MAX_SALARY-MIN_SALARY DIFFERENCE FROM JOBS WHERE MAX_SALARY BETWEEN 10000 AND 20000

Display first name, salary, and round the salary to thousands.

SELECT FIRST_NAME, SALARY, ROUND(SALARY, -3) FROM EMPLOYEES

Display details of jobs in the descending order of the title.

SELECT * FROM JOBS ORDER BY JOB_TITLE

Display employees where the first name or last name starts with S.

SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE  FIRST_NAME  LIKE 'S%' OR LAST_NAME LIKE 'S%'

Display employees who joined in the month of May.

SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'MON')= 'MAY'

Display details of the employees where commission percentage is null and salary in the range 5000 to 10000 and department is 30.

SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT IS NULL AND SALARY BETWEEN 5000 AND 10000 AND DEPARTMENT_ID=30

Display first name and date of first salary of the employees.

SELECT FIRST_NAME, HIRE_DATE, LAST_DAY(HIRE_DATE)+1 FROM EMPLOYEES

Display first name and experience of the employees.

SELECT FIRST_NAME, HIRE_DATE, FLOOR((SYSDATE-HIRE_DATE)/365)FROM EMPLOYEES

Display first name of employees who joined in 2001.

SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'YYYY')=2001

Display first name and last name after converting the first letter of each name to upper case and the rest to lower case.

SELECT INITCAP(FIRST_NAME), INITCAP(LAST_NAME) FROM EMPLOYEES

Display the first word in job title.

SELECT JOB_TITLE,  SUBSTR(JOB_TITLE,1, INSTR(JOB_TITLE, ' ')-1) FROM JOBS

Display the length of first name for employees where last name contain character ‘b’ after 3rd position.

SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE INSTR(LAST_NAME,'B') > 3

Display first name in upper case and email address in lower case for employees where the first name and email address are same irrespective of the case.

SELECT UPPER(FIRST_NAME), LOWER(EMAIL) FROM EMPLOYEES WHERE UPPER(FIRST_NAME)= UPPER(EMAIL)

Display employees who joined in the current year.

SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY')=TO_CHAR(SYSDATE, 'YYYY')

Display the number of days between system date and 1st January 2011.

SELECT SYSDATE - to_date('01-jan-2011') FROM DUAL

Display how many employees joined in each month of the current year.

SELECT TO_CHAR(HIRE_DATE,'MM'), COUNT (*) FROM EMPLOYEES 
WHERE TO_CHAR(HIRE_DATE,'YYYY')= TO_CHAR(SYSDATE,'YYYY') GROUP BY TO_CHAR(HIRE_DATE,'MM')

Display manager ID and number of employees managed by the manager.

 SELECT MANAGER_ID, COUNT(*) FROM EMPLOYEES GROUP BY MANAGER_ID

Display employee ID and the date on which he ended his previous job.

 SELECT EMPLOYEE_ID, MAX(END_DATE) FROM JOB_HISTORY GROUP BY EMPLOYEE_ID

Display number of employees joined after 15th of the month.

SELECT COUNT(*) FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'DD') > 15

Display the country ID and number of cities we have in the country.

SELECT COUNTRY_ID,  COUNT(*)  FROM LOCATIONS GROUP BY COUNTRY_ID

Display average salary of employees in each department who have commission percentage.

SELECT DEPARTMENT_ID, AVG(SALARY) FROM EMPLOYEES 
WHERE COMMISSION_PCT IS NOT NULL GROUP BY DEPARTMENT_ID

Display job ID, number of employees, sum of salary, and difference between highest salary and lowest salary of the employees of the job.

SELECT JOB_ID, COUNT(*), SUM(SALARY), MAX(SALARY)-MIN(SALARY) SALARY FROM EMPLOYEES GROUP BY JOB_ID

Display job ID for jobs with average salary more than 10000.

SELECT JOB_ID, AVG(SALARY) FROM EMPLOYEES 
GROUP BY JOB_ID 
HAVING AVG(SALARY)>10000

Display years in which more than 10 employees joined.

SELECT TO_CHAR(HIRE_DATE,'YYYY') FROM EMPLOYEES 
GROUP BY TO_CHAR(HIRE_DATE,'YYYY') 
HAVING COUNT(EMPLOYEE_ID) > 10

Display departments in which more than five employees have commission percentage.

SELECT DEPARTMENT_ID FROM EMPLOYEES 
WHERE COMMISSION_PCT IS NOT NULL
GROUP BY DEPARTMENT_ID 
HAVING COUNT(COMMISSION_PCT)>5

Display employee ID for employees who did more than one job in the past.

SELECT EMPLOYEE_ID FROM JOB_HISTORY GROUP BY EMPLOYEE_ID HAVING COUNT(*) > 1

Display job ID of jobs that were done by more than 3 employees for more than 100 days.

SELECT JOB_ID FROM JOB_HISTORY 
WHERE END_DATE-START_DATE > 100 
GROUP BY JOB_ID 
HAVING COUNT(*)>3

Display department ID, year, and Number of employees joined.

SELECT DEPARTMENT_ID, TO_CHAR(HIRE_DATE,'YYYY'), COUNT(EMPLOYEE_ID) 
FROM EMPLOYEES 
GROUP BY DEPARTMENT_ID, TO_CHAR(HIRE_DATE, 'YYYY')
ORDER BY DEPARTMENT_ID

Display departments where any manager is managing more than 5 employees.

SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, MANAGER_ID 
HAVING COUNT(EMPLOYEE_ID) > 5

Change salary of employee 115 to 8000 if the existing salary is less than 6000.

UPDATE EMPLOYEES SET SALARY = 8000 WHERE EMPLOYEE_ID = 115 AND SALARY < 6000

Insert a new employee into employees with all the required details.

INSERT INTO EMPLOYEES  (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE,JOB_ID, SALARY, DEPARTMENT_ID) 
VALUES (207, 'ANGELA', 'SNYDER','ANGELA','215 253 4737', SYSDATE, 'SA_MAN', 12000, 80)

Delete department 20.

DELETE FROM DEPARTMENTS WHERE DEPARTMENT_ID=20

Change job ID of employee 110 to IT_PROG if the employee belongs to department 10 and the existing job ID does not start with IT.

UPDATE EMPLOYEES SET JOB_ID= 'IT_PROG' 
WHERE EMPLOYEE_ID=110 AND DEPARTMENT_ID=10 AND NOT JOB_ID LIKE 'IT%'

Insert a row into departments table with manager ID 120 and location ID in any location ID for city Tokyo.

INSERT INTO DEPARTMENTS (150,'SPORTS',120,1200)

Display department name and number of employees in the department.

SELECT DEPARTMENT_NAME, COUNT(*) FROM EMPLOYEES NATURAL JOIN DEPARTMENTS GROUP BY DEPARTMENT_NAME

Display job title, employee ID, number of days between ending date and starting date for all jobs in department 30 from job history.

SELECT EMPLOYEE_ID, JOB_TITLE, END_DATE-START_DATE DAYS 
FROM JOB_HISTORY NATURAL JOIN JOBS 
WHERE DEPARTMENT_ID=30

Display department name and manager first name.

SELECT DEPARTMENT_NAME, FIRST_NAME FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D.MANAGER_ID=E.EMPLOYEE_ID)

Display department name, manager name, and city.

SELECT DEPARTMENT_NAME, FIRST_NAME, CITY FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D.MANAGER_ID=E.EMPLOYEE_ID) JOIN LOCATIONS L USING (LOCATION_ID)

Display country name, city, and department name.

SELECT COUNTRY_NAME, CITY, DEPARTMENT_NAME 
FROM COUNTRIES JOIN LOCATIONS USING (COUNTRY_ID) 
JOIN DEPARTMENTS USING (LOCATION_ID)

Display job title, department name, employee last name, starting date for all jobs from 2000 to 2005.

SELECT JOB_TITLE, DEPARTMENT_NAME, LAST_NAME, START_DATE 
FROM JOB_HISTORY JOIN JOBS USING (JOB_ID) JOIN DEPARTMENTS 
USING (DEPARTMENT_ID) JOIN  EMPLOYEES USING (EMPLOYEE_ID) 
WHERE TO_CHAR(START_DATE,'YYYY') BETWEEN 2000 AND 2005

Display job title and average salary of employees

SELECT JOB_TITLE, AVG(SALARY) FROM EMPLOYEES 
NATURAL JOIN JOBS GROUP BY JOB_TITLE

Display job title, employee name, and the difference between maximum salary for the job and salary of the employee.

SELECT JOB_TITLE, FIRST_NAME, MAX_SALARY-SALARY DIFFERENCE FROM EMPLOYEES NATURAL JOIN JOBS

Display last name, job title of employees who have commission percentage and belongs to department 30.

SELECT JOB_TITLE, FIRST_NAME, MAX_SALARY-SALARY DIFFERENCE FROM EMPLOYEES NATURAL JOIN JOBS WHERE DEPARTMENT_ID  = 30

Display details of jobs that were done by any employee who is currently drawing more than 15000 of salary.

SELECT JH.*
FROM  JOB_HISTORY JH JOIN EMPLOYEES E ON (JH.EMPLOYEE_ID = E.EMPLOYEE_ID)
WHERE SALARY > 15000

Display department name, manager name, and salary of the manager for all managers whose experience is more than 5 years.

SELECT DEPARTMENT_NAME, FIRST_NAME, SALARY 
FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D.MANAGER_ID=E.MANAGER_ID) 
WHERE  (SYSDATE-HIRE_DATE) / 365 > 5

Display employee name if the employee joined before his manager.

SELECT FIRST_NAME FROM  EMPLOYEES E1 JOIN EMPLOYEES E2 ON (E1.MANAGER_ID=E2.EMPLOYEE_ID) 
WHERE E1.HIRE_DATE < E2.HIRE_DATE

Display employee name, job title for the jobs employee did in the past where the job was done less than six months.

SELECT FIRST_NAME, JOB_TITLE FROM EMPLOYEES E JOIN JOB_HISTORY  JH ON (JH.EMPLOYEE_ID = E.EMPLOYEE_ID) JOIN JOBS J  ON( JH.JOB_ID = J.JOB_ID) 
WHERE  MONTHS_BETWEEN(END_DATE,START_DATE)  < 6

Display employee name and country in which he is working.

SELECT FIRST_NAME, COUNTRY_NAME FROM EMPLOYEES JOIN DEPARTMENTS USING(DEPARTMENT_ID) 
JOIN LOCATIONS USING( LOCATION_ID) 
JOIN COUNTRIES USING ( COUNTRY_ID)

Display department name, average salary and number of employees with commission within the department.

 
SELECT DEPARTMENT_NAME, AVG(SALARY), COUNT(COMMISSION_PCT) 
FROM DEPARTMENTS JOIN EMPLOYEES USING (DEPARTMENT_ID) 
GROUP BY DEPARTMENT_NAME

Display the month in which more than 5 employees joined in any department located in Sydney.

SELECT TO_CHAR(HIRE_DATE,'MON-YY')
FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPARTMENT_ID) JOIN  LOCATIONS USING (LOCATION_ID) 
WHERE  CITY = 'Seattle'
GROUP BY TO_CHAR(HIRE_DATE,'MON-YY')
HAVING COUNT(*) > 5

Display details of departments in which the maximum salary is more than 10000.

SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID IN 
( SELECT DEPARTMENT_ID FROM EMPLOYEES 
  GROUP BY DEPARTMENT_ID 
  HAVING MAX(SALARY)>10000)

Display details of departments managed by ‘Smith’.

SELECT * FROM DEPARTMENTS WHERE MANAGER_ID IN 
  (SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE FIRST_NAME='SMITH')

Display jobs into which employees joined in the current year.

SELECT * FROM JOBS WHERE JOB_ID IN 
       (SELECT JOB_ID FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY'))

Display employees who did not do any job in the past.

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID NOT IN 
       (SELECT EMPLOYEE_ID FROM JOB_HISTORY)

Display job title and average salary for employees who did a job in the past.

SELECT JOB_TITLE, AVG(SALARY) FROM JOBS NATURAL JOIN EMPLOYEES 
GROUP BY JOB_TITLE 
WHERE EMPLOYEE_ID IN
    (SELECT EMPLOYEE_ID FROM JOB_HISTORY)

Display country name, city, and number of departments where department has more than 5 employees.

SELECT COUNTRY_NAME, CITY, COUNT(DEPARTMENT_ID)
FROM COUNTRIES JOIN LOCATIONS USING (COUNTRY_ID) JOIN DEPARTMENTS USING (LOCATION_ID) 
WHERE DEPARTMENT_ID IN 
    (SELECT DEPARTMENT_ID FROM EMPLOYEES 
  GROUP BY DEPARTMENT_ID 
  HAVING COUNT(DEPARTMENT_ID)>5)
GROUP BY COUNTRY_NAME, CITY;

Display details of manager who manages more than 5 employees.

SELECT FIRST_NAME FROM EMPLOYEES 
WHERE EMPLOYEE_ID IN 
(SELECT MANAGER_ID FROM EMPLOYEES 
 GROUP BY MANAGER_ID 
 HAVING COUNT(*)>5)

Display employee name, job title, start date, and end date of past jobs of all employees with commission percentage null.

SELECT FIRST_NAME, JOB_TITLE, START_DATE, END_DATE
FROM JOB_HISTORY JH JOIN JOBS J USING (JOB_ID) JOIN EMPLOYEES E  ON ( JH.EMPLOYEE_ID = E.EMPLOYEE_ID)
WHERE COMMISSION_PCT IS NULL

Display the departments into which no employee joined in last two years.

SELECT  * FROM DEPARTMENTS
WHERE DEPARTMENT_ID NOT IN 
( SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE FLOOR((SYSDATE-HIRE_DATE)/365) < 2)

Display the details of departments in which the max salary is greater than 10000 for employees who did a job in the past.

SELECT * FROM DEPARTMENTS
WHERE DEPARTMENT_ID IN 
(SELECT DEPARTMENT_ID FROM EMPLOYEES 
 WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID FROM JOB_HISTORY) 
 GROUP BY DEPARTMENT_ID
 HAVING MAX(SALARY) >10000)

Display details of current job for employees who worked as IT Programmers in the past.

SELECT * FROM JOBS 
WHERE JOB_ID IN 
 (SELECT JOB_ID FROM EMPLOYEES WHERE EMPLOYEE_ID IN 
        (SELECT EMPLOYEE_ID FROM JOB_HISTORY WHERE JOB_ID='IT_PROG'))

Display the details of employees drawing the highest salary in the department.

SELECT DEPARTMENT_ID,FIRST_NAME, SALARY FROM EMPLOYEES OUTER WHERE SALARY = 
    (SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = OUTER.DEPARTMENT_ID)

Display the city of employee whose employee ID is 105.

SELECT CITY FROM LOCATIONS WHERE LOCATION_ID = 
    (SELECT LOCATION_ID FROM DEPARTMENTS WHERE DEPARTMENT_ID =
              (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID=105)
 )

Display third highest salary of all employees

select salary 
from employees main
where  2 = (select count( distinct salary ) 
            from employees
            where  salary > main.salary)

PL/SQL Programs

Write a program to interchange the salaries of employee 120 and 122.

Declare
   V_salary_120   employees.salary%type;
Begin
  Select  salary into v_salary_120
  From employees where  employee_id = 120;

  Update employees set salary  = ( select salary from employees where employee_id = 122)
  Where employee_id = 120;

  Update employees set salary  =  v_salary_120  Where employee_id = 122;

  Commit;
End;

Increase the salary of employee 115 based on the following conditions: If experience is more than 10 years, increase salary by 20% If experience is greater than 5 years, increase salary by 10% Otherwise 5% Case by Expression:

declare
    v_exp  number(2);
    v_hike number(5,2);
begin
    select  floor((sysdate-hire_date) / 365 ) into v_exp
    from employees
    where employee_id = 115;
    
    v_hike := 1.05;
    
    case 
      when  v_exp > 10 then
            v_hike := 1.20;
      when  v_exp > 5  then
            v_hike := 1.10;
    end case;
    
    update employees set salary = salary * v_hike 
    where employee_id = 115;
end;

Change commission percentage as follows for employee with ID = 150. If salary is more than 10000 then commission is 0.4%, if Salary is less than 10000 but experience is more than 10 years then 0.35%, if salary is less than 3000 then commission is 0.25%. In the remaining cases commission is 0.15%.

declare
    v_salary  employees.salary%type;
    v_exp     number(2);
    v_cp      number(5,2);
begin
    select  v_salary,  floor ( (sysdate-hire_date)/365) into v_salary, v_exp
    from  employees
    where employee_id = 150;
    
    if v_salary > 10000 then
           v_cp := 0.4;
    elsif  v_exp > 10 then
           v_cp := 0.35;
    elsif  v_salary < 3000 then
           v_cp := 0.25;
    else
           v_cp := 0.15;
           
    end if;
    
    update employees set commission_pct = v_cp
    where employee_id = 150;
end;

Find out the name of the employee and name of the department for the employee who is managing for employee 103.

declare
    v_name     employees.first_name%type;
    v_deptname departments.department_name%type;
begin
    select  first_name , department_name into v_name, v_deptname
    from  employees join departments using (department_id)
    where employee_id = ( select manager_id from employees    where employee_id = 103);
    
    dbms_output.put_line(v_name);
    dbms_output.put_line(v_deptname);
    
end;

Display missing employee IDs.

declare
     v_min  number(3);
     v_max  number(3);
     v_c    number(1);
begin
     select min(employee_id), max(employee_id) into v_min, v_max
     from employees;


     for i in  v_min + 1 .. v_max - 1
     loop
           select count(*) into v_c
           from employees 
           where employee_id = i;
           
           if  v_c = 0 then
                dbms_output.put_line(i);
           end if;
    end loop;
     
end;

Display the year in which maximum number of employees joined along with how many joined in each month in that year.

declare

      v_year  number(4);
      v_c     number(2);
begin
      select  to_char(hire_date,'yyyy') into v_year
      from  employees
      group by to_char(hire_date,'yyyy')
      having count(*) = 
             ( select  max( count(*))
               from  employees
               group by to_char(hire_date,'yyyy'));
               
      dbms_output.put_line('Year : ' || v_year);

      for month in 1 .. 12
      loop
          select  count(*) into v_c
          from employees
          where  to_char(hire_date,'mm') = month and to_char(hire_date,'yyyy') = v_year;
          
          dbms_output.put_line('Month : ' || to_char(month) || ' Employees : ' || to_char(v_c));

     end loop;          

end;

Change salary of employee 130 to the salary of the employee with first name ‘Joe’. If Joe is not found then take average salary of all employees. If more than one employee with first name ‘Joe’ is found then take the least salary of the employees with first name Joe.

declare
    v_salary  employees.salary%type;
begin
     select salary into v_salary
     from employees where first_name = 'Joe';
     
     update employees set salary = v_salary
     where employee_id = 130;
  
exception
     when no_data_found then
       update employees set salary = (select avg(salary) from employees)
     where employee_id = 130;
end;

Display Job Title and Name of the Employee who joined the job first day.

declare
       cursor  jobscur is select  job_id, job_title from jobs;
    v_name  employees.first_name%type;
begin
       for jobrec in jobscur
    loop
          select first_name into v_name
             from employees
             where hire_date = ( select min(hire_date) from employees where job_id = jobrec.job_id)
    and  job_id = jobrec.job_id;
                 
             dbms_output.put_line( jobrec.job_title || '-' || v_name);         
    end loop;
end;

Display 5th and 10th employees in Employees table.

declare

     cursor empcur is
        select employee_id, first_name 
        from employees;
        
begin
     for emprec  in empcur
     loop
         if empcur%rowcount > 4 then
              dbms_output.put_line( emprec.first_name);
              exit  when   empcur%rowcount > 10;
         end if;
     end loop;
     
end;

Update salary of an employee based on department and commission percentage. If department is 40 increase salary by 10%. If department is 70 then 15%, if commission is more than .3% then 5% otherwise 10%.

declare
    cursor empcur is
     select employee_id, department_id, commission_pct
     from employees;
  
    v_hike  number(2);
begin

    for emprec in empcur
    loop
         if  emprec.department_id = 40 then
              v_hike := 10;
         elsif emprec.department_id = 70 then
              v_hike := 15;
         elsif emprec.commission_pct  > 0.30 then
              v_hike := 5;
         else
              v_hike := 10;
         end if;

         update employees set salary = salary + salary * v_hike/100 
         where employee_id = emprec.employee_id;
         
    end loop;
end;

Create a function that takes department ID and returns the name of the manager of the department.

create or replace function get_dept_manager_name(deptid number)
return varchar is

   v_name  employees.first_name%type;
begin
   select first_name into v_name
   from employees
   where  employee_id = ( select manager_id from departments where department_id = deptid);

   return v_name;
end;

Create a function that takes employee ID and return the number of jobs done by the employee in the past.

create or replace function get_no_of_jobs_done(empid number)
return number is
   v_count  number(2);
begin
   select count(*) into v_count
   from job_history
   where  employee_id = empid;

   return v_count;
end;

Create a procedure that takes department ID and changes the manager ID for the department to the employee in the department with highest salary. (Use Exceptions).

create or replace procedure change_dept_manager(deptid number)
is
   v_empid  employees.employee_id%type;
begin
   select employee_id  into v_empid
   from employees
   where  salary = ( select max(salary) from employees where department_id = deptid)
     and department_id = deptid;

   update departments set manager_id = v_empid
   where  department_id = deptid;
end;

Create a function that takes a manager ID and return the names of employees who report to this manager. The names must be returned as a string with comma separating names.

create or replace function get_employees_for_manager(manager number)
return varchar2
is
   v_employees varchar2(1000) := '';
   cursor empcur is
      select  first_name from employees 
      where   manager_id = manager;
begin

     for emprec in empcur
     loop
         v_employees :=  v_employees ||  ',' || emprec.first_name;
     end loop;
     -- remove extra  , at the beginning
     return  ltrim(v_employees,',');
end;

Ensure no changes can be made to EMPLOYEES table before 6am and after 10pm in a day.

create or replace trigger  trg_employees_time_check
before update or insert or delete
on employees
for each row
begin
   if  to_char(sysdate,'hh24') < 6 or to_char(sysdate,'hh24') > 10 then
         raise_application_error(-20111,'Sorry! No change can be made before 6 AM and after 10 PM');
   end if;
end;

Create a Trigger to ensure the salary of the employee is not decreased.

create or replace trigger  trg_employees_salary_check
before update
on employees
for each row
begin
   if  :old.salary > :new.salary then
         raise_application_error(-20111,'Sorry! Salary can not be decreased!');
   end if;
end;

Create a trigger to ensure the employee and manager belongs to the same department.

Note:  This trigger need to read the row that is being modified, which causes mutating problem.  The solution to mutating problem is
explained at : Work around for mutating problem in Oracle Triggers. Please check it out.

Whenever the job is changed for an employee write the following details into job history. Employee ID, old job ID, old department ID, hire date of the employee for start date, system date for end date. But if a row is already present for employee job history then the start date should be the end date of that row +1.

 create or replace trigger trg_log_job_change
after update of job_id
on employees
for each row
declare
    v_enddate   date;
    v_startdate date;
begin
   -- find out whether the employee has any row in job_history table
   select max(end_date) into v_enddate
   from job_history
   where employee_id = :old.employee_id;

   if v_enddate is null then
      v_startdate := :old.hire_date;
   else
      v_startdate := v_enddate + 1;
   end if;

   insert into  job_history values (:old.employee_id, v_startdate, sysdate, :old.job_id, :old.department_id);
end;

Note: Before testing the above trigger, you need to disable UPDATE_JOB_HISTORY trigger, which is already present in HR account, as it does the same.

All About Oracle (SQL, PL/SQL , Forms and Reports) & UNIX

FAQ_SQL

Q1a: How to get Duplicate Records in SQL.

Insert into CUSTOMER (FIRSTNAME,LASTNAME,MOBILENO) values ('Niraj','Yadav','989898');

Insert into CUSTOMER (FIRSTNAME,LASTNAME,MOBILENO) values ('Chetan','Gadodia','959595');

Insert into CUSTOMER (FIRSTNAME,LASTNAME,MOBILENO) values ('Chetan','Gadodia','959595');

Insert into CUSTOMER (FIRSTNAME,LASTNAME,MOBILENO) values ('Atul','Kokam','42424242');

Insert into CUSTOMER (FIRSTNAME,LASTNAME,MOBILENO) values ('Atul','Kokam','42424242');

Insert into CUSTOMER (FIRSTNAME,LASTNAME,MOBILENO) values ('Vishal','Parte','9394453');

Insert into CUSTOMER (FIRSTNAME,LASTNAME,MOBILENO) values ('Vishal','Parte','9394453');

Insert into CUSTOMER (FIRSTNAME,LASTNAME,MOBILENO) values ('Vishal','Parte','9394453');

Insert into CUSTOMER (FIRSTNAME,LASTNAME,MOBILENO) values ('Jinendra','Jain','121212');

Insert into CUSTOMER (FIRSTNAME,LASTNAME,MOBILENO) values ('Jinendra','Jain','121212');

Q-1 : How to delete Duplicate rows in SQL.

Queries

PL/SQL Programs

No comments:

Post a Comment