Leetcode | SQL

Problems

Basic

182. Duplicate Emails

SELECT DISTINCT EmailFROM PersonGROUP BY EmailHAVING COUNT(*) > 1;

183. Customers Who Never Order

SELECT c.Name AS CustomersFROM Customers AS cWHERE 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 dWHERE e.DepartmentId = d.Id AND e.Salary = (    SELECT MAx(Salary)    FROM Employee e2    WHERE e2.DepartmentId = d.Id);

196. Delete Duplicate Emails

DELETE p1FROM Person p1, Person p2WHERE p1.Email = p2.Email AND p1.Id > p2.Id;

197. Rising Temperature

SELECT w1.IdFROM Weather w1, Weather w2WHERE 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.SateFrom Perosn P LEFT JOIN Address AON P.PersonID = A.PersonId;

181. Employees Earning More Than Their Managers

SELECT a.Name AS EmployeeFROM Employee a JOIN Employee bON a.ManagerId = b.IdWHERE a.Salary > b.Salary;

Rank

176. Second Highest Salary

SELECT Max(Salary) AS SecondHighestSalaryFROM EmployeeWHERE Salary < (SELECT Max(Salary) FROM Employee)

177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGINDECLARE 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 belowSELECT Scores.Score, COUNT(Ranking.Score) AS RankFROM Scores, (    SELECT DISTINCT Score    FROM Scores) RankingWHERE Scores.Score <= Ranking.ScoreGROUP BY Scores.Id, Scores.ScoreORDER 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 l3WHERE l1.Id=l2.Id-1 AND l2.Id=l3.Id-1 AND l1.Num=l2.Num AND l2.Num=l3.Num

-- using user-defined variablesSELECT 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    )tmp2WHERE 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 dWHERE (    SELECT COUNT(distinct(Salary))    FROM Employee    WHERE DepartmentId = e.DepartmentId AND Salary > e.Salary) in (0,1,2)AND e.DepartmentId = d.IdORDER 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_nameWHERE condition;# Search for a patternNOT LIKE or LIKE '%s%'# NOT starting with "b", "s", or "p"LIKE '[!bsp]%'# Starts with "a" & at least 3 characters in lengthLIKE 'a_%_%'# Between an inclusive rangecolumn_name BETWEEN value1 AND value2or NOT BETWEENe.g. WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;# To specify multiple possible values for a columncolumn_name IN (value1,value2,...) or IN (SELECT STATEMENT)<> or !==,...AND, OR, NOTIS NULL or IS NOT NULL# [] = optionalORDER 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 DistinctCountriesFROM (SELECT DISTINCT Country FROM Customers);

# SQL Server / MS Access Syntax:SELECT TOP number|percent column_name(s)FROM table_nameWHERE condition;e.g. SELECT TOP 3 or SELECT TOP 50 PERCENT # MySQL Syntax:SELECT column_name(s)FROM table_nameWHERE conditionLIMIT number;# Oracle Syntax:SELECT column_name(s)FROM table_nameWHERE ROWNUM <= number;

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

UPDATE - updates data in a database

UPDATE table_nameSET 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_nameWHERE 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 = nullINSERT 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_nameFROM table_name;# It requires double quotation marks or square brackets if the alias name contains spaces.[Contact Person]# Combine columnse.g. SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS AddressFROM Customers;# But in MySQLSELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS AddressFROM Customers;2.SELECT column_name(s)FROM table_name AS alias_name;e.g.SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerNameFROM Customers, OrdersWHERE Customers.CustomerName="Around the Horn" AND Customers.CustomerID=Orders.CustomerID;=>SELECT o.OrderID, o.OrderDate, c.CustomerNameFROM Customers AS c, Orders AS oWHERE 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.OrderDateFROM OrdersINNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;# Join three TablesSELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperNameFROM ((OrdersINNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);# Self JOINe.g.SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.CityFROM Customers A, Customers BWHERE A.CustomerID <> B.CustomerIDAND 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), CountryFROM CustomersGROUP BY Country;

Operators

UNION, UNION ALL with duplicate valuesHAVING, 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_nameADD column_name datatype;ALTER TABLE table_nameDROP COLUMN column_name;# ALTER/MODIFY COLUMN# SQL Server / MS Access:ALTER TABLE table_nameALTER COLUMN column_name datatype;# My SQL / Oracle (prior version 10G):ALTER TABLE table_nameMODIFY COLUMN column_name datatype;# Oracle 10G and later:ALTER TABLE table_nameMODIFY column_name datatype;

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