A financial analyst needs to calculate the total sales for each product category, but only for orders placed in the last quarter. The Orders table contains order_date and order_total, and the Products table contains product_id and category. Which SQL query correctly returns the category and total sales for the last quarter?
A data analyst runs a query on a PostgreSQL 15 database to find customers whose last purchase was more than 180 days ago. The query returns zero rows even though many customers meet the condition. The analyst confirms the 'last_purchase_date' column is defined as DATE and contains valid dates. Which function should the analyst use to calculate the day difference between the current date and 'last_purchase_date'?
A financial analyst runs a query that joins a large 'Transactions' table to a small 'CurrencyRates' table and filters on a date range. The execution plan shows a nested loop join with an index seek on the small table for every row of the large table, and the query runs slowly. Which change is most likely to improve performance without altering the result set?
A database developer is writing a query to retrieve a list of all customers who have placed at least one order in the last 30 days. The Orders table contains a CustomerID column and an OrderDate column. The developer wants to include each qualifying customer only once in the result set. Which SQL clause should be used?
A database administrator must ensure that a reporting query returns a consistent snapshot of data even while concurrent transactions are committing changes. The database uses multi-version concurrency control. Which isolation level should the administrator configure to guarantee that every read in the transaction sees the same snapshot?
A database developer is building a reporting query that must combine data from an Orders table and a Customers table. The query needs to include all customers, even those who have not placed any orders, and for customers with orders, it should show the order details. The developer wants to avoid returning duplicate customer rows when a customer has multiple orders. Which two techniques should the developer use? (Choose two.)
A database developer is optimizing a query that joins a large fact table 'Sales' with a dimension table 'Products' on product_id. The query filters on Products.category = 'Electronics' and aggregates sales amounts. The developer notices the query performs a sequential scan on 'Products' despite an index on category. The Products table has 100,000 rows, and only 5% belong to 'Electronics'. Which action is most likely to improve query performance?
A data analyst runs a transaction in a PostgreSQL 15 database: they issue an UPDATE on the 'inventory' table for 50 rows, then attempt to run a SELECT on the same table within the same transaction. The SELECT is blocked and waits indefinitely. The analyst confirms that no other session is holding a lock on those rows. Which of the following is the MOST likely cause of the block?
A database developer is writing a query to find all employees whose salary is above the average salary of their own department. The HR database contains an Employees table with columns employee_id, department_id, and salary. Which SQL statement will return the correct result?
A database administrator needs to store unstructured data that scales horizontally across multiple servers while maintaining high availability. Which database structure should be selected?
A database administrator is investigating performance degradation during heavy concurrent write operations. Which isolation level provides the highest level of data consistency but the lowest concurrency?
A developer needs to ensure that a 'Customer_ID' field in a 'Orders' table always references a valid ID in the 'Customers' table. Which constraint should be applied?
An analyst is writing an SQL query to retrieve total sales per region, but the result must only show regions with more than 100 transactions. Which clause is required?
A database administrator wants to check the current connection count. Which system catalog or dynamic management view would typically provide this information?
A data engineer is reviewing a table that stores customer support tickets. The table has a column TicketID defined as an IDENTITY column and a column CreatedDate defined as DATETIME2 with a DEFAULT constraint of SYSDATETIME(). During a bulk insert of 500 rows, the engineer notices that all 500 rows received the exact same CreatedDate value, but each TicketID is unique and sequential. What is the most likely explanation for the identical CreatedDate values?
A database administrator is designing a schema for a financial application that must enforce that every account balance is never negative. The application inserts and updates balances frequently, and the DBA wants the rule enforced at the database level regardless of which application or script performs the change. Which database object should the DBA create to enforce this rule?
A data engineer is designing a table that stores sensor readings. Each row must include the sensor ID, a timestamp, and a numeric reading. The sensor ID and timestamp together uniquely identify each row, and the table will be queried frequently by sensor ID for the most recent reading. Which table design best supports this scenario?
A data engineer is designing a database for a sensor network that generates 20,000 readings per second. The schema is fixed, and each reading must be stored with minimal write latency while preserving the exact order of arrival for later time-series analysis. Which database structure should the engineer choose?
A database administrator needs to grant a user the ability to retrieve data from a specific table but must prevent the user from modifying or deleting any data. Which SQL statement should the administrator execute?
A financial analyst runs a query that joins a 500-million-row transactions table to a 200-row currency table on a non-indexed currency code column. The optimizer chooses a nested loop join and the query runs for hours. Which execution strategy should the analyst encourage to improve performance most effectively?
A data engineer is designing a table in a relational database to store product inventory records. The business requires that the combination of 'ProductCode' and 'WarehouseID' uniquely identifies each row, and that no duplicate pairs are allowed. Which constraint should be applied to enforce this requirement?
A financial services company stores trade records in a table where the combination of TradeDate and TradeID must be unique, but individual TradeID values can repeat on different dates. The database administrator needs to enforce this uniqueness at the database level. Which constraint should be implemented?
A database administrator needs to add a new column named 'loyalty_points' of type INTEGER to an existing 'customers' table without affecting existing rows' data. Which SQL statement should the administrator execute?
A retail company is designing a data warehouse star schema for sales analysis. The team must choose attributes for the fact table and the dimension tables. Which TWO of the following should be placed in the fact table rather than a dimension table? (Choose two.)
A database architect is designing a schema for a high-volume transaction system. The system requires strict data consistency and supports complex queries with multiple joins. The architect must choose between a normalized relational model and a denormalized model. Which approach is most appropriate and why?
A database administrator is designing a table to store product inventory. The table must enforce that each product has a unique SKU and that the quantity_on_hand column never drops below zero. The administrator adds a PRIMARY KEY constraint on SKU and a CHECK constraint on quantity_on_hand. A developer then tries to insert a row with a NULL SKU and a negative quantity. Which statement describes the outcome?
A database administrator is reviewing a query that joins a large Sales table with a small Regions table. The query filters on a column in the Regions table and returns only matching rows from both tables. The administrator notices that the query plan shows a nested loop join. Which factor most likely influenced the optimizer's choice of this join method?
A database administrator is reviewing a schema for a reporting database. The team needs to ensure that queries against a large 'Sales' table return consistent results even if a long-running report is executing while new sales are being inserted. Which TWO of the following techniques help provide a consistent snapshot of data for the report? (Choose two.)
A data analyst needs to calculate the average order value per customer for the previous month. The Orders table contains CustomerID, OrderID, and OrderTotal. The analyst writes a query that includes the AVG(OrderTotal) function and groups by CustomerID. However, the result shows some customers with an average of NULL. Which condition could cause this?
A developer needs to retrieve a list of all customers who have placed at least one order, along with the total amount spent by each. The 'Customers' table has one row per customer, and the 'Orders' table has one row per order with a 'CustomerID' foreign key. Which SQL construct should the developer use?
A database team is designing a new table to store employee data. They need to ensure that the table structure supports efficient queries and maintains data integrity. Which two of the following statements about primary keys and indexes are true? (Choose two.)
A data architect is designing a schema for a system that must store highly variable product attributes across thousands of categories, where each category has a different set of attributes and the schema must evolve frequently without downtime. Which database structure is best suited for this requirement?
A database administrator needs to grant a new analyst read-only access to a specific table named 'SalesData' in a PostgreSQL database. The analyst should be able to query the table but not modify it. Which SQL statement should the administrator execute?
A database administrator is designing a schema for a new order processing system. The system must enforce that every order is associated with exactly one customer, and that a customer can place many orders. Which relationship type and implementation should be used?
A database developer is writing a query that must return the product_name and the total quantity sold for each product from a Sales table. The Sales table contains product_id, quantity, and sale_date. The Products table contains product_id and product_name. The query should include only products that have total sales quantity greater than 100. Which SQL statement is correct?
A database administrator is reviewing a schema and needs to ensure data integrity. Which TWO of the following are valid SQL constraints that can be applied to a column to enforce data integrity? (Choose two.)