An SQL interview with answers

There is one table,

Wages table
-------------------------------------------
Emp_id | basic salary | seniority salary |
-------------------------------------------
1 | 1.00 | 1.00 |
-------------------------------------------
2 | 1.00 | 2.00 |
-------------------------------------------
3 | 1.00 | 3.00 |
-------------------------------------------
4 | 1.00 | 4.00 |
-------------------------------------------
.........

You can use the "one-sentence combination query" command in the preceding table to query the wage statistical table. The format of the content to be retrieved is as follows:

salary
emp_id | basic salary | seniority salary | Total | ranking
salary
1 | 1.00 | 1.00 | 2.00 | x
salary
2 | 1.00 | 2.00 | 3.00 | Y
--------------------------------------------------------------------
3 | 1.00 | 3.00 | 4.00 | ..
------------------------------------------------------------------
4 | 1.00 | 4.00 | 5.00 | ..
------------------------------------------------------------------

answer: copy Code the code is as follows: begin Tran
Create Table wages (emp_id bigint not null primary key, basic salary money, seniority salary money)
go
insert into wages (emp_id, basic salary, length of service salary) values (1, 1.00, 1.00)
insert into wages (emp_id, basic salary, length of service salary) values (2, 1.00, 2.00)
insert into wages (emp_id, basic salary, seniority salary) values (3, 1.00, 3.00)
insert into wages (emp_id, basic salary, seniority salary) values (4, 1.00, 4.00)
If @ error> 0 rollback else commit Tran
select emp_id, basic salary, seniority salary, basic salary + seniority salary as total, row_number () over (order by basic salary + seniority salary) as ranking from wages order by total
-- drop table wages