Database Queries

db_queries.txt

# Nth highest salary - [Generic]
SELECT * from employee e1 where N-1 = (SELECT COUNT(DISTINCT(salary)) from employee e2 where e2.salary > e1.salary); 
# Ex: To get 2nd highest, N = 2
SELECT * from employee e1 where 1 = (SELECT COUNT(DISTINCT(salary)) from employee e2 where e2.salary > e1.salary); 

# Nth highest salary - [PostgreSQL] - Faster 
SELECT DISTINCT(salary) from employee order by salary desc limit 1 offset N-1;
# Ex: To get 2nd highest, N = 2
SELECT DISTINCT(salary) from employee order by salary desc limit 1 offset 1;

# Nth highest salary - [MySQL] - Faster
SELECT DISTINCT(salary) from employee order by salary desc limit N-1,1;
# Ex: To get 2nd highest, N = 2
SELECT DISTINCT(salary) from employee order by salary desc limit 1,1;

# Get highest salary by department
# 1st query:
SELECT e1.employeeId, 
       e1.firstName, 
       e1.salary,
       e1.department,
       e1.gender
FROM   employee e1 
WHERE  e1.salary IN (SELECT Max(e2.salary) AS salary 
                        FROM   employee e2 
                        GROUP  BY e2.department 
                        HAVING e1.department = e2.department);

# 2nd query:
SELECT a.employeeid, 
       a.firstname, 
       a.salary, 
       a.department, 
       a.gender 
FROM   employee a 
       JOIN (SELECT Max(salary) AS Highest, 
                    department 
             FROM   employee 
             GROUP  BY department) b 
         ON a.department = b.department 
            AND a.salary = b.highest;
          
# Joins Explanation:
https://towardsdatascience.com/what-is-the-difference-between-an-inner-and-an-outer-join-in-sql-5b5ec8277377

# Self join examples:
1. List employees along with their managers: http://venkatsqlinterview.blogspot.com/2011/05/self-join-with-example.html
       Table Name: Employee, Table Columns: employee_id, employee_name, manager_id
       
       SELECT E1.employee_id, 
              E1.employee_name AS manager 
       FROM   employee E1, 
              employee E2 
       WHERE  E1.manager_id = E2.employee_id 
       
2. Find a student enrolled in at least 2 courses. 
       Table Name: Study AND Columns: student_id, course_id
       
       SELECT S1.student_id 
       FROM   study S1, 
              study S2 
       WHERE  S1.student_id = S2.student_id 
              AND S1.course_id <> S2.course_id 
              
 # OrphanRemoval
 https://vladmihalcea.com/orphanremoval-jpa-hibernate/
 
 # ACID Properties:
 https://www.educative.io/edpresso/what-are-acid-properties-in-a-database?affiliate_id=5082902844932096&utm_source=google&utm_medium=cpc&utm_campaign=platform2&utm_content=ad-1-dynamic&gclid=Cj0KCQiAwMP9BRCzARIsAPWTJ_EIlXpj67qoUUj_y1-gW06Rhu5o9xkHs9jtKFPttyb8PZMiUCCbQ6gaAntgEALw_wcB
 
 # Spring @Transactional Annotation:
 https://www.marcobehler.com/guides/spring-transaction-management-transactional-in-depth#_introduction