Page 174 - SQL

Getting average income by department:

SELECT department, AVG(income)
FROM employees
GROUP BY department;

The important thing is to select only columns specified in the GROUP BY clause or used with
aggregate functions.

There WHERE clause can also be used with GROUP BY, but WHERE filters out records before any
grouping is done:

SELECT department, AVG(income)
FROM employees
WHERE department <> 'ACCOUNTING'
GROUP BY department;

If you need to filter the results after the grouping has been done, e.g, to see only departments
whose average income is larger than 1000, you need to use the HAVING clause:

SELECT department, AVG(income)
FROM employees
WHERE department <> 'ACCOUNTING'
GROUP BY department
HAVING avg(income) > 1000;

Selecting with more than 1 condition.

The AND keyword is used to add more conditions to the query.

Name Age Gender

Sam 18 M

John 21 M

Bob 22 M

Mary 23 F

SELECT name FROM persons WHERE gender = 'M' AND age > 20;

This will return:

Name

John

Bob

https://riptutorial.com/ 156