SQL: The Basic Syntax of SQL (Part III)

SQL DROP TABLE Statement

The DROP TABLE statement is used in SQL to delete a table from a database. It permanently removes the table and all of its data, and it cannot be undone.

Here is the basic syntax of the DROP TABLE statement:

DROP TABLE table_name;

The DROP TABLE statement is used to delete a table with the specified name.

Here is an example of the DROP TABLE statement:

DROP TABLE customers;

This statement deletes the “customers” table from the database.

SQL CREATE INDEX Statement

The CREATE INDEX statement is used in SQL to create an index on a table. An index is a data structure that allows faster retrieval of data from a table. It is used to improve the performance of SELECT, INSERT, UPDATE, and DELETE statements, and it is created on one or more columns of a table.

Here is the basic syntax of the CREATE INDEX statement:

CREATE INDEX index_name ON table_name (column1, column2, …);

The CREATE INDEX statement is used to create a new index with the specified name. The index name is followed by the ON keyword and the name of the table to create the index. The table name is followed by a list of columns to include in the index.

Here is an example of the CREATE INDEX statement:

CREATE INDEX idx_customer_name ON customers (name);

This statement creates a new index named “idx_customer_name” on the “customers” table, based on the “name” column.

SQL DROP INDEX Statement

The DROP INDEX statement is used in SQL to delete an index from a table. It permanently removes the index, and it cannot be undone.

Here is the basic syntax of the DROP INDEX statement:

DROP INDEX index_name;

The DROP INDEX statement is used to delete an index with the specified name.

Here is an example of the DROP INDEX statement:

DROP INDEX idx_customer_name;

This statement deletes the “idx_customer_name” index from the table it is associated with.

SQL DESC Statement

The DESC statement is used in SQL to display the structure of a table. It shows the column names, data types, and other information about the columns in the table.

Here is the basic syntax of the DESC statement:

DESC table_name;

The DESC statement is used to display the structure of a table with the specified name.

Here is an example of the DESC statement:

DESC customers;

This statement displays the structure of the “customers” table.

Note: The DESC statement is not standardized in SQL, and it may not be supported by all database systems. Some systems use the DESCRIBE statement instead.

As:

DESCRIBE table_name;

SQL TRUNCATE TABLE Statement

The TRUNCATE TABLE statement is used in SQL to delete all rows from a table and to reset the auto-increment counter (if the table has one). It is similar to the DELETE statement, but it is more efficient and faster because it does not generate any logging information.

Here is the basic syntax of the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name;

The TRUNCATE TABLE statement is used to delete all rows from a table with the specified name.

Here is an example of the TRUNCATE TABLE statement:

TRUNCATE TABLE customers;

This statement deletes all rows from the “customers” table and resets the auto-increment counter (if the table has one).

SQL ALTER TABLE Statement

The ALTER TABLE statement is used in SQL to modify the structure of an existing table. It allows you to add, modify, or delete columns, change the data type of a column, add or delete constraints, and more.

Here is the basic syntax of the ALTER TABLE statement:

ALTER TABLE table_name
action1,
action2,
…;

The ALTER TABLE statement is used to modify a table with the specified name. The table name is followed by one or more actions to perform on the table.

Here are some examples of the ALTER TABLE statement:

ALTER TABLE customers ADD COLUMN address VARCHAR(255);

This statement adds a new column named “address” to the “customers” table, with a data type of VARCHAR(255).

ALTER TABLE customers MODIFY COLUMN name VARCHAR(100) NOT NULL;

This statement modifies the “name” column in the “customers” table, changing the data type to VARCHAR(100) and adding the NOT NULL constraint.

ALTER TABLE customers DROP COLUMN address;

This statement deletes the “address” column from the “customers” table.

SQL ALTER TABLE Statement (Rename)

To rename a table using the ALTER TABLE statement, you can use the RENAME TO clause as follows:

ALTER TABLE old_table_name RENAME TO new_table_name;

This statement renames the table “old_table_name” to “new_table_name”.

Here is an example:

ALTER TABLE customers RENAME TO clients;

This statement renames the “customers” table to “clients”.

Note that the name of the table must be unique within the database, and it cannot be the same as any other existing table name.

SQL INSERT INTO Statement

The INSERT INTO statement is used in SQL to insert a new row into a table. It allows you to specify the values for each column in the new row, and it is used to add data to the table.

Here is the basic syntax of the INSERT INTO statement:

INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, …);

The INSERT INTO statement is used to insert a new row into a table with the specified name. The table name is followed by a list of columns to insert data into, and the VALUES keyword is followed by a list of values to insert.

Here is an example of the INSERT INTO statement:

INSERT INTO customers (name, email, country)
VALUES ('John Doe', '[email protected]', 'United States');

This statement inserts a new row into the “customers” table, with the values ‘John Doe’, ‘[email protected]’, and ‘United States’ for the “name”, “email”, and “country” columns, respectively.

SQL UPDATE Statement

The UPDATE statement is used in SQL to modify existing rows in a table. It allows you to specify the values to set for each column, and it is used to update data in the table.

Here is the basic syntax of the UPDATE statement:

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

The UPDATE statement is used to update rows in a table with the specified name. The SET keyword is followed by a list of columns to update and their new values and the WHERE clause specifies the conditions to use to filter the rows to update.

Here is an example of the UPDATE statement:

UPDATE customers
SET email = '[email protected]'
WHERE name = 'John Doe';

This statement updates the “email” column for all rows in the “customers” table where the “name” column is ‘John Doe’, and sets the value of the “email” column to ‘[email protected]’.