Learn the fundamentals of SQL and relational databases.
- What is SQL?
- History and evolution
- Relational database concepts
- RDBMS overview
- SQL standards (ANSI SQL)
- Database management systems
- Setting up database environment
- First SQL queries
Understand database structure and table fundamentals.
- Database and schema concepts
- Tables, rows, and columns
- Data types
- Primary keys
- Foreign keys
- Constraints
- Indexes
- Table relationships
Master the fundamental SELECT statement for data retrieval.
- SELECT syntax
- Selecting columns
- WHERE clause
- Comparison operators
- Logical operators
- LIKE and wildcards
- IN and BETWEEN
- NULL values
Learn to sort and filter query results effectively.
- ORDER BY clause
- Ascending vs descending
- Multiple column sorting
- DISTINCT keyword
- LIMIT and TOP
- OFFSET and pagination
- Complex WHERE conditions
- Case sensitivity
Perform calculations and summarize data with aggregate functions.
- COUNT function
- SUM and AVG functions
- MIN and MAX functions
- GROUP BY clause
- HAVING clause
- Grouping multiple columns
- NULL handling in aggregates
- Statistical functions
Combine data from multiple tables using various join types.
- Join concepts
- INNER JOIN
- LEFT JOIN (LEFT OUTER JOIN)
- RIGHT JOIN (RIGHT OUTER JOIN)
- FULL OUTER JOIN
- CROSS JOIN
- Self joins
- Multiple table joins
Insert, update, and delete data in database tables.
- INSERT statements
- INSERT with SELECT
- UPDATE statements
- Conditional updates
- DELETE statements
- Conditional deletions
- TRUNCATE vs DELETE
- Data integrity during modifications
Write complex queries using subqueries and nested SELECT statements.
- Subquery concepts
- Scalar subqueries
- Row subqueries
- Table subqueries
- Correlated subqueries
- EXISTS and NOT EXISTS
- IN and NOT IN with subqueries
- Subquery performance considerations
Use built-in functions for string manipulation, date handling, and data conversion.
- String functions
- Date and time functions
- Numeric functions
- Conversion functions
- Conditional functions (CASE, IIF)
- NULL handling functions
- Window functions
- User-defined functions
Learn database design principles and normalization techniques.
- Database design principles
- Entity-Relationship (ER) modeling
- Normalization (1NF, 2NF, 3NF)
- Denormalization
- Primary and foreign key design
- Referential integrity
- Database schema design
- Performance considerations
Optimize query performance with indexes and performance tuning techniques.
- Index types and structures
- Creating and dropping indexes
- Clustered vs non-clustered indexes
- Composite indexes
- Query execution plans
- Performance monitoring
- Index maintenance
- Query optimization strategies
Create reusable database objects with views and stored procedures.
- Creating and managing views
- Updatable views
- Materialized views
- Stored procedure basics
- Parameters and return values
- Control flow in procedures
- Functions vs procedures
- Security and permissions
Manage data consistency and handle concurrent access with transactions.
- ACID properties
- Transaction control (BEGIN, COMMIT, ROLLBACK)
- Isolation levels
- Locking mechanisms
- Deadlocks
- Concurrency control
- Transaction logs
- Recovery procedures
Explore advanced SQL features like CTEs, window functions, and pivoting.
- Common Table Expressions (CTEs)
- Recursive CTEs
- Window functions (ROW_NUMBER, RANK)
- PIVOT and UNPIVOT
- MERGE statements
- Analytical functions
- Regular expressions in SQL
- JSON data handling
Implement security measures and manage database access control.
- User management
- Roles and permissions
- GRANT and REVOKE statements
- Schema-level security
- SQL injection prevention
- Data encryption
- Audit trails
- Security best practices
Learn database administration tasks including backup, recovery, and maintenance.
- Database backup strategies
- Recovery procedures
- Database maintenance
- Performance monitoring
- Space management
- Database migration
- Version control for databases
- Automation and scripting