SQL , . . , , , .
, R1 . 4 , , , .
. . . 7.
| 7. | |
| COUNT | , |
| SUM | |
| AVG | |
| MIN | |
| MAX |
SELECT, : . SUM AVG . COUNT, MAX MIN , . MAX MIN ASCII . , ANSI .
, , . "" . * COUNT .
SELECT R1., COUNT(*)FROM R1GROUP BY R1.:
| COUNT(*) | |
. , 5 :
SELECT R1.FROM R1GROUP BY R1.HAVING COUNT(*) >= 5 :
SQL . , ( WHERE HAVING), , . EXISTS, , .
.
| PNUM | PNAME | PSTATUS |
P ()
|
|
|
| DNUM | DNAME | DSTATUS |
D ()
| PNUM | DNUM | VOLUME |
PD ()
1. , ( ):
SELECT *
FROM P
WHERE P.STATYS <
(SELECT MAX(P.STATUS)
FROM P);
. .. P.STATUS , , , .
. :
- .
- P, , , .
2. IN. , 2:
SELECT *
FROM P
WHERE P.PNUM IN
(SELECT DISTINCT PD.PNUM
FROM PD
WHERE PD.DNUM = 2);
. , .
. :
- , 2.
- P, , .
3. EXISTS. , 2:
SELECT *
FROM P
WHERE EXISTS
(SELECT *
FROM PD
WHERE
PD.PNUM = P.PNUM AND
PD.DNUM = 2);
. :
- P, , P.
- , .
. , ( ), - P.PNUM. (correlated). -, , , . EXISTS, .
. , , , . , .. , , , , . SQL , . , , , , , - .
|
|
|
4. NOT EXISTS. , 2:
SELECT *
FROM P
WHERE NOT EXISTS
(SELECT *
FROM PD
WHERE
PD.PNUM = P.PNUM AND
PD.DNUM = 2);
. , . , , .
5. , :
SELECT DISTINCT PNAME
FROM P
WHERE NOT EXISTS
(SELECT *
FROM D
WHERE NOT EXISTS
(SELECT *
FROM PD
WHERE
PD.DNUM = D.DNUM AND
PD.PNUM = P.PNUM));
. .
(D.DNUM, P.PNUM) : , PNUM DNUM. NOT EXISTS , . , P.PNUM, : , PNUM. NOT EXISTS , PNUM , . , , .
, , , , . , () , . .
FROM SQL2 :
FROM < >< >< >< >< >< >::= <__1> [ _1] [ ] [,<__n>[ < _n> ] ]< >:: =<__1> NATURAL { INNER | FULL [OUTER] LEFT [OUTER] | RIGHT [OUTER]} JOIN <__2>< >:: = <__1> CROSS JOIN <__2>< >::=<__1> UNION JOIN <__2>< >::= <__1> { INNERFULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN {ON | [USING ( )]} <__2>INNER () , LEFT , 1, , 2, NULL (). RIGHT , 2, 1 . FULL : . 1, , 2, .
OUTER , FULL, LEFT, RIGHT, .
|
|
|
. "". , , , . , . R2 R3 , R1, . , FROM SELECT.
SELECT R1., R1., R1.FROM (R2 NATURAL INNER JOIN R3) LEFT JOIN R1 USING (, ):
| . . | ||
| . . | ||
| . . | ||
| . . | ||
| . | ||
| . . | ||
| . . | Null | |
| . . | ||
| . . | Null | |
| . . | ||
| . |
