5> create table department(
6> dept_name char(20) not null,
7> emp_cnt int not null,
8> budget float,
9> date_month datetime);
10> GO
1>
2> insert into department values('Research', 5, 50000, '01.01.2002');
3> insert into department values('Research', 10, 70000, '01.02.2002');
4> insert into department values('Research', 5, 65000, '01.07.2002');
5> insert into department values('Accounting', 5, 10000, '01.07.2002');
6> insert into department values('Accounting', 10, 40000, '01.02.2002');
7> insert into department values('Accounting', 6, 30000, '01.01.2002');
8> insert into department values('Accounting', 6, 40000, '01.02.2003');
9> insert into department values('Marketing', 6, 10000, '01.01.2003');
10> insert into department values('Marketing', 10, 40000, '01.02.2003');
11> insert into department values('Marketing', 3, 30000, '01.07.2003');
12> insert into department values('Marketing', 5, 40000, '01.01.2003');
13> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT RANK() OVER(ORDER BY budget DESC) AS rank_budget,dept_name, emp_cnt, budget
3> FROM department
4> WHERE budget <= 50000;
5> GO
rank_budget dept_name emp_cnt budget
-------------------- -------------------- ----------- ------------------------
1 Research 5 50000
2 Accounting 10 40000
2 Accounting 6 40000
2 Marketing 10 40000
2 Marketing 5 40000
6 Marketing 3 30000
6 Accounting 6 30000
8 Accounting 5 10000
8 Marketing 6 10000
(9 rows affected)
1> drop table department;
2> GO
1>
2>
3>