We've finally reached the SQL section of training and I thought it would be great to share a bit of an introduction to SQL and the most important things to know to get started.
What is SQL?
SQL stands for Structured Query Language and it's a universal language used to work with databases and retrieve data with queries. You can insert new data, update existing data and delete data.
Basic Terms
To write SQL, query statements are used to encode what you want to do with your data. These statements include:
The order in which these queries go:
- Select (which fields you want to choose)
- From (the table you are choosing from)
- Where (Filter tool)
- Group by (groups aggregations by dimensions)
- Having (allows you to filter by aggregations)
- Order by (sort tool)
Let's see how we can use this statements to write a whole query and access our data in the form that we want it.
Selecting Your Data Fields
SELECT * FROM Table_1 ;
If we want to view all columns within Table_1 then we can use the '*'. However, if we want to specify the columns we want, we can write out the names separated by commas instead.
SELECT column_1, column_2 FROM Table_1 ;
Something to note, adding a semi-colon will end your query, allowing you to start a new one. Try not to forget these as this could cause errors!
The last query you have written will run automatically but you can highlight previous queries to run them if you need to.
Filtering
To filter your query, you can use the WHERE statement.
SELECT * FROM Table_1 WHERE column_1 = '....' ;
Essentially what you're saying is Only show me the rows where column_1 = '....'.
Of course, you can also filter with other mathematical operators (<,>, etc)
Another thing to note is that SQL is particular about quotation marks. Use single quotes (' ') for string values (e.g., 'London'), and double quotes (" ") for column or table names only if they contain spaces or reserved words.
SELECT * FROM Table_1 WHERE column_1 > 100 ;
Another statement that can be used to filter your table is the LIMIT statement, which will limit the amount of rows that are pulled through in the query. This can be useful when working with huge tables to avoid long waiting times when checking your queries.
SELECT * FROM Table_1 LIMIT 100 ;
Aggregations
If you want to aggregate a field in your query, without grouping anything, then you can simply use aggregate functions (sum(), avg(), count(), etc)when selecting your columns.
SELECT sum(column_1) FROM Table_1 ;
This will simply give you the total sum of the values in column_1. But what if we want to see the sum of values broken down by different categories (e.g. sum of sales in each state). We can use the GROUP BY statement.
SELECT sum(column_1), column_2 FROM Table_1 GROUP BY column_2 ;
This will sum your values in column_1 but group by the dimensions in column_2. We can then use the HAVING statement if we want to filter based on this aggregation.
SELECT sum(column_1), column_2 FROM Table_1 GROUP BY column_2 HAVING sum(column_1) >5000 ;
This will only return the dimensions that have a total value of over 5000.
Sorting
Last but not least, we can sort our queries with the ORDER BY statement.
SELECT * FROM Table_1 ORDER BY column_1 desc ;
You can specify ascending/descending order by using asc/desc.
Next Steps
I hope this blog has given you a nice introduction to SQL, but of course, the only way to really get to grips with SQL is to put it into use! A great place to start is The SQL Murder Mystery walkthrough. It’s an engaging way to apply the fundamentals you've learned. Enjoy SQLing!