Main SQL Commands
SELECT
The most fundamental SQL skill is to SELECT
one or more columns. You simply list the columns you need after the SELECT
, or put an * to select all of the columns.
SELECT
is well covered in the first chapter of the DataCamp course Introduction to SQL.And the w3schools SELECT tutorial.
FROM
FROM
tells SQL which data to query and lets you JOIN
. There isn’t really a training course on how to use FROM
for one table. Just put the table name after the FROM
command. When you want to join tables the FROM
command gets more complex by adding joins. A popular way to explain the different kinds of SQL joins is with Venn diagrams like these from the w3schools SQL joins Tutorial.
Note some people reject the Venn diagram explanation for joins
Some reccomend you “Say NO to Venn Diagrams When Explaining Joins”. Instead, all joins begin with a CROSS JOIN
(where every row on the left table is combined with every row on the right table), then all other joins (e.g. INNER JOIN
) are a filtered version of that CROSS JOIN
. A cross join is also known as the Caretsian product of the two tables.
* Image from Essential SQL.
Though please note that cross joins are, “..potentially an expensive and dangerous operation since it can lead to a large data explosion. It is best used in scenarios where a normal join cannot be used and very selective predicates are being used in the WHERE clause to limit the number of produced rows” Microsoft Docs. In other words, if you do use a cross join, make sure it is filtered with a WHERE
clause.
Joins are covered in the Oracle Tutorial Joining Tables.
And chapters 1 and 2 of the Datacamp course Joining Data in SQL. While this course is for the Postrgres flavour of SQL, you will find the methods will work in most flavours (e.g. Oracle or Microsoft SQL Server).
WHERE
(optional)
WHERE
filters rows.
The Oracle tutorial Querying and Filtering Rows shows you how to use
WHERE
to filter rows.Also, see chapter 2 of the DataCamp course Introduction to SQL.
And the w3schools WHERE tutorial.
GROUP BY
(optional)
After you have used SELECT
to choose the columns you need FROM
one or more tables, then filtered rows with WHERE
, a common further optional command is to use GROUP BY
. This will aggregate (e.g. sum up) values in one or more columns, by the values within another column (or columns). For example, to sum sales values by the values in a date column.
The oracle Tutorial Aggregating Rows describes how to both aggregate rows and use
DISTINCT
to find unique values.In DataCamp, use chapter 3 ofIntroduction to SQL.
And the w3schools GROUP BY tutorial.
HAVING
(optional)
HAVING
is an optional command to filter aggregated data from a GROUP BY
command. Think of it as a version of WHERE
created only to filter the result of the GROUP BY
command.
- See the w3schools HAVING tutorial.
ORDER BY
(optional)
ORDER BY
sorts rows by one or more columns. Your data often needs to be sorted so that it is ready to present to a client when exported, for example.
Oracle order by methods are covered well in Sorting and Limiting Rows.
See the final chapter 4 of DataCamp’s Introduction to SQL.
And the w3schools ORDER BY tutorial.