Create a gist now

Instantly share code, notes, and snippets.

@xiongjia /.gitignore
Last active Sep 14, 2017

What would you like to do?
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