```html

SQL: DDL/DML, Queries, Views, Stored Procedures, Triggers, and SQL Injection Issues

Introduction to SQL

SQL, which stands for Structured Query Language, is the standard language for managing and manipulating relational databases. It's used to communicate with databases, ranging from simple data retrieval to complex data updates and schema management. For anyone working with databases, a strong understanding of SQL is essential. It is the backbone of many applications and is heavily tested in computer science examinations.

SQL Commands Categories

SQL commands are broadly classified into several categories based on their functionality. Understanding these categories helps in organizing and recalling the commands effectively.

Data Definition Language (DDL)

DDL commands are used to define, modify, and delete the database structure or schema. They deal with the blueprint of the database.

  • CREATE: Used to create new database objects like tables, indexes, views, stored procedures, and functions.
  • ALTER: Used to modify the structure of existing database objects. This can include adding, deleting, or modifying columns in a table.
  • DROP: Used to delete existing database objects.
  • TRUNCATE: Used to remove all records from a table quickly, but the table structure remains intact. It's faster than DELETE for removing all rows because it deallocates the data pages.
  • RENAME: Used to rename an existing database object.

Example of CREATE TABLE:

Let's say we want to create a table named 'Students' to store student information. 

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100) UNIQUE,
    EnrollmentDate DATE
);

In this example, INT, VARCHAR, and DATE are data types. PRIMARY KEY ensures each student has a unique ID, and UNIQUE ensures each email address is distinct.

Example of ALTER TABLE: To add a new column 'Major' to the 'Students' table: 

ALTER TABLE Students
ADD Major VARCHAR(50);

Example of DROP TABLE: To remove the 'Students' table entirely: 

DROP TABLE Students;

Example of TRUNCATE TABLE: To remove all student records but keep the table structure: 

TRUNCATE TABLE Students;

Data Manipulation Language (DML)

DML commands are used to manage data within schema objects. They are used for retrieving, inserting, updating, and deleting records.

  • SELECT: Used to retrieve data from one or more tables. This is the most frequently used SQL command.
  • INSERT: Used to insert new records into a table.
  • UPDATE: Used to update existing records in a table.
  • DELETE: Used to delete records from a table.
  • MERGE: Used to perform INSERT or UPDATE operations on a table based on a join condition with another table. (Also known as UPSERT in some systems).
  • CALL: Used to execute a stored procedure.
  • EXPLAIN PLAN: Used to check how the database will execute a query.

Example of INSERT: Adding a new student record: 

INSERT INTO Students (StudentID, FirstName, LastName, Email, EnrollmentDate, Major)
VALUES (101, 'Alice', 'Smith', 'alice.smith@example.com', '2023-09-01', 'Computer Science');

Example of SELECT: Retrieving all student names and their majors: 

SELECT FirstName, LastName, Major
FROM Students;

You can add conditions using the WHERE clause: 

SELECT FirstName, LastName
FROM Students
WHERE Major = 'Computer Science';

Example of UPDATE: Changing Alice's major to 'Data Science': 

UPDATE Students
SET Major = 'Data Science'
WHERE StudentID = 101;

Example of DELETE: Deleting the record for a student with StudentID 101: 

DELETE FROM Students
WHERE StudentID = 101;

Note on DELETE vs. TRUNCATE: DELETE removes rows one by one and logs each operation, making it slower but allowing for rollback. TRUNCATE removes all rows at once by deallocating data pages, which is much faster but generally cannot be rolled back.

Data Control Language (DCL)

DCL commands are used to grant and revoke user privileges.

  • GRANT: Used to give users permission to perform certain tasks.
  • REVOKE: Used to take back permissions from users.

Example: 

GRANT SELECT ON Students TO 'john_doe'@'localhost';
REVOKE UPDATE ON Students FROM 'jane_doe'@'localhost';

Transaction Control Language (TCL)

TCL commands manage transactions within the database.

  • COMMIT: Saves all transactions to the database.
  • ROLLBACK: Undoes all transactions since the last COMMIT or ROLLBACK.
  • SAVEPOINT: Sets a point within a transaction to which you can later roll back.

Example: 

START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
SAVEPOINT transfer_complete;
-- Some other operations
-- If an error occurs:
ROLLBACK TO SAVEPOINT transfer_complete;
-- Or to undo everything:
ROLLBACK;
-- If all is well:
COMMIT;

SQL Queries: SELECT Statement Deep Dive

The SELECT statement is the heart of data retrieval in SQL. It allows you to fetch specific data based on various criteria.

Basic Structure

The fundamental structure of a SELECT statement is: 

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name ASC|DESC
GROUP BY column_name
HAVING group_condition
LIMIT number;

FROM Clause

Specifies the table(s) from which to retrieve data. You can join multiple tables using JOIN clauses (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN).

Example (JOIN): Assume we have an 'Enrollments' table with StudentID and CourseID. 

SELECT s.FirstName, s.LastName, c.CourseName
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
WHERE s.Major = 'Computer Science';

Here, `s`, `e`, and `c` are aliases for the tables to make the query shorter.

WHERE Clause

Filters records based on a specified condition. It uses comparison operators (=, !=, <, >, <=, >=), logical operators (AND, OR, NOT), and other operators (LIKE, IN, BETWEEN, IS NULL).

Examples: 

-- Students enrolled after a certain date
SELECT FirstName, LastName FROM Students WHERE EnrollmentDate > '2023-01-01';

-- Students with names starting with 'A'
SELECT FirstName FROM Students WHERE FirstName LIKE 'A%';

-- Students in specific majors
SELECT FirstName FROM Students WHERE Major IN ('Computer Science', 'Data Science');

-- Students with no major assigned
SELECT FirstName FROM Students WHERE Major IS NULL;

GROUP BY and HAVING Clauses

GROUP BY groups rows that have the same values in specified columns into summary rows, like "find the number of students in each major."

HAVING is used to filter groups based on a specified condition, similar to how WHERE filters rows. You can only use HAVING with aggregate functions (like COUNT, SUM, AVG, MAX, MIN) when used with GROUP BY.

Example: Find majors with more than 5 students. 

SELECT Major, COUNT(StudentID) AS NumberOfStudents
FROM Students
GROUP BY Major
HAVING COUNT(StudentID) > 5;

ORDER BY Clause

Sorts the result set in ascending (ASC) or descending (DESC) order.

Example: 

SELECT FirstName, LastName, EnrollmentDate
FROM Students
ORDER BY EnrollmentDate DESC; -- Sort by most recent enrollment first

LIMIT Clause

Restricts the number of rows returned by a SELECT statement. (Syntax may vary slightly across different SQL dialects, e.g., TOP in SQL Server).

Example: Get the 5 most recently enrolled students. 

SELECT FirstName, LastName, EnrollmentDate
FROM Students
ORDER BY EnrollmentDate DESC
LIMIT 5;

Aggregate Functions

These functions perform a calculation on a set of values and return a single value. Common ones include:

  • COUNT(): Counts the number of rows.
  • SUM(): Calculates the sum of values in a column.
  • AVG(): Computes the average of values.
  • MAX(): Finds the maximum value.
  • MIN(): Finds the minimum value.

Example: Find the average enrollment date (though date averaging is complex, this illustrates the function). 

SELECT AVG(DATEDIFF(year, '1970-01-01', EnrollmentDate)) AS AverageEnrollmentYear
FROM Students;

(Note: Actual date averaging often requires more complex calculations or specific database functions).

Views

A view is a virtual table based on the result-set of an SQL statement. It contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Benefits of Views:

  • Simplicity: Users can access complex queries as a single table.
  • Security: Can restrict access to sensitive data by showing only specific columns or rows.
  • Data Independence: The underlying table structure can change without affecting the applications that use the view (as long as the view definition remains compatible).

Creating a View: 

CREATE VIEW ComputerScienceStudents AS
SELECT StudentID, FirstName, LastName, Email
FROM Students
WHERE Major = 'Computer Science';

After creating this view, you can query it like a regular table: 

SELECT FirstName, Email FROM ComputerScienceStudents WHERE StudentID = 105;

Modifying a View: Use CREATE OR REPLACE VIEW or specific ALTER VIEW commands depending on the SQL dialect.

Dropping a View: 

DROP VIEW ComputerScienceStudents;

Stored Procedures

A stored procedure is a prepared SQL code that you can save in the database. This means you don't have to write the code every time you want to perform an operation. It can accept input parameters and return output parameters.

Benefits of Stored Procedures:

  • Performance: Pre-compiled and optimized by the database engine.
  • Reusability: Write once, call many times.
  • Reduced Network Traffic: Instead of sending multiple SQL statements, you send just one call to the procedure.
  • Security: Can grant execute permissions on procedures without granting direct table access.
  • Maintainability: Centralized logic makes updates easier.

Creating a Stored Procedure (Example in MySQL syntax): 

DELIMITER //
CREATE PROCEDURE GetStudentByMajor (IN major_name VARCHAR(50))
BEGIN
    SELECT StudentID, FirstName, LastName, Email
    FROM Students
    WHERE Major = major_name;
END //
DELIMITER ;

Calling a Stored Procedure: 

CALL GetStudentByMajor('Computer Science');

Stored procedures can also contain complex logic, loops, conditional statements, and error handling, making them powerful tools for application development.

Triggers

A trigger is a special type of stored procedure that automatically executes or is raised when an event occurs in the database. These events are typically INSERT, UPDATE, or DELETE statements on a particular table.

Use Cases for Triggers:

  • Auditing: Recording changes made to data in an audit log table.
  • Enforcing complex business rules: Ensuring data integrity beyond simple constraints.
  • Maintaining data consistency: Automatically updating related data in other tables.
  • Preventing invalid transactions: Rolling back operations if certain conditions aren't met.

Creating a Trigger (Example in MySQL syntax): Let's create a trigger to log changes to the Students table into a 'StudentAudit' table. 

-- First, create the audit table
CREATE TABLE StudentAudit (
    AuditID INT AUTO_INCREMENT PRIMARY KEY,
    StudentID INT,
    ActionType VARCHAR(10),
    ChangeTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Now, create the trigger
DELIMITER //
CREATE TRIGGER LogStudentUpdate
AFTER UPDATE ON Students
FOR EACH ROW
BEGIN
    INSERT INTO StudentAudit (StudentID, ActionType)
    VALUES (OLD.StudentID, 'UPDATE'); -- OLD refers to the row values before the UPDATE
END //
DELIMITER ;

This trigger will automatically insert a record into StudentAudit every time a row in the Students table is updated. You can also create triggers for INSERT (using NEW values) and DELETE (using OLD values).

Types of Triggers:

  • BEFORE Trigger: Executes before the triggering event occurs. Useful for validating or modifying data before it's inserted or updated.
  • AFTER Trigger: Executes after the triggering event occurs. Useful for logging, cascading updates, or enforcing rules that depend on the completion of the original operation.

FOR EACH ROW: This clause indicates that the trigger action should be performed for every single row affected by the triggering statement.

SQL Injection Issues

SQL Injection (SQLi) is a code injection technique used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution. It's one of the most common web application vulnerabilities.

How it Works: Imagine a login form where the username and password are used directly in a SQL query like this: 

String query = "SELECT * FROM Users WHERE Username = '" + username + "' AND Password = '" + password + "'";

If a malicious user enters `' OR '1'='1` as the username and anything as the password, the query becomes: 

SELECT * FROM Users WHERE Username = '' OR '1'='1' AND Password = '...';

Since `'1'='1'` is always true, the WHERE clause evaluates to true for all rows, potentially allowing the attacker to bypass authentication and log in as the first user in the database, or even retrieve all user data.

Consequences of SQL Injection:

  • Unauthorized access to sensitive data (e.g., user credentials, financial information).
  • Data modification or deletion.
  • Gaining administrative control over the database server.
  • Executing operating system commands (in some configurations).

Preventing SQL Injection

Preventing SQL injection is crucial for database security. The primary methods involve treating user input as data, not executable code.

  • Prepared Statements (Parameterized Queries): This is the most effective defense. Instead of concatenating user input directly into SQL strings, you use placeholders for the values. The database driver then ensures that the input is treated purely as data, preventing it from being interpreted as SQL commands.

Example using Prepared Statements (Conceptual Java/JDBC): 

String sql = "SELECT * FROM Users WHERE Username = ? AND Password = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, username); // User input for username
pstmt.setString(2, password); // User input for password
ResultSet rs = pstmt.executeQuery();

In this example, ? are placeholders. The setString() method binds the user-provided values to these placeholders, ensuring they are treated as literal strings and not executable SQL.

  • Input Validation: While not a complete solution on its own, validating user input to ensure it conforms to expected formats (e.g., an email address should look like an email address, a number should be a number) can help filter out some malicious attempts.
  • Escaping Special Characters: Manually escaping special characters (like single quotes, double quotes, backslashes) in user input before including them in SQL queries. However, this is error-prone and less secure than prepared statements.
  • Least Privilege Principle: Ensure database users have only the minimum necessary permissions. For example, a web application user should not have DROP or ALTER privileges.
  • Web Application Firewalls (WAFs): WAFs can help detect and block common SQL injection patterns.

Exam Tip: Always remember that prepared statements are the gold standard for preventing SQL injection. Understand the difference between DDL, DML, DCL, and TCL commands, as questions often test this classification. Views simplify complex queries, Stored Procedures enhance performance and reusability, and Triggers automate actions based on database events. Be aware of the security implications of improper data handling, especially SQL injection.

```
All E-Books
Online Test SQL: DDL-DML, queries, views, stored procedures, triggers and SQL injection issues. One Line Questions SQL: DDL-DML, queries, views, stored procedures, triggers and SQL injection issues. Question Bank SQL: DDL-DML, queries, views, stored procedures, triggers and SQL injection issues.