SQL

Last updated on Jul 17, 2023

SQL (pronounced ‘see-qwel’) is the currently most famous database language. As I need to learn it deeply in my current job, here are my side notes:

Learning

If you ever need to do a slighty complex query, I recommend you to watch this video first. Doug does a great job at going step by step to design a complex query.

SQL flavours

Because SQL is a really (and I mean REALLY) old language, it has developed different flavours. The most popular are:

MySQL
PostgreSQL. Documentation
Oracle
SQL Server, aka Microsoft SQL, aka T-SQL. More about T-SQL, Syntax convention.

Source: this Medium post

Format and Lint SQL

Because of the different SQL dialects, there isn’t much consensus on how to format queries. Even in different companies tried to make a standard, but only for their own dialect, so it’s useless once you move to another SQL flavour.

I hate to format my code manually, one of the first things I lookup is a formatter and a linter (if it even exist).

So far, the best I have found browsing on the web is SQLFluff which is a python package to format and lint many SQL problems in general.

It also has a webpage to format simple queries. http://online.sqlfluff.com

If you are looking for an alternative sqlfmt is a good customizable webapp.

source: SQLFluff - The linter for modern SQL

Tips and tricks

Is there a way to do `SELECT BOTTOM 5`?

Yes and no.

By definition, there is no strict order in rows. This means that unless you have a key ID, you can’t assume the rows are ordered in some way. Because of this issue, this opens 2 ways of solving the problem:

If you can order the rows, (for example, by creation/modification date, or by ID if you have one) you can do it with ORDER BY column_name DESC:

SELECT TOP 10 FROM dbo.my_table ORDER BY column_name DESC

But, if you have a table that can not be ordered, yo need to do this:

DECLARE
    @bottom int,
    @count int

SET @bottom = 1000
SET @count = (SELECT COUNT(*) FROM Employee)

SELECT * FROM
    Employee AS emp WHERE emp.EmployeeID NOT IN
(
    SELECT TOP (@count - @bottom) Employee.EmployeeID FROM Employee
)

source: This clever answer of stackoverflow

Don’t waste time with SQL Server

SQL Server Management Studio is the preferred choice for T-SQL and Azure Databases. Nontheless, you can run Azure Data Studio if you have SQL Management Studio installed. Azure Data Studio is WAY better in terms of interface. If you are familiar with VS Code, it is a really close user experience.

If you want a comparison, here is one done by Microsoft docs.

SQL