SQL Queries

Question 1

Write SQL Querie to find 5th highest Salary.

Answer 1

SELECT DISTINCT(salary)
FROM employee
ORDER BY DESC
LIMIT n-1,1 ;

Here n is 5

LIMIT X,Y
here x tells us the OFFSET
and Y tells us the number of values which we have to extract after the OFFSET

So here it will leave 4 values from the top and extract the 1st element after it.

Question 2

Write SQL Querie to find 5th highest Salary. Without LIMIT.

Answer 2

SELECT salary
FROM employee e1
WHERE n-1 =
(SELECT COUNT(DISTINCT(salary))
FROM employee e2
WHERE e2.salary > e1.salary
);

Here n is 5

Question 3

Write SQL Querie to find 5th highest Salary.
Using DENSE RANK.

Answer 3

Solution:
select name, salary from
(select name, salary ,
DENSE_RANK() OVER (ORDER BY salary DESC) as salary_dense
from employee
) as temp
where salary_dense = 5;

DENSE RAN() important points:
- with no gaps in the ranking values.
- If same values, they receive the same rank.
- DENSE_RANK() OVER (
PARTITION BY expr1 [ {,expr2…}]
ORDER BY expr1 [ASC|DESC], [ {,expr2…}]
)
- Remember it’s not necessary to include PARTITION BY, it just depends upon when to use and when to not.

if you want you can practice over here: https://platform.stratascratch.com/coding/10049-reviews-of-categories?code_type=3

using this querrie:
select name, review_count from
(select name, review_count,
DENSE_RANK() OVER (ORDER BY review_count DESC) as salary_dense
from yelp_business
) as temp
where salary_dense = 3;

Question 4

Write SQL Querie to find 5th highest Salary.
Using NOT IN.

Answer 4

SELECT name, salary
FROM emp
WHERE salary NOT IN (
SELECT DISTINCT(salary)
FROM emp
ORDER BY salary DESC
LIMIT 4
)
ORDER BY salary DESC
LIMIT 1;

Question 5

SQL Querry to find duplicate rows in a table.

Answer 5

SELECT *, COUNT(empid) as counts
FROM emp
GROUP BY empid
HAVING counts > 1;

For Practice:

CREATE TABLE emp (
empid INT,
empfname VARCHAR(255),
dept VARCHAR(255),
salary DECIMAL(10, 2)
);

INSERT INTO emp (empid, empfname, dept, salary) VALUES
(1, ‘John’, ‘HR’, 50000),
(2, ‘Jane’, ‘HR’, 60000),
(3, ‘Alice’, ‘IT’, 70000),
(4, ‘Bob’, ‘IT’, 80000),
(5, ‘Charlie’, ‘IT’, 90000),
(6, ‘David’, ‘Sales’, 55000),
(7, ‘Ethan’, ‘Sales’, 65000),
(8, ‘Fred’, ‘Sales’, 75000),
(9, ‘George’, ‘Sales’, 85000),
(10, ‘Hannah’, ‘HR’, 95000),
(11, ‘Irene’, ‘IT’, 52000),
(12, ‘Jack’, ‘IT’, 62000),
(13, ‘Kelly’, ‘Sales’, 72000),
(14, ‘Larry’, ‘HR’, 82000),
(15, ‘Mike’, ‘Sales’, 92000),
– duplicate records
(16, ‘John’, ‘HR’, 50000),
(17, ‘Jane’, ‘HR’, 60000),
(18, ‘Alice’, ‘IT’, 70000);

SOLUTION:
SELECT empfname, dept, salary, COUNT(*) as c
FROM emp
GROUP BY empfname, dept, salary
HAVING c > 1
order by c desc ;

Question 6

Display even and odd empid

Answer 6

FOR EVEN:
SELECT * from emp
WHERE MOD(empid,2) = 0;

FOR ODD:
SELECT * from emp
WHERE MOD(empid,2) = 1;

Question 7

SELF-JOIN : Retrieve the list of employees working in the same department.

Answer 7

SELECT DISTINCT e.empid, e.empfname, e.dept
FROM emp e, emp e1
where e.dept = e1.dept
and e.empid != e1.empid;

Question 8

Wildcard character underscore ‘_’

Answer 8

SELECT * from emp where NAME LIKE ‘____a’;

here i have used four underscore
which means name LIKE TANYA

Question 9

(where
product_name LIKE ‘C%’ OR
product_name LIKE ‘B%’ OR
product_name LIKE ‘S%’ )

TELL ME SOME ALTERNATE FOR THIS CODE

Answer 9

where SUBSTRING(product_name, 1, 1) IN (‘C’, ‘B’, ‘S’)

Question 10

If you want to convert string into lowercase and then check in WHERE clause. How will you do?

Answer 10

SELECT * from emp
where LOWER(emp_name) = ‘nikhil’;

Question 11

Suppose there is a table in which two columns are there first_name and last_name. You want to CONCAT both the columns and make new column.
But there are few null values present in both first and last name column how will you deal with it.

Answer 11

SELECT
CONCAT(IFNULL(first_name, “ “), “ “ , IFNULL(last_name, “ “))
from employee;

Question 12

Difference between FLOOR and CEIL in sql.

Answer 12

SELECT FLOOR(3.14); – returns 3
SELECT CEILING(3.14); – returns 4

Question 13

Order by execution of SQL Querries:

We write SQL querries like this :
SELECT >DISTINCT > FROM > JOIN> GROUP BY > HAVING > ORDER BY> LIMIT

Answer 13

FROM > JOIN> GROUP BY > HAVING > WINDOW FUNCTION (LAG, LEAD) >SELECT >DISTINCT > ORDER BY> LIMIT