Selects total group and compares it.
Table EMPLOYEES
ENO | ENAME | DES | DATE | SAL |
1 | MANOJ | MANAGER | 01-01-2007 | 26000 |
2 | SEENA | ACCOUNTANT | 02-03-2007 | 12000 |
3 | JINEESH | PROGRAMMER | 05-25-2007 | 24000 |
4 | GEORGE THOMAS | GENERAM MANAGER | 03-24-2007 | 40000 |
5 | ANU THOMAS | ACCOUNTANT | 07-12-2007 | 10000 |
6 | PADMA | CLEARK | 09-23-2007 | 8000 |
7 | PETER | CLEARK | 10-25-2007 | 7500 |
· SELECT MIN (SAL) FROM EMPLOYEES
· SELECT MAX (SAL) FROM EMPLOYEES
· SELECT SUM (SAL) FROM EMPLOYEES
· SELECT AVG (SAL) FROM EMPLOYEES
Counts the Number of Records in a Column
· SELECT COUNT (ENAME) FROM EMPLOYEES
· SELECT COUNT (*) FROM EMPLOYEES
Groups the record of a table
Note: For this - modify the table
add a new column named DEPTNO (use ALTER query )
ALTER TABLE EMPLOYEES COLUMN EDPTNO INT
and update all row (use UPDATE query)
UPDATE EMPLOYEES SET DEPTNO=20 WHERE ENAME='MANOJ'
Now the structure changes
ENO | ENAME | DES | DATE | SAL | DEPTNO |
1 | MANOJ | MANAGER | 01-01-2007 | 26000 | 20 |
2 | SEENA | ACCOUNTANT | 02-03-2007 | 12000 | 20 |
3 | JINEESH | PROGRAMMER | 05-25-2007 | 24000 | 30 |
4 | GEORGE THOMAS | GENERAM MANAGER | 03-24-2007 | 40000 | 40 |
5 | ANU THOMAS | ACCOUNTANT | 07-12-2007 | 10000 | 10 |
6 | PADMA | CLEARK | 09-23-2007 | 8000 | 20 |
7 | PETER | CLEARK | 10-25-2007 | 7500 | 10 |
· SELECT DEPTNO, COUNT (*) FROM EMPLOYEES GROUP BY DEPTNO
To give a condition for a group by statement
· SELECT DEPTNO, COUNT(*) FROM EMPLOYEES GROUP BY DEPTNO HAVING COUNT (*)>3