FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

create table Question (
    Id bigint primary key identity (1,1),
    DisplayOrder int unique not null,
    [Name] nvarchar(50) unique not null
)

create table QuestionPart (
    Id bigint primary key identity (1,1),
    QuestionId bigint not null
        constraint FK_QuestionPartVisible_QuestionId_Id foreign key (QuestionId) references Question (Id),
    DisplayOrder int unique not null,    
    VisibleText nvarchar(max) not null,
    BlankedText nvarchar(100) not null,
    IsDropDown bit not null
)

create table QuestionPartBlankOption (
    Id bigint primary key identity (1,1),
    QuestionPartId bigint not null
        constraint FK_QuestionPartBlankOption_QuestionPartId_Id foreign key (QuestionPartId) references QuestionPart (Id),
    DisplayOrder int unique not null,
    OptionText nvarchar(100),
    IsCorrectOption bit not null
)

insert into Question (
    DisplayOrder,
    [Name]
)
select 
    0 as DisplayOrder,
    'Mitochondrial energy storage' as [Name]

insert into QuestionPart (
    QuestionId,
    DisplayOrder,
    VisibleText,
    BlankedText,
    IsDropDown
)
select 
    (select Id from Question where [Name] = 'Mitochondrial energy storage') as QuestionId,
    x.*
from
(
    select
        0 as DisplayOrder,
        'Chemical energy produced by the' as VisibleText,
        'mitochondria' as BlankedText,
        1 as IsDropDown

union

select 
        1 as DisplayOrder,
        'is stored in a small molecule called' as VisibleText,
        'adenosine triphosphate' as BlankedText,
        0 as IsDropDown
) x

insert into QuestionPartBlankOption (
    QuestionPartId,
    DisplayOrder,
    OptionText,
    IsCorrectOption
)
select
    (
        select qp.Id 
        from QuestionPart qp
            inner join Question q on qp.QuestionId = q.Id
        where q.[Name] = 'Mitochondrial energy storage'
            and qp.VisibleText = 'Chemical energy produced by the'
    ) as QuestionPartId,
    x.*
from
(
    select 
        0 as DisplayOrder,
        'liver' as OptionText,
        0 as IsCorrectOption

union

select 
        1 as DisplayOrder,
        'mitochondria' as OptionText,
        1 as IsCorrectOption
) x

select 
    q.[Name] as QuestionName, 
    qp.VisibleText,
    qp.BlankedText,
    qpb.OptionText,
    qpb.IsCorrectOption
from Question q
    inner join QuestionPart qp on q.Id = qp.QuestionId
    left join QuestionPartBlankOption qpb on qp.Id = qpb.QuestionPartId and qp.IsDropDown = 1
order by
    q.DisplayOrder,
    qp.DisplayOrder,
    qpb.DisplayOrder

QuestionName	VisibleText	BlankedText	OptionText	IsCorrectOption
Mitochondrial energy storage	Chemical energy produced by the	mitochondria	liver	0
Mitochondrial energy storage	Chemical energy produced by the	mitochondria	mitochondria	1
Mitochondrial energy storage	is stored in a small molecule called	adenosine triphosphate	NULL	NULL

(1 rows affected)

(2 rows affected)

(2 rows affected)

(3 rows affected)