Материал: 2391

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

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

– в VFP, MS SQL Server, Access:

SELECT RTRIM(Name)+' '+RTRIM(Lastname)+' '+Surname, Staff.T_number, SUM(Sum_pay) FROM Staff, Pay WHERE (Staff.T_number = Pay.T_number) GROUP BY Staff.T_number, RTRIM(Name) + ' ' + RTRIM(Lastname)+' '+Surname

– в Oracle:

SELECT RTRIM(Name)+' '+RTRIM(Lastname)+' '+Surname, Staff.T_number, SUM(Sum_pay) FROM ADMIN_PAY.Staff, ADMIN_PAY.Pay WHERE (Staff.T_number = Pay.T_number) GROUP BY Staff.T_number, RTRIM(Name) + ' ' + RTRIM(Lastname)+' '+Surname;

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

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

HAVING позволяет выводить не все результаты группировки, а только те, которые удовлетворяют указанному условию. После конструкции HAVING можно указывать только условия на агрегатные функции.

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

– в VFP:

SELECT Name, Staff.T_number, AVG(Sum_pay) FROM Staff, Pay WHERE (Staff.T_number = Pay.T_number) AND (Pay_day BETWEEN CTOD(’01.01.2002’) AND CTOD(’31.12.2002’) ) GROUP BY Staff.T_number, Name HAVING AVG(Sum_pay)>10000

33

– в MS SQL Server:

SELECT Name, Staff.T_number, AVG(Sum_pay) FROM Staff, Pay WHERE (Staff.T_number = Pay.T_number) AND (Pay_day BETWEEN '01- JAN-2002' AND '31-DEC-2002' ) GROUP BY Staff.T_number, Name HAVING AVG(Sum_pay)>10000

– в Access:

SELECT Name, Staff.T_number, AVG(Sum_pay) FROM Staff, Pay WHERE (Staff.T_number = Pay.T_number) AND (Pay_day BETWEEN #01.01.2002# AND #31.12.2002# ) GROUP BY Staff.T_number, Name HAVING AVG(Sum_pay)>10000

– в Oracle:

SELECT Name, Staff.T_number, AVG(Sum_pay) FROM ADMIN_PAY.Staff, ADMIN_PAY.Pay WHERE (Staff.T_number = Pay.T_number) AND (Pay_day BETWEEN '01-JAN-2002' AND '31-DEC- 2002' ) GROUP BY Staff.T_number, Name HAVING AVG(Sum_pay)>10000;

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

– в VFP, MS SQL Server, Access:

SELECT Post, Count(T_number) FROM Staff GROUP BY Post HAVING Count(T_number)<5

– в Oracle:

SELECT Post, Count(T_number) FROM ADMIN_PAY.Staff GROUP BY Post HAVING Count(T_number)<5;

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

– в VFP, MS SQL Server, Access:

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

– в Oracle:

SELECT Min(Date_input), Max(Date_input) FROM ADMIN_PAY.Staff;

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

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

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

– в MS SQL Server:

34

SELECT Name+Lastname+Surname AS [Ф.И.О.], Staff.T_number, SUM(Sum_pay) AS Itog FROM Staff, Pay WHERE (Staff.T_number = Pay.T_number) GROUP BY Staff.T_number, Name+Lastname+Surname

– в Oracle:

SELECT Name+Lastname+Surname AS "Ф.И.О.", Staff.T_number, SUM(Sum_pay) AS Itog FROM ADMIN_PAY.Staff, ADMIN_PAY.Pay WHERE (Staff.T_number = Pay.T_number) Group by Staff.T_number, Name+Lastname+Surname;

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

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

– в VFP, MS SQL Server, Access:

SELECT Staff.T_number, Name, Surname, Pay_day, Sum_pay, (Sum_payItem_sum) AS Sum_With_Nalog FROM Staff INNER JOIN Pay INNER JOIN Items_pay ON Pay.Code_pay = Items_pay.Code_pay ON Staff.T_number = Pay.T_number WHERE Item_pay = 'подоходный налог'

– в Oracle:

SELECT Staff.T_number, Name, Surname, Pay_day, Sum_pay, (Sum_payItem_sum) AS Sum_With_Nalog FROM ADMIN_PAY.Staff INNER JOIN ADMIN_PAY.Pay INNER JOIN ADMIN_PAY.Items_pay ON Pay.Code_pay

=Items_pay.Code_pay ON Staff.T_number = Pay.T_number WHERE Item_pay = 'подоходный налог';

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

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

– в VFP, MS SQL Server, Access:

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

– в Oracle:

SELECT (RTRIM(Surname) + ' ' + RTRIM(Name) + ' '+ Lastname) AS FIO FROM ADMIN_PAY.Staff;

FIO

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

Сидоров Василий Михайлович

Васильков Петр Аркадьевич

35

Артемьев Иван Васильевич

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

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

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

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

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

– в VFP, MS SQL Server, Access:

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

– в Oracle:

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

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

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

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

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

– в MS SQL Server:

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

Declare @Perem_B DateTime, @Perem_E DateTime -- дата начала интересующего периода

SET @Perem_B=DATEADD ( month , -1, getdate()) -- дата конца интересующего периода

SET @Perem_E = GetDate( )

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

– в Oracle:

SET SERVEROUTPUT ON;

Declare

Perem_B Date;

Perem_E Date;

36

Name_ ADMIN_PAY.Staff.Name%TYPE;

Lastname_ ADMIN_PAY.Staff.Lastname%TYPE;

Surname_ ADMIN_PAY.Staff.Surname%TYPE;

BEGIN

Perem_B:= ADD_MONTHS(Sysdate,-1) ; Perem_E:= Sysdate; DBMS_OUTPUT.PUT_LINE(Perem_B||' '||Perem_E);

SELECT Name, Lastname, Surname INTO Name_, Lastname_, Surname_ FROM ADMIN_PAY.Staff WHERE Date_Input BETWEEN Perem_B AND Perem_E;

END;

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

(рис. 15):

– в VFP:

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

Perem = 45

SELECT Name, Lastname, Surname FROM Staff WHERE ((Day(Birthday)+Month(Birthday)*30.5)/365.25-Year(Birthday)+Year(Date()))

<Perem

–в MS SQL Server: Declare @Perem Int -- назначение возраста

SET @Perem= 45

SELECT Name, Lastname, Surname FROM Staff WHERE CAST( (getdate( )-Birthday) AS INT) < @Perem

–в Oracle:

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

Declare

Perem number(2);

Name_ ADMIN_PAY.Staff.Name%TYPE; Lastname_ ADMIN_PAY.Staff.Lastname%TYPE; Surname_ ADMIN_PAY.Staff.Surname%TYPE;

BEGIN

--назначение возраста

Perem:= 45;

SELECT Name, Lastname, Surname INTO Name_, Lastname_, Surname_ FROM ADMIN_PAY.Staff WHERE trunc((Sysdate-Birthday)/365.25) < Perem;

END;

Name Lastname Surname

37