COMPLETE
SQL
Guide
Topic Wise Functions
1:
Basic SQL Syntax
▪ SQL keywords
▪ Data types
▪ Operators
▪ SQL statements
▪ SELECT
▪ INSERT
▪ UPDATE
▪ DELETE
2:
Data Definition
Language (DDL)
▪ CREATE TABLE
▪ ALTER TABLE
▪ DROP TABLE
▪ TRUNCATE TABLE
3:
Data Manipulation
Language (DML)
▪ SELECT statement (SELECT,
FROM, WHERE, ORDER BY,
GROUP BY, HAVING, JOINs)
▪ INSERT statement
▪ UPDATE statement
▪ DELETE statement
4:
Aggregate Functions
▪ SUM, AVG, COUNT, MIN, MAX
▪ GROUP BY clause
▪ HAVING clause
5:
Data Constraints
▪ Primary Key
▪ Foreign Key
▪ Unique
▪ NOT NULL
▪ CHECK
6:
Joins
▪ INNER JOIN
▪ LEFT JOIN
▪ RIGHT JOIN
▪ FULL OUTER JOIN
▪ SELF JOIN
▪ CROSS JOIN
7:
Subqueries
▪ Types of subqueries (scalar,
column, row, table)
▪ Nested subqueries
▪ Correlated subqueries
8:
Advanced SQL
Functions
▪ String functions
▪ CONCAT, LENGTH, SUBSTRING,
REPLACE, UPPER, LOWER
▪ Date and time functions
▪ DATE, TIME, TIMESTAMP, DATEPART,
DATEADD
▪ Numeric functions:
▪ ROUND, CEILING, FLOOR, ABS, MOD
▪ Conditional functions:
▪ CASE, COALESCE, NULLIF
9:
Views
▪ Creating views
▪ Modifying views
▪ Dropping views
10:
Indexes
▪ Creating indexes
▪ CREATE INDEX
▪ Using indexes for query
optimization
11:
Transactions
▪ ACID properties
▪ Transaction management:
▪ BEGIN, COMMIT, ROLLBACK,
SAVEPOINT
▪ Transaction isolation levels
12:
Data Integrity and
Security
▪ Data integrity constraint
▪ Referential integrity, entity integrity
▪ GRANT and REVOKE
statements (granting and
revoking permissions)
▪ Database security best
practices
13:
Stored Procedures and
Functions
▪ Creating stored procedures
▪ CREATE PROCEDURE
▪ Executing stored procedures
▪ EXEC
▪ Creating functions
▪ Using functions in queries
14:
Performance
Optimization
▪ Query optimization
techniques
▪ Using indexes, optimizing joins,
reducing subqueries
▪ Performance tuning best
practices
15:
Advanced SQL
Concepts
▪ Recursive queries
▪ Pivot and unpivot operations
▪ Window functions:
▪ Row_number, rank, dense_rank, lead
& lag
▪ CTEs (Common Table
Expressions)
▪ Dynamic SQL
16:
Schema Management
▪ Creating schemas
▪ CREATE SCHEMA
▪ Altering schemas
▪ ALTER SCHEMA
▪ Dropping schemas
▪ DROP SCHEMA
17:
Sequences and
Identity Columns
▪ Creating sequences/identity
columns
▪ Using sequences/identity
columns
18:
Triggers
▪ Creating triggers
▪ CREATE TRIGGER
▪ Using triggers
19:
Error Handling
▪ TRY-CATCH blocks
▪ Raising custom exceptions
20:
Advanced SQL Data
Types
▪ BLOB
▪ CLOB
▪ ENUM
▪ SET, etc.
21:
Partitioning
▪ Creating partitions
▪ PARTITION
▪ Using partitions
22:
Regular Expressions
▪ Using REGEXP for pattern matching
in queries
▪ . (Dot): Matches any single character, except
newline characters
▪ * (Asterisk): Matches zero or more of the
preceding character or group
▪ + (Plus): Matches one or more of the
preceding character or group
▪ ? (Question mark): Matches zero or one of
the preceding character or group
▪ ^ (Caret): Matches the start of a string
without consuming any characters
▪ $ (Dollar): Matches the end of a string
without consuming any characters
23:
Temporal Tables
▪ Creating temporal tables
▪ E.g. GENERATED ALWAYS
▪ Using temporal tables
24:
Cursors
▪ Understanding and using
cursors
▪ DECLARE
▪ OPEN
▪ CLOSE
▪ DEALLOCATE
THANK YOU
If this helps, click on the
icon on my profile to
receive updates on similar
posts