Michael Lam

OOP ORM AOP Kubernetes (k8s) CAP theorem DTO - Spring Boot, Don't return Entity directly. MYSQL:  樂觀鎖 — Optimistic Lock  悲觀鎖 — Pessimistic Lock Spting Boot - AOP Spring Boot -  釋控制反轉（IOC）與依賴注入（ DI ）

REGEXP '^[a-z][[0-9][a-z][.][_][-]]*@leetcode[.]com$ Write an SQL query to find the users who have  valid emails . A valid e-mail has a prefix name and a domain where: The prefix name  is a string that may contain letters (upper or lower case), digits, underscore  '_' , period  '.' , and/or dash  '-' . The prefix name  must  start with a letter. The domain  is  '@leetcode.com' . Return the result table in  any order . The query result format is in the following example. Input: Users table: +---------+-----------+-------------------------+ | user_id | name | mail | +---------+-----------+-------------------------+ | 1 | Winston | winston@leetcode.com | | 2 | Jonathan | jonathanisgreat | | 3 | Annabelle | bella-@leetcode.com | | 4 | Sally | sally.come@leetcode.com | | 5 | Marwan | quarz#2020@leetcode.com | | 6 | David | david69@gmail.com | | 7 ...

https://popsql.com/learn-sql/mysql/how-to-calculate-cumulative-sum-running-total-in-mysql Use @ Select t0.person_name FROM (SELECT turn , person_id , person_name , weight , (@csum := @csum + weight) as cumulative_weight FROM Queue ,(SELECT @csum := 0) r order by turn) as t0 WHERE cumulative_weight <= 1000 ORDER BY cumulative_weight DESC  LIMIT 1   Use Over with CTE as ( Select person_id, person_name, weight, turn, sum(weight) over (order by turn) as Total_weight from Queue ) Select person_name from CTE where total_weight<=1000 order by total_weight DESC limit 1 Use Math!! SELECT q1.person_name FROM Queue q1, Queue q2 WHERE q1.turn >= q2.turn GROUP BY q1.turn HAVING SUM (q2.weight) <= 1000 ORDER BY SUM (q2.weight) DESC LIMIT 1 Input: Queue table: +-----------+-------------+--------+------+ | person_id | person_name | weight | turn | +-----------+-------------+--------+------+ | 5 | Alice | 250 | 1 | | 4 | Bob | 175 ...

  https://rakeshnarang.medium.com/what-the-hell-is-wrong-with-spring-boot-cant-convert-boolean-to-json-correctly-solution-aa5a418dd341

Aware the pom in the project is parenting a springboot default pom that defineded all the dependencies default version Mostly stored at Home .m2 folder(hidden); < parent > < groupId >org.springframework.boot</ groupId > < artifactId >spring-boot-starter-parent</ artifactId > < version >3.0.5</ version > < relativePath /> <!-- lookup parent from repository --> </ parent >

Using MAX(xxx) two time to find the second one will return null , Using LIMIT 1 OFFSET 1 will return bland if not exist  SELECT MAX(Salary) AS SecondHighestSalary FROM Employee WHERE Salary NOT IN ( SELECT MAX (Salary) FROM Employee); Input: Employee table: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ Output: +---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+ Input: Employee table: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | +----+--------+ Output: +---------------------+ | SecondHighestSalary | +---------------------+ | null | +---------------------+

  SELECT sell_date, count (DISTINCT product)as num_sold, GROUP_CONCAT (DISTINCT product order by product) as products FROM Activities GROUP BY sell_date; Input: Activities table: +------------+------------+ | sell_date | product | +------------+------------+ | 2020-05-30 | Headphone | | 2020-06-01 | Pencil | | 2020-06-02 | Mask | | 2020-05-30 | Basketball | | 2020-06-01 | Bible | | 2020-06-02 | Mask | | 2020-05-30 | T-Shirt | +------------+------------+ Output: +------------+----------+------------------------------+ | sell_date | num_sold | products | +------------+----------+------------------------------+ | 2020-05-30 | 3 | Basketball,Headphone,T-shirt | | 2020-06-01 | 2 | Bible,Pencil | | 2020-06-02 | 1 | Mask | +------------+----------+------------------------------+ Explanation: For 2020-05-30, Sold items were (Headphone, Basketball, T-shirt), we sort the...

  Always consider to new a object to copy , and aware you are using reference of value!   Always remember pass by value or pass by reference eg. List<List<sth>> Array

CONACT , SUBSTRING , LEFT , UPPER , LOWER SELECT user_id , CONCAT ( UPPER ( LEFT ((name), 1 )) , SUBSTRING ( LOWER (name), 2 ) ) as name FROM USERS ORDER BY user_ID; Input: Users table: +---------+-------+ | user_id | name | +---------+-------+ | 1 | aLice | | 2 | bOB | +---------+-------+ Output: +---------+-------+ | user_id | name | +---------+-------+ | 1 | Alice | | 2 | Bob | +---------+-------+

union all Merge all column with same no. of column (Diff type would be OK but may loss some data)  union Like union all but will remove duplicated rows like distinct  

  SELECT x , y , z, CASE   WHEN x + y > z AND y + z > x AND x + z > y THEN 'Yes' ELSE 'No' END AS 'Triangle' FROM Triangle; Input: Triangle table: +----+----+----+ | x | y | z | +----+----+----+ | 13 | 15 | 30 | | 10 | 20 | 15 | +----+----+----+ Output: +----+----+----+----------+ | x | y | z | triangle | +----+----+----+----------+ | 13 | 15 | 30 | No | | 10 | 20 | 15 | Yes | +----+----+----+----------+

USE IFNULL return 0 if null, mod (feild ,2) even/odd SELECT a.employee_id , IFNULL(b.salary,0) as bonus FROM employees a LEFT JOIN ( SELECT * FROM Employees where mod (employee_id, 2 ) and name NOT Like 'M%' ) b ON a.employee_id = b.employee_id ORDER BY employee_id Input: Employees table: +-------------+---------+--------+ | employee_id | name | salary | +-------------+---------+--------+ | 2 | Meir | 3000 | | 3 | Michael | 3800 | | 7 | Addilyn | 7400 | | 8 | Juan | 6100 | | 9 | Kannon | 7700 | +-------------+---------+--------+ Output: +-------------+-------+ | employee_id | bonus | +-------------+-------+ | 2 | 0 | | 3 | 0 | | 7 | 7400 | | 8 | 0 | | 9 | 7700 | +-------------+-------+ Explanation: The employees with IDs 2 and 8 get 0 bonus because they have an even employee_id. The employee with ID 3 gets 0 bonus because their n...

Always using Interval x day to calculate the days INTERVAL 1 DAY Subtract 1 day from  NOW() ... WHERE DATE_FIELD >= DATE_SUB(NOW(), INTERVAL 1 DAY ) Add 1 day from  NOW() ... WHERE DATE_FIELD >= DATE_ADD(NOW(), INTERVAL 1 DAY ) Select str_to_date("Augest 31 2017", "%M %d %Y") + Interval 1 day from dual <<From dual means from a dummy table