As the amount of data being generated and stored by companies and organizations continue to grow, the ability to effectively manage and manipulate this data using databases has become increasingly important for developers. Among the plethora of programming languages, we have SQL. Also known as Structured Query Language, SQL is a programming language widely used for managing data stored in relational databases.
SQL commands enable developers to perform a wide range of tasks such as creating tables, inserting, modifying data, retrieving data, searching databases, and much more. In this guide, we will highlight the top basic SQL commands that every developer should be familiar with.
What is SQL?
For the unversed, the programming language SQL is primarily used to manage and manipulate data in relational databases. Relational databases are a type of database that organizes data into tables with rows and columns, like a spreadsheet. SQL is used to create, modify, and query these tables and the data stored in them.
With SQL commands, developers can create tables and other database objects, insert and update data, delete data, and retrieve data from the database using SELECT statements. Developers can also use SQL to create, modify and manage indexes, which are used to improve the performance of database queries.
The language is used by many popular relational database management systems such as MySQL, PostgreSQL, and Microsoft SQL Server. While the syntax of SQL commands may vary slightly between different database management systems, the basic concepts are consistent across most implementations.
Types of SQL Commands
There are several types of SQL commands that are commonly used in relational databases, each with a specific purpose and function. Some of the most used SQL commands include:
- Data Definition Language (DDL) commands: These commands are used to define the structure of a database, including tables, columns, and constraints. Examples of DDL commands include CREATE, ALTER, and DROP.
- Data Manipulation Language (DML) commands: These commands are used to manipulate data within a database. Examples of DML commands include SELECT, INSERT, UPDATE, and DELETE.
- Data Control Language (DCL) commands: These commands are used to control access to the database. Examples of DCL commands include GRANT and REVOKE.
- Transaction Control Language (TCL) commands: These commands are used to control transactions in the database. Examples of TCL commands include COMMIT and ROLLBACK.
Essential SQL commands
There are several essential SQL commands that you should know in order to work effectively with databases. Here are some of the most important SQL commands to learn:
CREATE
The CREATE statement is used to create a new table, view, or another database object. The basic syntax of a CREATE TABLE statement is as follows:
The statement starts with the keyword CREATE, followed by the type of object you want to create (in this case, TABLE), and the name of the new object you’re creating (in place of “table_name”). Then you specify the columns of the table and their data types.
For example, if you wanted to create a table called “customers” with columns for ID, first name, last name, and email address, the CREATE TABLE statement might look like this:
This statement would create a table called “customers” with columns for ID, first name, last name, and email address, with their respective data types specified. The ID column is also set as the primary key for the table.
SELECT
Used on one of multiple tables, the SELECT statement Is used to retrieve data. The basic syntax of a SELECT statement is as follows:
The SELECT statement starts with the keyword SELECT, followed by a list of the columns you want to retrieve. You then specify the table or tables from which you want to retrieve the data, using the FROM clause. You can also use the JOIN clause to combine data from two or more tables based on a related column.
You can use the WHERE clause to filter the results of a query based on one or more conditions. Programmers can also use GROUP BY to manage the results by one or multiple columns. The HAVING clause is used to filter the groups based on a condition while the ORDER BY clause can be used to sort the results by one or more columns.
INSERT
INSERT is used to add new data to a table in a database. The basic syntax of an INSERT statement is as follows:
INSERT is used to add data to a specific table and begins with the keywords INSERT INTO, followed by the name of the table where the data will be inserted. You then specify the names of the columns in which you want to insert the data, enclosed in parentheses. You then specify the values you want to insert, enclosed in parentheses, and separated by commas.
UPDATE
Another common SQL command is the UPDATE statement. It is used to modify existing data in a table in a database. The basic syntax of an UPDATE statement is as follows:
The UPDATE statement starts with the keyword UPDATE, followed by the name of the table you want to update. You then specify the new values for one or more columns using the SET clause and use the WHERE clause to specify which rows to update.
DELETE
Next up, we have another SQL command DELETE which is used to delete data from a table in a database. The basic syntax of a DELETE statement is as follows:
In the above-mentioned code snippet, the statement begins with the keyword DELETE FROM. Then, we add the table name from which data must be deleted. You then use the WHERE clause to specify which rows to delete.
ALTER
The ALTER command in SQL is used to modify an existing table, database, or other database objects. It can be used to add, modify, or delete columns, constraints, or indexes from a table, or to change the name or other properties of a table, database, or another object. Here is an example of using the ALTER command to add a new column to a table called “tablename1”:
In this example, the ALTER TABLE command is used to modify the “users” table. The ADD keyword is used to indicate that a new column is being added, and the column is called “email” and has a data type of VARCHAR with a maximum length of 50 characters.
DROP
The DROP command in SQL is used to delete a table, database, or other database objects. When a table, database, or other object is dropped, all the data and structure associated with it is permanently removed and cannot be recovered. So, it is important to be careful when using this command. Here is an example of using the DROP command to delete a table called ” tablename1″:
In this example, the DROP TABLE command is used to delete the ” tablename1″ table from the database. Once the table is dropped, all the data and structure associated with it are permanently removed and cannot be recovered. It is also possible to use the DROP command to delete a database, an index, a view, a trigger, a constraint, and a sequence using a similar syntax as above by replacing the table with the corresponding keyword.
TRUNCATE
The SQL TRUNCATE command is used to delete all the data from a table. Simultaneously, this command also resets the auto-incrementing counter. Since it is a DDL operation, it is much faster than DELETE and does not generate undo logs, and does not fire any triggers associated with the table. Here is an example of using the TRUNCATE command to delete all data from a table called “customers”:
In this example, the TRUNCATE TABLE command is used to delete all data from the “customers” table. Once the command is executed, the table will be empty, and the auto-incrementing counter will be reset. It is important to note that the TRUNCATE statement is not a substitute for the DELETE statement, TRUNCATE can only be used on tables and not on views or other database objects.
INDEX
The SQL INDEX command is used to create or drop indexes on one or more columns of a table. An index is a data structure that improves the speed of data retrieval operations on a table at the cost of slower data modification operations. Here is an example of using the CREATE INDEX command to create a new index on a table called ” tablename1″ on the column “first_name”:
In this example, the CREATE INDEX command is used to create a new index called “idx_first_name” on the column “first_name” of the ” tablename1″ table. This index will improve the performance of queries that filter, or sort data based on the “first_name” column.
JOIN
Finally, we have a JOIN command that is primarily used to combine rows from two or more tables based on a related column between them. It allows you to query data from multiple tables as if they were a single table. It is used for retrieving data that is spread across multiple tables, or for creating more complex reports and analyses.
INNER JOIN – By implementing INNER JOIN, the database only returns/displays the rows that have matching values in both tables. For example,
LEFT JOIN – LEFT JOIN command returns all rows from the left table. It also returns possible matching rows from the right table. If there is no match, NULL values will be returned for the right table’s columns. For example,
RIGHT JOIN – In the RIGHT JOIN, the database returns all rows from the right table and possible matching rows from the left table. In case there is no match, NULL values will be returned for the left table’s columns.
FULL OUTER JOIN – This type of JOIN returns all rows from both tables and any matching rows from both tables. If there is no match, NULL values will be returned for the non-matching columns.
CROSS JOIN – This type of JOIN returns the Cartesian product of both tables, meaning it returns all combinations of rows from both tables. This can be useful for creating a matrix of data but can be slow and resource-intensive with large tables.
Furthermore, it is also possible to use JOINs with subqueries and add ON or USING clauses to specify the columns that one wants to join.
Bottom line
In conclusion, SQL is a powerful tool for managing and retrieving data in a relational database. The commands covered in this blog, SELECT, INSERT, UPDATE, and DELETE, are some of the most used in SQL commands and provide the foundation for performing a wide range of operations on a database. Understanding these commands is essential for anyone working with SQL commands and relational databases.
With practice and experience, you will become more proficient in using these commands and be able to create more complex queries to meet your specific needs.
