SQL is categorized based on its functional scope, primarily into four types: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Data Query Language (DQL).

DDL operates on database structures like schemas, tables, indexes, and views. Core commands are CREATE, DROP, and ALTER. For example:

-- Database creation
CREATE SCHEMA inventory_db;
CREATE SCHEMA backup_db;

-- Table creation
CREATE TABLE inventory_db.products(product_id INT);
CREATE TABLE inventory_db.suppliers(sup_id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE inventory_db.orders(ord_id INT, product_ref INT, qty INT);

-- Index and view creation
CREATE INDEX idx_product ON inventory_db.products(product_id);
CREATE UNIQUE INDEX idx_order_qty ON inventory_db.orders(qty);
CREATE VIEW product_view AS SELECT * FROM inventory_db.products;

-- Table modifications
ALTER TABLE inventory_db.products DROP INDEX idx_product;
ALTER TABLE inventory_db.products ADD PRIMARY KEY(product_id);
ALTER TABLE inventory_db.products ADD description VARCHAR(255);
ALTER TABLE inventory_db.suppliers DROP PRIMARY KEY;

-- Dropping objects
DROP TABLE inventory_db.suppliers;
DROP SCHEMA backup_db;
DROP VIEW product_view;

DML and DQL operate on data records within tables. DML commands (INSERT, UPDATE, DELETE) modify data, while DQL (SELECT) retrieves it.

CREATE SCHEMA sales_data;
CREATE TABLE sales_data.transactions(txn_id INT);

-- Inserting data
INSERT INTO sales_data.transactions VALUES (101), (102), (103);

-- Updating data
UPDATE sales_data.transactions SET txn_id = 500 WHERE txn_id > 102;

-- Deleting data
DELETE FROM sales_data.transactions WHERE txn_id = 101;

-- Creating another table and inserting data
CREATE TABLE sales_data.details(id INT, item_code INT, amount INT);
INSERT INTO sales_data.details VALUES (1, 1001, 50), (2, 1002, 75);
INSERT INTO sales_data.details SELECT * FROM sales_data.details;
INSERT INTO sales_data.details(id, item_code) VALUES (999, 2000);

-- Querying data with DQL
SELECT * FROM sales_data.details;

DCL manages database access and transactions. Key commands are GRANT, REVOKE, COMMIT, and ROLLBACK.

Database systems maintain several log files for operational management and troubleshooting:

• Error Log: Records server startup, shutdown, and critical errors.
• General Query Log: Captures all client connections and SQL statements.
• Binary Log (binlog): Logs data changes for replication and point-in-time recovery.
• Slow Query Log: Identifies queries exceeding a defined execution time.
• Relay Log: Used by replication slaves to hold data changes from the master's binlog before applying them.

Configuration for these logs is typically set in the server configuration file (e.g., my.cnf for MySQL). The binary log cycles to a new file upon server restart, execution of FLUSH LOGS, or when the current file reaches its maximum size.

Example configuration snippets:

# Error, General, and Slow Log Settings
log_error = /var/log/mysql/error.log
general_log = 1
general_log_file = /var/log/mysql/general.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1.0
log_queries_not_using_indexes = 1

# Binary Log Settings
log_bin = /var/log/mysql/mysql-bin
log_bin_index = /var/log/mysql/mysql-bin.index
sync_binlog = 1000
binlog_format = ROW
max_binlog_size = 1073741824
expire_logs_days = 7

The binary log content, which includes events like database or table creation and data modifications, can be viewed with utiliteis like mysqlbinlog.