Complete SQL Guide(Topic wise functions)

Basic SQL Syntax

• SQL keywords
• Data types
• Operators
• SQL statements
• SELECT
• INSERT
• UPDATE
• DELETE

Data Definition Language (DDL)

• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• TRUNCATE TABLE

Data Manipulation Language (DML)

• SELECT statement (SELECT,
• FROM, WHERE, ORDER BY,
• GROUP BY, HAVING, JOINs)
• INSERT statement
• UPDATE statement
• DELETE statement

Aggregate Functions

• SUM, AVG, COUNT, MIN, MAX
• GROUP BY clause
• HAVING clause

Data Constraints

• Primary Key
• Foreign Key
• Unique
• NOT NULL
• CHECK

Joins

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• SELF JOIN
• CROSS JOIN

Subqueries

• Types of subqueries (scalar, column, row, table)
• Nested subqueries
• Correlated subqueries

Advanced SQL Functions

• String functions ( CONCAT, LENGTH, SUBSTRING, REPLACE, UPPER, LOWER)
• Date and time functions (DATE, TIME, TIMESTAMP, DATEPART, DATE ADD)
• Numeric functions: (ROUND, CEILING, FLOOR, ABS, MOD)
• Conditional functions: ( CASE, COALESCE, NULLIF)

Views

• Creating views
• Modifying views
• Dropping views

Indexes

• Creating indexes (CREATE INDEX)
• Using indexes for query optimization

Transactions

• ACID properties
• Transaction management: (BEGIN, COMMIT, ROLLBACK, SAVEPOINT)
• Transaction isolation levels

Data Integrity and Security

• Data integrity constraint (Referential integrity, entity integrity)
• GRANT and REVOKE statements (granting and revoking permissions)
• Database security best practices

Stored Procedures and Functions

• Creating stored procedures (CREATE PROCEDURE)
• Executing stored procedures (EXEC)
• Creating functions
• Using functions in queries

Performance Optimization

• Query optimization techniques (Using indexes, optimizing joins, reducing subqueries)
• Performance tuning best practices

Advanced SQL Concepts

• Recursive queries
• Pivot and unpivot operations
• Window functions: (Row number, rank, dense rank, lead & lag)
• CTEs (Common Table Expressions)
• Dynamic SQL

Schema Management

• Creating schemas (CREATE SCHEMA)
• Altering schemas (ALTER SCHEMA)
• Dropping schemas (DROP SCHEMA)

Sequences and Identity Columns

• Creating sequences/identity columns
• Using sequences/identity columns

Triggers

• Creating triggers (CREATE TRIGGER)
• Using triggers

Error Handling

• TRY-CATCH blocks
• Raising custom exceptions

Advanced SQL Data Types

• BLOB
• CLOB
• ENUM
• SET, etc.

Partitioning

• Creating partitions (PARTITION)
• Using partitions

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 characters or group
• ? (Question mark): Matches zero or one of the preceding characters or group
• ^ (Caret): Matches the start of a string without consuming any characters
• $ (Dollar): Matches the end of a string without consuming any characters

Temporal Tables

• Creating temporal tables
  E.g. GENERATED ALWAYS
• Using temporal tables

Cursors

Understanding and using cursors
▪ DECLARE
▪ OPEN
▪ CLOSE
▪ DEALLOCATE