Команда для формирования набора данных без повторяющихся строк. Создание и заполнение таблиц с использованием визуального редактора БД Emma 0.6, страница 3

|    49 |      5 | 0000-00-00 |          16 |           6 |

|    50 |      5 | 0000-00-00 |          16 |           7 |

|    51 |      3 | 0000-00-00 |          18 |           2 |

|    52 |      5 | 0000-00-00 |          18 |           1 |

|    53 |      5 | 0000-00-00 |          18 |           6 |

|    54 |      5 | 0000-00-00 |          18 |           7 |

|    56 |      4 | 0000-00-00 |          19 |           2 |

|    57 |      5 | 0000-00-00 |          19 |           1 |

|    58 |      4 | 0000-00-00 |          19 |           5 |

|    60 |      5 | 0000-00-00 |          20 |           2 |

|    61 |      5 | 0000-00-00 |          20 |           1 |

|    62 |      5 | 0000-00-00 |          20 |           5 |

|    63 |      4 | 0000-00-00 |          21 |           2 |

|    64 |      5 | 0000-00-00 |          21 |           1 |

|    65 |      5 | 0000-00-00 |          21 |           5 |

|    66 |      2 | 0000-00-00 |          22 |           2 |

|    67 |      4 | 0000-00-00 |          22 |           1 |

|    68 |      4 | 0000-00-00 |          22 |           6 |

|    69 |      3 | 0000-00-00 |          22 |           7 |

|    70 |      5 | 0000-00-00 |          23 |           2 |

|    71 |      4 | 0000-00-00 |          23 |           1 |

|    72 |      4 | 0000-00-00 |          23 |           4 |

|    73 |      5 | 0000-00-00 |          24 |           2 |

|    74 |      5 | 0000-00-00 |          24 |           3 |

|    75 |      3 | 0000-00-00 |          24 |           1 |

+-------+--------+------------+-------------+-------------+

65 rows in set (0,00 sec)

Запросы выборки:

Выборка всех абитуриентов по специальностям:

mysql> SELECT spec.nazv AS Specialnost,

-> stud.fio AS FIO

-> FROM spec,stud

-> WHERE spec.idspec=stud.spec_idspec

-> ORDER BY spec.idspec;

+----------------+--------------+

| Specialnost    | FIO          |

+----------------+--------------+

| radiofizika    | magadova     |

| radiofizika    | pavlova      |

| radiofizika    | saharova     |

| radiofizika    | smirnova     |

| radiofizika    | nazarov      |

| bio-himia      | tarasov      |

| bio-himia      | udalov       |

| bio-himia      | cukerman     |

| bio-himia      | zilbershtein |

| bio-himia      | sidorov      |

| inf tehnologii | abakumov     |

| inf tehnologii | habarova     |

| inf tehnologii | sergeev      |

| inf tehnologii | taranova     |

| inf tehnologii | obarin       |

| jurnalistika   | petrov       |

| jurnalistika   | novikova     |

| jurnalistika   | sineva       |

| jurnalistika   | ivanov       |

| jurnalistika   | sherbakova   |

+----------------+--------------+

20 rows in set (0,00 sec)

Подсчет среднего балла по дисциплинам:

mysql> SELECT pred.nazv, AVG(ekz.ocenka)

-> FROM pred,ekz

-> WHERE ekz.pred_idpred=pred.idpred

-> GROUP BY pred.idpred;

+------+-----------------+

| nazv | AVG(ekz.ocenka) |

+------+-----------------+

| mat  |          4.2000 |

| rus  |          3.9500 |

| lit  |          4.8000 |

| inf  |          4.8000 |

| fiz  |          4.6000 |

| him  |          4.8000 |

| biol |          4.6000 |

+------+-----------------+

7 rows in set (0,00 sec)

Определить специальность, на которой максимальное количество абитуриентов имеют средний бал ниже, чем минимальный средний бал по дисциплинам данной специальности.

SELECT MAX(cnt_studs) AS Kolichestvo, spec.nazv FROM

-> (SELECT COUNT(sp_spec) AS cnt_studs, sp_spec AS spc

-> FROM

->   (SELECT AVG(ekz.ocenka) AS st_a_oc, stud.spec_idspec AS st_spec

->   FROM ekz,stud

->   WHERE stud.idstud=ekz.stud_idstud

->   GROUP BY ekz.stud_idstud) AS a_stud,

->

->   (SELECT AVG(ekz.ocenka) AS sp_a_oc, stud.spec_idspec AS sp_spec

->   FROM ekz,stud

->   WHERE stud.idstud=ekz.stud_idstud

->   GROUP BY stud.spec_idspec) AS a_spec

-> WHERE st_spec=sp_spec AND st_a_oc<sp_a_oc

-> GROUP BY sp_spec) AS t3, spec

-> WHERE spec.idspec=spc;

+-------------+-------------+

| Kolichestvo | nazv        |

+-------------+-------------+

|           2 | radiofizika |

+-------------+-------------+

1 row in set (0,00 sec)