How should you modify the INSERT statement?

###BeginCaseStudy###
Case Study: 1
Scenario 1
Application Information
Your company receives invoices in XML format from customers. Currently, the invoices are
stored as files and processed by a desktop application. The application has several
performance and security issues. The application is being migrated to a SQL Server-based
solution. A schema named InvoiceSchema has been created for the invoices xml.
The data in the invoices is sometimes incomplete. The incomplete data must be stored and
processed as-is. Users cannot filter the data provided through views.
You are designing a SQL Server database named DB1 that will be used to receive, process,
and securely store the invoice data. A third-party Microsoft .NET Framework component will
be purchased to perform tax calculations. The third-party tax component will be provided as a
DLL file named Treytax.dll and a source code file named Amortize.cs. The component will
expose a class named TreyResearch and a method named Amortize(). The files are located in
c:\temp\.
The following graphic shows the planned tables:

You have a sequence named Accounting.InvoiceID_Seq.
You plan to create two certificates named CERT1 and CERT2. You will create CERT1 in
master. You will create CERT2 in DB1.
You have a legacy application that requires the ability to generate dynamic T-SQL statements
against DB1. A sample of the queries generated by the legacy application appears in
Legacy.sql.
Application Requirements
The planned database has the following requirements:
• All stored procedures must be signed.
• The original XML invoices must be stored in the database.

• An XML schema must be used to validate the invoice data.
• Dynamic T-SQL statements must be converted to stored procedures.
• Access to the .NET Framework tax components must be available to T-SQL objects.
• Columns must be defined by using data types that minimize the amount of space used
by each table.
• Invoices stored in the InvoiceStatus table must refer to an invoice by the same
identifier used by the Invoice table.
• To protect against the theft of backup disks, invoice data must be protected by using
the highest level of encryption.
• The solution must provide a table-valued function that provides users with the ability
to filter invoices by customer.
• Indexes must be optimized periodically based on their fragmentation by using the
minimum amount of administrative effort.
Usp_InsertInvoices.sql

Invoices.xml
All customer IDs are 11 digits. The first three digits of a customer ID represent the customer’s
country. The remaining eight digits are the customer’s account number.
The following is a sample of a customer invoice in XML format:

InvoicesByCustomer.sql

Legacy.sql

CountryFromID.sql

IndexManagement.sql

###EndCaseStudy###

You attempt to process an invoice by using usp_InsertInvoice.sql and you receive the
following error message: “Msg 515, Level 16, State 2, Procedure usp_InsertInvoice, Line 10
Cannot insert the value NULL into column ‘InvoiceDate’, table ‘DB1.Accounting.Invoices’;
column does not allow nulls. INSERT fails.”
You need to modify usp_InsertInvoice.sql to resolve the error.
How should you modify the INSERT statement?

###BeginCaseStudy###
Case Study: 1
Scenario 1
Application Information
Your company receives invoices in XML format from customers. Currently, the invoices are
stored as files and processed by a desktop application. The application has several
performance and security issues. The application is being migrated to a SQL Server-based
solution. A schema named InvoiceSchema has been created for the invoices xml.
The data in the invoices is sometimes incomplete. The incomplete data must be stored and
processed as-is. Users cannot filter the data provided through views.
You are designing a SQL Server database named DB1 that will be used to receive, process,
and securely store the invoice data. A third-party Microsoft .NET Framework component will
be purchased to perform tax calculations. The third-party tax component will be provided as a
DLL file named Treytax.dll and a source code file named Amortize.cs. The component will
expose a class named TreyResearch and a method named Amortize(). The files are located in
c:\temp\.
The following graphic shows the planned tables:

You have a sequence named Accounting.InvoiceID_Seq.
You plan to create two certificates named CERT1 and CERT2. You will create CERT1 in
master. You will create CERT2 in DB1.
You have a legacy application that requires the ability to generate dynamic T-SQL statements
against DB1. A sample of the queries generated by the legacy application appears in
Legacy.sql.
Application Requirements
The planned database has the following requirements:
• All stored procedures must be signed.
• The original XML invoices must be stored in the database.

• An XML schema must be used to validate the invoice data.
• Dynamic T-SQL statements must be converted to stored procedures.
• Access to the .NET Framework tax components must be available to T-SQL objects.
• Columns must be defined by using data types that minimize the amount of space used
by each table.
• Invoices stored in the InvoiceStatus table must refer to an invoice by the same
identifier used by the Invoice table.
• To protect against the theft of backup disks, invoice data must be protected by using
the highest level of encryption.
• The solution must provide a table-valued function that provides users with the ability
to filter invoices by customer.
• Indexes must be optimized periodically based on their fragmentation by using the
minimum amount of administrative effort.
Usp_InsertInvoices.sql

Invoices.xml
All customer IDs are 11 digits. The first three digits of a customer ID represent the customer’s
country. The remaining eight digits are the customer’s account number.
The following is a sample of a customer invoice in XML format:

InvoicesByCustomer.sql

Legacy.sql

CountryFromID.sql

IndexManagement.sql

###EndCaseStudy###

You attempt to process an invoice by using usp_InsertInvoice.sql and you receive the
following error message: “Msg 515, Level 16, State 2, Procedure usp_InsertInvoice, Line 10
Cannot insert the value NULL into column ‘InvoiceDate’, table ‘DB1.Accounting.Invoices’;
column does not allow nulls. INSERT fails.”
You need to modify usp_InsertInvoice.sql to resolve the error.
How should you modify the INSERT statement?

A.
InvoiceDatevarchar(l00) ‘InvoiceDate’,

B.
InvoiceDatevarchar(100) ‘Customer/InvoiceDate’, ‘

C.
InvoiceDate date ‘@InvoiceDate’,

D.
InvoiceDate date ‘Customer/@InvoiceDate’,



Leave a Reply 5

Your email address will not be published. Required fields are marked *


Jai

Jai

I Think D, Invoice date is a Date Field.

zzz

zzz

@InvoiceDate is an attribute of Invoice element, but not Customer.
I would say C is correct.

Frank

Frank

C
declare @XML AS XML;
declare @XMLHandle int;
set @XML=N’

125
1666
‘;
exec sp_xml_preparedocument @XMLHandle OUTPUT, @XML;

select * from OPENXML(@XMLHandle, ‘/Invoice’,2)
WITH(
InvoiceDate date ‘@InvoiceDate’,
Total decimal(8,2) ‘Total’,
PONumber int ‘PONumber’);

insert into Invoices(
InvoiceID,
InvoiceXML,
InvoiceDate,
Total,
PONumber)
select 1, @XML,
* from OPENXML(@XMLHandle, ‘/Invoice’,2)
WITH(
InvoiceDate date ‘@InvoiceDate’,
Total decimal(8,2) ‘Total’,
PONumber int ‘PONumber’);
select * from Invoices;
exec sp_xml_removedocument @XMLHandle

ryahan

ryahan

what a crappy question .. talking about null etc.. where the issue is just to do with the attribute not being specified correctly .. it’s C