Главная / Базы данных /
Введение в модель данных SQL / Тест 6
Введение в модель данных SQL - тест 6
Упражнение 1:
Номер 1
Какая из показанных ниже таблиц является результатом запроса
SELECT EMP_SAL, EMP_BONUS, DEPT_NO,
MAX(EMP_BDATE) AS MAX_BDATE,
GROUPING (EMP_BONUS) AS GBON,
GROUPING (DEPT_NO) AS GDN
FROM EMP
GROUP BY ROLLUP (EMP_SAL, EMP_BONUS, DEPT_NO);
Ответ:
 
(1)
EMP_SAL | EMP_BONUS | DEPT_NO | MAX_BDATE | GBON | GDN |
---|
NULL | NULL | NULL | 1970 | 1 | 1 |
13000.00 | NULL | NULL | 1950 | 1 | 1 |
14000.00 | NULL | NULL | 1960 | 1 | 1 |
15000.00 | NULL | NULL | NULL | 1 | 1 |
16000.00 | NULL | NULL | 1960 | 1 | 1 |
17000.00 | NULL | NULL | NULL | 1 | 1 |
18000.00 | NULL | NULL | 1955 | 1 | 1 |
19000.00 | NULL | NULL | NULL | 1 | 1 |
20000.00 | NULL | NULL | 1960 | 1 | 1 |
22000.00 | NULL | NULL | 1960 | 1 | 1 |
13000.00 | 12000.00 | NULL | 1950 | 0 | 1 |
15000.00 | 13000.00 | NULL | 1950 | 0 | 1 |
15000.00 | 14000.00 | NULL | NULL | 0 | 1 |
16000.00 | 14000.00 | NULL | 1955 | 0 | 1 |
16000.00 | 13000.00 | NULL | 1960 | 0 | 1 |
16000.00 | 15000.00 | NULL | 1950 | 0 | 1 |
17000.00 | 16000.00 | NULL | 1970 | 0 | 1 |
18000.00 | 17000.00 | NULL | 1955 | 0 | 1 |
19000.00 | 18000.00 | NULL | NULL | 0 | 1 |
22000.00 | 20000.00 | NULL | 1960 | 0 | 1 |
13000.00 | 12000.00 | 1 | 1950 | 0 | 0 |
14000.00 | NULL | 1 | 1960 | 0 | 0 |
15000.00 | 13000.00 | 1 | 1950 | 0 | 0 |
15000.00 | 14000.00 | 1 | NULL | 0 | 0 |
16000.00 | 14000.00 | 1 | 1955 | 0 | 0 |
16000.00 | 13000.00 | 1 | 1960 | 0 | 0 |
16000.00 | 15000.00 | 2 | 1950 | 0 | 0 |
17000.00 | NULL | 1 | NULL | 0 | 0 |
17000.00 | 16000.00 | 2 | 1970 | 0 | 0 |
18000.00 | 17000.00 | 3 | 1955 | 0 | 0 |
20000.00 | NULL | 2 | 1960 | 0 | 0 |
22000.00 | 20000.00 | 3 | 1960 | 0 | 0 |
 
 
(2)
EMP_SAL | EMP_BONUS | DEPT_NO | MAX_BDATE | GBON | GDN
|
---|
NULL | NULL | NULL | 1970 | 1 | 1 |
13000.00 | NULL | NULL | 1950 | 1 | 1
|
14000.00 | NULL | NULL | 1960 | 1 | 1
|
15000.00 | NULL | NULL | NULL | 1 | 1
|
16000.00 | NULL | NULL | 1960 | 1 | 1
|
17000.00 | NULL | NULL | NULL | 1 | 1
|
18000.00 | NULL | NULL | 1955 | 1 | 1
|
19000.00 | NULL | NULL | NULL | 1 | 1
|
20000.00 | NULL | NULL | 1960 | 1 | 1
|
22000.00 | NULL | NULL | 1960 | 1 | 1
|
13000.00 | 12000.00 | NULL | 1950 | 0 | 1
|
14000.00 | NULL | NULL | 1960 | 0 | 1
|
15000.00 | 13000.00 | NULL | 1950 | 0 | 1
|
15000.00 | 14000.00 | NULL | NULL | 0 | 1
|
16000.00 | 14000.00 | NULL | 1955 | 0 | 1
|
16000.00 | 13000.00 | NULL | 1960 | 0 | 1
|
16000.00 | 15000.00 | NULL | 1950 | 0 | 1
|
17000.00 | NULL | NULL | NULL | 0 | 1
|
17000.00 | 16000.00 | NULL | 1970 | 0 | 1
|
18000.00 | 17000.00 | NULL | 1955 | 0 | 1
|
19000.00 | 18000.00 | NULL | NULL | 0 | 1
|
20000.00 | NULL | NULL | 1960 | 0 | 1
|
22000.00 | 20000.00 | NULL | 1960 | 0 | 1
|
13000.00 | 12000.00 | 1 | 1950 | 0 | 0
|
14000.00 | NULL | 1 | 1960 | 0 | 0
|
14000.00 | NULL | NULL | 1960 | 0 | 0
|
15000.00 | 13000.00 | 1 | 1950 | 0 | 0
|
15000.00 | 14000.00 | 1 | NULL | 0 | 0
|
16000.00 | 14000.00 | 1 | 1955 | 0 | 0
|
16000.00 | 13000.00 | 1 | 1960 | 0 | 0
|
16000.00 | 15000.00 | 2 | 1950 | 0 | 0
|
17000.00 | NULL | 1 | NULL | 0 | 0
|
17000.00 | 16000.00 | 2 | 1970 | 0 | 0
|
18000.00 | 17000.00 | 3 | 1955 | 0 | 0
|
19000.00 | 18000.00 | NULL | NULL | 0 | 0
|
20000.00 | NULL | 2 | 1960 | 0 | 0
|
22000.00 | 20000.00 | 3 | 1960 | 0 | 0
|
 
 
(3)
EMP_SAL | EMP_BONUS | DEPT_NO | MAX_BDATE | GBON | GDN |
---|
NULL | NULL | NULL | 1970 | 1 | 1 |
13000.00 | NULL | NULL | 1950 | 1 | 1 |
14000.00 | NULL | NULL | 1960 | 1 | 1 |
15000.00 | NULL | NULL | NULL | 1 | 1 |
16000.00 | NULL | NULL | 1960 | 1 | 1 |
17000.00 | NULL | NULL | NULL | 1 | 1 |
18000.00 | NULL | NULL | 1955 | 1 | 1 |
19000.00 | NULL | NULL | NULL | 1 | 1 |
20000.00 | NULL | NULL | 1960 | 1 | 1 |
22000.00 | NULL | NULL | 1960 | 1 | 1 |
13000.00 | 12000.00 | NULL | 1950 | 0 | 1 |
14000.00 | NULL | NULL | 1960 | 0 | 1 |
15000.00 | 13000.00 | NULL | 1950 | 0 | 1 |
16000.00 | 14000.00 | NULL | 1955 | 0 | 1 |
17000.00 | NULL | NULL | NULL | 0 | 1 |
18000.00 | 17000.00 | NULL | 1955 | 0 | 1 |
19000.00 | 18000.00 | NULL | NULL | 0 | 1 |
20000.00 | NULL | NULL | 1960 | 0 | 1 |
22000.00 | 20000.00 | NULL | 1960 | 0 | 1 |
13000.00 | 12000.00 | 1 | 1950 | 0 | 0 |
14000.00 | NULL | 1 | 1960 | 0 | 0 |
15000.00 | 13000.00 | 1 | 1950 | 0 | 0 |
16000.00 | 14000.00 | 1 | 1955 | 0 | 0 |
17000.00 | NULL | 1 | NULL | 0 | 0 |
18000.00 | 17000.00 | 3 | 1955 | 0 | 0 |
19000.00 | 18000.00 | NULL | NULL | 0 | 0 |
20000.00 | NULL | 2 | 1960 | 0 | 0 |
22000.00 | 20000.00 | 3 | 1960 | 0 | 0 |
 
Номер 2
Пусть имеется следующее дерево:
Какой из следующих обходов этого дерева является обходом в ширину?
Ответ:
 (1)
КОРЕНЬ-ПОТОМОК1-ПОТОМОК2-ПОТОМОК3-П1.1-П2.1-
П3.1-П3.2-П3.3-П1.1.1-П1.1.2-П2.1.1-П2.1.1.1 
 (2)
КОРЕНЬ-ПОТОМОК1-ПОТОМОК2-ПОТОМОК3-П1.1-П1.1.1-
П1.1.2-П2.1-П2.1.1-П2.1.1.1-П3.1-П3.2-П3.3 
 (3)
КОРЕНЬ-ПОТОМОК1- П1.1-П1.1.1-П1.1.2- ПОТОМОК2-
П2.1-П2.1.1-П2.1.1.1- ПОТОМОК3- П3.1-П3.2-П3.3 
Номер 3
Какая из показанных ниже таблиц является результатом запроса?
SELECT EMP_BDATE, EMP_BONUS,
MIN (EMP_SAL) AS MIN_SAL,
GROUPING (EMP_BDATE) AS GBD,
GROUPING (EMP_BONUS) AS GBON
FROM EMP
GROUP BY CUBE (EMP_BDATE, EMP_BONUS);
Ответ:
 
(1)
EMP_BDATE | EMP_BONUS | MIN_SAL | GBD | GBON |
---|
NULL | NULL | 13000.00 | 1 | 1 |
1950 | NULL | 13000.00 | 0 | 1 |
1955 | NULL | 16000.00 | 0 | 1 |
1960 | NULL | 14000.00 | 0 | 1 |
1970 | NULL | 17000.00 | 0 | 1 |
1950 | 13000.00 | 15000.00 | 0 | 0 |
1950 | 15000.00 | 16000.00 | 0 | 0 |
1950 | 17000.00 | 18000.00 | 0 | 0 |
1950 | 12000.00 | 13000.00 | 0 | 0 |
1950 | NULL | 14000.00 | 0 | 0 |
1955 | 14000.00 | 16000.00 | 0 | 0 |
1955 | 17000.00 | 18000.00 | 0 | 0 |
1960 | NULL | 14000.00 | 0 | 0 |
1960 | 13000.00 | 16000.00 | 0 | 0 |
1960 | 20000.00 | 22000.00 | 0 | 0 |
1960 | NULL | 14000.00 | 0 | 0 |
1970 | 16000.00 | 17000.00 | 0 | 0 |
NULL | 14000.00 | 15000.00 | 0 | 0 |
NULL | 18000.00 | 19000.00 | 0 | 0 |
NULL | 13000.00 | 15000.00 | 1 | 0 |
NULL | 14000.00 | 15000.00 | 1 | 0 |
NULL | 15000.00 | 16000.00 | 1 | 0 |
NULL | 16000.00 | 17000.00 | 1 | 0 |
NULL | 17000.00 | 18000.00 | 1 | 0 |
NULL | 18000.00 | 19000.00 | 1 | 0 |
NULL | 20000.00 | 22000.00 | 1 | 0 |
 
 
(2)
EMP_BDATE | EMP_BONUS | MIN_SAL | GBD | GBON |
---|
NULL | NULL | 13000.00 | 1 | 1 |
1950 | NULL | 13000.00 | 0 | 1 |
1955 | NULL | 16000.00 | 0 | 1 |
1960 | NULL | 14000.00 | 0 | 1 |
1970 | NULL | 17000.00 | 0 | 1 |
NULL | NULL | 15000.00 | 0 | 1 |
1950 | 13000.00 | 15000.00 | 0 | 0 |
1950 | 15000.00 | 16000.00 | 0 | 0 |
1950 | 17000.00 | 18000.00 | 0 | 0 |
1950 | 12000.00 | 13000.00 | 0 | 0 |
1950 | NULL | 14000.00 | 0 | 0 |
1955 | 14000.00 | 16000.00 | 0 | 0 |
1955 | 17000.00 | 18000.00 | 0 | 0 |
1960 | NULL | 14000.00 | 0 | 0 |
1960 | 13000.00 | 16000.00 | 0 | 0 |
1960 | 20000.00 | 22000.00 | 0 | 0 |
1960 | NULL | 14000.00 | 0 | 0 |
1970 | 16000.00 | 17000.00 | 0 | 0 |
NULL | 14000.00 | 15000.00 | 0 | 0 |
NULL | 18000.00 | 19000.00 | 0 | 0 |
NULL | NULL | 17000.00 | 0 | 0 |
NULL | 13000.00 | 15000.00 | 1 | 0 |
NULL | 14000.00 | 15000.00 | 1 | 0 |
NULL | 15000.00 | 16000.00 | 1 | 0 |
NULL | 16000.00 | 17000.00 | 1 | 0 |
NULL | 17000.00 | 18000.00 | 1 | 0 |
NULL | 18000.00 | 19000.00 | 1 | 0 |
NULL | 20000.00 | 22000.00 | 1 | 0 |
NULL | NULL | 14000.00 | 1 | 0 |
 
 
(3)
EMP_BDATE | EMP_BONUS | MIN_SAL | GBD | GBON |
---|
NULL | NULL | 13000.00 | 1 | 1 |
1950 | NULL | 13000.00 | 0 | 1 |
1955 | NULL | 16000.00 | 0 | 1 |
1960 | NULL | 14000.00 | 0 | 1 |
1970 | NULL | 17000.00 | 0 | 1 |
1950 | 13000.00 | 15000.00 | 0 | 0 |
1950 | 15000.00 | 16000.00 | 0 | 0 |
1950 | 17000.00 | 18000.00 | 0 | 0 |
1950 | 12000.00 | 13000.00 | 0 | 0 |
1955 | 14000.00 | 16000.00 | 0 | 0 |
1955 | 17000.00 | 18000.00 | 0 | 0 |
1960 | 13000.00 | 16000.00 | 0 | 0 |
1960 | 20000.00 | 22000.00 | 0 | 0 |
1970 | 16000.00 | 17000.00 | 0 | 0 |
NULL | 14000.00 | 15000.00 | 0 | 0 |
NULL | 18000.00 | 19000.00 | 0 | 0 |
NULL | 13000.00 | 15000.00 | 1 | 0 |
NULL | 14000.00 | 15000.00 | 1 | 0 |
NULL | 15000.00 | 16000.00 | 1 | 0 |
NULL | 16000.00 | 17000.00 | 1 | 0 |
NULL | 17000.00 | 18000.00 | 1 | 0 |
NULL | 18000.00 | 19000.00 | 1 | 0 |
NULL | 20000.00 | 22000.00 | 1 | 0 |
 
Упражнение 2:
Номер 1
Пусть имеется следующее дерево:
Какой из следующих обходов этого дерева является обходом в ширину?
Ответ:
 (1)
КОРЕНЬ-ПОТОМОК1-П1.1-П1.1.1-П1.1.2-П1.1.1.1-
П1.1.1.1.1-П1.1.1.1.2-ПОТОМОК2-П2.1 
 (2)
КОРЕНЬ-ПОТОМОК1-ПОТОМОК2-П1.1-П2.1-П1.1.1-
П1.1.2-П1.1.1.1-П1.1.1.1.1-П1.1.1.1.2 
 (3)
КОРЕНЬ-ПОТОМОК1-П1.1-П1.1.1-П1.1.1.1-
П1.1.1.1.1-П1.1.1.1.2-П1.1.2-ПОТОМОК2-П2.1 
Номер 2
Какая из показанных ниже таблиц является результатом запроса?
SELECT EMP_BONUS, EMP_DEPT,
MIN (EMP_BDATE) AS MIN_DDATE,
GROUPING (EMP_BONUS) AS GBON,
GROUPING (EMP_DEPT) AS GDN
FROM EMP
GROUP BY CUBE (EMP_BONUS, EMP_DEPT);
Ответ:
 
(1)
EMP_BONUS | EMP_DEPT | MIN_DDATE | GBON | GDN |
---|
NULL | NULL | NULL | 1 | 1 |
13000.00 | NULL | 1950 | 0 | 1 |
14000.00 | NULL | NULL | 0 | 1 |
15000.00 | NULL | 1950 | 0 | 1 |
16000.00 | NULL | 1970 | 0 | 1 |
17000.00 | NULL | 1950 | 0 | 1 |
18000.00 | NULL | NULL | 0 | 1 |
20000.00 | NULL | 1960 | 0 | 1 |
NULL | NULL | NULL | 0 | 1 |
13000.00 | 1 | 1950 | 0 | 0 |
14000.00 | 1 | NULL | 0 | 0 |
14000.00 | NULL | 1950 | 0 | 0 |
15000.00 | 2 | 1950 | 0 | 0 |
16000.00 | 2 | 1970 | 0 | 0 |
17000.00 | 3 | 1950 | 0 | 0 |
18000.00 | NULL | NULL | 0 | 0 |
20000.00 | 3 | 1960 | 0 | 0 |
NULL | 1 | NULL | 0 | 0 |
NULL | 2 | 1960 | 0 | 0 |
NULL | NULL | 1950 | 0 | 0 |
NULL | 1 | NULL | 1 | 0 |
NULL | 2 | 1950 | 1 | 0 |
NULL | 3 | 1955 | 1 | 0 |
NULL | NULL | NULL | 1 | 0 |
 
 
(2)
EMP_BONUS | EMP_DEPT | MIN_DDATE | GBON | GDN |
---|
NULL | NULL | NULL | 1 | 1 |
13000.00 | NULL | 1950 | 0 | 1 |
14000.00 | NULL | NULL | 0 | 1 |
15000.00 | NULL | 1950 | 0 | 1 |
16000.00 | NULL | 1970 | 0 | 1 |
17000.00 | NULL | 1950 | 0 | 1 |
18000.00 | NULL | NULL | 0 | 1 |
20000.00 | NULL | 1960 | 0 | 1 |
13000.00 | 1 | 1950 | 0 | 0 |
14000.00 | 1 | NULL | 0 | 0 |
14000.00 | NULL | 1950 | 0 | 0 |
15000.00 | 2 | 1950 | 0 | 0 |
16000.00 | 2 | 1970 | 0 | 0 |
17000.00 | 3 | 1950 | 0 | 0 |
20000.00 | 3 | 1960 | 0 | 0 |
NULL | 1 | NULL | 0 | 0 |
NULL | 2 | 1960 | 0 | 0 |
NULL | NULL | 1950 | 0 | 0 |
NULL | 2 | 1950 | 1 | 0 |
NULL | 3 | 1955 | 1 | 0 |
 
 
(3)
EMP_BONUS | EMP_DEPT | MIN_DDATE | GBON | GDN |
---|
NULL | NULL | NULL | 1 | 1 |
13000.00 | NULL | 1950 | 0 | 1 |
14000.00 | NULL | NULL | 0 | 1 |
15000.00 | NULL | 1950 | 0 | 1 |
16000.00 | NULL | 1970 | 0 | 1 |
17000.00 | NULL | 1950 | 0 | 1 |
18000.00 | NULL | NULL | 0 | 1 |
20000.00 | NULL | 1960 | 0 | 1 |
13000.00 | 1 | 1950 | 0 | 0 |
14000.00 | 1 | NULL | 0 | 0 |
15000.00 | 2 | 1950 | 0 | 0 |
16000.00 | 2 | 1970 | 0 | 0 |
17000.00 | 3 | 1950 | 0 | 0 |
20000.00 | 3 | 1960 | 0 | 0 |
NULL | 2 | 1960 | 0 | 0 |
NULL | 2 | 1950 | 1 | 0 |
NULL | 3 | 1955 | 1 | 0 |
 
Номер 3
Какая из приведенных ниже таблиц является результатом рекурсивного запроса?
WITH RECURSIVE PX (Major, Minor, Qty) AS
((SELECT Major, Minor, Qty
FROM PARTS
WHERE PARTS.Major = ‘P1’)
UNION ALL
(SELECT PX.Major, PARTS.Minor,
PX.Qty * PARTS.Qty
FROM PX, PARTS
where pp.Major = px.Minor))
SELECT Major, Minor, Qty FROM PX;
Ответ:
 
(1)
Major | Minor | Qty |
---|
P1 | P2 | 2 |
P1 | P3 | 3 |
P1 | P4 | 2 |
P1 | P3 | 6 |
P1 | P5 | 4 |
P1 | P5 | 24 |
P1 | P6 | 12 |
P1 | P5 | 12 |
P1 | P6 | 6 |
P1 | P3 | 10 |
P1 | P6 | 4 |
P1 | P5 | 40 |
P1 | P6 | 20 |
 
 
(2)
Major | Minor | Qty |
---|
P1 | P2 | 2 |
P1 | P3 | 3 |
P1 | P4 | 2 |
P1 | P5 | 4 |
P1 | P5 | 24 |
P1 | P6 | 12 |
P1 | P5 | 12 |
P1 | P6 | 6 |
P1 | P6 | 4 |
P1 | P5 | 40 |
P1 | P6 | 20 |
 
 
(3)
Major | Minor | Qty |
---|
P1 | P2 | 2 |
P1 | P3 | 3 |
P1 | P4 | 2 |
P1 | P5 | 80 |
P1 | P6 | 42 |
 
Упражнение 3:
Номер 1
Какая из показанных ниже таблиц является результатом запроса?
SELECT EMP_BDATE, EMP_BONUS,
MIN (EMP_SAL) AS MIN_SAL,
GROUPING (EMP_BDATE) AS GBD,
GROUPING (EMP_BONUS) AS GBON
FROM EMP
GROUP BY ROLLUP (EMP_BDATE, EMP_BONUS);
Ответ:
 
(1)
EMP_BDATE | EMP_BONUS | MIN_SAL | GBD | GBON |
---|
NULL | NULL | 13000.00 | 1 | 1 |
1950 | NULL | 13000.00 | 0 | 1 |
1955 | NULL | 16000.00 | 0 | 1 |
1960 | NULL | 14000.00 | 0 | 1 |
1970 | NULL | 17000.00 | 0 | 1 |
NULL | NULL | 15000.00 | 0 | 1 |
1950 | 13000.00 | 15000.00 | 0 | 0 |
1950 | 15000.00 | 16000.00 | 0 | 0 |
1950 | 17000.00 | 18000.00 | 0 | 0 |
1950 | 12000.00 | 13000.00 | 0 | 0 |
1955 | 14000.00 | 16000.00 | 0 | 0 |
1955 | 17000.00 | 18000.00 | 0 | 0 |
1960 | 13000.00 | 16000.00 | 0 | 0 |
1960 | 20000.00 | 22000.00 | 0 | 0 |
1970 | 16000.00 | 17000.00 | 0 | 0 |
NULL | 14000.00 | 15000.00 | 0 | 0 |
NULL | 18000.00 | 19000.00 | 0 | 0 |
 
 
(2)
EMP_BDATE | EMP_BONUS | MIN_SAL | GBD | GBON |
---|
NULL | NULL | 13000.00 | 1 | 1 |
1950 | NULL | 13000.00 | 0 | 1 |
1955 | NULL | 16000.00 | 0 | 1 |
1960 | NULL | 14000.00 | 0 | 1 |
1970 | NULL | 17000.00 | 0 | 1 |
1950 | 13000.00 | 15000.00 | 0 | 0 |
1950 | 15000.00 | 16000.00 | 0 | 0 |
1950 | 17000.00 | 18000.00 | 0 | 0 |
1950 | 12000.00 | 13000.00 | 0 | 0 |
1955 | 14000.00 | 16000.00 | 0 | 0 |
1955 | 17000.00 | 18000.00 | 0 | 0 |
1960 | 13000.00 | 16000.00 | 0 | 0 |
1960 | 20000.00 | 22000.00 | 0 | 0 |
1970 | 16000.00 | 17000.00 | 0 | 0 |
 
 
(3)
EMP_BDATE | EMP_BONUS | MIN_SAL | GBD | GBON |
---|
NULL | NULL | 13000.00 | 1 | 1 |
1950 | NULL | 13000.00 | 0 | 1 |
1955 | NULL | 16000.00 | 0 | 1 |
1960 | NULL | 14000.00 | 0 | 1 |
1970 | NULL | 17000.00 | 0 | 1 |
NULL | NULL | 15000.00 | 0 | 1 |
1950 | 13000.00 | 15000.00 | 0 | 0 |
1950 | 15000.00 | 16000.00 | 0 | 0 |
1950 | 17000.00 | 18000.00 | 0 | 0 |
1950 | 12000.00 | 13000.00 | 0 | 0 |
1950 | NULL | 14000.00 | 0 | 0 |
1955 | 14000.00 | 16000.00 | 0 | 0 |
1955 | 17000.00 | 18000.00 | 0 | 0 |
1960 | NULL | 14000.00 | 0 | 0 |
1960 | 13000.00 | 16000.00 | 0 | 0 |
1960 | 20000.00 | 22000.00 | 0 | 0 |
1960 | NULL | 14000.00 | 0 | 0 |
1970 | 16000.00 | 17000.00 | 0 | 0 |
NULL | 14000.00 | 15000.00 | 0 | 0 |
NULL | 18000.00 | 19000.00 | 0 | 0 |
NULL | NULL | 17000.00 | 0 | 0 |
 
Номер 2
Пусть имеется следующее дерево:
Какой из следующих обходов этого дерева является обходом в глубину?
Ответ:
 (1) КОРЕНЬ-ПОТОМОК1-П1.1-П1.1.1-П1.1.2-ПОТОМОК2-П2.1-П2.1.1-П2.1.1.1-П2.1.1.2-ПОТОМОК3-П3.1-П3.2-П3.3
 
 (2) КОРЕНЬ-ПОТОМОК2-П2.1-П2.1.1-П2.1.1.1-П2.1.1.2-КОРЕНЬ-ПОТОМОК1-П1.1-П1.1.1-П1.1.2-ПОТОМОК3-П3.1-П3.2-П3.3
 
 (3) КОРЕНЬ-ПОТОМОК1-ПОТОМОК2-ПОТОМОК3-П1.1-П2.1-П3.1-П3.2-П3.3- П1.1.1-П1.1.2-П2.1.1-П2.1.1.1-П2.1.1.2
 
Номер 3
Какая из приведенных ниже таблиц является результатом рекурсивного запроса?
WITH RECURSIVE PX (Major, Minor, Qty) AS
((SELECT Major, Minor, Qty
FROM PARTS
WHERE PARTS.Major = ‘P1’)
UNION ALL
(SELECT PX.Major, PARTS.Minor,
PX.Qty * PARTS.Qty
FROM PX, PARTS
where pp.Major = px.Minor))
SEARCH BREADTH
FIRST BY Major, Minor SET ORDER_COLUMN
SELECT Major, Minor, Qty FROM PX
ORDER BY ORDER_COLUMN;?
Ответ:
 
(1)
Major | Minor | Qty |
---|
P1 | P2 | 2 |
P1 | P3 | 3 |
P1 | P3 | 6 |
P1 | P3 | 10 |
P1 | P4 | 2 |
P1 | P5 | 4 |
P1 | P5 | 24 |
P1 | P5 | 12 |
P1 | P5 | 40 |
P1 | P6 | 12 |
P1 | P6 | 6 |
P1 | P6 | 4 |
P1 | P6 | 20 |
 
 
(2)
Major | Minor | Qty |
---|
P1 | P2 | 2 |
P1 | P3 | 10 |
P1 | P3 | 6 |
P1 | P3 | 3 |
P1 | P4 | 2 |
P1 | P5 | 40 |
P1 | P5 | 12 |
P1 | P5 | 24 |
P1 | P5 | 4 |
P1 | P6 | 20 |
P1 | P6 | 4 |
P1 | P6 | 6 |
P1 | P6 | 12 |
 
 
(3)
Major | Minor | Qty |
---|
P1 | P2 | 2 |
P1 | P3 | 3 |
P1 | P4 | 2 |
P1 | P3 | 6 |
P1 | P5 | 4 |
P1 | P5 | 24 |
P1 | P6 | 12 |
P1 | P5 | 12 |
P1 | P6 | 6 |
P1 | P3 | 10 |
P1 | P6 | 4 |
P1 | P5 | 40 |
P1 | P6 | 20 |
 
Упражнение 4:
Номер 1
Какая из показанных ниже таблиц является результатом запроса?
SELECT EMP_SAL, EMP_BONUS,
MAX(EMP_BDATE) AS MAX_BDATE,
GROUPING (EMP_BONUS) AS GBON)
FROM EMP
GROUP BY ROLLUP (EMP_SAL, EMP_BONUS);
Ответ:
 
(1)
EMP_SAL | EMP_BONUS | MAX_BDATE | GBON |
---|
NULL | NULL | NULL | 1 |
13000.00 | NULL | 1950 | 1 |
14000.00 | NULL | 1960 | 1 |
15000.00 | NULL | NULL | 1 |
16000.00 | NULL | 1970 | 1 |
17000.00 | NULL | NULL | 1 |
18000.00 | NULL | 1955 | 1 |
19000.00 | NULL | NULL | 1 |
20000.00 | NULL | 1960 | 1 |
22000.00 | NULL | 1960 | 1 |
NULL | 13000.00 | 1960 | 0 |
NULL | 14000.00 | NULL | 0 |
NULL | 15000.00 | 1950 | 0 |
NULL | 16000.00 | 1970 | 0 |
NULL | 17000.00 | 1955 | 0 |
NULL | 18000.00 | NULL | 0 |
NULL | 20000.00 | 1960 | 0 |
13000.00 | 12000.00 | 1950 | 0 |
15000.00 | 13000.00 | 1950 | 0 |
15000.00 | 14000.00 | NULL | 0 |
16000.00 | 14000.00 | 1955 | 0 |
16000.00 | 13000.00 | 1960 | 0 |
16000.00 | 15000.00 | 1950 | 0 |
17000.00 | 16000.00 | 1970 | 0 |
18000.00 | 17000.00 | 1955 | 0 |
19000.00 | 18000.00 | NULL | 0 |
22000.00 | 20000.00 | 1960 | 0 |
 
 
(2)
EMP_SAL | EMP_BONUS | MAX_BDATE | GBON |
---|
NULL | NULL | NULL | 1 |
13000.00 | NULL | 1950 | 1 |
14000.00 | NULL | 1960 | 1 |
15000.00 | NULL | NULL | 1 |
16000.00 | NULL | 1970 | 1 |
17000.00 | NULL | NULL | 1 |
18000.00 | NULL | 1955 | 1 |
19000.00 | NULL | NULL | 1 |
20000.00 | NULL | 1960 | 1 |
22000.00 | NULL | 1960 | 1 |
NULL | 13000.00 | 1960 | 0 |
NULL | 15000.00 | 1950 | 0 |
NULL | 16000.00 | 1970 | 0 |
NULL | 17000.00 | 1955 | 0 |
NULL | 20000.00 | 1960 | 0 |
13000.00 | 12000.00 | 1950 | 0 |
15000.00 | 13000.00 | 1950 | 0 |
16000.00 | 14000.00 | 1955 | 0 |
16000.00 | 13000.00 | 1960 | 0 |
16000.00 | 15000.00 | 1950 | 0 |
17000.00 | 16000.00 | 1970 | 0 |
18000.00 | 17000.00 | 1955 | 0 |
22000.00 | 20000.00 | 1960 | 0 |
 
 
(3)
EMP_SAL | EMP_BONUS | MAX_BDATE | GBON |
---|
NULL | NULL | NULL | 1 |
13000.00 | NULL | 1950 | 1 |
14000.00 | NULL | 1960 | 1 |
15000.00 | NULL | NULL | 1 |
16000.00 | NULL | 1970 | 1 |
17000.00 | NULL | NULL | 1 |
18000.00 | NULL | 1955 | 1 |
19000.00 | NULL | NULL | 1 |
20000.00 | NULL | 1960 | 1 |
22000.00 | NULL | 1960 | 1 |
NULL | 13000.00 | 1960 | 0 |
NULL | 14000.00 | NULL | 0 |
NULL | 15000.00 | 1950 | 0 |
NULL | 16000.00 | 1970 | 0 |
NULL | 17000.00 | 1955 | 0 |
NULL | 18000.00 | NULL | 0 |
NULL | 20000.00 | 1960 | 0 |
13000.00 | 12000.00 | 1950 | 0 |
14000.00 | NULL | 1960 | 0 |
15000.00 | 13000.00 | 1950 | 0 |
15000.00 | 14000.00 | NULL | 0 |
16000.00 | 14000.00 | 1955 | 0 |
16000.00 | 13000.00 | 1960 | 0 |
16000.00 | 15000.00 | 1950 | 0 |
17000.00 | NULL | NULL | 0 |
17000.00 | 16000.00 | 1970 | 0 |
18000.00 | 17000.00 | 1955 | 0 |
19000.00 | 18000.00 | NULL | 0 |
22000.00 | 20000.00 | 1960 | 0 |
 
Номер 2
Какая из следующих последовательностей является монотонной (n = {1, 2, …, …}
)?
Ответ:
 (1) Последовательность k
, где k = 2-n
 
 (2) Последовательность k
, где k = (-1)n
 
 (3) Последовательность k
, где k = (n+2)/(n+1)
 
Номер 3
Какая из приведенных ниже таблиц является результатом рекурсивного запроса?
WITH RECURSIVE PX (Major, Minor, Qty) AS
((SELECT Major, Minor, Qty
FROM PARTS
WHERE PARTS.Major = ‘P1’)
UNION ALL
(SELECT PX.Major, PARTS.Minor,
PX.Qty * PARTS.Qty
FROM PX, PARTS
where pp.Major = px.Minor))
SEARCH DEPTH FIRST BY Major,
Minor SET ORDER_COLUMN
SELECT Major, Minor, Qty FROM PX
ORDER BY ORDER_COLUMN;
Ответ:
 
(1)
Major | Minor | Qty |
---|
P1 | P2 | 2 |
P1 | P3 | 3 |
P1 | P3 | 6 |
P1 | P3 | 10 |
P1 | P4 | 2 |
P1 | P5 | 24 |
P1 | P5 | 4 |
P1 | P5 | 12 |
P1 | P5 | 40 |
P1 | P6 | 12 |
P1 | P6 | 6 |
P1 | P6 | 20 |
P1 | P6 | 4 |
 
 
(2)
Major | Minor | Qty |
---|
P1 | P2 | 2 |
P1 | P3 | 10 |
P1 | P3 | 6 |
P1 | P3 | 3 |
P1 | P4 | 2 |
P1 | P5 | 40 |
P1 | P5 | 12 |
P1 | P5 | 4 |
P1 | P5 | 24 |
P1 | P6 | 4 |
P1 | P6 | 20 |
P1 | P6 | 6 |
P1 | P6 | 12 |
 
 
(3)
Major | Minor | Qty |
---|
P1 | P2 | 2 |
P1 | P3 | 3 |
P1 | P3 | 6 |
P1 | P3 | 10 |
P1 | P4 | 2 |
P1 | P5 | 4 |
P1 | P5 | 24 |
P1 | P5 | 12 |
P1 | P5 | 40 |
P1 | P6 | 12 |
P1 | P6 | 6 |
P1 | P6 | 4 |
P1 | P6 | 20 |
 
Упражнение 5:
Номер 1
Какая из показанных ниже таблиц является результатом запроса?
SELECT EMP_BONUS, EMP_DEPT,
MIN (EMP_BDATE) AS MIN_DDATE,
GROUPING (EMP_BONUS) AS GBON,
GROUPING (EMP_DEPT) AS GDN
FROM EMP
GROUP BY ROLLUP (EMP_BONUS, EMP_DEPT);
Ответ:
 
(1)
EMP_BONUS | EMP_DEPT | MIN_DDATE | GBON | GDN |
---|
NULL | NULL | NULL | 1 | 1 |
13000.00 | NULL | 1950 | 0 | 1 |
14000.00 | NULL | NULL | 0 | 1 |
15000.00 | NULL | 1950 | 0 | 1 |
16000.00 | NULL | 1970 | 0 | 1 |
17000.00 | NULL | 1950 | 0 | 1 |
18000.00 | NULL | NULL | 0 | 1 |
20000.00 | NULL | 1960 | 0 | 1 |
NULL | NULL | NULL | 0 | 1 |
13000.00 | 1 | 1950 | 0 | 0 |
14000.00 | 1 | NULL | 0 | 0 |
14000.00 | NULL | 1950 | 0 | 0 |
15000.00 | 2 | 1950 | 0 | 0 |
16000.00 | 2 | 1970 | 0 | 0 |
17000.00 | 3 | 1950 | 0 | 0 |
18000.00 | NULL | NULL | 0 | 0 |
20000.00 | 3 | 1960 | 0 | 0 |
NULL | 1 | NULL | 0 | 0 |
NULL | 2 | 1960 | 0 | 0 |
NULL | NULL | 1950 | 0 | 0 |
 
 
(2)
EMP_BONUS | EMP_DEPT | MIN_DDATE | GBON | GDN |
---|
NULL | NULL | NULL | 1 | 1 |
13000.00 | NULL | 1950 | 0 | 1 |
14000.00 | NULL | NULL | 0 | 1 |
15000.00 | NULL | 1950 | 0 | 1 |
16000.00 | NULL | 1970 | 0 | 1 |
17000.00 | NULL | 1950 | 0 | 1 |
18000.00 | NULL | NULL | 0 | 1 |
20000.00 | NULL | 1960 | 0 | 1 |
13000.00 | 1 | 1950 | 0 | 0 |
14000.00 | 1 | NULL | 0 | 0 |
14000.00 | NULL | 1950 | 0 | 0 |
15000.00 | 2 | 1950 | 0 | 0 |
16000.00 | 2 | 1970 | 0 | 0 |
17000.00 | 3 | 1950 | 0 | 0 |
20000.00 | 3 | 1960 | 0 | 0 |
NULL | 1 | NULL | 0 | 0 |
NULL | 2 | 1960 | 0 | 0 |
NULL | NULL | 1950 | 0 | 0 |
 
 
(3)
EMP_BONUS | EMP_DEPT | MIN_DDATE | GBON | GDN |
---|
NULL | NULL | NULL | 1 | 1 |
13000.00 | NULL | 1950 | 0 | 1 |
14000.00 | NULL | NULL | 0 | 1 |
15000.00 | NULL | 1950 | 0 | 1 |
16000.00 | NULL | 1970 | 0 | 1 |
17000.00 | NULL | 1950 | 0 | 1 |
18000.00 | NULL | NULL | 0 | 1 |
20000.00 | NULL | 1960 | 0 | 1 |
NULL | NULL | NULL | 0 | 1 |
13000.00 | 1 | 1950 | 0 | 0 |
14000.00 | 1 | NULL | 0 | 0 |
14000.00 | NULL | 1950 | 0 | 0 |
14000.00 | NULL | 1955 | 0 | 0 |
15000.00 | 2 | 1950 | 0 | 0 |
15000.00 | NULL | 1960 | 0 | 0 |
16000.00 | 2 | 1970 | 0 | 0 |
17000.00 | 3 | 1950 | 0 | 0 |
18000.00 | 2 | 1960 | 0 | 0 |
18000.00 | NULL | NULL | 0 | 0 |
20000.00 | 3 | 1960 | 0 | 0 |
NULL | 1 | NULL | 0 | 0 |
NULL | 2 | 1960 | 0 | 0 |
NULL | NULL | 1950 | 0 | 0 |
 
Номер 2
Пусть имеется следующее дерево:
Какой из следующих обходов этого дерева является обходом в глубину?
Ответ:
 (1)
КОРЕНЬ-ПОТОМОК1-ПОТОМОК2-П2.1-П2.1.1-
П2.2-ПОТОМОК3-П3.1-П3.1.1-П3.1.2 
 (2)
КОРЕНЬ- ПОТОМОК3-П3.1-П3.1.2-П3.1.1-
ПОТОМОК2-П2.2-П2.1-П2.1.1-ПОТОМОК1 
 (3)
КОРЕНЬ-ПОТОМОК2-П2.1-П2.1.1-ПОТОМОК3-П3.1-
П3.1.1-П3.1.2-П2.2-ПОТОМОК1 
Номер 3
Какая из следующих последовательностей является монотонной?
Ответ:
 (1) ‘a’, ‘b’, … ‘z’, ‘aa’, ‘ab’, …,
‘az’, …, ‘a..aa’, ‘a..ab’, …, ‘a..az’, … 
 (2) ‘a’, ‘b’, … ‘z’, ‘za’, ‘zb’, …, ‘zz’, …,
‘z..za’, ‘z..zb’, …, ‘z..zz’, … 
 (3) ‘a’, ‘ba’, ‘aba’,
‘baba’, ‘ababa’, …