Creating Objects

Data Definition Language (DDL) is the subset of SQL used to create, modify, and delete the structure of database objects like tables, views, and indexes.

Definition: Tables vs. Views

Table: A physical object that stores data on the disk.
View: A “Virtual Table.” It does not store data itself; it stores a query. When you select from a view, the database runs the underlying query in the background.

Context: Why use Views?

Security: You can give a user access to a view that hides sensitive columns (like Salary) while showing other columns from the same table.
Simplicity: If you have a complex 5-table join that you use every day, you can save it as a view. Instead of writing 50 lines of code, you just write SELECT * FROM my_view;.
Consistency: It ensures everyone in the organization is using the same logic for “Total Sales” or “Active Users.”

Detailed Explanation: Syntax

1. Creating a Table

CREATE TABLE Products (
  ProductID   INT PRIMARY KEY,
  Name        VARCHAR(100) NOT NULL,
  Price       DECIMAL(10,2) DEFAULT 0.00,
  CreatedDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. Creating a View

CREATE VIEW HighValueProducts AS
SELECT Name, Price
FROM Products
WHERE Price > 500;

Example: The Reporting Layer

Imagine a complex query that calculates monthly revenue.

CREATE VIEW MonthlyRevenueReport AS
SELECT
  EXTRACT(YEAR FROM OrderDate) AS Year,
  EXTRACT(MONTH FROM OrderDate) AS Month,
  SUM(TotalAmount) AS Revenue
FROM Orders
GROUP BY 1, 2;

Now, a Business Analyst can simply run: SELECT * FROM MonthlyRevenueReport WHERE Year = 2023;

Under the Hood: Storage and Performance

1. View Merging

When you query a view, the query optimizer “merges” your query with the view’s definition. If the view is SELECT * FROM Products and you run SELECT * FROM view WHERE ID = 1, the optimizer converts it to SELECT * FROM Products WHERE ID = 1. This means views are generally as fast as the underlying query.

2. Materialized Views

In some databases (Postgres, Oracle), you can create a Materialized View. This does store the data on disk. It’s like taking a snapshot of a complex query. It’s extremely fast to read but must be “Refreshed” manually to see new data.

3. Temporary Tables

You can also create tables that only exist for your current session: CREATE TEMP TABLE my_temp AS SELECT ... These are automatically deleted when you disconnect from the database.