# Chapter 8: Query Language

This example shows some basic JPQL queries. The example allows you to try the named queries shown in this chapter of the book, as well as try your own queries.

### Try your own query

### SELECT clause examples

| SELECT e FROM Employee e
| SELECT d FROM Department d
| SELECT OBJECT(d) FROM Department d
| SELECT e.name FROM Employee e
| SELECT e.department FROM Employee e
| SELECT DISTINCT e.department FROM Employee e
| SELECT d.employees FROM Department d
| SELECT e.name, e.salary FROM Employee e
| SELECT NEW examples.model.EmployeeDetails(e.name, e.salary, e.department.name) FROM Employee e
| SELECT p FROM Project p WHERE p.employees IS NOT EMPTY

### FROM clause examples

| SELECT p FROM Employee e JOIN e.phones p
| SELECT DISTINCT p FROM Employee e, IN(e.phones) p
| SELECT p.number FROM Employee e JOIN e.phones p
| SELECT d FROM Employee e JOIN e.department d
| SELECT e.department FROM Employee e
| SELECT DISTINCT e.department FROM Project p JOIN p.employees e WHERE p.name = 'Release1' AND e.address.state = 'CA'
| SELECT DISTINCT d FROM Project p JOIN p.employees e JOIN e.department d JOIN e.address a WHERE p.name = 'Release1' AND a.state = 'CA'
| SELECT DISTINCT d FROM Department d, Employee e WHERE d = e.department
| SELECT d, m FROM Department d, Employee m WHERE d = m.department AND m.directs IS NOT EMPTY
| SELECT DISTINCT p FROM Department d JOIN d.employees e JOIN e.projects p
| SELECT e, d FROM Employee e LEFT JOIN e.department d
| SELECT e FROM Employee e JOIN FETCH e.address
| SELECT e, a FROM Employee e JOIN e.address a
| SELECT d FROM Department d LEFT JOIN FETCH d.employees
| SELECT d, e FROM Department d LEFT JOIN d.employees e

### WHERE clause examples

| SELECT e FROM Employee e WHERE e.salary BETWEEN 40000 AND 45000
| SELECT e FROM Employee e WHERE e.salary >= 40000 AND e.salary <= 45000
| SELECT d FROM Department d WHERE d.name LIKE '__Eng%'
| SELECT d FROM Department d WHERE d.name LIKE 'QA\_%' ESCAPE '\'
| SELECT e FROM Employee e WHERE e.salary = (SELECT MAX(e2.salary) FROM Employee e2)
| SELECT e FROM Employee e WHERE EXISTS (SELECT p FROM Phone p WHERE p.employee = e AND p.type = 'Cell')
| SELECT e FROM Employee e WHERE EXISTS (SELECT p FROM e.phones p WHERE p.type = 'Cell')
| SELECT e FROM Employee e WHERE e.address.state IN ('NY', 'CA')
| SELECT e FROM Employee e WHERE e.department IN (SELECT DISTINCT d FROM Department d JOIN d.employees de JOIN de.projects p WHERE p.name LIKE 'QA%')
| SELECT p FROM Phone p WHERE p.type NOT IN ('Office', 'Home')
| SELECT e FROM Employee e WHERE e.directs IS NOT EMPTY
| SELECT m FROM Employee m WHERE (SELECT COUNT(e) FROM Employee e WHERE e.manager = m) > 0
| SELECT e FROM Employee e WHERE e MEMBER OF e.directs
| SELECT e FROM Employee e WHERE NOT EXISTS (SELECT p FROM e.phones p WHERE p.type = 'Cell')
| SELECT e FROM Employee e WHERE e.directs IS NOT EMPTY AND e.salary < ALL (SELECT d.salary FROM e.directs d)
| SELECT e FROM Employee e WHERE e.department = ANY (SELECT DISTINCT d FROM Department d JOIN d.employees de JOIN de.projects p WHERE p.name LIKE 'QA%')
| SELECT d FROM Department d WHERE SIZE(d.employees) = 2
| SELECT d FROM Department d WHERE (SELECT COUNT(e) FROM d.employees e) = 2

### ORDER BY examples

| SELECT e FROM Employee e ORDER BY e.name DESC
| SELECT e FROM Employee e JOIN e.department d ORDER BY d.name, e.name DESC
| SELECT e.name FROM Employee e ORDER BY e.salary DESC

### Aggregate Query examples

| SELECT AVG(e.salary) FROM Employee e
| SELECT d.name, AVG(e.salary) FROM Department d JOIN d.employees e GROUP BY d.name
| SELECT d.name, AVG(e.salary) FROM Department d JOIN d.employees e WHERE e.directs IS EMPTY GROUP BY d.name
| SELECT d.name, AVG(e.salary) FROM Department d JOIN d.employees e WHERE e.directs IS EMPTY GROUP BY d.name HAVING AVG(e.salary) > 50000
| SELECT d.name, e.salary FROM Department d JOIN d.employees e WHERE e.directs IS EMPTY
| SELECT e, COUNT(p), COUNT(DISTINCT p.type) FROM Employee e JOIN e.phones p GROUP BY e

### GROUP BY Clause examples

| SELECT d.name, COUNT(e) FROM Department d JOIN d.employees e GROUP BY d.name
| SELECT d.name, COUNT(e), AVG(e.salary) FROM Department d JOIN d.employees e GROUP BY d.name
| SELECT d.name, e.salary, COUNT(p) FROM Department d JOIN d.employees e JOIN e.projects p GROUP BY d.name, e.salary
| SELECT COUNT(e), AVG(e.salary) FROM Employee e

### HAVING Clause example

| SELECT e, COUNT(p) FROM Employee e JOIN e.projects p GROUP BY e HAVING COUNT(p) >= 2

### UPDATE Query examples

| UPDATE Employee e SET e.salary = 60000 WHERE e.salary = 55000
| UPDATE Employee e SET e.salary = e.salary + 5000 WHERE EXISTS (SELECT p FROM e.projects p WHERE p.name = 'Release1')
| UPDATE Phone p SET p.number = CONCAT('288', SUBSTRING(p.number, LOCATE(p.number, '-'), 4)), p.type = 'Business' WHERE p.employee.address.city = 'New York' AND p.type = 'Office'

### DELETE Query example

| DELETE FROM Employee e WHERE e.department IS NULL