MySQL interview – Advanced Queries

MySQL Advanced Queries

Get unique email count

SELECT COUNT(DISTINCT email) as uniqueEmailCount 
FROM members;

Get duplicate email count

SELECT COUNT(duplicatedEmails.idCount) as numberOfDuplicatedEmails
FROM (
     SELECT COUNT(id) as idCount
     FROM users
     GROUP BY email
     HAVING idCount > 1) as duplicatedEmails;

 

Get duplicate emails

SELECT email, COUNT(email) as emailCount
FROM members
GROUP BY email
HAVING emailCount > 1;

 

How to update a table from another table?

 

Retreive inspectors that don’t have a job

SELECT DISTINCT(ins.name)
FROM inspector as ins
LEFT JOIN job as j ON j.inspector_id = ins.id
WHERE j.id IS NULL;

 Find the total number of jobs per day, ordered by chronological date.

SELECT 
    DATE(service_date) AS _day, 
    COUNT(id) AS numOfJobs 
FROM job 
GROUP BY 
    DATE(service_date)
ORDER BY _day

 




Subscribe To Our Newsletter
You will receive our latest post and tutorial.
Thank you for subscribing!

required
required


Leave a Reply

Your email address will not be published. Required fields are marked *