Leetcode | SQL

Problems

Basic

182. Duplicate Emails

SELECT DISTINCT Email
FROM Person
GROUP BY Email
HAVING COUNT(*) > 1;

183. Customers Who Never Order

SELECT c.Name AS Customers
FROM Customers AS c
WHERE c.Id NOT IN(
    SELECT CustomerId
    FROM Orders);

184. Department Highest Salary

SELECT d.Name AS Department, e.Name AS Employee, e.Salary 
FROM Employee e, Department d
WHERE e.DepartmentId = d.Id 
AND e.Salary = (
    SELECT MAx(Salary)
    FROM Employee e2
    WHERE e2.DepartmentId = d.Id
);

196. Delete Duplicate Emails

DELETE p1
FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id;

197. Rising Temperature

SELECT w1.Id
FROM Weather w1, Weather w2
WHERE TO_DAYS(w1.Date) = TO_DAYS(w2.Date)+1 AND w1.Temperature > w2.Temperature;

JOIN

175. Combine Two Tables

SELECT p.FirstName, P.LastName, A.City, A.Sate
From Perosn P LEFT JOIN Address A
ON P.PersonID = A.PersonId;

181. Employees Earning More Than Their Managers

SELECT a.Name AS Employee
FROM Employee a JOIN Employee b
ON a.ManagerId = b.Id
WHERE a.Salary > b.Salary;

Rank

176. Second Highest Salary

SELECT Max(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT Max(Salary) FROM Employee)

177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT Salary 
      FROM Employee 
      ORDER BY Salary DESC 
      LIMIT M, 1
  );
END

178. Rank Scores

# Write your MySQL query statement below
SELECT Scores.Score, COUNT(Ranking.Score) AS Rank
FROM Scores, (
    SELECT DISTINCT Score
    FROM Scores) Ranking
WHERE Scores.Score <= Ranking.Score
GROUP BY Scores.Id, Scores.Score
ORDER BY Scores.Score DESC;

-- If only group by Score, same score will be combined

180. Consecutive Numbers

SELECT DISTINCT l1.Num as ConsecutiveNums 
FROM Logs l1, Logs l2, Logs l3
WHERE l1.Id=l2.Id-1 AND l2.Id=l3.Id-1 AND l1.Num=l2.Num AND l2.Num=l3.Num

-- using user-defined variables
SELECT DISTINCT Num as ConsecutiveNums 
FROM(
    SELECT Num,
        @count := if (@prev = Num, @count+1, 1) count,
        @prev := Num prev
    FROM Logs, (
        SELECT @count:=0,
            @prev:=(SELECT Num FROM Logs LIMIT 1)
        )tmp1
    )tmp2
WHERE tmp2.count>=3;

185. Department Top Three Salaries

SELECT d.Name AS Department, e.Name AS Employee, e.Salary AS Salary 
FROM Employee e, Department d
WHERE (
    SELECT COUNT(distinct(Salary))
    FROM Employee
    WHERE DepartmentId = e.DepartmentId AND Salary > e.Salary
) in (0,1,2)
AND e.DepartmentId = d.Id
ORDER BY e.DepartmentId, E.Salary DESC;

Basic knowledge

Links

1. SQL Tutorial
2. SQLCourse
3. SQLCourses

SELECT - extracts data from a database

SELECT column1, column2, ...
FROM table_name
WHERE condition;

# Search for a pattern
NOT LIKE or LIKE '%s%'
# NOT starting with "b", "s", or "p"
LIKE '[!bsp]%'
# Starts with "a" & at least 3 characters in length
LIKE 'a_%_%'

# Between an inclusive range
column_name BETWEEN value1 AND value2
or NOT BETWEEN
e.g. WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

# To specify multiple possible values for a column
column_name IN (value1,value2,...) 
or IN (SELECT STATEMENT)

<> or !=
=,...

AND, OR, NOT
IS NULL or IS NOT NULL

# [] = optional
ORDER BY column1, column2, ... [ASC|DESC];

# return only distinct (different) values.
SELECT DISTINCT Country FROM Customers;

!NOTE: COUNT(DISTINCT column_name) is not supported in Microsoft Access databases.
SELECT COUNT(DISTINCT Country) FROM Customers;

SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);

# SQL Server / MS Access Syntax:
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
e.g. SELECT TOP 3 or SELECT TOP 50 PERCENT 

# MySQL Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

# Oracle Syntax:
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

MIN(), MAX(), COUNT(), AVG(), SUM()

UPDATE - updates data in a database

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

# can write column1 = column1 + 1

# If you omit the WHERE clause, ALL records will be updated!

DELETE - deletes data from a database

DELETE FROM table_name
WHERE condition;

# If you omit the WHERE clause, all records in the table will be deleted!
DELETE [*] FROM table_name;

INSERT INTO - inserts new data into a database

# If only insert in specified columns, others = null
INSERT INTO table_name [(column1, column2, column3, ...)]
VALUES (value1, value2, value3, ...);

ALIASE - give a table, or a column in a table, a temporary name

1. 
SELECT column_name AS alias_name
FROM table_name;

# It requires double quotation marks or square brackets if the alias name contains spaces.
[Contact Person]

# Combine columns
e.g. SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;
# But in MySQL
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;

2.
SELECT column_name(s)
FROM table_name AS alias_name;

e.g.
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName="Around the Horn" AND Customers.CustomerID=Orders.CustomerID;
=>
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;

JOIN - combine rows from two or more tables, based on a related column between them

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

# Join three Tables
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

# Self JOIN
e.g.
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City 
ORDER BY A.City;

-GROUP BY statement - used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

Operators

UNION, UNION ALL with duplicate values

HAVING, EXISTS, 

WHERE column_name operator ANY/ALL (SELECT column_name FROM table_name WHERE condition)

Comments

-- Single line
/*Multi-line*/

DATABASE

• CREATE DATABASE - creates a new database
• DROP DATABASE - drop an existing SQL database
• ALTER DATABASE - modifies a database

TABLE

• CREATE TABLE - creates a new table

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

• DROP TABLE - deletes a table
• ALTER TABLE - modifies a table

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

# ALTER/MODIFY COLUMN
# SQL Server / MS Access:
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
# My SQL / Oracle (prior version 10G):
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
# Oracle 10G and later:
ALTER TABLE table_name
MODIFY column_name datatype;

• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index
• …