SQL Language Components

SQL consists of two primary components: Data Manipulation Language (DML) and Data Definition Language (DDL).

Data Manipulation Language (DML)

DML handles database queries and updates:

• SELECT - Retrieve data from database tables
• UPDATE - Modify existing records in tables
• DELETE - Remove records from tables
• INSERT INTO - Add new records to tables

Data Definition Language (DDL)

DDL manages database structure and schema:

• CREATE DATABASE - Initialize new database
• ALTER DATABASE - Modify database properties
• CREATE TABLE - Define new table structure
• ALTER TABLE - Change existing table schema
• DROP TABLE - Remove table entirely
• CREATE INDEX - Create search optimization keys
• DROP INDEX - Remove existing indexes

Essential SQL Keywords and Operations

Data Filtering and Selection

DISTINCT - Retrieve unique values from specified columns:

SELECT DISTINCT column_name FROM table_name;

ORDER BY - Sort result sets by specified columns (ASC ascending, DESC descending)

LIMIT - Restrict number of returned records

LIKE - Pattern matching with wildcards

IN - Filter records matching specified values:

SELECT * FROM Employees
WHERE Department IN ('Engineering', 'Sales');

Table Joins

Combine data from multiple tables using JOIN operations:

INNER JOIN - Returns matching records from both tables:

SELECT e.LastName, e.FirstName, p.ProjectName
FROM Employees e
INNER JOIN Projects p ON e.EmployeeID = p.ManagerID;

LEFT JOIN - Returns all records from left table with matches from right table

RIGHT JOIN - Returns all records from right table with matches from left table

FULL JOIN - Returns records when match exists in either table

Data Combination

UNION - Combine results from multiple SELECT statements (removes duplicates):

SELECT CustomerName FROM DomesticCustomers
UNION
SELECT CustomerName FROM InternationalCustomers;

UNION ALL - Combine results including duplicate records

Data Backup

SELECT INTO - Create table backups:

SELECT * INTO EmployeeBackup FROM Employees;

Database Constraints

Constraints enforce data integrity rules:

NOT NULL - Prevent NULL values in columns:

CREATE TABLE Products (
    ProductID int NOT NULL,
    ProductName varchar(255) NOT NULL
);

UNIQUE - Ensure column values are distinct

PRIMARY KEY - Unique identifier for table records (one per table):

PRIMARY KEY (ProductID)

FOREIGN KEY - Establish relationships between tables:

ALTER TABLE Orders
ADD CONSTRAINT fk_CustomerOrder
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

CHECK - Validate data against specific conditions:

CHECK (Age >= 18 AND Age <= 65)

DEFAULT - Set automatic values for columns:

OrderDate datetime DEFAULT CURRENT_TIMESTAMP

Data Management Operations

TRUNCATE TABLE - Remove all records while preserving table structure:

TRUNCATE TABLE TemporaryData;

Views - Virtual tables based on SQL query results

Data Aggregation

GROUP BY - Organize results by specified columns for aggregate functions:

SELECT Department, COUNT(EmployeeID), AVG(Salary)
FROM Employees
GROUP BY Department;

HAVING - Filter grouped results (works with aggregate functions):

SELECT Department, AVG(Salary)
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000;

SQL Syntax Guidelines

• Enclose text values in single quotes
• Numeric values require no quotation marks
• SQL execution order follows specific sequence:

1. FROM and JOIN operations
2. WHERE clause filtering
3. GROUP BY aggregation
4. HAVING clause on grouped data
5. SELECT column selection
6. DISTINCT duplicate removal
7. ORDER BY sorting
8. LIMIT result restriction