Практика SQL 2.1. Создать таблицы, используя sql запросы. Связать таблицы в схеме данных или в таблицах в режиме конструктора. Заполните таблицы в режиме таблицы
 Скачать 153.19 Kb.
|
|
Представленная ниже структура БД позволяет вести учет сотрудников, работающих на предприятии, а также хранить все выданные заработные платы с их расшифровкой по доходным и расходным статьям (например, оклад, районный коэффициент, подоходный налог и т.д.). Создать таблицы, используя SQL запросы. Связать таблицы в схеме данных или в таблицах в режиме конструктора. Заполните таблицы в режиме таблицы.  Связь между таблицами осуществляется с помощью следующих пар полей с типом связи «один-ко-многим» соответственно: Staff. Tnumber- Paies. Tnumber. Paies.Code_pay - Items_pay.Code_pay. Таблица 1Список сотрудников (таблица Staff)
Таблица 2 Таблица учета выданной зарплаты (таблица Paies)
Таблица 3 Таблица расшифровки каждой зарплаты по статьям (таблица Itemspay)
Таблица 4 Пример заполнения таблицы Staff
Таблица 5 Пример заполнения таблицы Paies(фрагмент)
Таблица 6 Пример заполнения таблицы Itemspay(фрагмент)
Часть 1 Сортировка Вывести все сведения о сотрудниках из таблицы Staff и отсортировать результат по табельному номеру. Вывести список фамилий, имен, отчеств сотрудников, их должности, отсортировать результат по названиям должностей по возрастанию и по фамилиям по убыванию: ORDER BY- сортирует результаты запроса на основании данных, содержащихся в одном или нескольких столбцах, по умолчанию сортировка выполняется по возрастанию. Если это предложение не указано, результаты запроса не будут отсортированы. ASC- сортировка данных по возрастанию значений поля, после которого стоит ключевое слово ASC. DESC- сортировка данных по убыванию значений поля, после которого стоит ключевое слово DESC. Если сортировка выполняется по нескольким полям, то порядок сортировки следующий: выполняется сортировка строк по первому указанному полю; внутри групп повторяющихся значений первого поля выполняется сортировка строк по второму полю; и т.д. Выбор некоторых полей из двух (трех) таблиц Вывести табельные номера, даты получения зарплаты и ее расклад по статьям, результат отсортировать по табельному номеру сотрудника (рис. 4):
Рис. 4. Результат запроса с выбором полей из таблиц Вывести список фамилий и табельных номеров сотрудников, а также значения их заработных плат и даты получения с раскладкой каждой зарплаты по статьям: Если в запросе участвует несколько таблиц и в них встречаются поля с одинаковыми названиями, то обязательно рядом с полем указывать название таблицы, из которой берется поле. Например: Staff. Tnumber AND- "логическое И", выполняет роль объединения двух условий и возвращает результат ИСТИНА, оба условия также возвращают результат ИСТИНА. В результат запроса помещаются только те строки, которые соответствуют условиям=ИСТИНА, записанным после ключевого слова WHERE. Связь таблиц в запросе - несмотря на то, что в базе данных установлены связи между таблицами, при построении запроса нужно также указать правила связи между таблицами. Самый простой способ связать таблицы: в условии WHERE указать условия равенства полей связи пары таблиц; если нужно объединить три и более таблиц, то нужно перечислить пары полей связи и объединить их "логическими И". Условие неточного совпадения Вывести список сотрудников с должностью, название которой начинается на 'главный' (рис. 5):
Рис. 5. Результат запроса с условием неточного совпадения ' ... ' - для выделения в запросе строковых значений используются одинарные кавычки. Например, 'главный'. Вывести список сотрудников, которые получали заработную плату со статьей 'премия' за что-либо. Вывести список сотрудников, фамилии которых начинаются с 'Ива'. Точное несовпадение значений одного из полей Вывести список сотрудников и их должности, которые не являются служащими: NOT( ) - функция "логического НЕ". В примере если условие в скобках вернет ИСТИНУ, то функция NOT( ) изменит его на противоположное ЛОЖЬ и в результат строка помещена не будет. Поэтому в запросе будут выбраны только те работники, которые не являются служащими. Выбор записей по диапазону значений (Between) Вывести список сотрудников и размеры полученных зарплат за период 01.01.2003 по 01.03.2003 (рис. 7):
Рис. 7. Выбор по диапазону BETWEEN – проверка на принадлежность диапазону значений. При этом проверяется, находится ли значение поля между двумя определенными значениями. Вывести список сотрудников, у которых фамилия начинается на одну из букв диапазона 'Р' - 'У': Выбор записей по диапазону значений (In) Вывести список сотрудников с должностями 'начальник отдела кадров', 'специалист отдела кадров', 'операционист отдела кадров': IN( ) - проверка на членство в множестве. Вывести только те строки, у которых значение указанного поля принадлежит указанному множеству, т.е. равно одному из значений, перечисленных в IN(). Вывести список сотрудников, получающих одну из следующих надбавок к зарплате: 'премию', 'оплату учебы', 'поощрение': Выбор записей с использованием Like Вывести неповторяющийся список статей в зарплате, которые начинаются на букву 'н': LIKE( ) - проверка на соответствие шаблону, где шаблон записывается в двойных кавычках. % или * - подстановочный знак в шаблоне, | совпадающий с любой последовательностью из нуля и более символов. | _ или ? - подстановочный знак в шаблоне, совпадающий с одним любым символом на указанном месте. Пример шаблона на Access: "?нар*" вывести все строки, у которых первый символ любой, далее обязательная последовательность нар, конец строки любой. Вывести список сотрудников, отчества которых содержат сочетание букв 'ва': Выбор записей по нескольким условиям Вывести всех сотрудников, которые получили зарплату 15.03.2003в размере от 2000 до 3000 руб. Вывести НЕПОВТОРЯЮЩИЙСЯ список табельных номеров и имен сотрудников с табельными номерами 12-30 или с зарплатами, превысившими размер 5000 руб. Вывести список сотрудников с датами рождения 01.01.1950-01.01.1960 или табельными номерами из диапазона 10-150 (рис. 10):
Рис. 10. Результат запроса с несколькими условиями Вычисление итоговых значений с использованием агрегатных функций Вывести среднюю зарплату, которая когда-либо выдавалась на предприятии: AVG() - функция вычисляет среднее всех значений, содержащихся в столбце. COUNT( ) - функция подсчитывает количество значений, содержащихся в столбце. COUNT(*) - функция подсчитывает количество строк в таблице результатов запроса. МАХ( ) - функция находит наибольшее среди всех значений, содержащихся в столбце. MIN( ) - функция находит наименьшее среди всех значений, содержащихся в столбце. SUM() - функция вычисляет сумму всех значений, содержащихся в столбце. Вывести список сотрудников и суммарную зарплату каждого. GROUP BY позволяет создавать итоговый запрос. Обычный запрос включает в результат по одной строке для каждой строки из базы данных. Итоговый запрос, напротив, вначале группирует строки базы данных по определенному признаку, а затем включает в результаты запроса одну итоговую строку для каждой группы. Предложение GROUP BY позволяет вести расчет итогов внутри каждой группы, в данном случае расчет суммарной зарплаты каждого сотрудника. Если бы мы не использовали GROUP BY, то в результате получили бы сумму зарплат всех сотрудников без разбиения по сотрудникам. Вывести среднюю зарплату каждого сотрудника за 2002 год. Вывести количество сотрудников по каждой должности. Вывести дату устройства на работу самого первого и последнего сотрудника (рис. 13):
Рис. 13. Итоговые значения |