Practice Exam

Complete The T-SQL Statement

You work for MDFT Pro, a well-known training agency that manages student enrollment applications through an Azure Synapse Analytics platform. Mark, the Database Architect, has asked you to build a database in an Azure Synapse Analytics serverless SQL pool to analyze historical enrollment data.

The data is stored in Parquet files in an Azure Data Lake Storage Gen2 container. Each record contains student application information with fields for ID, house number, street address, and applicant names (up to two applicants per record). Mark needs you to create an external table that extracts only the address-related fields for geographical analysis of student demographics.

The records are structured as shown in the following sample:

{
	"id": 123,
	"address_housenumber": "10c",
	"address_line1": "Abraham Lane",
	"applicant1_name": "Mark Farragher",
	"applicant2_name": "Claire Smith"
}

How should you complete the Transact-SQL statement?

CREATE EXTERNAL TABLE applications
WITH (
	LOCATION = 'applications/'
	DATA SOURCE = applications_ds,
	FILE FORMAT = applications_file_format
)
AS
SELECT
	id,
	[address_housenumber] as addressnumber,
	[address_line1] as addressline1
FROM _______________ (BULK
	'https://mdftpro.dfs.core.windows.net/applications/year=*/*.parquet',
	FORMAT = 'PARQUET'
) AS [r]
GO

Choose the correct answer from the options below.