SQL BASICS
Structured Query Language (SQL) is one of the essential languages used by data scientists. It is designed to manage and query data in relational databases like MySQL and PostgreSQL. It’s also one of the skills most often seen in data-related job requirements. So, I figured covering the most basic methods used in SQL will give you an understanding of the foundations of a relational database and get you started on your data science journey. We’ll start with creating a simple table and use different methods to manipulate and query data.
These are the 7 main statements that we’ll cover to get started with SQL:
CREATE TABLE INSERT INTOUPDATEDELETESELECTORDER BY
CREATE TABLE and INSERT INTO
One of the very first steps when producing a database is to create a table. A table in SQL is a collection of rows and columns. Tables are used to store information to sort different types of data, from credit card transactions to a shop’s inventory. We’ll create a vintage clothing store and use the table to see what they’re selling:
CREATE TABLE vintage_store (id INTEGER PRIMARY KEY, name TEXT, color TEXT, price INTEGER, rarity INTEGER);We use the CREATE TABLE statement to create our table with the name vintage_store and inside the parenthesis, we initialize our column names followed by their column types. The two main types of columns are going to be INTEGER and TEXT. Notice that id has a type of INTEGER PRIMARY KEY. This will be used to index each item that will be put inside the table so that we know they are unique.
Now that we’ve created our table, we can start inserting our items. Let’s start with 10 items:
INSERT INTO vintage_store VALUES (1, 'Denim Jacket', 'Indigo', 400, 5);
INSERT INTO vintage_store VALUES (2, 'Denim Pants', 'Indigo', 350, 5);
INSERT INTO vintage_store VALUES (3, 'Black Pants', 'Black', 200, 4);
INSERT INTO vintage_store VALUES (4, 'Denim Vest', 'Indigo', 150, 4);
INSERT INTO vintage_store VALUES (5, 'Jumper', 'Black', 70, 1);
INSERT INTO vintage_store VALUES (6, 'Jumper', 'Indigo', 70, 1);
INSERT INTO vintage_store VALUES (7, 'Jumper', 'Gray', 70, 2);
INSERT INTO vintage_store VALUES (8, 'Chore Jacket', 'Indigo', 150, 3);
INSERT INTO vintage_store VALUES (9, 'Chore Jacket', 'Black', 150, 3);
INSERT INTO vintage_store VALUES (10, 'Blazer', 'Indigo', 200, 3);SQL syntax follows similar rules to how English is spoken, so understanding what each statement does is fairly simple. The INSERT INTO statement is followed by the table name and then VALUES, which we’ll specify inside the parenthesis. The values inside must match the same types we defined in our table. For example, the unique id number, what the name of the item is, it’s color, the price, and it’s rarity. Then, we can simply repeat the process for the other items. We now have a complete table with items in it!
Something important to note is that a semi-colon (;) is used at the end of each statement and that a TEXT type must be used with single quotes (‘’).
UPDATE and DELETE
Now that we have a complete table, we can begin manipulating it. We can start with UPDATE, which does exactly that and update any of our item’s values from our given table. Let’s say that our vintage denim pants are not exactly selling due to it’s high price. We decide to lower it.
UPDATE vintage_store SET price=300 WHERE id=2;Following the same logic as previous statements, we update our vintage store and set the price of our item with id #2 (denim pants). There are two new keywords used here associated with UPDATE. SET is used to set the new value for our price and WHERE is used to locate our item. This is why it’s important to have the unique id number so that we can easily identify which item we want to update.
After updating the price of our denim pants, someone decided to buy it. Reducing the price was a good idea after all! We can now use DELETE to take it off our table.
DELETE FROM vintage_store WHERE id=2;The DELETE statement removes the item completely from your table, so be cautious when doing this. Always remember to add WHERE to this statement because if not specified, you may accidentally delete the entire table.
SELECT
It’s easy to lose track of what’s happening in our table, especially if it’s a lot of data. We also wouldn’t want to scroll endlessly through our database if we’re looking for something specific. The SELECT statement helps us query our data and see only the data we need at the moment. On the other hand, if we wanted to see the entire content of a table, we also use SELECT.
SELECT * FROM vintage_store;The asterisk (*) followed by SELECT queries all of the content from vintage_store.
Notice that the denim pants, item #2, are gone since we deleted them. We can also limit our query results using WHERE followed by a conditional statement (=, <, >). For example, if we wanted to just look at our items that have a rarity over 3, we can do the following:
SELECT * FROM vintage_store WHERE rarity > 3;
ORDER BY
In addition to querying our data, we can also view our table in a sorted manner. Perhaps, we wanted to view the store’s inventory sorted by price from lowest to highest. We can use the statement ORDER BY to accomplish this task:
SELECT * FROM vintage_store ORDER BY price;
We can also use a combination of statements to be more specific in our query. If we wanted to get all of the items with a rarity over 3 and have it sorted by price, we can do the following:
SELECT * FROM vintage_store WHERE rarity > 3 ORDER BY price;
CONCLUSION
There are tons of other ways you can query and manipulate data but these are the basic foundations of SQL. With the 7 statements that we’ve learned, we were able to create a table, insert items, and manipulate them just as a real vintage shop would by using SQL to do it. The best way to master querying is the same way to master anything, by simply practicing. I added resources below so that you can read and practice what we’ve learned here and continue with other advanced methods.
Additional Resources:
I found these two sites to be the most helpful with practicing SQL!
