SQL Course Syllabus in Chennai at Payilagam

SQL Course Syllabus in Chennai

Module 1: Introduction to Databases and PostgreSQL

• 🔹 What is a Database? Types of databases
• 🔹 RDBMS vs NoSQL (with examples)
• 🔹 Why PostgreSQL?, Installing PostgreSQL and pgAdmin
• 🔹 Introduction to PostgreSQL ecosystem (CLI tools, GUI, drivers)

Module 2: SQL Basics – Table Operations & CRUD

• 🔹 Filtering with WHERE, AND, OR, IN, BETWEEN, LIKE
• 🔹 Sorting Results: ORDER BY
• 🔹 Limiting Data: LIMIT, OFFSET
• 🔹 Aggregate Functions: COUNT, SUM, AVG, MIN, MAX
• 🔹 Grouping Data: GROUP BY, HAVING

Module 3: Advanced Joins and Subqueries

• 🔹 Types of Joins:
  • ▪️ INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN
• 🔹 Writing Nested Queries and Subqueries
• 🔹 Using subqueries in SELECT, WHERE, FROM
• 🔹 Use cases in reporting and data analysis

Module 4: Views, Indexes, and Query Optimization

• 🔹 Creating and using VIEWs
• 🔹 Materialized Views: use cases and refreshing
• 🔹 Creating Indexes: B-tree
• 🔹 expression-based indexes
• 🔹 Query optimization basics with EXPLAIN ANALYZE
• 🔹 When and how indexes improve performance

Module 5: Transactions and Data Integrity

• 🔹 Transactions: BEGIN, COMMIT, ROLLBACK
• 🔹 Ensuring ACID properties
• 🔹 Save points and nested transactions
• 🔹 Handling transaction errors
• 🔹 Use cases: banking systems, form submissions

Module 6: Functions, Stored Procedures, and Triggers

• 🔹 Defining SQL & PL/pgSQL functions
• 🔹 Returning values, variables, and conditional logic
• 🔹 Stored Procedures
• 🔹 Triggers:
  • ▪️ BEFORE and AFTER triggers for INSERT, UPDATE,
  • ▪️ DELETE

Module 7: Relational Modeling and Database Design

• 🔹 Normalization:
  • 1NF, 2NF, 3NF with examples
• 🔹 Entity Relationship Diagrams (ERD)
• 🔹 Implementing:
  • ▪️ One-to-One
  • ▪️ One-to-Many
  • ▪️ Many-to-Many (junction tables)
• 🔹 Using UUID as Primary Keys

Module 8: Access Control and Security

• 🔹 Creating database users and roles
• 🔹 Granting and revoking permissions:
  • ▪️ GRANT, REVOKE
• 🔹 Role-based access control (RBAC)
• 🔹 Best practices for securing PostgreSQL in production
• 🔹 Schema-level and table-level permissions