Core Concepts of SQL

Relational Databases

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. A relational database organizes data into one or more tables. Each table has a defined structure, with columns representing attributes and rows representing records.

Tables

Tables are the fundamental building blocks of a relational database. They consist of:

• Columns: Define the attributes or properties of the data (e.g., `CustomerID`, `FirstName`, `OrderDate`). Each column has a specific data type.
• Rows (or Records/Tuples): Represent individual entries or instances of data within a table (e.g., a specific customer's information).

Keys

Keys are crucial for establishing relationships between tables and ensuring data integrity.

• Primary Key: A column or set of columns that uniquely identifies each row in a table. It cannot contain NULL values and must be unique.
• Foreign Key: A column or set of columns in one table that refers to the primary key in another table. This establishes a link (relationship) between the two tables.

Data Integrity

Data integrity refers to the accuracy, consistency, and reliability of data throughout its lifecycle. SQL databases enforce integrity through various constraints:

• Entity Integrity: Ensured by primary keys, guaranteeing that each row is unique.
• Referential Integrity: Maintained by foreign keys, ensuring that relationships between tables are valid.
• Domain Integrity: Ensures that values in a column are valid according to their defined data type and any specified rules (e.g., `CHECK` constraints).

Constraints

Constraints are rules enforced on data columns to limit the type of data that can go into a table. Common constraints include:

• PRIMARY KEY
• FOREIGN KEY
• UNIQUE
• NOT NULL
• CHECK
• DEFAULT

SQL Statements

SQL statements are commands used to interact with a database. They are broadly categorized into the following groups:

Data Manipulation Language (DML)

Used for managing data within schema objects.

• SELECT: Retrieves data from a database.
• INSERT: Adds new records to a table.
• UPDATE: Modifies existing records in a table.
• DELETE: Removes records from a table.

Data Definition Language (DDL)

Used for defining and modifying the database structure.

• CREATE: Creates database objects (tables, views, etc.).
• ALTER: Modifies existing database objects.
• DROP: Deletes database objects.
• TRUNCATE: Removes all records from a table quickly.

Data Control Language (DCL)

Used for managing access to data and database objects.

• GRANT: Gives users permission to access the database.
• REVOKE: Takes away permissions from users.

Transaction Control Language (TCL)

Manages transactions within the database.

• COMMIT: Saves all changes made during a transaction.
• ROLLBACK: Undoes all changes made during a transaction.
• SAVEPOINT: Sets a point within a transaction to which you can later roll back.

Querying Data

The SELECT statement is the most frequently used SQL command. It allows you to retrieve specific data based on various criteria.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The WHERE clause is used to filter records, allowing you to specify conditions that must be met for a record to be included in the result set.