Bazy_dannykh_Uchebnoe_posobie
.pdf2006 |
9891.88 |
|
2006 |
4723.00 |
|
2008 |
767.19 |
|
, |
|
, |
|
( . |
). |
40:
ORDER BY , , GROUP BY . , ORDER BY .
SELECT , , MAX () FROM
GROUP BY , ORDER BY ;
41:
, . , .
SELECT sname, comm
FROM Salespeople
GROUP BY 2 DESC;
, ,
,
. SQL
.
42:
, , , .
|
|
|
48. |
SELECT , |
, |
, |
* 100 |
FROM |
|
; |
|
----------------------------------------------------------------------------------------------------------
* 100
----------------------------------------------------------------------------------------------------------
1001 |
|
12 |
1002 |
|
13 |
1004 |
|
11 |
1007 |
. |
5 |
1003 |
|
10 |
91
43:
|
|
|
, |
|
|
(%). |
|
|
|
|
49. |
SELECT , |
, |
, |
* 100, '% |
FROM |
|
; |
|
----------------------------------------------------------------------------------------------------------
* 100
----------------------------------------------------------------------------------------------------------
1001 |
|
|
|
12% |
|
1002 |
|
|
|
13% |
|
1004 |
|
|
|
11% |
|
1007 |
|
. |
|
5% |
|
1003 |
|
|
|
10% |
|
44: |
|
|
|
|
|
, |
|
|
|
, |
|
|
|
|
. |
. |
|
|
|
|
|
50. |
|
SELECT ' ', |
, ', |
', COUNT (DISTINCT |
), |
||
' |
.' |
|
|
|
|
FROM |
|
|
|
|
|
GROUP BY |
; |
|
|
|
---------------------------------------------------------------------------------------------------------
' ', , ', ' COUNT (DISTINCT ), '.'
----------------------------------------------------------------------------------------------------------
10/03/1990 , |
5 |
10/04/1990 , |
2 |
10/05/1990 , |
1 |
10/06/1990, |
2 |
SQL -
, . - ,
.
,
,
. , , FROM
, .
92
, , .
,
. : Salespeople.snum, Salespeople.city, Orders.odate. ,
.
45:
|
|
|
51. |
|
|
SELECT |
. |
, |
. |
, |
. |
FROM |
, |
|
|
|
|
WHERE |
. |
= |
. |
|
; |
----------------------------------------------------------------------------------------------------------
...
----------------------------------------------------------------------------------------------------------
46:
, ,
|
|
|
. |
|
. |
|
|
|
|
|
52. |
SELECT |
. |
, |
. |
FROM |
, |
|
|
WHERE ( |
. |
= |
. ) |
ORDER BY 1 DESC, 2 DESC;
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
1900.10
5160.45
18.69
1098.16
1713.23
4723.00
9891.88
75.75
1309.95 767.19
47:
93
. ,
, , . 53.
SELECT |
, |
. |
, |
. |
, |
. |
, |
|
. |
* |
|
. |
|
|
|
FROM |
|
, |
|
, |
|
|
|
WHERE |
. |
|
= |
|
|
. |
|
AND |
. |
|
= |
. |
; |
|
|
---------------------------------------------------------------------------------------------------------
|
. |
|
. |
. |
|
|
. |
* |
|
. |
|
---------------------------------------------------------------------------------------------------------
3001 |
18.69 |
2,8 |
3003 |
767.19 |
92,06 |
3002 |
1900.10 |
209,01 |
3005 |
5160.45 |
670,86 |
3006 |
1098.16 |
164,72 |
3009 |
1713.23 |
171,32 |
3007 |
75.75 |
9,85 |
3008 |
4723.00 |
613,99 |
3010 |
1309.95 |
170,29 |
3011 |
9891.88 |
117,80 |
FROM c:
SELECT
FROM < 1> < > JOIN < 2> ON <>;
- |
( . |
7). |
|
|
SQL |
|
INNER JOIN |
OUTER JOIN |
. |
INNER JOIN |
|
, |
< |
> |
(NOT NULL) |
AND. OUTER JOIN , ,
<>
OR.
48:
3-, .,
. .
94
|
|
54. |
SELECT |
|
|
FROM |
INNER JOIN |
|
|
ON (outer. |
= inner. ) |
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
2001 |
|
100 |
1001 |
2003 |
|
200 |
1002 |
2008 |
|
300 |
1007 |
2007 |
. |
100 |
1004 |
()
SQL
.
.
49:
|
|
|
|
|
. |
|
|
|
|
|
. |
|
|
|
|
|
55. |
SELECT |
. |
, |
. |
, |
. |
FROM |
|
|
|
, |
|
WHERE |
. |
|
= |
. |
; |
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
200
200
200
200
300
300
100
100
100
100
100
100
100
100 100
, ,
. ,
, |
( |
) |
. |
, A
95
B , A B . ,
|
|
, |
. |
|
, |
, |
, |
, |
. ,
,
:
|
|
|
|
|
56. |
SELECT |
. |
, |
. |
, |
. |
FROM |
|
|
|
, |
|
WHERE |
. |
= |
|
. |
|
AND |
. |
< |
|
. |
; |
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
100
200
100
100 300
- - , ,
. ,
:
|
|
57. |
SELECT * |
|
|
FROM |
|
|
WHERE |
= (SELECT |
|
FROM |
|
|
WHERE |
= ' |
'); |
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
3002 1900.10 10.03.1990 2007 1004
() , SQL () WHERE. ,
, «»,
96
. , , = 1004.
SQL , , , = 1004. . ,
,
, . ,
, (
, ), . ,
,
WHERE = 1004
DISTICT
50:
, |
, |
. .
|
58. |
SELECT * |
|
FROM |
|
WHERE |
= (SELECT DISTINCT |
FROM |
|
WHERE |
= 2004); |
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
3007 |
75.75 |
10.04.1990 |
2004 |
1002 |
3010 |
1309.95 |
10.06.1990 |
2004 |
1002 |
51:
, , 4-. .
|
59. |
|
SELECT * |
|
|
FROM |
|
|
WHERE |
> (SELECT AVG ( |
) |
FROM |
|
|
WHERE |
= 10/04/1990); |
|
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
97
3002 |
1900.10 |
10.03.1990 |
2007 |
1004 |
3005 |
5160,45 |
10.03.19990 |
2003 |
1002 |
3006 |
1098.19 |
10.03.1990 |
2008 |
1007 |
3009 |
1713.23 |
10.04.1990 |
2002 |
1003 |
3008 |
4723.00 |
10/05/1990 |
2006 |
1001 |
3010 |
1309.95 |
10/06/1990 |
2004 |
1002 |
3011 |
9891.88 |
10/06/1990 |
2006 |
1001 |
SELECT
SELECT [ DISCTINCT | ALL {* | < 1> [, < 2> ]}
FROM <1 > [, <2 > ]
[ WHERE <_>]
[ GROUP BY [collate collation] [, 1 [collate collation]]
[ HAVING <_>] [ UNION <_SELECT>]
[ PLAN <__>] [ ORDER BY <_>];
: |
|
|
[] |
, |
|
{} |
|
|
A | B |
A |
B. |
< > - |
|
. |
|
|
. |
. |
|
SELECT, |
DISTINCT ALL, *,
, , FROM,
, , WHERE,
, (
.). , .. ,
. . 77.
.
SQL INSERT.
, INSERT :
INSERT INTO <_> VALUES (<>, <> . . .);
98
SELECT.
52:
,
:
INSERT INTO
VALUES (1001, '', '', .12);
53:
,
INSERT INTO
VALUES (1001, '', NULL, .12);
54:
INSERT INTO (, , )
VALUES ('', '', 2001);
SQL
UPDATE. , UPDATE :
UPDATE <_> VALUES (<>, <> . . .);
UPDATE WHERE.
55:
,
.
UPDATE
SET = 200;
56:
UPDATE
SET rating = 200
WHERE = 1001;
:
UPDATE
SET = '', city = '', comm = .10
WHERE = 1004;
99
57:
,
.
UPDATE
SET = * 2
WHERE = '';
SQL DELETE FROM. , DELETE FROM
:
DELETE FROM <_> WHERE <>;
58:
DELETE FROM ;
59:
DELETE FROM
WHERE = 1003;
100