8/4 每日一題(MYSQL 查詢獎金少於1千的員工)

 Table: Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| empId       | int     |
| name        | varchar |
| supervisor  | int     |
| salary      | int     |
+-------------+---------+
empId is the primary key column for this table.
Each row of this table indicates the name and the ID of an employee in addition to their salary and the id of their manager.

 

Table: Bonus

+-------------+------+
| Column Name | Type |
+-------------+------+
| empId       | int  |
| bonus       | int  |
+-------------+------+
empId is the primary key column for this table.
empId is a foreign key to empId from the Employee table.
Each row of this table contains the id of an employee and their respective bonus.

 

Write an SQL query to report the name and bonus amount of each employee with a bonus less than 1000.

Return the result table in any order.

The query result format is in the following example.

--------------

SELECT Employee.name AS name, Bonus.bonus AS bonus  

#將輸出欄位分別改名成 name以及 bonus

FROM Employee  

           

LEFT JOIN Bonus   

#使用左連接,會查出包含沒有獎金的員工

ON Employee.empid = Bonus.empId  

#關聯資料

WHERE Bonus.bonus < 1000 OR Bonus.bonus IS NULL;

#篩選條件設定 獎金小於一千 或是 沒有獎金

---------------參考答案

select e.name, b.bonus
from  employee e
left Join Bonus b 
on e.empId = b.empId
where bonus is null or bonus <1000