40 questions · Database Structures And SQL · All types, answers revealed
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?
FOREIGN KEY enforces referential integrity between tables.
Why this answer
A foreign key constraint ensures referential integrity between two tables by requiring that the value in the child table exists in the parent table.
Which SQL statement is used to remove a table entirely from the database?
DROP TABLE deletes the entire structure.
Why this answer
The DROP TABLE statement removes the definition and all data within the table permanently.
A transaction involves multiple updates that must either all succeed or all fail. Which command ensures this behavior?
COMMIT is the command that confirms and saves a transaction.
Why this answer
COMMIT finalizes the transaction, while ROLLBACK reverts it; together they ensure atomicity.
An application requires an automatic timestamp for every row inserted into an 'Audit' table. Which feature should the developer implement?
A trigger can automatically inject a timestamp during an INSERT.
Why this answer
A trigger is a database object that automatically executes code when a specific event (like INSERT) occurs on a table.
Which TWO of the following are DDL (Data Definition Language) commands?
Used to remove objects.
Why this answer
CREATE and DROP are DDL commands used to define or modify database structures.
Which THREE of the following are considered ACID properties in database transactions?
All or nothing operation.
Why this answer
ACID stands for Atomicity, Consistency, Isolation, and Durability.
Which TWO of the following statements about SQL are true?
It is the standard for RDBMS.
Why this answer
SQL is a standard language for relational databases, and DML is used for data manipulation.
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?
An index provides a direct path to the data.
Why this answer
A Table Scan occurs when the database must read every row; an index would allow it to find data more efficiently.
A user wants to add a new column to an existing table named 'Employees'. Which SQL statement should be executed?
ALTER TABLE is the correct command to add a column.
Why this answer
The ALTER TABLE command is used to modify the structure of an existing table, such as adding a new column.
An application is experiencing deadlocks. Which strategy would effectively reduce the frequency of deadlocks?
Consistent locking order prevents deadlocks.
Why this answer
Accessing tables in the same order across different transactions is a primary method to prevent circular wait conditions that cause deadlocks.
Which SQL command is used to permanently remove all rows from a table while keeping the table definition intact?
TRUNCATE effectively clears all data in a table.
Why this answer
TRUNCATE is a DDL operation that removes all records from a table and is generally faster than DELETE because it does not log individual row deletions.
Which THREE of the following are standard SQL aggregate functions?
Calculates the average of values.
Why this answer
SUM, AVG, and COUNT are standard aggregate functions used in SQL.
Which DML statement is used to update existing records in a database table?
UPDATE is the correct DML statement for editing records.
Why this answer
The UPDATE statement modifies the data of existing rows in a table.
Which operator is used to search for a pattern in a string column?
LIKE supports pattern matching with wildcards.
Why this answer
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
To improve read performance on a table frequently queried by a specific column, which database object should be created?
An index is specifically designed to accelerate data retrieval.
Why this answer
An index allows the database engine to find specific rows without scanning the entire table, significantly improving read performance.
A database developer needs to combine the results of two different SELECT queries into a single result set. Which operator should be used?
UNION stacks results of multiple queries.
Why this answer
The UNION operator is specifically designed to combine the result sets of two or more SELECT statements into one.
Which SQL keyword is used to eliminate duplicate values from a query result set?
DISTINCT removes duplicates from the output.
Why this answer
The DISTINCT keyword is used in a SELECT statement to return only unique values.
Which TWO of the following are common types of NoSQL databases?
Stores data as simple pairs.
Why this answer
Document and Key-Value stores are two primary categories of NoSQL databases.
A developer needs to calculate the running total of sales. Which SQL feature is most appropriate?
Window functions handle running totals efficiently.
Why this answer
Window functions, specifically SUM() OVER(), allow calculations across a set of rows related to the current row without collapsing the result into a single group.
To implement a secure 'Multi-Tenant' architecture, which design approach is most effective for data separation?
Separate schemas offer strong logical isolation.
Why this answer
Using a separate schema or database per tenant provides a hard boundary that prevents cross-tenant data access.
Which type of join should be used to return all records from the 'Left' table and only matching records from the 'Right' table?
LEFT JOIN preserves all records from the left source.
Why this answer
A LEFT JOIN returns all rows from the left table, and matching rows from the right table; if no match exists, NULL values are returned for right-side columns.
Which command is used to restrict a user's access to a specific table?
REVOKE is the standard DCL command to remove access.
Why this answer
REVOKE is the DCL command used to remove permissions previously granted to a user.
Which TWO of the following scenarios are best suited for using a 'View'?
Encapsulates logic for reuse.
Why this answer
Views are best for simplifying complex queries and implementing row/column level security.
To prevent unauthorized access to specific columns in a 'Salary' table, which object should be created for the reporting team?
Views are frequently used to restrict access to sensitive table data.
Why this answer
A view can be used to expose only specific columns to a user or group, effectively hiding sensitive data like salaries.
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?
HAVING is the correct clause to filter aggregated group results.
Why this answer
The HAVING clause is used to filter groups created by the GROUP BY clause, whereas WHERE filters individual rows.
Which NoSQL structure is best suited for scenarios where data relationships are complex and frequently traversed?
Graph databases excel at managing complex relationships.
Why this answer
Graph databases are specifically designed for data that is highly interconnected, such as social networks.
Which TWO of the following are common database maintenance tasks?
Helps the optimizer plan queries.
Why this answer
Updating statistics and rebuilding indexes are critical maintenance tasks to ensure optimal query performance.
A database administrator needs to store unstructured data that scales horizontally across multiple servers while maintaining high availability. Which database structure should be selected?
NoSQL databases provide the flexibility for unstructured data and distributed horizontal scaling.
Why this answer
NoSQL databases, such as document stores, are designed for horizontal scaling and handling unstructured data, unlike rigid relational schemas.
Which scenario best justifies the use of a relational database over a NoSQL database?
ACID compliance is a core requirement for financial systems.
Why this answer
Relational databases provide strong ACID compliance, which is essential for financial transactions where data integrity is paramount.
A database administrator wants to check the current connection count. Which system catalog or dynamic management view would typically provide this information?
DMVs are designed for monitoring server state and connections.
Why this answer
Most RDBMS provide dynamic management views (like sys.dm_exec_sessions in SQL Server) to monitor server health and connections.
Which THREE of the following are standard SQL join types?
Returns all left rows.
Why this answer
INNER, LEFT, and FULL are all standard SQL join types.
Which TWO of the following are potential issues when denormalizing a database?
Updates may cause inconsistencies.
Why this answer
Denormalization can lead to data anomalies (inconsistencies) and increased storage usage due to redundancy.
When migrating data between two different relational databases, which DDL-related issue is most likely to cause failure if ignored?
Data type mismatch is a critical technical failure point.
Why this answer
Data types can vary significantly between RDBMS vendors, leading to truncation or conversion errors if not mapped correctly during migration.
What is the primary purpose of a 'Normalization' process in database design?
Minimizing data duplication is the goal of normalization.
Why this answer
Normalization minimizes redundancy and dependency by organizing fields and table relationships, which reduces data anomalies.
Which THREE of the following are benefits of database indexing?
Unique indexes prevent duplicates.
Why this answer
Indexing speeds up data retrieval, improves JOIN performance, and aids in uniqueness enforcement.
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?
Serializable provides the highest consistency by preventing all concurrency-related anomalies.
Why this answer
Serializable isolation ensures that concurrent transactions result in a state that could have been achieved if transactions were executed serially, preventing all phenomena but reducing concurrency.
Which THREE of the following are common SQL constraints?
Ensures unique row identification.
Why this answer
PRIMARY KEY, UNIQUE, and CHECK are standard SQL constraints used to maintain data integrity.
A business user needs to calculate the average age of customers, but some age fields are NULL. Which function should be used to treat NULL as 0?
COALESCE is the standard SQL function for handling NULLs.
Why this answer
The COALESCE function returns the first non-null expression in the list, allowing NULLs to be replaced with 0.
Which SQL clause is used to sort the result of a query by one or more columns?
ORDER BY sorts query results.
Why this answer
The ORDER BY clause is used to sort the result set in ascending or descending order.
Which THREE of the following are components of a database connection string?
Identifies the host.
Why this answer
Server address, database name, and authentication credentials are all standard components of a connection string.
Ready to test yourself?
Try a timed practice session using only Database Structures And SQL questions.