Solving SQL Problems

1. Delete Duplicate Emails

SQL CODE

CREATE DATABASE sql_practice_DB;

USE sql_practice_DB;

DROP TABLE IF EXISTS `Person`;

CREATE TABLE `Person`(
  Id INT NOT NULL AUTO_INCREMENT,
  Email VARCHAR(255),
  PRIMARY KEY (Id)
)ENGINE InnoDB CHARSET UTF8;

INSERT INTO `Person` VALUES (1, "john@example.com"), (2, "bob@example.com"), (3, "john@example.com");


SELECT * FROM Person p INNER JOIN Person p2 ON  p.Email = p2.Email AND  p.Id > p2.Id;

DELETE p FROM Person p INNER JOIN Person p2 ON  p.Email = p2.Email AND  p.Id > p2.Id;
      

2. Select Second Highest Salary

Fails in null case where there are two max values that are equal!

SQL CODE

CREATE DATABASE sql_practice_DB;

USE sql_practice_DB;

DROP TABLE IF EXISTS `Employee`;

CREATE TABLE `Employee`(
  Id INT NOT NULL AUTO_INCREMENT,
  Salary INT,
  PRIMARY KEY (Id)
)ENGINE InnoDB CHARSET UTF8;


INSERT INTO `Employee` VALUES (1, 100), (2, 200), (3, 300);
INSERT INTO `Employee` VALUE (4, 400);
INSERT INTO `Employee` VALUES (5, 500);
INSERT INTO `Employee` VALUE (6, 600), (7, 700);

-- C1 -> fails in null case where 
-- there are two max values that are equal.
SELECT
( SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1 ) 
as SecondHighestSalary


-- C2
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee)
      

3. Select Nth Highest Salary

SQL CODE

CREATE DATABASE sql_practice_DB;

USE sql_practice_DB;

DROP TABLE IF EXISTS `Employee`;

CREATE TABLE `Employee`(
  Id INT NOT NULL AUTO_INCREMENT,
  Salary INT,
  PRIMARY KEY (Id)
)ENGINE InnoDB CHARSET UTF8;


INSERT INTO `Employee` VALUES (1, 100), (2, 200), (3, 300);
INSERT INTO `Employee` VALUE (4, 400);
INSERT INTO `Employee` VALUES (5, 500);
INSERT INTO `Employee` VALUE (6, 600), (7, 700);

SELECT * FROM Employee;


CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N -1;
RETURN (
      SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET N
  );
END
      

4. Ads Performance

SQL CODE

-- Time:  O(nlogn)
-- Space: O(n)

SELECT ad_id,
       CASE
           WHEN clicks + views = 0 THEN 0
           ELSE ROUND(100 * clicks / (clicks + views), 2)
       END ctr
FROM
  (SELECT ad_id,
          SUM(CASE
                  WHEN action ='Viewed' THEN 1
                  ELSE 0
              END) views,
          SUM(CASE
                  WHEN action = 'Clicked' THEN 1
                  ELSE 0
              END) clicks
   FROM Ads
   GROUP BY ad_id) a
ORDER BY ctr DESC,
         ad_id ASC
      

5. Find Customers Who Never Order

Create tables C1: We can output customers whose id’s are not listed in the CustomerId of table Orders: C2: Also, we can left join Customers table with Orders table. Then output customers whose order id is null:

SQL CODE

USE sql_practice_DB;

DROP TABLE IF EXISTS `Customers`;

CREATE TABLE `Customers`(
  Id INT NOT NULL AUTO_INCREMENT,
  Name VARCHAR(100),
  PRIMARY KEY (Id)
)ENGINE InnoDB CHARSET UTF8;

DROP TABLE IF EXISTS `Orders`;

CREATE TABLE `Orders`(
  Id INT NOT NULL AUTO_INCREMENT,
  CustomerId INT NOT NULL,
  PRIMARY KEY (Id)
)ENGINE InnoDB CHARSET UTF8;



INSERT INTO `Customers` VALUES (1, "Joe"), (2, "Henry"), (3, "Sam"), (4, "Max");
INSERT INTO `Orders` VALUES (1, 3), (2, 1);

SELECT * FROM Customers;

SELECT * FROM Orders;

-- C1: We can output customers whose 
-- id’s are not listed in the CustomerId of table Orders:
SELECT  Name  AS Customers
FROM Customers AS c 
WHERE Id NOT IN (SELECT CustomerId FROM Orders);

-- C2: Also, we can left join Customers table
--  with Orders table. Then output customers whose order id is null:
SELECT Name AS Customers FROM Customers AS c
LEFT JOIN Orders AS o ON c.Id = o.CustomerId
WHERE o.Id IS NULL;

      

6. Combine Two Tables

Create tables

SQL CODE

USE sql_practice_DB;

DROP TABLE IF EXISTS `Person`;

CREATE TABLE `Person`(
  PersonId INT NOT NULL,
  LastName VARCHAR(100),
  FirstName VARCHAR(100),
  PRIMARY KEY (PersonId)
)ENGINE InnoDB CHARSET UTF8;

DROP TABLE IF EXISTS `Address`;

CREATE TABLE `Address`(
  AddressId INT NOT NULL,
  PersonId INT NOT NULL,
  City VARCHAR(100),
  State VARCHAR(100),
  PRIMARY KEY (AddressId)
)ENGINE InnoDB CHARSET UTF8;



INSERT INTO `Person` VALUES (1, "Wang", "Allen");
INSERT INTO `Address` VALUES (1, 2, "New York City", "New York");

SELECT * FROM Person;

SELECT * FROM Address;

-- Write your MySQL query statement below
SELECT FirstName, LastName, City, State
FROM Person p
LEFT JOIN Address a
ON p.PersonId = a.PersonId;