VLOOKUP lets you search for specific information in your spreadsheet

Adding the arguments

Now, we’ll add our arguments. The arguments will tell VLOOKUP what to search for and where to search.

The first argument is the name of the item you’re searching for, which in this case is Photo frame. Because the argument is text, we’ll need to put it in double quotes:

=VLOOKUP(“Item 5”

The second argument is the cell range that contains the data. In this example, our data is in A2:B16. As with any function, you’ll need to use a comma to separate each argument:

=VLOOKUP(“Item 5”, A2:B16

Note: It’s important to know that VLOOKUP will always search the first column in this range. In this example, it will search column A for “Item 5”. In some cases, you may need to move the columns around so the first column contains the correct data.

The third argument is the column index number. It’s simpler than it sounds: The first column in the range is 1, the second column is 2, etc. In this case, we are trying to find the price of the item, and the prices are contained in the second column. This means our third argument will be 2:

=VLOOKUP(“Item 5”, A2:B16, 2

The fourth argument tells VLOOKUP whether to look for approximate matches, and it can be either TRUE or FALSE. If it is TRUE, it will look for approximate matches. Generally, this is only useful if the first column has numerical values that have been sorted. Because we’re only looking for exact matches, the fourth argument should be FALSE. This is our last argument, so go ahead and close the parentheses:

=VLOOKUP(“Item 5”, A2:B16, 2, FALSE)

Finding Value of Particular Data

 =VLOOKUP("Item 5", A2:B16, 2, FALSE)

Finding Category in Two Columns

  =VLOOKUP("Item 4", A2:C16, 3, FALSE)

Finding Matching Data in Two Columns

   =VLOOKUP(B2,$A$2:$A$361,1,FALSE)

Filter Tables in System
The Super set column in which the sub set is searched should be added as a second parameter. In our case its B

   =VLOOKUP(A2,$B$2:$B$23, 1, FALSE)

Java Bean Class – EmployeeBean

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class EmployeeBean {
	private String Name;
	private int EmpNo;
	private int Age;
	private float Weight;
	
	public String getName() {
		return Name;
	}
	public void setName(String name) {
		Name = name;
	}
	public int getEmpNo() {
		return EmpNo;
	}
	public void setEmpNo(int empNo) {
		EmpNo = empNo;
	}
	public int getAge() {
		return Age;
	}
	public void setAge(int age) {
		Age = age;
	}
	public float getWeight() {
		return Weight;
	}
	public void setWeight(float weight) {
		Weight = weight;
	}
	
	
	
	public EmployeeStringBean getStringBean() {
		
		EmployeeStringBean objEmpBean = new EmployeeStringBean();
		
		objEmpBean.setName(getName() == null?null:getName().toString());
		objEmpBean.setEmpNo(Integer.toString(getEmpNo()));
		objEmpBean.setAge(Integer.toString(getAge()));		
		objEmpBean.setWeight(getWeight()+"");
		
		return objEmpBean;
	}
	
	
	public static List getStringBeanList(List beanList) {
		 List stringBeanList = new ArrayList();

	        if (beanList != null) {
	            Iterator itr = beanList.iterator();

	            while (itr.hasNext()) {
	            	EmployeeBean bean = (EmployeeBean) itr.next();
	                stringBeanList.add(bean.getStringBean());
	            }
	        }

	        return stringBeanList;
	}
}

String Bean Class – EmployeeStringBean

public class EmployeeStringBean {
	private String Name;
	private String EmpNo;
	private String Age;
	private String Weight;
	
	public String getName() {
		return Name;
	}
	public void setName(String name) {
		Name = name;
	}
	public String getEmpNo() {
		return EmpNo;
	}
	public void setEmpNo(String empNo) {
		EmpNo = empNo;
	}
	public String getAge() {
		return Age;
	}
	public void setAge(String age) {
		Age = age;
	}
	public String getWeight() {
		return Weight;
	}
	public void setWeight(String weight) {
		Weight = weight;
	}	
	
	public EmployeeBean getBean()
	{
		EmployeeBean objEmployeeBean = new EmployeeBean();
		
		objEmployeeBean.setName(getName());
		objEmployeeBean.setEmpNo(Integer.parseInt(getEmpNo()));
		objEmployeeBean.setAge(Integer.parseInt(getAge()));
		objEmployeeBean.setWeight(Float.parseFloat((getWeight())));
		
		return objEmployeeBean;
	}	
}

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

UNION Example:

 SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL example:

 SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)
Posted in SQL.

During Release the Scripts are executed in the Following Order

  1. Schema – DDL Scripts – (Table Alter Scripts)
  2. Content Script – Rows addition and Deletion
  3. Packages (Stored Procedures and Function)- DML Scripts – (CRUD)

SELECT Sno,
       Name,
       Department,
       Salary,
       SUM(Salary) over(PARTITION BY Department ORDER BY Sno) AS Cum_Salary
  FROM Master_Employee
  ORDER BY Department;

How this Works
PARTITION BY Department ORDER BY Sno

Partition By will first Partition by Department.Which means the Cumulative Sum will get Reset once the Department Changes.

ORDER BY Sno will make sure the Cumulative value calculated will not be swapped and stays the same for the respective rows.

Now to have a total amount for the Salary the query is as below.

  SELECT Sno,
       Name,
       Department,
       Salary       
  FROM Master_Employee
  UNION
  SELECT null,
         null,
         'Total',
         NVL(SUM(Salary), 0)
   FROM Master_Employee
  ORDER BY Sno;

Total along with Cumulative Sum

 SELECT Sno,
       Name,
       Department,
       Salary,
       SUM(Salary) over(PARTITION BY Department ORDER BY Sno) AS Cum_Salary       
  FROM Master_Employee
  UNION
  SELECT null,
         null,
         'Total',
         NVL(SUM(Salary), 0),
         null
   FROM Master_Employee
  ORDER BY Sno;

Another wat of fetching records using sub query

SELECT Sno,
       Name,
       Department,
       Salary,
       CASE
         WHEN Department = 'Total' THEN
          NULL
         ELSE
          SUM(Salary) over(PARTITION BY Department ORDER BY Sno)
       END AS Cum_Salary
  FROM (SELECT Sno, Name, Department, Salary, null, 1 AS Sort_Order
          FROM Master_Employee
        UNION
        SELECT null,
               null,
               'Total',
               NVL(SUM(Salary), 0),
               null,
               2 AS Sort_Order
          FROM Master_Employee)
 ORDER BY Department, Sno, Sort_Order;