SQL SUMMARY

1. RDBMS and Database Basics

  • What is SQL?: Structured Query Language (SQL) is the standard language used to communicate with, manage, and manipulate relational databases.

  • What is a Database?: An organized collection of structured data stored electronically in a computer system.

  • Relational Database Management System (RDBMS): Software used to create, maintain, and query relational databases (e.g., PostgreSQL, MySQL, SQL Server, Oracle).

  • Database Components:

    • Tables: Main structures where data is stored in rows and columns.

    • Rows & Columns: Rows (records) represent individual data items; columns (fields) represent attributes.

    • Primary Key: A column or combination of columns that uniquely identifies each row in a table.

    • Foreign Key: A column that establishes a link between data in two tables.

    • Data Types: Common data types include VARCHAR (text), INT (integers), DECIMAL (numbers with decimals), DATE, TIMESTAMP, and BOOLEAN.

2. Basic Querying (SELECT and WHERE)

  • SELECT: Specifies the columns to retrieve from a table.

  • FROM: Indicates the table from which to retrieve data.

  • WHERE: Filters records based on specified conditions.

  • DISTINCT: Returns only distinct (unique) values by removing duplicates.

  • ORDER BY: Sorts the output in ascending (ASC) or descending (DESC) order.

  • TOP / LIMIT: Restricts the maximum number of rows returned by the query.

3. Filtering Data (LIKE, BETWEEN, IN, AND, OR, NOT)

  • LIKE: Matches patterns in text columns using wildcards:

    • %: Represents zero, one, or multiple characters.

    • _: Represents a single character.

  • BETWEEN: Filters data within an inclusive range of values.

  • IN: Specifies multiple possible values for a column.

  • Logical Operators:

    • AND: Requires all conditions to be true.

    • OR: Requires at least one condition to be true.

    • NOT: Reverses the logical state of the condition.

4. SQL Functions & Aggregations

  • Aggregate Functions:

    • COUNT(): Returns the total count of rows or non-null values.

    • SUM(): Calculates the sum of numeric values.

    • AVG(): Calculates the arithmetic mean of numeric values.

    • MIN(): Returns the minimum value.

    • MAX(): Returns the maximum value.

  • Scalar Functions:

    • String Functions: UPPER(), LOWER(), LENGTH(), CONCAT(), SUBSTRING().

    • Date Functions: CURRENT_DATE, DATEADD(), DATEDIFF(), EXTRACT().

5. Grouping Data (GROUP BY & HAVING)

  • GROUP BY: Groups rows that have the same values in specified columns into summary rows.

  • HAVING: Filters groups produced by GROUP BY based on aggregate conditions (unlike WHERE, which filters individual rows before grouping).

6. Combining Tables (JOINs and UNIONs)

  • JOIN Types:

    • INNER JOIN: Retrieves rows with matching values in both tables.

    • LEFT JOIN: Retrieves all rows from the left table and matching rows from the right table.

    • RIGHT JOIN: Retrieves all rows from the right table and matching rows from the left table.

    • FULL OUTER JOIN: Retrieves all records when there is a match in either left or right table.

  • Set Operations:

    • UNION: Combines result sets of two queries and eliminates duplicate rows.

    • UNION ALL: Combines result sets of two queries while retaining all duplicate rows.

7. Data Manipulation & Definition (DML & DDL)

  • Data Manipulation Language (DML):

    • INSERT INTO: Adds new records to a table.

    • UPDATE: Modifies existing data in a table.

    • DELETE: Removes specific rows from a table.

  • Data Definition Language (DDL):

    • CREATE TABLE: Constructs a new table with defined column names and data types.

    • ALTER TABLE: Modifies an existing table structure.

    • DROP TABLE: Permanently deletes a table and all its data.

8. Conditional Logic (CASE WHEN)

  • CASE WHEN: Evaluates conditions and returns a value when the first condition is met (similar to IF-THEN-ELSE statements).

9. Subqueries

  • Subquery: A query embedded within another SQL query.

    • IN: Used in a subquery to check if a value matches any value in a subquery result set.

    • EXISTS: Evaluates to true if the subquery returns one or more rows.

10. Advanced SQL & Real-World Application

  • Performance Concepts: Indexing, query optimization, and understanding execution plans.

  • Data Warehousing & Projects: Connecting SQL queries with data modeling, ETL processes, and analytical frameworks for real-world projects.