StudyFox
कीमतेंब्लॉगएक्सटेंशनमुफ़्त टूल
  • कीमतें
  • ब्लॉग
  • एक्सटेंशन
  • मुफ़्त टूल
  • शुरू करेंसाइन इन
StudyFox

अपने नोट्स, PDF और स्लाइड्स को फ़्लैशकार्ड, क्विज़, नोट्स और माइंड मैप में बदलें।

StudyFox खोलें

स्टडी टूल्स

  • होमवर्क हेल्पर
  • नोट मेकर
  • फ़्लैशकार्ड मेकर
  • क्विज़ मेकर
  • माइंड मैप मेकर

प्रोडक्ट

  • कीमतें
  • Chrome एक्सटेंशन
  • मुफ़्त एक्सटेंशन
  • स्टडी सेट देखें
  • ब्लॉग

सहायता

  • सहायता केंद्र
  • संपर्क करें
  • गोपनीयता नीति
  • सेवा की शर्तें

तुलना करें

  • Chegg का विकल्प
  • Course Hero का विकल्प
  • Coursology का विकल्प
  • Photomath का विकल्प
  • Gauthmath का विकल्प
  • PhotoSolve का विकल्प
  • CollegeTools का विकल्प
  • Transcript का विकल्प
  • SmartSolve का विकल्प
  • Homeworkify का विकल्प
  • AnswersAi का विकल्प
  • StudyX का विकल्प
  • Quizlet का विकल्प
  • Anki का विकल्प
  • Knowt का विकल्प
  • Brainscape का विकल्प
  • सभी तुलनाएँ

© 2026 StudyFox AI. सर्वाधिकार सुरक्षित।

  • X
  • Discord
  • Instagram
ExploreEngineering & TechData Science & AISQL Database Design, Indexes and Transactions

Quiz · Medium

SQL Database Design, Indexes and Transactions

Normal forms, keys, indexes and the leftmost-prefix rule, isolation levels, SQL injection, NULL logic, star schemas and views.

by Kill h.17 questionsCreated Nov 20, 2026, 08:27 PM
Data Science & AI· SQLTags#sql#database-design#indexes#transactions

17 questions

  1. 1

    A table stores order_id, customer_id, customer_city, where customer_city depends only on customer_id. Which normal form does this violate?

    • A

      First normal form

    • B

      Second normal form

    • Third normal form (transitive dependency)

    • D

      It violates none: it is fully normalized

    customer_city depends on customer_id, which is a non-key column, so order_id -> customer_id -> customer_city is a transitive dependency (3NF violation). Move customer data to its own table.

  2. 2

    A composite index exists on (last_name, first_name). Which query can use it most effectively?

    • WHERE last_name = 'Rossi'

    • B

      WHERE first_name = 'Marco'

    • C

      WHERE LOWER(last_name) = 'rossi'

    • D

      WHERE last_name LIKE '%ssi'

    A composite B-tree index is usable for the leftmost prefix. Filtering only on first_name skips the leading column; wrapping the column in LOWER() or starting a LIKE pattern with % prevents an index seek.

  3. 3

    Why does WHERE YEAR(order_date) = 2025 often ignore an index on order_date?

    • A

      Indexes only work on text columns

    • B

      YEAR() returns NULL for indexed columns

    • C

      Indexes cannot be used with equality comparisons

    • The function on the column prevents using the sorted index order

    The index stores order_date values, not YEAR(order_date). Rewrite as a range: order_date >= '2025-01-01' AND order_date < '2026-01-01' to allow an index range scan.

  4. 4

    Which isolation level is the default in PostgreSQL?

    • A

      READ UNCOMMITTED

    • READ COMMITTED

    • C

      REPEATABLE READ

    • D

      SERIALIZABLE

    PostgreSQL defaults to READ COMMITTED (each statement sees data committed before it started). MySQL InnoDB defaults to REPEATABLE READ, and PostgreSQL does not implement a true READ UNCOMMITTED.

  5. 5

    Transaction 1 runs the same SELECT ... WHERE amount > 100 twice. Between the two runs, transaction 2 inserts and commits a new row with amount 150, and the second run returns an extra row. What is this anomaly called?

    • Phantom read

    • B

      Dirty read

    • C

      Lost update

    • D

      Non-repeatable read of the same row

    A phantom read is a change in the SET of rows matching a condition. A non-repeatable read means the same existing row has a different value on re-read. A dirty read means seeing uncommitted data.

  6. 6

    A web app builds "SELECT * FROM users WHERE name = '" + name + "'". What is the best fix for SQL injection?

    • A

      Remove all quotes from the input by hand

    • B

      Convert the input to lowercase

    • C

      Use SELECT DISTINCT

    • Use parameterized queries with placeholders

    Placeholders send the SQL and the values separately, so input can never change the query structure. Manual escaping and filtering are incomplete defenses.

  7. 7

    In a star schema, what does one row of the fact table represent?

    • A

      One descriptive attribute value such as a product category

    • One event at the declared grain (for example one order line), with numeric measures and dimension keys

    • C

      A copy of every dimension row

    • D

      One database user

    The grain defines what a fact row is. Dimensions hold the descriptive context. Declaring the grain first prevents double-counting measures.

  8. 8

    What does SELECT * FROM t WHERE NULL = NULL return?

    • A

      All rows

    • B

      An error

    • No rows

    • D

      Rows where the column is NULL

    NULL = NULL evaluates to UNKNOWN, and WHERE keeps only rows where the condition is TRUE. Use IS NULL to test for NULL.

  9. 9

    NULL OR TRUE in SQL evaluates to:

    • A

      NULL (unknown)

    • B

      FALSE

    • TRUE

    • D

      An error

    Three-valued logic: if either operand of OR is TRUE, the result is TRUE regardless of the unknown. NULL AND FALSE is FALSE for the same reason, while NULL AND TRUE is UNKNOWN.

  10. 10

    An orders table has a foreign key to customers declared with ON DELETE CASCADE. What happens when a customer row is deleted?

    • The customer's orders are deleted automatically

    • B

      The delete is blocked while orders exist

    • C

      The orders' customer_id is set to NULL

    • D

      The orders are kept unchanged and become orphans

    CASCADE propagates the delete to child rows. RESTRICT/NO ACTION blocks the delete; SET NULL clears the foreign key. Orphan rows are exactly what foreign keys prevent.

  11. 11

    Which type is best for storing prices that must add up exactly?

    • A

      FLOAT

    • B

      DOUBLE PRECISION

    • C

      REAL

    • NUMERIC(12,2) / DECIMAL

    Binary floating-point types cannot represent many decimal fractions exactly, so sums drift. Exact numeric types (or integer cents) avoid that.

  12. 12

    What is the main difference between a view and a materialized view?

    • A

      A view stores its result; a materialized view does not

    • A materialized view stores its result on disk and must be refreshed; a view runs its query each time

    • C

      Only a view can be indexed

    • D

      A materialized view is always updated instantly

    A plain view is a saved query. A materialized view trades freshness for speed: reads are fast, but data are as old as the last refresh.

  13. 13

    What best describes a covering index for a query?

    • An index containing every column the query needs, so the table need not be read

    • B

      An index on every column of the table

    • C

      A unique index on the primary key

    • D

      An index that covers all rows with NULL

    With all needed columns in the index, the engine can do an index-only scan. It is often built with INCLUDE columns.

  14. 14

    Which workload is OLAP?

    • A

      Inserting one new order when a customer checks out

    • B

      Updating a single user's email address

    • C

      Fetching one account by primary key

    • Aggregating millions of sales rows by month and region for a dashboard

    OLAP means large analytical scans and aggregations, usually in a star schema or column store. The other options are classic OLTP operations.

  15. 15

    Which statement about DELETE and TRUNCATE is accurate?

    • A

      TRUNCATE accepts a WHERE clause

    • DELETE can remove selected rows with WHERE; TRUNCATE empties the whole table quickly

    • C

      DELETE always resets identity counters

    • D

      TRUNCATE never needs permissions

    TRUNCATE removes all rows with minimal logging and has no WHERE clause. DELETE can target rows, fires row-level triggers and is logged per row.

  16. 16

    Which diagram shows an INNER JOIN?

    • A

      A

    • B

    • C

      C

    • D

      D

    Only the overlap of A and B is shaded in diagram B. The others show left, full and right joins. Images: GermanX, CC BY-SA 4.0, via Wikimedia Commons.

  17. 17

    What data structure do most relational indexes use, supporting both equality and range lookups?

    • A

      Hash table only

    • B

      Linked list

    • B-tree

    • D

      Bloom filter

    B-trees keep keys sorted in a balanced tree, giving O(log n) lookups and efficient range scans. Hash indexes support only equality.