Share on Google+Share on Google+

JDBC : Salary greater than average salary

Advertisement
In this section you will learn how to get records of employee having salary greater than the average salary

JDBC : Salary greater than average salary

In this section you will learn how to get records of employee having salary greater than the average salary.

Salary greater than average salary : By using AVG(salary) function you can get average salary of employee. Now by using the greater sign, you can find records of such employees whose salary is greater than the average salary.

Your sql query -

sql = "SELECT * FROM employee WHERE salary >(SELECT AVG(salary) FROM employee)"

Employee Table :  -

Example : Here is an example displaying the average salary and the records of employees whose salary is greater than the average salary.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

class GreaterThanAverage {
	public static void main(String[] args) {
		System.out.println("Records whose salary greater than average salary...");
		Connection conn = null;
		String url = "jdbc:mysql://localhost:3306/";
		String dbName = "employees";
		String driverName = "com.mysql.jdbc.Driver";
		String userName = "root";
		String password = "root";
		Statement statement = null;
		ResultSet rs;
		try {
			Class.forName(driverName);
			conn = DriverManager
					.getConnection(url + dbName, userName, password);
			statement = conn.createStatement();

			String sql = "SELECT * FROM employee WHERE salary > (SELECT AVG(salary) FROM employee)";

			rs = statement.executeQuery(sql);
			System.out.println("EmpId\tName\tSalary");
			System.out.println("----------------------");
			while (rs.next()) {
				int roll = rs.getInt("emp_id");
				String name = rs.getString("name");
				long salary = rs.getLong("salary");

				System.out.println(roll + "\t" + name + "\t" + salary);

			}

			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

Output :

Records whose salary greater than average salary...
Average salary : 22000
EmpId	Name	Salary
----------------------
2	Roxi	25000
3	Linda	30000

Advertisements

Advertisement

Posted on: October 11, 2012 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Share this Tutorial Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.