QA Practices

LO1: Getting the Nth Highest Salary of Employees LO2: Getting the Nth Lowest Salary of Employees This is the most common question in interviews. The interviewer asks to get the nth highest, nth lowest, or to the get the nth highest or nth lowest Salary of Employees as per specific department. Getting the Nth Highest Salary of Employees Method 1: By using this method, Nth highest salary will be found. Let's say we have a table called tblemployee which contains the salary of different employees as below. Here we are going to use the subquery to get the nth highest salary. Let's say we need to find the 2nd highest salary to do that we need to run the following query. select e.name, e.salary from tblemployee e where 2 = (select count(distinct(b.salary)) from tblemployee b where e.salary <=b.salary) By running the above query we will get the second highest salary. In the same way, if we need to find the 3rd highest salary then we nee...

LO1: Joining a table with itself LO2: Classification of Self Join Prerequisite:  Joins   When we join the table with itself then join is called Self Join. Self join can be classified as Inner Self Join, Outer Self Join, and Cross Self Join. Joining a table with itself Let’s have a look on the tblemployee table. In the tblemployee table, we have the column ManagerID which contains the ID of manager for that employee. Let’s say we need to find the Manager name for the employee, to achieve this we need to use the Self Join. In self join we are taking the ManagerID and lookup in the ID column of the same table. In this way, we can find the employee manager. By running following query we will get the employee name and manager name + employee name for which the no manager is assigned maybe we can call him BOSS! select e.Name as Employee, m.Name as Manager from tblemployee e Left Join tblemployee m ON e.Ma...

LO1: Retrieve only non-matching rows from the left table LO2: Retrieve only non-matching rows from the right table LO3: Retrieve only non-matching rows from both the right and the left table Prerequisite: Joins Retrieve only non-matching rows from the left table In the previous tutorial, we have seen that how to retrieve the matching rows + non-matching from left the table using Left Outer Join. Now here we are finding only the non-matching rows from the left table as you can see in below figure. So to achieve the above result, we can use the IS NULL with where clause. Let’s see how we can use the IS NULL to filter out the rows using where clause in Left Outer Join. In table tblemployee we have the record that has DepartmentID is NULL as you can see in the below figure. When we apply left outer join on tblemployee and tbldepartment table then we get the matching rows from the left table as well as non-matching rows from the left table. sel...