Leetcode|Leetcode | SQL sql|SQL

- Problems
  - - Basic
    - JOIN
    - Rank
- Basic knowledge
  - - Links
    - SELECT - extracts data from a database
    - UPDATE - updates data in a database
    - DELETE - deletes data from a database
    - INSERT INTO - inserts new data into a database
    - ALIASE - give a table or a column in a table a temporary name
    - JOIN - combine rows from two or more tables based on a related column between them
    - Operators
    - Comments
    - DATABASE
    - TABLE

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)

【Leetcode|Leetcode | SQL】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

SQL Tutorial
SQLCourse
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 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_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;