Сборник упражнении по стандарту sql




НазваниеСборник упражнении по стандарту sql
страница3/7
Дата02.10.2012
Размер1.02 Mb.
ТипДокументы
1   2   3   4   5   6   7

Рис. 11. Результат многотабличного запроса

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

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

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

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

Вычисления

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

SELECT Name, Lastname, Surname, Post, (Date() - Date_input)/365.25 FROM Staff ORDER BY Dateinput



Name

Lastname

Surname

Post

Ехр_5

Анна

Михайловна

Иванова

Строитель

25.1061

Савел

Игнатьевич

Соянов

Строитель

24.4873

Иван

Васильевич

Артемьев

Главный инженер

6.8583

Василий

Михайлович

Сидоров

Начальник отдела кадров

5.1006

Иван

Петрович

Иванов

Бухгалтер

4.6899

Петр

Аркадьевич

Васильков

Специалист отдела кадров

4.0548

Виктор

Семенович

Ушаков

Бухгалтер

1.0897

Рис. 12. Результат запроса с вычислением

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

- на VFP:

17 PDF created with pdfFactory Pro trial version www.pdffactorv.com

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

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

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

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

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

SELECT AVG(Sum_pay) FROM Paies

AVG() - функция вычисляет среднее всех значений, содержащихся в столбце.

COUNT( ) - функция подсчитывает количество значений, содержащихся в столбце.

COUNT(*) - функция подсчитывает количество строк в таблице результатов запроса.

МАХ( ) - функция находит наибольшее среди всех значений, содержащихся в столбце.

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

SUM() - функция вычисляет сумму всех значений, содержащихся в столбце.

35. Вывести список сотрудников и суммарную зарплату каждого: SELECT Name, Lastname, Surname, Staff.Tnumber, SUM(Sum_pay)

FROM Staff, Paies WHERE (Staff. Tnumber = Paies. Tnumber) GROUP BY

Staff. T number

PDF created with pdfFactory Pro trial version www.pdffactorv.com

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

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

36. Вывести среднюю зарплату каждого сотрудника за прошедший год:

- на VFP:

SELECT Name, Lastname, Surname, Staff. Tnumber, AVG(Sum_pay) FROM Staff, Paies WHERE (Staff.Tnumber = Paies. Tnumber) AND (Pay_day BETWEEN CTOD('01.01.2002') AND CTOD('31.12.2002') ) GROUP BY Staff.Tnumber

- на MS SQL Server:

SELECT Name, Lastname, Surname, Staff.Tnumber, AVG(Sum_pay) FROM Staff, Paies WHERE (Staff.Tnumber = Paies.Tnumber) AND (Payday BETWEEN 'Ol-JAN-2002' AND'31-DEC-2002' ) GROUP BY Staff. Tnumber

- на Access:

SELECT Name, Lastname, Surname, Staff.Tnumber, AVG(Sum_pay) FROM Staff, Paies WHERE (Staff.Tnumber = Paies.Tnumber) AND (Payday BETWEEN #01.01.2002# AND #31.12.2002# ) GROUP BY Staff. Tnumber

37. Вывести количество сотрудников по каждой должности:
SELECT Post, Count(Tnumber) FROM Staff GROUP BY Post

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

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



Min_date_input

Max_date_input

12.11.1979

18.11.2003

Рис. 13. Итоговые значения

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

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

19

PDF created with pdfFactory Pro trial version www.pdffactorv.com

SELECT Name, Lastname, Surname, Staff. Tnumber, SUM(Sum_pay) AS Itog FROM Staff, Paies WHERE (Staff. Tnumber = Paies. Tnumber) GROUP BY Staff.T number



AS -

ключевое

слово,

назначающее

полю

или

выражению

альтернативное

название

поля

которое

будет отражено

в результате

запроса.




























40. Вывести список всех сотрудников, их табельные номера, даты и суммы получения зарплаты на руки и зарплаты, если бы у них не брали 'подоходный налог', результат поместить в столбец SumWithNalog:

SELECT Staff. Tnumber, Name, Surname, Payday, Sum_pay, (Sum_pay-Itemsum) AS SumWithNalog

FROM Staff INNER JOIN Paies INNER JOIN Items_pay

ON Paies.Code_pay = Items_pay.Code_pay

ON Staff. Tnumber = Paies. Tnumber

WHERE Item_pay = 'подоходный налог'

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

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

SELECT (Surname + " + Name + ' '+ Lastname) AS FIO FROM Staff

FIO

Иванов Иван Петрович

Сидоров Василий Михайлович Васильков Петр Аркадьевич Артемьев Иван Васильевич

Соянов Савел Игнатьевич

Ушаков Виктор Семенович

Иванова Анна Михайловна

Рис. 14. Объединение данных

42. Объединить данные фамилии, имена, отчества и названия
должности в одном столбце с названием FlOPost:

SELECT (Surname + ' ' + Name + ' '+ Lastname + ' в должности ' + Post) AS FIO_Post FROM Staff

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

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

20 PDF created with pdfFactory Pro trial version www.pdffactorv.com

- на VFP:

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

Perem_B=GOMONTH(Date(),-l) && дата начала интересующего периода
PeremE = Date() && дата конца интересующего периода

SELECT Name, Lastname, Surname FROM Staff WHERE Datelnput BETWEEN PeremB AND PeremE

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

- на VFP:

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

Perem = 45 SELECT Name, Lastname, Surname FROM Staff WHERE ((Day(Birthday)+Month(Birtliday)*30.5)/365.25-Year(Birtliday)+Year(Date0)) < Perem



Name

Lastname

Surname

Иван

Петрович

Иванов

Петр

Аркадьевич

Васильков

Иван

Васильевич

Артемьев

Савел

Игнатьевич

Соянов

Виктор

Семенович

Ушаков

Рис. 15. Результат запроса с использованием переменных

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

- на VFP:
Local Perem && объявление местной

Perem = 'Ив' переменной

SET ANSI OFF && настройка правила сравнения

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

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

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

SELECT a.Tnumber, Name, Surname, Payday, Sum_pay, (Sum_pay-Itemsum) FROM Staff a, Paies b, Items_pay с WHERE b.Code_pay = c.Code_pay AND a.Tnumber = b.Tnumber AND Item_pay = 'подоходный налог'

21

PDF created with pdfFactory Pro trial version www.pdffactorv.com

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

47. Вывести список сотрудников и суммарную зарплату каждого (рис. 16):

SELECT Name, Lastname, Surname, d.Tnumber, SUM(Sum_pay) FROM Staff d, Paies f WHERE (d.Tnumber = f.Tnumber) GROUP BY d.Tnumber



Name

Lastname

Surname

Т number

Sum_sum_pay

Иван

Петрович

Иванов

1

19607.00

Василий

Михайлович

Сидоров

2

5732.00

Петр

Аркадьевич

Васильков

3

7595.00

Савел

Игнатьевич

Соянов

4

2456.00

Рис. 16. Результат запроса

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

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

Выбор результата в курсор

49. Вывести все сведения о зарплатах сотрудника с фамилией 'Алеев'
и именем 'Павел' и поместить результат во временную таблицу с
названием Tempi:

SELECT Name, Lastname, Surname, Sum_pay, PayDay FROM Staff, Paies INTO CURSOR Tempi WHERE (Staff. Tnumber = Paies. Tnumber) AND Surname = 'Алеев' AND Name = 'Павел'

i Для того чтобы использовать результаты запроса в дальнейшем \
i коде программы, необходимо запрос сохранить либо на диске в i
i таблице (ключевая фраза INTO DBF или INTO TABLE) с заданным i
: названием, либо во временной таблице, которая сохраняется только на i
| период работы программы или в рамках сессии. ;

i INTO CURSOR - поместить результат запроса во временную
таблицу с указанным названием (в примере Tempi), которая будет i
i удалена из памяти по окончании работы программы. :

50. Вывести все сведения о сотрудниках с табельными номерами 12-54 и поместить результат во временную таблицу с названием Тетр2 (рис. 17):

22

PDF created with pdfFactory Pro trial version www.pdffactorv.com

SELECT * FROM Staff INTO CURSOR Temp2 WHERE Tnumber BETWEEN 12 AND 54

T number

Surname

Name

Lastname

Birthday

15

Иванова

Анна

Михайловна

12.03.1940

Date_input

12.11.1979

Рис. 17. Фрагмент выбора результата в курсор

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

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

премию:

SELECT DISTINCT Name, Lastname, Surname FROM Staff , Paies

WHERE Staff. Tnumber = Paies. Tnumber AND EXISTS(SELECT * FROM

Items_pay WHERE Items_pay.Code_pay = Paies. Code_pay AND

пет_рау='премия')

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

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

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

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

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



Surname

Name

Lastname

Иванов

Иван

Петрович

Васильков

Петр

Аркадьевич

Рис. 18. Результат запроса

с использованием квантора

существования

23

PDF created with pdfFactory Pro trial version www.pdffactorv.com

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

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

SELECT Surname, Name, Lastname, Sum_pay, PayDay FROM Staff INNER JOIN PAIES ON Staff.T number = Paies.Tnumber WHERE Paies.Sum_pay>(SELECT AVG(Sum_pay) FROM Paies)



Surname

Name

Lastname

Sum_pay

Pay_day

Иванов

Иван

Петрович

12542.00

01.03.2003

Иванов

Иван

Петрович

4521.00

01.02.2003

Васильков

Петр

Аркадьевич

4511.00

01.01.2003

Рис. 19. Результат запроса с подзапросом

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

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

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

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

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

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

57. Изменить значение Post на 'нет сведений', если значение поля
является пустым:

UPDATE Staff SET Post = 'нет сведений' WHERE Post = "

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

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

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

24 PDF created with pdfFactory Pro trial version www.pdffactorv.com

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

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

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

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

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

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

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

60. Добавить в таблицу Items_pay новую запись, причем так, чтобы код
зарплаты был 45, название статьи зарплаты = 'премия', а размер премии =
1500 руб. (рис. 20):

INSERT INTO Items_pay(Code_pay, Item_pay, Itemsum) VALUES(45, 'премия', 1500)

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

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

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

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

25 PDF created with pdfFactory Pro trial version www.pdffactorv.com

Code_pay

ltem_pay

Item sum

1

Оклад

1457.00

1

Поощрение

4512.00

1

Оплата учебы

145.00

2

Оклад

4656.00

2

Налог

-415.00

2

Поощрение

326.00

3

Оклад

1654.00

3

Премия квартальная

1213.00

10

За бездетность

-154.00

10

Оклад

1456.00

10

Премия разовая

1245.00

10

Налог подоходный

-452.00

45

Премия

1500.00

Рис. 20. Результат запроса с использованием оператора Insert

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

При выполнении команды обратите внимание на выполнение правил целостности баз данных.

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

DELETE FROM Items_pay WHERE пет_рау='не известно'

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

DELETE FROM Paies WHERE Tnumber = 0 AND Sum_pay = 0

1   2   3   4   5   6   7

Похожие:

Сборник упражнении по стандарту sql iconЛабораторная работа Использование редактора запросов sql borland sql explorer(TM) для создания запросов к данным с использованием языка sql

Сборник упражнении по стандарту sql iconTeaching Oracle to Write sql*Plus (Or using sql as a Code Generator)

Сборник упражнении по стандарту sql iconСтруктурированных запросов sql в
...
Сборник упражнении по стандарту sql iconЛабораторная работа №1. Создание функций на pl/sql
Функции предназначены для использования в sql-предложениях, обращающихся к соответствующим отношениям. Они, по возможности, не должны...
Сборник упражнении по стандарту sql iconЛабораторная работа №5 посвящена оптимизации запросов и работе с индексами
Лабораторные работы заключаются в изучении языка sql. Работы выполняются в sql plus под управлением системы Oracle (версии не ниже...
Сборник упражнении по стандарту sql iconЭкзаменационные вопросы интернет-курсов интуит (intuit): 241. Проектирование информационных систем в Microsoft sql server 2008 и Visual Studio 2008
Если при создании новой пользовательской функции используется одна sql команда после служебного слова return, то в этом случае
Сборник упражнении по стандарту sql iconПро порядок організованого закінчення 2011/2012 навчального року та проведення
Державного стандарту початкової загальної освіти та Державного стандарту базової І повної середньої освіти
Сборник упражнении по стандарту sql icon2011/2012 навчального року та проведення державної підсумкової атестації учнів
Державного стандарту початкової загальної освіти та Державного стандарту базової І повної середньої освіти
Сборник упражнении по стандарту sql iconУкраїна харківська міська рада харківської області виконавчий комітет
Державного стандарту початкової загальної освіти та Державного стандарту базової І повної загальної середньої освіти
Сборник упражнении по стандарту sql iconКен Хендедерсон хотел написать самую лучшую из возможных книг практическое программирование на Transact-sql. Ему это удалось. Когда он начинал ее писать, в
Книга предназначена для программистов. Она написана, чтобы помочь разработчикам создавать приложения, которые используют Transact-sql....
Разместите кнопку на своём сайте:
Библиотека


База данных защищена авторским правом ©lib.znate.ru 2014
обратиться к администрации
Библиотека
Главная страница