Complete SQL Guide
SQL Basics
SQL (Structured Query Language) is used to communicate with relational databases. It is the
standard language for relational database management systems.
Key Concepts:
- Database: Organized collection of data.
- Table: Data is stored in tables (rows and columns).
- Row: A single record.
- Column: A field in the table.
Common SQL Statements:
- SELECT: Retrieves data
- INSERT: Adds data
- UPDATE: Modifies data
- DELETE: Removes data
Data Definition Language (DDL)
DDL deals with schema and database structure.
Commands:
- CREATE: Creates database objects (tables, indexes, etc.)
Example: CREATE TABLE Students (ID INT, Name VARCHAR(50));
- ALTER: Modifies existing database objects
Example: ALTER TABLE Students ADD Age INT;
- DROP: Deletes database objects
Example: DROP TABLE Students;
- TRUNCATE: Removes all records from a table but keeps the structure.
Example: TRUNCATE TABLE Students;
Data Manipulation Language (DML)
DML is used for managing data within schema objects.
Commands:
- INSERT: Adds new records
Example: INSERT INTO Students (ID, Name) VALUES (1, 'John');
- UPDATE: Modifies existing records
Example: UPDATE Students SET Name = 'Jane' WHERE ID = 1;
- DELETE: Removes records
Example: DELETE FROM Students WHERE ID = 1;
Data Query Language (DQL)
DQL is used to fetch data from databases.
Command:
- SELECT: Retrieves data from one or more tables.
Example: SELECT * FROM Students;
Example: SELECT Name FROM Students WHERE Age > 20;
Data Control Language (DCL)
DCL deals with rights, permissions, and other controls of the database system.
Commands:
- GRANT: Gives user's access privileges to database.
Example: GRANT SELECT ON Students TO user_name;
- REVOKE: Withdraws access privileges given by GRANT.
Example: REVOKE SELECT ON Students FROM user_name;
Transaction Control Language (TCL)
TCL is used to manage transactions in the database.
Commands:
- COMMIT: Saves all changes made during the transaction.
Example: COMMIT;
- ROLLBACK: Undoes changes since the last COMMIT.
Example: ROLLBACK;
- SAVEPOINT: Sets a point to roll back to.
Example: SAVEPOINT savepoint_name;
Joins, Views, Indexes
JOINS:
- INNER JOIN: Returns records with matching values.
Example: SELECT * FROM A INNER JOIN B ON [Link] = [Link];
- LEFT JOIN: Returns all records from the left table and matched records from the right.
- RIGHT JOIN, FULL JOIN: Similar logic.
VIEWS:
- A virtual table based on the result set of a query.
Example: CREATE VIEW StudentView AS SELECT Name, Age FROM Students;
INDEXES:
- Improve the speed of data retrieval.
Example: CREATE INDEX idx_name ON Students (Name);
Advanced Queries (Subqueries, Window Functions, etc.)
SUBQUERIES:
- A query within another query.
Example: SELECT Name FROM Students WHERE Age = (SELECT MAX(Age) FROM Students);
WINDOW FUNCTIONS:
- Perform calculations across a set of table rows.
Example: SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;
CASE STATEMENT:
- Acts like if-else logic.
Example: SELECT Name, CASE WHEN Age > 18 THEN 'Adult' ELSE 'Minor' END AS Status
FROM Students;