DEV Community

Cover image for Performing SQL Commands
Nathalia Friederichs
Nathalia Friederichs

Posted on

Performing SQL Commands

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

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

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

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

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

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

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

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

Syntax

Image description

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

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

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

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

Syntax

Image description

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

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

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

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

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

Deleting Data from a Table

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

Syntax

Image description

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

Syntax

Image description

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.

Top comments (0)