add batch
remove batch
split batch
comment selection
show hidden batches
hide batch
highlight batch
db<>fiddle
Db2
Firebird
MariaDB
MySQL
Node.js
Oracle
Postgres
SQLite
SQL Server
TimescaleDB
YugabyteDB
Developer-C 11.1
3.0
4.0
10.2
10.3
10.4
10.5
10.6
10.7
10.8
10.9
5.5
5.6
5.7
8.0
18
11g Release 2
18c
21c
23c
8.4
9.3
9.4
9.5
9.6
10
11
12
13
14
15
16
3.8
3.16
3.27
3.39
2012
2014
2016
2017
2017 (Linux)
2019
2019 (Linux)
2022
2.11
2.6
2.8
2.18
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
Sakila
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
no sample DB
no sample DB
run
markdown
donate
feedback
about
By using db<>fiddle, you agree to license everything you submit by
Creative Commons CC0
.
-- A record of the overall test of a widget CREATE TABLE [TestRecords] ( [TestRecord_ID] BIGINT NOT NULL, -- ... CONSTRAINT PK_TestRecords PRIMARY KEY ([TestRecord_ID]) ); -- A record of an individual subtest within the overall test -- Any given subtest may be repeated N times CREATE TABLE [SubtestRuns] ( [TestRecord_ID] BIGINT NOT NULL, [TestType] VARCHAR(50) NOT NULL, -- [RunNumber] is unique within one overall test, but not across all time [RunNumber] INT NOT NULL, -- Fields common to all subtests -- ... CONSTRAINT PK_SubtestRuns PRIMARY KEY ([TestRecord_ID], [TestType], [RunNumber]), CONSTRAINT FK_SubtestRuns_TestRecord_ID FOREIGN KEY ([TestRecord_ID]) REFERENCES [TestRecords]([TestRecord_ID]) ); -- This is the first of many subtest types CREATE TABLE [Subtest_1] ( [TestRecord_ID] BIGINT NOT NULL, [RunNumber] INT NOT NULL, [TestType] VARCHAR(50) NOT NULL CONSTRAINT DF_Subtest_1_TestType DEFAULT 'Subtest_1', -- Fields specific to this subtest type -- ... CONSTRAINT PK_Subtest_1 PRIMARY KEY ([TestRecord_ID], [RunNumber]), CONSTRAINT CHK_Subtest_1_TestType_EQ_Subtest_1 CHECK ([TestType] = 'Subtest_1'), CONSTRAINT FK_Subtest_1_TestRecord_ID_RunNumber_TestType FOREIGN KEY ([TestRecord_ID], [TestType], [RunNumber]) REFERENCES [SubtestRuns]([TestRecord_ID], [TestType], [RunNumber]) ); -- This is the second of many subtest types CREATE TABLE [Subtest_2] ( [TestRecord_ID] BIGINT NOT NULL, [RunNumber] INT NOT NULL, [TestType] VARCHAR(50) NOT NULL CONSTRAINT DF_Subtest_2_TestType DEFAULT 'Subtest_2', -- Fields specific to this subtest type -- ... CONSTRAINT PK_Subtest_2 PRIMARY KEY ([TestRecord_ID], [RunNumber]), CONSTRAINT CHK_Subtest_2_TestType_EQ_Subtest_2 CHECK ([TestType] = 'Subtest_2'), CONSTRAINT FK_Subtest_2_TestRecord_ID_RunNumber_TestType FOREIGN KEY ([TestRecord_ID], [TestType], [RunNumber]) REFERENCES [SubtestRuns]([TestRecord_ID], [TestType], [RunNumber]) );
select * from [Subtest_2];
TestRecord_ID
RunNumber
TestType
CREATE VIEW [TestRecords_View] AS SELECT DISTINCT [T0].[TestRecord_ID], CASE WHEN [T1].[TestRecord_ID] IS NOT NULL THEN 1 ELSE 0 END AS col1, CASE WHEN [T2].[TestRecord_ID] IS NOT NULL THEN 1 ELSE 0 END AS col2 FROM [TestRecords] AS [T0] LEFT OUTER JOIN [Subtest_1] AS [T1] ON [T0].[TestRecord_ID] = [T1].[TestRecord_ID] LEFT OUTER JOIN [Subtest_2] AS [T2] ON [T0].[TestRecord_ID] = [T2].[TestRecord_ID];