Topic MCQs — DBMS & SQL

30 questions · DSSSB TGT CS style · Original

---

Q1. A major advantage of DBMS over flat files is:

• A) More uncontrolled redundancy
• B) Better data integrity and controlled sharing
• C) No security possible
• D) Queries must be binary only Answer: B
  DBMS centralizes control, constraints, and concurrency.

Q2. In the relational model, a row is called a:

• A) Attribute
• B) Tuple
• C) Domain
• D) Degree Answer: B
  Tuple = row; attribute = column.

Q3. Degree of a relation means:

• A) Number of tuples
• B) Number of attributes
• C) Number of databases
• D) Disk block size Answer: B
  Degree = column count; cardinality = row count.

Q4. A candidate key is:

• A) Any super key with extra unused attributes always
• B) A minimal super key
• C) Always a foreign key
• D) Never unique Answer: B
  Minimal set that uniquely identifies tuples.

Q5. Primary key must be:

• A) Nullable and duplicate
• B) Unique and not NULL (chosen candidate)
• C) Only floating point
• D) Always composite Answer: B
  One chosen candidate key; uniqueness + NOT NULL.

Q6. Foreign key is used to:

• A) Speed CPU clock
• B) Enforce referential integrity between relations
• C) Normalize to 5NF automatically
• D) Encrypt passwords only Answer: B
  FK references PK/unique key of another (or same) table.

Q7. 1NF requires:

• A) Transitive dependencies only
• B) Atomic attribute values / no repeating groups
• C) All keys composite
• D) No primary key Answer: B
  First normal form = atomic columns.

Q8. 2NF is violated by:

• A) Partial dependency on part of a composite primary key
• B) Having a primary key
• C) Using SQL
• D) Creating indexes Answer: A
  2NF = 1NF + no partial dependency.

Q9. BCNF compared to 3NF is:

• A) Weaker
• B) Stricter (every determinant a super key)
• C) Unrelated to FDs
• D) Only about file systems Answer: B
  BCNF is a stricter form than 3NF.

Q10. ACID “Atomicity” means:

• A) Transaction is all-or-nothing
• B) Data is never indexed
• C) Only one user ever
• D) Schema never changes Answer: A
  Either full commit or full rollback.

Q11. CREATE TABLE is:

• A) DML
• B) DDL
• C) TCL
• D) DCL Answer: B
  DDL defines/alters structure.

Q12. GRANT and REVOKE belong to:

• A) DML
• B) DCL
• C) TCL
• D) DDL only always Answer: B
  Data Control Language for privileges.

Q13. COMMIT is:

• A) DDL
• B) TCL
• C) DCL
• D) A join type Answer: B
  Transaction Control — make changes permanent.

Q14. SELECT * FROM T WHERE x>5 filters rows using:

• A) HAVING only
• B) WHERE
• C) GROUP BY only
• D) ORDER BY only Answer: B
  WHERE filters before grouping.

Q15. HAVING is used to:

• A) Filter groups after GROUP BY
• B) Create tables
• C) Drop indexes only
• D) Rename databases only Answer: A
  HAVING works on aggregated groups.

Q16. INNER JOIN returns:

• A) All rows from both tables always
• B) Only matching rows from both sides
• C) Only left table rows
• D) Cartesian product only Answer: B
  Matches on join condition.

Q17. LEFT OUTER JOIN returns:

• A) Only right unmatched rows
• B) All left rows plus matches (NULL if no match)
• C) No NULLs ever
• D) Only aggregated rows Answer: B
  Preserves left table.

Q18. Which is an aggregate function?

• A) SUBSTRING
• B) COUNT
• C) CONCAT
• D) ALTER Answer: B
  COUNT/SUM/AVG/MIN/MAX aggregate.

Q19. In MySQL, CONCAT('A','B') returns:

• A) Error always
• B) 'AB'
• C) 2
• D) NULL always Answer: B
  String concatenation.

Q20. NOW() in MySQL typically returns:

• A) Only the year
• B) Current date and time
• C) Table list
• D) User password Answer: B
  Current timestamp.

Q21. A view is:

• A) Always a physical copy of all base rows stored twice
• B) A virtual table defined by a query
• C) A type of deadlock
• D) A page replacement algo Answer: B
  Stored query definition (materialized views aside).

Q22. An index is mainly used to:

• A) Slow down all SELECTs intentionally
• B) Speed up data retrieval at some write/storage cost
• C) Replace normalization
• D) Enforce ACID alone Answer: B
  Auxiliary structure for faster lookup.

Q23. In ER diagrams, a diamond typically represents:

• A) Attribute
• B) Entity
• C) Relationship
• D) Primary key underline only Answer: C
  Rectangle=entity, ellipse=attribute, diamond=relationship.

Q24. Multivalued attribute in ER is often shown as:

• A) Diamond
• B) Double ellipse
• C) Cylinder
• D) Cube Answer: B
  Double oval/ellipse for multivalued.

Q25. DELETE FROM Emp; without WHERE:

• A) Drops the Emp table structure
• B) Removes all rows but keeps table
• C) Creates a new schema
• D) Grants privileges Answer: B
  DELETE removes rows; DROP removes object.

Q26. Alternate key means:

• A) Foreign key only
• B) Candidate key not chosen as primary
• C) Super key with all attributes always
• D) Index name Answer: B
  Remaining candidate keys after PK choice.

Q27. Cardinality of a relation is:

• A) Number of columns
• B) Number of rows
• C) Number of indexes
• D) Size of domain alphabet Answer: B
  Row count.

Q28. Which command changes existing table structure?

• A) UPDATE
• B) ALTER TABLE
• C) INSERT
• D) SELECT Answer: B
  ALTER is DDL for structure changes.

Q29. Durability in ACID ensures:

• A) Uncommitted data is visible to all
• B) Committed data survives system failure
• C) No transactions allowed
• D) Keys are optional Answer: B
  Once committed, changes persist.

Q30. Transitive dependency is mainly removed to achieve:

• A) 1NF only
• B) 3NF (from 2NF)
• C) Only physical clustering
• D) Round-robin scheduling Answer: B
  3NF addresses non-key → non-key transitive FDs.