Материал: 2391

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

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

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

‘Ив’:

– в VFP:

SELECT Name, Lastname, Surname FROM Staff WHERE Surname =

Perem

– в MS SQL Server:

Declare @Perem VarChar(10) -- назначение переменной

SET @Perem= 'Ив'

SELECT Name, Lastname, Surname FROM Staff WHERE Surname LIKE RTRIM(@Perem)+'%'

– в Oracle:

Declare

Perem VarChar2(10);

Surname_ ADMIN_PAY.Staff.Surname%TYPE; -- назначение переменной

BEGIN Perem:= 'Ив';

SELECT Surname INTO Surname_ FROM ADMIN_PAY.Staff WHERE Surname LIKE RTRIM(Perem)+'%';

END;

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

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

– в VFP, MS SQL Server, Access:

SELECT a.T_number, Name, Surname, Pay_day, Sum_pay, (Sum_payItem_sum) FROM Staff a, Pay b, Items_pay c WHERE b.Code_pay =

38

c.Code_pay AND a.T_number = b.T_number AND Item_pay = 'подоходный налог'

– в Oracle:

SELECT a.T_number, Name, Surname, Pay_day, Sum_pay, (Sum_payItem_sum) FROM ADMIN_PAY.Staff a, ADMIN_PAY.Pay b, ADMIN_PAY.Items_pay c WHERE b.Code_pay = c.Code_pay AND a.T_number = b.T_number AND Item_pay = 'подоходный налог';

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

45. Вывести список сотрудников и суммарную зарплату каждого

(рис. 16):

– в VFP, MS SQL Server, Access:

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

– в Oracle:

SELECT Surname, d.T_number, SUM(Sum_pay) FROM ADMIN_PAY.Staff d, ADMIN_PAY.Pay f WHERE (d.T_number = f.T_number) GROUP BY d.T_number, Surname;

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

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

– в VFP, MS SQL Server, Access:

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

– в Oracle:

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

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

39

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

– в VFP:

SELECT Name, Lastname, Surname, Sum_pay, Pay_Day FROM Staff, Pay INTO CURSOR Temp1 WHERE (Staff.T_number = Pay.T_number) AND Surname = ‘Алеев’ AND Name = ‘Павел’

– в MS SQL Server:

Declare TEMP1 CURSOR FOR SELECT Name, Lastname, Surname,

Sum_pay, Pay_Day FROM Staff, Pay WHERE (Staff.T_number =

Pay.T_number) AND Surname = 'Алеев' AND Name = 'Павел'

– в Oracle (с примером построчного вывода данных из курсора):

SET SERVEROUTPUT ON

DECLARE

Name1 ADMIN_PAY.Staff.Name%TYPE;

Lastname1 ADMIN_PAY.Staff.Lastname%TYPE;

Surname1 ADMIN_PAY.Staff.Surname%TYPE;

Sum_pay1 ADMIN_PAY.Pay.Sum_pay%TYPE;

Pay_Day1 ADMIN_PAY.Pay.Pay_Day%TYPE;

CURSOR TEMP1 IS SELECT Name, Lastname, Surname, Sum_pay, Pay_Day FROM ADMIN_PAY.Staff, ADMIN_PAY.Pay WHERE (Staff.T_number = Pay.T_number) AND Surname = 'Алеев' AND Name = 'Павел';

BEGIN OPEN TEMP1;

WHILE TEMP1%found LOOP

FETCH TEMP1 INTO Name1, Lastname1, Surname1, Sum_pay1, Pay_Day1;

DBMS_OUTPUT.PUT_LINE(Name1||' '|| Lastname1||' '||Surname1||' '|| Sum_pay1||' '|| Pay_Day1);

END LOOP;

CLOSE TEMP1; END;

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

INTO CURSOR – поместить результат запроса во временную

40

таблицу с указанным названием (в примере Temp1), которая будет удалена из памяти по окончании работы программы.

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

– в VFP:

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

– в MS SQL Server:

Declare Temp2 CURSOR FOR SELECT * FROM Staff WHERE T_number BETWEEN 12 AND 54

– в Oracle:

DECLARE

CURSOR TEMP1 IS SELECT * FROM ADMIN_PAY.Staff WHERE

T_number BETWEEN 12 AND 54;

BEGIN

Null;

END;

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

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

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

– в VFP, MS SQL Server, Access:

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

– в Oracle:

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

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

41

имеет ненулевой результат, ЛОЖЬ – если условие вернуло пустое множество.

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

– в VFP, MS SQL Server, Access:

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

– в Oracle:

SELECT Surname, Name, Lastname FROM ADMIN_PAY.Staff WHERE NOT EXISTS(SELECT * FROM ADMIN_PAY.Pay WHERE Staff.T_number

=Pay.T_number);

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

– в VFP, MS SQL Server, Access:

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

– в Oracle:

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

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

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

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

– в VFP, MS SQL Server, Access:

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

– в Oracle:

SELECT Surname, Name, Lastname, Sum_pay, Pay_Day FROM ADMIN_PAY.Staff INNER JOIN ADMIN_PAY.PAY ON Staff.T_number =

42