МИНИСТЕРСТВО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ

Московский государственный институт электроники и математики

(Технический университет)

Кафедра вычислительных систем и сетей

 

ИЗУЧЕНИЕ ОСНОВ ЯЗЫКА SQL

Методические указания к лабораторным работам по курсу "Базы данных"

И.П. Карпова, канд. техн. наук

 

Содержание

ЦЕЛИ И ЗАДАЧИ РАБОТ *

1. ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ *

1.1. Общие положения *

1.2. Реляционная модель данных *

1.3. Операции реляционной алгебры *

1.3.1 Основные операции реляционной алгебры *

1.3.2. Вспомогательные операции реляционной алгебры *

1.4. Введение в язык SQL *

1.4.1. Создание отношений *

1.4.2. Команды модификации данных *

1.4.3. Извлечение данных из отношений *

1.4.4. Работа с представлениями *

1.4.5. Удаление объектов базы данных *

1.4.6. NULL-значения *

1.4.7. Подзапросы *

1.4.8. Сложные запросы *

2. ВЫПОЛНЕНИЕ ЛАБОРАТОРНЫХ РАБОТ *

3. ВАРИАНТЫ ЗАДАНИЙ К ЛАБОРАТОРНЫМ РАБОТАМ *

Библиографический список *

ЦЕЛИ И ЗАДАЧИ РАБОТ

Цель выполнения лабораторных работ – изучение основ языка SQL и получение практических навыков работы с реляционными базами данных. Выполнение работ включает создание фрагмента базы данных, заполнение его данными, формирование запросов на SQL и создание представлений.

1. ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

1.1. Общие положения

SQL (Structured Query Language) – это структурированный язык запросов к реляционным базам данных (БД). SQL является декларативным языком, основанным на операциях реляционной алгебры.

Существуют два стандарта SQL, определённые американским национальным институтом стандартов (ANSI): SQL-89 (SQL-1) и SQL-92 (SQL-2). В настоящее время разрабатывается новый стандарт – SQL-3.

Большинство коммерческих систем управления базами данных (СУБД) поддерживают стандарт SQL-92, который принят ISO (International Standards Organization) в качестве международного стандарта. Многие версии имеют свои отличия, которые касаются, в основном, синтаксиса.

1.2. Реляционная модель данных

Базы данных, основанные на реляционной модели данных (РМД), являются в настоящее время наиболее широко распространёнными вследствие своей простоты и универсальности методов обработки данных.

В основе РМД лежит понятие отношения, представляющего собой подмножество декартова произведения доменов. Домен – это некоторое множество значений, которое может принимать элемент (например, множество целых чисел, множество дат и т.п.).

Пусть D1, D2 ,…, Dk – произвольные конечные и не обязательно различные множества (домены). Декартовым произведением этих множеств

D = D1 ґ D2 ґ ...ґ Dk,

называется множество последовательностей вида

{d1, d2,..., dk},

где d1 О D1, d2 О D2, …, dk О Dk. Т.о., декартово произведение позволяет получить все возможные комбинации элементов исходных множеств.

Пример. Декартово произведение для доменов D1 = (1,2), D2 = (A,B,C):

D = {(1,A),(1,B),(1,C),(2,A),(2,B),(2,C)}.

Элементы отношения называют кортежами, элементы кортежа – атрибутами (полями). Длина кортежа (количество атрибутов) определяет арность отношения, количество кортежей – мощность отношения.

Каждое отношение хранит данные об одном типе объекта (сущности) предметной области, причём один кортеж отношения содержит данные об одном экземпляре объекта данного типа.

Отношение обладает двумя важными свойствами:

  1. В отношении не должно быть одинаковых кортежей, т.к. это множество.

  2. Порядок кортежей в отношении несущественен.

Отношение удобно представлять как таблицу, где строка является кортежем, а столбец соответствует домену (рис. 1). Столбцам в такой таблице назначают имена и обращаются к ним по имени.

домен 1 (ключ) . . . . . . . . . . . . .домен 2 . . . . . . . . домен 3 . . . . . . . . домен 4

№ зачётной книжки

ФИО студента

Группа

Дата рождения

С–12201

Белый Сергей Юрьевич

С–12

12.05.1984

С-12202

Юдина Елена Павловна

С–31

22.03.1982

…

С-14401

Федин Юрий Ильич

С–14

06.11.1984

Рис.1. Пример табличной формы представления отношения

Несколько атрибутов отношения могут быть определены на одном и том же домене (например, год рождения и год смерти). Каждый атрибут принадлежит к определённому типу данных и характеризуется размером памяти, выделяемой под его хранение. Описание совокупности атрибутов отношения с их типами и размерами называется схемой отношения.

На атрибут (группу атрибутов) могут накладываться ограничения целостности, т.е. правила, которым должно соответствовать значение атрибута (или соотношение значений атрибутов).

Атрибут (группа атрибутов), значения которого являются уникальными в рамках отношения, идентифицируют кортеж и называются потенциальными ключами. Если ключ состоит из нескольких атрибутов, он называется составным. Ключей может быть несколько; основным является первичный ключ, его значения не могут обновляться. Отношение может иметь только один первичный ключ (ПК); его значения обязательны для кортежа.

Данные в отношениях обрабатываются с помощью операций реляционной алгебры.

1.3. Операции реляционной алгебры

Операции реляционной алгебры (РА) применимы к реляционным отношениям. Результатом выполнения операции реляционной алгебры также является отношение, построенное на основе одного или более исходных отношений. Существует пять основных операций РА и три вспомогательных, которые могут быть выражены через основные.

1.3.1 Основные операции реляционной алгебры

  1. Проекция (projection).

    2. Это унарная операция (выполняемая над одним отношением), служащая для выбора подмножества атрибутов из отношения R. Она уменьшает арность отношения и может уменьшить мощность отношения за счёт исключения одинаковых кортежей.

    Пример 1. Пусть имеется отношение R(A,B,C) (рис.2,а).

    Тогда проекция p A,C(R) будет такой, как показано на рис.2,б.

    а) Отношение R

    A

    B

    C

    a

    b

    c

    c

    a

    d

    c

    b

    d

    б) Проекция p A,C(R)

    A

    C

    a

    c

    c

    d

    Рис.2. Пример проекции отношения

  2. Селекция (selection).

    3. Это унарная операция, результатом которой является подмножество кортежей исходного отношения, соответствующих условиям, которые накладываются на значения определённых атрибутов.

    Пример 2. Для отношения R(A,B,C) (рис. 3,а) селекция s C=d(R) (при условии "значение атрибута C равно d") будет такой (рис. 3,б):

    а) Отношение R

    A

    B

    C

    a

    b

    c

    c

    a

    d

    c

    b

    d

    б) Селекция s C=d(R)

    A

    B

    C

    c

    a

    d

    c

    b

    d

    Рис.3. Пример селекции отношения

  3. Декартово произведение (Cartesian product).

    4. Это бинарная операция над разносхемными отношениями, соответствующая определению декартова произведения для РМД.

    Пример 3. Пусть имеются отношение R(A,B) и отношение S(C,D,E) (рис.4,а). Тогда декартово произведение Rґ S будет таким (рис.4,б):

    а) Исходные отношения

    A

    B

    a

    B

    c

    A

    b

    D

    C

    D

    E

    g

    h

    a

    a

    b

    c

    б) Декартово произведение

    A

    B

    C

    D

    E

    a

    b

    g

    h

    a

    a

    b

    a

    b

    c

    c

    a

    g

    h

    a

    c

    a

    a

    b

    c

    b

    d

    g

    h

    a

    b

    d

    a

    b

    c

    Рис.4. Пример декартова произведения отношений

  4. Объединение (union).

    5. Объединением двух односхемных отношений R и S называется отношение T = R U S, которое включает в себя все кортежи обоих отношений без повторов.

  5. Разность (set difference).

    6. Разностью односхемных отношений R и S называется множество кортежей R, не входящих в S.

    Пример 4. Пусть имеются отношение R(A,B,C) и отношение S(A,B,C) (рис.5,а). Тогда разность R–S будет такой (рис.5,б):

    а) Исходные отношения

    A

    B

    C

    a

    b

    c

    c

    a

    d

    c

    h

    c

    A

    B

    C

    g

    h

    a

    a

    b

    c

    h

    d

    d

    б)

    A

    B

    C

    c

    a

    d

    c

    h

    c

    Рис.5. Пример разности отношений

    1.3.2. Вспомогательные операции реляционной алгебры

  6. Пересечение (intersection).

    7. Пересечение двух односхемных отношений R и S есть подмножество кортежей, принадлежащих обоим отношениям. Это можно выразить через разность:

    R ∩ S = R – (R – S).

  7. Соединение (join).

    8. Эта операция определяет подмножество декартова произведения двух разносхемных отношений. Кортеж декартова произведения входит в результирующее отношение, если для атрибутов разных исходных отношений выполняется некоторое условие. Если условием является равенство атрибутов исходных отношений, такая операция называется эквисоединением. Естественным называется эквисоединение по одинаковым атрибутам исходных отношений.

    Пример 5. Пусть имеются отношения R(A,B,C) и S(A,D,E) (рис.6,а). Тогда естественное соединение
    R    > < S будет таким, как показано на рис.6,б.

    а) Исходные отношения

    A

    B

    C

    a

    b

    c

    c

    a

    d

    c

    h

    c

    g

    b

    d

    A

    D

    E

    g

    h

    a

    c

    b

    c

    h

    d

    d

    б) Соединение отношений

    A

    B

    C

    D

    E

    c

    a

    d

    b

    c

    c

    h

    c

    b

    c

    g

    b

    d

    h

    a

    Рис.6. Пример естественного соединения отношений

  8. Деление (division).

Пусть отношение R содержит атрибуты {r1,r2,...,ri,...,rn}, а отношение S – атрибуты {r1,r2,...,ri}. Результирующее отношение содержит атрибуты {ri+1,...,rn}. Кортеж включается в результирующее отношение, если его декартово произведение с каким-либо кортежем отношения S входит в R.

Пример 6. Пусть имеются отношения R(A,B,C) и S(A,B) (рис. 7,а). Тогда частное R/S будет таким как показано на рис. 7,б.

а) Исходные отношения

A

B

C

D

a

b

c

f

c

b

a

b

g

h

d

c

c

b

b

c

A

B

g

h

c

b

b

a

б) Частное

C

D

d

c

a

b

b

c

Рис.7. Пример операции деления

 

1.4. Введение в язык SQL

Язык работы с базами данных должен предоставлять пользователям следующие возможности:

Для реализации этих функций SQL включает три группы средств:

По стандарту ANSI DCL является частью DDL.

Синтаксис команд и примеры, рассмотренные в данном пособии, соответствуют синтаксису СУБД Sybase.

В командах SQL не различаются прописные и строчные буквы (за исключением строчных литералов). Каждая команда заканчивается символом ';'. Значения параметров по умолчанию выделено подчеркиванием, например, ALL.

Примем следующие обозначения для описания синтаксиса:

{} – содержимое скобок рассматривается как единое целое для остальных символов;

| – заменяет слово ИЛИ;

[] – содержимое этих скобок является необязательным;

… – всё, что предшествует этим символам, может повторяться произвольное число раз;

.,.. – всё, что предшествует этим символам, может повторяться произвольное число раз, каждое вхождение отделяется запятой.

1.4.1. Создание отношений

Создание нового отношения (таблицы) выполняется с помощью команды DDL CREATE TABLE. Команда CREATE TABLE используется для описания новой таблицы, её атрибутов (полей) и ограничений целостности. Упрощённый синтаксис этой команды:

CREATE TABLE <имя таблицы>
( {<имя поля> <тип данных> [(<размер>)]
[<ограничения целостности поля>…]} .,..
[, <ограничения целостности таблицы>.,..] );

Расшифровка элементов описания приведена в табл. 1.

Для обязательных полей устанавливается ограничение not null. Это означает, что при изменении значения этого поля или при добавлении новых записей таблицы это поле должно содержать допустимое значение. Ограничение not null можно наложить на поле только один раз, иначе возникает ошибка.

Таблица 1. Описание команды CREATE TABLE

Элемент

Описание

<имя поля>

Имя поля (столбца) таблицы, обычный идентификатор.

<тип данных>

Тип данных поля. Можно использовать одно из значений:

– INTEGER, INT, SMALLINT – целые числа;

– NUMERIC[(длина [, точность])], DECIMAL[(длина [, точность])] – числа с фиксированной запятой;

– FLOAT, REAL, DOUBLE – вещественные числа;

– CHAR[(длина)], VARCHAR(длина) – символьные строки фиксированной и переменной длины;

– DATE – дата; TIME – время.

<размер>

Размер поля в символах (для текста и чисел).

<ограничения целостности>

Можно использовать следующие ограничения:

– PRIMARY KEY – первичный ключ (обязательный и уникальный);

– UNIQUE – уникальное значение поля в пределах столбца таблицы;

– [NOT] NULL – [не] возможность не указывать значение поля;

– CHECK (<условие>) – проверка условия для поля (полей);

– DEFAULT <выражение> – задание значения поля по умолчанию;

– REFERENCES <имя таблицы> [(<имя столбца>)] – внешний ключ.

<ограничения целостности таблицы>

То же, что и для поля. Дополнительно используется:

FOREIGN KEY [(<список полей>.,..)] REFERENCES
<имя таблицы> [(<список полей>)] – внешний ключ.

Примеры создания таблиц:

  1. Таблица "Отделы" с полями "Номер отдела" (ПК), "Название отдела":

    2. create table depart
    ( depno numeric(2) primary key,
    name char(30) not null);

  2. Таблица "Сотрудники" с полями "Номер отдела" (внешний ключ), "Табельный номер сотрудника" (ПК), "ФИО сотрудника", "Должность", "Оклад", "Дата рождения", "Телефон":

    3. create table emp
    ( depno numeric(2) references depart,
    tabno char(3) primary key,
    name char(40) not null,
    post char(20) not null,
    salary numeric(7,2) not null,
    born data not null,
    tel char(9));

  3. Таблица "Дети сотрудников" с полями "Табельный номер родителя" (внешний ключ), "Имя ребенка", "Пол", "Дата рождения":

create table children
( tabno char(3) references emp(tabno),
name char(20) not null,
sex char(1),
born date,
primary key(tabno, name), /* составной первичный ключ*/
check (sex in (‘м’, ‘ж’)));

Обратите внимание:

create table tab
( id numeric(6) primary key,
class numeric(3),
fdate date,
group char(6),
foreign key (class, fdate) references exam(class, fdate));

1.4.2. Команды модификации данных

К командам модификации данных (DML) относятся добавление, удаление и изменение (обновление) кортежа (записи).

INSERT – добавление записи в таблицу. Синтаксис:

INSERT INTO <имя таблицы> [(<имя поля>.,..)]
VALUES (<список выражений>) | <запрос>;

Под <запросом> подразумевается команда SELECT (см. ниже), результаты работы которой добавляются в указанную таблицу.

В предложении VALUES указываются выражения, порождающие значения атрибутов новой записи таблицы. Типы значений выражений должны соответствовать типам полей таблицы. Если значения устанавливаются не для всех полей или порядок значений не соответствует тому порядку полей, который был установлен при создании таблицы, то после имени таблицы в скобках приводится список полей в соответствии со списком значений. Если в списке полей не указано обязательное поле таблицы (not null), то ему будет присвоено значение по умолчанию (default), если оно определено в командах CREATE TABLE или ALTER TABLE. (Подробнее о NULL-значениях см. п. 1.4.6).

Если в списке значений отсутствует хотя бы одно обязательное поле или нарушаются другие ограничения целостности, например, уникальность значения, то команда INSERT будет отвергнута.

Пример: Добавить в таблицу "Сотрудники" новую запись:

insert into emp

values(3, '112', 'Попов В.Г.', 'экономист', 400*13.5, '1979–12–23', '5–34–11');

UPDATE – обновление данных в таблице. Синтаксис:

UPDATE <имя таблицы>
SET {<имя поля> = <выражение>}.,..
[WHERE <условие>];

Запрос на обновление изменяет в указанной таблице значения указанных полей тех записей, которые удовлетворяют заданному условию отбора (where <условие>). Если условие не указано, обновляются все записи таблицы.

Пример: Изменить должность и зарплату сотрудника Попова В.Г., табельный номер 112:

update emp
set post = 'ст. экономист', salary = salary+1000
where tabno = '112';

DELETE – удаление записей из таблицы. Синтаксис этой команды:

delete from <имя таблицы> [ where <условие> ];

Внимание! Если в команде DELETE не указывать условие выбора записей, то все записи таблицы будут удалены без предупреждения и без запроса на подтверждение!

Пример: Удалить запись о сотруднике Попове В.Г., табельный номер 112:

delete from emp where tabno = '112';

1.4.3. Извлечение данных из отношений

Извлечение данных из отношений выполняется с помощью команды SELECT (селекция). Эта команда не изменяет данные в БД.

Результатом выполнения команды SELECT является временное отношение, которое помещается в курсор (специальную область памяти СУБД) и обычно сразу выводится на экран. Синтаксис этой команды:

SELECT * | { [ ALL | DISTINCT ] <список выбора>.,..}
FROM {<имя таблицы> [<алиас>] }.,..
[ WHERE <условие>]
[ GROUP BY {<имя поля> | <целое>}.,.. [ HAVING <условие>] ]
[ ORDER BY {<имя поля> | <целое> [ ASC | DESC ] }.,..]
[ UNION [ALL] SELECT …];

Расшифровка элементов описания приведена в табл. 2.

Таблица 2. Элементы команды SELECT

Элемент

Описание

<список выбора>

Список элементов, разделённых запятыми. Элемент списка выбора – выражение и необязательный алиас. Выражение может включать имена полей, знаки операций, вызовы функций и константы.

<имя таблицы>

Имя или синоним имени таблицы или представления.

<алиас>

Временный синоним имени таблицы, определённый только внутри запроса.

<условие>

Условие, которое может быть истинным или ложным для каждого поля или комбинации полей из таблицы (таблиц), определённых предложением FROM.

<имя поля>

Имя поля (столбца) таблицы.

<целое>

Число без десятичной точки. Номер поля в <списке полей>.

DISTINCT – предикат удаления из результирующего отношения повторяющихся кортежей.

ALL – предикат, обратный к DISTINCT (используется по умолчанию).

Рассмотрим основные предложения команды SELECT:

SELECT – после этого ключевого слова указывается список выбора – список выражений, которые будут образовывать результирующее отношение. Выражению можно сопоставить временный синоним (алиас), который будет названием поля результирующего отношения, например:

sal*0.87+bonus as salary

Если надо вывести все поля из тех отношений, к которым обращается данный запрос, можно указать символ * (если в отношениях нет полей с одинаковыми именами). В этом случае сначала будут выведены поля таблицы, стоящей первой в предложении FROM, затем – второй и т.д. Поля, относящиеся к одной таблице, будут выводиться в том порядке, в каком они были записаны при создании таблицы.

FROM – в этом предложении указывается имя таблицы (имена таблиц), в которой будет производиться поиск.

WHERE – содержит условия выбора отдельных записей.

GROUP BY – группирует записи по значению одного или нескольких полей. Каждой группе в результирующем отношении соответствует одна запись.

HAVING – позволяет указать условия выбора для групп записей. Может использоваться только после group by.

ORDER BY – упорядочивает результирующие записи по значению одного или нескольких полей: ASC – по возрастанию, DESC – по убыванию.

Порядок выполнения операции SELECT такой:

  1. Выбор из указанной таблицы тех записей, которые удовлетворяют условию отбора (where).

  2. Группировка полученных записей (group by).

  3. Выбор тех групп, которые удовлетворяют условию отбора (having).

  4. Сортировка записей в указанном порядке (order by).

  5. Извлечение из записей полей, заданных в списке выбора, и формирование результирующего отношения.

Если во фразе FROM указаны две и более таблицы, то эта последовательность действий выполняется для декартова произведения указанных таблиц.

Отношения для примеров приведены в таблицах 3-5.

Таблица 3. Отношение "Сотрудники" (Emp)

TabNo

DepNo

Name

Post

Salary

Born

Tel

988

1

Рюмин В.П.

начальник отдела

4850.0

01.02.60

5-26-12

909

1

Серова Т.В.

вед. программист

4850.0

20.10.71

5-91-19

100

2

Волков Л.Д.

программист

4650.0

16.10.72

null

034

3

Петрова К.В.

секретарь

3200.4

24.04.58

null

110

2

Буров Г.О.

бухгалтер

4588.5

22.05.65

5-46-32

023

2

Малова Л.А.

гл. бухгалтер

4924.0

24.11.54

4-24-55

002

3

Сухов К.А.

начальник отдела

4850.0

18.06.48

5-12-69

Таблица 4. Отношение "Отделы" (Depart)

DepNo

Name

2

Бухгалтерия

3

Отдел кадров

4

Отдел технического контроля

1

Плановый отдел

Таблица 5.Отношение "Дети"(Children)

TabNo

Name

Born

Sex

988

Вадим

03.05.85

м

110

Ольга

18.07.91

ж

023

Илья

19.02.77

м

023

Анна

26.12.79

ж

909

Инна

25.01.99

ж

Примеры:

  1. Выбрать все записи из таблицы "Отделы":

    2. select * from depart;

    DepNo

    Name

    2

    Бухгалтерия

    3

    Отдел кадров

    4

    Отдел технического контроля

    1

    Плановый отдел

  2. Вывести список сотрудников по отделам с указанием должности:

select depno, name, post
from emp
order by depno, name;

DepNo

Name

Post

1

Рюмин В.П.

начальник отдела

1

Серова Т.В.

вед. программист

2

Буров Г.О.

бухгалтер

2

Волков Л.Д.

программист

2

Малова Л.А.

гл. бухгалтер

3

Петрова К.В.

секретарь

3

Сухов К.А.

начальник отдела

Запрос SELECT на нескольких таблицах реализует декартово произведение исходных таблиц (или их соединение, если указать условия соответствия значений полей разных таблиц). Для полей с одинаковыми названиями нужно указывать имя таблицы (или алиас) перед именем поля, разделяя их точкой.

Пример: Запрос по двум таблицам. Список сотрудников с детьми:

select e.name, c.name child, c.born
from emp e, children c /* e, c – алиасы */
where e.tabno = c.tabno /* условие соединения */
order by e.name, c.born;

Name

Child

Born

Буров Г.О.

Ольга

18.07.91

Малова Л.А.

Илья

19.02.77

Малова Л.А.

Анна

26.12.79

Рюмин В.П.

Вадим

03.05.85

Серова Т.В.

Инна

25.01.99

Расширение возможностей команды SELECT достигается за счёт применения различных операторов, предикатов и функций.

Операторы:

Пример: Составить список сотрудников второго и третьего отдела, имеющих оклады выше 4600 рублей:

select depno, name, salary
from emp
where salary>4600 and (depno=2 or depno=3)
order by name;

DepNo

Name

Salary

2

Волков Л.Д.

4650.0

2

Малова Л.А.

4924.0

3

Сухов К.А.

4850.0

Предикаты, используемые в запросах:

field IN (список значений)

– определяет множество значений, с которыми будет сравниваться значение указанного поля field. Предикат считается истинным, если значение поля field равно хотя бы одному из элементов множества.

field BETWEEN значение1 AND значение2

– определяет, входит ли значение поля field в указанные границы. Если значение поля меньше, чем значение1, или больше, чем значение2, предикат возвращает "ложь".

field LIKE 'образец'

– используется для поиска подстрок, применяется только в полям типа CHAR, VARCHAR. Возможно использование шаблонов: '_' – один любой символ и '%' – произвольное количество символов (в т.ч., ни одного);

field IS [NOT] NULL

– определяет, установлено ли значение поля. Все другие предикаты и операторы сравнения возвращают неопределённый результат (null), если хотя бы один из операндов имеет значение null.

Примеры:

  1. Список программистов и ведущих программистов:

    2. select depno, name, post from emp
    where post like ('%программист%');

    DepNo

    Name

    Post

    1

    Серова Т.В.

    вед. программист

    2

    Волков Л.Д.

    программист

  2. Список сотрудников старше 40 лет из 1-го и 3-го отделов:

    3. select depno, name from emp
    where depno in (1, 3) and
    year(getdate()) – year(born) > 40;     /*(текущий год) – (год рождения)*/

    DepNo

    Name

    1

    Рюмин В.П.

    3

    Петрова К.В.

    3

    Сухов К.А.

  3. Список сотрудников, не имеющих телефонов:

select tabno, name, post
from emp
where tel is null;

TabNo

Name

Post

100

Волков Л.Д.

программист

034

Петрова К.В.

секретарь

Функции агрегирования:

Правила уточнения использования агрегирующих функций:

SUM (distinct <поле>) – суммирование различных значений поля;

AVG (distinct <поле>) – среднее арифметическое разных значений поля;

COUNT (distinct <поле>) – подсчёт количества разных значений поля;

COUNT (<поле>) – подсчёт количества ненулевых значений поля;

COUNT (*) – подсчёт количества строк в результате.

Примеры:

  1. Посчитать количество сотрудников по отделам:

    2. select depno, count(*), ‘ сотрудник(а)’
    from emp
    group by depno;

    DepNo

    Count(*)

    сотрудник(а)

    1

    2

    сотрудник(а)

    2

    3

    сотрудник(а)

    3

    2

    сотрудник(а)

  2. Сумма зарплаты по отделам:

select depno, sum(salary) as sal
from emp
group by depno;

DepNo

Общая сумма

1

9700.0

2

14162.5

3

8050.4

Предложение UNION позволяет объединять результаты нескольких запросов SELECT для реализации соответствующей операции реляционной алгебры. Результаты этих запросов должны быть построены по одной схеме. Предложение ORDER BY может встречаться в таком запросе один раз – в конце последнего предложения SELECT.

Пример: Посчитать количество сотрудников по всем отделам:

select depno, count(name), ‘ сотрудник(а)’
from emp
group by depno
union
select depno, 0, ‘ сотрудников’
from depart
where depno not in (select distinct depno from emp)

order by 1; /* упорядочение по первому столбцу */

DepNo

Count(name)

сотрудник(а)

1

2

сотрудник(а)

2

3

сотрудник(а)

3

2

сотрудник(а)

4

0

сотрудников

1.4.4. Работа с представлениями

Представление (view, обзор) – это хранимый запрос, создаваемый на основе команды SELECT. Представление реально не содержит данных. Запрос, определяющий представление, выполняется тогда, когда к представлению происходит обращение с другим запросом, например, SELECT, UPDATE и т.д.

Создание представления выполняется командой CREATE VIEW:

CREATE VIEW <имя представления> [(<имя столбца>.,..)]
AS <запрос>;

Запрос, на основании которого создаётся представление, называется определяющим запросом, а таблицы, к которым происходит обращение в определяющем запросе – базовыми таблицами. Определяющий запрос не может включать предложение ORDER BY.

Если не указывать имена столбцов, то они получат названия по именам, перечисленным в списке выбора определяющего запроса. Указывать имена столбцов представления обязательно, если список выбора содержит агрегирующие функции или столбцы с одинаковыми именами из разных таблиц.

Пример: Создание представления "Сотрудники с детьми":

create view emp_child(name, child, born)
as select e.name, c.name, c.born
from emp e, children c
where e.tabno = c.tabno;

select * from emp_child order by name, born;

Name

Child

Born

Буров Г.О.

Ольга

18.07.91

Малова Л.А.

Илья

19.02.77

Малова Л.А.

Анна

26.12.79

Рюмин В.П.

Вадим

03.05.85

Серова Т.В.

Инна

25.01.99

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

Пример: Создание представления "Сотрудники 2-го отдела":

create view emp2
as select tabno, name, post, salary, born
from emp where depno = 2;

select * from emp2 order by name;

TabNo

Name

Post

Salary

Born

110

Буров Г.О.

бухгалтер

4588.5

22.05.65

100

Волков Л.Д.

программист

4650.0

16.10.72

023

Малова Л.А.

гл. бухгалтер

4924.0

24.11.54

Обновление базового отношения через представление:

update emp2 set salary = 4800
where tabno = 100;

select * from emp2 order by name;

TabNo

Name

Post

Salary

Born

110

Буров Г.О.

бухгалтер

4588.5

22.05.65

100

Волков Л.Д.

программист

4800.0

16.10.72

023

Малова Л.А.

гл. бухгалтер

4924.0

24.11.54

Изменения будут произведены в базовой таблице и отразятся в представлении. По стандарту SQL-2 представление не является обновляемым, если определяющий запрос:

  1. содержит ключевое слово DISTINCT;
  2. ссылается на другое необновляемое представление;
  3. содержит вычисляемые выражения в списке выбора;
  4. выбирает данные более чем из одной таблицы;
  5. содержит предложение
    6. GROUP BY.

Если вносимые изменения выходят за рамки определяющего запроса и поэтому не могут быть отражены в представлении, они могут быть отвергнуты системой (это зависит от реализации).

1.4.5. Удаление объектов базы данных

Удаление объектов БД выполняется с помощью команды DROP.

DROP TABLE <имя таблицы> [RESTRICT | CASCADE];

Таблица будет удалена без дополнительного запроса на подтверждение. При указании CASCADE вместе с таблицей каскадно удаляются все зависящие от неё объекты БД. Если указать RESTRICT, то при наличии зависимых от удаляемой таблицы объектов операция будет отменена.

DROP VIEW <имя представления>;

1.4.6. NULL-значения

Стандарт SQL включает понятие неопределённого значения – NULL-значения. В тех случаях, когда при добавлении записи значение какого-либо поля неизвестно, его можно не устанавливать, пропустив это поле в списке полей или указав для него значение NULL (но только для тех полей, на которые не наложено ограничение целостности NOT NULL).

Значение NULL не сравнимо ни с каким другим значением, даже со значением NULL. Тем не менее, предложение GROUP BY объединяет все NULL-значения в одну группу, DISTINCT оставляет только одно NULL-значение, а функция AVG не учитывает NULL-значения, и сумма значений поля делится на количество ненулевых значений.

1.4.7. Подзапросы

Подзапросы можно разделить на следующие группы в зависимости от возвращаемых результатов:

Подзапросы бывают коррелированные и некоррелированные. Коррелированные подзапросы содержат условия, зависящие от значений полей в основном запросе. Запросы на существование обычно являются коррелированными.

Рассмотрим операторы, которыми модифицируются операторы сравнения:

> ALL (< ALL) – больше (меньше) каждого значения элементов результирующего множества.

= ANY – равно одному из значений элементов результирующего множества (эквивалентно использованию предиката IN).

> ANY (< ANY) – больше (меньше) любого значения элементов результирующего множества.

Если список, модифицированный оператором ALL, содержит NULL-значение, то результирующий запрос будет пуст, т.к. нельзя сравнить никакое значение с NULL-значением.

Выражение <>ANY(…) не эквивалентно NOT IN: оно выполняется всегда, кроме случаев NULL-значений.

Примеры:

  1. Выдать список бездетных сотрудников:

    2. select * from emp e
    where not exists (select * from children c where e.tabno=c.tabno);

    DepNo

    TabNo

    Name

    Post

    Salary

    Born

    2

    100

    Волков Л.Д.

    программист

    4650.0

    16.10.72

    3

    034

    Петрова К.В.

    секретарь

    3200.4

    24.04.58

    3

    002

    Сухов К.А.

    начальник отдела

    4850.0

    18.06.48

  2. Выдать список сотрудников, имеющих детей:

    3. select * from emp
    where tabno in (select distinct tabno from children);

    TabNo

    DepNo

    Name

    Post

    Salary

    Born

    988

    1

    Рюмин В.П.

    начальник отдела

    4850.0

    01.02.60

    909

    1

    Серова Т.В.

    вед. программист

    4850.0

    20.10.71

    110

    2

    Буров Г.О.

    бухгалтер

    4588.5

    22.05.65

    023

    2

    Малова Л.А.

    гл. бухгалтер

    4924.0

    24.11.54

    4. Список сотрудников, оклад которых меньше среднего на предприятии:

select depno, name, salary
from emp
where salary < ANY(select avg(salary) from emp);

DepNo

Name

Post

Salary

3

Петрова К.В.

секретарь

3200.4

1.4.8. Сложные запросы

Существуют варианты запросов, которые в принципе не могут быть реализованы в рамках одного запроса. Например, составление ведомости на получение зарплаты при условии, что подоходным налогом (13%) не облагается один минимальный оклад (300 рублей) на каждого ребенка. Для этого запроса необходима информация из двух отношений ("Сотрудники" и "Дети"), причём для определения количества детей необходимо использовать агрегирующую функцию.

create view nc(tabno, num) as
select tabno, count(tabno)
from children
group by tabno;

select distinct emp.name, salary*0.87+(300*0.13)*num as sal
from emp, nc
where emp.tabno = nc.tabno
union
select name, salary*0.87
from emp
where tabno not in (select distinct tabno from children);

Name

Sal

Буров Г.О.

4030.00

Волков Л.Д.

4176.00

Малова Л.А.

4361.88

Петрова К.В.

2784.35

Рюмин В.П.

4258.50

Серова Т.В.

4258.50

Сухов К.А.

4219.50

В заключение приведём пример ёще одного сложного типа запроса – создание на основании таблиц "Сотрудники" и "Дети" следующего отчёта:

ФИО сотрудника

Количество дочерей

Количество сыновей

…

…

…

В таких случаях обычно поступают следующим образом: создают представления, которые последовательно выполняют требуемый запрос.

create view v1 (tabno, fnum, mnum)
as select tabno, count(*), 0
from children
where sex='ж' /*количество дочерей*/
group by tabno;

create view v2 (tabno, fnum, mnum)
as select tabno, 0, count(*)
from children
where sex='м' /*количество сыновей*/
group by tabno;

create view child (name, girls, boys)
as /* сотрудники, имеющие и сыновей, и дочерей */
select name, v1.fnum, v2.mnum
from emp e, v1, v2
where e.tabno=v1.tabno and e.tabno=v2.tabno
union /* сотрудники, имеющие только дочерей */
select name, v1.fnum, 0
from emp e, v1
where e.tabno=v1.tabno and e.tabno not in (select tabno from v2)
union /* сотрудники, имеющие только сыновей */
select name, 0, v2.mnum
from emp e, v2
where e.tabno=v2.tabno and e.tabno not in
(select tabno from v1);

/*выполнение запроса*/

select * from child order by name;

Name

girls

Boys

Буров Г.О.

1

0

Малова Л.А.

1

1

Рюмин В.П.

0

1

Серова Т.В.

1

0

2. ВЫПОЛНЕНИЕ ЛАБОРАТОРНЫХ РАБОТ

Различают две версии SQL – интерактивную и встроенную. Интерактивный SQL подразумевает наличие командной строки, в которую вводятся команды. Результаты выполнения этих команд выводятся на экран. Встроенный SQL включается в различные утилиты, входящие в состав СУБД (например, в утилиты для формирования отчётов или экранных форм).

Выполнение лабораторных работ заключается в создании таблиц (фрагмента базы данных) и запросов в соответствии с вариантами задания (см. п. 3) в режиме работы с интерактивным SQL.

Лабораторная работа №1 посвящена созданию и заполнению таблиц базы данных. Каждая таблица должна иметь первичный ключ и обязательные поля (not null). Таблицы должны быть связаны по внешнему ключу.

Лабораторная работа №2 заключается в написании запросов к созданным отношениям. Необходимо предложить два варианта написания одного из запросов (по выбору) и проанализировать пути и эффективность их выполнения.

Третья работа посвящена созданию представлений. Для каждого представления необходимо проверить с помощью запросов модификации данных является ли оно обновляемым, и объяснить полученный результат.

Лабораторная работа №4 заключается в реализации операций реляционной алгебры средствами SQL, возможно, с использованием отношений, созданных по заданию лабораторной работы №1.

Перед началом работы необходимо получить у лаборанта имя и пароль. Если лабораторные работы проводятся на СУБД Sybase, то сначала запускается база данных Sybase (Sample Database), затем – интерактивный SQL. Система предложит ввести имя и пароль (dba/sql) и после успешной идентификации перейдёт в режим командной строки.

3. ВАРИАНТЫ ЗАДАНИЙ К ЛАБОРАТОРНЫМ РАБОТАМ

Примечания:

  1. Поля основного отношения с типами и ограничениями целостности приведены в таблице.

  2. Для остальных отношений в скобках перечислены обязательные поля.

  3. Типы полей обозначаются следующим образом: N – числовое, C – символьное, D – дата, T – время, L – логическое (имеющее значения .T. – истина и .F. – ложь).

  4. Для некоторых представлений также в скобках перечислены поля.

Вариант 1. Фрагмент БД недвижимости.

Л. р. №1. Создание и заполнение отношений.

  1. Отношение "Владельцы" (идентификатор, "Имя", "Телефон").

  2. Отношение "Станции метро" (идентификатор, "Название").

  3. Отношение "Квартиры" (основное):

Содержимое поля

Тип

Длина

Дес.

Примечание

Идентификатор

N

5

0

первичный ключ

Номер владельца

N

6

0

внешний ключ

Общая площадь

N

4

1

обязательное поле

Жилая площадь

N

4

1

обязательное поле

Количество комнат

N

1

0

обязательное поле

Размер кухни

N

3

1

обязательное поле

Этаж

N

2

0

обязательное поле

Всего этажей в доме

N

2

0

 

Ближайшая станция метро

N

3

0

внешний ключ

Цена

N

7

0

обязательное поле

Адрес

C

30

 

обязательное поле

Дата поступления

D

 

 

информации в БД

Л.р. №2. Выборка данных.

Проверить, что для всех квартир общая площадь больше, чем жилая площадь плюс размер кухни. Создать упорядоченные списки:

Л.р. №3. Работа с представлениями.

Создать представления:

  1. "Двухкомнатные квартиры" (все поля отношения "Квартиры").

  2. "Владение квартирами" (имя владельца, количество квартир, общая площадь этих квартир).

  3. "Владельцы однокомнатных квартир" (имя, телефон, площадь квартиры).

Вариант 2. Фрагмент БД деканата (преподаватели).

Л.р. №1. Создание и заполнение отношений.

  1. Отношение "Дисциплины" ("Шифр дисциплины", "Название").

  2. Отношение "Преподаватели" (идентификатор, "ФИО", "Кафедра").

  3. Отношение "Сессия" (основное):

Содержимое поля

Тип

Длина

Дес.

Примечание

Факультет

C

4

 

обязательное поле

Курс

N

1

0

обязательное поле

Шифр дисциплины

N

5

2

ключевая комбинация полей

Группа

С

5

0

Экзаменатор

N

4

0

идентификатор, внешний ключ

Аудитория

N

3

0

0

Дата

D

0

0

0

Время

T

0

0

0

Л.р. №2. Выборка данных.

Проверить уникальность комбинации "Аудитория", "Дата", "Время". Определить дисциплины, по которым нет экзаменов.

Создать расписание экзаменов:

Л.р. №3. Работа с представлениями.

Создать представления:

  1. "Количество экзаменов" (группа, количество экзаменов).

  2. "Количество экзаменов для факультетов АВТ и ФПМ" (название дисциплины, количество экзаменов на АВТ, количество экзаменов на ФПМ).

  3. "Преподаватели кафедры ВСиС" (ФИО преподавателя).

Вариант 3. Фрагмент БД деканата (студенты).

Л.р. №1. Создание и заполнение отношений.

  1. Отношение "Дисциплины" ("Шифр", "Название дисциплины").

  2. Отношение "Студенты" (основное):

    3. Содержимое поля

    Тип

    Длина

    Дес.

    Примечание

    Номер зачётной книжки

    N

    6

    0

    первичный ключ

    ФИО

    C

    20

     

    обязательное поле

    Год поступления

    N

    4

    0

    обязательное поле

    Группа

    С

    7

    0

    обязательное поле

    Курс

    N

    1

    0

    обязательное поле

    Средний балл

    N

    3

    1

    0

    Форма обучения

    С

    8

    0

    по умолчанию – дневная

    Академический отпуск

    L

    1

    0

    по умолчанию – .F.

  3. Отношение "Сессия" ("Группа", "Шифр дисциплины" (внешний ключ), "Дата", "Время", "Аудитория").

Л.р. №2. Выборка данных.

Проверить уникальность комбинации "Дата", "Время", "Аудитория". Создать расписание экзаменов на сессию для произвольной группы.

Создать упорядоченные списки:

Л.р. №3. Работа с представлениями.

Создать представления:

  1. "Списки групп" для дневной формы обучения (группа, фамилия, номер зачётной книжки, средний балл).

  2. "Успеваемость" (группа, средний балл по группе, минимальный средний балл, максимальный средний балл, разница (max-min)).

  3. "Количество экзаменов" (группа, количество экзаменов).

Вариант 4. Фрагмент БД института (сотрудники).

Л.р. №1. Создание и заполнение отношений.

  1. Отношение "Отделы" (идентификатор, "Название отдела").

  2. Отношение "Научные направления" (идентификатор, "Название научной специальности").

  3. Отношение "Сотрудники" (основное):

Содержимое поля

Тип

Длина

Дес.

Примечание

Табельный номер

N

4

0

первичный ключ

ФИО

C

20

0

обязательное поле

Должность

C

15

0

обязательное поле

Ученое звание

С

10

0

0

Шифр специальности

N

6

0

специальность по диплому

Научная специализация

С

8

0

внешний ключ

Номер отдела

С

6

0

внешний ключ

Л.р. №2. Выборка данных.

Создать упорядоченные списки:

Определить, в каком отделе (отделах) работает больше всего сотрудников.

Л.р. №3. Работа с представлениями.

Создать представления:

  1. "Сотрудники отдела “Информационные технологии”" (все поля отношения "Сотрудники").

  2. "Качественный состав научных сотрудников отделов" (отдел, количество профессоров, количество доцентов).

  3. "Научные кадры" (отдел, научная специализация, количество сотрудников).

Вариант 5. Фрагмент БД библиотеки (журнальные публикации).

Л. р. №1. Создание и заполнение отношений.

  1. Отношение "Журналы" (идентификатор, "Название", "Издатель").

  2. Отношение "Рубрикаторы" ("Шифр", "Название рубрики").

  3. Отношение "Публикации (статьи)" (основное):

Содержимое поля

Тип

Длина

Дес.

Примечание

Индекс журнала

N

6

0

ключевая комбинация полей

Автор(ы)

C

40

0

Название

C

40

0

Год выпуска

N

4

0

обязательное поле

Номер выпуска

N

2

0

обязательное поле

Шифр рубрики

C

6

0

внешний ключ

Страницы

С

7

0

0

Примечание

C

20

0

раздел журнала

Л.р. №2. Выборка данных.

Создать упорядоченные списки:

Л.р. №3. Работа с представлениями.

Создать представления:

  1. "Публикации за текущий год" (все поля отношения "Публикации").

  2. "Авторы, публиковавшиеся более одного раза" (все поля отношения "Публикации").

  3. "Состав по рубрикам" (рубрика, количество публикаций всего, количество публикаций за последние три года).

Вариант 6. Фрагмент БД отдела кадров.

Л. р. №1. Создание и заполнение отношений.

  1. Отношение "Отделы" ("Номер отдела", "Название отдела").
  2. Отношение "Сотрудники" (основное):

    3. Содержимое поля

    Тип

    Длина

    Дес.

    Примечание

    Табельный номер

    N

    6

    0

    первичный ключ

    ФИО

    C

    20

     

    обязательное поле

    Пол

    C

    1

     

    по умолчанию – мужской

    Дата рождения

    D

     

     

     

    Образование

    C

    20

     

    высшее, среднее, начальное

    Номер отдела

    N

    3

    0

    внешний ключ

    Должность

    C

    20

     

    обязательное поле
  3. Отношение "Дети" (внешний ключ к отношению "Сотрудники", "Имя", "Дата рождения").

Л.р. №2. Выборка данных.

Создать упорядоченные списки:

Посчитать количество сотрудников с разными уровнями образования.

Л.р. №3. Работа с представлениями.

Создать представления:

  1. "Отделы и сотрудники" (поля обоих отношений без повторов).

  2. "Бездетные сотрудники" (все поля отношения "Сотрудники").

  3. "Образовательный уровень сотрудников" (уровень образования, количество мужчин, количество женщин).

Вариант 7. Фрагмент БД библиотеки (книги).

Л. р. №1. Создание и заполнение отношений.

  1. Отношение "Издательства" (идентификатор, "Название", "Адрес").

  2. Отношение "Рубрикаторы" ("Шифр рубрики", "Название рубрики").

  3. Отношение "Каталог книг" (основное):

Содержимое поля

Тип

Длина

Дес.

Примечание

Рубрикатор

C

8

 

ключевая комбинация полей

Шифр

C

6

 

Автор(ы)

C

25

 

обязательное поле

Название

C

25

 

обязательное поле

Место издания

C

10

 

 

Шифр издательства

C

5

 

внешний ключ

Год издания

N

4

0

 

Количество страниц

N

4

0

 

Примечание

C

20

 

по умолчанию – учебник

Л.р. №2. Выборка данных.

Посчитать, сколько книг не имеют комментариев. Создать списки:

Л.р. №3. Работа с представлениями.

Создать представления:

  1. "Первая – последняя публикации" (автор, год выхода первой публикации, год выхода последней публикации).

  2. "Учебники и учебные пособия" (все поля отношения "Каталог книг").

  3. "Состав по рубрикам" (рубрика, количество книг до 1980 года издания, количество книг после 1980 года издания).

Вариант 8. Фрагмент БД больницы.

Л. р. №1. Создание и заполнение отношений.

  1. Отношение "Больничные палаты" ("Номер палаты", "Количество коек", "Отделение").

  2. Отношение "Врачи" (идентификатор, "ФИО врача", "Отделение", "Специализация").

  3. Отношение "Пациенты" (основное):

Содержимое поля

Тип

Длина

Дес.

Примечание

Регистрационный №

N

6

0

первичный ключ

ФИО

C

20

0

обязательное поле

Пол

C

1

0

по умолчанию – женский

Номер полиса

C

15

0

0

Дата поступления

D

0

0

обязательное поле

Номер палаты

N

3

0

внешний ключ

Лечащий врач

N

6

0

внешний ключ

Диагноз

C

20

0

0

Дата выписки

D

0

0

заполняется при выписке пациента

Л.р. №2. Выборка данных.

Проверить, что в одной палате не лежат мужчины и женщины. Посчитать количество язвенников, поступивших в текущем году.

Создать упорядоченные списки:

Л.р. №3. Работа с представлениями.

Создать представления:

  1. "Специализация больницы" (диагноз, количество пациентов-мужчин, количество пациентов-женщин).

  2. "Текущие пациенты отделения “Хирургия”" (все поля отношения "Пациенты").

  3. "Общее количество мест" (отделение, количество мест).

Вариант 9. Фрагмент БД торгового предприятия.

Л.р.№1. Создание и заполнение отношений.

  1. Отношение "Поставщики" (идентификатор, "Название", "Адрес").

  2. Отношение "Товары" (идентификатор, "Название", "Категория товара").

  3. Отношение "Поставки" (основное):

Содержимое поля

Тип

Длина

Дес.

Примечание

Шифр поставки

N

6

0

первичный ключ

Индекс поставщика

C

6

0

внешний ключ

Индекс товара

C

8

0

внешний ключ

Единица измерения

C

3

0

'кг', 'шт', 'уп' (по умолчанию – 'кг')

Количество товара

N

7

2

обязательное поле

Цена единицы товара

N

8

2

0

Дата поставки

D

0

0

0

Л.р. №2. Выборка данных.

Создать список поставщиков, от которых нет поставок.

Создать упорядоченные списки:

Л.р. №3. Работа с представлениями.

Создать представления:

  1. "Товары на складе" (соединение отношений "Поставки", "Товары" и "Поставщики").

  2. "Поставщики и категории товаров" (поставщик, категория).

  3. "Общая стоимость товаров" (название товара, общая стоимость).

Вариант 10. Фрагмент БД складского предприятия.

Л.р.№1. Создание и заполнение отношений.

  1. Отношение "Поставщики" (идентификатор, "Название ", "Телефон").

  2. Отношение "Поставки" (основное):

    3. Содержимое поля

    Тип

    Длина

    Дес.

    Примечание

    Шифр поставки

    N

    6

    0

    ключевая комбинация полей

    Индекс товара

    C

    6

    0

    Индекс поставщика

    C

    8

    0

    внешний ключ

    Единица измерения

    C

    3

    0

    значения 'шт', 'кг', 'уп', 'кор' (по умолчанию – 'шт')

    Вес единицы товара

    N

    7

    2

    0

    Количество товара

    N

    7

    2

    обязательное поле

    Номер склада

    N

    2

    0

    0

    Номер линии

    С

    1

    0

    0

    Дата поставки

    D

    0

    0

    обязательное поле

  3. Отношение "Заказы" (первичный ключ (идентификатор, "Индекс товара"), "Единица измерения", "Количество").

Л.р. №2. Выборка данных.

Создать упорядоченный список товаров с наименованиями и телефонами поставщиков. Для конкретного заказа выдать список местоположения заказанных товаров на складах (с учётом количества товара в заказе и на складе). Посчитать общий вес конкретного заказа.

Л.р. №3. Работа с представлениями.

Создать представления:

  1. "Товары на складе" (соединение отношений "Поставки" и "Поставщики").

  2. "Недостаток товаров": для заказа, который не может быть выполнен из-за недостатка товара, выдать список пунктов этого заказа, которые не могут быть выполнены.

  3. "Поставки и поставщики" (поставщик, индекс поставки).

Вариант 11. Фрагмент БД адвоката.

Л. р. №1. Создание и заполнение отношений.

  1. Отношение "Статьи УК" (идентификатор, "Статья", "Минимальный срок", "Максимальный срок").

  2. Отношение "Клиенты" (основное):

    3. Содержимое поля

    Тип

    Длина

    Дес.

    Примечание

    Номер дела

    N

    6

    0

    ключевая комбинация полей

    ФИО

    C

    20

    0

    Дата рождения

    D

    0

    0

    0

    Дата начала дела

    D

    0

    0

    обязательное поле

    Номер камеры

    N

    3

    0

    0

    Размер гонорара

    N

    7

    2

    0

    Срок

    N

    4

    1

    по приговору

    Дата окончания дела

    D

    0

    0

    0

  3. Отношение "Статьи" (внешний ключ ("Номер дела", "ФИО"), внешний ключ "Номер статьи").

Л.р. №2. Выборка данных.

Проверить, что обвиняемые по одному делу не сидят в одной камере. Посчитать общую сумму гонорара (по законченным делам).

Создать упорядоченные (по дате начала дела) списки:

Л.р. №3. Работа с представлениями.

Создать представления:

  1. "Эффективность защиты" (дело, ФИО, максимальный срок минус срок по приговору, срок по приговору минус минимальный срок).

  2. "Текущие подзащитные" (все поля отношения "Клиенты").

  3. "Количество несовершеннолетних клиентов по статьям".

Вариант 12. Фрагмент БД гостиницы.

Л. р. №1. Создание и заполнение отношений.

  1. Отношение "Стоимость мест" ("Класс", "Стоимость места").

  2. Отношение "Номера" (идентификатор, "Класс" (внешний ключ), "Количество мест", "Количество забронированных мест").

  3. Отношение "Постояльцы" (основное):

Содержимое поля

Тип

Длина

Дес.

Примечание

Номер паспорта

С

10

 0

первичный ключ

Занимаемый номер

N

3

0

внешний ключ

ФИО

C

20

 0

обязательное поле

Пол

С

1

 0

по умолчанию – мужской

Организация

С

20

 0

 0

Дата вселения

D

 0

 0

обязательное поле

Дата выселения

D

 0

 0

заполняется при выезде постояльца

Л.р. №2. Выборка данных.

Создать запрос на выдачу счета на оплату номера.

Создать упорядоченные списки:

Л.р. №3. Работа с представлениями.

Создать представления:

  1. "Постояльцы, проживающие в гостинице в данное время" (все поля отношения "Постояльцы").

  2. "Полностью занятые номера" (номер, количество мест).

  3. "Номера со свободными местами" (пол, номер, количество свободных мест).

Вариант 13. Фрагмент БД организации.

Л. р. №1. Создание и заполнение отношений.

  1. Отношение "Отделы" (идентификатор, "Название отдела").

  2. Отношение "Проекты" (идентификатор, "Название", "Дата начала", "Дата завершения").

  3. Отношение "Сотрудники" (основное):

Содержимое поля

Тип

Длина

Дес.

Примечание

Идентификатор

N

6

0

ключевое поле

ФИО

C

30

0

обязательное поле

Должность

C

20

0

обязательное поле

Пол

C

1

0

по умолчанию – 'ж'

Оклад

N

7

2

0

Номер отдела

N

7

2

внешний ключ

Номер проекта

N

3

0

внешний ключ

Руководитель проекта

L

1

0

по умолчанию – .F.

Л.р. №2. Выборка данных.

Создать упорядоченные списки:

Проверить, что дата начала проекта меньше, чем дата его завершения. Проверить, что у каждого проекта только один начальник.

Л.р. №3. Работа с представлениями.

Создать представления:

  1. "Сотрудники, работающие в отделе №2" (все поля отношения "Сотрудники").

  2. "Заработная плата" (ФИО, оклад–13%). Руководителю проекта полагается надбавка к окладу (20%).

  3. "Проекты по отделам" (название проекта, ФИО руководителя, название отдела (в котором работает руководитель), количество сотрудников (работающих над данным проектом)).

Библиографический список

  1. Грабер М. Введение в SQL. – М.: 1998. – 378 c.

  2. Коннолли Т., Бегг К., Страчан А. Базы данных: проектирование, реализация, сопровождение. Теория и практика. – 2-е изд. : Пер. с англ. : Уч. пос. – М.: Издательский дом "Вильямс", 2000. – 1120 с.

  3. Боуман Дж., Эмерсон С., Дарновски М. Практическое руководство по SQL. 3-е изд.: Пер. с англ. – К.: Диалектика, 1997. – 320 с.