Учебное пособие Омск • 2008 Федеральное агентство по образованию




НазваниеУчебное пособие Омск • 2008 Федеральное агентство по образованию
страница8/15
Дата15.01.2013
Размер1.47 Mb.
ТипУчебное пособие
1   ...   4   5   6   7   8   9   10   11   ...   15
24

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

- в VFP, MS SQL Server, Access:

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

- в Oracle:

SELECT RTRIM(Name)+' '+RTRIM(Lastname)+' '+Surname,
Staff. Tnumber,
SUM(Sum_pay) FROM ADMIN_PAY. Staff,

ADMINPAY.Pay WHERE (Staff. Tnumber = Pay.Tnumber) GROUP BY Staff. Tnumber, RTRIM(Name) + " + RTRIM(Lastname)+' '+Surname;

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

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

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

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

- в VFP:

SELECT Name, Staff.Tnumber, AVG(Sum_pay) FROM Staff, Pay WHERE (Staff.Tnumber = Pay.Tnumber) AND (Payday BETWEEN CTOD('01.01.2002') AND CTOD('31.12.2002') ) GROUP BY Staff.Tnumber, Name HAVING AVG(Sum_pay)> 10000

- в MS SQL Server:

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

- в Access:

SELECT Name, Staff.Tnumber, AVG(Sum_pay) FROM Staff, Pay WHERE (Staff.Tnumber = Pay.Tnumber) AND (Payday BETWEEN

25

#01.01.2002# AND #31.12.2002# ) GROUP BY Staff.Tnumber, Name HAVING AVG(Sum_pay)>10000

- в Oracle:

SELECT Name, Staff.T number, AVG(Sum_pay) FROM ADMINPAY. Staff, ADMINPAY.Pay WHERE (Staff. Tnumber = Pay.Tnumber) AND (Payday BETWEEN '01-JAN-2002' AND '31-DEC-2002') GROUP BY Staff.Tnumber, Name HAVING AVG(Sum_pay)>10000;

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

- в VFP, MS SQL Server, Access:

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

- в Oracle:

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

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


- в VFP, MS SQL Server, Access:

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

- в Oracle:

SELECT Min(Dateinput), Max(Dateinput) FROM ADMINPAY. Staff;



Min_date_input

Max_date_input

12.11.1979

18.11.2003

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

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

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

- в MS SQL Server:

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

- в Oracle:

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

26

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

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

- в VFP, MS SQL Server, Access:

SELECT Staff. Tnumber, Name, Surname, Payday, Sum_pay, (Sum_pay-Itemsum) AS SumWithNalog FROM Staff INNER JOIN Pay INNER JOIN Items_pay ON Pay.Code_pay = Items_pay.Code_pay ON Staff. Tnumber = Pay.Tnumber WHERE Item_pay = 'подоходный налог'

- в Oracle:

SELECT Staff. Tnumber, Name, Surname, Payday, Sum_pay, (Sum_pay-Itemsum) AS SumWithNalog FROM ADMINPAY. Staff INNER JOIN ADMINPAY.Pay INNER JOIN ADMIN_PAY.Items_pay ON Pay.Code_pay = Items_pay.Code_pay ON Staff. Tnumber = Pay.Tnumber 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 ADMINPAY. Staff;

FIO

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

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

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

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

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

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

40. Объединить данные фамилии, имена, отчества и названия должности в одном столбце с названием FlOPost: - в VFP, MS SQL Server, Access:

27

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

- в Oracle:

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

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

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

- в VFP:

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

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

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

- в 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 Datelnput BETWEEN @Perem_B AND @Perem_E

- в Oracle:

SET SERVEROUTPUT ON;

Declare

PeremB Date;

PeremE Date;

Name_ ADMINPAY. Staff .Name%TYPE;

Lastname_ ADMINPAY. Staff.Lastname%T YPE;

Surname_ ADMINPAY. Staff. Surname%T YPE;

BEGIN

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

28

SELECT Name, Lastname, Surname INTO Name_, Lastname_, Surname_ FROM ADMINPAY. Staff WHERE Datelnput BETWEEN PeremB AND PeremE;

END;

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

- в VFP:

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

Perem = 45 SELECT Name, Lastname, Surname FROM Staff WHERE ((Day(Birthday)+Month(Birthday)*30.5)/365.25-Year(Birtliday)+Year(Date0)) < 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_ ADMINPAY. Staff .Name%TYPE;

Lastname_ ADMINPAY. Staff.Lastname%T YPE;

Surname_ ADMINPAY. Staff. Surname%T YPE; BEGIN

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

Perem:= 45;

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

Name

Lastname

Surname

Иван

Петрович

Иванов

Петр

Аркадьевич

Васильков

Иван

Васильевич

Артемьев

Савел

Игнатьевич

Соянов

Виктор

Семенович

Ушаков

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

29

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


- в VFP:

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

Perem = 'Ив'

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

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

- в MS SQL Server:
Declare @Perem VarChar(lO)
~ назначение переменной
SET @Perem= 'Ив'

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

- в Oracle:
Declare

Perem VarChar2(10);

Surname_ ADMINPA Y. Staff. Surname%T YPE; ~ назначение переменной BEGIN Perem := 'Ив';

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

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

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


- в VFP, MS SQL Server, Access:

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

- в Oracle:

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

1   ...   4   5   6   7   8   9   10   11   ...   15

Похожие:

Учебное пособие Омск • 2008 Федеральное агентство по образованию iconУчебное пособие омск 2008 федеральное агентство по образованию государственное образовательное учреждение высшего профессионального образования
Учебное пособие предназначено для студентов и аспирантов высших учебных заведений, специализирующихся в области физики конденсированного...
Учебное пособие Омск • 2008 Федеральное агентство по образованию iconУчебное пособие Омск 2009 министерство образования и науки российской федерации федеральное агентство по образованию
Пушнина Западной Сибири: лосновы товароведения и ранок: учебное пособие / О. В. Гончарова Омск: Издатель ип погорелова, 2009. – 362...
Учебное пособие Омск • 2008 Федеральное агентство по образованию iconУчебное пособие Тамбов 2008 федеральное агентство по образованию тамбовский государственный университет им. Г. Р. Державина эмпирическая социология учебное пособие для студентов, обучающихся по специальности
Белинская Д. В., кандидат социологических наук (Предисловие, Раздел Специфика социологического метода, Раздел Методы социологического...
Учебное пособие Омск • 2008 Федеральное агентство по образованию iconУчебное пособие Челябинск 2006 Министерство образования и науки Российской Федерации Федеральное агентство по образованию
Учебное пособие предназначено для студентов университета дневной и заочной форм обучения по специальности физическая культура
Учебное пособие Омск • 2008 Федеральное агентство по образованию iconУчебное пособие томск 2007 Федеральное агентство по образованию

Учебное пособие Омск • 2008 Федеральное агентство по образованию iconУчебное пособие 2010 Федеральное агентство по образованию Государственное образовательное учреждение высшего профессионального образования
Приемы программирования в среде visual basic for application: учебное пособие / Ф. И. Воробьева, Е. С. Воробьев. – Казань: Изд-во...
Учебное пособие Омск • 2008 Федеральное агентство по образованию iconУчебное пособие ч елябинск 2 006 Министерство образования и науки Российской Федерации Федеральное агентство по образованию Южно-Уральский государственный университет Кафедра «Основы медицинских знаний»
Учебное пособие предназначено для студентов университета дневной и заочной форм обучения по специальности «Физическая культура»
Учебное пособие Омск • 2008 Федеральное агентство по образованию iconУчебное пособие 2010 Федеральное агентство по образованию Государственное образовательное учреждение высшего профессионального образования
Интеллектуальная автоматика в курсовых и дипломных проектах: учебное пособие (Том 2)/ В. П. Ившин, М. Ю. Перухин, И. А. Дюдина, А....
Учебное пособие Омск • 2008 Федеральное агентство по образованию iconФедеральное агентство по образованию государственное образовательное учреждение высшего профессионального образования
Биохимия белков и ферментов : учеб метод пособие / С. Е. Синютина, С. В. Романцова; Федеральное агентство по образованию, гоувпо...
Учебное пособие Омск • 2008 Федеральное агентство по образованию iconУчебное пособие Архангельск Поморский университет
Федеральное агентство по образованию Государственное образовательное учреждение высшего профессионального образования
Разместите кнопку на своём сайте:
Библиотека


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