A trigger is an automatic SQL procedure that executes in response to certain events on a table, such as INSERT, UPDATE, or DELETE. Example SQL Trigger: sqlCopyEditCREATE TRIGGER update_salary BEFORE UPDATE ON Employees FOR EACH ROW SET NEW.salary = GREATEST(NEW.salary, 30000); This ensures salaries never drop below 30,000.
A view is a virtual table based on the result of an SQL query that does not store data physically but provides a logical representation of it. Example SQL View: sqlCopyEditCREATE VIEW HighSalaryEmployees AS SELECT name, salary FROM Employees WHERE salary > 50000; Querying the view: sqlCopyEditSELECT * FROM HighSalaryEmployees; Returns only employees with a…
SQL Injection is a type of cyber attack where malicious SQL code is inserted into a query to manipulate or steal data from a database. Example of Vulnerable Code: phpCopyEdit$query = “SELECT * FROM users WHERE username = ‘$username’ AND password = ‘$password'”; Preventing SQL Injection (Using Prepared Statements in PHP): phpCopyEdit$stmt = $pdo->prepare(“SELECT *…
ACID properties (Atomicity, Consistency, Isolation, Durability) ensure reliable processing of transactions in a database. Example: A bank transfer must withdraw and deposit money together (Atomicity), ensure correct balances (Consistency), prevent interference from other transactions (Isolation), and remain stored permanently (Durability).
A transaction is a sequence of one or more SQL operations executed as a single unit of work, ensuring data consistency using ACID properties. Transactions are used to ensure that multiple database operations either all succeed or all fail. If an error occurs, the transaction can be rolled back to its previous state. Example SQL…
Data integrity refers to the accuracy, consistency, and reliability of data stored in a database by enforcing constraints and validation rules. Data integrity ensures that data remains accurate and valid throughout its lifecycle. It is enforced using: Example Constraint for Data Integrity: sqlCopyEditCREATE TABLE Employees ( emp_id INT PRIMARY KEY, salary DECIMAL(10,2) CHECK (salary >…
Query optimization is the process of improving the efficiency of SQL queries to minimize execution time and resource usage. Optimizing queries helps improve database performance by using techniques like: Example SQL Optimization: Instead of: sqlCopyEditSELECT * FROM Orders WHERE customer_name = ‘Alice’; Use an indexed column: sqlCopyEditSELECT * FROM Orders WHERE customer_id = 1; This…
Indexing is a database technique that improves the speed of data retrieval operations by creating a data structure that allows fast lookups of specific rows in a table. Indexes work like the table of contents in a book, allowing the database to quickly find data instead of scanning entire tables. There are different types of…
Denormalization is a database optimization technique that combines tables to reduce joins and improve query performance by introducing some level of redundancy. Denormalization is often used in read-heavy applications like data warehouses, where performance is prioritized over data redundancy. Instead of splitting data into multiple normalized tables, it is merged into fewer tables to reduce…
Normalization is a database design technique that organizes data into multiple related tables to eliminate redundancy and dependency by dividing large tables into smaller ones and defining relationships between them. Normalization improves data integrity and consistency by reducing redundancy. It involves dividing a database into smaller, related tables and ensuring that each table contains data…