How to Run SQL Queries in Database [13 SQL Query Examples]

Do you want to learn about SQL query commands? Many organizations throughout the world use MySQL and PostgreSQL relational database management systems with the standard use of SQL query language. More specifically, if you want to operate with databases effectively, you need to get to know about SQL queries.

The important SQL queries or commands related to creating databases and tables, inserting data or rows, selecting and updating data, and removing rows, tables, and databases, will be covered in this article.

So, keep reading to discover more about SQL query commands and how they can facilitate effective data management and manipulation!

1. Create Database SQL Query

In SQL, the CREATE DATABASE query is used for creating or adding a new database. The newly created database can then be utilized for storing data in the form of tables, indexes, views, and other objects.

CREATE DATABASE database_name;

According to the given syntax:-

• CREATE DATABASE is the keyword that indicates that it is required to create or add a new database.
• database_name is the new database name. Note that, the specified database name must be unique and should not already exist in the database server.

To create a new database named database1, we will now run the following command.

CREATE DATABASE database1;

As a result, a new database will be created successfully.

2. Show Database SQL Query

The SHOW DATABASE query is used for showing or displaying all of the already existing databases on the server.

For the corresponding purpose, write out the following command in the terminal.

SHOW DATABASES;

It can be observed that the given command printed the list of existing databases, which also comprises the database1.

3. Use Database SQL Query

Want to switch to a different database? SQL offers the USE DATABASE command for switching or connecting to the specified database. After doing so, you will be permitted to execute the required queries in that database context.

USE database_name;

In the given syntax:

• The USE keyword signifies that we need to switch between databases.
• The database_name represents the database name for the connection.

Let’s connect to the newly created database1 in MySQL with the help of the USE query.

USE database1;

The displayed message indicates that the database has been changed successfully.

4. Alter Database SQL Query

To alter or modify an existing database, the ALTER DATABASE command be utilized. Moreover, it allows you to update the database name or its relevant collation or character set.

Here is the syntax you should follow for altering the database.

ALTER DATABASE database_name [DEFAULT] CHARACTER SET charset_name [DEFAULT] COLLATE collation_name

In the above command.

• ALTER DATABASE is the required command that we need to execute.
• database_name refers to the database name that is required to alter.
• Both CHARACTER SET and COLLATE are the optional parameters. By utilizing them, you can alter the character set or collation of the mentioned database.

The given code will alter the database names database1 and modify its character set as utf8 and the relevant collation to utf8_general_ci.

ALTER DATABASE database1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

5. Create Table SQL Query

In a database, the CREATE TABLE can be utilized for creating or defining a new table. It also enables you to specify the table structure comprising the required columns and their data types.

CREATE TABLE table_name (
  col1 datatype [optional_parameters], 
  col2 datatype [optional_parameters], 
  ...[table_constraints]
);

Here:

• CREATE TABLE command is utilized for a new table.
• table_name refers to the table name that is required to create.
• col1, clo2…, etc, represent the tables columns.
• [optional_parameters] signifies the use of additional column attributes, such as AUTO_INCREMENT, DEFAULT, or NOT NULL, etc.
• [table_constraints] represents the table constraints, such as UNIQUE, FOREIGN KEY, or PRIMARY KEY, etc.

Create a table named authors comprising three columns, id, name, and email.

CREATE TABLE authors (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255));

According to the given command, the data type of the id column has been defined as an integer, and its length will be auto-incremented.

Moreover, it is specified as the PRIMARY key of the specified table. Other than this, there are two columns name and email, each representing the variable-length character data types with a maximum length of 255.

6. Show Tables SQL Query

The SHOW TABLES query in MYSQL can be utilized when you need to list out the tables of a certain database.

SHOW TABLES;

The given command will fetch the table names of the default or current database.

SHOW TABLES [FROM database_name] [LIKE 'pattern'];

Here:

• FROM clause is used with the database_name to refer to the particular database. In case, if it is not defined, the default or current database.
• LIKE clause with pattern is an optional parameter for filtering the tables list based on specified requirements.

In the ongoing scenario, the SHOW TABLES query will display the tables of the database1 with which we are connected.

SHOW TABLES;

7. Insert Into SQL Query

The INSERT INTO command is used for inserting or adding single or multiple rows to a table in MySQL.

Here is the syntax you need to follow for inserting into a MySQL table.

INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);

According to the given syntax.

• INSERT INTO is the MySQL command.
• table_name refers to the table where it is required to insert records or data.
• (col1, col2, ...) are the column names where the data is going to be inserted.
• VALUES (val1, val2, …) indicates the values that will be inserted.
• Note that you have to specify the values in the same sequence in which the column names have been defined.

Example 1 – Inserting a Single Record Into a Table

In this example, we will insert a single record including the name Ravi and the email as ravi@tecmint.com into the authors table.

INSERT INTO authors (name, email) VALUES ('Ravi', 'ravi@tecmint.com');

Example 2 – Inserting Multiple Records Into a Table

For the purpose of inserting multiple records into the authors table, we will specify the names and emails and separate both values set by comma, as follows.

INSERT INTO authors (name, email) VALUES ('Kennedy', 'kennedy@tecmint.com'), ('Sharqa', 'sharqa@tecmint.com');

8. SELECT SQL Query

To retrieve or fetch the records from single or multiple tables in a MySQL database, utilize the SELECT query.

To do so, check out the given syntax.

SELECT col1, col2, ... FROM table_name WHERE condition;

Here:

• SELECT is the specified MySQL query.
• col1, col2, …, are the column names whose records are going to be fetched.
• table_name refers to the required table name.
• WHERE clause with the specified condition is an optional parameter that can be added if you need to filter the record.

Example 1 – Selecting All Records From a Table

As the first example of a SELECT query, we will fetch all of the records from the authors table.

SELECT * FROM authors;

It can be observed that all of the records from the authors table have been retrieved successfully.

Example 2 – Selecting a Specific Record From a Table

In the second example, we will only retrieve the record WHERE the name value equals Sharqa.

SELECT * FROM authors WHERE name = 'Sharqa';

As you can see, the SELECT query displayed the id, name, and email accordingly.

9. Update SQL Query

To modify or alter the existing data of a table in MySQL, the UPDATE query can be used. It can be utilized in scenarios where it is required to modify the accidentally added record.

For updating records in MySQL, follow the provided syntax.

UPDATE table_name SET col1 = val1, col2 = val2, ... WHERE condition;

Here:

• UPDATE query is added for updating records.
• table_name refers to the table name.
• col1 = val1, col2 = val2, are the column names with the respective new values.
• WHERE clause is added with the specific condition to update the rows that meet the given criteria.

Let’s modify or update the email id of the author named Sharqa with the one that is mentioned below.

UPDATE authors SET email = 'sharqahameed@tecmint.com' WHERE name = 'Sharqa';

The output indicates that the update operation has been done successfully.

10. Delete SQL Query

While managing the database and the relevant tables, you may need to omit or delete unnecessary records or data that is no longer required. In such a scenario, execute the DELETE query. This query can be utilized for deleting single or multiple rows or records from a selected table.

To DELETE records, specify the table_name and define the condition.

DELETE FROM table_name WHERE condition;

As a result, the given DELETE query removes the arrows that fulfill the condition specified with the WHERE clause. In case, the WHERE clause is not added, all of the table rows will get deleted.

In the example, we will utilize the DELETE query for deleting the record from the authors table where the name equals Kennedy.

DELETE FROM authors WHERE name = 'Kennedy';

12. Alter Table SQL Query

In order to modify, update, or alter the structure of an existing table in MySQL database, utilize the ALTER TABLE query. This query can be utilized in scenarios where you tend to make a scalable database management system.

To utilize the ALTER TABLE command, follow the stated syntax.

ALTER TABLE table_name action;

In the above-code block :-

• ALTER TABLE command is defined for modifying or altering the table.
• table_name refers to the table that needs to be modified.
• action specifies the action or alteration that is required to perform. For instance, it can be adding or dropping columns, modifying or renaming columns, etc.

In this example, we will alter the authors table and add a new column named age with the integer data type.

ALTER TABLE authors ADD age INT;

12. Drop Table SQL Query

To drop or delete an existing table of a database along with its data, run the DROP TABLE query. This query is required to execute when you want to remove a whole table all at once.

Here the DROP TABLE command will delete the specified table_name.

DROP TABLE table_name;

Now, let’s drop the authors table from our current database.

DROP TABLE authors;

13. Drop Database SQL Query

The DROP DATABASE query in MySQL can be utilized for dropping or deleting a database along with the table and the relevant table.

Here, specify  database_name which you need to drop or delete.

DROP DATABASE database_name;

Lastly, let’s drop our current database database1 which we created for the demonstration purpose,

DROP DATABASE database1;

To List out the databases for the verification run the following command.

SHOW DATABASES;

It can be observed that database1 is no longer in the list which means it has been dropped successfully.

In this post, we have covered the 13 essential SQL commands that every MySQL or PostgreSQL user should know. However, to take your learning to new heights, check out other dedicated tutorials as well.

Conclusion

As you have seen in this blog, SQL query commands offer a robust toolset for managing your database and the relevant data. From creating and altering tables to inserting, modifying, and dropping records, tables, or databases, these commands offer a reliable and flexible way to handle various data-related tasks in a database server.