Lesson 4: Administrative SQL and Schema Objects

A DBA must know SQL not only for querying data, but also for controlling database structures.

Three categories are especially important:

For database administration, DDL is central because schema changes affect the whole system. Creating a table is easy in class. In production, it requires more care:

• Is the table name consistent?
• Are constraints included?
• Will it affect existing applications?
• Is there a rollback plan?

A careless ALTER TABLE can break reports, increase downtime, or invalidate application code. That is why DBAs prefer controlled changes, documented versions, and test validation before production release.

Schema objects help organize logic and control access.

Views

A view is a saved query presented like a table. Views are useful when:

• you want users to see only selected columns
• you want to simplify repeated joins
• you want to enforce a more stable interface for reports

Example idea: a cashier may need student balances, but not confidential profile details.

Stored routines

Depending on the DBMS, you may create stored procedures or functions. These package SQL logic on the server side. DBAs care about them because they affect:

• maintainability
• consistency
• permissions
• performance patterns

Triggers

A trigger runs automatically when an event occurs, such as an insert or update. Triggers can support:

• audit logging
• automatic timestamps
• rule enforcement

But triggers should be used carefully. Hidden automatic behavior can make systems harder to debug. A DBA should ask whether the logic is truly best placed in the database.