l****c
发帖数: 782 | 1 EMPLOYEES BONUS
__________________________________________ _____________
EMPID NAME SUPERVISOR LOCATION SALARY EMPID NBONUS
------------------------------------------ -------------
34 Amy NY 110000 17 5000
17 Ben 34 TN 75000 10 2000
5 Chris 34 TN 80000 34 5000
10 Don 5 HI 100000 ...
1.列出每个城市，工资最高的。（工资基于萨拉瑞+包呢死）输出名字，地区，和个人
总工资
2. Given a NEW_SUPERVISOR table (columns: EMPID, SUPERVISOR), write an
update statement that updates the supervisor of each employee with a new
supervisor. The NEW_SUPERVISOR table is an incremental update, so employees
not listed in the table must retain their existing supervisor. |
l****c
发帖数: 782 | |
c********9
发帖数: 53 | 3 Select EMPID, NAME, LOCATION, SALARY+BONUS as HSalary from EMPLOYEES inner
join Bonus on EMPID Where HSalary=max(SALARY+BONUS) group by LOCATION; |
c********9
发帖数: 53 | 4 not sure...haven't used sql for a while. |
y***t
发帖数: 644 | 5 SELECT E.EMPID AS EMPID, E.NAME AS NAME, LOCATION, E.SALARY+B.NBONUS AS
TOTAL FROM EMPLOYEES E INNER JOIN BONUS B ON E.EMPID = B.EMPID ORDER DESC BY
TOTAL LIMIT 1 |
B*****g
发帖数: 34098 | 6 如果有人没有bonus并且在bonus table没有record就不要用inner join
【在 l****c 的大作中提到】
: EMPLOYEES BONUS
: __________________________________________ _____________
: EMPID NAME SUPERVISOR LOCATION SALARY EMPID NBONUS
: ------------------------------------------ -------------
: 34 Amy NY 110000 17 5000
: 17 Ben 34 TN 75000 10 2000
: 5 Chris 34 TN 80000 34 5000
: 10 Don 5 HI 100000 ...
: 1.列出每个城市，工资最高的。（工资基于萨拉瑞+包呢死）输出名字，地区，和个人
: 总工资
|
h***i
发帖数: 3844 | 7 没有group by 不行吧。
BY
【在 y***t 的大作中提到】
: SELECT E.EMPID AS EMPID, E.NAME AS NAME, LOCATION, E.SALARY+B.NBONUS AS
: TOTAL FROM EMPLOYEES E INNER JOIN BONUS B ON E.EMPID = B.EMPID ORDER DESC BY
: TOTAL LIMIT 1
|
g*****e
发帖数: 282 | 8 right join就行
【在 B*****g 的大作中提到】
: 如果有人没有bonus并且在bonus table没有record就不要用inner join
|
