Database schema for leetcode db problems #db
*.sqlite | |
*.db | |
*.log |
-- DB Schema: | |
-- https://leetcode.com/problems/combine-two-tables/ | |
-- | |
-- > Table Schema: | |
-- Person Table ( PersonId is the primary key column for this table. ) | |
-- +-------------+---------+ | |
-- | Column Name | Type | | |
-- +-------------+---------+ | |
-- | PersonId | int | | |
-- | FirstName | varchar | | |
-- | LastName | varchar | | |
-- +-------------+---------+ | |
-- | |
-- Address Table ( AddressId is the primary key column for this table. ) | |
-- +-------------+---------+ | |
-- | Column Name | Type | | |
-- +-------------+---------+ | |
-- | AddressId | int | | |
-- | PersonId | int | | |
-- | City | varchar | | |
-- | State | varchar | | |
-- +-------------+---------+ | |
-- | |
-- > Import the schema to SQLite database: | |
-- `sqlite3 test_db.sqlite < combine-two-tables.sql` | |
-- | |
-- > My Solution for the leetcode problem: | |
-- SELECT Person.FirstName, Person.LastName, Address.City, Address.State | |
-- FROM Person | |
-- LEFT JOIN Address | |
-- ON Person.PersonId = Address.PersonId; | |
-- | |
-- Drop the test tables and create these again. | |
DROP TABLE IF EXISTS Person; | |
DROP TABLE IF EXISTS Address; | |
CREATE TABLE Person ( | |
PersonId INTEGER PRIMARY KEY AUTOINCREMENT, | |
FirstName TEXT NOT NULL, | |
LastName TEXT NOT NULL | |
); | |
CREATE TABLE Address ( | |
AddressId INTEGER PRIMARY KEY AUTOINCREMENT, | |
PersonId INTEGER NOT NULL, | |
City TEXT NOT NULL, | |
State TEXT NOT NULL, | |
FOREIGN KEY(PersonId) REFERENCES Person(PersonId) | |
); | |
-- Add test data | |
INSERT INTO Person ( FirstName, LastName ) VALUES | |
( "XiongJia", "Le" ), ( "Ming", "Xiao" ); | |
INSERT INTO Address ( PersonId, City, State ) | |
VALUES ( | |
(SELECT PersonId | |
FROM Person | |
WHERE FirstName = "XiongJia" AND LastName = "Le"), | |
"Shanghai", "N/A"); | |
-- DB Schema: | |
-- https://leetcode.com/problems/customers-who-never-order/ | |
-- | |
-- > Sample tables: | |
-- Customers table | |
-- +----+-------+ | |
-- | Id | Name | | |
-- +----+-------+ | |
-- | 1 | Joe | | |
-- | 2 | Henry | | |
-- | 3 | Sam | | |
-- | 4 | Max | | |
-- +----+-------+ | |
-- Orders | |
-- +----+------------+ | |
-- | Id | CustomerId | | |
-- +----+------------+ | |
-- | 1 | 3 | | |
-- | 2 | 1 | | |
-- +----+------------+ | |
-- | |
-- > Import the schema to SQLite database: | |
-- `sqlite3 test_db.sqlite < customers-who-never-order.sql` | |
-- | |
-- > My Solution for the leetcode problem: | |
-- SELECT Customers.Name AS Name | |
-- FROM Customers | |
-- LEFT JOIN Orders | |
-- ON Customers.Id = Orders.CustomerId WHERE Orders.CustomerId IS NULL; | |
-- | |
-- Drop tables and create these again. | |
DROP TABLE IF EXISTS Customers; | |
DROP TABLE IF EXISTS Orders; | |
CREATE TABLE Customers ( | |
Id INTEGER PRIMARY KEY AUTOINCREMENT, | |
Name TEXT NOT NULL | |
); | |
CREATE TABLE Orders ( | |
Id INTEGER PRIMARY KEY AUTOINCREMENT, | |
CustomerId INTEGER NOT NULL, | |
FOREIGN KEY(CustomerId) REFERENCES Customers(Id) | |
); | |
-- Add test data | |
INSERT INTO Customers ( Name ) VALUES | |
( "Joe" ), ( "Henry" ), ( "Sam" ), ( "Max" ); | |
INSERT INTO Orders ( CustomerId ) VALUES | |
( (SELECT Id FROM Customers WHERE Name = "Sam") ), | |
( (SELECT Id FROM Customers WHERE Name = "Joe") ); | |
-- DB Schema: | |
-- https://leetcode.com/problems/delete-duplicate-emails/ | |
-- | |
-- > Sample of Person table: | |
-- +----+------------------+ | |
-- | Id | Email | | |
-- +----+------------------+ | |
-- | 1 | john@example.com | | |
-- | 2 | bob@example.com | | |
-- | 3 | john@example.com | | |
-- +----+------------------+ | |
-- | |
-- > Import the schema to SQLite database: | |
-- `sqlite3 test_db.sqlite < delete-duplicate-emails.sql` | |
-- | |
-- > My Solution for the leetcode problem: | |
-- DELETE FROM Person WHERE Id IN | |
-- (SELECT alldata.id AS Id FROM | |
-- (SELECT Email, Min(id) AS minid FROM Person GROUP BY Email HAVING COUNT(Email) > 1) AS dup | |
-- INNER JOIN | |
-- (SELECT Email, Id FROM Person) AS alldata | |
-- ON dup.Email = alldata.Email WHERE dup.minid != alldata.id) | |
-- | |
-- Drop the Person table and create it again. | |
DROP TABLE IF EXISTS Person; | |
CREATE TABLE Person ( | |
Id INTEGER PRIMARY KEY AUTOINCREMENT, | |
Email TEXT NOT NULL | |
); | |
-- Add test data | |
INSERT INTO Person ( Email ) VALUES | |
( "john@example.com" ), ( "bob@example.com" ), | |
( "john@example.com" ), ( "john@example.com" ); | |
-- DB Schema: | |
-- https://leetcode.com/problems/duplicate-emails/ | |
-- | |
-- > Sample of Person table: | |
-- +----+---------+ | |
-- | Id | Email | | |
-- +----+---------+ | |
-- | 1 | a@b.com | | |
-- | 2 | c@d.com | | |
-- | 3 | a@b.com | | |
-- +----+---------+ | |
-- | |
-- > Import the schema to SQLite database: | |
-- `sqlite3 test_db.sqlite < duplicate-emails.sql` | |
-- | |
-- > My Solution for the leetcode problem: | |
-- SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email) > 1; | |
-- | |
-- Drop the Person table and create it again. | |
DROP TABLE IF EXISTS Person; | |
CREATE TABLE Person ( | |
Id INTEGER PRIMARY KEY AUTOINCREMENT, | |
Email TEXT NOT NULL | |
); | |
-- Add test data | |
INSERT INTO Person ( Email ) VALUES | |
( "a@b.com" ), ( "c@d.com" ), ( "a@b.com" ); | |
-- DB Schema: | |
-- https://leetcode.com/problems/Employee-earning-more-than-their-managers/ | |
-- | |
-- > Sample of Employee table: | |
-- +----+-------+--------+-----------+ | |
-- | Id | Name | Salary | ManagerId | | |
-- +----+-------+--------+-----------+ | |
-- | 1 | Joe | 70000 | 3 | | |
-- | 2 | Henry | 80000 | 4 | | |
-- | 3 | Sam | 60000 | NULL | | |
-- | 4 | Max | 90000 | NULL | | |
-- +----+-------+--------+-----------+ | |
-- | |
-- > Import the schema to SQLite database: | |
-- `sqlite3 test_db.sqlite < employees-earning.sql` | |
-- | |
-- > My Solution for the leetcode problem: | |
-- SELECT base.Name as Name FROM | |
-- (SELECT e.Name AS Name, (e.Salary - m.Salary) as diff | |
-- FROM (SELECT Name, Salary, ManagerId FROM Employee) AS e | |
-- INNER JOIN (SELECT Id, Salary, NAME FROM Employee) AS m | |
-- ON e.ManagerId = m.Id) AS base | |
-- WHERE base.diff > 0; | |
-- | |
-- Drop the Employee table and create it again | |
DROP TABLE IF EXISTS Employee; | |
CREATE TABLE Employee ( | |
Id INTEGER PRIMARY KEY AUTOINCREMENT, | |
Name TEXT NOT NULL, | |
Salary INTEGER NOT NULL, | |
ManagerId INTEGER DEFAULT NULL | |
); | |
-- Add test data | |
INSERT INTO Employee ( NAME, SALARY ) VALUES | |
("Joe", 70000), ("Henry", 80000), ("Sam", 60000), ("Max", 90000); | |
-- Update ManagerId | |
UPDATE Employee | |
SET ManagerId = (SELECT Id FROM Employee WHERE Name = 'Sam') | |
WHERE Name = 'Joe'; | |
UPDATE Employee | |
SET ManagerId = (SELECT Id FROM Employee WHERE Name = 'Max') | |
WHERE Name = 'Henry'; | |
-- DB Schema: | |
-- https://leetcode.com/problems/rising-temperature/ | |
-- | |
-- > Sample of the Weather table | |
-- +---------+------------+------------------+ | |
-- | Id(INT) | Date(DATE) | Temperature(INT) | | |
-- +---------+------------+------------------+ | |
-- | 1 | 2015-01-01 | 10 | | |
-- | 2 | 2015-01-02 | 25 | | |
-- | 3 | 2015-01-03 | 20 | | |
-- | 4 | 2015-01-04 | 30 | | |
-- +---------+------------+------------------+ | |
-- | |
-- > Import the schema to SQLite database: | |
-- `sqlite3 test_db.sqlite < rising-temperature.sql` | |
-- | |
-- > My Solution for the leetcode problem: | |
-- In this problem, we need the calculate the Date of yesterday. | |
-- For MySQL: yesterday == `DATE_SUB(Date, INTERVAL 1 DAY)` | |
-- For SQLite: yesterday == `date(Date, "-1 day")` | |
-- For Postgres: yesterday == `DATE(Date) - integer '1'` | |
-- | |
-- * The Solution for MySQL: | |
-- SELECT res.Id AS Id FROM | |
-- (SELECT day2.Id AS Id, (day2.Temperature - day1.Temperature) AS tdiff FROM | |
-- (SELECT Id, Temperature, Date from Weather) AS day1 INNER JOIN | |
-- (SELECT Id, Temperature, Date, DATE_SUB(Date, INTERVAL 1 DAY) | |
-- as yesterday from Weather) AS day2 | |
-- ON day1.Date = day2.yesterday) AS res | |
-- WHERE res.tdiff > 0; | |
-- | |
-- * The Solution for SQLite: (Only changed the DATE_SUB function) | |
-- SELECT res.Id AS Id FROM | |
-- (SELECT day2.Id AS Id, (day2.Temperature - day1.Temperature) AS tdiff FROM | |
-- (SELECT Id, Temperature, Date from Weather) AS day1 INNER JOIN | |
-- (SELECT Id, Temperature, Date, date(Date, "-1 day") as yesterday from Weather) AS day2 | |
-- ON day1.Date = day2.yesterday) AS res | |
-- WHERE res.tdiff > 0; | |
-- | |
-- Drop the table and create it again. | |
DROP TABLE IF EXISTS Weather; | |
CREATE TABLE Weather ( | |
Id INTEGER PRIMARY KEY AUTOINCREMENT, | |
Date TEXT NOT NULL, | |
Temperature INTEGER NOT NULL | |
); | |
-- Add test data | |
INSERT INTO Weather (Date, Temperature) VALUES | |
(date('2015-01-01'), 10), (date('2015-01-02'), 25), | |
(date('2015-01-03'), 20), (date('2015-01-04'), 30); | |
-- DB Schema: | |
-- https://leetcode.com/problems/second-highest-salary/ | |
-- | |
-- > Sample of the Employee table | |
-- +----+--------+ | |
-- | Id | Salary | | |
-- +----+--------+ | |
-- | 1 | 100 | | |
-- | 2 | 200 | | |
-- | 3 | 300 | | |
-- +----+--------+ | |
-- | |
-- > Import the schema to SQLite database: | |
-- `sqlite3 second-highest-salary.sqlite < second-highest-salary.sql` | |
-- | |
-- > My Solution for the leetcode problem: | |
-- NOTE: | |
-- In MySQL, SQLite, PostgreSQL: We can use the "SELECT...LIMIT n OFFSET n" | |
-- In Oracle and some databases: We should use the "ROW_NUMBER" | |
-- | |
-- * The Solution form MySQL & SQLite: | |
-- SELECT COALESCE( | |
-- (SELECT t1.Salary FROM | |
-- (SELECT Salary FROM Employee GROUP BY Salary ORDER BY Salary DESC LIMIT 2 OFFSET 1) | |
-- AS t1 LIMIT 1), | |
-- null) as Salary; | |
-- | |
-- Drop the table and create it again. | |
DROP TABLE IF EXISTS Employee; | |
CREATE TABLE Employee ( | |
Id INTEGER PRIMARY KEY AUTOINCREMENT, | |
Salary INTEGER NOT NULL | |
); | |
INSERT INTO Employee ( Salary ) VALUES | |
( 100 ), ( 200 ), ( 300 ); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment