Select , .
COUNT (<>)
MIN (<>)
MAX (<>)
SUM (<>)
AVG (<>)
5.1 1.
| employee | ||
| id | Name | Year |
| 1 | 1985 | |
| 2 | 1981 | |
| 3 | 1987 | |
SELECT COUNT (*) FROM employee.
5.12. 1985.
SELECT COUNT (*) FROM employee WHERE year = 1985
5.13. , .
:
SELECT MIN(2017- year) AS ,
MAX(2017- year) AS ,
AVG(2017- year) AS [ ] FROM employee
, (select) , . , GROUP BY. . , , .
5.14. , , .
| employee | ||
| id | name | s |
| 1 | 2000 | |
| 2 | 5000 | |
| 3 | 3000 | |
| 4 | 1000 | |
| 5 | 2000 | |
| 6 | 3000 | |
: ?
SELECT name, COUNT(*) AS q FROM employee GROUP BY name.
| name | q |
| 3 | |
| 1 | |
| 2 |
: ?
: SELECT name, SUM(s) AS ss FROM employee GROUP BY name
| name | ss |
| 7000 | |
| 5000 | |
| 4000 |
. GROUP BY HAVING.
5.15. , ≤ 1000 ?
:
SELECT name, SUM (s) AS ss FROM employee HAVING s >1000 GROUP BY name
| name | ss |
| 7000 | |
| 5000 | |
| 3000 |
, SELECT SELECT.
5.16.
| employee | ||
| id | name | Year |
| 1 | 1985 | |
| 2 | 1981 | |
| 3 | 1987 | |
.
|
|
|
SELECT name FROM employee WHERE year = (SELECT MAX(year) FROM employee)
. : any, all.
5.17. , .
: SELECT name FROM employee WHERE year > any (SELECT year FROM employee)
.
. , . , INNER JOIN.
5.18. . idc , .
| student | ||
| id | name | idc |
| 1 | 1 | |
| 2 | 2 | |
| city | |
| id | name |
| 1 | |
| 2 | |
: , .
:
SELECT student.name [], city.name [] FROM student INNER JOIN city ON student.idc=city.id
:
1. , < >.< >.
2. AS , .
, , , . , , OUTER JOIN.
, .
: LEFT OUTER JOINON
, .
: RIGHT OUTER JOINON
: FULL OUTER JOINON
