SQL Procedures, Transactions and Database Security

SQL beginner course · Chapter 9 of 10

SQL Procedures, Transactions and Database Security explained in simple steps. Examples use practice tables such as students and scores; adapt names to your database.

Follow the concepts from left to right, then run the examples below.

In this lesson

1. Stored procedures
2. Alter and drop procedures
3. COMMIT and ROLLBACK
4. ACID
5. Users and permissions
6. SQL injection

Stored procedures

A stored procedure is reusable database-side logic. CREATE, ALTER and DROP syntax differs substantially between SQL Server, Oracle, PostgreSQL and MySQL. Start with a SELECT query, then follow your product’s procedure syntax.

Example

CALL refresh_student_summary();

Expected result: A previously defined procedure runs where CALL is supported.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Alter and drop procedures

Procedure syntax is database-specific. SQL Server uses ALTER PROCEDURE, PostgreSQL has CREATE OR REPLACE PROCEDURE for replacement, and DROP PROCEDURE removes a procedure. Use the database manual and test in a lab.

Example

DROP PROCEDURE demo_procedure;

Expected result: The procedure is removed where the dialect supports this form.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

COMMIT and ROLLBACK

A transaction groups changes. COMMIT makes the transaction durable; ROLLBACK cancels it before commit. The exact BEGIN syntax differs by system.

Example

BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 10 WHERE id = 1; UPDATE accounts SET balance = balance + 10 WHERE id = 2; COMMIT;

Expected result: Both transfers are committed together.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

ACID

Atomicity means all-or-nothing; consistency protects rules; isolation separates concurrent work; durability preserves committed work. These are the goals of reliable transactions.

Example

SELECT balance FROM accounts WHERE id IN (1, 2);

Expected result: Check both balances before and after a transfer.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Users and permissions

Grant only the access an application needs. Reading reports may need SELECT but should not automatically receive DELETE or schema rights.

Example

GRANT SELECT ON students TO report_reader;

Expected result: A reporting user receives read permission where supported.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

SQL injection

Never join untrusted input into SQL text. Send values as parameters through your application driver. A parameter is data even if it contains quote marks or SQL-looking text.

Example

SELECT student_name FROM students WHERE student_id = ?;

Expected result: The application binds the id value separately from SQL text.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Quick review

Explain each concept in your own words. Recreate one example without looking at the code. Compare your result with the expected result above.

Browse the full SQL learning path. This is original teaching material; the syllabus was checked against the w3resource SQL tutorial index.