Comments (48)

  1. Sorry to say that those questions were utter blunder. They were not properly framed. And the sixth query is totally wrong.

  2. E. Department_id = d. Department_id
    Why not e. Department_id =d. Department_name.. Or both are possible?

  3. These are all basic questions. If I was a recruiter these would be my questions.

    1 name at least 2 ways to avoid using a cursor. Bonus points if you can name more.
    2 what is the difference between in and exists.
    3 when should you use triggers.
    4 how to analyse performance issues. Explain your steps.
    5 what is an execution plan. and what is the estimated execution plan

  4. Your third question is ok, but I think you should have spoken about the order in which sql server will apply the predicate. From and Where are applied first, then after that the select is performed. This will stop people getting confused about selecting something that looks like it's being filtered out…

  5. How to write Query to find 5th highest salary, name, and Department name in the employee and Department table?

  6. Don't know if it is worth anything but I am looking at an associates in database. I don't want to take any of the hardware classes like cisco and networking. Are those classes needed?

  7. To find nth highest salary using CTE

    WITH RESULT AS

    (

    SELECT SALARY,

    DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK

    FROM EMPLOYEES

    )

    SELECT TOP 1 SALARY

    FROM RESULT

    WHERE DENSERANK = N
    To find 2nd highest salary we can simply replace N with 2.

  8. — return employee record with highest salary

    select top 1 * from employees order by salary desc

    — What if two employees have the same salary and they are both the highest

    — return highest salary in employee table

    select max(salary) from employees

    — return the second highest salary from the employee table

    select * from employees order by salary desc offset 1 rows fetch next 1 rows only

    — again as preposed question states what if there are more than one employees with same salary

    — return records of employees based on a range (id .. i will use salary more commonly asked)

    select * from employees where salary between 99000 and 200000 order by salary

    — return employee name, highest salary and department

    select top 1 e.fname, e.lname, e.salary, d.name from employees e inner join departments d on d.id = e.department order by e.salary desc

    — return highest salary, employee name department name for each department

    select d.name, MAX(e.salary) as salary from departments d inner join employees e on d.id = e.department group by d.name

  9. For the last question, what if we have salary of 80000 in sales department as well. Then your query will return 3 records ( 2 records from sales and 1 record for IT department)

  10. aww man in my interview they gave me a scenario that seems difficult which included the use of joins. I've yet to hold my first full-time job and haven't used that extensively, I really didn't think it would appear and so I got creamed

  11. This is my first SQL video and I learnt it easily….You taught me very quicky 😬 Thanks

  12. To get the 2nd highest salary, you can use: select top 1 * from employee
    where salary not in (select max(salary) from employee)
    order by salary
    desc

  13. If you’re calling it Es Que El then you’re already lost the audience. Hit the books bro you’re a fraud.

  14. The last solution is incorrect! If there was another person in the Sales Department with a salary of 80000 (the maximum salary in the IT department), it would also appear in your query result, ginving two records in the sales department with different salaries, along with the one record in the IT department. Try it!

Comments are closed.