Data-Management-Foundations Sample Questions Answers

Avalid SELECT statementin SQL follows this basic syntax:

sql

SELECT column1, column2

FROM table_name

WHERE condition;

The correct optionDfollows this syntaxcorrectly.

Why Other Options Are Incorrect:

Option A (Incorrect):SQL does not usedouble quotes(") around column/table names unless explicitly required in some databases.

Option B (Incorrect):The WHERE clausemust appear after the FROM clause.

Option C (Incorrect):ALL isnota valid keyword in standard SQL queries.

Thus,Option Dfollows the correct SQL syntax.

Non-relational databases(also calledNoSQL databases) are designed for handlingbig dataandunstructured dataefficiently. They are optimized forhorizontal scaling, making them ideal forlarge-scale distributed systems.

Option A (Correct):Non-relational databases areoptimized for big data, handling massive volumes of data across distributed architectures.

Option B (Incorrect):NoSQL databases donotuse SQL as their primary query language. They often use JSON-based queries (e.g., MongoDB).

Option C (Incorrect):Transaction-heavy applications requireACID compliance, which relational databases (SQL) handle better than NoSQL databases.

Option D (Incorrect):NoSQL databases usedocument, key-value, graph, or column-family storage models, nottables, columns, and rowslike relational databases.

TheMERGEstatement, also known asUPSERT, combinesINSERT, UPDATE, and DELETEoperations into asingle statementbased on a given condition. It is commonly used indata warehouses and large-scale databases.

Example Usage:

sql

MERGE INTO Employees AS Target

USING NewEmployees AS Source

ON Target.ID = Source.ID

WHEN MATCHED THEN

UPDATE SET Target.Salary = Source.Salary

WHEN NOT MATCHED THEN

INSERT (ID, Name, Salary) VALUES (Source.ID, Source.Name, Source.Salary);

If a match is found, the UPDATE clause modifies the existing record.

If no match is found, the INSERT clause adds a new record.

Why Other Options Are Incorrect:

Option A (INTO) (Incorrect):Used in INSERT INTO, butdoes not combine operations.

Option B (JOIN) (Incorrect):Used to combine rows from multiple tables, butnot for merging data.

Option D (DROP) (Incorrect):Deletes database objects liketables, views, and indexes, butdoes not merge data.

Thus, the correct answer isMERGE, as itcombines inserts, updates, and deletes into a single operation.

MongoDB is aNoSQL (non-relational) databasethat isopen-sourceand supportsdocument-oriented storage. It allows for flexible schema design and is optimized for big data applications.

Option A (Correct):MongoDB is aNoSQL databasewith an open-sourceAGPLlicense, making it both free to use and non-relational.

Option B (Incorrect):SQL Serveris arelational database(RDBMS) developed by Microsoft and isnot open-source.

Option C (Incorrect):MySQLisopen-sourcebut is arelational database(RDBMS), not a NoSQL system.

Option D (Incorrect):Oracle Databaseisrelationalandproprietary(not open-source).

In database indexing, abranchrefers to thepath from the top-level block (root node) to a bottom-level block (leaf node)in aB-Tree or B+ Tree index structure.

Example Usage in Indexing:

AB-Tree indexorganizes data hierarchically, withbranches leading to different parts of the tree.

When searching for a record, thequery follows a branchfrom theroot node down to the correct leaf node.

Why Other Options Are Incorrect:

Option A (Fan-out) (Incorrect):Refers tohow many children a node has, not the path.

Option B (Crow’s foot) (Incorrect):Anotation used in ER diagrams, not indexing.

Option D (Sparse index) (Incorrect):A type of indexstoring only some entries, not the path itself.

Thus, the correct answer isBranch, as it defines thepath from top to bottom in a database index.

Anattribute typerefers to asingle, specific valuewithin a table, such as Salary, Age, or Price.

Example Usage:

A screenshot of a computer AI-generated content may be incorrect.

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Name VARCHAR(50),

Salary DECIMAL(10,2)

);

Salary is anattribute typewith individual values for each employee.

Why Other Options Are Incorrect:

Option A (Glossary) (Incorrect):Refers todocumentation, not database values.

Option B (Entity type) (Incorrect):Representsa class of objects(e.g., Employees), not individual values.

Option D (Relationship) (Incorrect):Definesconnections between entities, not attributes.

Thus, the correct answer isAttribute type, as it represents anindividual data value.

Cardinalitydefines theminimum and maximum number of occurrencesof one entity in relation to another.

Example Usage in an ER Model:

One-to-Many (1:M): Acustomer can place multiple orders, but each order belongs to onlyonecustomer.

Customers (1) ——— (M) Orders

Cardinality notation:

(1,1) → One-to-One

(0,M) → Zero-to-Many

(1,M) → One-to-Many

Why Other Options Are Incorrect:

Option B (Physical design) (Incorrect):Focuses onstorage and indexing, not relationships.

Option C (Attribute minimum) (Incorrect):No such formal term in database modeling.

Option D (Partition) (Incorrect):Refers todividing tables, not relationship constraints.

Thus, the correct answer isCardinality, as it definesmin/max constraints on relationships.

Aquery processoris responsible forquery optimization and executionin a database management system (DBMS). It analyzes SQL statements, optimizes execution plans, and ensures efficient retrieval of data.

Option A (Correct):Thequery processoroptimizes queries by analyzing metadata from thesystem catalogto determine the best execution strategy.

Option B (Incorrect):Logging transactionsbefore applying changes is the responsibility of thetransaction manager.

Option C (Incorrect):Translating instructions intofile system commandsis handled by thestorage manager, not the query processor.

Option D (Incorrect):While the query processor helps retrieve results, thedatabaseengineandAPI layerare responsible for returning results to applications.

Thesecond step in implementing relationshipsis definingone-to-one (1:1) relationshipsbetween entities.

Example Usage:

Example of a 1:1 relationship:

sql

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Name VARCHAR(50)

);

CREATE TABLE EmployeeDetails (

EmpID INT PRIMARY KEY,

Address VARCHAR(255),

FOREIGN KEY (EmpID) REFERENCES Employees(EmpID)

);

Here, eachemployee has exactly one detail record, creating a1:1 relationship.

Why Other Options Are Incorrect:

Option A (Implement weak entities) (Incorrect):Weak entities rely on aforeign keyand are implementedlater.

Option C (Implement subtype entities) (Incorrect):Subtypes arespecial casesandnot implemented in the second step.

Option D (Specify cascade) (Incorrect):Cascade rules (ON DELETE, ON UPDATE)are defined duringforeign key implementation, not in the second step.

Thus, the correct answer isImplement one-one relationships, as it is thenext logical stepafter defining entities.

TheHAVINGclause is used in SQL to filtergrouped resultsgenerated by the GROUP BY clause. Unlike WHERE, which filters individual rowsbeforegrouping, HAVING filtersafter aggregationhas been performed.

Example Usage:

sql

SELECT Department, AVG(Salary) AS AvgSalary

FROM Employees

GROUP BY Department

HAVING AVG(Salary) > 50000;

This query first groups employees by Department, calculates theaverage salary per department, and then filters onlythose departments where the average salary is greater than 50,000.

Why Other Options Are Incorrect:

Option A (REPLACE) (Incorrect):Used for string substitution, not filtering.

Option C (WITH) (Incorrect):Used inCommon Table Expressions (CTEs), not for filtering.

Option D (WHERE) (Incorrect):Used forrow-level filtering before aggregation, but itcannot be used on aggregate functions like SUM() or AVG().

Thus,HAVING is the correct answerfor filtering after grouping.

TheCHECKconstraint is used in SQL toenforce ruleson a column's values. It ensures that data inserted into a table meets specified conditions, such as range restrictions or logical rules.

Example Usage:

sql

CREATE TABLE Employees (

ID INT PRIMARY KEY,

Name VARCHAR(50),

Salary INT CHECK (Salary BETWEEN 30000 AND 150000)

);

This constraint ensures thatsalary values fall between 30,000 and 150,000.

If an INSERT or UPDATE statement tries to set Salary = 20000, itfailsbecause it does notmeet the CHECK condition.

Why Other Options Are Incorrect:

Option B (FROM) (Incorrect):Used in SELECT statements, not for constraints.

Option C (WHERE) (Incorrect):Filters rows in queries butdoes not enforce constraints.

Option D (SET) (Incorrect):Used for updating records (UPDATE table_name SET column = value) butnot for defining constraints.

Thus,CHECK is the correct answer, as it ensures that column values remain within an expected range.

Asimple primary keyconsists ofonly one columnthat uniquely identifies each row in a table.

Example Usage:

sql

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

Name VARCHAR(50)

);

StudentIDis asimple primary keybecause it consists of only one field.

Why Other Options Are Incorrect:

Option B (Partition) (Incorrect):Refers topartitioned tables, which divide data for performance reasons butare not related to primary keys.

Option C (Stable) (Incorrect):This is not a recognized term in database keys.

Option D (Meaningless) (Incorrect):Primary keys are oftenmeaningless (e.g., auto-incremented IDs), but this isnot a term used to describe their structure.

Thus, the correct answer isSimple, as asingle-field primary keyis referred to as asimple primary key.

Databases need to supportApplication Programming Interfaces (APIs)to enable seamless integration withgeneral-purpose programming languageslike Python, Java, and C#. APIs likeODBC (Open Database Connectivity) and JDBC (Java Database Connectivity)allow applications to interact with databases without requiring complex SQL commands.

Option A (Incorrect):Thestorage manageris responsible for managing low-levelfile system operations, but it does not simplify SQL integration with programming languages.

Option B (Incorrect):The ability toreverse results(e.g., using ORDER BY DESC) is a SQL feature but is unrelated to integration with programming languages.

Option C (Incorrect):Thequery processoroptimizes and executes SQL queries but does not provide an interface for application development.

Option D (Correct):APIsallow databases to be accessed easily from different programming environments, simplifying integration.

In MySQL, theBIGINTdata type is a64-bit integerthat requires8 bytes (64 bits) of storage. It is used to store large numerical values beyond the range of INT (4 bytes).

Option A (Incorrect):1 byte corresponds toTINYINT, which can store values from -128 to 127.

Option B (Incorrect):3 bytes is not a standard integer storage size in MySQL.

Option C (Incorrect):4 bytes corresponds toINT, which has a range of -2,147,483,648 to 2,147,483,647.

Option D (Correct):BIGINT takes8 bytesand supports a massive range of numbers from -2^63 to 2^63 -1.

TheABS()function in SQL returns theabsolute valueof a given number, effectively measuring itsdistance from zero.

Example Usage:

sql

SELECT ABS(-50), ABS(50);

Result:

50 | 50

This function ensures that numbers arealways positive, regardless of their original sign.

Why Other Options Are Incorrect:

Option A (CONCAT) (Incorrect):Used tocombine strings(not numbers).

Option B (LOWER) (Incorrect):Converts text tolowercase, not numerical operations.

Option C (FROM) (Incorrect):Part of SELECT FROM queries,not a function.

Thus, the correct choice isABS(), which computes the absolute value of a number.