MySQL having clause with group by clause

MySQL having clause with group by clause :

Syntax :


select * from <table_name> group by <column_name> having <condition>;

Example :


select * from employee group by salary having salary>45000;

Advertisements

MySQL having clause

MySQL having clause :

The having clause used to give a condition in query statements. It can be used with or without group by clause.

Syntax :


select * from <table_name> having <condition>;

Example :


select * from employee having salary>45000;

MySQL sorting data from table

MySQL sorting data from table :

MySQL allow us to view data from table in a sorted order. The rows retrieved from the table will be sorted in either ascending order or descending order depending on given condition.
Note : to sort data as ascending use “asc” and descending use “desc”.

Syntax :


select * from <table_name> order by <column_name> <[sorting_order]>;

Example :

Suppose you want to view data from table as salary in descending order. So you will have to write query as follows…


select * from employee order by salary desc;

 

MySQL eliminating duplicate rows with select statement :

MySQL eliminating duplicate rows with select statement :

A table may contain duplicate rows and you need to view only unique row then you can use distinct keyword with
select statement.

Syntax :


select distinct * from <table_name>;

Example :


select distinct * from employee;

Select operation in Hibernate

Select  operation in Hibernate :

Here you will see select operation through annotation  in Hibernate. To create select operation you have to create following java class.

  • Employee.java
  • HibernateTest.java

Employee.java :


package com.pkjavacode.com;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
/**
*
* @author pradeep
*/
@Entity
@Table(name = "employee")
public class Employee implements Serializable {

private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Integer id;
@Column(name = "name")
private String name;
@Column(name = "salary")
private Integer salary;

public Employee() {
}

public Employee(Integer id) {
this.id = id;
}

public Integer getId() {
return id;
}

public void setId(Integer id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public Integer getSalary() {
return salary;
}

public void setSalary(Integer salary) {
this.salary = salary;
}
}

HibernateTest.java :


package com.pkjavacode.com;

import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.cfg.AnnotationConfiguration;

/**
*
* @author pradeep
*/
public class HibernateTest {

public static void main(String a[]) {

Session s = null;
try {
s = new AnnotationConfiguration().configure().buildSessionFactory().openSession();
Transaction t = s.beginTransaction();
List<Employee> employees=s.createQuery("from Employee").list();
System.out.println("Id"+" "+"Name"+" "+"Salary");
System.out.println("----------------------------------");
for(Employee e2:employees) {
System.out.print(e2.getId()+" "+e2.getName()+" "+e2.getSalary()+"\n");
//System.out.print("\n"+e);
}

} catch (Exception e) {
e.printStackTrace();
} finally {
s.close();
}
}
}

Output :

Hibernate: select employee0_.id as id0_, employee0_.name as name0_, employee0_.salary as salary0_ from employee employee0_
Id Name Salary
———————————-
72 Rahul 35000
73 Pradeep Yadav 45000
74 Amit 65000

BUILD SUCCESSFUL (total time: 3 seconds)