JDBC : Salary greater than 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

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

Ads