MODEL ANSWER KEY

MBA – Structured Query Language for Business | CIA Examination

Question order used: Odd Numbered Questions → SET_1 | Even Numbered Questions → SET_2

Question 1
Source: SET_1 | Original Question 1 | SECTION A

Define a database system application with one example.

Answer

A database system application is a software-based system that uses a database to store, manage, retrieve,
and update structured data for an organizational purpose. It combines data, DBMS software, and user
operations. One example is a university student information system that stores student details, courses,
attendance, marks, and fee records. Such an application allows multiple users to access the same
information in a controlled manner. Its value lies in organized storage, faster retrieval, reduced duplication,
and better decision support compared with manual or file-based record handling.

Question 2
Source: SET_2 | Original Question 1 | SECTION A

Differentiate between instance and schema in the context of an MBA admissions database, and explain
why confusing the two may lead to poor reporting decisions.
Answer

Instance and schema are different database concepts. Schema is the overall design or blueprint of the
database, such as the structure of tables in an MBA admissions system, including ApplicantID, Name,
Program, Score, and Status. Instance is the actual data stored in those tables at a given moment, such as the
list of applicants currently recorded. Confusing the two can lead to poor reporting because managers may
mistake a structural definition for live data reality. Reports must be based on the current instance, while
schema guides how that data is organized and interpreted.

Question 3
Source: SET_1 | Original Question 2 | SECTION A

State two purposes of a database system.

Answer

Two important purposes of a database system are data storage with efficient retrieval and data control with
consistency. First, a DBMS stores large volumes of data in an organized form so users can search, update, and
report quickly. Second, it reduces redundancy and helps maintain data integrity, so the same fact is not
inconsistently recorded in many places. In business settings, databases also support multi-user access,
1
security, backup, and transaction processing. Overall, a database system exists to manage data as a reliable
organizational resource rather than as isolated files.

Question 4
Source: SET_2 | Original Question 2 | SECTION A

A retailer wants separate data views for store managers, finance staff, and HR executives. Explain how
views of data and data abstraction improve usability and control in such a system.
Answer

Views of data and data abstraction improve both usability and control. Views of data mean that different users
can see different slices of the same database according to their needs. For example, store managers may view
inventory and daily sales, finance staff may view revenue and cost records, and HR may view employee
attendance and payroll. Data abstraction hides unnecessary technical complexity and presents only the
relevant level of detail. Together, these features simplify user interaction, improve security, reduce confusion,
and ensure that employees access only the information necessary for their roles.

Question 5
Source: SET_1 | Original Question 3 | SECTION A

What is meant by views of data? Name any two types of views.

Answer

Views of data refer to the way different users see or interact with the same database according to their needs.
A database may present only selected portions of the full data, hiding irrelevant details. This helps in security,
simplicity, and role-based access. Two commonly named types of views are the external view and the internal
view, though in the three-level architecture the conceptual view is also important. For example, a student may
see only grades and attendance, while an administrator may see complete academic and fee records. Thus,
views personalize database access without changing the actual stored data.

Question 6
Source: SET_2 | Original Question 3 | SECTION A

A hospital is currently maintaining patient details in spreadsheets. Identify two limitations of this
approach and justify why a DBMS would be a better business solution.
Answer

A hospital maintaining patient data in spreadsheets faces several limitations. First, spreadsheets are weak in
handling concurrent multi-user access, so different departments may update conflicting versions. Second,
they provide poor control over integrity and relationships, making it easy to create duplicate, incomplete, or
inconsistent records. A DBMS is a better solution because it centralizes data, enforces validation rules,
supports security, and allows reliable querying across related tables such as Patients, Doctors, Visits, and

2
Billing. This improves operational coordination, reporting accuracy, and long-term scalability for healthcare
administration.

Question 7
Source: SET_1 | Original Question 4 | SECTION A

Define data abstraction.

Answer

Data abstraction is the process of hiding complex storage and implementation details while showing users
only the information they need. It allows different users to interact with a database at appropriate levels
without understanding how the data is physically stored. For example, a user may run a query on student
records without knowing file locations or indexing methods. Data abstraction improves simplicity, usability,
and security. In DBMS, it is commonly discussed through physical level, logical level, and view level, where
each level hides complexity from the next higher level.

Question 8
Source: SET_2 | Original Question 4 | SECTION A

An e-commerce firm stores customer and order data in separate tables. Explain why the join operation is
more useful than simple projection when analysing repeat-purchase behaviour.
Answer

Join is more useful than simple projection when analysing repeat-purchase behaviour because projection only
selects columns from one table, while join combines related data from multiple tables. In an e-commerce
system, customer data may be stored in one table and order data in another. To identify repeat purchasers,
the analyst must link customer identities with their order history. A join makes this connection possible. It
allows counting orders per customer, comparing purchase dates, and analysing repeat frequency. Therefore,
join supports relationship-based analysis, whereas projection alone cannot answer cross-table business
questions.

Question 9
Source: SET_1 | Original Question 5 | SECTION A

Differentiate between instance and schema (any one point).

Answer

Instance is the actual data stored in the database at a specific time, while schema is the logical structure or
design of the database. For example, the schema states that a Students table has StudentID, Name, and
CGPA columns. The instance is the current set of student rows stored in that table today. Schema changes
rarely, while instances change frequently through insert, update, and delete operations. In simple terms,
schema is the blueprint and instance is the live content. This difference is important for both database design
and day-to-day reporting.

3
Question 10
Source: SET_2 | Original Question 5 | SECTION A

A placement team needs to find students who have offers but no joining date yet. Explain how NULL
should be handled carefully while writing such SQL queries.
Answer

NULL must be handled carefully because NULL does not mean zero or blank text; it means the value is
unknown, missing, or not yet assigned. Therefore, a query such as JoiningDate = NULL will not work correctly.
The correct SQL condition is JoiningDate IS NULL. In the placement example, this helps identify students who
have an offer but no recorded joining date yet. Careful NULL handling is essential because it affects filtering,
aggregates, comparisons, and business interpretation. Incorrect NULL logic can hide valid records and lead to
wrong managerial conclusions.

Question 11
Source: SET_1 | Original Question 6 | SECTION A

What is a data model? Name any two data models.

Answer

A data model is a formal way of describing how data is structured, related, and constrained within a database
system. It provides the concepts used to design tables, attributes, entities, and relationships. A data model
helps convert a business situation into a database design that can be stored and queried systematically. Two
common data models are the relational model and the entity-relationship model. Other examples include
hierarchical and network models. In practice, the data model acts as the foundation for database design,
consistency, and meaningful information retrieval.

Question 12
Source: SET_2 | Original Question 6 | SECTION A

Why are aggregate functions important for managerial reporting? Illustrate your answer with one
business question that can be answered using an aggregate query.
Answer

Aggregate functions are important for managerial reporting because managers usually need summaries rather
than individual records. Functions such as COUNT, SUM, AVG, MIN, and MAX convert transaction-level data
into decision-ready indicators. For example, a manager may ask, “What is the average CGPA by department?”
or “How many students received offers this month?” These questions require aggregation. Aggregate queries
support dashboards, trend analysis, performance monitoring, and exception review. Without aggregate
functions, reporting would remain at raw-record level and would be far less useful for strategic and
operational decision-making.

4
Question 13
Source: SET_1 | Original Question 7 | SECTION A

Define database language.

Answer

Database language refers to the set of commands used to define, manipulate, control, and query data in a
database system. The most common example is SQL. Database language includes statements for creating
tables, inserting records, updating data, deleting data, controlling user permissions, and retrieving
information through queries. It is the means by which users and applications communicate with the DBMS.
Broadly, it includes data definition language, data manipulation language, data control language, and
transaction control language. Thus, database language is essential for both database design and day-to-day
operations.

Question 14
Source: SET_2 | Original Question 7 | SECTION A

A university wants to record students, courses, and enrolments. Distinguish between entities,
attributes, and relationships using this context.
Answer

In the university context, entities are the major objects about which data is stored, such as Students, Courses,
and Enrolments. Attributes are the properties that describe those entities, such as StudentID, StudentName,
CourseID, CourseTitle, and Grade. Relationships show how entities are connected. For example, a student
enrols in a course, and the Enrolments table captures that association. Thus, Students and Courses are
entities, their descriptive fields are attributes, and “enrols in” is the relationship. This distinction is
fundamental because it helps convert a real-world academic process into a structured database design.

Question 15
Source: SET_1 | Original Question 8 | SECTION B

Explain database architecture (three-level architecture) with a neat diagram.

Answer

The three-level database architecture consists of external level, conceptual level, and internal level. The
external level shows different user views. For example, a faculty member may view marks and attendance,
while an administrator may view fees and admissions. The conceptual level provides the overall logical
structure of the database, including tables, relationships, and constraints. The internal level describes how
data is physically stored, indexed, and accessed in storage.
A simple diagram can be represented as:
External Views → Conceptual Schema → Internal Schema
This architecture is important because it supports data abstraction and data independence. Changes in
physical storage do not necessarily affect the logical design, and different users can have customized views

5
without changing the database itself. Thus, the three-level architecture improves flexibility, security, and
maintainability.

Question 16
Source: SET_2 | Original Question 8 | SECTION B

Design a suitable relational schema for a college placement management system that must track
students, recruiters, job roles, offers, and joining status. Identify the key entities, primary keys, and
important relationships, and justify how your design supports reliable business reporting.
Answer

A suitable relational schema can include the following core tables:

Students(StudentID PK, FullName, Department, Program, Year, CGPA, Email)
Recruiters(RecruiterID PK, RecruiterName, Domain, Location)
JobRoles(RoleID PK, RecruiterID FK, RoleTitle, PackageLPA, MinCGPA)
Offers(OfferID PK, StudentID FK, RoleID FK, OfferDate, Status, JoiningDate)
Important relationships are: one recruiter can post many job roles; one student can receive many offers; one
job role can generate many offers; and each offer belongs to one student and one job role. Primary keys
uniquely identify each record, while foreign keys maintain referential integrity.
This schema supports reliable business reporting because it separates master data from transactional data.
The placement cell can analyse offers by recruiter, department, role, or joining status without duplication. It
also supports consistent filtering, aggregate reporting, and tracking of offer lifecycle from issue to joining.

Question 17
Source: SET_1 | Original Question 9 | SECTION B

Explain ER concepts: entities, attributes, entity sets with examples.

Answer

Entities are real-world objects or concepts about which the database stores information. Examples include
Student, Company, and Course. Attributes are the characteristics that describe an entity, such as StudentID,
Name, Department, or CGPA for a Student entity. An entity set is the collection of similar entities of the same
type. For example, all students together form the Student entity set, and all companies form the Company
entity set.
These concepts are central to ER modelling because they help identify what data must be stored and how it
should be organized. For example, in a placement system, Student is an entity, StudentID is a key attribute,
and the collection of all student records forms the student entity set. Clear identification of entities and
attributes is necessary before defining relationships and converting the design into relational tables.

Question 18
Source: SET_2 | Original Question 9 | SECTION B

6
A placement coordinator wants a report showing department-wise average CGPA of students, along with
only those departments where the average CGPA is above 7.50. Explain the SQL logic that should be
used, clearly distinguishing the role of GROUP BY and HAVING in this context.
Answer

To produce the department-wise average CGPA report, SQL must first group student records by department
using GROUP BY Department. This creates one result group for each department. Then the AVG(CGPA)
function calculates the average within each group. However, the requirement also says that only departments
with average CGPA above 7.50 should be shown. That condition is applied after aggregation, so it belongs in
the HAVING clause, not the WHERE clause.
A suitable query is:
SELECT Department, AVG(CGPA) AS AvgCGPA
FROM Students
GROUP BY Department
HAVING AVG(CGPA) > 7.50;
Thus, GROUP BY forms the summary groups, and HAVING filters those groups based on aggregated values.
This distinction is essential in SQL reporting logic.

Question 19
Source: SET_1 | Original Question 10 | SECTION B

Explain joins (any three types) with examples.

Answer

Joins combine rows from related tables based on matching columns. Three common types are INNER JOIN,
LEFT JOIN, and RIGHT JOIN. INNER JOIN returns only matching records from both tables. For example, joining
Students and Placements on StudentID returns only students who have placement records. LEFT JOIN returns
all rows from the left table and matching rows from the right table; unmatched right-side values appear as
NULL. This is useful for listing all students, including those without offers. RIGHT JOIN works similarly but
preserves all rows from the right table.
Example INNER JOIN:
SELECT [Link], [Link]
FROM Students s
INNER JOIN Placements p ON [Link] = [Link];
Joins are fundamental because business information is usually distributed across related tables rather than
stored in one place.

Question 20
Source: SET_2 | Original Question 10 | SECTION B

A company wants to analyse applicants who received offers from more than one recruiter. Explain how
nested queries or joins can be used to answer this problem, and discuss when one approach may be
preferred over the other.
7
Answer

Applicants who received offers from more than one recruiter can be identified either through nested queries or
joins. A simple nested-query approach is to group offer records by applicant and filter those having a recruiter
count greater than one. For example:
SELECT ApplicantID
FROM Offers
GROUP BY ApplicantID
HAVING COUNT(DISTINCT RecruiterID) > 1;
This result can then be used in an outer query to retrieve applicant names. A join-based approach can
combine Applicants and Offers first, then apply grouping and counting. Joins are often preferred when the final
report needs columns from multiple tables and when the logic should remain in one query block. Nested
queries may feel clearer for stepwise reasoning. In practice, the better approach depends on readability,
complexity, and performance requirements.

Question 21
Source: SET_1 | Original Question 11 | SECTION C

Scenario: “Campus Placement Management System”

A college wants to maintain a database for campus placements. The system must store:
1. Students (StudentID, Name, Department, CGPA, Email)
2. Companies (CompanyID, CompanyName, Domain, MinCGPA)
3. Applications (ApplicationID, StudentID, CompanyID, ApplyDate, Status)
4. Interviews (InterviewID, ApplicationID, RoundNo, InterviewDate, Result)
Rules:
1. A student can apply to many companies; a company can receive many applications.
2. A student can apply only if [Link] >= [Link].
3. If an interview result becomes “Selected”, the application status should automatically become
“Selected”.
4. The placement cell wants a view that shows: StudentName, Department, CompanyName, Status.
Using the given scenario, draw an ER diagram clearly showing:
1. Entities, attributes (key attributes must be marked), and entity sets
2. Relationships and relationship sets with cardinality (1:1, 1:M, M:N)
3. Mention at least three integrity constraints applicable to this system.
Answer

For the Campus Placement Management System, the ER design should include four main entities: Students,
Companies, Applications, and Interviews.
Entities and key attributes:
Students(**StudentID**, Name, Department, CGPA, Email)
Companies(**CompanyID**, CompanyName, Domain, MinCGPA)
Applications(**ApplicationID**, StudentID, CompanyID, ApplyDate, Status)
Interviews(**InterviewID**, ApplicationID, RoundNo, InterviewDate, Result)

8
Relationships and cardinality:
1. A Student applies to many Companies through Applications. Since one student can apply to many
companies and one company can receive many applications, Student and Company have an M:N relationship
resolved through the Applications entity.
2. One Application can have many Interviews across rounds, so Application to Interview is 1:M.
3. Each Interview belongs to exactly one Application.
A textual ER representation is:
Student 1..* — Applications — *..1 Company
Application 1..* — Interviews
Three important integrity constraints are:
1. Entity integrity: StudentID, CompanyID, ApplicationID, and InterviewID must be unique and not NULL.
2. Referential integrity: [Link] must exist in Students, [Link] must exist in
Companies, and [Link] must exist in Applications.
3. Business constraint: a student can apply only when [Link] >= [Link].
Additional constraints may include valid status values and a trigger so that if [Link] becomes
“Selected”, then [Link] is automatically updated to “Selected”.
The requested view can be created by joining Students, Applications, and Companies to show StudentName,
Department, CompanyName, and Status. Overall, the model captures the full placement process in a
normalized and manageable way.

Question 22
Source: SET_2 | Original Question 11 | SECTION C

A placement coordinator at Mount Carmel College wants to analyse the following two tables to support
internship and final placement decisions:
Students(StudentID, FullName, Department, Program, Year, CGPA, City)
Placements(PlacementID, StudentID, Company, Role, PackageLPA, OfferDate, JoiningDate, Status,
Location, OfferType)
Write SQL queries for the following requirements:
(a) Retrieve the FullName, Department, Company, and Role of students who have accepted an offer and
whose CGPA is greater than 8.00.
(b) Display each Department along with the number of students who have received at least one offer.
Arrange the output in descending order of the count.
(c) Find the Company names that have made offers to students from the Business Analytics department
only.
(d) List the FullName and Company of students who have an offer record but whose JoiningDate is NULL.
Answer

The required SQL queries can be written as follows.

(a) Students who accepted an offer and have CGPA greater than 8.00:
SELECT [Link], [Link], [Link], [Link]
FROM Students s
INNER JOIN Placements p ON [Link] = [Link]
WHERE [Link] = 'Accepted'

9
 AND [Link] > 8.00;

This query joins the two tables through StudentID, filters only accepted offers, and keeps only high-CGPA
students.
(b) Department-wise count of students who received at least one offer:
SELECT [Link], COUNT(DISTINCT [Link]) AS OfferStudentCount
FROM Students s
INNER JOIN Placements p ON [Link] = [Link]
GROUP BY [Link]
ORDER BY OfferStudentCount DESC;
COUNT(DISTINCT [Link]) avoids double counting when a student has multiple offers.
(c) Companies that made offers to students from the Business Analytics department only:
SELECT DISTINCT [Link]
FROM Placements p
WHERE [Link] IN (
SELECT [Link]
FROM Placements p1
INNER JOIN Students s1 ON [Link] = [Link]
GROUP BY [Link]
HAVING SUM(CASE WHEN [Link] <> 'Business Analytics' THEN 1 ELSE 0 END) = 0
)
AND [Link] IN (
SELECT [Link]
FROM Placements p2
INNER JOIN Students s2 ON [Link] = [Link]
WHERE [Link] = 'Business Analytics'
);
This logic ensures the company has at least one Business Analytics offer and no offers to any other
department.
(d) Students with an offer record but no joining date yet:
SELECT [Link], [Link]
FROM Students s
INNER JOIN Placements p ON [Link] = [Link]
WHERE [Link] IS NULL;
This query uses IS NULL, which is the correct SQL way to test missing joining dates. Together, these queries
support operational placement review, department-level reporting, and exception tracking.

10