/****** Object:  UserDefinedFunction [dbo].[task6]    Script Date: 14.12.2019 11:01:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[task6]() RETURNS REAL
BEGIN
    DECLARE @Средняя_стоимость REAL
    select @Средняя_стоимость=convert(REAL,AVG(coast)) FROM plane_models
    RETURN (@Средняя_стоимость)
END;
GO
/****** Object:  Table [dbo].[plane_models]    Script Date: 14.12.2019 11:01:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[plane_models](
	[id_Model] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) NOT NULL,
	[coast] [money] NULL,
	[manufacturer] [int] NULL,
 CONSTRAINT [PK__plane_mo__F406B314C8020036] PRIMARY KEY CLUSTERED
(
	[id_Model] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  UserDefinedFunction [dbo].[task7]    Script Date: 14.12.2019 11:01:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[task7]() RETURNS TABLE AS
        RETURN(select top(5) name as Самолет, coast from plane_models order by name asc)
GO
/****** Object:  Table [dbo].[aircompany]    Script Date: 14.12.2019 11:01:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aircompany](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[city]    Script Date: 14.12.2019 11:01:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[city](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[city_aircompany]    Script Date: 14.12.2019 11:01:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[city_aircompany](
	[id_City] [int] NOT NULL,
	[id_aircompany] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
	[id_City] ASC,
	[id_aircompany] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[manufacturer]    Script Date: 14.12.2019 11:01:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[manufacturer](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[plane_park]    Script Date: 14.12.2019 11:01:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[plane_park](
	[id_Plane] [int] NOT NULL,
	[model] [int] NULL,
	[aircompany] [int] NULL,
PRIMARY KEY CLUSTERED
(
	[id_Plane] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Авиакомпании со средним числом производителей самолетов равный 1]    Script Date: 14.12.2019 11:01:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Авиакомпании со средним числом производителей самолетов равный 1](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Название] [varchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Авиакомпании со средним числом производителей самолетов равный 5]    Script Date: 14.12.2019 11:01:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Авиакомпании со средним числом производителей самолетов равный 5](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Название] [varchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Смена производителя]    Script Date: 14.12.2019 11:01:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Смена производителя](
	[название] [varchar](50) NULL,
	[дата изменения название] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[aircompany] ON
GO
INSERT [dbo].[aircompany] ([id], [name]) VALUES (1, N'company1')
GO
INSERT [dbo].[aircompany] ([id], [name]) VALUES (2, N'company2')
GO
INSERT [dbo].[aircompany] ([id], [name]) VALUES (3, N'company3')
GO
INSERT [dbo].[aircompany] ([id], [name]) VALUES (4, N'company4')
GO
SET IDENTITY_INSERT [dbo].[aircompany] OFF
GO
SET IDENTITY_INSERT [dbo].[city] ON
GO
INSERT [dbo].[city] ([id], [name]) VALUES (1, N'city1')
GO
INSERT [dbo].[city] ([id], [name]) VALUES (2, N'city2')
GO
INSERT [dbo].[city] ([id], [name]) VALUES (3, N'city3')
GO
INSERT [dbo].[city] ([id], [name]) VALUES (4, N'city4')
GO
INSERT [dbo].[city] ([id], [name]) VALUES (5, N'Стамбул')
GO
SET IDENTITY_INSERT [dbo].[city] OFF
GO
INSERT [dbo].[city_aircompany] ([id_City], [id_aircompany]) VALUES (1, 1)
GO
INSERT [dbo].[city_aircompany] ([id_City], [id_aircompany]) VALUES (2, 2)
GO
INSERT [dbo].[city_aircompany] ([id_City], [id_aircompany]) VALUES (3, 3)
GO
INSERT [dbo].[city_aircompany] ([id_City], [id_aircompany]) VALUES (4, 4)
GO
INSERT [dbo].[city_aircompany] ([id_City], [id_aircompany]) VALUES (5, 1)
GO
SET IDENTITY_INSERT [dbo].[manufacturer] ON
GO
INSERT [dbo].[manufacturer] ([id], [name]) VALUES (5, N'man5')
GO
INSERT [dbo].[manufacturer] ([id], [name]) VALUES (6, N'changer1')
GO
SET IDENTITY_INSERT [dbo].[manufacturer] OFF
GO
SET IDENTITY_INSERT [dbo].[plane_models] ON
GO
INSERT [dbo].[plane_models] ([id_Model], [name], [coast], [manufacturer]) VALUES (13, N'plane_russia', 3000.0000, 6)
GO
INSERT [dbo].[plane_models] ([id_Model], [name], [coast], [manufacturer]) VALUES (14, N'testytyyt', 20000.0000, 6)
GO
INSERT [dbo].[plane_models] ([id_Model], [name], [coast], [manufacturer]) VALUES (18, N't5', 20000.0000, 6)
GO
INSERT [dbo].[plane_models] ([id_Model], [name], [coast], [manufacturer]) VALUES (19, N't6', 20000.0000, 6)
GO
INSERT [dbo].[plane_models] ([id_Model], [name], [coast], [manufacturer]) VALUES (20, N't7', 20000.0000, 6)
GO
INSERT [dbo].[plane_models] ([id_Model], [name], [coast], [manufacturer]) VALUES (21, N't10', 20000.0000, 5)
GO
SET IDENTITY_INSERT [dbo].[plane_models] OFF
GO
SET IDENTITY_INSERT [dbo].[Авиакомпании со средним числом производителей самолетов равный 1] ON
GO
INSERT [dbo].[Авиакомпании со средним числом производителей самолетов равный 1] ([id], [Название]) VALUES (1, N'company1')
GO
INSERT [dbo].[Авиакомпании со средним числом производителей самолетов равный 1] ([id], [Название]) VALUES (2, N'company2')
GO
INSERT [dbo].[Авиакомпании со средним числом производителей самолетов равный 1] ([id], [Название]) VALUES (3, N'company3')
GO
INSERT [dbo].[Авиакомпании со средним числом производителей самолетов равный 1] ([id], [Название]) VALUES (4, N'company4')
GO
SET IDENTITY_INSERT [dbo].[Авиакомпании со средним числом производителей самолетов равный 1] OFF
GO
INSERT [dbo].[Смена производителя] ([название], [дата изменения название]) VALUES (N'man6', CAST(N'2019-12-07T03:10:52.227' AS DateTime))
GO
INSERT [dbo].[Смена производителя] ([название], [дата изменения название]) VALUES (N'changer1', CAST(N'2019-12-07T14:55:35.460' AS DateTime))
GO
INSERT [dbo].[Смена производителя] ([название], [дата изменения название]) VALUES (N'test1', CAST(N'2019-12-07T14:55:52.920' AS DateTime))
GO
INSERT [dbo].[Смена производителя] ([название], [дата изменения название]) VALUES (N'changer1', CAST(N'2019-12-07T15:05:40.597' AS DateTime))
GO
INSERT [dbo].[Смена производителя] ([название], [дата изменения название]) VALUES (N'changer1', CAST(N'2019-12-07T15:06:10.927' AS DateTime))
GO
INSERT [dbo].[Смена производителя] ([название], [дата изменения название]) VALUES (N'test1', CAST(N'2019-12-07T16:28:34.377' AS DateTime))
GO
ALTER TABLE [dbo].[Смена производителя] ADD  DEFAULT (getdate()) FOR [дата изменения название]
GO
ALTER TABLE [dbo].[city_aircompany]  WITH CHECK ADD FOREIGN KEY([id_aircompany])
REFERENCES [dbo].[aircompany] ([id])
GO
ALTER TABLE [dbo].[city_aircompany]  WITH CHECK ADD FOREIGN KEY([id_City])
REFERENCES [dbo].[city] ([id])
GO
ALTER TABLE [dbo].[plane_models]  WITH CHECK ADD  CONSTRAINT [FK__plane_mod__manuf__44FF419A] FOREIGN KEY([manufacturer])
REFERENCES [dbo].[manufacturer] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[plane_models] CHECK CONSTRAINT [FK__plane_mod__manuf__44FF419A]
GO
ALTER TABLE [dbo].[plane_park]  WITH CHECK ADD FOREIGN KEY([aircompany])
REFERENCES [dbo].[aircompany] ([id])
GO
ALTER TABLE [dbo].[plane_park]  WITH CHECK ADD  CONSTRAINT [FK__plane_par__model__412EB0B6] FOREIGN KEY([model])
REFERENCES [dbo].[plane_models] ([id_Model])
GO
ALTER TABLE [dbo].[plane_park] CHECK CONSTRAINT [FK__plane_par__model__412EB0B6]
GO
/****** Object:  StoredProcedure [dbo].[task1]    Script Date: 14.12.2019 11:01:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*вставка фильма, если отсутствует студия, то она добавляется  есть работает*/
CREATE PROCEDURE [dbo].[task1](@name varchar(50), @manufacturer varchar(50))
AS
BEGIN
    DECLARE @man_id INT;
	select @man_id=id from manufacturer where name=@manufacturer
    if EXISTS(select id from manufacturer where name=@manufacturer)
        insert INTO plane_models(name, manufacturer) VALUES (@name, @man_id)
    ELSE
        BEGIN
            insert into manufacturer (name) VALUES (@manufacturer)
            insert INTO plane_models(name, manufacturer) VALUES (@name, @@IDENTITY)
        END
END;
GO
/****** Object:  StoredProcedure [dbo].[task2]    Script Date: 14.12.2019 11:01:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[task2](@name varchar(50), @manufacturer varchar(50))
AS
BEGIN
    DECLARE @man_id INT;
	select @man_id=id from manufacturer where name=@manufacturer

    delete FROM plane_models where name=@name and manufacturer=@man_id

    if not EXISTS(select * from plane_models where manufacturer=@man_id)
        delete FROM manufacturer where id=@man_id
END;
GO
/****** Object:  StoredProcedure [dbo].[task3]    Script Date: 14.12.2019 11:01:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[task3] (@name varchar(50)) AS
BEGIN
    DECLARE @man_id INT
    /*обязательно нужен иначе не понятно, что означает man_id*/
    select @man_id=id from manufacturer where name=@name
    IF EXISTS(select * from plane_models where manufacturer=@man_id)
    BEGIN
        delete from plane_models where manufacturer=@man_id
        DELETE FROM manufacturer where name=@name
    END
    ELSE
        DELETE FROM manufacturer where name=@name
END;
GO
/****** Object:  StoredProcedure [dbo].[task4]    Script Date: 14.12.2019 11:01:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*4 ХП которая использует агрегатную функцию*/
CREATE PROCEDURE [dbo].[task4] (@name varchar(50))AS
BEGIN
    DECLARE @man_id INT
    select @man_id=id from manufacturer where name=@name
    SELECT count(name) as [Кол-во моделей у каждого производителя], manufacturer FROM plane_models WHERE manufacturer=@man_id group by manufacturer
END
GO