SQLize Online / PHPize Online  /  SQLtest Online

/****** -------------------------------------------------- ******/ /****** Atelier sur les requetes SQL en utilisant le site web ******/ /****** sqlfiddle.com ******/ /****** -------------------------------------------------- ******/ /****** Création Olivier NAVARRE - Version 1.1 - 10/2021 ******/ /****** -------------------------------------------------- ******/ /****** Création du schéma de la base de données ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Actor]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Actor]( [ID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [nvarchar](max) NULL, [LastName] [nvarchar](max) NULL, [BirthDate] [datetime2](7) NOT NULL, CONSTRAINT [PK_Actor] 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] TEXTIMAGE_ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Character]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Character]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](max) NULL, [ActorID] [int] NULL, [MovieViewModelID] [int] NULL, CONSTRAINT [PK_Character] 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] TEXTIMAGE_ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Movie]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Movie]( [ID] [int] IDENTITY(1,1) NOT NULL, [Title] [nvarchar](max) NULL, [ReleaseDate] [int] NOT NULL, [Runtime] [time](7) NOT NULL, [Resume] [nvarchar](max) NULL, CONSTRAINT [PK_Movie] 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] TEXTIMAGE_ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Producer]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Producer]( [ID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [nvarchar](max) NULL, [LastName] [nvarchar](max) NULL, [MovieViewModelID] [int] NULL, CONSTRAINT [PK_Producer] 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] TEXTIMAGE_ON [PRIMARY] END GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Character_Actor_ActorID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Character]')) ALTER TABLE [dbo].[Character] WITH CHECK ADD CONSTRAINT [FK_Character_Actor_ActorID] FOREIGN KEY([ActorID]) REFERENCES [dbo].[Actor] ([ID]) GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Character_Actor_ActorID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Character]')) ALTER TABLE [dbo].[Character] CHECK CONSTRAINT [FK_Character_Actor_ActorID] GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Character_Movie_MovieViewModelID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Character]')) ALTER TABLE [dbo].[Character] WITH CHECK ADD CONSTRAINT [FK_Character_Movie_MovieViewModelID] FOREIGN KEY([MovieViewModelID]) REFERENCES [dbo].[Movie] ([ID]) GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Character_Movie_MovieViewModelID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Character]')) ALTER TABLE [dbo].[Character] CHECK CONSTRAINT [FK_Character_Movie_MovieViewModelID] GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Producer_Movie_MovieViewModelID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Producer]')) ALTER TABLE [dbo].[Producer] WITH CHECK ADD CONSTRAINT [FK_Producer_Movie_MovieViewModelID] FOREIGN KEY([MovieViewModelID]) REFERENCES [dbo].[Movie] ([ID]) GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Producer_Movie_MovieViewModelID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Producer]')) ALTER TABLE [dbo].[Producer] CHECK CONSTRAINT [FK_Producer_Movie_MovieViewModelID] GO /* Insertion des données d'exemple */ INSERT [dbo].[Actor] ([FirstName], [LastName], [BirthDate]) VALUES (N'Keanu', N'Reeves', CAST(N'1964-09-02T00:00:00.0000000' AS DateTime2)) INSERT [dbo].[Actor] ([FirstName], [LastName], [BirthDate]) VALUES (N'Laurence', N'Fishburne', CAST(N'1961-07-30T00:00:00.0000000' AS DateTime2)) INSERT [dbo].[Actor] ([FirstName], [LastName], [BirthDate]) VALUES (N'Carrie-Anne', N'Moss', CAST(N'1967-08-21T00:00:00.0000000' AS DateTime2)) INSERT [dbo].[Actor] ([FirstName], [LastName], [BirthDate]) VALUES (N'Harrison', N'Ford', CAST(N'1942-07-13T00:00:00.0000000' AS DateTime2)) INSERT [dbo].[Actor] ([FirstName], [LastName], [BirthDate]) VALUES (N'Mark', N'Hamill', CAST(N'1951-09-25T00:00:00.0000000' AS DateTime2)) INSERT [dbo].[Actor] ([FirstName], [LastName], [BirthDate]) VALUES (N'Carrie', N'Fisher', CAST(N'1956-10-21T00:00:00.0000000' AS DateTime2)) INSERT [dbo].[Actor] ([FirstName], [LastName], [BirthDate]) VALUES (N'Robert', N'Downey Jr.', CAST(N'1965-04-04T00:00:00.0000000' AS DateTime2)) INSERT [dbo].[Actor] ([FirstName], [LastName], [BirthDate]) VALUES (N'Chris', N'Evans', CAST(N'1981-07-13T00:00:00.0000000' AS DateTime2)) INSERT [dbo].[Actor] ([FirstName], [LastName], [BirthDate]) VALUES (N'Mark', N'Ruffalo', CAST(N'1967-10-22T00:00:00.0000000' AS DateTime2)) INSERT [dbo].[Actor] ([FirstName], [LastName], [BirthDate]) VALUES (N'Chris', N'Hemsworth', CAST(N'1983-08-11T00:00:00.0000000' AS DateTime2)) INSERT [dbo].[Actor] ([FirstName], [LastName], [BirthDate]) VALUES (N'Scarlett', N'Johansson', CAST(N'1984-11-22T00:00:00.0000000' AS DateTime2)) INSERT [dbo].[Movie] ([Title], [ReleaseDate], [Runtime], [Resume]) VALUES (N'Avengers', 2012, CAST(N'02:23:00' AS Time), N'Earth''s mightiest heroes must come together and learn to fight as a team if they are going to stop the mischievous Loki and his alien army from enslaving humanity.') INSERT [dbo].[Movie] ([Title], [ReleaseDate], [Runtime], [Resume]) VALUES (N'Avengers: Endgame', 2019, CAST(N'03:01:00' AS Time), N'After the devastating events of Avengers: Infinity War (2018), the universe is in ruins.') INSERT [dbo].[Movie] ([Title], [ReleaseDate], [Runtime], [Resume]) VALUES (N'Star Wars', 1977, CAST(N'02:01:00' AS Time), N'Luke Skywalker joins forces with a Jedi Knight, a cocky pilot, a Wookiee and two droids to save the galaxy from the Empire''s world-destroying battle station...') INSERT [dbo].[Movie] ([Title], [ReleaseDate], [Runtime], [Resume]) VALUES (N'Matrix', 1999, CAST(N'02:16:00' AS Time), N'When a beautiful stranger leads computer hacker Neo to a forbidding underworld, he discovers the shocking truth--the life he knows is the elaborate deception of an evil cyber-intelligence.') INSERT [dbo].[Character] ([Name], [ActorID], [MovieViewModelID]) VALUES (N'Neo', 1, 4) INSERT [dbo].[Character] ([Name], [ActorID], [MovieViewModelID]) VALUES (N'Morpheus', 2, 4) INSERT [dbo].[Character] ([Name], [ActorID], [MovieViewModelID]) VALUES (N'Trinity', 3, 4) INSERT [dbo].[Character] ([Name], [ActorID], [MovieViewModelID]) VALUES (N'Luke Skywalker', 5, 3) INSERT [dbo].[Character] ([Name], [ActorID], [MovieViewModelID]) VALUES (N'Han Solo', 4, 3) INSERT [dbo].[Character] ([Name], [ActorID], [MovieViewModelID]) VALUES (N'Princess Leia Organa', 6, 3) GO