第25個冬天

  LeetCode常見題目 LeetCode 1. Two Sum LeetCode 268. Missing Number LeetCode 283. Move Zeroes LeetCode 26. Remove Duplicates from Sorted Array LeetCode 27.Remove Element LeetCode.485. Max Consecutive Ones

Table:  Employee +--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ id is the primary key column for this table. departmentId is a foreign key of the ID from the Department table. Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.  Table:  Department +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ id is the primary key column for this table. It is guaranteed that department name is not NULL. Each row of this table indicates the ID of a department and its name.  Write an SQL query to find employees who have the highest salary in each of the departments. Return the result table in  any order . The query result format is in the f...

Table:  Employee +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ id is the primary key column for this table. Each row of this table contains information about the salary of an employee.  Write an SQL query to report the  n th  highest salary from the  Employee  table. If there is no  n th  highest salary, the query should report  null . The query result format is in the following example. Input: Employee table: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ n = 2 Output: +------------------------+ | getNthHighestSalary(2) | +------------------------+ | 200 | +------------------------+ CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS result NUMBER; BEGIN     /* Write your PL/SQL query statement below */     WITH  real_query...

Table:  Logs +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ id is the primary key for this table. id is an autoincrement column.  Write an SQL query to find all numbers that appear at least three times consecutively. Return the result table in  any order . The query result format is in the following example. Input: Logs table: +----+-----+ | id | num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+ Output: +-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+ Explanation: 1 is the only number that appears consecutively for at least three times.  SELECT DISTINCT num as ConsecutiveNums FROM (      SELECT id, num,      LAG(num) OVER(ORDER BY id) as prev_num,     LEAD(num) OVER(ORDER BY id) as next_num ...

Table:  Scores +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | score | decimal | +-------------+---------+ id is the primary key for this table. Each row of this table contains the score of a game. Score is a floating point value with two decimal places. Write an SQL query to rank the scores. The ranking should be calculated according to the following rules: The scores should be ranked from the highest to the lowest. If there is a tie between two scores, both should have the same ranking. After a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no holes between ranks. Return the result table ordered by  score  in descending order. The query result format is in the following example. Input: Scores table: +----+-------+ | id | score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+...

Table:  Employee +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ id is the primary key column for this table. Each row of this table contains information about the salary of an employee. Write an SQL query to report the second highest salary from the  Employee  table. If there is no second highest salary, the query should report  null .The query result format is in the following example. Input: Employee table: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ Output: +---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+ select salary as SecondHighestSalary from (   select salary, RANK () OVER (ORDER BY salary DESC) RANK from Employee ) where RANK=2 select MAX(salary) "SecondHighestSalary" from employee  where salary < (select max(salary...

Table:  Tree +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | p_id | int | +-------------+------+ id is the primary key column for this table. Each row of this table contains information about the id of a node and the id of its parent node in a tree. The given structure is always a valid tree.   Each node in the tree can be one of three types: "Leaf" : if the node is a leaf node. "Root" : if the node is the root of the tree. "Inner" : If the node is neither a leaf node nor a root node. Write an SQL query to report the type of each node in the tree. Return the result table in  any order . The query result format is in the following example. /* Write your PL/SQL query statement below */ select id,'Root' as type from tree t where p_id is null union select id,'Inner' as type from tree t1 where exists(select 1 from tree where p_id = t1.id ) and p_id is not null union select id,'Leaf' a...

Easy LeetCode175. Combine Two Tables LeetCode181. Employees Earning More Than Their Managers LeetCode182. Duplicate Emails LeetCode183. Customers Who Never Order LeetCode196. Delete Duplicate Emails LeetCode577. Employee Bonus LeetCode511. Game Play Analysis I LeetCode584. Find Customer Referee LeetCode595. Big Countries LeetCode596. Classes More Than 5 Students LeetCode619. Biggest Single Number Medium LeetCode176. Second Highest Salary LeetCode177.Nth Highest Salary LeetCode178. Rank Scores LeetCode180. Consecutive Numbers LeetCode184. Department Highest Salary LeetCode608. Tree Node

Table:  MyNumbers +-------------+------+ | Column Name | Type | +-------------+------+ | num | int | +-------------+------+ There is no primary key for this table. It may contain duplicates. Each row of this table contains an integer.  A  single number  is a number that appeared only once in the  MyNumbers  table. Write an SQL query to report the largest  single number . If there is no  single number , report  null . The query result format is in the following example. Input: MyNumbers table: +-----+ | num | +-----+ | 8 | | 8 | | 3 | | 3 | | 1 | | 4 | | 5 | | 6 | +-----+ Output: +-----+ | num | +-----+ | 6 | +-----+ Explanation: The single numbers are 1, 4, 5, and 6. Since 6 is the largest single number, we return it. Input: MyNumbers table: +-----+ | num | +-----+ | 8 | | 8 | | 7 | | 7 | | 3 | | 3 | | 3 | +-----+ Output: +------+ | num | +------+ | null | +------+ Explanation: There are no ...

Table:  Courses +-------------+---------+ | Column Name | Type | +-------------+---------+ | student | varchar | | class | varchar | +-------------+---------+ (student, class) is the primary key column for this table. Each row of this table indicates the name of a student and the class in which they are enrolled.   Write an SQL query to report all the classes that have  at least five students . Return the result table in  any order . The query result format is in the following example. Input: Courses table: +---------+----------+ | student | class | +---------+----------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | +---------+----------+ Output: +---------+ | class | +---------+ | Math | +---------+ Explanation: - Math has 6 students, so we include it. - English has 1 student, so ...

Table:  World +-------------+---------+ | Column Name | Type | +-------------+---------+ | name | varchar | | continent | varchar | | area | int | | population | int | | gdp | bigint | +-------------+---------+ name is the primary key column for this table. Each row of this table gives information about the name of a country, the continent to which it belongs, its area, the population, and its GDP value. A country is  big  if: it has an area of at least three million (i.e.,  3000000 km 2 ), or it has a population of at least twenty-five million (i.e.,  25000000 ). Write an SQL query to report the name, population, and area of the  big countries . Return the result table in  any order . The query result format is in the following example. Input: World table: +-------------+-----------+---------+------------+--------------+ | name | continent | area | population | gdp | +-------------+--...