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 salaryJDBC : 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
