Performing SQL Commands
Tags: database, sql, programming, learning
Published: 2023-10-03 22:22:12

In this article, we will explore some of the key SQL commands that serve as essential building blocks for querying, updating, and managing information.

**Creating a Database**

To create a database, we use the "CREATE" command.

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yz24ebhvl9iznp45k0bd.png)

After creating the database, or if it already exists, we use the "USE" command to select the database we want to use.

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1gi5wn390qsuax6vvhcp.png)

**Creating a Table**

To create a table in SQL, you use the "CREATE TABLE" command. This command allows you to define the table's structure, specifying column names, data types for those columns, and other properties.

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g0mucsuu3k5flue5or53.png)

In the command below, a table is being created where the "ID" field is defined as a primary key and therefore cannot contain null values. However, the other fields are allowed to contain null values.

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nd81yd7pgo3ysbqf554n.png)

If you want to create a table with more than one column as a primary key, you can do so as follows:

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2u739ru4iv394zmfllbf.png)

**Altering a Table**

If the table already exists, you can make changes to it using the "ALTER" command.

To add a column to the table, we need to define the table to which we want to add the new column, the column name, and the data type.

The process of adding columns to tables allows for the expansion and adaptation of the table's structure to accommodate new types of information or requirements.

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ddacs1ei6cftw0vy1ctg.png)

To remove a column from a table, you simply need to choose the table from which you want to remove the column and specify which column you want to delete. This allows you to eliminate a specific column from the table's structure, adjusting its structure and content as needed.

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4y2vghshj6mjcd1wrgnz.png)

To change the name of a column in SQL, you use the "ALTER TABLE" command along with "RENAME COLUMN".

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/971g0d2x56kvpkki7ig3.png)

To change the data type of a column, the syntax is very similar to the syntax for adding a column to the table. You just need to replace "ADD" with "ALTER COLUMN". See below!

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/45tiab4nav520435m9ew.png)

If you have created the table, but forgot to create the primary key, don't worry, you can alter the table structure and create one. This primary key can consist of a single column or be composed of multiple columns. To indicate multiple columns, simply list them within parentheses, separating them with commas.

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1yy8ua4c4qnpfgaq3o9p.png)

**Inserting Data into a Table**

After creating the table, we will insert data into it using the "INSERT" command.

To insert a single record:

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x7dduq8gr13yiqx4zxjm.png)

It is also possible to insert multiple records at the same time:

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6yuejonifpg3b2omumd8.png)

**Selecting Data from a Table**

After the data has been added to the table, we can retrieve it using the "SELECT" command.

If you want to retrieve all records and all columns from the table:

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7kg4edoi7mlpbmm8wzr9.png)

To select specific columns in an SQL query, you use the "SELECT" command followed by the column names you want to retrieve. This allows you to narrow the query's output to the information that is relevant to you.

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yd57j14grvmz8zmcudbh.png)

If it's necessary to filter a record in the table, you can use the "SELECT" command with the "WHERE" clause. In the example below, only records with an "ID" equal to 1 will be returned.

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qtnzvv1nwc80wusns5fa.png)

**Updating Data in a Table**

If you already have a table with information and want to modify only a specific record, you can perform this update using the "UPDATE" command with the "WHERE" clause. This allows you to modify the data of a specific record within the table while keeping the other records unchanged.

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j3zgmnnu7p5ky35h6bys.png)

Additionally, you can modify the values of multiple columns simultaneously. In the example below, we are changing the values of the columns "AGE" and "NAME".

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4pr0y4oip5xty13odcj4.png)

**Deleting Data from a Table**

To delete a specific record from the table, we use the following command:

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o9laywc7isdwl8q436ax.png)

You might want to delete all data from a table. For this, you can simply use the command:

_Syntax_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uhjrdlp3oeosptwtwsli.png)

Be very cautious with this command, as it is irreversible and will result in the complete loss of data in the table. Therefore, it is extremely important to be sure that you want to delete all records before executing this command, and it is highly recommended to back up the data before performing a large-scale deletion.

To prevent the accidental deletion of all records, many Database Management Systems (DBMSs) require you to include a "WHERE" clause as part of the "DELETE" command unless you have special privileges to perform such a critical operation.

Remember that the exact syntax may vary slightly depending on the Database Management System (DBMS) you are using, but the fundamental concepts of using these commands remain the same.