Практика SQL 2.2. Многотабличные запросы выборка из двух таблиц, выборка из трех таблиц с использованием

• Название: Многотабличные запросы выборка из двух таблиц, выборка из трех таблиц с использованием
• Анкор: ghfrnbrf
• Дата: 06.01.2022
• Размер: 298.81 Kb.
• Имя файла: Практика SQL 2.2.docx
• Тип: Документы; #324773

Часть 2

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

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

SELECT Name, Lastname, Surname

FROM Staff INNER JOIN (Paies INNER JOIN Itemspay ON Paies.Code_pay = Items_pay.Code_pay) ON Staff. Tnumber = Paies. Tnumber

WHERE Item_pay in (‘премия', 'оплата учебы', 'поощрение')

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

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

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

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

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

SELECT DISTINCT Name, Lastname, Surname

FROM Staff INNER JOIN Paies ON Staff. T number = Paies. Tnumber WHERE (Sum_pay>=2000) AND (Sum_pay<3000)

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

SELECT Paies.Code_pay

FROM Paies INNER JOIN Items_pay ON Paies.Code_pay = Items_pay.Code_pay

WHERE Item_pay = 'забездетность'

Вычисления

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

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

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

SELECT Staff.T_number, Name, Surname, Pay_day, Sum_pay, (Sum_pay-Item_sum)

FROM Staff INNER JOIN (Paies INNER JOIN Itemspay ON Paies.Code_pay = Itemspay.Code_pay) ON Staff.T_number = Paies.T_number

WHERE Item_pay = 'за бездетность'

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

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

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

SELECT Name, Lastname, Surname, Staff. Tnumber, SUM(Sum_pay) AS Itog FROM Staff, Paies

WHERE (Staff. Tnumber = Paies. Tnumber)

GROUP BYStaff.Tnumber, Name, Lastname, Surname

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

22. 
    Вывести список всех сотрудников, их табельные номера, даты и суммы получения зарплаты на руки и зарплаты, если бы у них не брали 'подоходный налог', результат поместить в столбец 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 = 'подоходный налог'

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

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



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

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

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

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

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

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

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 = 'подоходный налог'

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

22. 
    Вывести список сотрудников и суммарную зарплату каждого (рис. 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. Результат запроса

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

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

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

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

SELECT Name, Lastname, Surname, Sum_pay, PayDay INTO Temp1

FROM Staff, Paies

WHERE (Staff. Tnumber= Paies. Tnumber) AND Surname= 'Алеев' AND Name= 'Павел'

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

SELECT * INTO Temp2

FROM Staff

WHERE T_number BETWEEN 12 AND 54



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

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

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

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

Item_рау='премия')

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

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

SELECT Surname, Name, Lastname FROM Staff

WHERE NOT EXISTS(SELECT * FROM Paies WHERE Staff. Tnumber= Paies. Tnumber)

22. 
    Вывести список сотрудников, у которых размер зарплаты не
    меньше 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. Результат запроса с использованием квантора существования

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

22. 
    Вывести список сотрудников и даты с размерами полученных зарплат, которые превысили средний размер их же зарплат (рис. 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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