Database Transactions and ACID Properties

Questions and Answers

Which of the following properties ensures that a transaction returns to its initial state after a failure?

Durability

Atomicity (correct)

Isolation

Consistency

Which SQL aggregate function would you use to determine the total of a specified column's values?

COUNT()

MAX()

SUM() (correct)

FIRST()

What is the correct purpose of the LCASE() scalar function in SQL?

Converts text to lowercase (correct)

Converts text to uppercase

Returns the length of the text field

Rounds up the decimal field value

Which component of an SQL trigger specifies the action that causes the trigger to execute?

Event Signup and view all the answers

Which of the following is NOT an aggregate function in SQL?

MID() Signup and view all the answers

What is the correct SQL command to update an existing view?

CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name; Signup and view all the answers

Which command is used to revoke access from a user in SQL?

REVOKE privilege_name ON object_name FROM user_name; Signup and view all the answers

What type of privilege allows a user to perform administrative tasks in SQL?

System privilege Signup and view all the answers

Which of the following statements best describes SQL Injection?

An attack that involves inserting malicious SQL statements into input fields. Signup and view all the answers

Which command grants users the right to perform actions on database objects?

GRANT privilege_name ON object_name TO user_name; Signup and view all the answers

Study Notes

Transaction Properties - ACID

Atomicity: Guarantees all transactions complete successfully or none at all; unsuccessful transactions are aborted, and changes are rolled back.
Consistency: Ensures all successful transaction changes are accurately reflected in the database.
Isolation: Transactions operate independently, preventing changes in one transaction from affecting others until committed.
Durability: Committed changes persist even after system failures.

SQL Aggregate Functions

Count of Functions: Seven aggregate functions available in SQL.
AVG(): Calculates the average of specified column values.
COUNT(): Returns the total number of rows in a table.
MAX(): Identifies the largest value from records.
MIN(): Identifies the smallest value from records.
SUM(): Computes the total sum of specified column values.
FIRST(): Retrieves the first value in a specified column.
LAST(): Retrieves the last value in a specified column.

Scalar Functions in SQL

Definition: Return a single value based on input values.
UCASE(): Converts text to uppercase.
LCASE(): Converts text to lowercase.
MID(): Extracts a substring from a text field.
FORMAT(): Specifies a display format for data.
LEN(): Returns the length of a text field.
ROUND(): Rounds a numeric value to a specified number of decimal places.

SQL Triggers

Definition: Stored procedures that respond to specific database actions (INSERT, UPDATE, DELETE).
Components: Consist of actions and events; certain actions trigger a corresponding event.
Syntax: CREATE TRIGGER name {BEFORE|AFTER} (event [OR..]} ON table_name [FOR [EACH] {ROW|STATEMENT}] EXECUTE PROCEDURE functionname {arguments}

Views in SQL

Definition: Virtual tables based on fields from one or more tables.
Syntax: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

Updating Views

Method: Use CREATE OR REPLACE VIEW to update an existing view.
Syntax: CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

SQL Privileges

Commands: Managed through GRANT and REVOKE commands to control user access to database objects.
GRANT Command: Provides specific database access rights to users.
Syntax: GRANT privilege_name ON object_name TO {user_name|PUBLIC|role_name} [WITH GRANT OPTION]
REVOKE Command: Denies or removes access rights to users.
Syntax: REVOKE privilege_name ON object_name FROM {user_name|PUBLIC|role_name}

Types of Privileges

System Privileges: Allow users to perform actions on specific types of objects (e.g., administrative tasks, creating or altering tables/views).
Object Privileges: Allow users to perform actions on specific database objects (e.g., SELECT, INSERT, UPDATE, DELETE).

SQL Injection

Definition: A security vulnerability where malicious SQL code is injected into an input field, allowing attackers to execute unauthorized database operations.
Purpose: Typically used against data-driven applications to access sensitive data or perform administrative tasks.

Description

This quiz covers the fundamental properties of database transactions, known as ACID properties. It focuses on concepts such as Atomicity, Consistency, Isolation, and Durability, ensuring a solid understanding of transaction management in databases. Test your knowledge of these critical aspects of transaction processing.