An Outer Join is a type of database join (commonly used in SQL) that returns records from one or both tables even if there’s no matching record in the other table.

Types of Outer Joins:

1. LEFT OUTER JOIN (or simply: LEFT JOIN):
   → Returns all records from the left table, and the matching ones from the right table.
   → If there’s no match, the result is filled with NULL values from the right table.

2. RIGHT OUTER JOIN (or: RIGHT JOIN):
   → Returns all records from the right table, and the matching ones from the left table.
   → If there’s no match, NULL is used for the left side.

3. FULL OUTER JOIN:
   → Returns all records from both tables, with NULL where no match exists on either side.

Example:

Suppose you have two tables:

• Customers

  CustomerID	Name
  1	Anna
  2	Bernd
  3	Clara

• Orders

  OrderID	CustomerID	Product
  101	2	Book
  102	4	Lamp

LEFT JOIN (Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID)

PDO stands for PHP Data Objects and it's a database abstraction layer in PHP. It provides an object-oriented interface to access different types of databases—such as MySQL, PostgreSQL, or SQLite—without needing to change much of your code.

Key Features of PDO:

✅ Consistent API
Same code style regardless of which database you use.

✅ Prepared Statements
Helps prevent SQL injection via bound parameters:

$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute(['id' => $userId]);

✅ Transactions Support
Great for use cases like banking systems or other operations that need rollback/commit.

✅ Exception-based Error Handling

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

✅ Easy Database Switching
Switching from MySQL to PostgreSQL? Usually only the DSN and driver need to be changed.

Example: Connecting to MySQL with PDO

$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
$user = 'root';
$pass = '';

try {
    $pdo = new PDO($dsn, $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

Summary:

PDO is the recommended way to work with databases in modern PHP applications—especially for its security, flexibility, and ease of use.

Data Control Language (DCL) is a subset of SQL that focuses on managing access rights and permissions within a database. DCL commands are used to control who can do what in the database.

Main DCL Commands:

GRANT SELECT, INSERT ON Customers TO User123;
REVOKE INSERT ON Customers FROM User123;

Common Privileges:

• SELECT – Read data

• INSERT – Add new data

• UPDATE – Modify existing data

• DELETE – Remove data

• ALL – Grant all available privileges

Key Characteristics:

• DCL handles security and access control in the database.

• Typically used by a database administrator (DBA).

• Permissions can be granted at the table, column, or database level.

• DCL operations are often transaction-dependent, requiring a COMMIT to take effect.

Comparison with Other SQL Subsets:

• DDL (Data Definition Language) – Defines the database structure (e.g., tables)

• DML (Data Manipulation Language) – Works with the data itself (e.g., insert or update)

• TCL (Transaction Control Language) – Manages transactions (COMMIT, ROLLBACK)

• DCL (Data Control Language) – Manages permissions and user access

DQL stands for Data Query Language, and it's a subset of SQL (Structured Query Language). It is used specifically to query data from a database without modifying it.

Key Characteristics of DQL:

• Read-only: DQL is used to retrieve data, not to insert, update, or delete it.

• The primary command used is:

SELECT

Example:

SELECT name, birthdate FROM customers WHERE city = 'Berlin';

This command retrieves the names and birthdates of all customers living in Berlin — without changing any data.

DQL vs Other SQL Sub-languages:

💡 What is an Entity Manager?

An Entity Manager is a core component of ORM (Object-Relational Mapping) frameworks, especially in Java (JPA – Java Persistence API), but also in other languages like PHP (Doctrine ORM).

📦 Responsibilities of an Entity Manager:

1. Persisting:

   • Saves a new object (entity) to the database.

   • Example (Doctrine): $entityManager->persist($user);

2. Finding/Loading:

   • Retrieves an object by its ID or other criteria.

   • Example: $entityManager->find(User::class, 1);

3. Updating:

   • Tracks changes to objects and writes them to the database (usually via flush()).

4. Removing:

   • Deletes an object from the database.

   • Example: $entityManager->remove($user);

5. Managing Transactions:

   • Begins, commits, or rolls back transactions.

6. Handling Queries:

   • Executes custom queries, often using DQL (Doctrine Query Language) or JPQL.

🔁 Entity Lifecycle:

The Entity Manager tracks the state of entities:

• managed (being tracked),

• detached (no longer tracked),

• removed (marked for deletion),

• new (not yet persisted).

🛠 Example with Doctrine (PHP):

$user = new User();
$user->setName('Max Mustermann');

$entityManager->persist($user); // Mark for saving
$entityManager->flush();        // Write to DB

✅ Summary:

The Entity Manager is the central component for working with database objects — creating, reading, updating, deleting. It abstracts SQL and provides a clean, object-oriented way to interact with your data layer.

Doctrine DBAL (Database Abstraction Layer) is a PHP library that provides an abstraction layer for database access. It is part of the Doctrine project (a popular ORM for PHP), but it can be used independently of the ORM.

Purpose and Benefits of Doctrine DBAL:

Doctrine DBAL offers a unified API to interact with different databases (such as MySQL, PostgreSQL, SQLite, etc.) without writing raw SQL specific to each database system.

Key Features of Doctrine DBAL:

• Connection Management

• • Easily configure and manage connections to various database systems.

  • Supports connection pooling, transactions, and more.

• SQL Query Builder

• • Build SQL queries programmatically using an object-oriented API:

$qb = $conn->createQueryBuilder();
$qb->select('u.id', 'u.name')
   ->from('users', 'u')
   ->where('u.age > :age')
   ->setParameter('age', 18);
$stmt = $qb->executeQuery();

• Database Independence

  • The same code works with different database systems (e.g., MySQL, PostgreSQL) with minimal changes.

• Schema Management

  • Tools to create, update, and compare database schemas.

  • Useful for migrations and automation.

• Data Type Conversion

  • Automatically converts data between PHP types and database-native types.

use Doctrine\DBAL\DriverManager;

$conn = DriverManager::getConnection([
    'dbname' => 'test',
    'user' => 'root',
    'password' => '',
    'host' => 'localhost',
    'driver' => 'pdo_mysql',
]);

$result = $conn->fetchAllAssociative('SELECT * FROM users');

When to Use DBAL Instead of ORM:

You might choose DBAL without ORM if:

• You want full control over your SQL.

• Your project doesn't need complex object-relational mapping.

• You're working with a legacy database or custom queries.

Summary:

Doctrine DBAL is a powerful tool for clean, portable, and secure database access in PHP. It sits between raw PDO usage and a full-featured ORM like Doctrine ORM, making it ideal for developers who want abstraction and flexibility without the overhead of ORM logic.

An INNER JOIN is a term used in SQL (Structured Query Language) to combine rows from two (or more) tables based on a related column between them.

Example:

You have two tables:

Table: Customers

Table: Orders

Now you want to know which customers have placed orders. You only want the customers who exist in both tables.

SQL with INNER JOIN:

SELECT Customers.Name, Orders.Product
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result:

Explanation:

• Clara didn’t place any orders → not included.

• The order with CustomerID 4 doesn’t match any customer → also excluded.

In short:

An INNER JOIN returns only the rows with matching values in both tables.

An explicit join is a clear and direct way to define a join in an SQL query, where the type of join (such as INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL OUTER JOIN) is explicitly stated.

Example of an explicit join:

SELECT *
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

This makes it clear:

• Which tables are being joined (customers, orders)

• What kind of join is used (INNER JOIN)

• What the join condition is (ON customers.customer_id = orders.customer_id)

In contrast: Implicit join

An implicit join is the older style, using a comma in the FROM clause, and putting the join condition in the WHERE clause:

SELECT *
FROM customers, orders
WHERE customers.customer_id = orders.customer_id;

This works the same, but it's less clear and not ideal for complex queries.

Benefits of explicit joins:

• More readable and structured, especially with multiple tables

• Clear separation of join conditions (ON) and filter conditions (WHERE)

• Recommended in modern SQL development

The Flask Framework is a popular, lightweight web framework for the Python programming language. It's widely used for developing web applications and APIs and is known for its simplicity and flexibility. Flask is a micro-framework, meaning it provides only the core functionalities needed for web development without unnecessary extras. This keeps it lightweight and customizable.

Key Features of Flask

1. Minimalistic: Flask includes only essential features like routing, URL management, and template rendering.
2. Extensible: Additional features (e.g., database integration, authentication) can be added with extensions like Flask-SQLAlchemy or Flask-Login.
3. Flexibility: Developers have the freedom to design the application's architecture as they prefer, with no rigid rules.
4. Jinja2: Flask uses the Jinja2 template engine to dynamically render HTML pages.
5. Werkzeug: Flask is built on Werkzeug, a WSGI (Web Server Gateway Interface) library that serves as the foundation for many Python web applications.

When to Use Flask?

Flask is particularly suited for:

• Small to medium-sized projects
• Rapid prototyping
• APIs and microservices
• Projects where developers need maximum control over the structure

Simple Flask Application Example:

from flask import Flask

app = Flask(__name__)

@app.route('/')
def hello_world():
    return 'Hello, World!'

if __name__ == '__main__':
    app.run(debug=True)

Flask vs. Django

Compared to Django (a more comprehensive Python web framework), Flask is less opinionated and provides more freedom. While Django follows a "batteries-included" philosophy with many features built-in, Flask is ideal when you want to build only the parts you need.

Strapi is a headless CMS (Content Management System) built with JavaScript, designed specifically for developers. It offers a flexible and open solution for managing content and APIs. Here's an overview of Strapi's key features:

1. Headless CMS

• Headless means Strapi doesn't have a fixed frontend. Instead, it delivers content via APIs (REST or GraphQL) that can be consumed by any frontend (e.g., React, Vue.js, Angular, mobile apps, or even IoT devices).
• This allows for maximum flexibility, letting developers choose their preferred technology and frontend framework.

2. Open Source

• Strapi is fully open source and licensed under MIT.
• Developers can customize the source code, extend its functionality, or build their own plugins.

3. Features

• API Builder: Quickly create custom content types and APIs using an intuitive interface.
• User-Friendly Dashboard: Editors can manage content without requiring technical expertise.
• Extensibility: Supports custom plugins and middleware.
• Authentication & Permissions: Role-based access control ensures fine-grained control over user actions.
• Media Library: Includes built-in tools for managing images, videos, and other files.

4. Technology

• Strapi is written in Node.js, making it highly performant and scalable.
• It works with your choice of database, including SQLite, MongoDB, PostgreSQL, or MySQL.

5. Benefits

• Developer-Friendly: Prioritizes flexibility and a great developer experience.
• Cross-Platform: Ideal for websites, mobile apps, or even omnichannel projects.
• Quick Setup: You can have a fully functional API up and running in minutes.

6. Use Cases

• Blogs, e-commerce websites, mobile apps, landing pages, or even complex enterprise projects.