database:

Top Useful Sql Templates

In this post, we introduce the most useful SQL template statements. Find all duplicates based on a field SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN (SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT(*) > 1) This is significantly more efficient than using EXISTS Select DISTINCT on multiple columns We can select distinct on multiple columns(distinct combinations of column values.), for example, gender(male) AND age(>18). Select duplicate rows based on column SELECT TOP 1 C1.

by lek tin in "database" access_time 1-min read

Employees Earning More Than Their Managers

# SQL Schema: Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, ManagerId int) Truncate table Employee insert into Employee (Id, Name, Salary, ManagerId) values ('1', 'Joe', '70000', '3') insert into Employee (Id, Name, Salary, ManagerId) values ('2', 'Henry', '80000', '4') insert into Employee (Id, Name, Salary, ManagerId) values ('3', 'Sam', '60000', 'None') insert into Employee (Id, Name, Salary, ManagerId) values ('4', 'Max', '90000', 'None') The Employee table holds all employees including their managers.

by lek tin in "database" access_time 2-min read

Duplicate Emails

Write a SQL query to find all duplicate emails in a table named Person. +----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+ For example, your query should return the following for the above table: +---------+ | Email | +---------+ | a@b.com | +---------+ Note: All emails are in lowercase. Solution # Write your MySQL query statement below SELECT email FROM Person WHERE email IN ( SELECT email FROM Person GROUP BY email HAVING COUNT(*) > 1 ) GROUP BY email

by lek tin in "database" access_time 1-min read