Back to SQL Course

SQL Query to find second highest salary of Employee

select MAX(Salary) from Employee 
WHERE Salary 
NOT IN (select MAX(Salary) from Employee );

SQL Query to find Max Salary from each department.

SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID;

Write a SQL Query to print the name of distinct employee whose DOB is between 01/01/1960 to 31/12/1975

SELECT DISTINCT EmpName FROM Employees
WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975';

Write an SQL Query find number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975.

SELECT COUNT(*), sex FROM Employees 
WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975' GROUP BY sex;

Write an SQL Query to find employee whose Salary is equal or greater than 10000.

SELECT EmpName FROM Employees WHERE Salary>=10000;

Write an SQL Query to find name of employee whose name Start with 'M'

SELECT * FROM Employees WHERE EmpName like 'M%';

Find the 3rd MAX salary in the emp table.

SELECT distinct sal FROM emp e1 
WHERE 3 = (SELECT count(distinct sal) FROM emp e2 WHERE e1.sal <= e2.sal);

Find the 3rd MIN salary in the emp table.

SELECT distinct sal FROM emp e1 WHERE 3 = (SELECT count(distinct sal) FROM emp e2 WHERE e1.sal >= e2.sal);

List dept no., Dept name for all the departments in which there are no employees in the department

SELECT * FROM dept 
WHERE deptno 
NOT IN (SELECT deptno FROM emp);

How to get 3 Max salaries ?

select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;

How to get 3 Min salaries ?

select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal);

How to get nth max salaries ?

select distinct hiredate from emp a where &n = (select count(distinct sal) from emp b where a.sal >= b.sal);

How to print duplicate rows in a table?

Let us consider below table.

Name Section

abc CS1

bcd CS2

abc CS1

In the above table, we can find duplicate row using below query.

SELECT name, section FROM tbl
GROUP BY name, section
HAVING COUNT(*) > 1

Fetch only odd rows from table

SELECT E.EMPID,E.PROJECT,E.SALARY FROM(SELECT *,ROW_NUMBER() OVER(ORDER BY EMPID) AS ROWNUMBER FROM EMPLOYEESALARY E WHERE E.ROWNUMBER%2=1

Create an empty table with same structure as so other table

SELECT * INTO NEWTABLE FROM EMPLOYEEDETAILS WHERE 1=0;

Write a SQL query to find current daya-time MYSQL

SELECT NOW();

SQLSERVER

SELECT getdate();

ORACLE

SELECT SYSDATE FROM DUAL;

How to Get the names of the table in SQL?

SELECT * FROM INFORMATION_SCHEMA.TABLES

Return employee record with max salary

Select * from employee where sal=(select max(sal) from employee);

Select highest salary in employee table

SELECT MAX(SAL) FROM  EMPLOYEE;

Select 2nd highest salary in employee table

SELECT MAX(SAL) FROM EMPLOYEE WHERE SAL NOT IN (SELECT MAX(SAL) FROM EMPLOYEE);

How to find Nth highest salary from a table?

SQLSERVER:

SELECT TOP 1 SALARY
FROM(SELECT DISTINCT TOP N SALARY 
FROM EMPLOYEE ORDER BY SALARY DESC) 
ORDER BY SALARY ASC;

MYSQL:

SELECT SALARY FROM EMPLOYEE ORDER BY SALARY DESC LIMIT N-1,1;
WITHOUT USING LIMIT and TOP
SELECT e1.SALARY FROM EMPLOYEE e1
WHERE N = (SELECT COUNT(DISTINCT(e2.SALARY))
FROM EMPLOYEE e2 WHERE e2.SALARY > e1.SALARY);

**Select range of employee based on id **

Select * from employee where employee_id between 2003 and 2008

Return employee name,highest salary and department name

SELECT e.employee_name,e.salary,d.department_name 
from Employee e 
INNER JOIN Department d 
ON (e.department_id=d.department_id) 
WHERE salary in(select max(salary) from employee);

Display the names of employees who are not working as managers.

select * from emp minus (select * from emp where empno in (select mgr from emp));
(or)
select * from emp where empno not in (select mgr from emp where mgr is not null);
(or)
select * from emp e where empno not in (select mgr from emp where e.empno=mgr)

Display those employees who are working as manager?

select * from emp where empno in(select mgr from emp);

Display the manager who is having maximum number of employees working under him?

Select mgr from emp group by mgr having count(*)=(select max(count(mgr)) from emp group by mgr);

Return highest salary,employee name, department name for each department

Select e.employee)name,e.salary,d.department_name from Employee e Inner Join Department d ON (e.department_id=d.department_id) where salary in (select max(salary) from Employee group by department_id);

Delete duplicate data from table only first data remains constant.

DELETE M1
From managers M1, managers M2
Where M2.Name = M1.Name AND M1.Id>M2.Id;

Drop primary key on employee table

ALTER TABLE EMPLOYEE drop CONSTRAINT EMPLOYEE_PK;

Get department wise minimum salary from employee table order by salary ascending

SELECT DEPARTMENT,min(SALARY) MINSALARY FROM EMPLOYEE GROUP BY DEPARTMENT ORDER BY MINSALARY ASC.

How to select unique records from a table?

Select DISTINCT EmployeeId, EmployeeName from Employee

Display the list of employees who have joined the company before 30th June 90 or after 31st dec 90.

select * from emp where hiredate between ‘30-jun-1990' and ‘31-dec-1990';

Write an sql query to find number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975.

SELECT COUNT(*),sex from Employees where DOB BETWEEN ‘01/01/1960' AND ‘31/12/1975' GROUP BY sex;

Display the names of employees whose name starts with alphabet S.

select ename from emp where ename like ‘S%';

Display employee names for employees whose name ends with alphabet S.

select ename from emp where ename like ‘%S';

Display the names of employees whose names have second alphabet S in their names.

select ename from emp where ename like ‘_S%';

Write a SQL query to fetch common records between two tables.

SELECT * FROM EmployeeSalary
INTERSECT
SELECT * FROM ManagerSalary

Write a SQL query to fetch records that are present in one table but not in another table.

SELECT * FROM EmployeeSalary
MINUS
SELECT * FROM ManagerSalary

Write a SQL query to fetch top n records?

In mySQL using LIMIT,

SELECT * FROM EmployeeSalary ORDER BY Salary DESC LIMIT N

In SQL server using TOP command,

SELECT TOP N * FROM EmployeeSalary ORDER BY Salary DESC

In Oracle using ROWNUM,

SELECT * FROM (SELECT * FROM EmployeeSalary ORDER BY Salary DESC)
WHERE ROWNUM <= N;

Find the factorial of a number in pl/sql

declare
-- it gives the final answer after computation
fac number :=1;

-- given number n
-- taking input from user
n number := &1;

-- start block
begin

-- start while loop
while n > 0 loop

-- multiple with n and decrease n's value
fac:=n*fac;
n:=n-1;
end loop;
-- end loop

-- print result of fac
dbms_output.put_line(fac);

-- end the begin block
end;

Count odd and even digits in a number in PL/SQL

--Odd and Even digits in a number
--in PL/SQL
DECLARE
--num variable declared
--num assign with a number
num NUMBER := 123456;
--len varible char declared
len VARCHAR2(20);
--cntvarible declared
cnt1 NUMBER(5) := 0;
cnt2 NUMBER(5) := 0;
BEGIN
--for loop go from 1 to length of the number
FOR i IN 1..Length(num)
LOOP
len := Substr(num, i, 1);
IF mod(len, 2) != 0 THEN
cnt1 := cnt1 + 1;
ELSE
cnt2:=cnt2+1;
END IF;
END LOOP;
--end loop
dbms_output.Put_line('Odd Digits: '
|| cnt1);
dbms_output.Put_line('Even Digits: '
|| cnt2);
--display result
END;
--end program

Check whether a string is palindrome or not in PL/SQL

DECLARE
-- Declared variables are s, l, t .
-- These variables are of same data type VARCHAR.
s VARCHAR2(10) := 'abccba';
l VARCHAR2(20);
t VARCHAR2(10);
BEGIN
FOR i IN REVERSE 1..Length(s) LOOP
l := Substr(s, i, 1);
-- here || are used for concatenation of string.
t := t
||''
||l;
END LOOP;
IF t = s THEN
dbms_output.Put_line(t
||''
||' is palindrome');
ELSE
dbms_output.Put_line(t
||''
||' is not palindrome');
END IF;
END;
-- Program End

Count no. of characters and words in a string in PL/SQL

DECLARE
-- Declare required variables
str VARCHAR2(40) := 'Praveen Oruganti'';
noofchars NUMBER(4) := 0;
noofwords NUMBER(4) := 1;
s CHAR;
BEGIN
FOR i IN 1..Length(str) LOOP
s := Substr(str, i, 1);
-- Count no. of characters
noofchars := noofchars + 1;
-- Count no. of words
IF s = ' ' THEN
noofwords := noofwords + 1;
END IF;
END LOOP;
dbms_output.Put_line('No. of characters:'
||noofchars);
dbms_output.Put_line('No. of words: '
||noofwords);
END;
-- Program End

Swap two numbers in PL/SQL without using temp

DECLARE
-- declare variable num1, num2
-- of datatype number
num1 NUMBER;
num2 NUMBER;
BEGIN
num1 := 1000;
num2 := 2000;
-- print result before swapping
dbms_output.Put_line('Before');
dbms_output.Put_line('num1 = '
|| num1
||' num2 = '
|| num2);
-- swapping of numbers num1 and num2
num1 := num1 + num2;
num2 := num1 - num2;
num1 := num1 - num2;
-- print result after swapping
dbms_output.Put_line('After');
dbms_output.Put_line('num1 = '
|| num1
||' num2 = '
|| num2);
END;
-- Program End

No. of vowels and consonants in a given string in PL/SQL

DECLARE
-- Here variable V is varchar datatype
-- and flag variable is number datatype
-- variable c is char datatype .
v VARCHAR2(400) := 'Praveen Oruganti'';
noofvowels NUMBER := 0;
noofconsonants NUMBER := 0;
C CHAR;
BEGIN
FOR i IN 1..Length(v) LOOP
c := Substr(v, i, 1);
-- Check if the current character is vowel
IF c IN ( 'A', 'E', 'I', 'O', 'U' )
OR c IN ( 'a', 'e', 'i', 'o', 'u' ) THEN
noofvowels := noofvowels + 1;
-- Else current character is a consonant except space
ELSE
IF c NOT IN ( ' ' ) THEN
noofconsonants := noofconsonants + 1;
END IF;
END IF;
END LOOP;
dbms_output.Put_line('No. of Vowels: '
|| noofvowels);
dbms_output.Put_line('No. of Consonants: '
|| noofconsonants);
END;
-- Program End

Sum of the first and last digit of a number in PL/SQL

DECLARE
-- declare variables are A, B, C and S
-- these are same datatype integer
a INTEGER := 14598;
b INTEGER := 0;
C INTEGER := 0;
s INTEGER;
BEGIN
IF a > 9 THEN
c := Substr(a, 1, 1);
b := Substr(a, Length(a), 1);
s := b + c;
ELSE
s := a;
END IF;
dbms_output.Put_line('Sum of the first and last digit is '
||s);
END;
-- Program End