Scribd
Upload
7 views26 pages

sql-1

Uploaded by

Kai Pula
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views26 pages

sql-1

Uploaded by

Kai Pula
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 26

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

You might also like