SQL concepts

SQL databases are made up of tables. A table is something like a CSV or spreadsheet file, in that it has rows representing individual items and columns representing data values associated with each item. A SQL table has some important differences from a spreadsheet, though:

• First, each column in the table is assigned a data type, which is strictly enforced. Just as Python will produce an error when you try to convert "abcd" to an int or 0.03 into a date, a SQL database will return an error if you try to insert letters into a numeric column or decimal values into a date column. SQL databases typically support basic data types like text, numbers, dates and times, Boolean values, and binary data; in addition, some implementations have specialized data types for things like IP addresses, JSON data, currency, or images.
• SQL tables can also have constraints, which further enforce the validity of data inserted into the table. For example, a column can be given a unique constraint, which prevents two rows from having the same value in that column, or a not null constraint, which means that every row must have a value.

SQL databases commonly contain many tables, and these can be joined together to represent much more complicated data structures. By breaking data into multiple linked tables, we can store it in a way that is much more efficient and resilient than a two-dimensional plaintext CSV file.

Syntax differences from Python

If you've only ever programmed in Python, SQL may feel odd at first, as the rules and syntax are very different. We'll be going over the individual commands and keywords, but here are some general differences from Python:

• SQL is (mostly) case-insensitive: Although it's conventional for readability purposes to type the SQL keywords in all caps, most SQL implementations are not case-sensitive. There are a few small exceptions here and there, but, for the most part, you can type SQL in whatever case is easiest for you.
• Whitespace is not significant: In Python, new lines and indentation can change the meaning of a piece of code. In SQL, whitespace is not significant and statements are terminated with a semicolon. Indents and new lines in a query are only there for readability.
• SQL is declarative: Python could be described as an imperative programming language: we tell Python what we want it to do by telling it how to do it. SQL is more of a declarative language: we describe what we want done, and the SQL engine figures out how to do it.

We'll encounter additional syntax differences as we look at specific SQL code examples.