Материал: 2391

Pay.T_number WHERE Pay.Sum_pay>(SELECT AVG(Sum_pay) FROM

ADMIN_PAY.Pay);

Рис. 19. Результат запроса с подзапросом

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

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

– в VFP:

SET ANSI OFF

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

– в MS SQL Server:

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

– в Oracle:

UPDATE ADMIN_PAY.Staff SET Type_post = 'ИТР' WHERE Post LIKE 'главный%';

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

54. Перевести всех сотрудников в статус ‘пенс.’, а значение должности удалить, если стаж их работы больше 20 лет и возраст больше

60лет:

–в VFP:

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

– в MS SQL Server:

UPDATE Staff SET Type_post = 'пенс.', Post = ' ' WHERE CAST(DATEDIFF(Year,Date_Input,GetDate()) AS INT)>20 AND CAST(DATEDIFF(Year,Birthday,GetDate()) AS INT)>60

– в Oracle:

43

UPDATE ADMIN_PAY.Staff SET Type_post = 'пенс.', Post = ' ' WHERE

(Sysdate-Date_Input)/365.25>20 AND (SysdateBirthday)/365.25>60;

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

– в VFP:

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

– в MS SQL Server:

UPDATE Staff SET Post = 'нет сведений' WHERE RTRIM(Post) = '' OR Post IS Null

– в Oracle:

UPDATE ADMIN_PAY.Staff SET Post = 'нет сведений' WHERE RTRIM(Post) = '' OR Post IS Null;

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

56. Добавить в таблицу сотрудников новую запись, причем так, чтобы табельный номер был автоматически увеличен на 1, а в должности стояло значение ‘нет сведений’:

– в VFP (если табельный номер без автонаращивания):

SELECT MAX(T_number) AS Max_ FROM Staff INTO CURSOR Temp INSERT INTO Staff(T_number, Post)VALUES(Temp.Max_+1, ‘нет

сведений’)

–в MS SQL Server (если табельный номер c автонаращиванием, объявленным через IDENTITY(1,1) ):

INSERT INTO Staff(Post)VALUES('нет сведений')

–в MS SQL Server(если табельный номер без автонаращивания): Declare @max bigint

SET @max = (SELECT MAX(T_number) FROM Staff) SET @max = ISNULL(@max, 0)

INSERT INTO Staff(T_number, Post)VALUES(@max+1, 'нет сведений')

–в Oracle (если для табельного номера объявлена последовательность SEQUENCE для генерации уникальных ключей):

INSERT INTO ADMIN_PAY.Staff(T_number, Post)VALUES (ADMIN_PAY.ID_STAFF_SEQ.NextVal, 'нет сведений');

–в Oracle (если для табельного номера нет объявленной последовательности):

DECLARE Max_ int; BEGIN

SELECT MAX(T_number) INTO Max_ FROM ADMIN_PAY.Staff;

44

IF Max_ IS NULL THEN Max_:=0;

END IF;

INSERT INTO ADMIN_PAY.Staff(T_number, Post)VALUES (Max_+1, 'нет сведений');

END;

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

Оператор INSERT добавляет в таблицу новую строку. В предложении INTO указывается таблица, в которую добавляется новая строка (целевая таблица), а в предложении VALUES содержатся значения данных для новой строки. Список столбцов определяет, какие значения в какой столбец заносятся. В столбцы, не перечисленные в первых скобках, будут автоматически записаны значения типа NULL (пустые или неопределенные).

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

57. Добавить в таблицу Pay новую запись, причем так, чтобы код зарплаты был автоматически увеличен на 1, табельный номер =23, дата зарплаты = текущей дате, а размер зарплаты = 5000:

– в VFP (если код зарплаты без автонаращивания):

SELECT MAX(Code_pay) AS Max_ FROM Pay INTO CURSOR Temp INSERT INTO Pay(T_number, Code_pay, Pay_day, Sum_pay) VALUES(23, Temp.Max_+1, Date(), 5000)

–в MS SQL Server (если код зарплаты c автонаращиванием, объявленным через IDENTITY(1,1) ):

INSERT INTO Pay(T_number, Pay_day, Sum_pay) VALUES(23, GetDate(), 5000)

–в MS SQL Server(если код зарплаты без автонаращивания):

Declare @max bigint

SET @max = (SELECT MAX(Code_pay) FROM Pay) SET @max = ISNULL(@max, 0)

INSERT INTO Pay(T_number, Code_pay, Pay_day, Sum_pay) VALUES(23, @max+1, GetDate(), 5000)

45

–в Oracle (если для кода зарплаты объявлена последовательность SEQUENCE для генерации уникальных ключей):

INSERT INTO ADMIN_PAY.Pay(T_number, Code_pay, Pay_day, Sum_pay) VALUES(23, ADMIN_PAY.ID_PAY_SEQ.NextVal, SysDate, 5000);

–в Oracle (если для кода зарплаты нет объявленной последовательности):

DECLARE Max_ int; BEGIN

SELECT MAX(Code_pay) INTO Max_ FROM ADMIN_PAY.Pay; if Max_ IS NULL then

Max_:=0; END IF;

INSERT INTO ADMIN_PAY.Pay(T_number, Code_pay, Pay_day, Sum_pay) VALUES(23, Max_+1, SysDate, 5000);

END;

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

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

–в VFP, MS SQL Server, Access (если ключевое поле Code_Items с

автонаращиванием):

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

–в Oracle (если ключевое поле Code_Items наращивается через

SEQUENCE):

INSERT INTO ADMIN_PAY.Items_pay(Code_pay, Item_pay, Item_sum, Code_Items) VALUES(45, 'премия', 1500, ADMIN_PAY.ID_ITEM_SEQ.NextVal);

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

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

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

80 лет (рис. 20):

46

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

с использованием оператора Insert

– в VFP:

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

– в MS SQL Server:

DELETE FROM Staff WHERE CAST( DATEDIFF( Year, Birthday,

GetDate()) AS INT) >80

– в Oracle:

DELETE FROM ADMIN_PAY.Staff WHERE (Sysdate-

Birthday)/365.25>80;

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

При выполнении команды обратите внимание на выполнение правил целостности баз данных.

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

– в VFP, MS SQL Server, Access:

DELETE FROM Items_pay WHERE Item_pay='не известно'

– в Oracle:

DELETE FROM ADMIN_PAY.Items_pay WHERE Item_pay='не известно';

61.Удалить из таблицы зарплат все записи, в которых значения полей сумма зарплаты и табельный номер сотрудника равны 0:

– в VFP, MS SQL Server, Access:

DELETE FROM Pay WHERE T_number = 0 AND Sum_pay = 0

– в Oracle:

47