Database and Table Manipulation Statements in SQL

Definition

There are several types of statements in SQL that are used to manipulate databases and the data they contain. Some of the most commonly used are:

• CREATE: creates a new database or table
• ALTER: modifies the structure of a database or table
• TRUNCATE: empties all data from table but table and structure remains
• DROP: deletes an entire table or database

Examples

Here is an example of each of these statements:

-- create a new table called "employees"
CREATE TABLE employees (
    id INT PRIMARY KEY, 
    name VARCHAR(255), 
    salary DECIMAL(10, 2)
);

-- add a new column called "address" to the "employees" table
ALTER TABLE employees 
ADD COLUMN address VARCHAR(255);

-- insert a new row of data into the "employees" table
INSERT INTO employees (id, name, salary, address) 
VALUES (1, 'John Doe', 50000, '123 Main St');

-- truncate the table employee
TRUNCATE TABLE employees;

-- drop the "employees" table
DROP TABLE employees;

These are the basic statements of SQL, you can use multiple JOINS, subqueries, with clause and UNION etc. to perform complex manipulation on data in database.

Best Practices

Here are a few best practices for working with database manipulation statements in SQL:

• Be very careful when executing queries that modify the structure of a table, as some queries can cause loss of data which can be non-reversible.

• If possible, first execute the queries in a test environment, before committing to executing them on a production database.

• If the timeframe, size and storage of the database allows for it, make sure to have recent backups before executing queries that have the potential to break the database.

• Before considering altering, creating or dropping a table or a database, do research if that is the only solution to your use case. In some cases, there might be better solutions such as adding additional relationships between tables or only adding columns to existing tables.