Data Insertion Principles

When populating relational tables, observe the following storage and syntax behaviors:

• Integer Display Width: The width parameter for INT columns controls display padding when ZEROFILL is applied. The underlying storage consistently consumes four bytes regardless of the specified width.
• Date Parsing: Database engines accept multiple date separators. Inputs formatted as '2023-12-25', '2023/12/25', or '2023.12.25' are processed identically.
• String Delimiters: Both single (') and double (\") quotation marks are valid for defining string literals.
• Dynamic Timestamps: Invoke NOW(), SYSDATE(), or CURRENT_DATE() within INSERT statements to capture the exact execution time.
• Character vs Byte Length: CHAR and VARCHAR measure character count, not byte size. For byte-oriented storage, utilize BINARY or VARBINARY.
• Selective Column Insertion: When omitting specific fields, the query must explicitly list the target column names in the header.

-- Retrieve current dataset
SELECT * FROM staff_profiles;

-- Insert a complete record
INSERT INTO staff_profiles VALUES 
(1001, 'Alice Chen', 'Female', 28, '2023-05-15', 'Engineering', 'alice.c@corp.com');

-- Utilize alternative date formats and mixed quotation styles
INSERT INTO staff_profiles VALUES 
(1002, "Bob Smith", "Male", 32, '2023/05/15', 'Engineering', 'bob.s@corp.com');

-- Leverage built-in functions for automatic timestamp generation
INSERT INTO staff_profiles VALUES 
(1003, "Dana Wu", 'Female', 25, NOW(), 'Design', 'dana.w@corp.com');

-- Perform partial insertion by explicitly declaring target fields
INSERT INTO staff_profiles (emp_id, full_name, join_date) VALUES 
(1004, 'Evan Park', '2023-07-20');

Modifying and Purging Records

Updating or deleting existing data requires strict adherence to syntax rules and operational safety:

• Identifier Casing: Reserved keywords, table names, and column identifiers are generally case-insensitive.
• String Copmarison: Default colllation rules typically ignore case differences during text matching.
• Deletion Syntax: The FROM keyword is mandatory in standard DELETE statements.
• Operational Safety: Always append a WHERE clause to UPDATE and DELETE commands. Omitting the condition will alter or erase every row in the target table.

-- Update a specific column across all rows
UPDATE staff_profiles SET gender = 'Non-binary';

-- Apply changes to a single record using primary identifier
UPDATE staff_profiles SET gender = 'Male' WHERE emp_id = 1004;

-- Identifier casing does not impact query execution
UPDATE STAFF_PROFILES SET years_old = 33 WHERE emp_id = 1004;

-- Modify department assignment with case variation
UPDATE staff_profiles SET department = 'Cloud_Services' WHERE emp_id = 1003;
UPDATE staff_profiles SET department = 'CLOUD_SERVICES' WHERE emp_id = 1002;

-- Filter updates based on existing string values
UPDATE staff_profiles SET years_old = 30 WHERE department = 'cloud_services';

-- Remove a targeted record
DELETE FROM staff_profiles WHERE emp_id = 1001;

Altering and Removing Schema Objects

Structural modifications adjust column definitions, reorder fields, or permanently remove database objects:

-- Inspect table state prior to structural adjustments
SELECT * FROM staff_profiles;

-- Append a new numeric column with defined precision
ALTER TABLE staff_profiles ADD performance_score DECIMAL(5,2);

-- Populate the newly added column
UPDATE staff_profiles SET performance_score = 98.45 WHERE emp_id = 1003;

-- Insert a column at the beginning of the schema
ALTER TABLE staff_profiles ADD performance_score DECIMAL(5,2) FIRST;

-- Insert a column immediately following a specific field
ALTER TABLE staff_profiles ADD performance_score DECIMAL(5,2) AFTER gender;

-- Completely remove a column from the table structure
ALTER TABLE staff_profiles DROP COLUMN performance_score;

-- Alter only the data type while preserving the original column name
ALTER TABLE staff_profiles MODIFY COLUMN performance_score DECIMAL(4,1);

-- Rename the column and modify its data type simultaneously
ALTER TABLE staff_profiles CHANGE COLUMN performance_score eval_rating DECIMAL(5,1);

-- Permanently eliminate the table and all associated data
DROP TABLE staff_profiles;