Database Fundamentals

DS0-002 · 57 questions

• A startup's checkout must insert an order, its line items and a payment row as one unit, and the database itself must reject any payment that references an order that does not exist. Which database type best fits these requirements?
• An online retailer's catalog holds items whose attributes differ widely: shoes have sizes and widths, laptops have CPU and memory specs, and some items carry nested lists of variants. Developers want to store each item as one nested JSON record and still query on fields inside it. Which NoSQL type is the most natural fit?
• A web tier stores user session data and always retrieves it by session ID; it never searches by any other attribute, and each lookup must complete in well under a millisecond. Which database type best fits this access pattern?
• An analytics team runs queries that scan billions of rows of sales history, but each query reads only three or four of the table's 120 columns to compute totals. Which storage model reduces the disk I/O for this workload the most?
• A fraud team must find rings of accounts connected through shared devices, shared addresses and shared payment cards, often four or five hops apart. Relational queries with many self-joins are timing out. Which database type is designed for this kind of query?
• A support portal converts every knowledge-base article into an embedding and must return the articles whose meaning is closest to a user's question, even when they share no keywords. Which database type is built for this retrieval?
• An IoT platform ingests a temperature reading from 50,000 sensors every second. Queries ask for averages per five-minute window over the last day, and readings older than 90 days are downsampled to hourly values. Which database type best fits this workload?
• An engineering application models assemblies as in-memory objects that use class inheritance and references between parts. The team wants to persist these objects directly, with the class hierarchy intact, instead of translating them into tables through a mapping layer. Which database model does this describe?
• A payroll table defines fixed columns such as employeeid INTEGER, paydate DATE and grosspay DECIMAL(10,2), and every row must conform to that definition. How is this data classified?
• A legal department wants to catalog an archive of scanned contract images, recorded customer calls and site photographs. None of the files follows a predefined data model. How should a DBA classify this content?
• A vendor sends a daily JSON feed. Each record carries self-describing field names, but some records include optional fields and nested arrays that others omit. How is this data classified?
• A social media app must scale writes horizontally across several regions to absorb viral traffic spikes. A post's like count can be briefly out of date between regions without harming the business, and the post format changes often. Which choice best fits these requirements?
• User profiles are stored as JSON. The application must fetch a profile by user ID, but it must also find profiles by email address and list every user in a given city. A developer proposes a key-value store. Why is a document database the better fit?
• A monitoring team loaded server CPU and memory metrics into a graph database, modeling each reading as a node linked to its host. Queries that average CPU per host in hourly buckets across the last month are now slow. Which change best matches the workload to a database type?
• A data lake receives three feeds: CSV exports from an ERP system with a fixed column layout, application logs written as JSON lines with varying fields, and product image files. How should the three feeds be classified, in that order?
• A developer must create a new shipments table and lengthen the customers table's email column from 100 to 255 characters. Which SQL subtype covers both statements?
• Prices in the products table must rise by 10 percent, but only for products in the 'Garden' category. Which statement makes this change correctly?
• A funds-transfer routine debits one account and then credits another. Yesterday the server failed between the two UPDATE statements, leaving money debited but never credited. How should the routine be changed?
• A DBA is running a long data-correction transaction in several steps. After step 3 succeeds, step 4 might need to be undone if its row counts look wrong, but the work from steps 1 through 3 must be kept. Which approach does this within the same transaction?
• A report must list every customer with the number of orders each has placed, including customers who have never ordered, who should appear with a count of 0. Which FROM clause supports this report?
• A query joining a 2,000-row customers table to a 50,000-row orders table returns 100,000,000 rows. The query is: SELECT c.name, o.total FROM customers c, orders o WHERE o.total > 0; What is the most likely cause?
• The employees table holds employeeid, name and managerid, where managerid refers to another row's employeeid in the same table. Which query lists each employee's name beside the manager's name?
• A sales report must show every salesperson's row with region and revenue, plus a column giving that person's rank within their own region by revenue, highest first. Which expression produces the rank column?
• A monthlysales table holds one row per month with salesmonth and amount. The report must show each month's amount next to a running total from the first month through that month. Which expression produces the running total?
• The products table has productid, category and price. Which query returns only the products priced above the average price of their own category?
• A query must return customers who have never placed an order. The orders.customerid column allows NULLs, and some legacy order rows have a NULL customerid. Which WHERE clause on the customers table returns the correct customers?
• An analyst needs every region whose total sales exceed 500000. The query SELECT region, SUM(amount) FROM sales WHERE SUM(amount) > 500000 GROUP BY region; fails. Which corrected query returns the intended result?
• An application's account features are register, view profile, change email address and close account, where closing removes the user's row. Which SQL statements map to these four actions, in that order?
• A developer's script opens a cursor over one million rows in the orders table and, for each row, runs an UPDATE that sets status = 'ARCHIVED' when orderdate is before 2020-01-01. The job takes hours. What should the DBA recommend?
• Marketing wants one mailing list combining the newslettersubscribers and webinarattendees tables, each with an email column. A person who appears in both tables must receive only one email. Which statement builds the list?
• Compliance requires that every change to the salary column in the employees table be recorded in a salaryaudit table with the old value, new value and time, no matter which application or user makes the change. Which approach meets this requirement?
• After a row-level trigger was added to a busy orderitems table, inserts slowed sharply, and on some days the trigger's own UPDATE against orderitems fires the trigger again until the engine aborts the statement. What is the most likely explanation?
• Dozens of client applications send the same multi-step order-creation SQL as ad hoc text, some by concatenating user input into the statements. The DBA wants one reusable implementation, lower injection risk, and the ability to grant execute rights without table rights. Which solution fits?
• Analysts repeatedly write the same expression to convert a price and a currency code into US dollars, and they need to use it inside the SELECT lists and WHERE clauses of their own queries. Which object should the DBA create?
• A routine must accept a start date and an end date, return a multi-row sales summary for that range, and insert a row into a reportlog table every time it runs. Which database object should implement this routine?
• A nightly data-cleanup script currently runs from a DBA's workstation and fails whenever that laptop is shut down or the DBA is logged out. The cleanup must run every night unattended. What is the best change?
• A web form checks in the browser that the email field is well formed and the quantity is a positive number, which cuts round trips to the database. A developer proposes removing the server-side validation because the browser already checks. What should the DBA advise?
• A Windows-based DBA team must automate the same health checks across 60 SQL Server instances, reading Windows event logs and service states as well as running queries on each instance. Which scripting language is the most natural fit?
• A script must call a vendor's REST API, reshape the nested JSON response into rows, and load those rows into a database table on a schedule. Which language and approach is the best fit?
• On a Linux database host, nightly dump files must be compressed, files older than 14 days must be deleted, and the job must run from cron. Which scripting option is the most direct fit?
• A legacy job parses fixed-width mainframe text reports using dozens of complex regular expressions before loading the results into a database. Which scripting language is traditionally associated with this kind of regex-heavy text processing?
• An administrator must apply the same schema change to 40 database servers overnight, unattended, using identical steps on each and keeping a log of each server's result. Which approach best meets this need?
• A developer writing complex SQL wants syntax highlighting, code completion, result grids and a graphical view of execution plans while working interactively. Which environment provides these features together?
• A maintenance script runs on an application server and connects to the database with a username and password stored in plain text at the top of the script file. Several operators can read that file. What should the DBA do?
• A reporting script on a laptop pulls all 40 million rows of the sales table across the network and then sums revenue by region in memory. It is slow and saturates the network link. What is the best fix?
• A DBA does two kinds of work: one-off exploratory queries while investigating a slow report, and a nightly production export that must run unattended. Which pairing of environments fits best?
• A Java application using Hibernate lists 200 orders with their line items. The SQL log shows one query for the orders followed by 200 separate queries, one per order, to load the items. What is the cause, and what is the usual remedy?
• A .NET application using Entity Framework calls ToList() on the Customers set and then filters the resulting list with a LINQ Where clause for customers in one country. Database monitoring shows the full table being read on every request. What should be changed?
• A Python service built with SQLAlchemy has slow endpoints, and the DBA needs to see the exact SQL statements the ORM sends to the database before tuning anything. What is the most direct first step?
• Analytics engineers built dbt models materialized as tables, so every scheduled run rebuilds each large fact table from scratch and consumes heavy warehouse compute. New rows arrive daily, and older rows rarely change. Which change reduces the load on the warehouse?
• A Java development team is adopting Ebean for a new order-management service, and a junior developer asks what the framework will actually do between the application's classes and the relational database. Which description of an object-relational mapper (ORM) is accurate?
• A new application release went live yesterday, and CPU on the database server has roughly doubled during normal traffic. The release changed several entity mappings and repository methods in the application's ORM layer, but no database settings or hardware changed. What should the DBA do first?
• A developer submits an ORM query for review. In testing it returns exactly the right rows for a customer summary page, but the logged SQL is a SELECT against a table with about 80 columns, while the page displays only three of them. Which review conclusion is correct?
• A team has changed how its ORM loads related order lines and wants to know, objectively, whether the change made database performance worse before rolling it out widely. Which approach measures the impact most reliably?
• A monthly report query generated by the application's ORM joins six tables and runs for minutes. Profiling shows it needs a rewrite the ORM's query API cannot express, while the rest of the application uses the ORM without problems. Which remediation is most appropriate?
• An architecture review board is deciding whether a new service should use an ORM such as Entity Framework or hand-written SQL for its data access layer. Which statement best summarizes the trade-off the board should weigh?
• During a sales event the database starts rejecting connections with a too-many-connections error. Investigation shows the application's ORM configuration opens a brand-new database connection for every web request and closes it afterward. Which change is the most appropriate programming-side fix?