Запросы на извлечение информации

⇐ ПредыдущаяСтр 2 из 9Следующая ⇒

Инструкция SQL select извлекает информацию из базы данных и возвращает её в виде таблицы результатов запроса. Данная инструкция состоит из шести синтаксических единиц, называемых зачастую предложениями. Предложения select и from являются обязательными, остальные четыре включаются в запрос при необходимости.

- в предложении select указывается список столбцов, которые должны быть возвращены инструкцией.

- в предложении from указывается список таблиц, которые содержат элементы данных, извлекаемые запросом.

Например следующий запрос извлекает из таблицы Staff три столбца:

Вывести для каждого сотрудника имя, фамилию и занимаемую должность

selectfname, lname, position

fromstaff;

Помимо этого в предложении select может содержаться и так называемый “вычисляемый столбец”, например:

Выдать строки сотрудников с указанием зарплаты с 10 % надбавкой

selectfname, lname, position, ( salary + 0.1*salary ) aspercent

fromstaff;

В данном запросе percent определяет название столбца в результирующей таблице.

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

В SQL обычно используются пять основных условий отбора (в стандарте ANSI/ISO они называются предикатами)

- сравнение

- проверка на принадлежность диапазону

- проверка на членство в множестве

- проверка на соответствие шаблону

- проверка на равенство значению NULL.

Приведем примеры использования различных условий отбора в предложении where

Найти служащих, родившихся до 1988 года

selectfname, lname

fromstaff

where DOB< ’01-Jan-88’;

Здесь следует обратить внимание на формат записи данных типа дата. В различных СУБД формат записи даты неодинаков. Используемый в примере формат поддерживается в ORACLE и специфичен для него. Даты в ORACLE так же как и строковые константы заключаются в парные одинарные кавычки.

Найти служащих, родившихся в интервале времени с 1октября 1963 по 31 декабря 1971

selectfname, lname

fromstaff

where DOB between ’01-Oct-63’ and ’31-Dec-71’;

Здесь следует отметить, что проверка на принадлежность диапазону не расширяет возможностей SQL, поскольку её можно выразить в виде двух сравнений, т. е. выражение A between B and C эквивалентно (A> =B) and (A< =C)

Вывести информацию об офисах, расположенных в Минске, Витебске и Бресте

select address, tel_no

from branch

where city in (‘Минск’, ‘Витебск’, ‘Брест’);

Проверка in не добавляет новых возможностей, так же как и between and, так как условие X in (A, B, C) полностью эквивалентно условию (X=A) or (X=B) or (X=C)

- Вывести информацию о всех сотрудников фамилии которых начинаются на букву К

select lname, address, tel_no

from staff

where lname like ‘K%’

Здесь также следует обратить внимание на запись шаблона строки сравнения в условии like. Указанный способ задания строки шаблона характерен для диалекта ORACLE и отличен от регламентированного стандартом. Символ ‘%’ - замещает произвольную последовательность символов, а ‘_’ – замещает одиночный символ. Строки-шаблоны так же как и обыкновенные строки-константы заключаются в парные одинарные кавычки.

Запросы с объединением таблиц

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

Составить список всех сотрудников, работающих в Минском отделении.

select fname, lname, position, S.tel_no

from Branch B, Staff S

where B.bno=S.bno and city = ‘Минск’;

Тот же запрос можно выполнить с помощью подзапроса

select fname, lname, position, tel_no

from staff where bno in (select bno from branch where city= ‘Минск’);

В связи с подчиненными запросами можно выделить ряд особенностей:

- Таблица результатов подчиненного запроса всегда состоит из одного столбца

- В подчиненный запрос не может включаться предложение order by

- Имена столбцов в подчиненном запросе могут являться ссылками на столбцы таблиц главного запроса

Итоговые запросы

Результирующую таблицу итогового запроса можно рассматривать как некий отчет. Для получения подобных отчетов в запросе на получение итоговой информации требуется указывать предложение group by и возможное having для отбора групп. Ограничением при выполнении итоговых запросов является то, что здесь в предложении select могут употребляться лишь столбцы группировки (т.е. те которые указываются в предложении group by), строковые константы и статистические функции. Таких функций в SQL пять:

- sum() – для вычисления суммы всех значений столбца-аргумента

- avg() – для вычисления среднего значения столбца

- min() – определяет минимальное значение столбца

- max() – определяет максимальное значение столбца

- count() – подсчитывает число всех определенных значений столбца

- count(*) – подсчитывает число строк таблицы.

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

select position, avg(salary)

from staff

group by position;

Подсчитать количество сотрудников работающих в каждом из офисов, исключив офисы, в которых работает менее 2 человек.

select bno, count(sno)

from staff

group by bno;

having count(sno)> 2;

Представления

Представление – объект базы данных, представляющий собой именованный и сохраненный запрос. Часто представления также называют “виртуальными таблицами”. В случае если определение представления простое, СУБД выполняет его “на лету”, в обратном же случае СУБД приходится “материализовать ” представление, т.е. сохранять его результаты во временной таблице. Создаются представления посредством инструкции create view. Использование данной инструкции продемонстрируем на примере.

Создать представление, включающее в себя список сотрудников, работающих в отделениях Минска.

create view Minsk as select fname, lname, address, position, tel_no, sex, dob

from staff

where bno in (select bno

from branch

where city=’Минск’);

· Варианты заданий

1. Получить список сотрудников с зарплатой от 200 до 300.

Получить список сотрудников, работающих в офисах Бреста и Гомеля.

Определить суммарную и среднюю зарплату сотрудников в зависимости от занимаемой ими должности.

Создать представление с информацией о офисах в Бресте.

2. Определить адреса и телефоны офисов, расположенных в Минске и Гродно.

Кто из сотрудников предлагает для аренды 3-х комнатные квартиры.

Вывести итоговый отчет о средней и суммарной зарплатах в зависимости от половой принадлежности сотрудников.

Создать представление с информацией о директорах отделений.

3. Определить адреса всех 3-х комнатных квартир, предлагаемых в аренду.

Получить список арендаторов, осматривавших объекты аренды 20 октября 1999 года.

Определить минимальную и максимальную зарплаты сотрудников различных отделений.

Создать представление с информацией о владельцах, чьи дома или квартиры осматривались потенциальными арендаторами.

4. Вывести номера домашних телефонов всех директоров.

Составить список владельцев всех 3-х комнатных квартир.

Подсчитать количество сотрудников в каждом из отделений.

Создать представление сотрудниках и объектов, которые они предлагают в аренду.

5. Вывести список сотрудников, родившихся до 1980 года.

Подсчитать сколько сотрудников работает в отделении в Бресте

Вывести количество арендаторов, желающих арендовать 3-х комнатные и 4-х комнатные квартиры.

Создать представление об объектах с минимальной рентной стоимостью.

6. Определить адреса всех квартир с рентной стоимостью не более 300

Подсчитать сколько менеджеров работает в Минске

Получить итоговый список с количеством домов и квартир сдаваемых в аренду.

Создать представление о арендаторах, желающих арендовать 3-х комнатные квартиры.

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

7. Вывести домашние телефоны всех потенциальных арендаторов, желающих арендовать дома.

Вывести телефоны владельцев, дома или квартиры которых осматривались 12 сентября 2001года.

Определить квартиры и дома минимальной рентной стоимости.

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

8. Вывести список всех женщин-менеджеров

Определить максимальную зарплату сотрудников в отделении в Гродно

Определить количество осмотров с группировкой по датам.

Создать представление о количестве сделанных осмотров с комментариями.

9. Определить сколько объектов было осмотрено потенциальными арендаторами за Октябрь 1996 года

Создать список сотрудников предлагающих объекты недвижимости в Минске.

Определить суммарную рентную стоимость объектов в Минске и Гродно.

Создать представление о сотрудниках, чьи фамилии начинаются с буквы ‘О’.

10. Кто из арендаторов желает снять 4-хкомнатные квартиры.

Определить сколько потенциальных арендаторов осмотрели предлагаемые им квартиры или дома.

Определить какие из офисов имеют более 3-х сотрудников.

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

· Контрольные вопросы

1. Как вы понимаете значение NULL?

2. Какова общая структура запроса на извлечение информации

3. Перечислите особенности итоговых запросов.

4. Что такое представление и для чего создаются такие объекты базы данных.

⇐ Предыдущая 123 4 5 6 7 8 9 Следующая ⇒