Database Structures And SQL

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?

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?

Which SQL statement is used to remove a table entirely from the database?

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 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 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 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 analyst needs to retrieve a list of all unique countries from a Customers table, sorted alphabetically. Which SQL statement should be used?

An application requires an automatic timestamp for every row inserted into an 'Audit' table. Which feature should the developer implement?

Which TWO of the following are DDL (Data Definition Language) commands?

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?

Which TWO of the following statements about SQL are true?

A query is slow due to excessive scanning of a large table. The execution plan shows a 'Table Scan'. What is the most likely missing element?

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 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 user wants to add a new column to an existing table named 'Employees'. Which SQL statement should be executed?

An application is experiencing deadlocks. Which strategy would effectively reduce the frequency of deadlocks?

Which SQL command is used to permanently remove all rows from a table while keeping the table definition intact?

Which THREE of the following are standard SQL aggregate functions?

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.)

Which DML statement is used to update existing records in a database table?

Which operator is used to search for a pattern in a string column?

To improve read performance on a table frequently queried by a specific column, which database object should be created?

A database developer needs to combine the results of two different SELECT queries into a single result set. Which operator should be used?

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?

Which SQL keyword is used to eliminate duplicate values from a query result set?

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 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?

Which TWO of the following are common types of NoSQL databases?

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 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 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 developer needs to calculate the running total of sales. Which SQL feature is most appropriate?

To implement a secure 'Multi-Tenant' architecture, which design approach is most effective for data separation?

Which type of join should be used to return all records from the 'Left' table and only matching records from the 'Right' table?

Which TWO of the following scenarios are best suited for using a 'View'?

To prevent unauthorized access to specific columns in a 'Salary' table, which object should be created for the reporting team?

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?

Which NoSQL structure is best suited for scenarios where data relationships are complex and frequently traversed?

Which TWO of the following are common database maintenance tasks?

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 database administrator needs to store unstructured data that scales horizontally across multiple servers while maintaining high availability. Which database structure should be selected?

Which scenario best justifies the use of a relational database over a NoSQL database?

A database administrator wants to check the current connection count. Which system catalog or dynamic management view would typically provide this information?

Which THREE of the following are standard SQL join types?

Which TWO of the following are potential issues when denormalizing a database?

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 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 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?

When migrating data between two different relational databases, which DDL-related issue is most likely to cause failure if ignored?

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?

What is the primary purpose of a 'Normalization' process in database design?

Which THREE of the following are benefits of database indexing?

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?

Which THREE of the following are common SQL constraints?

Which SQL clause is used to sort the result of a query by one or more columns?

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 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?

Which THREE of the following are components of a database connection string?