CHAR FUNCTIONS

 WAQ TO DISPLAY EACH YEAR WISE SUM OF SALARY IN EMP TABLE

SELECT TO_CHAR( HIREDATE,’YYYY’ ) ,SUM(SAL) FROM EMP GROUP BY TO_CHAR

( HIREDATE, ‘YYYY’);

 WAQ TO DISPLAY EACH DAY WISE SUM OF SALARY IN EMP TABLE

SELECT TO_CHAR(HIREDATE,’DAY’),SUM(SAL) FROM EMP GROUP BY TO_CHAR( HIREDATE,’DAY’);

WAQ TO DISPLAY SUM OF SALARY OF DAY WISE IN 1981,1983,1987

SELECT TO_CHAR(HIREDATE,’DAY’),TO_CHAR(HIREDATE,’YYYY’),SUM(SAL) FROM EMP WHERE

TO_CHAR(HIREDATE,’YYYY’) IN (1981,1983,1987) GROUP BY TO_CHAR(HIREDATE,’DAY’),
TO_CHAR(HIREDATE,’YYYY’);

SUBQUERIES

 WHO ARE GETTING THE FIRST HIGH SALARY FROM EMP TABLE ?

SELECT MAX(SAL) FROM EMP;

SELECT *FROM EMP WHERE SAL=(SELECT MAX(SAL)FROM EMP); ----- SRSQ TOTAL EMPLOYEE LIST

 DISPLAY EMPLOYEE DETAILS WHO ARE GETTING MIN,MAX SALARIES ?

SELECT*FROM EMP WHERE SAL IN

(SELECT MAX(SAL) FROM EMP

UNION

SELECT MIN(SAL) FROM EMP);-----------MRSQ

WAQ TO DISPLAY MAXIMUM SALARY OF EMP TABLE EACH JOB WISE?------MRSQ

SELECT*FROM EMP WHERE SAL IN(SELECT MAX(SAL),JOB FROM EMP GROUP BY JOB);

 WHOSE EMPLOYEE JOB IS SAME AS THE JOB OF 'SMITH' ?

SELECT*FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME=’SMITH’);

 EMPLOYEE DETAILS WHOSE EMPLOYEE JOB IS SAME AS

THE JOB OF THE EMPLOYEE "SMITH","CLARK" ?

SELECT*FROM EMP WHERE JOB IN(SELECT JOB FROM EMP WHERE ENAME=’SMITH’ OR
ENAME=’CLARK’);---------- MRSQ

 WHOSE SALARY IS MORE THAN MAX.SALARY OF THE JOB IS "SALESMAN"?

SELECT*FROM EMP WHERE SAL>(SELECT JOB,MAX(SAL) FROM EMP WHERE JOB=’SALESMAN’);

WASQ TO DISPLAY EMPLOYEES WHOSE SALARY IS MORE THAN ANY "SALESMAN"

SALARY ?-----MRSQ

SELECT *FROM EMP WHERE SAL> ANY(SELECT SAL FROM EMP WHERE JOB=’SALESMAN’);

WASQ TO DISPLAY EMPLOYEES WHOSE SALARY IS MORE THAN OF ALL "SALESMAN"

SALARY ?-----------MRSQ

SELECT *FROM EMP WHERE SAL>ALL (SELECT SAL FROM EMP WHERE JOB=’SALESMAN’);

 WHOSE EMPLOYEE JOB IS SAME AS THE JOB OF "BLAKE" AND WHO ARE EARNING

SALARY MORE THAN "BLAKE" SALARY ?

SELECT * FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME=’BLAKE’) AND
SAL>(SELECT MAX(SAL)FROM EMP WHERE ENAME=’BLAKE’);

 DISPLAY SENIOR EMPLOYEE ?

SELECT MIN(HIREDATE)FROM EMP;

SELECT*FROM EMP WHERE HIREDATE=(SELECT MIN(HIREDATE) FROM EMP); ------SRSQ

 TO FIND SECOND HIGH. SALARY FROM EMP TABLE ?

SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP);

SELECT*FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL)
FROM EMP)); ----SRSQ TOTAL EMPLOYEE LIST

 DISPLAY EMPLOYEE DETAILS WHO ARE GETTING

3rd HIGH. SALARY IN EMP TABLE ?

SELECT *FROM EMP WHERE SAL=

(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT
MAX(SAL) FROM EMP)));

 NO.OF EMPLOYEE OF DEPT. NUMBERS.IN WHICH DEPTNO NO.OF EMPLOYEE

IS LESS THAN THE NO.OF EMPLOYEE OF DEPTNO IS 20 ?

SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO

HAVING COUNT(*)<(SELECT COUNT(*) FROM EMP WHERE DEPTNO=20);

 SUM OF SALARY OF JOBS.IF SUM OF SALARY OF JOBS ARE

MORE THAN SUM OF SALARY OF THE JOB IS 'CLERK' ?

SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB

HAVING SUM( SAL)>(SELECT SUM(SAL) FROM EMP WHERE JOB=’CLERK’);

 TO UPDATE EMPLOYEE SALARY WITH MAX.SALARY OF

EMP TABLE WHOSE EMPNO IS 7900?

QUPDATE EMP SET SAL=(SELECT MAX(SAL) FROM EMP)WHERE EMPNO=7900);

 WASQ TO DELETE EMPLOYEE DETAILS FROM EMP TABLE

WHOSE JOB IS SAME AS THE JOB OF 'SCOTT' ?

DELETE FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME=’SCOTT’);

MCSQ QUERIES:
 WAQ TO DISPLAY MAXIMUM SALARY OF EMPLOYEE EACH JOB WISE USING MCSQ

SELECT *FROM EMP WHERE(JOB,SAL) IN(SELECT JOB,MAX(SAL) FROM EMP GROUP BY JOB);----
MCSQ
( OR )

SELECT *FROM EMP WHERE SAL IN(SELECT JOB,MAX(SAL) FROM EMP GROUP BY JOB);-------MRSQ

 WAQ TO DISPLAY EMPLOYEE WHOSE JOB,MGR ARE SAME AS THE JOB,MGR OF THE
EMPLOYEE "SCOTT" ?
SELECT*FROM EMP WHERE (JOB,MGR) IN (SELECT JOB,MGR FROM EMP WHERE ENAME
=’SCOTT’);

ROWNUM QUERIES
 WAQ TO FETCH THE FIRST ROW EMPLOYEE DETAILS FROM EMP

TABLE BY USING ROWNUM ?

SELECT * FROM EMP WHERE ROWNUM=1;

 WAQ TO FETCH THE SECOND ROW EMPLOYEE DETAILS FROM EMP

TABLE BY USING ROWNUM ?

SELECT*FROM EMP WHERE ROWNUM<=2

MINUS

SELECT * FROM EMP WHERE ROWNUM=1;

 WAQ TO FETCH THE FIRST FIVE ROWS FROM EMP TABLE BY

USING ROWNUM

SELECT*FROM EMP WHERE ROWNUM<=5;

 WAQ TO FETCH THE FIFTH ROW EMPLOYEE DETAILS FROM EMP TABLE BY

USING ROWNUM

SELECT * FROM EMP WHERE ROWNUM<=5

MINUS

SELECT*FROM EMP WHERE ROWNUM<=4;

 WAQ TO FETCH FROM 3rd TO 9th ROW FROM EMP TABLE BY USING ROWNUM?

SELECT * FROM EMP WHERE ROWNUM<=9

MINUS

SELECT * FROM EMP WHERE ROWNUM<3 OR <=2;

 WAQ TO FETCH THE LAST TWO ROWS FROM EMP TABLE BY ROWNUM?

SELECT *FROM EMP

MINUS

SELECT *FROM EMP WHERE ROWNUM<=(SELECT COUNT(*)-2 FROM EMP);

INLINE VIEW QUERIES

USING COLUMN ALIAS NAMES IN WHERE CLAUSE CONDITION :

 WAQ TO DISPLAY EMPLOYEE WHOSE EMPLOYEE ANNUAL SALARY IS MORE THAN

25000 ?

SELECT*FROM (SELECT ENAME, SAL ,SAL*12 ANNUALSAL FROM EMP ) WHERE ANNUALSAL>25000;

USING "ORDER BY" CLAUSE IN SUBQUERY:

 WAQ TO DISPLAY FIRST FIVE HIGHEST SALARIES OF EMPLOYEE FROM EMP

TABLE BY USING ROWNUM ALONG WITH INLINE VIEW ?

SELECT * FROM (SELECT * FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=5;

 WAQ TO DISPLAY 5th HIGHEST SALARY OF EMPLOYEE FROM EMP

TABLE BY USING ROWNUM ALONG WITH INLINE VIEW ?

SELECT* FROM (SELECT *FROM EMP ORDER BY SAL DESC)WHERE ROWNUM<=5

MINUS

SELECT* FROM (SELECT *FROM EMP ORDER BY SAL DESC)WHERE ROWNUM<=4;

USING ROWNUM ALIAS NAME:

 WAQ TO DISPLAY 3rd POSITION ROW FROM EMP TABLE BY USING ROWNUM ALIAS NAME

ALONG WITH INLNE VIEW ?

SELECT *FROM (SELECT ROWNUM R ,ENAME,SAL,DEPTNO FROM EMP )WHERE R=3;

( OR )

SELECT *FROM (SELECT ROWNUM R,E.* FROM EMP E)WHERE R=3;

 WAQ TO DISPLAY 1st,3rd,5th,7th,9th ROWS FROM EMP TABLE BY USING ROWNUM

ALIAS NAME ALONG WITH INLINE VIEW ?

SELECT *FROM(SELECT ROWNUM R ,E.* FROM EMP E)WHERE R IN(1,3,5,7,9);

( OR )

SELECT *FROM (SELECT ROWNUM R,E.* FROM EMP E )WHERE MOD(R,2)=1; --- ODD ROWS

SELECT *FROM (SELECT ROWNUM R,E.* FROM EMP E )WHERE MOD(R,2)=0;---- EVEN ROWS

 WAQ TO DISPLAY FIRST ROW AND LAST ROW FROM EMP TABLE BY USING

ROWNUM ALIAS NAME ALONG WITH INLINE VIEW ?

SELECT * FROM (SELECT ROWNUM R,E.* FROM EMP E)WHERE R=1 OR R=(SELECT COUNT(*) FROM
EMP);

ANALYTICAL FUNCTIONS

 WAQ TO DISPLAY 4TH SENIOR MOST EMPLOYEE FROM EACH JOB WISE BY USING
DENSE_RANK() ALONG WITTH INLINE VIEW SUBQUERY

SELECT*FROM (SELECT E.*,DENSE_RANK() OVER (PARTITION BY JOB ORDER BY HIREDATE )R FROM

EMP E) WHERE R=4;

 WAQ TO DISPLAY 3rd HIGHEST SALARY EMPLOYEE DETAILS FROM EMP TABLE

IN EACH DEPTNO WISE BY USING DENSE_RANK() ALONG WITH INLINE VIEW ?

SELECT*FROM (SELECT ENAME,JOB,SAL ,DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL

DESC)R FROM EMP)WHERE R=3;

WAQ TO DISPLAY THE EMPLOYEE DETAILS OF ROW_NUMBER,RANK(),DENSE RANK() FROM EMP

TABLE
WITH PARTITION
SELECT*FROM
(SELECT E.*,ROW_NUMBER() OVER (PARTITION BY E.DEPTNO ORDER BY E.SAL
DESC)RNB, DENSE_RANK() OVER (PARTITION BY E.DEPTNO ORDER BY E.SAL DESC)
DNK,RANK() OVER (PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) RNK FROM EMP
E);

( OR )

SELECT * FROM ( SELECT E.*,DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY

JOB )DNK,RANK() OVER (PARTITION BY DEPTNO ORDER BY JOB ) RNK ,
ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY JOB ) RN FROM EMP E);

WITHOUT PARTITION
SELECT*FROM
(SELECT E.*,ROW_NUMBER() OVER ( ORDER BY E.SAL DESC)RNB, DENSE_RANK() OVER
(ORDER BY E.SAL DESC)
DNK,RANK() OVER ( ORDER BY E.SAL DESC) RNK FROM EMP E);

( OR )

SELECT * FROM ( SELECT E.*,DENSE_RANK() OVER ( ORDER BY SAL DESC)DNK,RANK()

OVER ( ORDER BY SAL DESC) RNK ,
ROW_NUMBER() OVER ( ORDER BY SAL DESC ) RN FROM EMP E);

CORELATED SUBQUERY
TO FIND OUT EMPLOYEE WHO ARE GETTING FIRST HIGHEST SALARY FROM

EMPLOYEE TABLE N-1=()

SELECT * FROM EMP E1 WHERE 0=(SELECT COUNT(DISTINCT SAL) FROM EMP E2 WHERE
E2.SAL>E1.SAL);

TO FIND OUT EMPLOYEE WHO ARE GETTING FOURTH HIGHEST SALARY FROM

EMPLOYEE TABLE

SELECT * FROM EMP E1 WHERE 3 = (SELECT COUNT (*) FROM EMP E2 WHERE E2.SAL>E.SAL);

WAQ TO DISPLAY TOP 3 HIGHEST SALARIES EMPLOYEE DETAILS FROM EMPLOYEE

TABLE ?

SELECT * FROM EMP E1 WHERE 3>(SELECT COUNT(*) FROM EMP E2 WHERE E2.SAL>E1.SAL);

NOTE:

-----------

1. TO FIND OUT "Nth" HIGH / LOW SALARY ----------> N-1

2. TO DISPLAY "TOP n" HIGH / LOW SALARIES -----> N>

WAQ TO DISPLAY DEPARTMENT DETAILS IN WHICH DEPARTMENT EMPLOYEE ARE WORKING?

SELECT *FROM EMP E WHERE EXISTS(SELECT DEPTNO FROM DEPT D WHERE
E.DEPTNO=D.DEPTNO);

WAQ TO DISPLAY DEPARTMENT DETAILS IN WHICH DEPARTMENT EMPLOYEE ARE NOT WORKING?

SELECT *FROM EMP E WHERE NOT EXISTS(SELECT DEPTNO FROM DEPT D WHERE
E.DEPTNO=D.DEPTNO);

SCALAR SUBQUERY
WAQ TO DISPLAY NO OF ROWS IN EMP AND DEPT TABLES AT A TIME

SELECT (SELECT COUNT(*) FROM EMP)AS EMP_TOTAL ,(SELECT COUNT(*) FROM DEPT) AS
DEPT_TOTAL FROM DUAL;

WAQ TO DISPLAY NO. OF ROWS IN EACH DEPT.

SELECT(SELECT COUNT(*) FROM EMP WHERE DEPTNO=10)AS "10",
(SELECT COUNT(*) FROM EMP WHERE DEPTNO=20)AS "20",
(SELECT COUNT(*) FROM EMP WHERE DEPTNO=30)AS "30" FROM DUAL;

WAQ TO DISPLAY TOTAL SALARY OF EMPLOYEE IN EACH DEPT. WISE

SELECT(SELECT SUM(SAL) FROM EMP WHERE DEPTNO=10)AS "10",

(SELECT SUM(SAL) FROM EMP WHERE DEPTNO=20)AS "20",
(SELECT SUM(SAL) FROM EMP WHERE DEPTNO=30)AS "30" FROM DUAL;

Write a SQL Query to fetch all the duplicate records in a table.

create table users
(
user_id int primary key,
user_name varchar(30) not null,
email varchar(50));

insert into users values

(1, 'Sumit', 'sumit@gmail.com'),
(2, 'Reshma', 'reshma@gmail.com'),
(3, 'Farhana', 'farhana@gmail.com'),
(4, 'Robin', 'robin@gmail.com'),
(5, 'Robin', 'robin@gmail.com');

select * from users;

-- Solution 1:

-- Replace ctid with rowid for Oracle, MySQL and Microsoft SQLServer
select *
from users u
where u.ctid not in (
select min(ctid) as ctid
from users
group by user_name
order by ctid);

-- Solution 2: Using window function.

select user_id, user_name, email

from (
select *,
row_number() over (partition by user_name order by user_id) as rn
from users u
order by user_id) x
where x.rn <> 1;

Write a SQL query to fetch the second last record from employee
table.
SELECT * FROM ( SELECT E.*, ROW_NUMBER() OVER (ORDER BY EMPNO DESC)RN FROM EMP E)
WHERE RN=2;

Write a SQL query to display only the details of employees who either earn the
highest salary or the lowest salary in each department from the employee table.
SELECT* FROM EMP WHERE SAL IN
(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO
UNION
 SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO) ORDER BY DEPTNO;
( OR )
select x.*
from employee e
join (select *,
max(salary) over (partition by dept_name) as max_salary,

min(salary) over (partition by dept_name) as min_salary

from employee) x
on e.emp_id = x.emp_id
and (e.salary = x.max_salary or e.salary = x.min_salary)
order by x.dept_name, x.salary;

From the doctors table, fetch the details of doctors who work in the same
hospital but in different speciality.
SELECT D1.HOSPITAL,D1.SPECIALITY,D2.HOSPITAL,D2.SPECIALITY FROM DOCTORS
D1,DOCTORS D2 WHERE D1.HOSPITAL=D2.HOSPITAL AND
D1.SPECIALITY<>D2.SPECIALITY

Now find the doctors who work in same hospital irrespective of their speciality.
select d1.name, d1.speciality,d1.hospital from doctors d1,doctors d2 WHERE
d1.hospital = d2.hospital and d1.id <> d2.id;