DBMS Practice Quiz (37 questions)

All questions and answers

1. Which statements accurately distinguish a database from a DBMS?

A database is managed data, while a DBMS is a software system.
A database is an organized collection of related data.
A DBMS is the organized collection of related information itself.
A DBMS is software that can create and retrieve database contents.
A database is the software that controls access to stored information.

A database is managed data, while a DBMS is a software system. · A database is an organized collection of related data. · A DBMS is software that can create and retrieve database contents.

Explanation

A database is an organized collection of related data, and a DBMS is software used to manage it. The DBMS is not the data collection itself, and the database is not the access-control software.

2. A database architect is assigning responsibilities across the three-schema architecture. Which statements are correct?

The architecture uses one external schema and multiple conceptual schemas.
The internal schema describes data attributes and relationships, rather than physical storage.
Different users may receive different external views of the database.
The conceptual schema presents only the portion required by one particular application.
The architecture includes external schemas, one conceptual schema, and an internal schema.

Different users may receive different external views of the database. · The architecture includes external schemas, one conceptual schema, and an internal schema.

Explanation

The three-schema architecture has external schemas, one conceptual schema, and an internal schema; users may have different external views. The conceptual schema describes the overall logical structure, while the internal schema concerns physical storage.

3. An administrator adds an index without changing the logical schema, then considers changing the conceptual schema while preserving existing views. Which statements apply?

Data independence means changing a schema level without requiring changes at the next higher level.
Adding an index without changing the logical schema is physical data independence.
Changing the conceptual schema while preserving views is physical data independence.
Logical data independence is generally easier to achieve than physical data independence.
Logical data independence concerns changing internal storage while keeping applications unchanged.

Data independence means changing a schema level without requiring changes at the next higher level. · Adding an index without changing the logical schema is physical data independence.

Explanation

Adding an index is an internal storage change that exemplifies physical data independence. Data independence permits a change at one schema level without requiring changes at the next higher level; conceptual changes that preserve views are logical independence, which is generally harder to achieve.

4. Which statements correctly characterize the principal data models?

The hierarchical model arranges data as a tree of parent-child relationships.
The relational model organizes data into relations composed of rows and columns.
The relational model represents data as a graph whose records may have multiple parents.
The network model represents data as tables organized into rows and columns.
The ER model represents entities, attributes, and relationships for conceptual design.

The hierarchical model arranges data as a tree of parent-child relationships. · The relational model organizes data into relations composed of rows and columns. · The ER model represents entities, attributes, and relationships for conceptual design.

Explanation

Relations have rows and columns, the hierarchical model uses a parent-child tree, and the ER model supports conceptual design. Graphs with possible multiple parents describe the network model, while tables with rows and columns describe the relational model.

5. A company defines requirements for its operations and selects representations for its data. Which statements apply?

An object-relational model replaces relational tables with flexible XML documents.
A policy restricting how the company operates can help determine its database constraints.
A design using JSON documents exemplifies a semi-structured data model.
A rule requiring each invoice to have a unique identifier can guide database constraints.
Entities, attributes, relationships, constraints, and operations are model building blocks.

A policy restricting how the company operates can help determine its database constraints. · A design using JSON documents exemplifies a semi-structured data model. · A rule requiring each invoice to have a unique identifier can guide database constraints. · Entities, attributes, relationships, constraints, and operations are model building blocks.

Explanation

An organizational policy that restricts operations is a business rule and can inform database constraints; a unique invoice identifier is a suitable example. JSON documents are associated with semi-structured models, while the listed elements are data-model building blocks. Object-relational models combine relational tables with selected object-oriented features; flexible XML documents characterize semi-structured models.

6. Regarding transactions, business-rule implementation, and the evolution of data management, which statements are accurate?

A transaction is a logical unit of work containing one or more database operations.
A CHECK constraint can enforce a rule that marks fall within an allowed range.
A transaction is a single database operation that cannot contain several operations.
Data management evolved from file-processing systems through hierarchical and network databases.
Business rules are implemented through PRIMARY KEY constraints, but not application-level validation.

A transaction is a logical unit of work containing one or more database operations. · A CHECK constraint can enforce a rule that marks fall within an allowed range. · Data management evolved from file-processing systems through hierarchical and network databases.

Explanation

A transaction may group one or more database operations, and business rules can be enforced with CHECK constraints. The broad evolution began with file-processing systems and proceeded through hierarchical and network databases. A transaction need not contain just one operation, and application-level validation is also a way to implement business rules.

7. During a database operation, a failure occurs before changes are committed. Which statements describe transaction management and ACID behavior?

Transaction management allows failures to leave the database in an invalid state.
COMMIT undoes uncommitted changes and restores the prior database state.
Durability means committed changes may disappear after a system failure.
Atomicity requires the operations in a transaction to succeed together or have none take effect.
ROLLBACK undoes changes that have not been committed.

Atomicity requires the operations in a transaction to succeed together or have none take effect. · ROLLBACK undoes changes that have not been committed.

Explanation

Atomicity means all transaction operations take effect together or none do, and ROLLBACK undoes uncommitted changes. Transaction management is intended to prevent failures from leaving invalid states; durability means committed changes survive failures, while COMMIT makes changes permanent.

8. A team has gathered requirements and business rules for a new database. Which statements describe its design process and entities?

The team can next create a conceptual design using an ER diagram.
A distinguishable real-world object, such as a course, can be modeled as an entity.
The team should normalize relations before creating a conceptual ER design.
A database entity represents a collection of similar real-world objects rather than one object.
The team should implement SQL tables before converting entities and relationships into tables.

The team can next create a conceptual design using an ER diagram. · A distinguishable real-world object, such as a course, can be modeled as an entity.

Explanation

After gathering requirements and business rules, the process creates a conceptual ER design; a course is an example of a distinguishable entity. Normalization follows conversion to relational tables, and SQL implementation follows that conversion. A collection of similar entities is an entity set, not a single entity.

9. In an ER design for a school, which statements correctly distinguish attributes from entity sets?

An attribute is a collection of similar real-world entities.
StudentID can be modeled as an attribute describing a student entity.
Name is an entity set because it identifies a collection of students.
An entity set is a property that describes a single student.
A collection of similar student entities forms an entity set.

StudentID can be modeled as an attribute describing a student entity. · A collection of similar student entities forms an entity set.

Explanation

StudentID and Name are examples of attributes that describe entities, while a collection of similar students is an entity set. An entity set is not a single entity property, and an attribute is not a collection of entities.

10. An ER diagram models students enrolling in courses and records several kinds of student information. Which statements are correct?

The enrollment association is an attribute describing a student entity.
A student’s phone numbers can be represented as a multivalued attribute.
An address is simple because it cannot be divided into meaningful components.
An attribute calculated from another value, such as age from birth date, is single-valued by definition.
The enrollment association between a student and a course is a relationship.

A student’s phone numbers can be represented as a multivalued attribute. · The enrollment association between a student and a course is a relationship.

Explanation

Enrollment associates student and course entities, so it is a relationship; phone numbers are an example of a multivalued attribute. An address is composite because it can be divided into components, and an attribute derived from other data is classified as derived, not defined as single-valued.

11. Which statements correctly describe common ER diagram symbols?

A rectangle represents an entity, while an oval represents an attribute.
An underlined attribute denotes a key, while a double rectangle denotes a weak entity.
A double diamond denotes a weak entity, while a double rectangle denotes an identifying relationship.
A diamond represents an entity, while a rectangle represents a relationship.
An oval represents a relationship, while a diamond represents an attribute.

A rectangle represents an entity, while an oval represents an attribute. · An underlined attribute denotes a key, while a double rectangle denotes a weak entity.

Explanation

A rectangle denotes an entity and an oval denotes an attribute; a diamond instead denotes a relationship. An underlined attribute marks a key, while a double rectangle represents a weak entity; a double diamond represents an identifying relationship.

12. A dependent record cannot be identified by its own attributes without reference to an employee. Which statements characterize this weak entity?

It has a partial key that contributes to identifying its instances.
It can be uniquely identified without using information from its owner.
It is independent of an owner entity when a partial key exists.
Its own attributes alone uniquely identify each instance.
It depends on an owner strong entity for identification.

It has a partial key that contributes to identifying its instances. · It depends on an owner strong entity for identification.

Explanation

A weak entity depends on an owner strong entity and has a partial key that contributes to identification. Its own attributes alone are insufficient, so a partial key does not make it independent or remove the need for the owner.

13. In STUDENT(StudentID, Email, Name), both StudentID and Email are unique, and StudentID is selected as the primary key. Which statements follow?

A super key must be minimal, with no extra identifying attributes.
StudentID and Email may each qualify as candidate keys.
Email is an alternate key because it is a candidate key not selected as primary.
A composite key consists of a single attribute that uniquely identifies a row.
StudentID is the primary key because it is the selected candidate key.

StudentID and Email may each qualify as candidate keys. · Email is an alternate key because it is a candidate key not selected as primary. · StudentID is the primary key because it is the selected candidate key.

Explanation

Because StudentID and Email are each unique, both may be candidate keys; selecting StudentID makes it the primary key and Email an alternate key. A super key may contain extra attributes, whereas a candidate key is minimal, and a composite key has two or more attributes.

14. A database designer is defining value and relationship rules for several columns. Which statements correctly describe the constraints?

A CHECK constraint can require a mark to fall between 0 and 100.
A UNIQUE constraint requires every foreign-key value to match a referenced key.
A DEFAULT constraint supplies a value when an insert omits that column.
A domain constraint limits an attribute to values in its permitted type, range, or set.
A primary key subject to entity integrity cannot contain a NULL value.

A CHECK constraint can require a mark to fall between 0 and 100. · A DEFAULT constraint supplies a value when an insert omits that column. · A domain constraint limits an attribute to values in its permitted type, range, or set. · A primary key subject to entity integrity cannot contain a NULL value.

Explanation

Domain constraints restrict values to an allowed domain, and CHECK can enforce a condition such as a mark range. Entity integrity bars NULL primary-key values, while DEFAULT supplies an omitted value; matching a referenced key is referential integrity, not UNIQUE.

15. An employee relation has EmpID → DeptID and DeptID → DeptName. Which statements correctly describe its normalization?

In the enrollment example, StudentName depends on the composite StudentID–CourseID key as a whole.
Third normal form permits every non-trivial dependency when its determinant is a prime attribute.
Splitting into EMPLOYEE(EmpID, DeptID) and DEPARTMENT(DeptID, DeptName) removes that transitive dependency.
DeptName is transitively dependent on EmpID through DeptID in this relation.
The employee decomposition places DeptName in EMPLOYEE alongside EmpID and DeptID.

Splitting into EMPLOYEE(EmpID, DeptID) and DEPARTMENT(DeptID, DeptName) removes that transitive dependency. · DeptName is transitively dependent on EmpID through DeptID in this relation.

Explanation

EmpID determines DeptID, which determines DeptName, so DeptName is transitively dependent on EmpID; separating the employee and department facts removes that dependency. The 3NF test requires a superkey determinant or a prime dependent attribute, and the enrollment example has partial, not whole-key, dependencies.

16. A decomposition is being evaluated for BCNF and the quality of the resulting tables. Which statements are correct?

A lossless join may produce extra tuples beyond those in the original relation.
For every non-trivial functional dependency in BCNF, the determinant must be a superkey.
BCNF requires the dependent attribute, rather than the determinant, to be a superkey.
A lossless decomposition joins back to recreate exactly the original relation.
BCNF is less restrictive than third normal form.

For every non-trivial functional dependency in BCNF, the determinant must be a superkey. · A lossless decomposition joins back to recreate exactly the original relation.

Explanation

BCNF requires every determinant of a non-trivial functional dependency to be a superkey. A lossless decomposition reconstructs exactly the original relation; BCNF is stricter than 3NF, and extra tuples would violate losslessness.

17. A system is assessed against Codd’s relational rules. Which statement correctly describes Rule 0?

Rule 0 guarantees access to each atomic value by table name, primary key, and column name.
Rule 0 requires the system to manage the database entirely through its relational capabilities.
Rule 0 requires missing or inapplicable information to receive consistent treatment.
Rule 0 requires all information to be represented as values in tables.
Rule 0 requires relational metadata to be represented in a queryable catalog.

Rule 0 requires the system to manage the database entirely through its relational capabilities.

Explanation

Rule 0 requires database management to occur entirely through the system’s relational capabilities. Representing information as table values is the Information rule, while guaranteed access, NULL treatment, and a queryable catalog are separate rules.

18. A database analyst projects Name and Dept from STUDENT, compares relations R and S, renames a relation, and forms a product; which statements are correct?

Applying ρ S(STUDENT) renames the STUDENT relation to S.
The difference R − S contains rows present in R but absent from S.
A Cartesian product pairs every row of one relation with every row of another.
Projecting Name and Dept returns those columns, with duplicate rows removed in classical algebra.
Projection selects columns, whereas selection is the operator that filters rows by conditions.

Applying ρ S(STUDENT) renames the STUDENT relation to S. · The difference R − S contains rows present in R but absent from S. · A Cartesian product pairs every row of one relation with every row of another. · Projecting Name and Dept returns those columns, with duplicate rows removed in classical algebra. · Projection selects columns, whereas selection is the operator that filters rows by conditions.

Explanation

Projection selects columns and classical relational algebra removes duplicate rows; selection instead filters rows. Set difference keeps rows in R but not S, rename can give STUDENT the name S, and Cartesian product forms every pair of rows.

19. Relations R and S are being considered for set operations; which statements correctly describe their compatibility and intersection?

Union-compatible relations have the same number of attributes and compatible corresponding domains.
The intersection R ∩ S returns tuples found in either R or S.
Set difference requires compatible attribute domains in corresponding positions.
Relations with different attribute counts can be union-compatible if their domains match.
The intersection R ∩ S consists of tuples that occur in both relations.

Union-compatible relations have the same number of attributes and compatible corresponding domains. · Set difference requires compatible attribute domains in corresponding positions. · The intersection R ∩ S consists of tuples that occur in both relations.

Explanation

Union, intersection, and difference require equal attribute counts and compatible corresponding domains. Intersection contains tuples common to both relations; different attribute counts do not meet the compatibility requirement, and tuples occurring in either relation describe union rather than intersection.

20. A query joins two relations using a condition, with some rows lacking matches; which statements correctly distinguish the join types?

An inner join retains unmatched left rows and fills their right-side attributes with NULL.
A theta join can use a general condition, while an equijoin uses equality.
A natural join matches same-named attributes and removes duplicate join columns.
A full outer join retains matched rows and unmatched rows from both relations.
A join combines related tuples from two relations.

A theta join can use a general condition, while an equijoin uses equality. · A natural join matches same-named attributes and removes duplicate join columns. · A full outer join retains matched rows and unmatched rows from both relations. · A join combines related tuples from two relations.

Explanation

Theta joins allow general conditions, equijoins use equality, and natural joins match same-named attributes while removing duplicate join columns. Joins combine related tuples; a full outer join retains unmatched rows from both sides, whereas unmatched left rows with NULL right-side attributes are retained by a left outer join, not an inner join.

21. A reporting team needs every required course per student, a complete pairing of two tables, and grouped aggregates; which statements are correct?

Grouping can calculate aggregates such as COUNT, SUM, or AVG for tuples sharing grouping values.
A cross join pairs every row on the left with every row on the right.
Division of ENROLLED(StudentID, CourseID) by REQUIRED(CourseID) returns students enrolled in every required course.
Division returns students associated with at least one course in REQUIRED.
Extended relational algebra often represents grouping with the γ operator.

Grouping can calculate aggregates such as COUNT, SUM, or AVG for tuples sharing grouping values. · A cross join pairs every row on the left with every row on the right. · Division of ENROLLED(StudentID, CourseID) by REQUIRED(CourseID) returns students enrolled in every required course. · Extended relational algebra often represents grouping with the γ operator.

Explanation

Division expresses an “every required value” condition, so it returns students enrolled in every course listed in REQUIRED. Cross join forms all row pairs, and grouping collects tuples with common grouping values for aggregates such as COUNT, SUM, or AVG, often using γ; “at least one” does not describe division.

22. A data analyst groups student marks by department and later expands nested collections; which statements correctly describe these operations?

The notation used for ungrouping can vary across textbooks.
Grouping students by department with AVG computes a single overall average without department-specific results.
The expression γ Dept; AVG(Marks)→AverageMarks (STUDENT) calculates average marks for each department.
Ungrouping combines separate tuples into collection-valued attributes rather than expanding them.
Ungrouping expands collection-valued attributes into separate tuples.

The notation used for ungrouping can vary across textbooks. · The expression γ Dept; AVG(Marks)→AverageMarks (STUDENT) calculates average marks for each department. · Ungrouping expands collection-valued attributes into separate tuples.

Explanation

The γ expression groups by department and computes each department’s average marks. Ungrouping reverses grouping by expanding collection-valued attributes into separate tuples, and its exact notation varies; the stated overall average omits departmental grouping, while the reverse operation is described incorrectly.

23. A database user writes a predicate-based query for student records; which statements correctly describe relational calculus and TRC?

Relational calculus is procedural and specifies how to obtain results through an operation sequence.
Relational calculus describes the required data rather than a sequence of operations for obtaining it.
In TRC, variables represent whole tuples rather than individual attribute values.
The general TRC form { t | P(t) } denotes tuples for which predicate P(t) is true.
The expression { t | STUDENT(t) AND t.Marks > 70 } returns qualifying student tuples.

Relational calculus describes the required data rather than a sequence of operations for obtaining it. · In TRC, variables represent whole tuples rather than individual attribute values. · The general TRC form { t | P(t) } denotes tuples for which predicate P(t) is true. · The expression { t | STUDENT(t) AND t.Marks > 70 } returns qualifying student tuples.

Explanation

Relational calculus is non-procedural: it states what data is required, unlike relational algebra’s operation sequence. TRC variables represent whole tuples, and its general form selects tuples satisfying a predicate; the example therefore returns student tuples with marks above 70.

24. Which statements about Domain Relational Calculus (DRC) are accurate?

DRC expresses queries through algebraic operators such as selection and projection.
DRC variables represent individual attribute values rather than whole tuples.
The illustrated DRC query returns complete student tuples with marks above 70.
The illustrated DRC query returns names of students scoring below 70.
A DRC expression generally uses variables representing entire database tuples.

DRC variables represent individual attribute values rather than whole tuples.

Explanation

DRC variables stand for individual attribute values, not whole tuples. The example returns names for marks above 70; tuple results and algebraic operators describe different query forms, while the example does not select scores below 70.

25. A database team compares safe relational calculus with relational algebra. Which statements are correct?

Safe TRC and DRC express the same class of relational queries as relational algebra.
Relational calculus specifies an operational sequence for obtaining each result.
Safe relational calculus expresses a broader class of queries than relational algebra.
Relational algebra leaves its sequence of operations unspecified, like relational calculus.
A safe calculus query produces a finite result based on database values.

Safe TRC and DRC express the same class of relational queries as relational algebra. · A safe calculus query produces a finite result based on database values.

Explanation

Safe TRC and DRC have the same expressive power as relational algebra, and safety restricts results to finite sets based on database values. Algebra specifies its operational sequence; calculus does not, and safe calculus is not more expressive.

26. A table design must preserve valid, consistent data while enforcing types and unique keys. Which statements apply?

A key constraint requires values of the designated key to be unique.
Entity integrity requires a primary key value to be non-NULL.
Constraints restrict permitted stored data and help maintain validity and consistency.
A domain constraint can require values to have the correct type or range.
A key constraint permits repeated values for the designated key.

A key constraint requires values of the designated key to be unique. · Entity integrity requires a primary key value to be non-NULL. · Constraints restrict permitted stored data and help maintain validity and consistency. · A domain constraint can require values to have the correct type or range.

Explanation

Constraints limit stored data to help preserve validity and consistency; primary keys cannot be NULL, domain constraints govern type or range, and key values must be unique. Allowing duplicate designated key values contradicts the key constraint.

27. A student row omits a DEFAULTed field, has Marks of 105, and uses a nullable foreign key. Which statements apply?

A CHECK constraint supplies a missing value from its DEFAULT.
A foreign key may be NULL when the schema permits NULL values.
Referential integrity requires every foreign key to be non-NULL.
A DEFAULT supplies its designated value when an input is omitted.
A CHECK condition can enforce Marks between 0 and 100.

A foreign key may be NULL when the schema permits NULL values. · A DEFAULT supplies its designated value when an input is omitted. · A CHECK condition can enforce Marks between 0 and 100.

Explanation

Referential integrity allows a foreign key to be NULL when permitted, and a CHECK can enforce the 0–100 marks range. DEFAULT supplies an omitted value; CHECK tests a condition, so it does not provide the default, and a foreign key need not be non-NULL.

28. A reporting design uses a query-defined virtual table and considers refresh behavior and referential actions. Which statements are correct?

A conventional view stores its query definition instead of a separate result copy.
A view is a named query that behaves like a virtual table.
A materialized view stores query results and must be refreshed.
Foreign keys may use ON DELETE or ON UPDATE actions such as CASCADE.
An ordinary view automatically improves query performance for reports.

A conventional view stores its query definition instead of a separate result copy. · A view is a named query that behaves like a virtual table. · A materialized view stores query results and must be refreshed. · Foreign keys may use ON DELETE or ON UPDATE actions such as CASCADE.

Explanation

A view is a named query that behaves like a virtual table. A conventional view stores its query definition rather than a separate copy of the results, while a materialized view stores results and must be refreshed. Foreign keys may use actions such as CASCADE on deletion or update, subject to DBMS support and design needs. Ordinary views do not necessarily store results and do not automatically improve query performance.

29. A team publishes a view for restricted reporting and later changes an underlying table. Which statements describe likely view properties?

An ordinary view necessarily stores results and improves performance.
A view may break when the structure of an underlying table changes.
A view can restrict access to sensitive rows or columns.
Views can simplify complex queries and present customized data.
Some views involving aggregates or GROUP BY may not be updatable.

A view may break when the structure of an underlying table changes. · A view can restrict access to sensitive rows or columns. · Views can simplify complex queries and present customized data. · Some views involving aggregates or GROUP BY may not be updatable.

Explanation

Views can restrict sensitive data, simplify queries, provide customized presentations, and may be affected by underlying table structure changes. Views involving aggregates or GROUP BY may not be updatable; ordinary views do not necessarily store results or improve performance.

30. Regarding SQL command categories, which statements are correct?

SELECT and UPDATE are TCL commands for managing transactions.
CREATE and ALTER are DDL commands that define or change database structures.
COMMIT and ROLLBACK are DDL commands for changing database structures.
INSERT and DELETE are DML commands that modify table data.
GRANT and REVOKE are DCL commands used to control permissions.

CREATE and ALTER are DDL commands that define or change database structures. · INSERT and DELETE are DML commands that modify table data. · GRANT and REVOKE are DCL commands used to control permissions.

Explanation

CREATE and ALTER belong to DDL, while INSERT and DELETE are DML operations on table data. GRANT and REVOKE manage permissions; COMMIT and ROLLBACK are TCL, and SELECT and UPDATE are not TCL commands.

31. A Course table is being defined with the constraints and columns described below. Which statements are correct?

Title can be declared as VARCHAR(60) with a NOT NULL constraint.
Credits can be defined as INT with a CHECK constraint requiring values below zero.
CourseID can be declared as an INT column and designated as the primary key.
The INSERT example adds course 101 with title DBMS and four credits.
The example INSERT stores course 101 with a title of SQL and three credits.

Title can be declared as VARCHAR(60) with a NOT NULL constraint. · CourseID can be declared as an INT column and designated as the primary key. · The INSERT example adds course 101 with title DBMS and four credits.

Explanation

The table definition permits CourseID INT PRIMARY KEY and Title VARCHAR(60) NOT NULL; its Credits constraint is CHECK (Credits > 0). The INSERT example specifies ID 101, title DBMS, and 4 credits, not SQL and 3 credits.

32. An administrator plans a bulk change to a student table and must preserve its definition in one operation. Which statements are correct?

Checking the WHERE clause before UPDATE or DELETE helps prevent unintended changes to every row.
TRUNCATE removes all rows while retaining the table structure, subject to DBMS-specific details.
A SELECT with Marks >= 60 and ORDER BY Marks DESC returns qualifying marks from highest downward.
An UPDATE without a WHERE clause may change every row in the student table.
DROP removes all table rows while retaining the table definition for future inserts.

Checking the WHERE clause before UPDATE or DELETE helps prevent unintended changes to every row. · TRUNCATE removes all rows while retaining the table structure, subject to DBMS-specific details. · A SELECT with Marks >= 60 and ORDER BY Marks DESC returns qualifying marks from highest downward. · An UPDATE without a WHERE clause may change every row in the student table.

Explanation

Without WHERE, UPDATE may affect every row, so checking its filter is important; the same caution applies to DELETE. The stated SELECT filters at 60 or above and sorts descending, while TRUNCATE preserves structure and DROP removes both the definition and data.

33. A grouped report must exclude low-scoring rows before grouping and keep only groups whose average reaches 60. Which statements are correct?

WHERE filters individual rows before the grouping operation is performed.
COUNT(*) counts rows, including rows whose Marks value is NULL.
HAVING filters groups after GROUP BY has formed them.
SUM(Marks) counts every row, including rows where Marks is NULL.
HAVING filters individual rows before GROUP BY forms any groups.

WHERE filters individual rows before the grouping operation is performed. · COUNT(*) counts rows, including rows whose Marks value is NULL. · HAVING filters groups after GROUP BY has formed them.

Explanation

WHERE acts on rows before grouping, whereas HAVING filters groups after GROUP BY. COUNT(*) counts rows regardless of NULL values, but COUNT(Marks) counts only non-NULL Marks values. SUM(Marks) totals numeric values and does not count rows with NULL Marks.

34. A query must find records whose Marks value is missing. Which statements about NULL handling are correct?

The condition Marks IS NULL tests whether Marks has a NULL value.
SUM(Marks) includes NULL entries as zero-valued terms in its total.
COUNT(*) continues to count a row even when its Marks value is NULL.
The condition Marks = NULL is the correct comparison for finding missing marks.
NULL can represent information that is missing, unknown, or inapplicable.

The condition Marks IS NULL tests whether Marks has a NULL value. · COUNT(*) continues to count a row even when its Marks value is NULL. · NULL can represent information that is missing, unknown, or inapplicable.

Explanation

IS NULL is the appropriate test, and NULL represents missing, unknown, or inapplicable information rather than zero or an empty string. COUNT(*) still counts rows; Marks = NULL is incorrect, and aggregate functions generally ignore NULL values rather than treating them as zero.

35. Which statements correctly describe nested queries and their behavior?

A subquery supplies its result from the outer query to the inner SELECT statement.
A non-correlated subquery must be evaluated once for each row of the outer query.
Arithmetic involving NULL generally produces NULL, and SQL includes UNKNOWN in its logic.
A subquery is a SELECT statement nested inside another SQL statement.
A correlated subquery refers to a column from the outer query.

Arithmetic involving NULL generally produces NULL, and SQL includes UNKNOWN in its logic. · A subquery is a SELECT statement nested inside another SQL statement. · A correlated subquery refers to a column from the outer query.

Explanation

A subquery is a SELECT nested within another statement, and its result supplies data to the outer query. Arithmetic involving NULL generally yields NULL, while a correlated subquery references an outer-query column; a non-correlated one can execute independently, rather than once for each row of the outer query.

36. A comparison uses values returned by a subquery, and the query designer is also considering a NULL-prone exclusion test. Which statements are correct?

IN tests membership, while EXISTS tests whether at least one row exists.
A comparison using ANY is true when it holds for at least one returned value.
A scalar subquery is used when the query expects several values.
A comparison using ALL is true when it holds for at least one returned value.
NOT EXISTS can be safer than NOT IN when the subquery may contain NULLs.

IN tests membership, while EXISTS tests whether at least one row exists. · A comparison using ANY is true when it holds for at least one returned value. · NOT EXISTS can be safer than NOT IN when the subquery may contain NULLs.

Explanation

ANY requires the comparison to hold for at least one returned value, whereas ALL requires it to hold for every value. IN tests membership, EXISTS tests for a row, and NOT EXISTS can avoid NULL-related risks of NOT IN; scalar subqueries are used when one value is expected.

37. A database administrator needs to grant and later remove SELECT access on Student for user1. Which statements correctly describe the required access-control actions?

GRANT SELECT ON Student TO user1 gives user1 SELECT access to Student.
REVOKE SELECT ON Student TO user1 removes user1's SELECT access to Student.
REVOKE SELECT ON Student FROM user1 removes user1's SELECT access to Student.
GRANT SELECT ON Student FROM user1 gives user1 SELECT access to Student.
GRANT gives privileges, whereas REVOKE removes privileges from a user.

GRANT SELECT ON Student TO user1 gives user1 SELECT access to Student. · REVOKE SELECT ON Student FROM user1 removes user1's SELECT access to Student. · GRANT gives privileges, whereas REVOKE removes privileges from a user.

Explanation

GRANT with TO gives user1 SELECT access, while REVOKE with FROM removes it. Reversing those commands' effects or using the wrong recipient clause does not match the stated access-control actions.

Study with flashcards

Lock in the answers with 95 flashcards on DBMS.

What is a database?

An organized collection of related data.

What software defines, creates, stores, retrieves, updates, and controls access to a database?

A Database Management System (DBMS).

What does a DBMS do to reduce data problems?

It reduces unnecessary data redundancy and inconsistency.

See the flashcards →

Read the study guide

Go over the full study guide for DBMS.

Read the study guide →

Related classes

Make your own practice quizzes

Upload your class notes and Revizly writes a practice quiz with explained answers in about a minute.

Quiz maker