A Beginner’s Guide to SQL
SQL is one of the core skills of a data engineer and data scientist. This mini-tutorial explains the four fundamental SQL functions: Create, Read, Update, and Delete using a fun example of movie quotes database.
Until now, we have been discussing only how to filter the data from the database. Returned rows will be sorted by the order in which they were entered (committed) into the database. To control the order in which the rows will appear, we can sort the output data by including the `ORDER BY` clause. The `ORDER BY` clause includes one or more column names that specify the sort order:
SELECT column_1, column_1, ... column_n
FROM table_name
WHERE column_name operator value
ORDER BY column_name;
To expand our previous Star Wars movie quotes example, now sorted by the year, we would write:
SELECT * FROM Movie_quotes
WHERE Q_MOVIE = ‘Star Wars’
ORDER BY Q_YEAR;
By default, the sort order for a column is ascending from lowest value to highest value. To change the sort order for a column to be descending, we can include the `DESC` keyword after the column name:
SELECT * FROM Movie_quotes
WHERE Q_MOVIE = ‘Star Wars’
ORDER BY Q_YEAR DESC;
The `ORDER BY` clause is not limited to a single column. You can include a comma-delimited list of columns to sort by. Returned rows will be sorted by the first specified column, and then subsequently by the next specified columns. It is also important to note that a sort column does not need to be included in the list of selected columns. We can write the query like this:
SELECT Q_TEXT, Q_CHARACTER, Q_MOVIE FROM Movie_quotes
WHERE Q_MOVIE = ‘Star Wars’
ORDER BY Q_YEAR DESC;Updating Data
After we insert the data initially, we are not restricted to only read that data. We can make changes to any data in any column in any row. The `UPDATE` statement is used to update or change records. The `UPDATE` statement syntax is as follows:
UPDATE table_name
SET column_name = new_value
WHERE column_name operator value;
When using the `UPDATE` statement, it is important to carefully construct a `WHERE` clause. The `WHERE` clause specifies which record or records should be updated. If we don’t use a `WHERE` clause when executing the `UPDATE` statement, we will update all the data in the specified column(s). Let’s look at movie quotes in our `Movie_quotes` table. We have all the quotes ending with punctuation, except the Terminator one. This is a perfect example of how to use `UPDATE` statement:
UPDATE Movie_quotes
SET Q_TEXT = ‘I’ll be back!’
WHERE Q_MOVIE = ‘The Terminator’;
Like explained before, if we omit the `WHERE` clause by accident or on purpose we will update all the quotes rows to say “I’ll be back!”. By selecting just the row with the movie “The Terminator”, we are updating only a column `Q_TEXT` in that one specified row.
Deleting Data
When databases are used a lot, sooner or later it becomes necessary to remove obsolete data from the database. We can delete just a few rows from tables, or we can delete whole tables altogether. The `DELETE` statement is used to delete rows in a table. The command’s syntax is as follows:
DELETE FROM table_name
WHERE column_name operator value;
Again, like with `UPDATE` statement, the `WHERE` clause specifies which record or records should be deleted. Without a specified `WHERE` clause, all the rows and records will be deleted:
DELETE FROM Movie_quotes;
Let’s assume we don’t like the movie Forrest Gump any more and want to delete quotes from that movie. To remove all quotes from that movie we would write the following SQL command:
DELETE FROM Movie_quotes
WHERE Q_MOVIE = ‘Forrest Gump’;
Finally, we had enough of those movies. We are not interested in the movie quotes anymore, and we changed our interest to music. We started collecting song lyrics. With all the existing SQL knowledge we learned so far, changing our database is very simple. First we need to clean up our database from the data that we are not interested in anymore. To delete a table with all its rows, we can use `DROP TABLE` statement. It is important to note that `DROP TABLE` statement is different from using a `DELETE` statement and deleting all of the records in the table. Deleting all of the records in the table leaves us the table itself and all the defined table structure; including column data type definition and other related database information to that table. Dropping the table removes the table, all the table definition, as well as all of its rows. The `DROP TABLE` statement syntax is as follows:
DROP TABLE table_name;
To delete our `Movie_quotes` table from the database, we would write:
DROP TABLE Movie_quotes;
Now our database is empty, and ready to accept new data. We can start all over with the CRUD process by creating a new table called `Song_Lyrics` and building a song lyrics database from our new favorite songs.
Conclusion
In this article we have taken a look at the four fundamental database functions covered by CRUD: how to create new data, read that data, update the data we want to change, and finally how to delete unwanted data. This included basic but most important SQL commands like: `CREATE TABLE`, `INSERT INTO`, `SELECT`, `UPDATE`, `DELETE`, and `DROP`. Those basic SQL commands allow lots of data manipulation, but it is important to know that every covered command has many more options and additional functionality not covered by this article. Altogether, the basics should give a novice SQL developer a head start when beginning work with databases and using a new language, SQL.
Related:
