Intro to SQL: A Beginner’s Guide
SQL is a powerful tool that is used amongst data analysts and scientists in the tech industry. Building a repertoire that includes both SQL and Python knowledge can really help give an aspiring data scientist a leg up in their job interviews, as well as on the job itself! I have begun my SQL learning journey and have decided to create this short blog post that outlines some of the basic queries that can be done in SQL.
- The “SELECT” statement:
SELECT * FROM table_name;
When you have a table in SQL, the most basic thing you can do is to select all the columns of that table. When you include an * after the “SELECT” command, this tells SQL to select everything from your table. This is a much more efficient way than typing out every single column name that you have. Pretty straightforward!
However, if you did want to select specific column names, the query would look like this:
SELECT col_1, col_2 FROM table_name;
Here, I am selecting only column 1 and 2 from the table to be shown in the output. All you need to do is list all the columns you would like to show, separated by a comma.
It is also possible to select only distinct values from a column. If I was only interested in distinct values from col_1 and col_2, I could include the operator DISTINCT with the select statement in order to modify the results. The updated query would look like this:
SELECT DISTINCT col_1, col_2 FROM table_name;
2. The “ORDER BY” statement:
Another basic SQL command is the “ORDER BY” command. This will sort your table by a certain value that you specify. For example, suppose col_2 is a numeric column that you want to sort your table by. You could do so with the following query:
SELECT col_1, col_2 FROM table_name ORDER BY col_2;
This query selects two columns, col_1 and col_2, from your table, and sorts the output by the values of col_2. It is important to note that the default is to sort the values in ascending order. However, if you would like to sort the values in descending order, the query could be updated to include this specification:
SELECT col_1, col_2 FROM table_name ORDER BY col_2 DESC;
3. Aggregate functions
SQL also has the ability to calculate several aggregate functions for numeric columns from the existing tables. The most common functions are MAX, MIN, SUM and COUNT. For example, if you wanted to select the sum or a certain column, the query would be as follows:
SELECT SUM(col_1) FROM table_name;
The output would then show the sum of col_1.
You could also look at the max of a certain column:
SELECT MAX(col_2) FROM table_name;
The output of this query would show the max value that is found in column 2.
4. The “WHERE” clause
Including a WHERE statement in your query allows the output to adhere to some condition that is specified. For example, suppose you wanted to select all columns in the tables where col_2 is greater than a value of 20. The query would look like this:
SELECT * FROM table_name WHERE col_2>20;
5. AND/OR Statements
The “AND” statement can be used with the WHERE clause to specify multiple conditions to be met. For example, if you wanted to select all columns where col_2 is greater than 20 and where col_1 is less than 5, you would need to use an “AND” statement. The query would be as follows:
SELECT * FROM table_name WHERE col_2>20 AND col_1<5;
The result would only show rows where both of these conditions are met. However, if you wanted to see results of rows where col_2 is greater than 20 OR rows where COL_1 is less than 5, you can use the “OR” operate instead. The query would look very similar to the one above; just replace “AND” with “OR” like this:
SELECT * FROM table_name WHERE col_2>20 OR col_1<5;
Conclusion
This is a very introductory guide to working with SQL. This post displayed some of the basic operations that can be done in SQL that are beneficial for a new learner to become familiar with as they begin to learn SQL queries. Future blog posts will include a step by step guide on how to combine multiple operators into one query, as well as more basic functions, such as how to use aggregate functions with the “GROUP BY” operate, how to join multiple tables in different ways, and more advanced queries such as the CASE statement. Thank you for reading!