Упражнения с использованием операторов обработки данных SQL

Оператор создания базы данных CREATE DATABASE

Оператор создания структуры таблицы CREATE TABLE

Оператор создания хранимой процедуры CREATE PROCEDURE

Оператор модификации структуры таблицы ALTER TABLE

Оператор удаления таблицы DROP TABLE

Оператор удаления базы данных DROP DATABASE

Сортировка

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

SELECT * FROM Staff ORDER BY T_number

SELECT – ключевое слово, обозначающее начало SQL-запроса, за которым обычно следует перечень полей, информация из которых помещается в результат выполнения запроса. * – условное обозначение, которое позволит помещать в результат запроса информацию из всех полей таблицы, в которой осуществляется поиск (в некоторых СУБД используется ключевое слово ALL). FROM – ключевое слово, после которого указывается имя источника/ов данных (если источников несколько, то они разделяются запятыми) для выполнения запроса.

2. Вывести список фамилий И.О. сотрудников, их должности, отсортировать результат по названиям должностей по возрастанию и по фамилиям по убыванию:

SELECT Surname, Name, Lastname, Post FROM Staff ORDER BY Post ASC, Surname DESC

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

Изменение порядка следования полей

3. Вывести все сведения о сотрудниках из таблицы Staff таким образом, чтобы в результате порядок столбцов был следующим: Name, Lastname, Surname, Post, Date_input, Phone, Birthday, T_number, Type_post:

SELECT Name, Lastname, Surname, Post, Date_input, Phone, Birthday, T_number, Type_post FROM Staff

4. Выбрать все поля из таблицы Paies таким образом, чтобы в результате порядок столбцов был следующим: Sum_pay, Pay_day, T_number, Code_pay:

SELECT Sum_pay, Pay_day, T_number, Code_pay FROM Paies

Выбор некоторых полей из двух (трех) таблиц

5. Вывести список фамилий, имен, отчеств сотрудников (поля Surname, Name, Lastname), а также значения их заработных плат (поле Sum_pay) и даты получения (поле Sum_pay):

SELECT Surname, Name, Lastname, Sum_pay, Pay_day FROM Staff, Paies WHERE Staff.T_number = Paies.T_number

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

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

SELECT T_number, Pay_day, Item_pay, Item_sum FROM Paies, Items_pay WHERE Paies.Code_pay = Items_pay.Code_pay ORDER BY T_number

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

SELECT Surname, Staff.T_number, Sum_pay, Pay_day, Item_pay, Item_sum FROM Staff, Paies, Items_pay WHERE (Staff.T_number = Paies.T_number) AND (Paies.Code_pay = Items_pay.Code_pay)

Если в запросе участвует несколько таблиц и в них встречаются поля с одинаковыми названиями, то обязательно рядом с полем указывать название таблицы, из которой берется поле. Например: Staff.T_number AND - "логическое И", выполняет роль объединения двух условий и возвращает результат ИСТИНА, оба условия также возвращают результат ИСТИНА. В результат запроса помещаются только те строки, которые соответствуют условиям=ИСТИНА, записанным после ключевого слова WHERE. Связь таблиц в запросе - несмотря на то, что в базе данных установлены связи между таблицами при построении запроса нужно также указать правила связи между таблицами. Самый простой способ связать таблицы: в условии WHERE указать условия равенства полей связи пары таблиц, если нужно объединить три и более таблиц, то нужно перечислить пары полей связи и объединить их "логическими И", как показано в примере.

Точное несовпадение значений одного из полей

8. Вывести список сотрудников и их должности, которые не являются служащими:

SELECT Surname, Name, Lastname, Post FROM Staff WHERE NOT(Type_post = ‘служащий’)

NOT() - функция "логического НЕ". В примере, если условие в скобках вернет ИСТИНУ, то функция NOT() изменит его на противоположное ЛОЖЬ и в результат строка помещена не будет. Поэтому в запросе будут выбраны только те работники, которые не являются служащими.

9. Вывести список сотрудников и их даты поступления на работу, которые не являются бухгалтерами:

SELECT Name, Lastname, Surname, Date_input FROM Staff WHERE NOT(Post = ‘бухгалтер’)

Выбор записей по диапазону значений (Between)

10. Вывести список сотрудников и размеры полученных зарплат за период 01.01.2003 по 01.03.2003:

- на VFP:

SELECT Name, Lastname, Surname, Sum_pay, Pay_day FROM Staff, Paies WHERE (Staff.T_number = Paies.T_number) AND Pay_day BETWEEN CTOD(‘01.01.2003’) AND CTOD(‘01.03.2003’)

- на MS SQL Server:

SELECT Name, Lastname, Surname, Sum_pay FROM Staff, Paies WHERE (Staff.T_number = Paies.T_number) AND Pay_day BETWEEN ‘1-JAN-2003’ AND ‘1-MAR-2003’

- на Access:

SELECT Name, Lastname, Surname, Sum_pay FROM Staff, Paies WHERE (Staff.T_number = Paies.T_number) AND Pay_day BETWEEN CDate(‘01.01.2003’) AND CDate(‘01.03.2003’)

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

Особенности оформления дат в различных СУБД В Access дата заключается в решетки # # (формат дд.мм.гггг) либо передается как строка с преобразованием к дате через функцию CDate(). В VFP оформляется как строка и преобразуется в формат даты с помощью функции CTOD(). В MS SQL Server можно задать как строку в апострофах в формате дд-мес буквами-гггг (где месяц может быть оформлен как: JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC). В MS SQL Server по умолчанию дата передается в апострофах в формате мм.дд.гггг или мм/дд/гггг

11. Вывести список сотрудников и их телефоны, значения которых находятся в диапазоне с 111111 по 222222:

SELECT Name, Lastname, Surname, Phone FROM Staff WHERE Phone BETWEEN 111111 AND 222222

12. Вывести список сотрудников, у которых фамилия начинается на одну из букв диапазона ‘Р’-‘У’:

SELECT Name, Lastname, Surname FROM Staff WHERE Surname BETWEEN ‘Р’ AND ‘У’

Выбор записей по диапазону значений (In)

13. Вывести список сотрудников с должностями ‘начальник отдела кадров’, ‘специалист отдела кадров’, ‘операционист отдела кадров’:

SELECT Name, Lastname, Surname FROM Staff WHERE Post IN(‘начальник отдела кадров’, ‘специалист отдела кадров’, ‘операционист отдела кадров’)

IN() - проверка на членство в множестве. Вывести только те строки, у которых значение указанного поля принадлежит указанному множеству, т.е. равно одному из значений, перечисленных в IN().

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

SELECT Name, Lastname, Surname FROM Staff, Paies, Items_pay WHERE (Staff.T_number = Paies.T_number) AND (Paies.Code_pay = Items_pay.Code_pay) AND (Item_pay IN(‘премия’, ‘оплата учебы’, ‘поощрение’))

15. Вывести список сотрудников с табельными номерами 4, 67, 45, 77:

SELECT Name, Lastname, Surname FROM Staff WHERE T_Number IN(4, 67, 45, 77)

Выбор записей с использованием Like

16. Вывести неповторяющийся список статей в зарплате, которые начинаются на букву ‘н’:

- на VFP, MS SQL Server:

SELECT DISTINCT Item_pay FROM Items_pay WHERE Item_pay LIKE ‘н%’

- на Access:

SELECT DISTINCT Item_pay FROM Items_pay WHERE Item_pay LIKE(“н*”)

LIKE - проверка на соответствие шаблону, где шаблон записывается в двойных кавычках. % или * - подстановочный знак в шаблоне, совпадающий с любой последовательностью из нуля и более символов. _ или? - подстановочный знак в шаблоне, совпадающий с одним любым символом на указанном месте. Пример шаблона на Access: "?нар*" - вывести все строки, у которых первый символ любой, далее обязательная последовательность нар, конец строки любой.

17. Вывести список сотрудников, отчества которых содержат сочетание букв ‘ва’:

- на VFP, MS SQL Server:

SELECT Name, Lastname, Surname FROM Staff WHERE Lastname LIKE ‘%ва%’

- на Access:

SELECT Name, Lastname, Surname FROM Staff WHERE Lastname LIKE(“*ва*”)

Выбор записей по нескольким условиям

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

SELECT DISTINCT Name, Lastname, Surname, Staff.T_number FROM Staff, Paies WHERE (Staff.T_number = Paies.T_number) AND ((T_Number BETWEEN 12 AND 30) OR Sum_pay>5000)

Многотабличные запросы (выборка из двух таблиц, выборка из трех таблиц с использованием JOIN)

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

SELECT Name, Lastname, Surname FROM (Staff INNER JOIN Paies ON Staff.T_number = Paies.T_number) INNER JOIN Items_pay ON Paies.Code_pay = Items_pay.Code_pay WHERE Item_pay IN(‘премия’, ‘оплата учебы’, ‘поощрение’)

INNER JOIN - создает объединение пары таблиц, в котором выбираются только те записи, которые содержат совпадающие значения в полях связи, указанных после ключевого слова ON. LEFT JOIN - создает объединение пары таблиц, в котором выбираются все записи из левой таблицы, а также записи из правой таблицы, значения поля связи которого совпадают со значениями поля связи левой таблицы. RIGHT JOIN - создает объединение пары таблиц, в котором выбираются все записи из правой таблицы, а также записи из левой таблицы, значения поля связи которого совпадают со значениями поля связи правой таблицы. ON - ключевое слово, после которого указывается условие связи пары таблиц.

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

SELECT DISTINCT Name, Lastname, Surname FROM Staff INNER JOIN Paies ON Staff.T_number = Paies.T_number WHERE (Sum_pay>=2000) AND (Sum_pay<3000)

21. Вывести коды зарплат, в которых была статья вычетов ‘за бездетность’:

SELECT Paies.Code_pay FROM Paies INNER JOIN Items_pay ON Paies.Code_pay = Items_pay.Code_pay WHERE Item_pay = ‘за бездетность’

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

SELECT DISTINCT Name, Lastname, Surname FROM (Staff INNER JOIN Paies ON Staff.T_number = Paies.T_number) INNER JOIN Items_pay ON Paies.Code_pay = Items_pay.Code_pay WHERE Item_pay = ‘за бездетность’

Вычисления

23. Вывести список сотрудников, должности и срок их работы в годах с сортировкой по уменьшению стажа:

- на MS SQL Server:

SELECT Name, Lastname, Surname, Post, (cast((GetDate() - Date_input) as bigint))/365.25 FROM Staff ORDER BY Date_input

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

- на vfp:

set date to german &&необходима для установки даты в формате дд.мм.гг

SELECT Name, Lastname, Surname, Post, Birthday, CTOD(str(day(Birthday))+'.'+str(month(Birthday))+'.'+str(YEAR(Date())))-DATE() FROM Staff Where CTOD (str(day(Birthday)) + '. ' + str (month(Birthday)) + '. ' + str (YEAR(Date())))-DATE()) >0

25. Вывести список всех сотрудников, их табельные номера, даты и суммы получения зарплаты на руки и зарплаты, если бы у них не брали налог ‘за бездетность’:

SELECT Staff.T_number, Name, Surname, Pay_day, Sum_pay, (Sum_pay-Item_sum) FROM (Staff INNER JOIN Paies ON Staff.T_number = Paies.T_number) INNER JOIN Items_pay ON Paies.Code_pay = Items_pay.Code_pay WHERE Item_pay = 'за бездетность'

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

Вычисление итоговых значений с использованием агрегатных функций

26. Вывести среднюю зарплату, которая когда-либо выдавалась на предприятии:

SELECT AVG(Sum_pay) FROM Paies

AVG() - функция вычисляет среднее всех значений, содержащихся в столбце. COUNT() - функция подсчитывает количество значений, содержащихся в столбце. COUNT(*) - функция подсчитывает количество строк в таблице результатов запроса. MAX() - функция находит наибольшее среди всех значений, содержащихся в столбце. MIN() - функция находит наименьшее среди всех значений, содержащихся в столбце. SUM() - функция вычисляет сумму всех значений, содержащихся в столбце.

27. Вывести список табельных номеров сотрудников и суммарную зарплату каждого:

SELECT Staff.T_number, SUM(Sum_pay) FROM Staff, Paies WHERE (Staff.T_number = Paies.T_number) Group by Staff.T_number

GROUP BY - позволяет создавать итоговый запрос. Обычный запрос включает в результат по одной строке для каждой строки из базы данных. Итоговый запрос, напротив, вначале группирует строки базы данных по определенному признаку, а затем включает в результаты запроса одну итоговую строку для каждой группы. Предложение Group by позволяет вести расчет итогов внутри каждой группы, в данном случае расчет суммарной зарплаты каждого сотрудника. Если бы мы не использовали Group by, то в результате получили бы сумму зарплат всех сотрудников без разбиения по сотрудникам.

28. Вывести количество сотрудников по каждой должности:

SELECT Post, Count(T_number) FROM Staff Group by Post

29. Вывести дату устройства на работу самого первого и последнего сотрудника:

SELECT Min(Date_input), Max(Date_input) FROM Staff

Изменение наименований полей

30. Вывести список табельных номеров сотрудников и суммарную зарплату каждого, которую поместить в поле с названием Itog:

SELECT Staff.T_number, SUM(Sum_pay) AS Itog FROM Staff, Paies WHERE (Staff.T_number = Paies.T_number) Group by Staff.T_number

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

31. Объединить данные фамилии, имени, отчества в одном столбце с названием FIO:

SELECT (Surname + ‘ ’ + Name + ‘ ’+ Lastname) AS FIO FROM Staff

Использование переменных в условии

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

- на VFP:

Local Perem_B, Perem_E Perem_B=GOMONTH(Date(),-1) Perem_E = Date()

&& объявление частной переменной && дата начала интересующего периода && дата конца интересующего периода

SELECT Name, Lastname, Surname FROM Staff WHERE Date_Input BETWEEN Perem_B AND Perem_E

33. Вывести список сотрудников, возраст которых меньше заданного:

- на MS SQL Server:

DECLARE @Perem int SET @Perem = 45

&& объявление местной переменной

SELECT Name, Lastname, Surname FROM Staff WHERE

(Year(GETDATE())-Year(Birthday)+(Day(Birthday)+Month(Birthday)*30.5)/365.25)< @Perem

34. Вывести список сотрудников, с фамилиями начинающимися на ‘Ив’:

- на VFP

Local Perem Perem = ‘Ив’ SET ANSI OFF

&& объявление местной переменной && настройка правила сравнения

SELECT Name, Lastname, Surname FROM Staff WHERE Surname = Perem

Использование переменных вместо названий таблиц

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

SELECT a.T_number, Name, Surname, Pay_day, Sum_pay FROM Staff a, Paies b, Items_pay c WHERE b.Code_pay = c.Code_pay AND a.T_number = b.T_number

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

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

SELECT Name, Lastname, Surname, b.Code_pay FROM Staff a, Paies b, Items_pay c WHERE b.Code_pay = c.Code_pay AND a.T_number = b.T_number AND Item_pay IN('премия', 'оплата учебы', 'поощрение')

Использование совместно с подзапросом квантора существования

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

SELECT DISTINCT Name, Lastname, Surname FROM Staff, Paies WHERE Staff.T_number = Paies.T_number AND EXISTS(SELECT * FROM Items_pay WHERE Items_pay.Code_pay = Paies.Code_pay AND Item_pay='премия')

EXISTS() - квантор существования, понятие, заимствованное из формальной логики. Возвращает два значения либо ИСТИНА, либо ЛОЖЬ. ИСТИНА - если условие, указанное в скобках, выполнилось и имеет ненулевой результат, ЛОЖЬ - если условие вернуло пустое множество.

38. Вывести список сотрудников, которые ни разу не получали зарплаты:

SELECT Surname, Name, Lastname FROM Staff WHERE NOT EXISTS(SELECT * FROM Paies WHERE Staff.T_number = Paies.T_number)

39. Вывести список сотрудников, у которых размер зарплаты не меньше 3000 руб.:

SELECT Surname, Name, Lastname FROM Staff WHERE EXISTS(SELECT * FROM Paies WHERE Staff.T_number = Paies.T_number AND Sum_pay >=3000)

Использование функций совместно с подзапросом

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

SELECT Surname, Name, Lastname, Sum_pay, Pay_Day FROM Staff INNER JOIN PAIES ON Staff.T_number = Paies.T_number WHERE Paies.Sum_pay>(SELECT AVG(Sum_pay) FROM Paies)

Оператор обработки данных Update

41. Перевести всех сотрудников в статус ‘ИТР’, у которых название должности начинается с ‘главный’:

UPDATE Staff SET Type_post = ‘ИТР’ WHERE Post = ‘главный’

Оператор UPDATE - обновляет значения одного или нескольких столбцов в выбранных строках одной таблицы. В операторе указывается целевая таблица, которая должна быть модифицирована, при этом пользователь должен иметь право на обновление. Предложение WHERE отбирает строки таблицы, подлежащие обновлению. В предложении SET указывается, какие столбцы должны быть обновлены, и для них закладываются новые значения.

42. Перевести всех сотрудников в статус ‘почет.пенсионер’, а значение должности удалить, если стаж их работы больше 20 лет и возраст больше 60 лет:

UPDATE Staff SET Type_post = ‘почет.пенсионер’, Post = ‘’ WHERE (Date()-Date_Input)/365.25>20 AND (Date()-Birthday)/365.25>60

43. Измените значение Post на ‘нет сведений’, если значение поля является пустым:

UPDATE Staff SET Post = ‘нет сведений’ WHERE Post = ‘’

Оператор обработки данных Insert

44. Добавьте в таблицу сотрудников новую запись, причем так, чтобы табельный номер был автоматически увеличен на 1, а в должности стояло значение ‘нет сведений’:

SELECT MAX(T_number) AS Max_ FROM Staff INTO CURSOR Temp

INSERT INTO Staff(T_number, Post)VALUES(Temp.Max_+1, ‘нет сведений’)

Перед выполнением оператора добавления новой строки использован SELECT для того, чтобы определить максимальное значение ключевого индексного поля таблицы, и при добавлении новой строки автоматически нарастить его, тем самым не нарушая целостности таблицы (исключая появление в ключевом поле двух одинаковых значений). Оператор INSERT - добавляет в таблицу новую строку. В предложении INTO указывается таблица, в которую добавляется новая строка (целевая таблица), а в предложении VALUES содержатся значения данных для новой строки. Список столбцов определяет, какие значения в какой столбец заносятся. В столбцы, не перечисленные в первых скобках, будут автоматически записаны значения типа NULL (пустые или неопределенные). Список столбцов в первых скобках должен строго соответствовать списку записываемых значений во вторых скобках.

45. Добавьте в таблицу Paies новую запись, причем так, чтобы код зарплаты был автоматически увеличен на 1, табельный номер =23, дата зарплаты = текущей дате, а размер зарплаты = 5000:

SELECT MAX(Code_pay) AS Max_ FROM Paies INTO CURSOR Temp INSERT INTO Paies(T_number, Code_pay, Pay_day, Sum_pay) VALUES(23, Temp.Max_+1, Date(), 5000)

Данная команда INSERT сработает, если в главной таблице Staff есть запись с T_number=23 или при отсутствии поддержки целостности БД.

46. Добавьте в таблицу Items_pay новую запись, причем так, чтобы код зарплаты был 45, название статьи зарплаты = ‘премия’, а размер премии = 1500 руб.:

INSERT INTO Items_pay(Code_pay, Item_pay, Item_sum) VALUES(45, ‘премия’, 1500)

Данная команда INSERT сработает, если в главной таблице Paies есть запись с Code_pay=45 или при отсутствии поддержки целостности БД.

Оператор обработки данных Delete

47. Удалить из таблицы всех сотрудников, у которых возраст больше 80:

DELETE FROM Staff WHERE (Date()-Birthday)>80

Оператор DELETE - удаляет выбранные строки данных из одной таблицы. В предложении FROM указывается таблица, содержащая строки. Которые требуется удалить. В предложении WHERE указываются строки, которые должны быть удалены. При выполнении команды обратите внимание на выполнение правил целостности баз данных.

48. Удалить из таблицы Статьи зарплат (таблица Items_pay) все записи, у которых в поле название статьи зарплаты = ‘не известно’:

DELETE FROM Items_pay WHERE Item_pay=‘не известно’

49. Удалить из таблицы зарплат все записи, у которых размер зарплаты=0 и табельный номер сотрудника=0:

DELETE FROM Paies WHERE T_number = 0 AND Sum_pay = 0

Оператор назначения привилегии GRANT

Оператор отмены привилегии REVOKE

Оператор запрета привилегии DENY

Дата добавления: 2014-01-04