Design and develop database solutions

DP-800 · 75 questions

• A city permits office is designing an Azure SQL table for parcel IDs, fee amounts, and inspection dates. Which column design best fits those municipal permit fields?
• A county public-health casework table has grown into millions of encounter rows and analysts need fast rollups by clinic and month. Which indexing approach should the team choose?
• A transit ridership fact table must support chronological trip loads and frequent lookups by routeid for ops queries. How should the indexes be designed?
• A courts clerk system sees extreme docket check-in write spikes every morning calendar open. Which specialized table feature should the team use for those bursty transactional inserts?
• A housing authority must retain lease status change history for audits without building custom audit tables. Which specialized table feature should they implement?
• A county open-data team publishes GIS attribute extracts in blob storage and wants analysts to query them from Azure SQL without importing every file into the primary database. What should they design?
• City finance must keep tamper-evident records of grant disbursements tied to federal pass-through funds. Which specialized table capability should they choose?
• Emergency management wants to model facility–asset–incident relationships for graph-style traversals inside SQL. Which design should they use?
• A 311 intake system stores free-form request payloads and must filter efficiently by category keys inside those documents. What should the team implement?
• Permit applications must guarantee each applicationid is unique and that every application references a valid parcelid in the parcels table. Which constraints should be applied?
• Library card numbers must never collide across city branches. Which constraint should enforce that rule on patroncardnumber?
• A water utility meter-reading table must reject negative readingvalue entries and values outside the sensor’s valid range. Which constraint should enforce that rule?
• Parks reservation rows should start as Pending until staff confirm the booking. Which constraint best supplies that initial status automatically?
• A county clerk cash window needs sequential receipt numbers for payments without relying on an identity column on every related table. Which object should they implement?
• A public-health lab results table must speed purge of aged rows by sampledate. Which design should the team apply?
• City payroll staging stores employee badge codes and must avoid silent truncation when codes are loaded from HR feeds. What data-type sizing practice should they follow?
• Transit ops needs near-real-time lookups of active vehicles and wants a lean nonclustered index that ignores inactive fleet rows. Which index design fits?
• Courts evidence metadata must remain cryptographically verifiable when chain-of-custody attributes change. Which feature should they select?
• Housing advocates must review waitlist standing as it existed on a past effective date for appeal hearings. Which capability should the database provide?
• Open-data restaurant inspection documents are stored as JSON and analysts filter heavily on a nested attribute path. Which indexing approach should the team design?
• A permit fees table must archive old fiscal years quickly during year-end close. Which partitioning pattern should they use?
• Licensing must detect conflict-of-interest paths between licensed contractors and permitted job sites. How should those relationships be modeled in SQL?
• A 311 dashboard must join service requests to neighborhoods for reporting without exposing raw PII columns to analysts. What programmability object should the team create?
• Public-health clinic triage scripts need a reusable calculation that maps height and weight inputs to BMI bands. Which programmability object should they implement?
• Transit planners need a reusable query that returns stops within a given radius of a depot for planning sessions. Which programmability object should they create?
• A city permits counter app must calculate fees and update application status in one server-side call. Which database object should the team implement?
• A county courts system must append a row to an audit trail table whenever a new docket event is inserted. Which programmability object should they use?
• A water utility exposes meter identity through an updatable view, but meter swaps must be validated before base tables change. Which object should enforce that rule?
• Housing caseworkers call a procedure that advances case status and must receive the new queuenumber back to the client app. Which design meets that need?
• Library analytics needs a view of branch circulation totals that will later support an indexed view, so underlying columns must stay locked to the view definition. Which view option should they use?
• An emergency-ops dispatch console needs a reusable query that returns open shelters with remaining capacity as a result set. Which object should they create?
• The county assessor wants every application to format parcel APN strings the same way without copying formatting logic into each app. Which object should centralize that formatting?
• A 311 escalation procedure must reject unknown category codes with a structured error before any escalation row is written. Where should that validation live?
• Parks reservations must not allow overlapping bookings for the same facilityid and time range. Which programmability approach enforces that rule on insert?
• At close of business, the clerk’s cash-drawer process must total tender types and write one daily reconciliation row. Which object should implement that workflow?
• Public health wants open-data consumers to see encounter counts by clinic without exposing row-level PII. Which object should they publish?
• Transit maintenance needs a reusable list of vehicles due for inspection based on mileage thresholds. Which object should return that list?
• Courts analysts need to walk hierarchical case–motion relationships for a single docket number in T-SQL. Which construct should they use?
• A 311 triage board must rank open tickets by age within each neighborhood. Which T-SQL feature should produce that ranking?
• Permit applications store inspection checklists as JSON arrays that must become relational checklist rows. Which T-SQL function should shred those arrays?
• Housing intake stores applicant preferences as JSON and must read a single nested preference key for routing. Which function should extract that scalar value?
• A water-quality API must build JSON response objects and arrays for sensor readings entirely in T-SQL. Which functions should construct those payloads?
• County open data must publish each restaurant inspection with its violation codes collected into one JSON array. Which function should aggregate those codes?
• 311 operators must filter requests whose JSON payload contains a specific tag value. Which JSON function should they use in the predicate?
• Public-health intake must validate that phone numbers in free-text clinic notes match an expected pattern. Which T-SQL predicate function should they use?
• Permits staff must scrub OCR noise from address lines with a regex replacement before geocoding. Which function should transform the text?
• Courts need to pull the case-year token from mixed caption strings using a regular expression. Which function should extract that substring?
• Library acquisitions must locate where an ISBN-like token starts inside messy receiving notes. Which regex function returns that position?
• Transit safety analysts must count how many plate-like tokens appear in each incident narrative. Which regex function should they use?
• Emergency management stores multi-agency radio codes in a delimited string and must explode them into one row per code. Which regex function should they use?
• A housing authority needs to flag near-duplicate applicant names against a master resident index so clerks can merge files before waitlist placement. Which T-SQL function should the developer use to measure how many character edits separate two names?
• A 311 intake screen must suggest close street-name matches when residents mistype an address. Which T-SQL function returns a similarity score the team can threshold for civic address correction?
• A county clerk must merge vendor payee names that look alike but are not identical before issuing warrants. Which fuzzy-matching function is the best fit for comparing those municipal payee strings?
• Emergency management models facilities, contractors, and officials as SQL graph nodes and needs a query that finds contractors linked to both a jobsite and a conflicted official. Which T-SQL construct should the developer use?
• A water-billing stored procedure inserts new meter rows and must handle unique-key collisions without aborting the whole batch. Which T-SQL pattern should the developer implement?
• Parks planners need cumulative reservation hours per facility over a season. Which approach combines a CTE with a window aggregate for that civic utilization metric?
• A FOIA export review must surface every email-like token in free-text notes so redaction staff can inspect them. Which regex function returns the set of matches for that municipal review?
• Public-health analysts want clinics ranked by outbreak case counts within each district without skipping ranks after ties. Which window function should they use?
• Transit ops must build a nested JSON array of stop names per route for a mobile API. Which combination best produces that civic route payload?
• A courts clerk ops procedure catches failures and must persist diagnostic details for after-hours triage. Which values should the CATCH block typically log into the municipal error table?
• A city Azure SQL team plans to enable GitHub Copilot against municipal schema in a shared repo. What security impact should leaders evaluate first?
• County warehouse developers building SQL on clinic tables in Microsoft Fabric need in-product SQL assistance. What should they enable?
• A permits SQL Database Project team wants AI pair-programming inside the GitHub workspace for that repo. What should the organization enable?
• A 311 platform team uses Copilot chat with MCP tools and must harden the session for safer civic development. What should they configure?
• A housing authority wants Copilot to consistently follow civic PII handling rules when suggesting T-SQL. What should the team create?
• Courts developers want Copilot MCP assistance against docket schemas without touching production. How should they connect MCP?
• Open-data engineers need Copilot helpers against lakehouse SQL for civic extracts. Which MCP endpoint type should they connect?
• Transit DevSecOps reviews Copilot suggestions in a municipal repo. Which risk should they specifically watch for?
• Public-health staging must let Copilot discover objects through MCP without mutating data. How should MCP tools be configured?
• A clerk team wants Copilot suggestions to favor safe query patterns in municipal T-SQL. What should their instruction file emphasize?
• A water utility must enable Copilot for regulated billing code without exposing every city repo. Which enablement scope is most appropriate?
• Emergency operations staff are tempted to paste live incident rows into Copilot chat for faster queries. What is the correct guidance?
• Parks developers run SQL in local Docker and want Copilot MCP to reach that instance. What should they configure?
• County IT wants Copilot instruction files to stay aligned as the SQL Database Project evolves. What practice should they follow?
• A 311 platform team’s SQL work spans both a Fabric lakehouse and a GitHub-hosted SQL Database Project. How should they choose Copilot surfaces?