SQL Comprehensive Guide

1. Introduction to SQL

SQL (Structured Query Language) is a standard language used to interact with relational database systems. It allows users to query, manipulate, and manage data efficiently.

---

2. SQL Commands

SQL commands are categorized into the following types:

2.1 Data Definition Language (DDL)

Used to define and manage database schema.

Command	Description	Example
CREATE	Creates a new database, table, or other database objects.	CREATE TABLE students (id INT, name VARCHAR(50));
ALTER	Modifies the structure of an existing table.	ALTER TABLE students ADD COLUMN age INT;
DROP	Deletes a database or table.	DROP TABLE students;
TRUNCATE	Removes all records from a table but keeps its structure.	TRUNCATE TABLE students;

2.2 Data Manipulation Language (DML)

Used to manage data within tables.

Command	Description	Example
SELECT	Retrieves data from one or more tables.	SELECT * FROM students;
INSERT	Adds new records to a table.	INSERT INTO students (id, name) VALUES (1, 'John');
UPDATE	Modifies existing records in a table.	UPDATE students SET name = 'Jane' WHERE id = 1;
DELETE	Removes records from a table.	DELETE FROM students WHERE id = 1;

2.3 Data Control Language (DCL)

Used to control access to data.

Command	Description	Example
GRANT	Provides privileges to users.	GRANT SELECT, INSERT ON students TO user1;
REVOKE	Removes privileges from users.	REVOKE INSERT ON students FROM user1;

2.4 Transaction Control Language (TCL)

Manages database transactions.

Command	Description	Example
COMMIT	Saves all changes made during a transaction.	COMMIT;
ROLLBACK	Reverts changes made during a transaction.	ROLLBACK;
SAVEPOINT	Sets a point within a transaction to roll back to.	SAVEPOINT sp1;
SET TRANSACTION	Configures properties of a transaction.	SET TRANSACTION READ ONLY;

---

3. SQL Clauses

Clauses specify conditions for queries and data manipulation.

Clause	Description	Example
WHERE	Filters records based on a condition.	SELECT * FROM students WHERE age > 18;
GROUP BY	Groups rows sharing a property for aggregate functions.	SELECT age, COUNT(*) FROM students GROUP BY age;
HAVING	Filters aggregated data.	SELECT age, COUNT(*) FROM students GROUP BY age HAVING COUNT(*) > 2;
ORDER BY	Sorts data in ascending or descending order.	SELECT * FROM students ORDER BY name ASC;
LIMIT	Restricts the number of rows returned.	SELECT * FROM students LIMIT 5;

---

4. SQL Conditions

Used to define rules for filtering and data manipulation.

Condition	Description	Example
AND	Combines multiple conditions (all must be true).	SELECT * FROM students WHERE age > 18 AND name = 'John';
OR	Combines multiple conditions (any can be true).	SELECT * FROM students WHERE age > 18 OR name = 'John';
NOT	Reverses the result of a condition.	SELECT * FROM students WHERE NOT age > 18;
BETWEEN	Specifies a range of values.	SELECT * FROM students WHERE age BETWEEN 18 AND 25;
IN	Matches values in a list.	SELECT * FROM students WHERE name IN ('John', 'Jane');
LIKE	Searches for patterns in text.	SELECT * FROM students WHERE name LIKE 'J%';
IS NULL	Checks for null values.	SELECT * FROM students WHERE age IS NULL;

---

5. SQL Functions

5.1 Aggregate Functions

Perform calculations on multiple rows.

Function	Description	Example
COUNT()	Returns the number of rows.	SELECT COUNT(*) FROM students;
SUM()	Returns the sum of numeric values.	SELECT SUM(age) FROM students;
AVG()	Returns the average value.	SELECT AVG(age) FROM students;
MAX()	Returns the maximum value.	SELECT MAX(age) FROM students;
MIN()	Returns the minimum value.	SELECT MIN(age) FROM students;

5.2 Scalar Functions

Perform operations on a single value.

Function	Description	Example
UCASE()	Converts text to uppercase.	SELECT UCASE(name) FROM students;
LCASE()	Converts text to lowercase.	SELECT LCASE(name) FROM students;
LEN()	Returns the length of a string.	SELECT LEN(name) FROM students;
ROUND()	Rounds numeric values.	SELECT ROUND(age, 0) FROM students;
NOW()	Returns the current date and time.	SELECT NOW();

---

6. SQL Case Statements

Allows conditional logic in queries.

Statement	Description	Example
CASE	Executes conditional expressions.	SELECT name, CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END AS category FROM students;

---

7. SQL Joins

Combines rows from two or more tables.

Join Type	Description	Example
INNER JOIN	Returns rows with matching values in both tables.	SELECT * FROM students INNER JOIN courses ON students.course_id = courses.id;
LEFT JOIN	Returns all rows from the left table and matching rows from the right table.	SELECT * FROM students LEFT JOIN courses ON students.course_id = courses.id;
RIGHT JOIN	Returns all rows from the right table and matching rows from the left table.	SELECT * FROM students RIGHT JOIN courses ON students.course_id = courses.id;
FULL JOIN	Returns rows when there is a match in either table.	SELECT * FROM students FULL JOIN courses ON students.course_id = courses.id;
CROSS JOIN	Returns the Cartesian product of two tables.	SELECT * FROM students CROSS JOIN courses;

---

8. Advanced SQL Features

8.1 Subqueries

Queries nested within other queries.

Feature	Description	Example
Subquery	A query within a query.	SELECT name FROM students WHERE age = (SELECT MAX(age) FROM students);

8.2 Views

Virtual tables based on SQL queries.

Command	Description	Example
CREATE VIEW	Creates a view.	CREATE VIEW adult_students AS SELECT * FROM students WHERE age >= 18;
DROP VIEW	Deletes a view.	DROP VIEW adult_students;

8.3 Indexes

Improve query performance.

Command	Description	Example
CREATE INDEX	Creates an index.	CREATE INDEX idx_name ON students(name);
DROP INDEX	Deletes an index.	DROP INDEX idx_name;

---

9. SQL Constraints

Define rules for data integrity.

Constraint	Description	Example
NOT NULL	Ensures a column cannot have null values.	CREATE TABLE students (id INT NOT NULL);
UNIQUE	Ensures all values in a column are unique.	CREATE TABLE students (email VARCHAR(100) UNIQUE);
PRIMARY KEY	Combines NOT NULL and UNIQUE.	CREATE TABLE students (id INT PRIMARY KEY);
FOREIGN KEY	Links two tables.	ALTER TABLE students ADD CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES courses(id);
CHECK	Ensures values satisfy a condition.	CREATE TABLE students (age INT CHECK (age >= 18));
DEFAULT	Sets a default value.	CREATE TABLE students (enrolled BOOLEAN DEFAULT TRUE);

---

10. SQL Practical Example

Task: Retrieve the names of students enrolled in the “Math” course.

SELECT s.name
FROM students s
INNER JOIN courses c ON s.course_id = c.id
WHERE c.name = 'Math';

---