4. Запись выражений указанных в варианте задания типов запросов на языке sql

В результате работы над курсовым проектом было написано множество хранимых процедур и функций на языке SQL для выполнения определенных манипуляций над данными. Рассмотрим примеры нескольких типов хранимых процедур и функций.

1. Запрос, возвращающий информацию об автомобилях, взятых на технический осмотр

2. Запрос, возвращающий список справок о техническом состоянии автомобилей.

SELECT [AutoShop].[dbo].[Car].[IDCar],

[AutoShop].[dbo].[RefTechnicalCondition].NumberOfRef,

[AutoShop].[dbo].[RefTechnicalCondition].DateIssue,

[AutoShop].[dbo].[RefTechnicalCondition].Expert,

[AutoShop].[dbo].[Conclusions].TypeConclusion,

[AutoShop].[dbo].[RefTechnicalCondition].PriceOfCar

FROM [AutoShop].[dbo].[Car], [AutoShop].[dbo].[RefTechnicalCondition], [AutoShop].[dbo].[Conclusions]

WHERE [AutoShop].[dbo].[RefTechnicalCondition].IDRefTC

=[AutoShop].[dbo].[Car].FKRefTechCondition and

[AutoShop].[dbo].[Conclusions].IDConclusion

= [AutoShop].[dbo].[RefTechnicalCondition].FKConclusion

3. Запрос-представление для оформления сделки с клиентом-покупателем.

SELECT [AutoShop].dbo.Buyers.IDBuyer, [AutoShop].dbo.Car.IDCar , [AutoShop].dbo.TypeOfPay.NamePay, [AutoShop].dbo.Buyers.NumberOfCount,

[AutoShop].dbo.PassportData.LastName, [AutoShop].dbo.PassportData.FirstName,

[AutoShop].dbo.PassportData.Patronymic, [AutoShop].dbo.PassportData.BirthDate, [AutoShop].dbo.PassportData.NumberOfPassport, [AutoShop].dbo.PassportData.DateOfIssue,

[AutoShop].dbo.PassportData.PersonalNumber,

[AutoShop].dbo.Nationalities.NameNationality, [AutoShop].dbo.Countries.NameCountry , [AutoShop].dbo.City.NameCity, [AutoShop].dbo.Addresses.Street, [AutoShop].dbo.Addresses.House,

[AutoShop].dbo.Addresses.Flat

FROM [AutoShop].dbo.Buyers , [AutoShop].dbo.Car, [AutoShop].dbo.Addresses, [AutoShop].dbo.Countries, [AutoShop].dbo.City, [AutoShop].dbo.Nationalities, [AutoShop].dbo.PassportData , [AutoShop].dbo.TypeOfPay

WHERE [AutoShop].dbo.Car.IDCar = [AutoShop].dbo.Buyers.FKCar and

[AutoShop].dbo.Buyers.FKPassport=[AutoShop].dbo.PassportData.IDPassport and [AutoShop].dbo.PassportData.FKAddressRegistr

= [AutoShop].dbo.Addresses.IDAddress and

[AutoShop].dbo.Addresses.FKCountry

= [AutoShop].dbo.Countries.IDCountry and

[AutoShop].dbo.Addresses.FKCity = [AutoShop].dbo.City.IDCity and

[AutoShop].dbo.PassportData.FKNationality

= [AutoShop].dbo.Nationalities.IDNationality and

[AutoShop].dbo.Buyers.FKPay = [AutoShop].dbo.TypeOfPay.IDpay

4. Запрос-представление для оформления сделки с клиентом-покупателем.

SELECT [AutoShop].[dbo].Employee.IDEmployee, [AutoShop].[dbo].Sellers.IDSeller, [AutoShop].[dbo].Orders.DateOfOrder,

[AutoShop].[dbo].Car.IDCar, [AutoShop].[dbo].Prices.PriceOfSell

FROM [AutoShop].[dbo].Employee, [AutoShop].[dbo].Sellers, [AutoShop].[dbo].Orders, [AutoShop].[dbo].Car, [AutoShop].[dbo].Prices

WHERE [AutoShop].[dbo].Employee.IDEmployee=[AutoShop].[dbo].Orders.FKEmployee and

[AutoShop].[dbo].Sellers.IDSeller = [AutoShop].[dbo].Orders.FKSeller and

[AutoShop].[dbo].Car.IDCar = [AutoShop].[dbo].Sellers.FKCar and

[AutoShop].[dbo].Car.FKPrices = [AutoShop].[dbo].Prices.IDPrices

5. Запрос-представление выводящий отчет о приобретенных автомобилях за 30 дней.

SELECT [AutoShop].[dbo].Employee.IDEmployee, [AutoShop].[dbo].Orders.IDOrder, [AutoShop].[dbo].Orders.DateOfOrder,

[AutoShop].[dbo].Prices.PriceOfSell as Price, [AutoShop].[dbo].Car.IDCar, [AutoShop].[dbo].Sellers.IDSeller, [AutoShop].dbo.MarkOfCar.NameMark, [AutoShop].dbo.ModelOfCar.NameModel

FROM [AutoShop].[dbo].Employee, [AutoShop].[dbo].Sellers, [AutoShop].[dbo].Orders, [AutoShop].[dbo].Car, [AutoShop].[dbo].Prices, [AutoShop].dbo.MarkOfCar, [AutoShop].dbo.ModelOfCar

WHERE [AutoShop].[dbo].Employee.IDEmployee=[AutoShop].[dbo].Orders.FKEmployee and

[AutoShop].[dbo].Sellers.IDSeller = [AutoShop].[dbo].Orders.FKSeller and

[AutoShop].[dbo].Car.IDCar = [AutoShop].[dbo].Sellers.FKCar and

[AutoShop].[dbo].Car.FKPrices = [AutoShop].[dbo].Prices.IDPrices and

[AutoShop].[dbo].MarkOfCar.IDMark = [AutoShop].dbo.Car.FKMark and

[AutoShop].[dbo].ModelOfCar.IDModel = [AutoShop].dbo.Car.FKModel and

[AutoShop].[dbo].Orders.DateOfOrder < GetDate() and

[AutoShop].[dbo].Orders.DateOfOrder > DATEADD(DAY, -30,GetDate() )

6. Запрос-представление выводящий отчет о проданных автомобилях за 30 дней

SELECT [AutoShop].[dbo].Employee.IDEmployee, [AutoShop].[dbo].Orders.IDOrder, [AutoShop].[dbo].Orders.DateOfOrder,

[AutoShop].[dbo].Prices.PriceBuy as Price, [AutoShop].[dbo].Car.IDCar, [AutoShop].[dbo].Buyers.IDBuyer, [AutoShop].dbo.MarkOfCar.NameMark, [AutoShop].dbo.ModelOfCar.NameModel

FROM [AutoShop].[dbo].Employee, [AutoShop].[dbo].Buyers, [AutoShop].[dbo].Orders, [AutoShop].[dbo].Car, [AutoShop].[dbo].Prices, [AutoShop].dbo.MarkOfCar, [AutoShop].dbo.ModelOfCar

WHERE [AutoShop].[dbo].Employee.IDEmployee=[AutoShop].[dbo].Orders.FKEmployee and

[AutoShop].[dbo].Buyers.IDBuyer = [AutoShop].[dbo].Orders.FKBuyer and

[AutoShop].[dbo].Car.IDCar = [AutoShop].[dbo].Buyers.FKCar and

[AutoShop].[dbo].Car.FKPrices = [AutoShop].[dbo].Prices.IDPrices and

[AutoShop].[dbo].MarkOfCar.IDMark = [AutoShop].dbo.Car.FKMark and

[AutoShop].[dbo].ModelOfCar.IDModel = [AutoShop].dbo.Car.FKModel and

[AutoShop].[dbo].Orders.DateOfOrder < GetDate() and

[AutoShop].[dbo].Orders.DateOfOrder > DATEADD(DAY, -30,GetDate() )

7. Запрос-представление выводящий отчет о приобретенных автомобилях за текущий месяц

SELECT [AutoShop].[dbo].Employee.IDEmployee, [AutoShop].[dbo].Orders.IDOrder, [AutoShop].[dbo].Orders.DateOfOrder,

[AutoShop].[dbo].Prices.PriceOfSell as Price, [AutoShop].[dbo].Car.IDCar, [AutoShop].[dbo].Sellers.IDSeller, [AutoShop].dbo.MarkOfCar.NameMark, [AutoShop].dbo.ModelOfCar.NameModel

FROM [AutoShop].[dbo].Employee, [AutoShop].[dbo].Sellers, [AutoShop].[dbo].Orders, [AutoShop].[dbo].Car, [AutoShop].[dbo].Prices, [AutoShop].dbo.MarkOfCar, [AutoShop].dbo.ModelOfCar

WHERE [AutoShop].[dbo].Employee.IDEmployee=[AutoShop].[dbo].Orders.FKEmployee and

[AutoShop].[dbo].Sellers.IDSeller = [AutoShop].[dbo].Orders.FKSeller and

[AutoShop].[dbo].Car.IDCar = [AutoShop].[dbo].Sellers.FKCar and

[AutoShop].[dbo].Car.FKPrices = [AutoShop].[dbo].Prices.IDPrices and

[AutoShop].[dbo].MarkOfCar.IDMark = [AutoShop].dbo.Car.FKMark and

[AutoShop].[dbo].ModelOfCar.IDModel = [AutoShop].dbo.Car.FKModel and

[AutoShop].[dbo].Orders.DateOfOrder < GetDate() and

[AutoShop].dbo.Orders.DateOfOrder>DATEADD(DAY,-DAY(GetDate()),GetDate() )

8. Запрос-представление выводящий отчет о проданных автомобилях за текущий месяц

SELECT [AutoShop].[dbo].Employee.IDEmployee, [AutoShop].[dbo].Orders.IDOrder, [AutoShop].[dbo].Orders.DateOfOrder,

[AutoShop].[dbo].Prices.PriceBuy as Price, [AutoShop].[dbo].Car.IDCar, [AutoShop].[dbo].Buyers.IDBuyer, [AutoShop].dbo.MarkOfCar.NameMark, [AutoShop].dbo.ModelOfCar.NameModel

FROM [AutoShop].[dbo].Employee, [AutoShop].[dbo].Buyers, [AutoShop].[dbo].Orders, [AutoShop].[dbo].Car, [AutoShop].[dbo].Prices, [AutoShop].dbo.MarkOfCar, [AutoShop].dbo.ModelOfCar

WHERE [AutoShop].[dbo].Employee.IDEmployee

=[AutoShop].[dbo].Orders.FKEmployee and

[AutoShop].[dbo].Buyers.IDBuyer = [AutoShop].[dbo].Orders.FKBuyer and

[AutoShop].[dbo].Car.IDCar = [AutoShop].[dbo].Buyers.FKCar and

[AutoShop].[dbo].Car.FKPrices = [AutoShop].[dbo].Prices.IDPrices and

[AutoShop].[dbo].MarkOfCar.IDMark = [AutoShop].dbo.Car.FKMark and

[AutoShop].[dbo].ModelOfCar.IDModel = [AutoShop].dbo.Car.FKModel and

[AutoShop].[dbo].Orders.DateOfOrder < GetDate() and

[AutoShop].dbo.Orders.DateOfOrder>DATEADD(DAY,-DAY(GetDate()),GetDate() )

9. Триггер на удаление данных

USE AutoShop

GO

CREATE TRIGGER Delete_MarkOfCar

ON MarkOfCar

INSTEAD OF Delete

AS

DECLARE @id INT

SELECT @id = deleted.IDMark from deleted

DECLARE @c int

SELECT @c=count(ModelOfCar.IDModel) FROM ModelOfCar

WHERE ModelOfCar.FKMark = @id

while @c>0

BEGIN

SELECT @c=count(ModelOfCar.IDModel) FROM ModelOfCar

WHERE ModelOfCar.FKMark = @id

DELETE ModelOfCar WHERE ModelOfCar.FKMark = @id

END

DECLARE @d int

SELECT @d=count(Car.IDCar) FROM Car

WHERE Car.FKMark = @id

while @d>0

BEGIN

SELECT @d=count(Car.IDCar) FROM Car

WHERE Car.FKMark = @id

DELETE Car WHERE Car.FKMark = @id

END

DECLARE @f int

SELECT @f=count(SpareParts.IDSparePart) FROM SpareParts

WHERE SpareParts.FKMark = @id

while @f>0

BEGIN

SELECT @f=count(SpareParts.IDSparePart) FROM SpareParts

WHERE SpareParts.FKMark = @id

DELETE SpareParts WHERE SpareParts.FKMark = @id

END

DELETE MarkOfCar WHERE MarkOfCar.IDMark = @id

10. Триггер на обновление данных

USE AutoShop

GO

CREATE TRIGGER trigger_update_DataOfSell_Buyers ON Buyers

FOR UPDATE

AS

BEGIN

IF (SELECT DataOfSell FROM INSERTED)<>(SELECT DateOfOrder FROM Orders, INSERTED WHERE Orders.FKBuyer = inserted.[IDBuyer])

BEGIN

RAISERROR(N'Error', 10, 1);

ROLLBACK TRANSACTION

END

END

11. Триггер на вставку данных

USE AutoShop

GO

CREATE TRIGGER Trigger_insert_country_city

ON [dbo].[Addresses] FOR INSERT

AS

IF @@ROWCOUNT=1

BEGIN

IF NOT EXISTS(SELECT *

FROM inserted

WHERE inserted.FKCountry=ALL(SELECT

City.FKCountry

FROM City

WHERE City.IDCity=

inserted.FKCity))

BEGIN

ROLLBACK TRAN

PRINT

'Отмена операции. Город не соответствует стране'

END

END