SQL Fundamentals final test: 25 free questions

Q1 / 25

A read-only analytical query preserves sorted worker output, but it calls a user-defined function with no parallel label. Parallel workers are otherwise available. What prevents the desired plan?

1. AGather Merge cannot be used for read-only queries
2. BAvailable workers force the leader to stop participating
3. CThe unlabeled function defaults to parallel unsafe
4. DSorted output requires a data-modifying CTE

Q2 / 25

An equality operator receives one non-null operand and one null operand. What does the expression yield?

1. AAn error, because null cannot be compared
2. BA plain false
3. CNull, signifying unknown
4. DTrue, because both sides are comparable

Q3 / 25

Which statement about the `RENAME` forms of `ALTER TABLE` is correct?

1. AThey may be listed alongside other alterations so that one pass over the table covers them all
2. BThey rewrite each row so that it records the new name of its table
3. CThey rename a parent table's column while leaving the descendant tables untouched
4. DA rename changes only the name, and a renamed constraint's index is renamed too

Q4 / 25

Which PostgreSQL JSON type preserves the exact input text, including insignificant whitespace and object-key order?

1. Ajsonb
2. Bjsonpath
3. Cjson
4. Dtext[]

Q5 / 25

A ledger column stores monetary amounts that must be summed without accumulated rounding drift, and equality tests on stored balances must behave predictably. Reporting speed is a secondary concern. Which type fits?

1. Anumeric, with a declared precision and scale
2. Bbigint, storing the amount as whole units
3. Cdouble precision, for its wider documented range
4. Dreal, for its smaller per-value storage cost

Q6 / 25

After writing `FROM customer_accounts AS ca`, which reference to that source is valid later in the same query?

1. A`account_id.customer_accounts`
2. B`ca.account_id` in the select list
3. C`customer_accounts.ca.account_id` in the same query
4. D`customer_accounts.account_id`

Q7 / 25

What comes after the central syntax and data-operation topics in the deeper documentation path?

1. AA guarantee that PostgreSQL extensions work unchanged in every engine
2. BRemoval of tables, clients, data types, and database connections
3. CReplacement of SQL with fixed row positions and server filenames
4. DIndexes, text search, concurrency, performance, and parallel query

Q8 / 25

A product and its referenced supplier are independent business objects, the supplier column is `NOT NULL`, and deleting a supplier must not delete products. Which action is suitable?

1. AUse `RESTRICT` or `NO ACTION` and handle both objects explicitly
2. BUse `SET NULL` so each product remains with a null supplier
3. CUse `CASCADE` so deleting the supplier also deletes every product
4. DUse `SET DEFAULT` without ensuring the default references a supplier

Q9 / 25

What must match before an indexed comparison can use a chosen index method?

1. AThe comparison must use every operator supported by the method
2. BThe comparison value must reference another column in the indexed table
3. CThe comparison operator must belong to the column's operator class
4. DThe index name must contain the comparison operator's text

Q10 / 25

An application repeatedly filters inside large JSON documents and does not need to preserve whitespace, key order, or duplicate object keys. Which storage choice best fits both requirements?

1. AStore the documents as jsonb and use its indexing support
2. BStore the documents as json to retain the original input representation
3. CStore each document as text and reparse it for every filter
4. DStore keys in an array so object order remains available

Q11 / 25

A value must contain the text Dianne's horse, use the regular SQL string form, and avoid PostgreSQL escape or dollar-quote extensions. Which expression meets all constraints?

1. A'Dianne''s horse'
2. B"Dianne's horse"
3. C$$Dianne's horse$$
4. DE'Dianne\'s horse'

Q12 / 25

What does scalar NOT IN return when its right-side subquery returns no rows?

1. ANULL, because every empty subquery is unknown
2. BAn error, because NOT IN requires one returned row
3. CFalse, because no right-side value confirms inequality
4. DTrue, because only unequal rows were found vacuously

Q13 / 25

A read-only report runs several queries that must all use one view of the data, concurrent commits must not appear midway, and the report does not require its result to participate in a serial business-rule outcome. Which least demanding PostgreSQL level fits?

1. ASerializable, because only dependency monitoring can keep query results unchanged
2. BRead Uncommitted, reading concurrent work before it commits
3. CRepeatable Read, using the database view fixed by its first query
4. DRead Committed, taking a new database view for every report query

Q14 / 25

Two joined sources both expose a column named `id`. What identifies the `id` from alias `c`?

1. A`c AS id`
2. B`id.c`
3. C`id FROM c.id`
4. D`c.id`

Q15 / 25

Which window function gives 1, 2, 3 and so on to the rows of a partition, handing a different number even to rows that tie?

1. Arank, which numbers rows from one and repeats a number for ties
2. Brow_number, which numbers the rows from one inside the partition
3. Cdense_rank, which numbers peer groups rather than individual rows
4. Dntile, which numbers the buckets the partition has been divided into

Q16 / 25

A learner must connect to an existing database, inspect current rows, and avoid confusing client help with a data request. Which sequence fits?

1. ARun psql for the database, issue SELECT with a semicolon, and use backslash h only for client help
2. BRename the cluster to psql, make a row the database, and query by closing the terminal
3. COpen a table as the client, omit command termination, and use DELETE to display syntax help
4. DCreate a server from SELECT, use INSERT as the client application, and treat every backslash command as database SQL

Q17 / 25

What does `UNIQUE (a, c)` require?

1. AEach value of `a` and each value of `c` must be unique separately
2. BEach combination of `a` and `c` must be unique
3. CAt least one of `a` or `c` must differ from every earlier row
4. DThey must be the table's primary key

Q18 / 25

Which statement defines a view named active_orders from a SELECT query?

1. ACREATE TABLE VIEW active_orders AS SELECT ...
2. BCREATE VIEW active_orders AS SELECT ...
3. CCREATE VIEW active_orders FROM SELECT ...
4. DVIEW active_orders USING SELECT ...

Q19 / 25

Which command is optimized to load many rows with less overhead than a series of INSERT commands?

1. AANALYZE
2. BVACUUM
3. CEXPLAIN
4. DCOPY

Q20 / 25

A nightly load re-sends rows that may already exist under a unique key. Existing rows must be refreshed with the incoming values, the run must be safe to repeat under concurrent activity, and no violation error may surface. Which clause meets the requirements?

1. AA delete of the matching keys followed by a plain insert of the incoming rows
2. BA conflict clause naming the unique key, with a skipping action
3. CA conflict clause naming the unique key, with an updating action reading the proposed row
4. DA conflict clause with no target, with an updating action reading the proposed row

Q21 / 25

A side-effect-free common table expression reads a large table and is referenced twice. Each reference needs a different small subset, and the underlying computation is cheap enough to repeat. Which choice favors applying both filters early?

1. AUse NOT MATERIALIZED so each restriction can reach the base scan
2. BUse a recursive term so each subset becomes a working table
3. CUse UNION so duplicate rows replace parent restrictions
4. DUse MATERIALIZED so the complete large result is calculated once

Q22 / 25

A Read Committed consistency check compares totals from two actively updated tables. Non-serializable writers must remain possible elsewhere, but this check needs one current view with no uncommitted table changes. Which design fits?

1. ALock every table used by the check in `SHARE` mode or higher before reading
2. BRun the two totals as successive plain queries with no explicit locks
3. CMake only the check Serializable while other writers remain non-serializable
4. DLock one returned row from each table after both totals are calculated

Q23 / 25

What fills left-side columns for an unmatched right row in a right outer join?

1. AValues copied from the right-side key
2. BZeros for numeric columns
3. CThe previous matched row
4. DNull values

Q24 / 25

Which call concatenates the values of column a with a comma and guarantees that the resulting text is in alphabetical order?

1. Astring_agg(a ORDER BY a, ',')
2. Bstring_agg(a, ',') with the query's own ORDER BY on a
3. Cstring_agg(a, ',' ORDER BY a)
4. Dstring_agg(a, ',') on its own, since input arrives in table order

Q25 / 25

What transferable habit should accompany PostgreSQL practice?

1. AIdentify product extensions and verify dialect-specific details
2. BAssume each PostgreSQL behavior is universal across SQL engines
3. CTreat every SQL statement as a PostgreSQL server process
4. DAvoid general relational ideas whenever PostgreSQL runs a query