SQL Starter Guide Part I

Getting data out of databases is a common activity of many in data roles. It's also great data skill to be familiar with for promotion-hunting in many industries. (Dare you to name an industry that doesn't use a database/data repository!?!).

Our community resources are stock full of SQL goodies, for the just-tell-me-the-basics to just-give-me-a-go-to-repo since searching the interweb machine, e.g., Google can have you watching lots of *unhelpful* videos, inundated with advertisements and looking at course-style exercises with no solutions. Check out these videos, articles, tutorials and such to dip your toe in SQL waters.

Excel to SQL: Why Make the Shift

This data resource shares why SQL is needed when you're an Excel uer. I know, I know, SQL is yucky to many. Some find it boring and frustrating. Well, indulge me as I share my tech happy-place. Many people giving the side-eye to SQL who are familiar with MS Excel. Excel can do A LOT especially with the slate of plug-ins. Check out this short MS Excel vs SQL article. There are three main parts of SQL: building databases, retrieving rows from database tables and maintaining databases

Select/From/Where Clauses

We're walking through SELECT statements. SELECT statements can have upto 6 parts: select, from, where, group by, having and order by. Each of them are keywords in database management systems like MySQL and Oracle.

Think of SQL SELECT statements as a way to shuffle data and present it to us in a format we can understand it. This week it's about first three main parts of an SQL query: the select, from and where clauses.

SELECT clause lists columns and/or expressions that are to be returned from the SQL query
FROM clause specifies which database table(s) the data exists
WHERE clause tells the computer which conditions need to hold for a database row should be returned in the SQL query result

So what does this all mean?

We can write statements that returns database rows according to the conditions you want. Consider the customer table with columns labeled: CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country. Ok, so here's the first brief example:

SELECT CustomerName, City

FROM Customers;

When this SQL query is run, we'll see a number of rows returned that contain the customer name and their corresponding city. This query isn't as useful in the real world -- it simply parrots back data that's in the database table.

Here's a bit more meaty example that you'll see frequently:

SELECT CustomerName, City

FROM Customers

WHERE Country='Mexico';

Now, the SQL query results will display only the customers (their names and city) who live in Mexico. SQL statements using select, from and where clauses can do a a lot to filter the data in databases, but these are the basics.

GroupBy/Having & Order By Clauses

It's all about the GROUP BY, HAVING and ORDER BY clauses. Ok, these three clauses extend the power of retrieving matching rows from a database table. First up, let's talk GROUP BY. You'll want to use a GROUP BY clause to group rows that have the same values, especially when summarizing, like so:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

This query counts the number of customers per country. That's really convenient to obtain some general information about what data exists in the database table. So any country that has 1 or more customers will be returned. For a large database table, you'd retrieve many query results with the count being 1. And that's not very useful.

What if you'd want to only know the countries with more than, say 100, customers? The SQL clause HAVING, always coupled with GROUP BY, gives us a way to retrieve the answer:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

Note that COUNT(CustomerID) appears in both the SELECT and HAVING clauses. This is necessary when writing HAVING clauses. Said a different way: If you have a condition on a SQL statement with a GROUP BY clause, you must use the HAVING clause and the column that appears in the HAVING clause must also be in the SELECT clause. To help cement the use of HAVING clauses, here's what doesn't work:

SELECT COUNT(CustomerID), Country
FROM Customers
HAVING COUNT(CustomerID) > 5;

The GROUP BY clause is missing so this query is considered malformed. You'll see an error if you try to run the above query. Also, the following SQL SELECT statement will throw at least 2 SQL error:

SELECT COUNT(CustomerID), Country

FROM Customers

WHERE COUNT(CustomerID) > 5

HAVING COUNT(CustomerID) > 5;

The first SQL error happens at the WHERE clause -- which don't accept summary functions, like COUNT( ), as part of its querying format and rules. The second SQL error occurs at the HAVING clause since it appears without its partner, the GROUP BY clause. It's good to know what doesn't work and hopefully it helps clarify what does work :-)

Last up, let's discuss the ORDER BY clause. It allows us to sort SQL query results in either ascending or descending order, like so:

SELECT CustomerName FROM Customers
ORDER BY Country;

This SQL query returns all customers, by name, in alphabetical order. Ascending order is the default for the ORDER BY clause. If you want descending order, you'll have to be explicit, like so:

SELECT CustomerName FROM Customers
ORDER BY Country DESC;

Well, that covers a brief introduction to GROUP BY, HAVING and ORDER BY clauses. More advanced queries can be created that use multiple database tables in the FROM clause, includes multiple filtering conditions in the WHERE clause, grouping by multiple columns in the GROUP BY and HAVING clauses and issues more than one sort in the ORDER BY clause. SQL query is similar to coding in that way. But take one step at a time :- )

SQL Joins

The deep work of SQL happens in joining database tables. The intent of the JOIN clause is to combine rows from two or more tables, based on a related column between them.

Here are the different types of the JOINs in SQL (go to https://www.w3schools.com/sql/sql_join.asp for more details):

(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

The figure above gives you the SQL statement templates for writing SQL joins. It's best to learn by doing so check out the w3schools website I've added above.

More Complex SQL Querying

You are encouraged to sign up for a Khan Academy account (it’s free!). Your progress can be tracked to earn challenge patches and badges. This instruction module show you how to perform more advanced SQL queries using AND/OR, IN, LIKE, HAVING, and more.

SQL basics: They''ll show you the basics of creating tables and selecting data in various different ways
More advanced SQL queries: Learn how to perform more advanced SQL queries using AND/OR, IN, LIKE, HAVING, and more.
Relational queries in SQL: Learn how to store related data in multiple tables and use joins to bring them together (inner joins, outer joins, and self joins).
Modifying databases with SQL: Learn how to update data, delete data, and change table schemas with SQL commands UPDATE, DELETE, ALTER, and DROP.
Further learning in SQL: Get ideas for how you can continue learning more about SQL and databases.

Learn SQL in 1 Hour - SQL Basics for Beginners

Learn what you need and leave the rest for someone else :-)