What do PL/SQL and T-SQL add to plain SQL?

Variables, decisions, loops and error handling, inside the database. A whole unit of work travels to the data once instead of statement by statement, and the database itself decides what to do when a step fails.

Your new procedural language has loops. Should a balance update now run one UPDATE per account?

No. One UPDATE with a WHERE clause changes every matching row in a single statement. The SQL engine still works best on whole sets; the procedural parts are for the order of the steps, the checks between them and what happens when one fails.

Is GO a T-SQL statement?

No. Microsoft documents GO as a command of sqlcmd and the query editors that ends a batch. Oracle's equivalent is the lone slash that tells SQL*Plus to send the block it has collected.

Where does a transaction begin on each engine, if nobody opens one?

Oracle starts one at the first executable SQL statement, so a block only has to commit or roll back. SQL Server makes each statement a transaction of its own unless BEGIN TRANSACTION groups them, which is why a T-SQL batch opens one and its CATCH block rolls back whatever is still open.

Oracle 26ai prints Release 23.26.3.0.0. How do you read that?

Major release 23, then the year of the release update: 23.26.3 is the third quarterly update of 2026. DBMS_DB_VERSION.VERSION holds the major release, so a version test on 26ai compares with 23, never with 26. In 19c the second numeral is just the release-update number, as in 19.7.

Which SQL Server engine version is which product, and what is the third number?

15 is SQL Server 2019, 16 is 2022 and 17 is 2025; the third number is the build, so 17.0.5005.3 is SQL Server 2025 with cumulative update 9.

Why can one SQL Server 2025 engine accept REGEXP_LIKE in one database and reject it in the next?

Each database has its own compatibility level, 170 by default on 2025, 160 on 2022 and 150 on 2019. Microsoft documents REGEXP_LIKE as working only at level 170, and a database restored from an older server keeps its old level.

A figure says 30.1 % for SQL Server. What has to come with it before it means anything?

Its source, its question and its period. That number is the share of the 26,083 respondents to Stack Overflow's 2025 database question who had done extensive work in SQL Server over the past year, with 10.6 % for Oracle; it counts people answering a survey, not installations.

Which computers can run SQL Server's container image, and what about Oracle's?

Microsoft supports the SQL Server image only on Linux hosts with Intel or AMD x86-64 processors, and does not test or support emulation such as Rosetta 2 or Prism. Oracle AI Database 26ai Free runs on x86-64 and Arm64, so an Apple silicon Mac runs Oracle locally and needs another machine for SQL Server.

Why is docker run -p 1433:1433 the wrong way to publish a database port?

Docker's own documentation calls publishing ports insecure by default: that form makes the port available to the outside world as well as to your computer. Write -p 127.0.0.1:1433:1433 so only the Docker host can reach it, which is also OWASP's advice for any database.

You published a port on 127.0.0.1. Why does the Docker version still matter?

Docker notes that in releases older than 28.0.0 other hosts on the same network segment could reach ports published to localhost. Keep Docker current, and in a codespace leave a forwarded database port private, because a public port is open to anyone with its URL.

Where do the lab's passwords live, and how do they reach the containers?

In one generated file of mode 600 that .gitignore hides. docker run -e NAME with no value copies the value from the script's environment, so no password reaches a command line, and SQLCMDPASSWORD hands it to sqlcmd because Microsoft calls the -P option insecure.

Which password weakness stays in a SQL Server container even after you keep it out of Git?

Microsoft names it: the container keeps MSSQL_SA_PASSWORD in its environment, where anyone who can inspect the container can read it. On a shared machine change the sa password after the first start with ALTER LOGIN, and use MSSQL_SA_PASSWORD, never the deprecated SA_PASSWORD.

What are the two address traps when connecting to the lab?

Oracle connects to a service and the lab user lives in the pluggable database FREEPDB1, so the address is //127.0.0.1:1521/FREEPDB1. SQL Server's clients write the port after a comma, 127.0.0.1,1433, never after a colon.

What proves a two-engine lab is ready?

A smoke test that connects, creates, writes, reads and undoes in each dialect, plus docker port lines that all point to 127.0.0.1. That checks the engines, the passwords and the network setting in one go. Oracle commits its CREATE TABLE itself, so the table survives the rollback while the rows do not.

What is Oracle AI Database 26ai Free limited to?

At most 2 CPU threads, 2 GB of memory for the database and 12 GB of user data, under Oracle's Free Use Terms, which allow internal use for developing, testing, prototyping and demonstrating your own applications.

What is an empty string in each engine?

Oracle's SQL reference says a character value of length zero is a null, so LENGTH('') is NULL and '' behaves as NULL in every comparison. SQL Server keeps it as a real value: LEN(N'') is 0 and N'' is not NULL. Store NULL for no value and test IS NULL.

What does joining text to NULL give in each engine?

Oracle's 'a' || NULL is 'a', because a null joined to text gives the text back. SQL Server's 'a' + NULL is NULL under its default session settings.

Is 'abc' = 'ABC' true, and is 'a' = 'a ' true?

Oracle compares text with binary collation by default, so 'abc' and 'ABC' differ; the lab's SQL Server collation SQL_Latin1_General_CP1_CI_AS is case-insensitive, so they are equal. SQL Server pads trailing spaces for =, while Oracle pads only CHAR values and literals, so two VARCHAR2 values with different trailing spaces differ.

What is 7 / 2 in T-SQL and in Oracle?

3 in T-SQL, because Microsoft documents integer division as truncating the fraction, and 3.5 in Oracle, which does the arithmetic on its NUMBER type. 7 / 2.0 is 3.500000 in T-SQL, and TRUNC(7 / 2) gives 3 in Oracle. A rate moved from Oracle can lose its fraction with no error.

How do the two engines hold dates and add an hour?

An Oracle DATE always holds a year, month, day, hour, minute and second, and a number added to it is a number of days, so + 1 / 24 adds an hour. SQL Server separates date, datetime2(n) with 0 to 7 fractional digits and the older datetime, and adds an hour with DATEADD(hour, 1, ...).

How does each engine take the first n rows, with ties?

Both accept the standard ORDER BY ... OFFSET m ROWS FETCH NEXT n ROWS ONLY, and SQL Server requires the ORDER BY. SQL Server also has TOP (n) after SELECT, Oracle FETCH FIRST n ROWS ONLY, and both add WITH TIES to keep every row whose sort key equals the last one taken.

Why does every key, check and reference in the practice schema have a name of its own?

Without one each engine makes a name up (Oracle's SYS_Cn form, SQL Server a system-generated name) that changes with every rebuild, and Microsoft notes the constraint name appears in every violation message. A named constraint gives the same message each time, so tests and log searches keep working.

Where do you look up an error number in each engine?

An Oracle error is ORA- and five digits, and each has a help page with its cause and action at docs.oracle.com/error-help/db/, with the number in lower case. A SQL Server error carries a number, a severity from 0 to 25 and a state; the number finds it in Microsoft's lists of database engine errors, split into ranges.

What is a batch, and what does GO really do?

A batch is the group of statements a client sends in one go, and Microsoft documents that its statements are compiled into a single execution plan, so it compiles as a whole. GO is not T-SQL: sqlcmd and the query editors send everything typed since the previous GO as one batch and never send the GO line itself.

An application reads a migration file with GO lines and passes the whole text to its driver. What happens?

A syntax error: Microsoft notes that programs using the ODBC or OLE DB interfaces get one, because the server has never heard of GO. Code that runs scripts must split them at their GO lines itself and send each batch on its own.

Which two batch rules give errors 111 and 137?

CREATE VIEW, PROCEDURE, FUNCTION and TRIGGER must begin their batch, or the server raises 111, must be the first statement in a query batch. A local variable lives only in the batch that declares it, so using it after a GO raises 137, must declare the scalar variable. Both are compile errors, so no statement of the batch runs.

What value does a variable hold right after DECLARE?

NULL, unless the declaration gives it one, and NULL spreads through arithmetic and comparisons with no error. A variable is also typed once and for all, so a string longer than its nvarchar(20) is cut to 20 characters silently. Start counters and totals explicitly at 0.

A lookup query matches two rows. What does SELECT assignment do, and what does SET do?

SELECT @v = balance FROM ... keeps the last row the query returned, with no error, and keeps the old value when no row matches. SET @v = (SELECT ...) raises error 512, subquery returned more than 1 value, for two rows and assigns NULL for none. Which row comes last is not promised, so SELECT assignment can change answer after an index change.

Why must @@ROWCOUNT be read on the very next line?

It describes only the statement just before it, and almost everything changes it: SET, RETURN and a SELECT without a query set it to 1, PRINT, RAISERROR, BEGIN TRANSACTION, COMMIT and SET options reset it to 0, and EXECUTE leaves it unchanged. A debugging PRINT above the test is enough to report that nothing changed.

What do SELECT, PRINT and SET NOCOUNT ON each send to the client?

SELECT returns a result set that client code reads as data; PRINT sends a message on a separate stream, at most 8,000 characters or 4,000 for Unicode, longer ones cut. SET NOCOUNT ON stops the rows affected messages and leaves @@ROWCOUNT working; Microsoft advises it as a procedure's first statement and notes the speed gain is not measurable.

Which ways of joining text in T-SQL give NULL when one side is NULL?

+ does, because CONCAT_NULL_YIELDS_NULL is always ON from SQL Server 2017 and its OFF value is deprecated, and the || operator of SQL Server 2025 ignores the setting and gives NULL too. CONCAT reads each NULL as an empty string instead.

How many statements does an IF control in T-SQL?

Exactly one, and ELSE one more. Indentation means nothing to the parser, so several statements need BEGIN and END around them. Many teams write the block even for a single statement, so a line added later cannot fall outside the condition.

Which loop and which statement does T-SQL not have?

There is no FOR loop and no CASE statement. Loops are written with WHILE, and CASE exists only as an expression.

Why is CREATE OR ALTER PROCEDURE better than DROP then CREATE in a release script?

It creates the procedure when it is missing and changes it when it exists, so the script runs twice without error and leaves no gap when the procedure does not exist. ALTER keeps the procedure's permissions; a procedure dropped and created again loses every GRANT EXECUTE it had.

Which two naming rules does Microsoft give for a procedure?

Write the schema both when creating and when calling it, because without it the creator's default schema is used and the name takes longer to resolve. Never start a name with sp_, the prefix SQL Server uses for its own system procedures, since one of the same name can break your code.

What are T-SQL's three kinds of user-defined function?

A scalar function returning one value; an inline table-valued function, written RETURNS TABLE AS RETURN with a single SELECT; and a multi-statement one, which declares a table variable in its RETURNS clause, fills it with several statements and returns it.

What are the three ways a T-SQL statement ends up in a transaction?

Autocommit, the default, where each statement is committed when it succeeds and rolled back when it fails; an explicit transaction opened by BEGIN TRANSACTION; and implicit mode after SET IMPLICIT_TRANSACTIONS ON, where a change or a table read opens one silently whenever @@TRANCOUNT is 0 and nothing commits until the code says so.

What does a second BEGIN TRANSACTION in the same session start?

Nothing new. Microsoft's pages say the Database Engine does not support independently manageable nested transactions: the second BEGIN only raises @@TRANCOUNT to 2. A session has one transaction and a counter.

Which COMMIT keeps the work, and how much does a bare ROLLBACK undo?

Only the COMMIT that brings @@TRANCOUNT to 0; a COMMIT while the count is above 1 just lowers it and makes nothing permanent. A ROLLBACK without a savepoint name undoes everything since the outermost BEGIN and sets the count to 0.

A script rolls back and then commits. What does the COMMIT say?

Error 3902: the COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. The earlier ROLLBACK had already ended the whole transaction and set @@TRANCOUNT to 0.

What does a rollback to a savepoint do to @@TRANCOUNT and to locks?

It leaves the count unchanged, so the transaction must still end with a COMMIT or a full ROLLBACK. It undoes only the changes made after the savepoint, releases the locks taken after it apart from escalated and converted ones, and with two savepoints of one name it returns to the most recent.

What is error 266, and which two habits cause it?

SQL Server's message says "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements": a procedure returned with a different @@TRANCOUNT from the one it found. The usual causes are an early RETURN between BEGIN TRANSACTION and COMMIT, and a full ROLLBACK inside a procedure called within a caller's transaction.

How does a procedure stay balanced when it may be called inside a caller's transaction?

Read @@TRANCOUNT on entry, open a transaction only when it is 0, set a savepoint otherwise, and commit only what the procedure itself opened. That is the shape of the example on Microsoft's SAVE TRANSACTION page.

Why does the length of a transaction matter to everyone else?

It holds the locks protecting its changes until it ends, whatever the isolation level, and Microsoft warns that a long one can also prevent transaction log truncation and version store cleanup. Check before BEGIN TRANSACTION, never wait for a person, a file or a network call inside one, and change large sets in batches.

Which errors does a TRY block hand to its CATCH block?

Every run-time error of severity above 10 that does not close the database connection, including one raised inside a procedure the TRY block called, unless that procedure has a TRY...CATCH of its own. A TRY...CATCH cannot span two batches and cannot be used inside a user-defined function.

What do the ERROR_ functions report, and where do they answer?

ERROR_NUMBER, ERROR_SEVERITY, ERROR_STATE, ERROR_PROCEDURE (schema included since SQL Server 2017, NULL outside a procedure or trigger), ERROR_LINE and ERROR_MESSAGE. They answer inside the CATCH block only: outside it each returns NULL.

A CATCH block logs the error and ends. What does the calling application see?

A batch that succeeded. Microsoft notes that an error trapped by a CATCH block is not returned to the calling application unless the block sends it on.

With XACT_ABORT off, which failures end only their statement and which end the whole transaction?

A CHECK or foreign key violation ends only its statement and leaves a committable transaction. A deadlock ends the victim's batch and rolls its transaction back, and a ROLLBACK inside a trigger does the same to the statement that fired it. A THROW with no CATCH ends the batch but leaves an open transaction open.

Who can see a #table a stored procedure creates?

The procedure itself and the procedures it calls. It is dropped as soon as the procedure ends, so the caller's next SELECT from it fails with error 208, invalid object name. Any other #table, such as one a batch creates, lasts until the session ends or a DROP removes it.

Two sessions each create #stage. How many tables are there, and what does that cost the name?

Two: SQL Server adds a unique suffix to each local temporary table's name inside tempdb, which is why such a name may have at most 116 characters instead of 128.

This page needs a newer browser to run its tools. You can still read everything here.

PL/SQL & T-SQL (Oracle and SQL Server)

PL/SQL and T-SQL flashcards

Recall cards for the PL/SQL and T-SQL track: batches and blocks, errors and transactions, cursors and bulk SQL, triggers, plans, security and tests.

411 cards. Read the question, answer it in your head, then open the card to check.

The backs of 64 of these 411 cards come from in-depth parts of the lessons and open with any MySmartCoPilot pass. See the passes

What do PL/SQL and T-SQL add to plain SQL?

Variables, decisions, loops and error handling, inside the database. A whole unit of work travels to the data once instead of statement by statement, and the database itself decides what to do when a step fails.

From the lesson PL/SQL and T-SQL: what they are and where they run
Your new procedural language has loops. Should a balance update now run one UPDATE per account?

No. One UPDATE with a WHERE clause changes every matching row in a single statement. The SQL engine still works best on whole sets; the procedural parts are for the order of the steps, the checks between them and what happens when one fails.

From the lesson PL/SQL and T-SQL: what they are and where they run
Is GO a T-SQL statement?

No. Microsoft documents GO as a command of sqlcmd and the query editors that ends a batch. Oracle's equivalent is the lone slash that tells SQL*Plus to send the block it has collected.

From the lesson PL/SQL and T-SQL: what they are and where they run
Where does a transaction begin on each engine, if nobody opens one?

Oracle starts one at the first executable SQL statement, so a block only has to commit or roll back. SQL Server makes each statement a transaction of its own unless BEGIN TRANSACTION groups them, which is why a T-SQL batch opens one and its CATCH block rolls back whatever is still open.

From the lesson PL/SQL and T-SQL: what they are and where they run
Oracle 26ai prints Release 23.26.3.0.0. How do you read that?

Major release 23, then the year of the release update: 23.26.3 is the third quarterly update of 2026. DBMS_DB_VERSION.VERSION holds the major release, so a version test on 26ai compares with 23, never with 26. In 19c the second numeral is just the release-update number, as in 19.7.

From the lesson PL/SQL and T-SQL: what they are and where they run
Which SQL Server engine version is which product, and what is the third number?

15 is SQL Server 2019, 16 is 2022 and 17 is 2025; the third number is the build, so 17.0.5005.3 is SQL Server 2025 with cumulative update 9.

From the lesson PL/SQL and T-SQL: what they are and where they run
Why can one SQL Server 2025 engine accept REGEXP_LIKE in one database and reject it in the next?

Each database has its own compatibility level, 170 by default on 2025, 160 on 2022 and 150 on 2019. Microsoft documents REGEXP_LIKE as working only at level 170, and a database restored from an older server keeps its old level.

From the lesson PL/SQL and T-SQL: what they are and where they run
A figure says 30.1 % for SQL Server. What has to come with it before it means anything?

Its source, its question and its period. That number is the share of the 26,083 respondents to Stack Overflow's 2025 database question who had done extensive work in SQL Server over the past year, with 10.6 % for Oracle; it counts people answering a survey, not installations.

From the lesson PL/SQL and T-SQL: what they are and where they run
Which computers can run SQL Server's container image, and what about Oracle's?

Microsoft supports the SQL Server image only on Linux hosts with Intel or AMD x86-64 processors, and does not test or support emulation such as Rosetta 2 or Prism. Oracle AI Database 26ai Free runs on x86-64 and Arm64, so an Apple silicon Mac runs Oracle locally and needs another machine for SQL Server.

From the lesson Set up a lab with Oracle 26ai and SQL Server 2025
Why is docker run -p 1433:1433 the wrong way to publish a database port?

Docker's own documentation calls publishing ports insecure by default: that form makes the port available to the outside world as well as to your computer. Write -p 127.0.0.1:1433:1433 so only the Docker host can reach it, which is also OWASP's advice for any database.

From the lesson Set up a lab with Oracle 26ai and SQL Server 2025
You published a port on 127.0.0.1. Why does the Docker version still matter?

Docker notes that in releases older than 28.0.0 other hosts on the same network segment could reach ports published to localhost. Keep Docker current, and in a codespace leave a forwarded database port private, because a public port is open to anyone with its URL.

From the lesson Set up a lab with Oracle 26ai and SQL Server 2025
Where do the lab's passwords live, and how do they reach the containers?

In one generated file of mode 600 that .gitignore hides. docker run -e NAME with no value copies the value from the script's environment, so no password reaches a command line, and SQLCMDPASSWORD hands it to sqlcmd because Microsoft calls the -P option insecure.

From the lesson Set up a lab with Oracle 26ai and SQL Server 2025
Which password weakness stays in a SQL Server container even after you keep it out of Git?

Microsoft names it: the container keeps MSSQL_SA_PASSWORD in its environment, where anyone who can inspect the container can read it. On a shared machine change the sa password after the first start with ALTER LOGIN, and use MSSQL_SA_PASSWORD, never the deprecated SA_PASSWORD.

From the lesson Set up a lab with Oracle 26ai and SQL Server 2025
What are the two address traps when connecting to the lab?

Oracle connects to a service and the lab user lives in the pluggable database FREEPDB1, so the address is //127.0.0.1:1521/FREEPDB1. SQL Server's clients write the port after a comma, 127.0.0.1,1433, never after a colon.

From the lesson Set up a lab with Oracle 26ai and SQL Server 2025
What proves a two-engine lab is ready?

A smoke test that connects, creates, writes, reads and undoes in each dialect, plus docker port lines that all point to 127.0.0.1. That checks the engines, the passwords and the network setting in one go. Oracle commits its CREATE TABLE itself, so the table survives the rollback while the rows do not.

From the lesson Set up a lab with Oracle 26ai and SQL Server 2025
What is Oracle AI Database 26ai Free limited to?

At most 2 CPU threads, 2 GB of memory for the database and 12 GB of user data, under Oracle's Free Use Terms, which allow internal use for developing, testing, prototyping and demonstrating your own applications.

From the lesson Set up a lab with Oracle 26ai and SQL Server 2025
What is an empty string in each engine?

Oracle's SQL reference says a character value of length zero is a null, so LENGTH('') is NULL and '' behaves as NULL in every comparison. SQL Server keeps it as a real value: LEN(N'') is 0 and N'' is not NULL. Store NULL for no value and test IS NULL.

From the lesson The practice schema and dialect basics
What does joining text to NULL give in each engine?

Oracle's 'a' || NULL is 'a', because a null joined to text gives the text back. SQL Server's 'a' + NULL is NULL under its default session settings.

From the lesson The practice schema and dialect basics
Is 'abc' = 'ABC' true, and is 'a' = 'a ' true?

Oracle compares text with binary collation by default, so 'abc' and 'ABC' differ; the lab's SQL Server collation SQL_Latin1_General_CP1_CI_AS is case-insensitive, so they are equal. SQL Server pads trailing spaces for =, while Oracle pads only CHAR values and literals, so two VARCHAR2 values with different trailing spaces differ.

From the lesson The practice schema and dialect basics
What is 7 / 2 in T-SQL and in Oracle?

3 in T-SQL, because Microsoft documents integer division as truncating the fraction, and 3.5 in Oracle, which does the arithmetic on its NUMBER type. 7 / 2.0 is 3.500000 in T-SQL, and TRUNC(7 / 2) gives 3 in Oracle. A rate moved from Oracle can lose its fraction with no error.

From the lesson The practice schema and dialect basics
How do the two engines hold dates and add an hour?

An Oracle DATE always holds a year, month, day, hour, minute and second, and a number added to it is a number of days, so + 1 / 24 adds an hour. SQL Server separates date, datetime2(n) with 0 to 7 fractional digits and the older datetime, and adds an hour with DATEADD(hour, 1, ...).

From the lesson The practice schema and dialect basics
How does each engine take the first n rows, with ties?

Both accept the standard ORDER BY ... OFFSET m ROWS FETCH NEXT n ROWS ONLY, and SQL Server requires the ORDER BY. SQL Server also has TOP (n) after SELECT, Oracle FETCH FIRST n ROWS ONLY, and both add WITH TIES to keep every row whose sort key equals the last one taken.

From the lesson The practice schema and dialect basics
Why does every key, check and reference in the practice schema have a name of its own?

Without one each engine makes a name up (Oracle's SYS_Cn form, SQL Server a system-generated name) that changes with every rebuild, and Microsoft notes the constraint name appears in every violation message. A named constraint gives the same message each time, so tests and log searches keep working.

From the lesson The practice schema and dialect basics
Where do you look up an error number in each engine?

An Oracle error is ORA- and five digits, and each has a help page with its cause and action at docs.oracle.com/error-help/db/, with the number in lower case. A SQL Server error carries a number, a severity from 0 to 25 and a state; the number finds it in Microsoft's lists of database engine errors, split into ranges.

From the lesson The practice schema and dialect basics
What is a batch, and what does GO really do?

A batch is the group of statements a client sends in one go, and Microsoft documents that its statements are compiled into a single execution plan, so it compiles as a whole. GO is not T-SQL: sqlcmd and the query editors send everything typed since the previous GO as one batch and never send the GO line itself.

From the lesson T-SQL batches, GO and variables
An application reads a migration file with GO lines and passes the whole text to its driver. What happens?

A syntax error: Microsoft notes that programs using the ODBC or OLE DB interfaces get one, because the server has never heard of GO. Code that runs scripts must split them at their GO lines itself and send each batch on its own.

From the lesson T-SQL batches, GO and variables
Which two batch rules give errors 111 and 137?

CREATE VIEW, PROCEDURE, FUNCTION and TRIGGER must begin their batch, or the server raises 111, must be the first statement in a query batch. A local variable lives only in the batch that declares it, so using it after a GO raises 137, must declare the scalar variable. Both are compile errors, so no statement of the batch runs.

From the lesson T-SQL batches, GO and variables
What value does a variable hold right after DECLARE?

NULL, unless the declaration gives it one, and NULL spreads through arithmetic and comparisons with no error. A variable is also typed once and for all, so a string longer than its nvarchar(20) is cut to 20 characters silently. Start counters and totals explicitly at 0.

From the lesson T-SQL batches, GO and variables
A lookup query matches two rows. What does SELECT assignment do, and what does SET do?

SELECT @v = balance FROM ... keeps the last row the query returned, with no error, and keeps the old value when no row matches. SET @v = (SELECT ...) raises error 512, subquery returned more than 1 value, for two rows and assigns NULL for none. Which row comes last is not promised, so SELECT assignment can change answer after an index change.

From the lesson T-SQL batches, GO and variables
Why must @@ROWCOUNT be read on the very next line?

It describes only the statement just before it, and almost everything changes it: SET, RETURN and a SELECT without a query set it to 1, PRINT, RAISERROR, BEGIN TRANSACTION, COMMIT and SET options reset it to 0, and EXECUTE leaves it unchanged. A debugging PRINT above the test is enough to report that nothing changed.

From the lesson T-SQL batches, GO and variables
What do SELECT, PRINT and SET NOCOUNT ON each send to the client?

SELECT returns a result set that client code reads as data; PRINT sends a message on a separate stream, at most 8,000 characters or 4,000 for Unicode, longer ones cut. SET NOCOUNT ON stops the rows affected messages and leaves @@ROWCOUNT working; Microsoft advises it as a procedure's first statement and notes the speed gain is not measurable.

From the lesson T-SQL batches, GO and variables
Which ways of joining text in T-SQL give NULL when one side is NULL?

+ does, because CONCAT_NULL_YIELDS_NULL is always ON from SQL Server 2017 and its OFF value is deprecated, and the || operator of SQL Server 2025 ignores the setting and gives NULL too. CONCAT reads each NULL as an empty string instead.

From the lesson T-SQL batches, GO and variables
How many statements does an IF control in T-SQL?

Exactly one, and ELSE one more. Indentation means nothing to the parser, so several statements need BEGIN and END around them. Many teams write the block even for a single statement, so a line added later cannot fall outside the condition.

From the lesson T-SQL control flow: IF, WHILE and CASE
What does a comparison with NULL return, and how do IF, WHILE and CASE treat it?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL control flow: IF, WHILE and CASE
Which loop and which statement does T-SQL not have?

There is no FOR loop and no CASE statement. Loops are written with WHILE, and CASE exists only as an expression.

From the lesson T-SQL control flow: IF, WHILE and CASE
A WHILE loop gets each due date by adding a month to the one before, starting from the last day of January. Why are 11 of 12 dates wrong?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL control flow: IF, WHILE and CASE
What does GENERATE_SERIES replace, and which releases have it?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL control flow: IF, WHILE and CASE
What decides which WHEN clause of a searched CASE gives the answer?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL control flow: IF, WHILE and CASE
What does WAITFOR DELAY cost while it waits?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL control flow: IF, WHILE and CASE
Why is CREATE OR ALTER PROCEDURE better than DROP then CREATE in a release script?

It creates the procedure when it is missing and changes it when it exists, so the script runs twice without error and leaves no gap when the procedure does not exist. ALTER keeps the procedure's permissions; a procedure dropped and created again loses every GRANT EXECUTE it had.

From the lesson T-SQL stored procedures and parameters
Which two naming rules does Microsoft give for a procedure?

Write the schema both when creating and when calling it, because without it the creator's default schema is used and the name takes longer to resolve. Never start a name with sp_, the prefix SQL Server uses for its own system procedures, since one of the same name can break your code.

From the lesson T-SQL stored procedures and parameters
A call leaves OUTPUT off its variable. What happens?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL stored procedures and parameters
What are a procedure's three ways to hand something back, and how should failure be reported?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL stored procedures and parameters
Which mistake is caught when a procedure is created, and which only when it runs?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL stored procedures and parameters
Which function returns the key your own INSERT made, even when a trigger inserts rows too?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL stored procedures and parameters
What does the fixed header of every procedure in this track contain?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL stored procedures and parameters
NEXT VALUE FOR a sequence appears twice in one row of a SELECT. How many numbers do you get?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL stored procedures and parameters
What are T-SQL's three kinds of user-defined function?

A scalar function returning one value; an inline table-valued function, written RETURNS TABLE AS RETURN with a single SELECT; and a multi-statement one, which declares a table variable in its RETURNS clause, fills it with several statements and returns it.

From the lesson T-SQL functions: scalar, inline and multi-statement
When can SQL Server fold a scalar function into the calling query?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL functions: scalar, inline and multi-statement
Name two things that stop a scalar function being inlined.

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL functions: scalar, inline and multi-statement
What is an inline table-valued function, as the optimizer sees it?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL functions: scalar, inline and multi-statement
How do CROSS APPLY and OUTER APPLY differ when they call a function per row?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL functions: scalar, inline and multi-statement
What row estimate does SQL Server plan with for a multi-statement table-valued function?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL functions: scalar, inline and multi-statement
What may a T-SQL function not do?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL functions: scalar, inline and multi-statement
What does WITH SCHEMABINDING give a function?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL functions: scalar, inline and multi-statement
What are the three ways a T-SQL statement ends up in a transaction?

Autocommit, the default, where each statement is committed when it succeeds and rolled back when it fails; an explicit transaction opened by BEGIN TRANSACTION; and implicit mode after SET IMPLICIT_TRANSACTIONS ON, where a change or a table read opens one silently whenever @@TRANCOUNT is 0 and nothing commits until the code says so.

From the lesson T-SQL transactions, @@TRANCOUNT and savepoints
What does a second BEGIN TRANSACTION in the same session start?

Nothing new. Microsoft's pages say the Database Engine does not support independently manageable nested transactions: the second BEGIN only raises @@TRANCOUNT to 2. A session has one transaction and a counter.

From the lesson T-SQL transactions, @@TRANCOUNT and savepoints
Which COMMIT keeps the work, and how much does a bare ROLLBACK undo?

Only the COMMIT that brings @@TRANCOUNT to 0; a COMMIT while the count is above 1 just lowers it and makes nothing permanent. A ROLLBACK without a savepoint name undoes everything since the outermost BEGIN and sets the count to 0.

From the lesson T-SQL transactions, @@TRANCOUNT and savepoints
A script rolls back and then commits. What does the COMMIT say?

Error 3902: the COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. The earlier ROLLBACK had already ended the whole transaction and set @@TRANCOUNT to 0.

From the lesson T-SQL transactions, @@TRANCOUNT and savepoints
What does a rollback to a savepoint do to @@TRANCOUNT and to locks?

It leaves the count unchanged, so the transaction must still end with a COMMIT or a full ROLLBACK. It undoes only the changes made after the savepoint, releases the locks taken after it apart from escalated and converted ones, and with two savepoints of one name it returns to the most recent.

From the lesson T-SQL transactions, @@TRANCOUNT and savepoints
What is error 266, and which two habits cause it?

SQL Server's message says "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements": a procedure returned with a different @@TRANCOUNT from the one it found. The usual causes are an early RETURN between BEGIN TRANSACTION and COMMIT, and a full ROLLBACK inside a procedure called within a caller's transaction.

From the lesson T-SQL transactions, @@TRANCOUNT and savepoints
How does a procedure stay balanced when it may be called inside a caller's transaction?

Read @@TRANCOUNT on entry, open a transaction only when it is 0, set a savepoint otherwise, and commit only what the procedure itself opened. That is the shape of the example on Microsoft's SAVE TRANSACTION page.

From the lesson T-SQL transactions, @@TRANCOUNT and savepoints
Why does the length of a transaction matter to everyone else?

It holds the locks protecting its changes until it ends, whatever the isolation level, and Microsoft warns that a long one can also prevent transaction log truncation and version store cleanup. Check before BEGIN TRANSACTION, never wait for a person, a file or a network call inside one, and change large sets in batches.

From the lesson T-SQL transactions, @@TRANCOUNT and savepoints
Which errors does a TRY block hand to its CATCH block?

Every run-time error of severity above 10 that does not close the database connection, including one raised inside a procedure the TRY block called, unless that procedure has a TRY...CATCH of its own. A TRY...CATCH cannot span two batches and cannot be used inside a user-defined function.

From the lesson T-SQL TRY...CATCH, THROW and RAISERROR
What do the ERROR_ functions report, and where do they answer?

ERROR_NUMBER, ERROR_SEVERITY, ERROR_STATE, ERROR_PROCEDURE (schema included since SQL Server 2017, NULL outside a procedure or trigger), ERROR_LINE and ERROR_MESSAGE. They answer inside the CATCH block only: outside it each returns NULL.

From the lesson T-SQL TRY...CATCH, THROW and RAISERROR
A CATCH block logs the error and ends. What does the calling application see?

A batch that succeeded. Microsoft notes that an error trapped by a CATCH block is not returned to the calling application unless the block sends it on.

From the lesson T-SQL TRY...CATCH, THROW and RAISERROR
Four statements run in a transaction and the second fails with a CHECK violation. What does COMMIT keep?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL TRY...CATCH, THROW and RAISERROR
How do THROW and RAISERROR differ?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL TRY...CATCH, THROW and RAISERROR
What must the statement just before THROW end with, and why?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL TRY...CATCH, THROW and RAISERROR
Which failures never reach a CATCH block, and which only reach one a level down?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL TRY...CATCH, THROW and RAISERROR
In which order does a good CATCH block do its work?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL TRY...CATCH, THROW and RAISERROR
With XACT_ABORT off, which failures end only their statement and which end the whole transaction?

A CHECK or foreign key violation ends only its statement and leaves a committable transaction. A deadlock ends the victim's batch and rolls its transaction back, and a ROLLBACK inside a trigger does the same to the statement that fired it. A THROW with no CATCH ends the batch but leaves an open transaction open.

From the lesson XACT_ABORT, XACT_STATE and doomed transactions
What does SET XACT_ABORT ON change?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson XACT_ABORT, XACT_STATE and doomed transactions
What do XACT_STATE()'s three values mean?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson XACT_ABORT, XACT_STATE and doomed transactions
What is error 3930, and what does it tell you about the transaction?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson XACT_ABORT, XACT_STATE and doomed transactions
May a CATCH block commit the work its transaction has done so far?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson XACT_ABORT, XACT_STATE and doomed transactions
What does the module's error template do in a procedure that runs inside a caller's transaction?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson XACT_ABORT, XACT_STATE and doomed transactions
Where does the log row of an error that doomed the transaction get written?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson XACT_ABORT, XACT_STATE and doomed transactions
How should code answer error 1205?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson XACT_ABORT, XACT_STATE and doomed transactions
Who can see a #table a stored procedure creates?

The procedure itself and the procedures it calls. It is dropped as soon as the procedure ends, so the caller's next SELECT from it fails with error 208, invalid object name. Any other #table, such as one a batch creates, lasts until the session ends or a DROP removes it.

From the lesson T-SQL temporary tables: #local and ##global
Two sessions each create #stage. How many tables are there, and what does that cost the name?

Two: SQL Server adds a unique suffix to each local temporary table's name inside tempdb, which is why such a name may have at most 116 characters instead of 128.

From the lesson T-SQL temporary tables: #local and ##global
When is a ##global temporary table dropped?

When the session that created it has ended and no statement still uses it, while the database-scoped option GLOBAL_TEMPORARY_TABLE_AUTO_DROP is on, its default. SQL Server reads that option from tempdb, so setting it in a user database changes nothing.

From the lesson T-SQL temporary tables: #local and ##global
How many changes make a small temporary table's statistics out of date?

Six, for a table with fewer than 6 rows, where a permanent one waits for 500. Above 500 rows both kinds use the smaller of 500 plus a fifth of the rows and the square root of 1,000 times the rows. So a procedure that fills a small #table row by row can recompile its statements during the run.

From the lesson T-SQL temporary tables: #local and ##global
What does SELECT ... INTO #t copy, and what does it not?

Each new column keeps the name, data type, nullability and value of its expression, and a plain identity column even brings its IDENTITY property unless the query joins, unions, repeats or computes it. Indexes, constraints and triggers are never copied. Most expressions give a nullable column, which CAST and ISNULL fix.

From the lesson T-SQL temporary tables: #local and ##global
What does a ROLLBACK do to temp-table rows, and which constraint is never enforced?

Rows inserted inside the transaction are removed by its ROLLBACK. A FOREIGN KEY on a temporary table is skipped with message 1756 and never enforced, and no table may reference a temporary table, so a staged row for an account that does not exist is accepted.

From the lesson T-SQL temporary tables: #local and ##global
Why do temporary tables leave their constraints unnamed?

The unique suffix is added to table names only, and every temporary table is created in tempdb's dbo schema, where a constraint name must be unique. A name you write yourself is one name for the whole instance, so a second session fails with error 2714, there is already an object named that.

From the lesson T-SQL temporary tables: #local and ##global
What are the three steps of applying a messy supplier feed with temporary tables?

Keep the raw rows in one #table, build a second with one row per cleaned key (the row ROW_NUMBER() numbers 1 from the newest time) and a unique index on it, then update and insert into the real tables in one set-based transaction. Tied rows need a tie-breaker such as a line number at the end of the ORDER BY.

From the lesson T-SQL temporary tables: #local and ##global
How far does a table variable reach?

Only the batch, procedure or function that declares it, and it is cleaned up when that ends; a second batch fails with error 1087, must declare the table variable. Procedures it calls cannot see it unless it is passed as a table-valued parameter, and dynamic SQL run with EXEC or sp_executesql cannot see it either.

From the lesson Table variables and table-valued parameters
Which three things can you not do to a table variable?

Alter it after it is declared, make it the target of SELECT ... INTO, or add an index later: its primary key, unique and check constraints and its indexes must all be written inline in the declaration. Inline indexes exist from SQL Server 2014.

From the lesson Table variables and table-valued parameters
A transaction writes rows into a #table and into a table variable, then rolls back. What is left?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Table variables and table-valued parameters
After a ROLLBACK, what do the rows in a table variable actually tell you?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Table variables and table-valued parameters
What row count does SQL Server plan with for a table variable?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Table variables and table-valued parameters
What does a table-valued parameter need, and what may the routine not do with it?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Table variables and table-valued parameters
An application saves a 40-line basket. Why one call with a table-valued parameter rather than 40 calls?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Table variables and table-valued parameters
Which of the three set tools fits which job?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Table variables and table-valued parameters
Which clauses can a MERGE have, and how many of each?

WHEN MATCHED updates or deletes a target row that has a partner, and there may be two such clauses, one updating and one deleting, the first carrying an AND condition. WHEN NOT MATCHED BY TARGET inserts a source row that has none, and there may be only one. WHEN NOT MATCHED BY SOURCE changes a target row the source does not mention, reading only target columns.

From the lesson T-SQL MERGE, OUTPUT and safe upserts
Why does the order of two WHEN MATCHED clauses matter?

Each row goes to the first clause that applies, so the second only sees the rows the first did not take. The clause with the narrower condition, such as AND s.on_hand = 0 for the rows to delete, has to come first.

From the lesson T-SQL MERGE, OUTPUT and safe upserts
When is WHEN NOT MATCHED BY SOURCE THEN DELETE safe?

Only when the source is complete, such as a full stock count. On a feed that sends just the items that changed, it deletes every row the feed left out.

From the lesson T-SQL MERGE, OUTPUT and safe upserts
What does OUTPUT $action give you after a MERGE?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL MERGE, OUTPUT and safe upserts
Why would you write OUTPUT ... INTO rather than a bare OUTPUT?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL MERGE, OUTPUT and safe upserts
Which three errors does MERGE raise, and what fixes each?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL MERGE, OUTPUT and safe upserts
Two sessions upsert the same new key at once. What happens, and what stops it?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL MERGE, OUTPUT and safe upserts
When are separate UPDATE and INSERT statements the better choice?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL MERGE, OUTPUT and safe upserts
What is the fixed shape of a correct T-SQL cursor?

DECLARE ... LOCAL FAST_FORWARD, OPEN, one FETCH NEXT before the loop, WHILE @@FETCH_STATUS = 0, the work, a second FETCH NEXT as the last statement of the body, then CLOSE and DEALLOCATE.

From the lesson T-SQL cursors and set-based rewrites
What do LOCAL and FAST_FORWARD each give a cursor?

LOCAL makes the name valid only in the batch, procedure or trigger that declares it; without LOCAL or GLOBAL the database default decides, and a global cursor lives until the connection ends. FAST_FORWARD declares a forward-only, read-only cursor with performance optimizations.

From the lesson T-SQL cursors and set-based rewrites
Why must the FETCH come before the WHILE test and again at the end of the body?

@@FETCH_STATUS reports on the last FETCH of the connection, whichever cursor ran it: 0 for a row, -1 for a failure or past the end, -2 for a missing row. With the fetch only inside the body, the first test reads a status this cursor never set, and after the last row the body runs once more on the old values.

From the lesson T-SQL cursors and set-based rewrites
Which window function replaces a running total kept in a variable, and why the explicit frame?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL cursors and set-based rewrites
Which two settings prove a rewrite is cheaper?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL cursors and set-based rewrites
What does a cursor cost per row that a set-based statement does not?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL cursors and set-based rewrites
Why do chunked loops use a few thousand rows a pass?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL cursors and set-based rewrites
Which loops are worth keeping?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL cursors and set-based rewrites
How do ROW_NUMBER, RANK, DENSE_RANK and NTILE number a tie at second place?

ROW_NUMBER gives 1, 2, 3, ties or not; RANK repeats the number and skips the next, 1, 2, 2, 4; DENSE_RANK repeats it without a gap, 1, 2, 2, 3; NTILE(n) cuts the partition into n groups whose sizes differ by one at most, the larger ones first. All four return bigint and all four require an ORDER BY in OVER.

From the lesson T-SQL ranking and offset window functions
When does ROW_NUMBER promise the same numbers on every run?

Only when the partition and ORDER BY columns together are unique. Otherwise tied rows can trade places between runs, which is why a tie-breaker column goes at the end of the ORDER BY.

From the lesson T-SQL ranking and offset window functions
What are LAG's optional arguments, and what are their rules?

The offset, 1 by default and never negative, and the default value returned when the offset points past the edge of the partition, NULL when none is given and type-compatible with the first argument. Neither LAG nor LEAD takes a frame, and each partition starts again.

From the lesson T-SQL ranking and offset window functions
Why is LAG(units, 1, 0) the wrong thing to subtract in a change column?

The first row of each partition has no row before it, so the default 0 makes it look like a rise from nothing, and a growth rate divided by it fails with error 8134. Leave the default out so the first row's change is NULL and the report handles it; a default fits only where 0 is a true value.

From the lesson T-SQL ranking and offset window functions
Why can a WHERE clause not filter on a window function's result?

SQL Server binds the query FROM, ON, JOIN, WHERE, GROUP BY, WITH CUBE or ROLLUP, HAVING, SELECT, DISTINCT, ORDER BY, TOP, and window functions are computed in the SELECT step, so WHERE runs before they have a value. Error 4108 says windowed functions can only appear in the SELECT or ORDER BY clauses.

From the lesson T-SQL ranking and offset window functions
Does T-SQL have QUALIFY?

No. Microsoft lists QUALIFY for Fabric Data Warehouse only, while Oracle AI Database 26ai has one. In SQL Server every filter on a window result goes through a CTE or a derived table: number the rows inside, filter in the outer query.

From the lesson T-SQL ranking and offset window functions
Which numbering function should a top-2 per group use?

It depends on ties. ROW_NUMBER returns exactly two rows per group and drops one of a tied pair; RANK keeps the tie and returns three; DENSE_RANK counts distinct amounts, so it can return three in every group. For a single group, TOP (2) WITH TIES ... ORDER BY does what RANK() <= 2 does.

From the lesson T-SQL ranking and offset window functions
How do you remove duplicate rows for one key?

Number each key's rows newest first with ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC, event_id DESC), then DELETE through the CTE where the number is above 1, with OUTPUT deleted. to list what went. The tie-breaker is what makes the result repeatable.

From the lesson T-SQL ranking and offset window functions
What frame does SUM() OVER use when the window has an ORDER BY and no ROWS or RANGE?

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With RANGE the current row means the current value, so every peer row sharing that ORDER BY value is in the frame: two sales on the same day show the same running total.

From the lesson T-SQL window frames and running totals
What does a running total need in order to move row by row?

A unique ORDER BY, such as sold_on with sale_id as a tie-breaker, and an explicit ROWS frame, which counts physical rows and stops at the row itself.

From the lesson T-SQL window frames and running totals
Which frame bounds does T-SQL refuse that Oracle accepts?

A RANGE frame in T-SQL allows only UNBOUNDED and CURRENT ROW as bounds, so RANGE BETWEEN 1 PRECEDING AND CURRENT ROW fails with error 4194, and T-SQL has no GROUPS frames at all. Oracle accepts both, with numbers or intervals as RANGE offsets.

From the lesson T-SQL window frames and running totals
What does LAST_VALUE return with the default frame, and which frame gives the partition's last value?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL window frames and running totals
What does SQL Server 2022's WINDOW clause do, and what are its rules?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL window frames and running totals
What does IGNORE NULLS do, and where can you write it?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL window frames and running totals
How do you find the unbroken runs of days in an ordered sequence?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL window frames and running totals
What makes a running total cheap for the engine, and which operator appears without it?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson T-SQL window frames and running totals
What three things does sp_executesql take?

The statement, which must be a Unicode constant or variable (an expression built with + is not accepted there), a declaration of each parameter with its type, and the values passed by name. A parameter marked OUTPUT in both places hands a value back.

From the lesson Dynamic SQL with sp_executesql, safely
What can a dynamic batch see of the caller?

Not its local variables or table variables: sp_executesql behaves like EXECUTE for batches and the scope of names. A temporary table created by the caller's session is visible inside it.

From the lesson Dynamic SQL with sp_executesql, safely
How does input pasted into a statement change what it means?

The input ends the string literal early and appends code of its own: a city of x' OR '1'='1 closes the quotes and adds a condition that every row meets, so the WHERE clause filters nothing and the search returns all customers. As the value of a typed parameter the same text is compared as data and returns none.

From the lesson Dynamic SQL with sp_executesql, safely
Why is doubling the quotes in a value not the fix for injection?

It handles one character in one place, and the next change to the code forgets it. Bound parameters are the first defence on OWASP's injection cheat sheet: pass every value as a typed parameter and keep values out of the statement text entirely.

From the lesson Dynamic SQL with sp_executesql, safely
Which parts of a statement cannot be parameters, and how do they get in safely?

Table names, column names and keywords such as ASC and DESC. A name passes an allowlist of names known to be legal and is then quoted with QUOTENAME; a keyword is chosen by the code from a bit or a fixed list, never pasted.

From the lesson Dynamic SQL with sp_executesql, safely
Why does an allowlist check test IS NULL before it tests NOT IN?

NOT IN is never true for NULL, so a NULL name would pass the check and then turn the whole statement text into NULL. QUOTENAME has the same trap: it accepts at most 128 characters and returns NULL for longer input, so check its result before running the statement.

From the lesson Dynamic SQL with sp_executesql, safely
Why does parameterised dynamic SQL reuse its plan where text with literals does not?

SQL Server looks for a cached plan whose statement text matches exactly. With parameters the text stays constant while only the values change, so one plan serves every call; three different literal values are three different statements to the cache.

From the lesson Dynamic SQL with sp_executesql, safely
Why does a user who may only EXECUTE a procedure get error 229 from its dynamic statement?

Ownership chaining does not apply to dynamic queries inside a module: a string run by a module is checked in the context of the user who runs the module, at the moment the string runs. WITH EXECUTE AS a named user, or signing the procedure with a certificate, keeps callers at EXECUTE.

From the lesson Dynamic SQL with sp_executesql, safely
How often does a DML trigger run, and what does it receive?

Once per statement, not once per row, whether that statement changed 3 rows or none, with every changed row at once in two tables: deleted holds the rows as they were before an UPDATE or DELETE, inserted the new or changed rows after an INSERT or UPDATE.

From the lesson T-SQL triggers: inserted, deleted and pitfalls
What do inserted and deleted hold for each kind of statement?

An INSERT fills inserted and leaves deleted empty; a DELETE fills deleted and leaves inserted empty; an UPDATE fills both, the same rows after and before the change, and the trigger matches them on the key.

From the lesson T-SQL triggers: inserted, deleted and pitfalls
Which statement removes rows without firing any trigger?

TRUNCATE TABLE, because it does not log the rows it removes one by one.

From the lesson T-SQL triggers: inserted, deleted and pitfalls
What does a trigger body that is right for 0, 1 or many rows look like?

SET NOCOUNT ON, then IF NOT EXISTS (SELECT 1 FROM inserted) RETURN;, then one INSERT ... SELECT that joins inserted and deleted on the key: no variable holding a row, and no result sets, because anything the trigger returns reaches the application that ran the statement.

From the lesson T-SQL triggers: inserted, deleted and pitfalls
What goes wrong when a trigger reads SELECT @sku = i.sku FROM inserted?

It keeps one of the rows and silently drops the rest, so an UPDATE of 3 prices logs 1; and on a statement that changed nothing its variables stay NULL, so the INSERT fails with error 515.

From the lesson T-SQL triggers: inserted, deleted and pitfalls
Which declarative rules often replace a trigger?

A CHECK constraint refuses a row whose own columns break a rule and a foreign key refuses a reference to a key that does not exist; a temporal table keeps the earlier versions of its rows without any code; and the OUTPUT clause returns, or stores in a table, the rows one statement changed.

From the lesson T-SQL triggers: inserted, deleted and pitfalls
What does an actual execution plan add to an estimated one?

The real row counts beside the estimates, how many times each operator ran, and more warnings, such as a spill to tempdb. An estimated plan shows what the optimizer will do, from statistics, before the query runs.

From the lesson Reading SQL Server execution plans
What is the first thing to look for in an actual plan, and what causes it?

The gap between estimated and actual rows. A large one, estimated 10 against 10,000 actual, points to stale statistics, skewed data a histogram cannot capture, a table variable before deferred compilation, or a multi-statement function before interleaved execution.

From the lesson Reading SQL Server execution plans
Which SET statements capture a plan, and which of them run the query?

SHOWPLAN_TEXT, SHOWPLAN_ALL and SHOWPLAN_XML return the estimated plan without running the query, and each must be the only statement in its batch. STATISTICS XML and STATISTICS PROFILE run the query and return the actual plan, with no batch rule.

From the lesson Reading SQL Server execution plans
What is a key lookup, and when does it become the problem?

A nonclustered index seek found the rows but the query needs columns the index does not hold, so the full row is fetched from the clustered index once per row. Ten lookups cost little; 99,000 cost nearly 300,000 page reads, far more than a scan of 1,700 pages.

From the lesson Reading SQL Server execution plans
What makes an index covering?

It holds every column the query needs, in its key or its INCLUDE list, so the optimizer reads only that index and never visits the clustered index, which removes the key lookups and their page reads.

From the lesson Reading SQL Server execution plans
What makes a predicate sargable, and what makes it scan?

The column stands alone, so the index can seek the range: ordered_on >= @from AND ordered_on < @to, or pin_code LIKE '411%'. A function or expression on the column, such as YEAR(ordered_on) = 2026 or amount * 1.18 > 1000, hides its values from the index, and a leading wildcard reads every row.

From the lesson Reading SQL Server execution plans
Why can comparing a varchar column with a number turn a seek into a scan?

Data-type precedence converts the type with lower precedence to the higher one, and int outranks varchar, so the column is converted for every row and the plan shows CONVERT_IMPLICIT with an index scan. Quoting the literal, or matching a parameter's type to the column, restores the seek.

From the lesson Reading SQL Server execution plans
What do Nested Loops and Hash Match each do?

Nested Loops joins two inputs by calling the inner one once per row of the outer; Hash Match builds a hash table from one input and probes it with the other. A plan is a tree where data flows from the leaves, the seeks and scans, towards the root.

From the lesson Reading SQL Server execution plans
What is parameter sniffing?

While compiling, the optimizer reads the parameter values of the first call and estimates from the statistics how many rows match; the plan cache then keeps that plan for every later call. Microsoft calls it parameter sensitivity and applies it to stored procedures, sp_executesql and prepared queries.

From the lesson Parameter sniffing, PSP and Query Store
When does one cached plan become expensive?

When the values differ a lot. In the lesson's 100,000-row table status = 'new' matches 10 rows and 'shipped' 99,000, and whichever call comes first decides the plan: the model reads 297,418 pages for the big value on the small value's plan, where a scan reads 1,695.

From the lesson Parameter sniffing, PSP and Query Store
Which fix for a sniffed plan comes before any hint?

Statistics and indexes: a covering index holds every column the query reads, so no key lookups remain and the plans stop differing, and current statistics keep the estimates honest. Microsoft advises completing both before any hint.

From the lesson Parameter sniffing, PSP and Query Store
What do OPTION (RECOMPILE), OPTIMIZE FOR a value and OPTIMIZE FOR UNKNOWN each do?

RECOMPILE compiles the statement on every call with that call's values and discards the plan afterwards. OPTIMIZE FOR a value compiles as if the parameter held the value you choose. OPTIMIZE FOR UNKNOWN, like a local variable, estimates from the column's average density, so every call gets one plan.

From the lesson Parameter sniffing, PSP and Query Store
What are PSP optimization and OPPO, and which levels do they need?

From compatibility level 160, parameter sensitive plan optimization can store a dispatcher plan that sends each call to a query variant chosen by the rows its value matches. From level 170, optional parameter plan optimization gives a search like column = @p OR @p IS NULL one variant for NULL and one for a value.

From the lesson Parameter sniffing, PSP and Query Store
What does Query Store keep, and where?

Each query, its plans and their runtime statistics per interval, inside the user database. It arrived in SQL Server 2016 and is on by default for databases created on SQL Server 2022 and later.

From the lesson Parameter sniffing, PSP and Query Store
Which two fixes change a query's plan without editing its code?

A Query Store hint, such as RECOMPILE, attached to a query by its query_id, from SQL Server 2022; and plan forcing, which pins a plan Query Store recorded for a query_id so the optimizer tries to use it at every compile.

From the lesson Parameter sniffing, PSP and Query Store
Which lock requests wait, and which are granted?

A shared lock to read is granted beside held S and U locks and waits for an X lock. An update lock waits for U and X. An exclusive lock waits for all three. A reader under locking READ COMMITTED releases its S lock as soon as the row is read; a writer holds its X lock until the transaction ends.

From the lesson Locking, RCSI and deadlocks in SQL Server
What does SET LOCK_TIMEOUT change, and what does it not?

How long the session waits for any lock; the default -1 waits for ever, and a wait past the limit ends the statement with error 1222 while the session and its transaction go on. It turns an endless wait into a handleable error but removes no cause.

From the lesson Locking, RCSI and deadlocks in SQL Server
How do READ_COMMITTED_SNAPSHOT and SNAPSHOT isolation differ?

With RCSI on, each statement of a reader sees the data as it was committed when that statement began. A SNAPSHOT transaction sees one committed view for its whole duration, and if it updates a row another transaction changed and committed after the snapshot began it ends with error 3960, an update conflict.

From the lesson Locking, RCSI and deadlocks in SQL Server
What can a NOLOCK read return?

A change that is later rolled back, a balance that never existed, and rows twice or not at all while others change them. It still waits for a schema stability lock, and it is ignored on the table an UPDATE or DELETE changes, which is locked as usual. So it never belongs where numbers must be right.

From the lesson Locking, RCSI and deadlocks in SQL Server
Which four habits make deadlocks rarer?

Access objects in the same order in every session, keep transactions short and in one batch with no waiting for a user inside them, avoid REPEATABLE READ and SERIALIZABLE where they are not needed because they hold read locks longer, and use row versioning so readers take no shared locks.

From the lesson Locking, RCSI and deadlocks in SQL Server
Where is the evidence of a deadlock that already happened?

In the xml_deadlock_report extended event, which the system_health session captures by default. Each report is a deadlock graph: the processes with their last statements, the resources each held and waited for, and which one was the victim.

From the lesson Locking, RCSI and deadlocks in SQL Server
What are the two parts of SQL Server 2025 optimized locking, and what must be on first?

Transaction ID locking labels each row with the last transaction that changed it, so a writer holds one lock on its transaction ID instead of a lock per row until it ends. Lock after qualification tests a row against the WHERE clause on its latest committed version before locking it, and needs RCSI. Accelerated database recovery must be on first.

From the lesson Locking, RCSI and deadlocks in SQL Server
How does a worker claim a queued job so that no job goes to two workers?

In one statement, with UPDLOCK to take an update lock as the row is read and hold it to the end of the transaction, READPAST to skip rows other transactions have locked, and ROWLOCK to keep the lock on the one row. Reading first and marking in a second statement leaves a gap in which READ COMMITTED keeps no lock.

From the lesson Locking, RCSI and deadlocks in SQL Server
In which order does SQL Server settle a permission?

A principal needs a right on each securable a statement touches. A right denied to any identity of the caller fails the check whatever else is granted; otherwise a grant to any of them passes it. GRANT gives, DENY takes away, REVOKE removes either.

From the lesson Security for T-SQL code: permissions and chains
How does an application reach tables it has no rights on?

GRANT EXECUTE ON SCHEMA::api lets its role run every procedure in that schema, now and later, with no right on any table, and ownership chaining skips the check on a table whose owner is the procedure's owner. The procedures become the only way in.

From the lesson Security for T-SQL code: permissions and chains
Where does the ownership chain stop?

At dynamic SQL inside a procedure: it starts a statement no chain covers, so it is checked for the caller. Granting the table to the role fixes that one call and opens the table to everything else the role does.

From the lesson Security for T-SQL code: permissions and chains
What does least privilege give each principal around database code?

The application's user: membership of a role with EXECUTE on the procedure schema, no table rights. A reporting user: EXECUTE on report procedures or SELECT on report views, no write rights. The migration login: the DDL rights a release needs, only while it runs. Support people: read access through a database role.

From the lesson Security for T-SQL code: permissions and chains
Why does no application belong in db_owner?

Microsoft's roles page warns that its members can change anything in the database and drop it, so every right beyond the few procedures the application runs is a right a bug or a stolen password can use.

From the lesson Security for T-SQL code: permissions and chains
What are the options when one procedure needs more than its caller holds?

Impersonation with EXECUTE AS, which runs the module as another user, or a module signature, which adds the rights of a certificate's user to that one procedure. The TRUSTWORTHY setting is off by default and should stay off.

From the lesson Security for T-SQL code: permissions and chains
Where do secrets and log details belong?

Secrets in a secret store, never in a script; SQL Server 2025 hashes SQL login passwords with PBKDF2 by default. Logs keep identifiers and error numbers, not the names or phone numbers of the people behind them.

From the lesson Security for T-SQL code: permissions and chains
What does SQL Server 2025's json type accept and refuse?

It stores documents in a binary format and accepts only a JSON object or array at the top level: a JSON scalar such as a bare string, an empty string, or text that is not JSON fails with error 13609. A duplicate key is not an error, the type keeps the first value, and the type works at every compatibility level.

From the lesson SQL Server 2025: JSON, regex and new functions
Which function reads one scalar from JSON, which reads a subtree, and which turns a document into rows?

JSON_VALUE returns one scalar from a path such as $.lang, JSON_QUERY returns an object or an array from a path, and OPENJSON returns rows and columns from a document.

From the lesson SQL Server 2025: JSON, regex and new functions
Which three things build JSON from rows?

FOR JSON turns a query's rows into JSON text, JSON_OBJECTAGG builds one object from many rows' keys and values, and JSON_ARRAYAGG builds one array from many rows' values.

From the lesson SQL Server 2025: JSON, regex and new functions
Which REGEXP function is a condition, and what do the others return?

REGEXP_LIKE returns true or false, so it goes where T-SQL expects a condition: WHERE, CASE WHEN or a CHECK constraint. REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR and REGEXP_COUNT return a value, and REGEXP_MATCHES and REGEXP_SPLIT_TO_TABLE return rows. All follow RE2.

From the lesson SQL Server 2025: JSON, regex and new functions
Which of the 2025 features need compatibility level 170?

Only REGEXP_LIKE, REGEXP_MATCHES and REGEXP_SPLIT_TO_TABLE. REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR and REGEXP_COUNT work at any level, as do the json and vector types, JSON_OBJECTAGG and JSON_ARRAYAGG; OPENJSON needs 130 or more.

From the lesson SQL Server 2025: JSON, regex and new functions
Which two string similarity functions does the lesson use, and what switches them on?

EDIT_DISTANCE and JARO_WINKLER_SIMILARITY, two of SQL Server 2025's four fuzzy string matching functions, which measure how alike two strings are; all four are previews and need the PREVIEW_FEATURES setting on.

From the lesson SQL Server 2025: JSON, regex and new functions
Name four smaller additions of SQL Server 2025.

The double-pipe operator joins strings, PRODUCT multiplies a column's values, CURRENT_DATE returns today's date, UNISTR turns escapes into characters, BASE64_ENCODE writes binary as text, and SUBSTRING may leave out its length.

From the lesson SQL Server 2025: JSON, regex and new functions
What does the lesson's import do with a row whose number or code does not match its pattern?

It keeps the row with the reason it failed rather than guessing a value: digits that do not make a mobile number, a vehicle code with two digits where four belong, and a preferences value that is a JSON string instead of an object are all reported, not repaired.

From the lesson SQL Server 2025: JSON, regex and new functions
What is a tSQLt test, and how is it run?

A stored procedure whose name starts with test, grouped in a schema that tSQLt calls a test class. EXEC tSQLt.Run 'withdraw_tests' runs one class and EXEC tSQLt.RunAll every class in the database.

From the lesson Testing T-SQL: tSQLt, CI and safe deployments
Why does a tSQLt test need no cleanup?

Tests run within transactions and tSQLt rolls each one back at the end of the test, fakes included, so the real tables end exactly as they began.

From the lesson Testing T-SQL: tSQLt, CI and safe deployments
What does tSQLt.FakeTable do, and what follows from it?

It swaps a table for an empty copy without its constraints, so the test sees what the procedure itself checks rather than what a CHECK constraint refuses. tSQLt.ApplyConstraint puts one constraint of the real table back when the test needs it.

From the lesson Testing T-SQL: tSQLt, CI and safe deployments
Which tSQLt procedures assert a value, a table and an error?

tSQLt.AssertEquals fails unless two values are equal, tSQLt.AssertEqualsTable compares two tables row by row, and tSQLt.ExpectException fails unless an error follows it. tSQLt.SpyProcedure replaces a called procedure with one that logs its calls.

From the lesson Testing T-SQL: tSQLt, CI and safe deployments
What runs before each test of a class?

A procedure called SetUp in that class, if there is one.

From the lesson Testing T-SQL: tSQLt, CI and safe deployments
How do database tests run in CI?

Every pull request starts a job on a hosted runner with a disposable SQL Server container as a service: the job deploys the schema and procedures, installs tSQLt, runs tSQLt.RunAll and fails when any test fails. The job takes the server's password from the repository's secret store, and the server is gone when the job ends.

From the lesson Testing T-SQL: tSQLt, CI and safe deployments
Where should tSQLt never be installed?

In production: tSQLt's own quick start says so, which is also why CI uses a server that exists for one job and no test reaches a shared server.

From the lesson Testing T-SQL: tSQLt, CI and safe deployments
What makes a release script safe to run again after it stopped halfway?

Each change checks first: CREATE OR ALTER for modules, DROP ... IF EXISTS, and guarded ALTER TABLE statements. SQL database projects keep the schema as code and build a dacpac, which SqlPackage compares with a database before changing anything.

From the lesson Testing T-SQL: tSQLt, CI and safe deployments
In which order does a T-SQL code review read a procedure?

In the order its failures cost: errors and transactions, sets and order, concurrency, security, performance, then tests.

From the lesson T-SQL code review and interview drills
Why does one passing call prove so little?

It cannot show what a failure halfway through leaves behind, what two callers at once do to the same account, or who can reach the tables without the procedure. Most findings appear only when a statement fails, a second session runs, a customer holds two accounts or a caller sends unexpected text.

From the lesson T-SQL code review and interview drills
What does the review's errors and transactions row ask for?

SET XACT_ABORT ON, a CATCH block that rolls back and re-raises rather than committing and printing, and nothing left half committed.

From the lesson T-SQL code review and interview drills
What does the review's concurrency row look for?

No NOLOCK on values the code acts on, and locks kept from the read until the write, so two sessions cannot spend one balance twice.

From the lesson T-SQL code review and interview drills
What does the review's security row check?

That values are passed as parameters rather than pasted into dynamic SQL, and that callers hold EXECUTE on the procedures instead of rights on the tables, so nobody can step around the procedure's rules.

From the lesson T-SQL code review and interview drills
Which two set-and-order findings does the checklist name?

A loop where one statement would do, and a TOP without an ORDER BY beside it, which takes whichever of a customer's accounts the engine reaches first.

From the lesson T-SQL code review and interview drills
Why is 0 findings from a pattern-based checker not the end of a review?

The checker knows a fixed set of patterns and is built on patterns rather than a T-SQL parser, so every line it names is a place to look and everything it does not know is still yours to read. Follow the whole checklist and use the checker so the known patterns are never missed.

From the lesson T-SQL code review and interview drills
What shape does a strong answer to an interview drill have, and which sources can you trust?

The failure first, reproduced, then the fix, then the test that proves it. Trust the vendor's documentation plus a run on your own lab; check an answer written for an old release against the current page; a list of questions said to be asked at a company is unverified, often leaked or copied.

From the lesson T-SQL code review and interview drills
Which sections does a PL/SQL block have, and which one is compulsory?

DECLARE names the block's constants and variables, BEGIN opens the executable statements, EXCEPTION lists the handlers, and END closes it. Only the executable section is compulsory, so BEGIN NULL; END; is a complete block. Whatever a block declares disappears when it completes.

From the lesson PL/SQL blocks, variables and data types
Why does a PL/SQL block need a line holding only a slash?

Semicolons already end the statements inside the block, so one cannot also end the block: the slash marks it complete and sends it, and a second slash runs the most recent block again. A slash after a SQL statement that already ended with a semicolon runs that statement a second time.

From the lesson PL/SQL blocks, variables and data types
What does a declared variable hold before anything assigns to it?

NULL, so adding 1 to it leaves NULL with no error. := and DEFAULT both give a first value, and a block assigns it again every time control enters the block. A CONSTANT and a NOT NULL variable must be given one or they do not compile, and a zero-length string is NULL.

From the lesson PL/SQL blocks, variables and data types
What does = do in PL/SQL?

It only compares. v_total = v_total + 100; is not a statement and the block does not compile; assignment is v_total := v_total + 100;.

From the lesson PL/SQL blocks, variables and data types
What do %TYPE and %ROWTYPE copy, and what do they not?

%TYPE copies a column's or variable's data type and size, and its constraints unless the item is a column; %ROWTYPE declares a record with one field per column, with the same names and types. Neither copies a first value, and neither brings a column's NOT NULL, so NULL can be assigned without complaint.

From the lesson PL/SQL blocks, variables and data types
What does PLS_INTEGER buy, and what does it cost?

It holds whole numbers from -2,147,483,648 to 2,147,483,647 in 32 bits and uses the machine's own arithmetic, so it is faster than NUMBER and needs less storage. A calculation that leaves that range raises ORA-01426, numeric overflow, even when the result goes into a NUMBER; INTEGER, a NUMBER subtype, carries on.

From the lesson PL/SQL blocks, variables and data types
What does VARCHAR2(10) count, and how do you make it count characters?

Bytes, unless the declaration says CHAR; with no word at all the session's NLS_LENGTH_SEMANTICS decides and its default is BYTE. Oracle's globalization guide advises declaring lengths in characters, as VARCHAR2(100 CHAR). A PL/SQL VARCHAR2 still never holds more than 32,767 bytes.

From the lesson PL/SQL blocks, variables and data types
Why does a block's DBMS_OUTPUT appear only when the block ends?

PUT_LINE adds the line to a buffer in your session, and Oracle says messages are not sent until the block or subprogram completes, with no way to flush them sooner. SET SERVEROUTPUT ON enables it with no size limit; if nobody reads the buffer, as for a job an application starts, the lines are lost.

From the lesson PL/SQL blocks, variables and data types
How does IF ... ELSIF ... ELSE choose a branch, and where does a NULL value land?

Oracle tests the conditions from the top, runs the first branch whose condition is TRUE and runs ELSE only when none was. Comparing a value with NULL gives neither TRUE nor FALSE, so an unpriced order fails every test and reaches ELSE. The keyword is ELSIF, one word without a second E.

From the lesson PL/SQL control structures and iterators
What happens when nothing matches a CASE, and there is no ELSE?

A CASE expression gives NULL; a CASE statement raises the predefined exception CASE_NOT_FOUND, ORA-06592. A handler for CASE_NOT_FOUND in the block's exception section can stand in place of the ELSE.

From the lesson PL/SQL control structures and iterators
How do a simple and a searched CASE differ, and why does a NULL selector need the searched form?

A simple CASE compares one selector with the value after each WHEN; a searched CASE has no selector and tests a full condition. In both, the first match wins and nothing falls through. A NULL selector never matches WHEN NULL, because NULL equals nothing, not even NULL, so use WHEN kind IS NULL.

From the lesson PL/SQL control structures and iterators
What are PL/SQL's three kinds of loop?

A basic LOOP repeats until an EXIT, an EXIT WHEN or an error ends it. WHILE tests its condition before every pass. FOR declares its own index, a PLS_INTEGER the body may read but never assign, counts up or down with REVERSE, and forgets the index after END LOOP.

From the lesson PL/SQL control structures and iterators
What do CONTINUE WHEN, EXIT WHEN and a loop label each do?

CONTINUE WHEN ends one pass early, EXIT WHEN ends the loop, and a label lets an inner loop leave an outer one: EXIT rows WHEN ... ends the loop named rows, where a plain EXIT would leave only the inner loop.

From the lesson PL/SQL control structures and iterators
Which EXIT WHEN never fires?

One that tests a variable the loop body never changes: the loop then runs until the session is stopped. Change the tested value on every pass, or use FOR, whose range ends the loop by itself.

From the lesson PL/SQL control structures and iterators
What did Oracle Database 21c add to the FOR loop?

Iteration controls that produce the index values: a stepped range such as 1 .. 10 BY 3, several controls separated by commas running one after another in one loop, a WHEN filter that skips values whose condition is not TRUE, a declared index type for fractional steps, and INDICES OF, VALUES OF and PAIRS OF for collections.

From the lesson PL/SQL control structures and iterators
What did 26ai add to the simple CASE statement, and what may 19c code use?

A dangling predicate, a comparison with its left side missing such as < 0, and a choice list that puts several values after one WHEN. Code that must also run on 19c can use neither those nor the 21c iterators.

From the lesson PL/SQL control structures and iterators
How many rows must a SELECT INTO find?

Exactly one. No row raises NO_DATA_FOUND, ORA-01403, whose SQLCODE inside a handler is +100 where Oracle's other errors are negative. Two or more raise TOO_MANY_ROWS, ORA-01422, with SQLCODE -1422, and Oracle leaves the variables undefined.

From the lesson SQL inside PL/SQL: SELECT INTO and DML
What is the safe way to ask whether a row exists?

SELECT COUNT(*) INTO, because a count always returns exactly one row. Handle NO_DATA_FOUND where a missing row is normal, and read TOO_MANY_ROWS as a sign that the WHERE clause does not pin one row.

From the lesson SQL inside PL/SQL: SELECT INTO and DML
Why does IF SQL%NOTFOUND THEN never run after a SELECT INTO that found nothing?

The exception comes first: NO_DATA_FOUND is raised before the next line. Use a NO_DATA_FOUND handler, or a COUNT(*) query.

From the lesson SQL inside PL/SQL: SELECT INTO and DML
What are the implicit cursor's attributes, and how far back do they reach?

SQL%ROWCOUNT counts the rows the statement returned or changed, SQL%FOUND is TRUE when that is above 0, SQL%NOTFOUND is its opposite and SQL%ISOPEN is always FALSE. The first three describe the most recent statement only and are NULL before the first one.

From the lesson SQL inside PL/SQL: SELECT INTO and DML
Why copy SQL%ROWCOUNT into a variable at once?

Because the next SQL statement replaces it, and even a procedure call may run SQL: a SELECT SUM(...) INTO after an UPDATE of 2 rows leaves the count at 1. An UPDATE that matches nothing is not an error either, it just leaves the count at 0, so code that must change a row has to check.

From the lesson SQL inside PL/SQL: SELECT INTO and DML
Which SQL statements can a block run directly, with variables in them?

SELECT, INSERT, UPDATE, DELETE, MERGE, COMMIT, ROLLBACK and SAVEPOINT, as static SQL, with PL/SQL variables wherever the SQL could take a bind value. DDL such as CREATE TABLE is not static SQL and goes through EXECUTE IMMEDIATE.

From the lesson SQL inside PL/SQL: SELECT INTO and DML
What does RETURNING INTO save you?

A second query: it returns values of the changed row, such as a new identity value, to variables in the same INSERT, UPDATE, DELETE or MERGE, one variable per returned value. RETURNING ... BULK COLLECT INTO does the same for many rows, into collections.

From the lesson SQL inside PL/SQL: SELECT INTO and DML
Which release lets a query leave out its FROM clause?

Release 23 and later, so Oracle AI Database 26ai accepts SELECT CURRENT_DATE;. Oracle Database 19c needs FROM DUAL, so scripts shared with 19c keep it.

From the lesson SQL inside PL/SQL: SELECT INTO and DML
Which handler runs when a statement fails, and where does control go afterwards?

The block stops at the failing statement and the first handler that names the exception runs; WHEN a OR b names two, and WHEN OTHERS catches what no earlier WHEN names and must come last, or the block does not compile (PLS-00370). Control then goes to the end of that block, never back to the failing statement.

From the lesson PL/SQL exceptions and how they propagate
Which Oracle errors do the commonest predefined exceptions name?

NO_DATA_FOUND is ORA-01403, ZERO_DIVIDE is ORA-01476, VALUE_ERROR is ORA-06502 and DUP_VAL_ON_INDEX is ORA-00001.

From the lesson PL/SQL exceptions and how they propagate
What do SQLCODE and SQLERRM give you inside a handler?

SQLCODE returns the error's code, -1476 for a division by zero, and SQLERRM its message.

From the lesson PL/SQL exceptions and how they propagate
How do you keep the statements after a risky one running?

Put the risky statement in an inner BEGIN ... EXCEPTION ... END; block, whose handler ends only that block. A handler never returns to the failing statement, so one block holding both the risky statement and the work after it loses the rest.

From the lesson PL/SQL exceptions and how they propagate
Where does an exception go when its own block has no handler for it?

It leaves the block and is raised again in the enclosing block, then in the caller, at the call, one level at a time until a handler names it. With none, SQL*Plus or SQLcl prints the error with ORA-06512 lines naming each place it passed.

From the lesson PL/SQL exceptions and how they propagate
What is a user-defined exception, and how does a handler catch an Oracle error by name?

e_limit EXCEPTION; declares a name raised only by your own RAISE e_limit;. PRAGMA EXCEPTION_INIT ties such a name to an error number, so a handler can catch that numbered error by name.

From the lesson PL/SQL exceptions and how they propagate
What does RAISE_APPLICATION_ERROR raise, and what does a bare RAISE do?

Called from a stored procedure or function, RAISE_APPLICATION_ERROR raises an application error: a number from -20000 to -20999 with your own message. RAISE; inside a handler raises the exception being handled again.

From the lesson PL/SQL exceptions and how they propagate
What does PLSQL_WARNINGS do for exception handling?

It switches on compile-time warnings about stored code that may behave wrongly, such as a handler that hides errors. An exception can also start in a declaration's initial value or inside a handler, which changes who can catch it.

From the lesson PL/SQL exceptions and how they propagate
What are an explicit cursor's four moves?

DECLARE ties the name to a query, OPEN runs it, FETCH ... INTO reads one row into variables, and CLOSE releases its resources. EXIT WHEN c%NOTFOUND; is the first line after the FETCH.

From the lesson Explicit cursors and cursor FOR loops
Why must the FETCH come before the %NOTFOUND test?

The test reads the previous pass's fetch. On the first pass %NOTFOUND is NULL, so an IF before any fetch does not exit, and at the end the body runs once more after the fetch that returned no row.

From the lesson Explicit cursors and cursor FOR loops
What do a cursor's four attributes read in each state?

Not open: %ISOPEN FALSE and the other three raise INVALID_CURSOR. Open with no fetch yet: TRUE, NULL, NULL and 0. After a fetch that returned a row: TRUE, TRUE, FALSE and the rows fetched so far; after one that returned nothing: TRUE, FALSE, TRUE and the same count.

From the lesson Explicit cursors and cursor FOR loops
What does %ROWCOUNT count, and what does a fetch from a closed cursor raise?

The rows the cursor has fetched so far, not the rows the query will return: after the fourth of nine fetches it reads 4. A fetch from a closed cursor, like reading its attributes, raises INVALID_CURSOR, ORA-01001.

From the lesson Explicit cursors and cursor FOR loops
What are the rules for a cursor's parameters?

They behave like IN parameters and may have defaults, so one cursor serves many accounts. Their datatype cannot have constraints: no NOT NULL, no string length, no precision or scale. The query reads its parameters and variables when the cursor opens, so a new value needs CLOSE and OPEN again.

From the lesson Explicit cursors and cursor FOR loops
What does a cursor FOR loop write for you, and what does it cost?

It declares its record as c%ROWTYPE, opens the cursor, fetches one row per pass, exits when the result is exhausted and closes the cursor. An exception inside it closes the cursor before the handler runs, so the attributes are not available there; the inline form hides them altogether.

From the lesson Explicit cursors and cursor FOR loops
Why do fetch calls matter, and what does fetch_counts.sql measure?

Each fetch call crosses between the PL/SQL and SQL engines, so fewer calls mean less work even for the same rows. An explicit loop over 1,002 rows runs 1,003 fetch statements; the script reads the FETCHES column of V$SQL for each loop shape, so the counts come from the database rather than from a rule.

From the lesson Explicit cursors and cursor FOR loops
What does FOR UPDATE lock, and what ends it?

OPEN locks every row the result set contains, and UPDATE or DELETE ... WHERE CURRENT OF c changes exactly the row the cursor last fetched, with no second WHERE clause to keep in step. A COMMIT or ROLLBACK releases the locks, and the next FETCH then raises ORA-01002, fetch out of sequence, so the COMMIT belongs outside the loop.

From the lesson Explicit cursors and cursor FOR loops
What does %ROWTYPE give you, and when does the table change?

A record with one field per column of the table, so r.sku and r.unit_price exist with no type written by hand. SELECT * INTO r copies the row, so changing a field leaves the table as it was: the table changes only when a statement writes the record back.

From the lesson PL/SQL records and collections
What does a new %ROWTYPE record hold?

Every field NULL: a %ROWTYPE record inherits neither the default value nor the NOT NULL rule of a column.

From the lesson PL/SQL records and collections
Which two statements write a whole record back to its table?

INSERT INTO stock VALUES r, where the record is the whole VALUES clause, and UPDATE stock SET ROW = r WHERE ..., where it supplies every column. The record must represent a row of the table, so a %ROWTYPE record fits and a record of two chosen fields does not.

From the lesson PL/SQL records and collections
Where may a record not stand?

Nowhere but the whole VALUES clause, the right of SET ROW, or a RETURNING ... INTO: never in a select list or a WHERE, GROUP BY or ORDER BY clause. ROW belongs only on the left of a SET, never with a subquery, and takes one SET clause; EXECUTE IMMEDIATE does not support record statements.

From the lesson PL/SQL records and collections
Why does INSERT INTO accounts VALUES a fail for an accounts%ROWTYPE record?

account_id is a GENERATED ALWAYS identity column, and assigning a value to one in an INSERT or UPDATE returns an error (ORA-32795, ORA-32796). Name the other columns and leave the identity column out.

From the lesson PL/SQL records and collections
Which three properties separate the kinds of collection?

Whether it is sparse, so its indexes can have gaps; whether it needs initialising, that is whether it is null (does not exist yet) rather than empty (exists with no elements); and whether it is bounded, so its type declares a maximum.

From the lesson PL/SQL records and collections
What is special about an associative array?

It is a set of key-value pairs indexed by a string or a PLS_INTEGER, its keys come back in sort order rather than the order you added them, and it is empty rather than null from the moment it is declared. It can be sparse, it is unbounded, and its type can live only in a block or a package.

From the lesson PL/SQL records and collections
How do a nested table and a varray differ?

A nested table is indexed from 1 with no maximum: it starts dense and becomes sparse when elements are deleted. A varray is indexed from 1 up to a declared maximum and is always dense. Both are null until a constructor initialises them, and both can be created at schema level, so a table column can hold one.

From the lesson PL/SQL records and collections
What does every SQL statement in a PL/SQL block cost?

One hand-over between the two engines: the block hands a request to the SQL engine and takes the answer back. A fetch loop pays it per row, and Oracle's bulk SQL page notes the saving from doing the work in one call begins to pay off for four rows or more.

From the lesson BULK COLLECT with LIMIT
What are the two halves of bulk SQL?

BULK COLLECT returns rows from SQL to PL/SQL in batches, and FORALL sends DML the other way.

From the lesson BULK COLLECT with LIMIT
How many trips to the SQL engine does each form make for 100,000 rows?

A FETCH ... INTO loop one row at a time: 100,001. BULK COLLECT INTO with no cap: 1. FETCH BULK COLLECT INTO with LIMIT 1,000: 101. A larger LIMIT means fewer trips and more rows held in memory at once.

From the lesson BULK COLLECT with LIMIT
What does SELECT ... BULK COLLECT INTO do when the query finds nothing?

Nothing: it raises no NO_DATA_FOUND, so the code must check COUNT before it reads index 1. The whole-result form fits lookups whose row count is small by design.

From the lesson BULK COLLECT with LIMIT
What is the long-table form of a bulk fetch?

FETCH ... BULK COLLECT INTO ... LIMIT n: a cursor opened once, the batch handled inside the loop, the loop left when the cursor is empty. Without a cap, BULK COLLECT holds every row in session memory.

From the lesson BULK COLLECT with LIMIT
Where may the exit of a LIMIT loop stand, and what happens if it stands first?

Last in the body, as EXIT WHEN c%NOTFOUND, so the test runs on a batch already written; or first, as EXIT WHEN v_rows.COUNT = 0, at the cost of one more empty fetch. Testing %NOTFOUND before the body skips the last short batch.

From the lesson BULK COLLECT with LIMIT
How does a DML statement hand back every row it changed?

With RETURNING ... BULK COLLECT INTO, which fills collections with the columns of every changed row in the same statement.

From the lesson BULK COLLECT with LIMIT
Does a cursor FOR loop already fetch in batches?

Yes, at the default optimize level, which the module's first lesson measures in V$SQL. BULK COLLECT with LIMIT adds control over the batch size and the memory it costs.

From the lesson BULK COLLECT with LIMIT
What is FORALL, despite looking like a loop?

One statement: it binds collections to a single INSERT, UPDATE, DELETE or MERGE and sends it to the SQL engine in one call, which runs it once for each index in the bounds. It is the sending half of bulk SQL, where BULK COLLECT is the fetching half.

From the lesson FORALL, SAVE EXCEPTIONS and DML error logging
What are FORALL's three ways of picking elements?

A range such as 1 .. v_feed.COUNT, which needs every index of the range, so a deleted element raises an exception; INDICES OF, which takes only the elements that exist, for a collection with gaps; and VALUES OF, which runs the elements whose numbers another collection holds.

From the lesson FORALL, SAVE EXCEPTIONS and DML error logging
Which limits does Oracle set on a FORALL?

One DML statement per FORALL, the index only as a subscript and never inside an expression, and the same indexes in every collection the statement reads.

From the lesson FORALL, SAVE EXCEPTIONS and DML error logging
Which form comes first, and when does FORALL earn its place?

When SQL can compute every column, one INSERT ... SELECT wins: in the lesson's copy of 200,000 rows it is 1 call against 200 for batches, and no row travels to PL/SQL and back. FORALL is for rows that need PL/SQL first: a lookup, a calculation in several steps or a procedure call.

From the lesson FORALL, SAVE EXCEPTIONS and DML error logging
What happens to a FORALL without SAVE EXCEPTIONS when one element fails?

It stops at that element, and the elements after it never run. With no handler, PL/SQL rolls back the rows of the earlier elements too; with a handler, only the failing element is undone and the rows before it stay in the transaction, counted by SQL%ROWCOUNT.

From the lesson FORALL, SAVE EXCEPTIONS and DML error logging
What does the SAVE EXCEPTIONS clause change?

It lets the FORALL carry on past failing elements and deal with all the failures once it ends, so one run finds every bad line instead of the first.

From the lesson FORALL, SAVE EXCEPTIONS and DML error logging
Which two attributes report on a bulk DML statement?

SQL%BULK_EXCEPTIONS describes the elements that failed, and SQL%BULK_ROWCOUNT counts the rows each element changed.

From the lesson FORALL, SAVE EXCEPTIONS and DML error logging
What does DML error logging do for a single SQL statement?

A LOG ERRORS clause on one INSERT, UPDATE, MERGE or DELETE sends each refused row to an error logging table, so the statement can carry on instead of stopping.

From the lesson FORALL, SAVE EXCEPTIONS and DML error logging
How do CREATE OR REPLACE and IF NOT EXISTS differ?

OR REPLACE re-creates and recompiles an existing unit and its granted privileges survive, so your script's version always wins. IF NOT EXISTS leaves the original source in place without an error, so the first deployment's version survives. The two clauses cannot be combined, and Oracle prints the same created message either way.

From the lesson PL/SQL procedures, functions and parameters
What does a scalar OUT parameter hold on entry?

The default of its type, NULL for a number or a string, whatever the caller's variable held. Oracle recommends the subprogram assign every OUT parameter itself.

From the lesson PL/SQL procedures, functions and parameters
When is the caller's variable certain to hold an OUT parameter's value?

Only after a call that ends normally: Oracle passes OUT and IN OUT by value, so the final value becomes the caller's value when the call returns, and after an unhandled exception the caller's value is documented as undefined.

From the lesson PL/SQL procedures, functions and parameters
What is NOCOPY, and why must logic never depend on it?

A hint asking the compiler to pass an OUT or IN OUT parameter by reference to avoid copying a large collection. The compiler decides silently at each invocation; if it obeys, an assignment shows in the caller's variable at once even when an exception follows, and one global passed twice is reachable under two names.

From the lesson PL/SQL procedures, functions and parameters
What do defaults and named notation give a subprogram's callers?

A parameter with a default may be left out, so adding one does not break an existing call. Named notation writes name => value, so each value carries its name and any defaulted parameter can be skipped. In mixed notation the positional arguments come first, and once one is named every later one is named too.

From the lesson PL/SQL procedures, functions and parameters
What may be overloaded, and what may not?

Package subprograms, nested subprograms and type methods, when the parameters differ in name, number, order or data type family. Not standalone subprograms, not parameters differing only in mode or only in a subtype of one family, not functions differing only in return type. An ambiguous call raises PLS-00307.

From the lesson PL/SQL procedures, functions and parameters
Which purity rules must a function obey to be callable from SQL?

From a SELECT it cannot modify tables. From INSERT, UPDATE, DELETE or MERGE it cannot query or modify the tables that statement changes, or ORA-04091 follows. From any of them it cannot COMMIT, ROLLBACK, run DDL or session control statements unless it declares PRAGMA AUTONOMOUS_TRANSACTION.

From the lesson PL/SQL procedures, functions and parameters
What does DETERMINISTIC promise, and who checks it?

That the function returns the same value for the same arguments, has no side effects and raises no unhandled exception. Oracle does not check it: it may reuse earlier results, so a false promise silently produces wrong values. It is required for a function-based index, a virtual column, or a materialized view using query rewrite or fast refresh.

From the lesson PL/SQL procedures, functions and parameters
What does a cursor variable hold?

A pointer to a SQL work area, so it is not limited to one query, can be assigned, and can be a subprogram parameter or a host variable of a client program. Cursor variables and explicit cursors are not interchangeable: neither can stand where the other is expected.
What does OPEN rc FOR do, and when are the query's variables read?

It runs the query, identifies its result set and places the cursor before the first row. The variables in the query are read at that moment, so changing them afterwards does not change the rows.
A function returns SYS_REFCURSOR and ends. What happens to the result set?

The function's own variable disappears, but the work area stays open while any cursor variable points to it, here the caller's. The session's open-cursor count rises at OPEN and stays up until the receiver closes it.
Why must a function not close the cursor it returns?

The caller would receive a pointer to a closed work area, and a PL/SQL caller's first FETCH raises INVALID_CURSOR. Return it open; the code that reads the rows closes it.
How does each kind of caller read a returned cursor variable?

FETCH rc INTO in PL/SQL, PRINT rc in SQL*Plus, or a client driver's fetch loop, where python-oracledb asks for the result type DB_TYPE_CURSOR. The receiver owns the close, and SQL*Plus closes the variable once PRINT has read the last row, so a second PRINT needs the function to run again.
Which parameter mode does a procedure use to hand a result set back?

IN OUT, when the procedure itself opens the cursor variable.
How do a strong and a weak REF CURSOR type differ?

A strong type names the row its queries return, so the compiler checks each fetch; a weak type such as SYS_REFCURSOR accepts any query.
What does DBMS_SQL.RETURN_RESULT do, and which error follows a leaked cursor?

It sends an open cursor to the client as an implicit result set, with no OUT parameter, as a T-SQL procedure's SELECT does. A returned cursor stays open until its receiver closes it, and a session past its OPEN_CURSORS limit fails with ORA-01000.
What does each of a package's two pieces hold?

The specification declares what callers may use, carrying everything a caller needs and nothing about how it works; the body defines those items and hides the private helpers and variables. The body's headings must repeat the declarations word for word apart from white space, or it compiles with PLS-00323.

From the lesson PL/SQL packages, session state and ORA-04068
What happens when a caller names an item declared only in the body?

It does not compile: a private helper or variable cannot be reached from outside. Oracle's writing guidelines ask for exactly that, declaring only the items invoking programs must see, with procedures in place of variables.

From the lesson PL/SQL packages, session state and ORA-04068
What is a package's state, and how much of it does one session see?

The values of the variables, constants and cursors it declares in either piece, and one variable is enough to make a package stateful. It is session state: every session that references a package item gets its own instantiation with its own copy, so two clients keep two counters.

From the lesson PL/SQL packages, session state and ORA-04068
What does instantiating a package do, and how often?

Oracle assigns the initial values of the public constants, then of the public variables that declare one, then runs the body's initialisation part, the block between BEGIN and END at its foot. That happens on the session's first reference, not on every call.

From the lesson PL/SQL packages, session state and ORA-04068
What is ORA-04068, and what did the call that reported it do?

Existing state of packages has been discarded: the body of an instantiated, stateful package was recompiled, so that package's next call in each holding session throws away the session state. That call does no work, and a second identical call runs normally against a freshly initialised package.

From the lesson PL/SQL packages, session state and ORA-04068
What does the RESETTABLE clause offer?

From 26ai Release Update 23.26.0, a session may discard such a unit's state itself, without ORA-04068.

From the lesson PL/SQL packages, session state and ORA-04068
What does ACCESSIBLE BY do?

It lists the units allowed to call a package or one subprogram; any other caller is refused with PLS-00904.

From the lesson PL/SQL packages, session state and ORA-04068
What does PRAGMA SERIALLY_REUSABLE change about state?

It keeps the state for one server call rather than for the session, in a shared pool.

From the lesson PL/SQL packages, session state and ORA-04068
What does SQLERRM tell you, and what does it leave out?

The error's message, such as ORA-02290 naming the broken check constraint, and nothing about where it happened. It returns at most 512 bytes, so Oracle recommends DBMS_UTILITY.FORMAT_ERROR_STACK, which returns the whole error stack up to 2000 bytes.

From the lesson Error logging, backtraces and autonomous logs
What does DBMS_UTILITY.FORMAT_ERROR_BACKTRACE return?

One ORA-06512 line for each frame, from the statement that failed up to the unit holding the handler. It is for reading inside a handler: the function returns NULL when no error is being handled.

From the lesson Error logging, backtraces and autonomous logs
Which end do UTL_CALL_STACK's backtrace indexes start from?

Index 1 is the origin, the frame of the statement that raised the error, and the highest index, BACKTRACE_DEPTH, is the unit that examines the backtrace, the handler's own unit. So BACKTRACE_LINE(1) is the failing line, in the same order as the FORMAT_ERROR_BACKTRACE text, which starts at the origin too.

From the lesson Error logging, backtraces and autonomous logs
How many frames does a backtrace have, and what is the depth outside a handler?

It ends where it is examined, so the same error seen by a handler one level down has one frame fewer. With no error being handled the depth is 0. The call stack counts the same way: dynamic depth 1 is the subprogram running now.

From the lesson Error logging, backtraces and autonomous logs
What happens to a log row the handler wrote in the caller's transaction?

The caller's ROLLBACK removes it together with everything else the caller changed, so the failure leaves no trace. A row written in a transaction of its own is kept, because it committed on its own.

From the lesson Error logging, backtraces and autonomous logs
What does PRAGMA AUTONOMOUS_TRANSACTION give a logger?

A transaction of its own, so its COMMIT keeps its row whatever the caller does later.

From the lesson Error logging, backtraces and autonomous logs
Which two errors wait for a careless autonomous routine?

ORA-06519 when it returns without a COMMIT or ROLLBACK, and ORA-00060 when it updates a row its caller has locked, because it deadlocks with its own caller.

From the lesson Error logging, backtraces and autonomous logs
What may a log row hold, and what may it never hold?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Error logging, backtraces and autonomous logs
Where does Oracle record which object depends on which?

In USER_DEPENDENCIES, ALL_DEPENDENCIES and DBA_DEPENDENCIES: each row gives the dependent object and its type and the referenced object and its type. CREATE statements update these on their own.

From the lesson Dependencies, compilation and warnings
Why is a unit that reaches a table only through EXECUTE IMMEDIATE never marked stale?

Dynamic SQL creates no dependencies, so Oracle records nothing to invalidate when the table changes.

From the lesson Dependencies, compilation and warnings
Which states can an object be in, and how do the dictionary views show them?

Valid; compiled with errors; invalid because something it references changed; and unauthorized because a privilege was revoked. The OBJECTS views show the last three all as INVALID.

From the lesson Dependencies, compilation and warnings
What does INVALID actually mean for a PL/SQL unit?

That the compiled form may no longer match what the unit references, not that the code is wrong. On its next use Oracle recompiles it if a referenced object changed in a way that affects it, and otherwise revalidates it without recompiling.

From the lesson Dependencies, compilation and warnings
How do coarse-grained and fine-grained invalidation differ, and what cascades?

Coarse-grained invalidation marks every dependent of the changed object; fine-grained marks only the dependents that rely on the changed attribute or whose compiled metadata is no longer correct. A change that invalidates one object also invalidates that object's dependents, which Oracle calls cascading invalidation.

From the lesson Dependencies, compilation and warnings
Where are the errors of a recompile that failed?

The unit stays INVALID and the errors are in USER_ERRORS and in SHOW ERRORS. A package that is invalidated and revalidated also loses its instantiation in every session that held it.

From the lesson Dependencies, compilation and warnings
What are the three categories of PL/SQL compiler warning?

SEVERE for conditions that might cause wrong results, such as aliasing problems with parameters; PERFORMANCE for conditions that might slow the code, such as passing a VARCHAR2 value to a NUMBER column; and INFORMATIONAL for untidy code, such as code that can never run. Each message code has the form PLW-nnnnn.

From the lesson Dependencies, compilation and warnings
What can PLSQL_WARNINGS be set for, and where do warnings appear?

For the instance, the session or one stored unit. Warnings are read where errors are: in USER_ERRORS and with SHOW ERRORS.

From the lesson Dependencies, compilation and warnings
How often does each level of DML trigger fire?

A row trigger, written FOR EACH ROW, fires once for every affected row, so an UPDATE matching no row fires it 0 times. A statement trigger, written without FOR EACH ROW, fires once for the statement whatever its row count, even when it changes nothing.

From the lesson Oracle DML triggers: row, statement, INSTEAD OF
What are :NEW and :OLD, and which triggers may write to them?

Pseudorecords with a field per column: :OLD holds the row before the change and :NEW after it. An INSERT leaves :OLD null, a DELETE leaves :NEW null, an UPDATE fills both. Only a row trigger sees them, and only a BEFORE row trigger may assign to :NEW; an AFTER row trigger may only read.

From the lesson Oracle DML triggers: row, statement, INSTEAD OF
What does a WHEN clause do, and what is its one syntax trap?

It is a condition checked before the body runs, so the body runs only for rows that match. Inside WHEN the correlation names are NEW and OLD without a colon: WHEN (:NEW.unit_price IS NOT NULL) does not compile.

From the lesson Oracle DML triggers: row, statement, INSTEAD OF
What do INSERTING, UPDATING and DELETING report?

Which statement fired a trigger defined for more than one event. UPDATING('UNIT_PRICE') is true when that column is in the statement's SET list, whatever the row count, because the predicate reflects the statement and not the row.

From the lesson Oracle DML triggers: row, statement, INSTEAD OF
What does an error raised inside a trigger do to the statement that fired it?

It ends it: a RAISE_APPLICATION_ERROR in the trigger makes the whole UPDATE fail, so no row is changed.

From the lesson Oracle DML triggers: row, statement, INSTEAD OF
What does FOLLOWS guarantee?

The order of two triggers on the same table and timing point: the trigger carrying FOLLOWS runs after the one it names. Without it the order of triggers of the same type is not guaranteed, which turns a dependency between two triggers into a bug that appears only sometimes.

From the lesson Oracle DML triggers: row, statement, INSTEAD OF
How does a view that joins two tables accept an INSERT?

Through an INSTEAD OF trigger, defined FOR EACH ROW and only on a view: it runs in place of the statement and writes each base table itself, for example inserting the customer, reading the new key with RETURNING INTO and inserting the account. Without it Oracle refuses the insert with ORA-01779.

From the lesson Oracle DML triggers: row, statement, INSTEAD OF
Which declarative features should rule out a trigger first?

An identity column for a surrogate key, DEFAULT ON NULL to fill a value even when NULL is passed, a virtual column to compute one from other columns on read, a CHECK constraint for a rule about a row's own columns and a foreign key for a reference. Each is enforced by the engine itself and holds for every path into the table.

From the lesson Oracle DML triggers: row, statement, INSTEAD OF
What is a mutating table, and what may a row trigger not do with it?

A table that a DML statement is changing. A row-level trigger fired by that statement may neither query nor change it; trying raises ORA-04091, and the database rolls back both the trigger's work and the triggering statement.

From the lesson Compound triggers and the mutating-table error
Why does Oracle refuse the read rather than allow it?

The trigger would see the table half changed, and SQL does not fix the order in which one statement changes its rows: a sweep whose result is 50,000 shows 18,000 in one row order and 52,500 in the other, so a check made between two rows could pass or fail by chance.

From the lesson Compound triggers and the mutating-table error
Why can a CHECK constraint not enforce a cap on the sum of a customer's accounts?

A CHECK constraint sees one row, and the rule spans rows: it is about the total of all the customer's accounts.

From the lesson Compound triggers and the mutating-table error
Which sections can a compound trigger have, and how long do its variables live?

Up to four timing points in one trigger: BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW and AFTER STATEMENT. Its variables live for one triggering statement, so each statement starts with an empty record.

From the lesson Compound triggers and the mutating-table error
How does a compound trigger enforce a cross-row rule?

The row section records which keys changed, and the AFTER STATEMENT section, which the restriction does not cover, sums just those keys once the table is whole again.

From the lesson Compound triggers and the mutating-table error
What can a plain AFTER statement trigger not do that a compound trigger can?

It may also read the finished table, but it cannot tell which keys changed, so it has to check them all.

From the lesson Compound triggers and the mutating-table error
Why is an autonomous transaction not a fix for ORA-04091?

It silences the error, but its query reads only committed data, so the check works on the wrong totals.

From the lesson Compound triggers and the mutating-table error
What do system triggers fire for?

For DDL in a schema or for database events such as a logon, not for changed rows.

From the lesson Compound triggers and the mutating-table error
Where do the values and the results of an EXECUTE IMMEDIATE statement go?

Values go in USING, one row comes back with INTO, many rows with BULK COLLECT INTO, and a changed row's values with RETURNING INTO when the text has a RETURNING clause. In a SQL statement the placeholders match the USING list by position, not by name.

From the lesson Dynamic SQL in PL/SQL without injection
What happens to a dynamic SELECT with a USING list but no INTO?

Oracle's reference says such a statement never runs, so no row is read. Add INTO for one row or BULK COLLECT INTO for many.

From the lesson Dynamic SQL in PL/SQL without injection
Which two EXECUTE IMMEDIATE rules catch people out?

USING does not accept the word NULL, so bind a variable that was never assigned. And DDL commits: the database commits the open transaction before and after every DDL statement, so a ROLLBACK after a dynamic CREATE TABLE undoes nothing.

From the lesson Dynamic SQL in PL/SQL without injection
What does Oracle trace every injection technique back to?

One weakness: string input that is not validated and is concatenated into a dynamic statement. The parser then reads whatever the input holds as SQL, so a test input matches every row and an ordinary name with an apostrophe fails to parse (ORA-03049).

From the lesson Dynamic SQL in PL/SQL without injection
Why are bind variables the most effective guard?

The database uses their values only as data and never interprets them, and the statement text stays the same on every call. Doubling quotes is easy to forget in one of many places, and refusing every apostrophe turns away real names without making the query safe.

From the lesson Dynamic SQL in PL/SQL without injection
What is the trap with a date pasted into dynamic SQL text?

It is converted to characters with the session's NLS date format, which the session itself can change. Bind the value, or convert it with an explicit format model such as TO_CHAR(d, 'YYYY-MM-DD') after the keyword DATE.

From the lesson Dynamic SQL in PL/SQL without injection
What do the DBMS_ASSERT checks do, and why is the allowlist still first?

SIMPLE_SQL_NAME checks a name's form, QUALIFIED_SQL_NAME a dotted name, SQL_OBJECT_NAME that an object exists, SCHEMA_NAME that a schema does, and ENQUOTE_NAME quotes a name. But a quoted name passes the form check and an existing table passes the object check without being a column the report should sort by, so Oracle says the package helps validation code and does not replace it.

From the lesson Dynamic SQL in PL/SQL without injection
When does native dynamic SQL stop being enough?

When the number and types of the inputs or outputs are not known at compile time, because EXECUTE IMMEDIATE and OPEN FOR take a USING list written when the code is compiled. Then DBMS_SQL parses, binds by name, executes and either fetches or hands back a ref cursor with TO_REFCURSOR; native dynamic SQL is shorter and runs noticeably faster.

From the lesson Dynamic SQL in PL/SQL without injection
What does a unit's AUTHID affect, and what does it not?

How the SQL the unit issues at run time resolves names and checks privileges. It does not affect compilation.

From the lesson Definer's rights, invoker's rights and roles
What happens when a definer's-rights unit is called?

AUTHID DEFINER is the default: CURRENT_USER and CURRENT_SCHEMA become the unit's owner and only the role PUBLIC stays enabled, so the owner's roles count for nothing inside it.

From the lesson Definer's rights, invoker's rights and roles
What does AUTHID CURRENT_USER give a unit?

Invoker's rights, which Oracle calls the preferred usage: nothing switches, so names resolve in the caller's schema and the caller's privileges and enabled roles are checked.

From the lesson Definer's rights, invoker's rights and roles
An invoker's-rights unit names lab.accounts and still fails with ORA-00942. Why?

Oracle reports that error both for a table that does not exist and for one the user may not read, so naming the owner finds the table but the caller's missing privilege still refuses it.

From the lesson Definer's rights, invoker's rights and roles
Which rights model does a trigger use, and which does an anonymous block use?

A trigger always behaves as definer's rights and an anonymous block as invoker's rights.

From the lesson Definer's rights, invoker's rights and roles
When are a unit's names resolved and privileges checked for dynamic and for static SQL?

Dynamic SQL is resolved and checked once, when it runs. Static SQL is also checked when the unit compiles, with the privileges a definer's-rights unit would have.

From the lesson Definer's rights, invoker's rights and roles
What does an application user get instead of rights on the tables?

EXECUTE on one definer's-rights package and no privilege on any table: the package checks every request, and a direct UPDATE from the application fails with ORA-00942, so a bug or a stolen password can do only what the package allows.

From the lesson Definer's rights, invoker's rights and roles
Which four tools tighten the pattern further?

Code-based access control, a role granted to the package itself, which gives a unit privileges no user holds; ACCESSIBLE BY, which limits the units allowed to call it; separate data, API and application schemas; and narrow roles, such as DB_DEVELOPER_ROLE instead of DBA, with schema privileges granting one system privilege on a single schema.

From the lesson Definer's rights, invoker's rights and roles
What does EXPLAIN PLAN do, and who formats its output?

It asks for a query's plan without running it: the optimizer writes one row per step into PLAN_TABLE and DBMS_XPLAN.DISPLAY formats those rows. It is a DML statement that does not commit, so its rows can be rolled back.

From the lesson Oracle execution plans with DBMS_XPLAN
What do DBMS_XPLAN's BASIC, TYPICAL and ALL formats show?

BASIC shows only the operations; TYPICAL, the default, adds the estimated rows, bytes and cost and the predicates; ALL adds the projection and alias sections.

From the lesson Oracle execution plans with DBMS_XPLAN
Why is the explained plan not always the plan that ran?

Oracle's tuning guide warns it can differ when bind values, statistics or session parameters differ. Use EXPLAIN PLAN to ask questions and the cursor cache to see what actually happened.

From the lesson Oracle execution plans with DBMS_XPLAN
How do you put measured rows beside the estimates?

Run the query with the gather_plan_statistics hint, so each step records the rows it produced and the buffers it read, then DBMS_XPLAN.DISPLAY_CURSOR with the format ALLSTATS LAST shows E-Rows next to A-Rows with a Buffers column of logical reads.

From the lesson Oracle execution plans with DBMS_XPLAN
Which two practical points come with DISPLAY_CURSOR?

Called with no SQL_ID it shows the last cursor the session ran, and SQL*Plus calls DBMS_OUTPUT.GET_LINES after each statement while SERVEROUTPUT is on, so run SET SERVEROUTPUT OFF first or pass the SQL_ID from V$SQL. It also needs read access to V$SQL, V$SQL_PLAN, V$SQL_PLAN_STATISTICS_ALL and V$SESSION.

From the lesson Oracle execution plans with DBMS_XPLAN
What does the optimizer assume about a column with no histogram?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Oracle execution plans with DBMS_XPLAN
How do you give the optimizer the real counts, and why does SIZE AUTO sometimes not?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Oracle execution plans with DBMS_XPLAN
When is a bind value looked at, and what can follow?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Oracle execution plans with DBMS_XPLAN
How often does a PL/SQL function in a query run?

Once for every row it is evaluated on: in the select list once per returned row, and in the WHERE clause once per tested row, which is usually every row of the table. Each run is a hand-over to the PL/SQL engine and back.

From the lesson PL/SQL functions in SQL: avoiding the slowdown
What else does a function wrapped around a column cost?

It keeps an ordinary index on that column out of the plan, so the filter has to test every row.

From the lesson PL/SQL functions in SQL: avoiding the slowdown
A filter on with_tax(amount) keeps 2,198 of 200,000 rows. How many calls, and what is the fix?

200,000 calls to find them. Moving the test onto the column in an inner query, amount > 990, keeps the same 2,198 rows and calls the function only for those, which is the nested-query fix Oracle's guide shows.

From the lesson PL/SQL functions in SQL: avoiding the slowdown
What does PRAGMA UDF tell the compiler?

That the function is used mainly in SQL statements, which might improve its performance. It is one line in a stored function's declarations and changes none of the purity rules.

From the lesson PL/SQL functions in SQL: avoiding the slowdown
What is WITH FUNCTION, and which definition wins?

A function declared in the query's own WITH clause, so it exists only for that query and its subqueries and needs no stored object. Inside its query it takes precedence over a stored function of the same name. In SQL*Plus the statement ends with a line holding only a slash.

From the lesson PL/SQL functions in SQL: avoiding the slowdown
Do PRAGMA UDF and WITH FUNCTION reduce the number of calls?

No. Both are declared for calls from SQL and make each call cheaper, but both still run once for every row they are evaluated on.

From the lesson PL/SQL functions in SQL: avoiding the slowdown
Which two features remove the call from the plan altogether?

A scalar SQL macro, whose body returns an expression as text for Oracle to put in place of the call, so the optimizer sees plain SQL; and the SQL Transpiler, new in 26ai and off by default, which converts an eligible function into a SQL expression and so helps code you cannot edit.

From the lesson PL/SQL functions in SQL: avoiding the slowdown
When does RESULT_CACHE pay, and what does DETERMINISTIC declare?

RESULT_CACHE keeps a function's results so calls with arguments seen before skip the body, which suits functions whose arguments repeat and whose data rarely changes. DETERMINISTIC declares that a result depends only on the arguments, so Oracle may reuse earlier results.

From the lesson PL/SQL functions in SQL: avoiding the slowdown
What does a pipelined table function gain over one that returns a collection?

It hands each row to the query with PIPE ROW as soon as it is built, so early rows arrive early and the work stops once the query has enough. A collection-returning function builds all 240 rows of a schedule before the query sees month 1.

From the lesson Pipelined table functions and SQL macros
What does a pipelined function's header and return type look like?

RETURN <collection type> PIPELINED, with the collection type declared at schema level or in a package and with SQL element types, never an associative array. PIPE ROW sends one element without giving up control, and the final RETURN carries no value.

From the lesson Pipelined table functions and SQL macros
What happens when the query stops reading a pipelined function early?

The PIPE ROW at which the function is paused raises the predefined exception NO_DATA_NEEDED, ORA-06548, so the function builds no more rows, and the query finishes normally with the rows it has. Unhandled, the exception simply ends the function's run and never reaches the query.

From the lesson Pipelined table functions and SQL macros
When does NO_DATA_NEEDED need a handler?

When the function holds something to release, such as a cursor or a file it opened: the handler closes it and ends with RETURN, and re-raising the exception does the same.

From the lesson Pipelined table functions and SQL macros
Why can a WHEN OTHERS handler break a query that reads only three rows?

It catches NO_DATA_NEEDED too, so WHEN OTHERS THEN RAISE_APPLICATION_ERROR turns the normal early stop into ORA-20001, which looks like a bug in the query. Put WHEN NO_DATA_NEEDED THEN RETURN; before WHEN OTHERS.

From the lesson Pipelined table functions and SQL macros
Why may a pipelined function have built rows the query never read?

The runtime hands piped rows over in batches, so the function can be a few rows past the last one the query read when it stops.

From the lesson Pipelined table functions and SQL macros
What does a table SQL macro do instead?

Its body returns a query as text, and Oracle substitutes that query for the call, so the optimizer reads it as if it had been written into the statement and nothing runs in PL/SQL row by row.

From the lesson Pipelined table functions and SQL macros
Which of the three fits which job?

A pipelined table function for rows PL/SQL computes, such as a schedule or a series; a table SQL macro for a parameterised query whose values already sit in tables; a polymorphic table function, which takes a whole table as an argument, when the result columns are decided per table. PARALLEL_ENABLE lets a parallel query split a pipelined function's input.

From the lesson Pipelined table functions and SQL macros
Who sees the rows of a global temporary table, and how long do they last?

It is created once at deployment and its definition stays in the data dictionary, but its data is visible only to the session that inserted it. ON COMMIT DELETE ROWS, the default, truncates that session's rows at its next commit; ON COMMIT PRESERVE ROWS keeps them until the session ends.

From the lesson Staging in Oracle: temporary tables and MERGE
A load stages its feed, commits, then merges. What does the MERGE find?

No rows, and no error: the commit truncated the session's rows in an ON COMMIT DELETE ROWS table. Stage and merge in one transaction, or create the table ON COMMIT PRESERVE ROWS, and remember that DDL commits implicitly, so a CREATE TABLE between the steps empties the stage too.

From the lesson Staging in Oracle: temporary tables and MERGE
What is a private temporary table, and what is its naming rule?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Staging in Oracle: temporary tables and MERGE
What can a private temporary table not have?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Staging in Oracle: temporary tables and MERGE
How does one MERGE apply a staged feed?

Its ON clause joins the feed to the table: a line whose key is present takes WHEN MATCHED and updates the row, any other takes WHEN NOT MATCHED and inserts one. That is an upsert in one set-based statement instead of thousands of single-row ones.

From the lesson Staging in Oracle: temporary tables and MERGE
Which rows can a MERGE's DELETE WHERE clause remove, and by which values does it judge them?

Only rows this MERGE has just matched and updated, judged by the values the UPDATE has just written, not the old ones: a count updated to -1 is deleted, while a row no line joined stays.

From the lesson Staging in Oracle: temporary tables and MERGE
What do two feed lines for one target row cause, and which cure fits which feed?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Staging in Oracle: temporary tables and MERGE
What does LOG ERRORS add to a MERGE, and which failure still rolls it back?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Staging in Oracle: temporary tables and MERGE
Does a reader in Oracle ever wait for a writer?

No. While a row is being changed the database uses undo data to give each query the version last committed, and every query gets its rows from one committed point in time. Waiting belongs to locks, never to reading.

From the lesson Row locks, SKIP LOCKED and scheduler jobs
When is a row locked, and how long does the lock last?

Only when a writer changes it or when SELECT ... FOR UPDATE asks for its lock, and the lock lasts until the transaction commits or rolls back. A writer blocks another writer of the same row.

From the lesson Row locks, SKIP LOCKED and scheduler jobs
Where may SELECT ... FOR UPDATE not be used?

Only in a top-level query: not with DISTINCT, set operators, GROUP BY or aggregate functions.

From the lesson Row locks, SKIP LOCKED and scheduler jobs
What do the four lock-wait choices do when a selected row is held by another transaction?

With no clause the session waits until that transaction ends; NOWAIT fails at once with ORA-00054; WAIT 2 waits up to two seconds and then fails if the row is still locked; SKIP LOCKED leaves that row out and locks and returns the others.

From the lesson Row locks, SKIP LOCKED and scheduler jobs
What unit does WAIT count in?

Seconds, unless MILLISECONDS or MICROSECONDS follows the number.

From the lesson Row locks, SKIP LOCKED and scheduler jobs
What is SKIP LOCKED built for, and what does it return when every job is held?

Multiconsumer queues: each consumer skips the rows other consumers hold and gets the remaining rows without waiting. When every row is locked it returns no row at once, which a worker reads as nothing to do now.

From the lesson Row locks, SKIP LOCKED and scheduler jobs
What is a lost update, and what stops it?

A change that another session overwrites from an older read. An optimistic check in the UPDATE, which refuses to write when the row has changed since it was read, stops it.

From the lesson Row locks, SKIP LOCKED and scheduler jobs
What runs PL/SQL in the background, and where is each run recorded?

Scheduler jobs, which run on a calendar, and each run is logged in USER_SCHEDULER_JOB_RUN_DETAILS.

From the lesson Row locks, SKIP LOCKED and scheduler jobs
What are the three steps of changing a JSON document in PL/SQL?

JSON_OBJECT_T.parse reads the text into an object held in memory, get functions read its fields and put changes them, and to_String turns the whole object back into text.

From the lesson JSON in PL/SQL: object types and 26ai features
Which five JSON object types does Oracle document?

JSON_ELEMENT_T, any parsed value and the supertype of the next three; JSON_OBJECT_T with get_Number, get_Array, has and put; JSON_ARRAY_T with get(i), get_size and append; JSON_SCALAR_T for one string, number, true, false or null; and JSON_KEY_LIST, the key names get_Keys returns.

From the lesson JSON in PL/SQL: object types and 26ai features
Where do positions in a JSON_ARRAY_T start, and what does get(i) return?

At 0, so Oracle's own example walks an array with FOR i IN 0 .. get_size - 1; counting from 1 as PL/SQL collections do silently skips the first item. get(i) returns a JSON_ELEMENT_T, so TREAT makes it the subtype you need before reading a field.

From the lesson JSON in PL/SQL: object types and 26ai features
Do the JSON object types work on 19c?

Yes: the 19c reference documents the same five types, so JSON code written with them runs on both releases.

From the lesson JSON in PL/SQL: object types and 26ai features
Which five 26ai features does everyday PL/SQL code meet?

A SQL BOOLEAN column type; IF [NOT] EXISTS on DDL; SELECT without FROM; VALUES rows that build rows without a table; and JSON for PL/SQL types, so a record or collection becomes JSON and comes back as one.

From the lesson JSON in PL/SQL: object types and 26ai features
What does a 19c database offer in place of those 26ai features?

No BOOLEAN in SQL; IF [NOT] EXISTS from Release Update 19.28; SELECT 3 * 7 FROM dual instead of a bare SELECT; one-row queries with UNION ALL instead of VALUES rows; and a put or get call per field instead of mapping a whole record.

From the lesson JSON in PL/SQL: object types and 26ai features
What is a VALUES clause used as a source of rows called, and where is it accepted?

A table value constructor, which Oracle accepts in SELECT, INSERT and MERGE.

From the lesson JSON in PL/SQL: object types and 26ai features
Which three larger 26ai features belong to other tracks?

SQL domains, which declare what a column holds such as an email address; JSON-relational duality views, which show the same data as documents or as rows; and AI Vector Search, which queries data by meaning.

From the lesson JSON in PL/SQL: object types and 26ai features
What makes a PL/SQL package a utPLSQL suite?

Annotations: comments in its specification such as --%suite and --%test. They are read only in the specification and are case-insensitive. utPLSQL is an open-source framework, licensed under Apache 2.0, whose tests are PL/SQL procedures.

From the lesson Testing PL/SQL: utPLSQL, CI and coverage
What do --%suite, --%test, --%beforeeach and --%throws do?

--%suite makes the package a suite, --%test marks the procedure below it as a test, --%beforeeach runs the procedure below it before every test, and --%throws(-20002) makes the test succeed only if that error is raised.

From the lesson Testing PL/SQL: utPLSQL, CI and coverage
How does a test compare a value, and what runs the suite?

An expectation such as ut.expect(v_from).to_equal(150), with to_be_null, to_be_between and to_have_count among the other matchers. ut.run('test_ledger_api') runs the suite and prints a report.

From the lesson Testing PL/SQL: utPLSQL, CI and coverage
Which bug does --%throws catch that a quick manual check misses?

A refused transfer that now fails with the table's ORA-02290 instead of the documented ORA-20002: the behaviour still looks right, but the screen that catches ORA-20002 shows a generic error. The report reads Actual: -2290 was expected to equal: -20002.

From the lesson Testing PL/SQL: utPLSQL, CI and coverage
What does the default --%rollback(auto) do?

It sets a savepoint before each test's set-up and rolls back to it when the test ends, so the customer and accounts a set-up creates disappear. No clean-up code is needed and no test depends on rows another test or person left.

From the lesson Testing PL/SQL: utPLSQL, CI and coverage
Why is a shared development database safe for these tests?

utPLSQL's documentation notes that changes kept uncommitted do not affect others who share the database, and the test data is synthetic, such as an address in the reserved .invalid domain that no real customer holds.

From the lesson Testing PL/SQL: utPLSQL, CI and coverage
What do you need for code that commits?

Another approach: --%rollback(manual) and clean-up of its own, because the automatic rollback cannot undo committed work.

From the lesson Testing PL/SQL: utPLSQL, CI and coverage
How do these tests gate a change?

In CI a disposable Oracle container runs the suite on every pull request, coverage reporters list which lines the tests ran, and a required check lets a failed run block the merge.

From the lesson Testing PL/SQL: utPLSQL, CI and coverage
In which order does a PL/SQL code review read a package?

Costliest failures first: errors and transactions, loops and bulk SQL, dynamic SQL and rights, package state, performance, then tests.

From the lesson PL/SQL code review and interview drills
What does one successful call prove nothing about?

The state a failure leaves partway, the effect of a caller's text inside a statement, and the count a second call in the same session reports.

From the lesson PL/SQL code review and interview drills
What does the review's errors and transactions row look for?

No WHEN OTHERS THEN NULL, no COMMIT in a reusable unit, and the error codes documented in the specification.

From the lesson PL/SQL code review and interview drills
What do the loops and the dynamic SQL rows of the checklist ask for?

SQL before loops, and LIMIT loops whose exit keeps the last batch; then binds for values, an allowed list for names, and an explicit AUTHID.

From the lesson PL/SQL code review and interview drills
What do the package state and performance rows check?

That no variable outlives a call without a reason, and that conditions keep bare columns, with the plan read rather than guessed: TRUNC around an indexed column is a finding.

From the lesson PL/SQL code review and interview drills
Why did the author's test miss the bug that drops the last 50 lines of a 250-line feed?

Its 200 lines filled the LIMIT exactly and none of them failed, so the exit placed straight after the fetch never had a short last batch to drop.

From the lesson PL/SQL code review and interview drills
Which three answers change with the release?

A stepped FOR loop, which exists from 21c; a BOOLEAN column, which 26ai has and 19c replaces with NUMBER(1) and a check of 0 or 1; and CREATE TABLE IF NOT EXISTS, which reached 19c in Release Update 19.28 and before that needed a block that ignores ORA-00955 only.

From the lesson PL/SQL code review and interview drills
What shape does a strong drill answer have, and which sources are worth practising from?

The deciding sizes first, rows per run, callers at once and what must survive a failure, then the failure, the fix and the test that proves it. Practise from the vendor's reference checked in your own lab; an answer written for an old release needs rechecking, and lists of real interview questions from companies have no source.

From the lesson PL/SQL code review and interview drills
How do a PL/SQL block, an EXCEPTION section and RAISE_APPLICATION_ERROR port to T-SQL?

A block becomes a batch ended by GO, whose variables end with the batch; an EXCEPTION section becomes BEGIN TRY ... END CATCH, where one CATCH receives every error; and RAISE_APPLICATION_ERROR(-20001, ...) becomes THROW 50001, ..., 1, because THROW numbers start at 50,000.

From the lesson Porting code between PL/SQL and T-SQL
Which PL/SQL construct has no T-SQL counterpart, and what replaces it?

Package state: a package variable lives once per session and keeps its value between calls, while T-SQL has no packages and a variable ends with its batch. SESSION_CONTEXT is the nearest store, written by sp_set_session_context with keys up to 128 bytes and 1 MB per session; anything lasting or shared goes in a table.

From the lesson Porting code between PL/SQL and T-SQL
What should a BULK COLLECT and FORALL loop become in T-SQL?

One set-based UPDATE ... FROM or INSERT ... SELECT, or a table-valued parameter when the rows come from the application, never a WHILE loop or a cursor over the same rows.

From the lesson Porting code between PL/SQL and T-SQL
Which rules change a ported result without raising an error?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Porting code between PL/SQL and T-SQL
How does a port make each of those differences explicit?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Porting code between PL/SQL and T-SQL
Which assignment rule do ports most often get wrong?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Porting code between PL/SQL and T-SQL
What does each engine leave behind after a failing statement, and what about DDL?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Porting code between PL/SQL and T-SQL
What does SSMA convert, and when is a port finished?

Pro : opens with a Pro, Premium or Ultimate pass This card’s back opens with any pass.

From the lesson Porting code between PL/SQL and T-SQL
Why do Oracle's readers and writers not block each other?

A change writes the old values as undo data, and a query that meets a changed row rebuilds the version committed when the query began. Oracle states it as two rules: a reader never blocks a writer and a writer never blocks a reader. The exception is SELECT ... FOR UPDATE, which locks the rows it reads.

From the lesson Oracle vs SQL Server: reads, locks and deadlocks
What happens to the same read on SQL Server's default settings?

READ_COMMITTED_SNAPSHOT is off, so the reader takes a shared lock on each row, which cannot be granted while a writer holds its exclusive lock: it waits until the writer's transaction ends or until its lock timeout runs out, -1 by default, and SET LOCK_TIMEOUT 3000 ends the statement after 3 seconds with error 1222.

From the lesson Oracle vs SQL Server: reads, locks and deadlocks
Why is WITH (NOLOCK) the wrong answer for a ported report that now waits?

It reads changes that may still be rolled back, a balance that was never real, which Oracle never allows. Turning READ_COMMITTED_SNAPSHOT on gives readers the last committed version instead.

From the lesson Oracle vs SQL Server: reads, locks and deadlocks
How does a deadlock form on both engines?

Each session locks its first row before it asks for the other's, so two transfers in opposite directions close a cycle. Both engines find the cycle; they differ in how they break it.

From the lesson Oracle vs SQL Server: reads, locks and deadlocks
What does Oracle undo when it breaks a deadlock?

One statement, the one that detected the deadlock, returning ORA-00060 to its session. That transaction's earlier changes and their locks stay, so the other session keeps waiting until this one commits or rolls back, and either session may be the one that gets the error.

From the lesson Oracle vs SQL Server: reads, locks and deadlocks
What does SQL Server undo when it breaks a deadlock?

The victim's whole transaction: the deadlock monitor picks the session with the lower DEADLOCK_PRIORITY or else the cheaper transaction to roll back, ends its batch, rolls the transaction back, frees its locks and returns error 1205 before the code sees anything.

From the lesson Oracle vs SQL Server: reads, locks and deadlocks
What does SERIALIZABLE do on each engine when it meets a newer change?

Oracle fails with ORA-08177; SQL Server holds locks instead, so other sessions wait.

From the lesson Oracle vs SQL Server: reads, locks and deadlocks
What becomes of FOR UPDATE NOWAIT, WAIT and SKIP LOCKED in a port?

They become session settings and table hints in T-SQL, and a deadlock handler written for one engine can leave wrong balances on the other, because the two undo different amounts of work.

From the lesson Oracle vs SQL Server: reads, locks and deadlocks

Back to the PL/SQL & T-SQL (Oracle and SQL Server) track