Создание запросов на создание и заполнение таблиц

Часть 1: СОЗДАНИЕ ЗАПРОСОВ НА СОЗДАНИЕ ТАБЛИЦ

Таблица команд.

Запрос для создания таблицы:

USE [db_it052dadaev]

GO

/****** Object:  Table [dbo].[commands]    Script Date: 02/29/2008 11:05:24 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[commands](

      [id_command] [int] IDENTITY(1,1) NOT NULL,

      [command] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL,

 CONSTRAINT [PK_commands] PRIMARY KEY CLUSTERED

(

      [id_command] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Таблица места проведения чемпионатов.

Запрос для создания таблицы:

USE [db_it052dadaev]

GO

/****** Object:  Table [dbo].[location]    Script Date: 02/29/2008 11:14:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[location](

      [id] [int] NOT NULL,

      [locate] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL,

 CONSTRAINT [PK_location] PRIMARY KEY CLUSTERED

(

      [id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Таблица участников комманд.

Запрос для создания таблицы:

USE [db_it052dadaev]

GO

/****** Object:  Table [dbo].[polottone]    Script Date: 02/29/2008 11:17:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[polottone](

      [id_pilote] [int] IDENTITY(1,1) NOT NULL,

      [pilotes] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL,

      [id_command] [int] NOT NULL,

 CONSTRAINT [PK_polottone] PRIMARY KEY CLUSTERED

(

      [id_pilote] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

USE [db_it052dadaev]

GO

ALTER TABLE [dbo].[polottone]  WITH CHECK ADD  CONSTRAINT [FK_polottone_commands] FOREIGN KEY([id_command])

REFERENCES [dbo].[commands] ([id_command])

Таблица сетки чемпионата ( место проведения, результат гонки, имя пилота, команда).

Запрос для создания таблицы:

USE [db_it052dadaev]

GO

/****** Object:  Table [dbo].[competition]    Script Date: 02/29/2008 11:17:41 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[competition](

      [id_time_] [int] IDENTITY(1,1) NOT NULL,

      [time] [datetime] NULL,

      [id] [int] NOT NULL

) ON [PRIMARY]

GO

USE [db_it052dadaev]

GO

ALTER TABLE [dbo].[competition]  WITH CHECK ADD  CONSTRAINT [FK_competition_location] FOREIGN KEY([id])

REFERENCES [dbo].[location] ([id])

Таблица результатов гонки.

Запрос для создания таблицы:

USE [db_it052dadaev]

GO

/****** Object:  Table [dbo].[result]    Script Date: 02/29/2008 11:23:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[result](

      [id_result] [int] IDENTITY(1,1) NOT NULL,

      [results] [int] NOT NULL,

      [id_pilote] [int] NOT NULL,

      [id] [int] NOT NULL

) ON [PRIMARY]

GO

USE [db_it052dadaev]

GO

ALTER TABLE [dbo].[result]  WITH CHECK ADD  CONSTRAINT [FK_result_location] FOREIGN KEY([id])

REFERENCES [dbo].[location] ([id])

GO

ALTER TABLE [dbo].[result]    WITH CHECK ADD  CONSTRAINT [FK_result_polottone] FOREIGN KEY([id_pilote])

REFERENCES [dbo].[polottone] ([id_pilote])

Часть 2: СОЗДАНИЕ ЗАПРОСОВ НА ЗАПОЛНЕНИЕ ТАБЛИЦ

Заполнение таблицы команд:

INSERT INTO [db_it052dadaev].[dbo].[commands]

           ([command])

     VALUES

           ('FERRARY')

INSERT INTO [db_it052dadaev].[dbo].[commands]

           ([command])

     VALUES

           ('RENAULT')

INSERT INTO [db_it052dadaev].[dbo].[commands]

           ([command])

     VALUES

           ('TOYOTA')

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

Запрос на заполнение таблицы пилотов:

INSERT INTO [db_it052dadaev].[dbo].[polottone]

           ([pilotes]

           ,[id_command])

     VALUES

           ('Mikka Hekkinen'

           ,'1')

INSERT INTO [db_it052dadaev].[dbo].[polottone]

           ([pilotes]

           ,[id_command])

     VALUES

           ('Rubens Barrikello'

           ,'1')

INSERT INTO [db_it052dadaev].[dbo].[polottone]

           ([pilotes]

           ,[id_command])

     VALUES

           ('Michail Schumaher'

           ,'2')

INSERT INTO [db_it052dadaev].[dbo].[polottone]

           ([pilotes]

           ,[id_command])

     VALUES

           ('Fernando Alonso'

           ,'2')

INSERT INTO [db_it052dadaev].[dbo].[polottone]

           ([pilotes]

           ,[id_command])

     VALUES

           ('Kimi Raikonen'

           ,'3')

INSERT INTO [db_it052dadaev].[dbo].[polottone]

           ([pilotes]

           ,[id_command])

     VALUES

           ('Ralf Schumaher'

           ,'3')

Результат выполнения запроса:

Запрос на заполнение таблицы места проведения гонок:

INSERT INTO [db_it052dadaev].[dbo].[location]

           ([id]

           ,[locate])

     VALUES

           ('1'

           ,'Автралия')

INSERT INTO [db_it052dadaev].[dbo].[location]

           ([id]

           ,[locate])

     VALUES

           ('2'

           ,'Германия')

INSERT INTO [db_it052dadaev].[dbo].[location]

           ([id]

           ,[locate])

     VALUES

           ('3'

           ,'Монако')

Результат выполнения запроса:

Запрос на заполнение таблицы результатов проведения гонки:

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('10'

           ,'3'

           ,'1')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('8'

           ,'1'

           ,'1')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('6'

           ,'2'

           ,'1')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('4'

           ,'6'

           ,'1')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('3'

           ,'5'

           ,'1')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('2'

           ,'4'

           ,'1')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('10'

           ,'5'

           ,'2')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('8'

           ,'1'

           ,'2')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('6'

           ,'3'

           ,'2')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('4'

           ,'2'

           ,'2')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('3'

           ,'6'

           ,'2')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('2'

           ,'4'

           ,'2')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id_])

     VALUES

           ('10'

           ,'6'

           ,'3')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('8'

           ,'1'

           ,'3')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('6'

           ,'3'

           ,'3')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('4'

           ,'5'

           ,'3')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('3'

           ,'4'

           ,'3')

INSERT INTO [db_it052dadaev].[dbo].[result]

           ([results]

           ,[id_pilote]

           ,[id])

     VALUES

           ('2'

           ,'2'

           ,'3')

Результат выполнения запроса:

Запрос на заполнение таблицы времени проведения гонки:

INSERT INTO [db_it052dadaev].[dbo].[competition]

           ([time]

           ,[id])

     VALUES

           ('16.03.2008 14:20:00'

           ,'1')

INSERT INTO [db_it052dadaev].[dbo].[competition]

           ([time]

           ,[id])

     VALUES

           ('24.04.2008 14:25:00'

           ,'2')

INSERT INTO [db_it052dadaev].[dbo].[competition]

           ([time]

           ,[id])

     VALUES

           ('13.05.2008 13:10:00'

           ,'3')

Результат выполнения запроса: