To communicate with databases, developers and data analysts make extensive use of SQL (Structured Query Language). Thanks to these various commands, it's easy to manipulate tables. So what are the main SQL queries and what are they used for? Find out the answers.
The "SELECT" SQL query
The SELECT command is undoubtedly the query most frequently used by developers and data experts. As its name suggests, it is used to select data.
To do this, the query takes the following form:
SELECT column name FROM table name.
You can also select multiple columns within the same table. In this case, you must separate the data with a comma:
SELECT column name 1,column name 2 FROM table name.
In addition to data selection, the SELECT query can also be used to remove duplicates, calculate sums, identify minimums and maximums, etc. For all these functions, SELECT can be combined with other commands, such as WHERE, GROUP BY, ORDER BY, HAVING, …
For example, if you wish to select a column according to a specific condition, you need only enter the following query:
SELECT column name FROM table WHERE condition.
The "INSERT INTO" query
This SQL query lets you add new data to a table. It looks like this:
INSERT INTO table name VALUES
Depending on your needs, INSERT INTO can include one or more rows. If you wish to insert a single row, you have two options:
- Specify information for each column: in this case, you must respect the order of the data.
- Specify only the desired columns: in this case, the order is not as important.
The "UPDATE" command
This command allows you to update tables by making modifications to existing rows. To do this, the SQL UPDATE query is used in conjunction with other commands:
SET: this specifies which column to replace and what to replace it with.
WHERE: this command identifies the rows to be modified and defines a condition. The columns affected by this condition will be modified. The others remain unchanged.
In concrete terms, SQL Update looks like this:
UPDATE table
SET column_1 = ‘new value 1’, column_2 = ‘new value 2’, column_3 = ‘new value 3’, etc.
WHERE condition
The "DELETE" SQL command
DELETE is used to delete data; either all the rows in a table, or just a few specific rows. In the latter case, the SQL query is combined with the WHERE command to identify the rows to be deleted.
The syntax is as follows
DELETE FROM `table`
WHERE condition
Good to know: we recommend that you back up your database before deleting any rows.
The "DROP TABLE" SQL query
This SQL query is used if you wish to delete an entire table from your database. In this case, you delete everything associated with the table, such as indexes, triggers, constraints and permissions. That’s why it’s vital to make an upstream backup. Otherwise, all your data will be lost. Moreover, if the table to be deleted is dependent on other tables, we recommend deleting them beforehand.
As DROP TABLE requires no additional conditions or commands, the syntax is very simple:
DROP TABLE table name.
Deleting a table in its entirety frees up memory and simplifies understanding of the data base.
These are just a few examples of the most common SQL queries. If you’d like to learn more, take a Datascientest training course.
Learn SQL queries with DataScientest
Whether for data analysis or application development, mastery of SQL queries is essential. To deepen your knowledge, join our training courses at Datascientest. In addition to mastering this computer language, you’ll discover all the tools you need to manipulate data.
