Database schema for leetcode db problems #db

.gitignore

*.sqlite
*.db
*.log

combine-two-tables.sql

-- 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");

customers-who-never-order.sql

-- 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") );

delete-duplicate-emails.sql

-- 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" );

duplicate-emails.sql

-- 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" );

employees-earning.sql

-- 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';

rising-temperature.sql

-- 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);

second-highest-salary.sql

-- 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 );