14. Data Manipulation Language (DML)
16. Data Control Language (DCL)
PostgreSQL Tutorial - 15. Data Query Language (DQL)

15.1 Introduction to Data Query Language (DQL)

Overview of DQL and its role in database management

Importance of DQL for retrieving and manipulating data

Comparison between DQL and other SQL languages (DDL, DML, DCL)


15.2 SELECT Statement

Retrieving data from one or more tables using SELECT

Specifying columns in the SELECT clause

Filtering data using the WHERE clause


15.3 JOIN Operations

Performing inner joins between tables

Using aliases for table names

Performing outer joins (LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN)


15.4 Subqueries

Writing subqueries within SELECT, FROM, WHERE, and HAVING clauses

Correlated vs. non-correlated subqueries

Using subqueries for filtering and aggregation


15.5 Set Operations

Combining query results with UNION, INTERSECT, and EXCEPT

Understanding set operations and their usage


15.6 Aggregate Functions

Calculating aggregate values (e.g., SUM, AVG, COUNT, MAX, MIN)

Using aggregate functions with GROUP BY clause

Filtering grouped data with HAVING clause


15.7 Window Functions

Introduction to window functions for advanced analytics

Performing calculations across rows with window functions

Ranking, partitioning, and aggregating data using window functions


15.8 String and Date Functions

Common string functions (e.g., CONCAT, SUBSTRING, UPPER, LOWER, REPLACE)

Date and time functions for manipulating date/time data

Formatting date/time values


15.9 Pivoting and Unpivoting Data

Transforming rows into columns with PIVOT operation

Unpivoting columns into rows with UNPIVOT operation

Using pivot and unpivot for data analysis and reporting


15.10 Dynamic SQL

Introduction to dynamic SQL and its usage

Building SQL statements dynamically at runtime

Executing dynamic SQL using EXECUTE statement


15.11 Common Table Expressions (CTEs)

Understanding common table expressions (CTEs)

Using CTEs for temporary result sets and recursive queries

Advantages and usage scenarios of CTEs


15.12 Query Optimization

Techniques for optimizing DQL queries for performance

Understanding query execution plans and optimization strategies

Improving query performance with indexing and statistics


14. Data Manipulation Language (DML)
16. Data Control Language (DCL)