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




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



Т number

Code_pay

Pay day

Sum_pay

1

1

01.01.2003

2544.00

1

2

01.02.2003

4521.00

1

3

01.03.2003

12542.00

2

4

01.01.2003

1452.00

2

5

01.02.2003

2145.00

6


Окончание таблицы 5



Т number

Code_pay

Pay day

Sum_pay

2

6

01.03.2003

2135.00

3

7

01.01.2003

4511.00

3

8

01.02.2003

1542.00

3

9

01.03.2003

1542.00

4

10

01.03.2003

2456.00

Таблица 6 Пример заполнения таблицы Itemspay (фрагмент)



Code_pay

Item_pay

Item sum

Code Items




Премия

124.00

1




Налог

-451.00

2




Оклад

1457.00

3




Поощрение

4512.00

4




Оплата учебы

145.00

5

2

Оклад

4656.00

6

2

Налог

-415.00

7

2

Поощрение

326.00

8

3

Оклад

1654.00

9

3

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

1213.00

10

10

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

-154.00

11

10

Оклад

1456.00

12

10

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

1245.00

13

10

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

-452.00

14

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

Сортировка

1. Вывести все сведения о сотрудниках из таблицы Staff и отсортировать результат по табельному номеру: SELECT * FROM Staff ORDER BY T number

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

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

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

FROM - ключевое слово, после которого указывается имя источника/ов данных (если источников несколько, то они разделяются запятыми) для выполнения запроса.

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

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

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

ASC - сортировка данных по возрастанию значений поля, после которого стоит ключевое слово ASC.

DESC - сортировка данных по убыванию значений поля, после которого стоит ключевое слово DESC.

Если сортировка выполняется по нескольким полям, то порядок сортировки следующий:

  • выполняется сортировка строк по первому указанному полю;

  • внутри групп повторяющихся значений первого поля выполняется сортировка строк по второму полю;

-и т.д.

3.Выбрать из таблицы Paies табельные номера сотрудников и даты получения зарплат и отсортировать результат по дате по убыванию (см. рис. 2):

SELECT Tnumber, Payday FROM Paies ORDER BY Payday DESC



T Number

Pay_day

1

01.03.2003

2

01.03.2003

3

01.03.2003

4

01.03.2003

1

01.02.2003

2

01.02.2003

3

01.02.2003

1

01.01.2003

2

01.01.2003

3

01.01.2003

Рис. 2. Сортировка по дате

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

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

4.Вывести все сведения о сотрудниках из таблицы Staff таким образом,

чтобы в результате порядок столбцов был следующим: Name, Lastname,

Surname, Post, Dateinput, Phone, Birthday, Tnumber, Type_post (см. рис. З):

SELECT Name, Lastname, Surname, Post, Dateinput, Phone,

Birthday, Tnumber, Type_post FROM Staff



Surname

Post

Date_input

Phone

Birthday

Т number

Type_post

Иванов

Бухгалтер

12.04.2000

124563

12.01.1971

1

Служащий

Сидоров

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

14.11.1999

451263

14.06.1954

2

ИТР

Васильков

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

30.11.2000

145236

14.06.1981

3

Служащий

Артемьев

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

10.02.1998

365462

05.12.1970

67

ИТР

Соянов

Строитель

25.06.1980

121212

15.05.1981

4

Рабочий

Ушаков

Бухгалтер

18.11.2003

156462

30.05.1970

11

Рабочий

Иванова

Строитель

12.11.1979

145214

12.03.1940

15

Служащий

Рис. 3. Результат запроса изменения порядка полей

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

SELECT Sum_pay, Payday, Tnumber, Code_pay FROM Staff

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

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

SELECT Surname, Name, Lastname, Sum_pay, Payday FROM Staff, Paies WHERE Staff.T number = Paies.T number

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

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

SELECT Tnumber, Payday, Item_pay, Itemsum FROM Paies, Items_pay WHERE Paies.Code_pay = Items_pay.Code_pay ORDER BY Tnumber

9

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

T Number

Pay_day

ltem_pay

Item sum

1

01.01.2003

Премия

124.00

1

01.01.2003

Налог

-451.00

1

01.01.2003

Оклад

1457.00

1

01.01.2003

Поощрение

4512.00

1

01.01.2003

Оплата учебы

145.00

1

01.02.2003

Оклад

4656.00

1

01.02.2003

Налог

-415.00

1

01.02.2003

Поощрение

326.00

1

01.03.2003

Оклад

1654.00

1

01.03.2003

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

1213.00

4

01.03.2003

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

-154.00

4

01.03.2003

Оклад

1456.00

4

01.03.2003

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

1245.00

4

01.03.2003

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

-452.00

Рис. 4. Результат запроса с выбором полей из таблиц

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

SELECT Surname, Staff. Tnumber, Sum_pay, Payday, Item_pay, Itemsum FROM Staff, Paies, Items_pay WHERE (Staff.Tnumber = Paies. Tnumber) AND (Paies. Code_pay = Items_pay.Code_pay)

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

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

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

Самый простой способ связать таблицы: в условии WHERE указать условия равенства полей связи пары таблиц; если нужно объединить три и более таблиц, то нужно перечислить пары полей связи и объединить их "логическими И", как показано в примере.

10

Условие неточного совпадения

9.Вывести список сотрудников с должностью, название которой начинается на 'главный' (рис. 5):

SELECT Surname, Name, Lastname, Post FROM Staff WHERE Post = 'главный'



Surname

Name

Lastname

Post

Артемьев

Иван

Васильевич

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

Рис. 5. Результат запроса с условием неточного совпадения

' ... ' - для выделения в запросе строковых значений используются одинарные кавычки. Например, 'главный'.

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

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

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

SELECT Surname, Name, Lastname FROM Staff WHERE Surname = 'Ива'

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

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

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

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

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

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

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

Name

Lastname

Surname

Date_input

Василий

Михайлович

Сидоров

14.11.1999

Петр

Аркадьевич

Васильков

30.11.2000

Иван

Васильевич

Артемьев

10.02.1998

Савел

Игнатьевич

Соянов

25.06.1980

Анна

Михайловна

Иванова

12.11.1979

Рис. 6. Список сотрудников, не являющихся бухгалтерами

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

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

- на VFP:

SELECT Name, Lastname, Surname, Sum_pay, Payday FROM Staff, Paies WHERE (Staff Tnumber = Paies. Tnumber) AND Payday BETWEEN CTOD('01.01.2003') AND CTOD('01.03.2003')



Name

Lastname

Surname

Sum_pay

Pay_day

Иван

Петрович

Иванов

2544.00

01.01.2003

Иван

Петрович

Иванов

4521.00

01.02.2003

Иван

Петрович

Иванов

12542.00

01.03.2003

Василий

Михайлович

Сидоров

1452.00

01.01.2003

Василий

Михайлович

Сидоров

2145.00

01.02.2003

Василий

Михайлович

Сидоров

2135.00

01.03.2003

Петр

Аркадьевич

Васильков

4511.00

01.01.2003

Петр

Аркадьевич

Васильков

1542.00

01.02.2003

Петр

Аркадьевич

Васильков

1542.00

01.03.2003

Савел

Игнатьевич

Соянов

2456.00

01.03.2003

Рис. 7. Выбор по диапазону

- на MS SQL Server:

SELECT Name, Lastname, Surname, Sum_pay FROM Staff, Paies WHERE (Staff Tnumber = Paies. Tnumber) AND Payday BETWEEN "l-JAN-2003" AND "l-MAR-2003"

- на Access:

SELECT Name, Lastname, Surname, Sum_pay FROM Staff, Paies WHERE (Staff Tnumber = Paies. Tnumber) AND Payday BETWEEN #01.01.2003# AND#01.03.2003#



BETWEEN

- проверка на

принадлежность

диапазону

значений.

При этом проверяется, находится

ли

значение

поля

между

двумя

определенными

значениями.






















12

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

i Особенности оформления дат в различных СУБД . i

| В Access дата заключается в решетки # # (формат дд.мм.гггг). i

■ В VFP оформляется как строка и преобразуется в формат даты с ■
i помощью функции CTOD(). \

• В MS SQL Server можно задать как строку в двойных кавычках в i
i формате дд-мес-гггг (где месяц может быть оформлен как: JAN, FEB, i
| MAR, APR, MAY, JUN, JUL, AUG, SEP, ОСТ, NOV, DEC). j

15. Вывести список сотрудников, которые были устроены на работу в
период с 12.03.2000 по 15.06.2000, и их должности:

- на VFP:

SELECT Name, Lastname, Surname, Post FROM Staff WHERE Dateinput BETWEEN CTOD012.03.2000') AND CTOD('15.06.2000')

- на MS SQL Server:

SELECT Name, Lastname, Surname, Post FROM Staff WHERE Dateinput BETWEEN "12-MAR-2000" AND "15-JUN-2000"

- на MS Access:

SELECT Name, Lastname, Surname, Sumday FROM Staff WHERE Dateinput BETWEEN #12.03.2000# AND #15.06.2000#

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

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

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

SELECT Name, Lastname, Surname FROM Staff WHERE Surname BETWEEN 'P' AND 'У'

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

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

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

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

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

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

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

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

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



Name

Lastname

Surname

Т number

Иван

Васильевич

Артемьев

67

Савел

Игнатьевич

Соянов

4

Рис. 8. Список сотрудников по указанным номерам

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

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

- на VFP, MS SQL Server:

SELECT DISTINCT Item_pay FROM Items_pay WHERE Item_pay LIKE "h%"

- на Access:

SELECT DISTINCT Item_pay FROM Items_pay WHERE Item_pay LIKE "h*"

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

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

- на VFP, MS SQL Server:

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

- на Access:

SELECT Name, Lastname, Surname FROM Staff WHERE Lastname LIKE "*ва*"

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

23. Выбрать неповторяющийся список должностей, у которых значение оканчивается на 'ль' (рис. 9): - на VFP, MS SQL Server: SELECT DISTINCT Post FROM Staff WHERE Post LIKE "%ль"

Post

Строитель

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

- на Access:

SELECT DISTINCT Post FROM Staff WHERE Post LIKE "*ль"

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

24. Вывести всех сотрудников, которые получили зарплату 15.03.2003
в размере от 2000 до 3000 руб.:

- на VFP:

SELECT Name, Lastname, Surname FROM Staff, Paies WHERE (Staff.Tnumber = Paies.Tnumber) AND Payday = CTOD('15.03.2003') AND ((Sum_pay>=2000) AND (Sum_pay<3000))

- на MS SQL Server:

SELECT Name, Lastname, Surname FROM Staff, Paies WHERE (Staff. Tnumber = Paies. Tnumber) AND Payday = '15-MAR-2003' AND ((Sum_pay>=2000) AND (Sum_pay<3000))

- на Access:

SELECT Name, Lastname, Surname FROM Staff, Paies WHERE (Staff.Tnumber = Paies.Tnumber) AND Payday = #15.03.2003# AND ((Sum_pay>=2000) AND (Sum_pay<3000))

25. Вывести НЕПОВТОРЯЮЩИЙСЯ список табельных номеров и
имен сотрудников с табельными номерами 12-30 или с зарплатами,
превысившими размер 5000 руб.:

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

26. Вывести список сотрудников с датами рождения 01.01.1950 -
01.01.1960 или табельными номерами из диапазона 10-150 (рис. 10):

- на VFP:

SELECT Name, Lastname, Surname, Birthday, Tnumber FROM Staff
WHERE (Birthday BETWEEN CTOD('01.01.1950') AND

CTOD('01.01.1960')) OR (T_number>=10 AND T_number<=150)

Name Lastname Surname Birthday T number

15

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

Василий

Михайлович

Сидоров

14.06.1954

2

Иван

Васильевич

Артемьев

05.12.1970

67

Виктор

Семенович

Ушаков

30.05.1970

11

Анна

Михайловна

Иванова

12.03.1940

15

Рис. 10. Результат запроса с несколькими условиями

- на MS SQL Server:

SELECT Name, Lastname, Surname, Birthday, Tnumber FROM Staff WHERE (Birthday BETWEEN 'Ol-JAN-1950' AND 'Ol-JAN-1960') OR (T_number>=10 AND T_number<=150)

- на Access:

SELECT Name, Lastname, Surname, Birthday, Tnumber FROM Staff WHERE (Birthday BETWEEN #01.01.1950# AND #01.01.1960#) OR (T_number>=10 AND T_number<=150)

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

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

SELECT 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 ПЧСпремия', 'оплата учебы', 'поощрение')

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

LEFT JOIN создает объединение пары таблиц, из которого выбираются все записи из левой таблицы, а также записи из правой таблицы, значения поля связи которой совпадают со значениями поля связи левой таблицы.

RIGHT JOIN создает объединение пары таблиц, из которой выбираются все записи из правой таблицы, а также записи из левой таблицы, значения поля связи которой совпадают со значениями поля связи правой таблицы.

ON - ключевое слово, после которого указывается условие связи пары таблиц.

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

16

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

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



Name

Lastname

Surname

Василий

Михайлович

Сидоров

Иван

Петрович

Иванов

Савел

Игнатьевич

Соянов
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
обратиться к администрации
Библиотека
Главная страница