JOIN
INNER JOIN: Returns all rows when there is at least one match in BOTH tables
LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
FULL JOIN: Return all rows when there is a match in ONE of the tables
Return nth highest salary from table
+—-+——–+
| Id | Salary |
+—-+——–+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+—-+——–+
Select max(Salary) from Employee emp1
where (N-1)=(select count(DISTINCT(emp2.Salary)) from Employee emp2
where emp2.Salary>emp1.Salary)
Duplicate Email
SELECT Email FROM Person GROUP BY Email HAVING COUNT(*) > 1;
select distinct(p.Email) from Person p, Person q where p.Id!=q.Id and p.Email=q.Email;
Highest Salary In Department
select d.Name Department, e.Name Employee, s.Salary from (
select MAX(e.Salary) Salary, e.DepartmentId from Employee e, Department d where e.DepartmentId=d.Id group by e.DepartmentId
) s, Employee e, Department d where s.Salary=e.Salary and e.DepartmentId=d.Id and e.DepartmentId=s.DepartmentId;
Top N Salary in Depmartemnt
Select d1.Name as Department, e1.Name as Employee, e1.Salary
From Employee e1 ,
Department d1
Where d1.Id=e1.DepartmentId
And (select count(Distinct(Salary)) From Employee e2 where e2.Salary>e1.Salary and e2.DepartmentId=e1.DepartmentId)<N
order by d1.Name ASC, e1.Salary DESC
Delete duplicate Emails
DELETE FROM p1 USING Person p1 INNER JOIN Person p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id;
TO_DAYS — date conversion
TO_DAYS(today)=TO_DAYS(yesterday)+1
dev4future 