problem with selecting correct values from database

Question

I have the following database and I'm having problems writing the correct select phrase. I am new to SQL and hope you could help me by showing me the correct way to select the desired data:

• Employee (eid, ename, salary, did, classification)
• Department (did, dname, floor, head)
• dbudget (did, byear, budget)
• Project (pid, pname, did, budget, ddate)
• Onproject (pid, eid, fdate)

Explanation regarding the database:

• Department.did = the department id of the project (what are they working on)
• dbudget.byear = the year of the budget

My assignment:

1. Show the name and salary of the worker with classification 4 who earns the most.

   My query:

   select ename ,salary from Employee where classification='4' 
   

   I don't know how to select the who earns the most....

2. Show the department id (did) and its name (dname) that have only two employees with classification 3

   My query:

   select did,dname from Department where classification='3'
   

   I don't know how to select so that it will show only those who have two employees with classification

3. Show department id (did), first year, and the year after it of departments that in two consequentive years their salary was less than 700000 (hard)

   I don't know how to create a query that will represent the first year and the year after it.

Answer 1

1)

select TOP (1) ename,salary from Employee where classification='4'
order by salary desc

2)

 select did,dname from Department
 where exists
 (select 1  from Employee 
 group by did,classification
 having count(*) = 2
 and classification = '3' and Department.did = Employee.did)

3)

 with cte as(
 select did,byear,budget,LAG(byear) OVER (partition by did order by budget) as preceeding, 
 LEAD(byear) OVER (partition by did order by budget) as next from budget
 where budget < 70000 
 )
 select distinct did,coalesce(preceeding,byear) as FirstYear,coalesce(next,byear) as NextYear from cte where cte.byear = preceeding + 1 or cte.byear = next - 1

Hope this helps!