Casie Bao

All the lights we cannot see

•

MySQL Interview

IFNULL

IFNULL(expression, alt_value): The expression to test whether is NULL, alt_value: The value to return if expression is NULL

UNION

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

JOIN

  • A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Here are the different types of the JOINs in SQL:

  • (INNER) JOIN: Returns records that have matching values in both tables
  • LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

INNER JOIN

  • 这里的select columns指的是after join, 哪几个column会被display出来
  • 注意： 和LEFT JOIN/RIGHT JOIN 不同的是只有both table 有的才被保留下来 ``` SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

company +———+——–+————+ | com_id | name | city | +———+——–+————+ | 1 | RED | Boston | | 2 | ORANGE | New York | | 3 | YELLOW | Boston | | 4 | GREEN | Austin | +———+——–+————+ order +———-+————+———+———-+——–+ | order_id | order_date | com_id | sales_id | amount | +———-+————+———+———-+——–+ | 1 | 1/1/2014 | 3 | 4 | 100000 | | 2 | 2/1/2014 | 4 | 5 | 5000 | | 3 | 3/1/2014 | 1 | 1 | 50000 | | 4 | 4/1/2014 | 1 | 4 | 25000 | +———-+————+———+———-+——–+

company LEFT JOIN order +———+——–+————+————-+————+——–+———-+——-+ | com_id | name | city | order_id | order_date | com_id | sales_id | amount| +———+——–+————+————-+————+——–+———-+——-+ | 3 | YELLOW | Boston | 1 | 1/1/2014 | 3 | 4 | 100000| | 4 | GREEN | Austin | 2 | 2/1/2014 | 3 | 4 | 5000 | | 1 | RED | Boston | 3 | 3/1/2014 | 1 | 1 | 50000 | | 1 | RED | Boston | 4 | 4/1/2014 | 1 | 4 | 25000 | +———+——–+————+————-+————+——–+———-+——-+


### LEFT JOIN
* 注意： 1. column order

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

company +———+——–+————+ | com_id | name | city | +———+——–+————+ | 1 | RED | Boston | | 2 | ORANGE | New York | | 3 | YELLOW | Boston | | 4 | GREEN | Austin | +———+——–+————+ order +———-+————+———+———-+——–+ | order_id | order_date | com_id | sales_id | amount | +———-+————+———+———-+——–+ | 1 | 1/1/2014 | 3 | 4 | 100000 | | 2 | 2/1/2014 | 4 | 5 | 5000 | | 3 | 3/1/2014 | 1 | 1 | 50000 | | 4 | 4/1/2014 | 1 | 4 | 25000 | +———-+————+———+———-+——–+

company LEFT JOIN order +———+——–+————+————-+————+——–+———-+——-+ | com_id | name | city | order_id | order_date | com_id | sales_id | amount| +———+——–+————+————-+————+——–+———-+——-+ | 1 | RED | Boston | 3 | 3/1/2014 | 1 | 1 | 50000 | | 1 | RED | Boston | 4 | 4/1/2014 | 1 | 4 | 25000 | | 2 | ORANGE | New York | Null | NULL | NULL | NULL | NULL | | 3 | YELLOW | Boston | 1 | 1/1/2014 | 3 | 4 | 100000| | 4 | GREEN | Austin | 2 | 2/1/2014 | 3 | 4 | 5000 | +———+——–+————+————-+————+——–+———-+——-+


### RIGHT JOIN
* 注意： 1. column order

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

company +———+——–+————+ | com_id | name | city | +———+——–+————+ | 1 | RED | Boston | | 2 | ORANGE | New York | | 3 | YELLOW | Boston | | 4 | GREEN | Austin | +———+——–+————+ order +———-+————+———+———-+——–+ | order_id | order_date | com_id | sales_id | amount | +———-+————+———+———-+——–+ | 1 | 1/1/2014 | 3 | 4 | 100000 | | 2 | 2/1/2014 | 4 | 5 | 5000 | | 3 | 3/1/2014 | 1 | 1 | 50000 | | 4 | 4/1/2014 | 1 | 4 | 25000 | +———-+————+———+———-+——–+

company RIGHT JOIN order +——+——+——-+———-+————+———+———-+——–+ |com_id| name | city | order_id | order_date | com_id | sales_id | amount | +——+——+——-+———-+————+———+———-+——–+ | 3 |YELLOW| Boston| 1 | 1/1/2014 | 3 | 4 | 100000 |
| 4 | GREEN| Austin| 2 | 2/1/2014 | 4 | 5 | 5000 | | 1 | RED | Boston| 3 | 3/1/2014 | 1 | 1 | 50000 |
| 1 | RED | Boston| 4 | 4/1/2014 | 1 | 4 | 25000 |
+——-+——+——+———-+————+———+———-+——–+


### FULL JOIN

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;


### UPDATE
* Used to modify the existing records in a table

UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;


### SELECT

SELECT column1, column2, … FROM table_name; SELECT * FROM table_name;


### SELECT DISTINCT
- statement is used to return only distinct (different) values.

SELECT DISTINCT column1, column2, … FROM table_name;


### DENSE_RANK (MySQL)
* The DENSE_RANK() is a window function that assigns a rank to each row within a partition or result set with no gaps in ranking values.

DENSE_RANK() OVER ( PARTITION BY [{,...}] ORDER BY [ASC|DESC], [{,...}] )


### WHERE

SELECT column1, column2, … FROM table_name WHERE condition;

SELECT * FROM CITY WHERE Country=’Mexico’;

### AND, NOT, OR

SELECT column1, column2, … FROM table_name WHERE condition1 AND condition2 AND condition3 …;

SELECT column1, column2, … FROM table_name WHERE NOT condition1 AND condition2 AND condition3 …;


### SUBSTRING

SUBSTRING (, , )


### IN 
- case-insensitive

SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, …);


e.g., HackerRank SQL Weather Observation Station 8-11

Query the non-depulicated list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION.

SELECT DISTINCT CITY FROM STATION WHERE SUBSTR(CITY, 1, 1) IN (‘A’, ‘E’, ‘I’, ‘O’, ‘U’);

Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.

index -1 only works in MySQL

SELECT DISTINCT CITY FROM STATION WHERE SUBSTR(CITY, -1, 1) IN (‘A’, ‘E’, ‘I’, ‘O’, ‘U’);

Query the non-depulicated list of CITY names NOT starting with vowels (i.e., a, e, i, o, or u) from STATION.

SELECT DISTINCT CITY FROM STATION WHERE SUBSTR(CITY, 1, 1) NOT IN (‘A’, ‘E’, ‘I’, ‘O’, ‘U’);


### COLLATE
- You can make your query case sensitive by making use of the COLLATE keyword.

SELECT A FROM MyTbl WHERE A COLLATE Latin1_General_CS_AS = ‘ABCdef’


### ORDER BY

SELECT column1, column2, … FROM table_name ORDER BY column1, column2, … ASC|DESC;


### COUNT, SUM, AVG, FLOOR, CEIL, MAX, MIN, ROUND, POWER, SQRT
- Only used in selection block
- 'Is' is one equal sign "=", not two "=="

Query a count of the number of cities in CITY having a Population larger than 1000000.

SELECT COUNT(ID) FROM CITY WHERE POPULATION>100000;

SELECT AVG(POPULATION) FROM CITY WHERE DISTRICT=’California’;


- `ROUND(number, decimals, operation)`: e.g., `ROUND(3.333, 2)-> 3.33`
- `POWER(number, power)`
- HackerRank Weather Observation Station 19


### MOD
- only on numerical 
`MOD(value, 2) = 1`

### REPLACE in str
`SELECT REPLACE('SQL Tutorial', 'T', 'M');`: replaces all occurrences of a substring within a string, with a new substring, the search is case-insensitive.

works in mysql even when salary is an integer field, not a string field

From the documentation “In MySQL, NUMERIC is implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC.”, but CAST doesn’t accept NUMERIC as target type (unlike DECIMAL).

CAST not working in mysql here

Write a query calculating the amount of error (i.e.: correct-miscalculated average monthly salaries) from Employees db. Round to next integer

SELECT CEIL(AVG(Salary) - AVG(REPLACE(salary, ‘0’, ‘’))) FROM EMPLOYEES;


### MySQL Not Support xx<A<xx

SELECT ROUND(SUM(LAT_N),4) FROM STATION WHERE 38.7880<LAT_N AND LAT_N<137.2345;


NOT working when write `38.7880<LAT_N<137.2345`.

### LIMIT

HackerRank: Weather Observation Station 15, 16

Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than . Round your answer to decimal places.

MySQL

SELECT ROUND(LONG_W,4) FROM STATION WHERE LAT_N<137.2345 ORDER BY LAT_N DESC LIMIT 1;


HackerRank: Weather Observation Station 17

Note MIN(LAT_N) cannot be used in WHERE block

SELECT ROUND(LONG_W, 4) FROM STATION WHERE LAT_N>38.7780 ORDER BY LAT_N ASC LIMIT 1;


### CASE

CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;


### Group By
* if group by multiple columns, group by their order in the command, e.g., first by col_a, then by col_b.
* The GROUP BY clause is used often used in conjunction with an aggregate function such as SUM() and AVG()
* it reduces the number of rows returned by rolling them up and calculating the sums or averages for each group.

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name_a, column_name_b (etc) ORDER BY column_name(s);

### Having
* The HAVING clause is often used with the GROUP BY clause to filter groups based on a specified condition. If the GROUP BY clause is omitted, the HAVING clause behaves like the WHERE clause.
* In this syntax, you specify a condition in the HAVING clause. If a row, which is generated by the group by clause, causes the group_condition to evaluate to true, the query will include it in the result set.

SELECT select_list FROM table_name WHERE search_condition GROUP BY group_by_expression HAVING group_condition; ORDER BY column LIMIT int


### PARTITION AS
* The PARTITION BY clause divides the result set into partitions and changes how the window function is calculated. The PARTITION BY clause does not reduce the number of rows returned.

SELECT first_name, last_name, department_id, ROUND(AVG(salary) OVER ( PARTITION BY department_id )) avg_department_salary FROM employees;


### 177. Nth Highest Salary (Medium)
Select second highest salary 
- 括号要加对
- outside of return, has semi-colon
- offset means skip the first N-1.

Write your MySQL query statement below

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN DECLARE temp INT; SET temp = N-1; RETURN ( SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET temp ); END


### DATE
* `MONTH(<DATE>), YEAR(<DATE>), DAY(<DATE>)`
* `ISDATE(<OBJ>)`: return 1 if OBJ is a date object, 0 else

### SUBSTRING
* `SUBSTRING('SQL Tutorial', 1, 3)-> 'SQL'`: start and end inclusive, index start from 1
* MYSQL: `LEFT("SQL Tutorial", 3)`: extract :3 character from a string



========================================================
### Simple SELECT
* 注意 SELECT的顺序就是最后return table的field 的顺序
* 相似题：
    * 620. Not Boring Movies
    * 1113. Reported Posts
    * 1757. Recyclable and Low Fat Products
    * 584. Find Customer Referee
    * 586. Customer Placing the Largest Number of Orders

620

SELECT * FROM Cinema WHERE mod(id,2) = 1 AND NOT description=”boring” ORDER BY rating DESC

1113. Reported Posts

SELECT extra AS report_reason, COUNT(DISTINCT post_id) AS report_count FROM Actions WHERE action_date=”2019-07-04” AND extra IS NOT NULL AND action=”report” GROUP BY extra

1757

SELECT product_id FROM Products WHERE low_fats =’Y’ AND recyclable=’Y’

584

SELECT name FROM customer WHERE NOT referee_id=’2’ OR referee_id IS NULL

586

SELECT customer_number FROM Orders GROUP BY customer_number ORDER BY COUNT(customer_number) DESC LIMIT 1


### CASE SELECT
* `END AS`, 如果是update已有column value, use `<table>.<field>`
* similar questions: 
    * 610. Triangle Judgement
    * 627. Swap Salary
    * 626. Exchange Seats

610

SELECT *, CASE WHEN x+y>z AND x+z>y AND y+z>x THEN ‘Yes’ ELSE ‘No’ END AS ‘triangle’ FROM triangle

627, 别忘了CASE sex

UPDATE Salary SET sex= CASE WHEN sex=”m” THEN ‘f’ ELSE ‘m’ END;

626

SELECT CASE WHEN MOD(seat.id, 2)=1 AND seat.id=(SELECT COUNT(*) FROM seat) THEN seat.id WHEN MOD(seat.id, 2)=0 THEN seat.id - 1 ELSE seat.id+1 END AS id, student FROM seat ORDER BY id


### Multiple Tables Select
* 类似题： 
    * 181. Employees Earning More Than Their Managers
    * 197. Rising Temperature
    * 196. Delete Duplicate Emails
    * 601. Human Traffic of Stadium (Hard)
    * 180. Consecutive Numbers (Medium): 和601一样
    * 612. Shorest Distance in a Plane
    * 579. Find Cumulative Salary of an Employee (Hard)
- Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.
-

SELECT DISTINCT t1.* FROM Stadium t1, Stadium t2, Stadium t3 table look like as below id date people id date people id date people 2 2017-01-02 109 2 2017-01-02 109 2 2017-01-02 109 3 2017-01-03 150 2 2017-01-02 109 2 2017-01-02 109 5 2017-01-05 145 2 2017-01-02 109 2 2017-01-02 109 6 2017-01-06 1455 2 2017-01-02 109 2 2017-01-02 109 7 2017-01-07 199 2 2017-01-02 109 2 2017-01-02 109 8 2017-01-08 188 2 2017-01-02 109 2 2017-01-02 109 2 2017-01-02 109 3 2017-01-03 150 2 2017-01-02 109 3 2017-01-03 150 3 2017-01-03 150 2 2017-01-02 109 5 2017-01-05 145 3 2017-01-03 150 2 2017-01-02 109 6 2017-01-06 1455 3 2017-01-03 150 2 2017-01-02 109 7 2017-01-07 199 3 2017-01-03 150 2 2017-01-02 109 8 2017-01-08 188 3 2017-01-03 150 2 2017-01-02 109

181.

SELECT a.Name AS ‘Employee’ FROM Employee AS a, Employee AS b WHERE a.ManagerId = b.Id AND a.Salary > b.Salary

197

SELECT DISTINCT p1.id FROM Weather AS p1, Weather AS p2 WHERE DATEDIFF(p1.recordDate, p2.recordDate)=1 AND p1.Temperature > p2.Temperature

196

DELETE p1 FROM Person AS p1, Person AS p2 WHERE p1.Id > p2.Id AND p1.Email = p2.Email

601

SELECT DISTINCT s1.* FROM Stadium as s1, Stadium as s2, Stadium as s3 WHERE s1.people>=100 AND s2.people>=100 AND s3.people>=100 AND ((s1.id+1=s2.id AND s1.id+2=s3.id AND s2.id+1=s3.id) OR (s1.id-1=s2.id AND s1.id+1=s3.id AND s2.id+2=s3.id) OR (s1.id-1=s2.id AND s1.id-2=s3.id AND s3.id+1=s2.id)) ORDER BY s1.id

# SELECT DISTINCT l1.Num AS ConsecutiveNums FROM Logs AS l1, Logs AS l2, Logs AS l3 WHERE ((l1.Id+1=l2.Id AND l2.Id+1=l3.Id AND l1.Id+2=l3.Id) OR (l1.Id-1=l2.Id AND l2.Id+2=l3.Id AND l1.Id+1=l3.Id) OR (l1.Id-1=l2.Id AND l2.Id+1=l3.Id AND l1.Id-2=l3.Id)) AND l1.Num=l2.Num AND l2.Num=l3.Num AND l1.Num=l3.Num

612

SELECT MIN(ROUND(SQRT((POW(p1.x-p2.x, 2)+POW(p1.y-p2.y, 2))), 2)) AS shortest FROM point_2d as p1, point_2d as p2 where (p1.x, p1.y) != (p2.x, p2.y)

579

SELECT e1.Id, MAX(e2.Month) as Month, SUM(e2.Salary) AS Salary FROM Employee e1, Employee e2 WHERE e1.Id=e2.Id AND e2.Month BETWEEN (e1.Month-3) AND (e1.Month-1) GROUP BY e1.Id, e1.Month ORDER BY Id ASC, Month DESC


### 1741. Find Total Time Spent by Each Employee
* 相似题： 
    * 1050. Actors and Directors Who Cooperated At Least Three Times： use HAVING
    * 619. Biggest Single Number: 注意如果这边用limit， 那么没有符合的num就会出`[]`, 但是不用LIMIT, 没有符合的就会出`NULL`

1741

SELECT event_day AS “day”, emp_id, SUM(out_time-in_time) AS ‘total_time’ FROM Employees GROUP BY event_day, emp_id

1050

SELECT actor_id, director_id FROM ActorDirector GROUP BY actor_id, director_id HAVING COUNT(director_id)>=3

619

SELECT MAX(num) AS num FROM ( SELECT num FROM my_numbers GROUP BY num HAVING COUNT(*)=1 ) AS T


### 1303. Find the Team Size

SELECT employee_id, COUNT(team_id) OVER (PARTITION BY team_id) AS team_size FROM Employee



### 复杂的FROM
* 只有FROM里面可以做复杂的table结合， `COUNT(x)`: count x frequency
* join derived table must have its name, so must use `AS`
* 相似题： 
    * 577. Employee Bonus
    * 183. Customers Who Never Order
    * 574. Winning Candidate (Medium)
    * 570. Managers with at Least 5 Direct Reports (Medium): 注意这边不能用RIGHT JOIN, 因为如果T为空，那么RIGHT JOIN会return null, where JOIN (INNER) Returns records that have matching values in both tables

183

SELECT Name AS “Customers” FROM Customers WHERE Id NOT IN (SELECT CustomerId FROM Orders)

577

SELECT Employee.name, Bonus.bonus FROM ( Employee LEFT JOIN Bonus ON Employee.empId = Bonus.empId) WHERE bonus < 1000 OR bonus IS NULL

574

SELECT Name FROM (
Candidate RIGHT JOIN ( SELECT CandidateId, COUNT(CandidateId) AS counts FROM Vote GROUP BY CandidateId ORDER BY counts DESC LIMIT 1 ) AS TotalVotes ON Candidate.id=TotalVotes.CandidateId )

570

SELECT Name FROM( Employee JOIN (SELECT ManagerId FROM Employee GROUP BY ManagerId HAVING COUNT(ManagerId) >= 5) AS T ON Employee.Id = T.ManagerId)



### 复杂的SELECT
* 相似题： 
    * 178. Rank Scores
        * The rank number in fact is counting how many records >= it

SELECT Score, (SELECT COUNT(DISTINCT Score) FROM Scores AS s2 WHERE s2.Score>=s1.Score) AS Rank FROM Scores AS s1 ORDER BY Rank ASC


### JOIN
* JOIN 是不能有ambiguous field， 如果两个table 都有这个field, 要确定是来自哪个table
* 相似题： 
    * 580. Count Student Number in Departments (Medium): 思考这边为什么是RIGHT JOIN而不是LEFT JOIN
    * 607. Sales Person. (Easy, 有意义的一题）Output all the names in the table salesperson, who didn’t have sales to company 'RED'. 难点就是利用JOIN使得company name!='RED'的人的company name那一个column为NULL。
    * 614. Second Degree Follower. (Medium, 有意思的一题)
    * 615. Average Salary: Department VS Company (Hard, 有意思的一题！)

580

SELECT dept_name, COUNT(DISTINCT student_id) AS student_number FROM (SELECT dept_name, student_id FROM student RIGHT JOIN department ON student.dept_id=department.dept_id) AS T GROUP BY T.dept_name ORDER BY student_number DESC, dept_name

607

SELECT name FROM ( salesperson LEFT JOIN (SELECT name AS companyName, sales_id FROM company JOIN orders ON company.com_id=orders.com_id and company.name=’RED’) AS T ON salesperson.sales_id=T.sales_id ) WHERE companyName IS NULL

614

SELECT * FROM ( SELECT follower, COUNT(DISTINCT followee) AS num FROM (SELECT f1.follower, f2.follower AS followee FROM follow AS f1 LEFT JOIN follow AS f2 ON f1.follower=f2.followee) AS f GROUP BY follower ) AS ff WHERE num > 0

615

SELECT pay_month, department_id, CASE WHEN ROUND(AVG(amount),4)>ROUND(T.AVG_THIS_MONTH,4) THEN ‘higher’ WHEN ROUND(AVG(amount),4)<ROUND(T.AVG_THIS_MONTH,4) THEN ‘lower’ ELSE ‘same’ END AS comparison FROM (SELECT * FROM ( SELECT Salary.employee_id, amount, LEFT(pay_date,7) AS pay_month, department_id FROM Salary LEFT JOIN employee ON Salary.employee_id=employee.employee_id
) AS T1 LEFT JOIN (SELECT LEFT(pay_date,7) AS Month, AVG(amount) AS AVG_THIS_MONTH FROM salary GROUP BY LEFT(pay_date,7)) AS T2 ON T1.pay_month=T2.Month ) AS T GROUP BY pay_month, department_id ORDER BY pay_month DESC, department_id

HackerRank Contest LeaderBoard

SET sql_mode = ‘’; SELECT T.hacker_id, T.name, SUM(T.maxscore) AS total_score FROM (SELECT Submissions.hacker_id, Submissions.challenge_id, Hackers.name, MAX(Submissions.score) AS maxscore FROM Submissions LEFT JOIN Hackers ON Submissions.hacker_id=Hackers.hacker_id GROUP BY Submissions.hacker_id, Submissions.challenge_id) AS T GROUP BY T.hacker_id HAVING SUM(T.maxscore)>0 ORDER BY total_score DESC, T.hacker_id

HackerRank Placements

SELECT name FROM Friends fri LEFT JOIN Packages p1 ON fri.FRIEND_ID=p1.ID LEFT JOIN Students stu ON fri.ID=stu.ID LEFT JOIN Packages p2 ON fri.ID=p2.ID WHERE p2.Salary<p1.Salary ORDER BY p1.Salary

HackerRank Interviews

SET sql_mode = ‘’; SELECT con.contest_id, con.hacker_id, con.name, SUM(total_submissions), SUM(total_accepted_submissions), SUM(total_views), SUM(total_unique_views) FROM Challenges chall JOIN Colleges col ON col.college_id=chall.college_id JOIN Contests con ON con.contest_id=col.contest_id LEFT JOIN (SELECT challenge_id, SUM(total_submissions) AS total_submissions, SUM(total_accepted_submissions) AS total_accepted_submissions FROM Submission_Stats GROUP BY challenge_id) AS sub ON sub.challenge_id=chall.challenge_id LEFT JOIN (SELECT challenge_id, SUM(total_views) AS total_views, SUM(total_unique_views) AS total_unique_views FROM View_Stats GROUP BY challenge_id)AS vie ON vie.challenge_id=chall.challenge_id GROUP BY con.contest_id, con.hacker_id, con.name HAVING SUM(total_submissions)>0 AND SUM(total_accepted_submissions)>0 AND SUM(total_views)>0 AND SUM(total_unique_views)>0 ORDER BY con.contest_id


### IF NULL  case
* 相似题： 
    * 597. Friend Requests I: Overall Acceptance Rate (Easy)

SELECT IFNULL(ROUND (COUNT(DISTINCT requester_id, accepter_id)/COUNT(DISTINCT sender_id, send_to_id), 2), 0) AS accept_rate FROM FriendRequest, RequestAccepted



## MySQL Client vs Server
* MySql Client : The mysql-client package allows you to connect to a MySQL server. It will give you the "mysql" command-line program.
* MySql Server : The mysql-server package allows to run a MySQL server which can host multiple databases and process queries on those databases.
* MySQL : The "MySQL" package probably includes both of the above.
If you just need to connect to a remote server and run queries, install just mysql-client. If you need to host a database, install the client and server.

## MYSQL GROUP BY settings
* 解：（1）MySQL的默认设置中有“only_full_group_by”，如果使用group by，那么select的对象必须是group by的相关对象，非常受限。所以往往会修改这个设置 by `SET sql_mode = '';`
原错误

SELECT list is not in GROUP BY clause and contains nonaggregated column ‘T.name’ which is not functionally dependent on columns in GROUP BY ```

Eariler Articles

Sql

<!doctype html>SELECTSELECT column1, column2, ... FROM table_name;SELECT * FROM table_name;SELECT DISTINCTstatement is used to return only distinct (different) values.SELECT DISTINCT column1,...…

• Read More
© 2021 Casie Bao