A comprehensive discussion on SQL will not be covered here, as it is beyond the scope of this book. However, there are a few common SQL commands to create and manipulate entries in a database table that you can try on our current data. They are as follows:
- SELECT: This command is used to query data from one or multiple tables. By specifying attribute (row) names in this command, you can specify which attributes from the tables to be queried should be returned. You can also use * to indicate that all attributes should be returned.
- INSERT: This command adds new entries (rows) into a given database table. Within an INSERT command, you specify the value of each attribute the new entry should hold; otherwise, the default values (which are determined by the database table) will be used.
- UPDATE: This command is used to modify entries that already exist in a given database table. Using this command, we are able to assign specific attributes of any entry to new values. This command is typically used with a SET command for that assignment process (as we will see later).
- DELETE: Finally, this command deletes specific entries from a given table. Once a DELETE command is executed, there is no way to recover the deleted data.
When using these commands, we typically combine them with a WHERE clause, with which we can specify a condition to filter out the entries in a database table we want to manipulate. For example, with the following command we can go into the database table named student, look for the entries that have the location attribute holding "Indiana", and finally change the value of the age attribute to 18:
UPDATE student
SET age = 18
WHERE location = "Indiana";
Additionally, to execute SQL commands, we need a platform in which we can enter and run the commands. Normally, these platforms come preinstalled with any database management tool that you use for your databases. However, as we will see later on, PyCharm actually offers an editor for SQL as well as the option to execute them within a given PyCharm project.
Needless to say, this is simply a brief overview of what SQL is and what a number of SQL commands can help us achieve. To learn more about this tool on your own, I recommend tutorials from https://www.codecademy.com/learn/learn-sql or https://www.khanacademy.org/computing/computer-programming/sql.
Next, let's see how PyCharm supports the use of SQL.