LEFT JOIN: retains the table to the left of the join.Should SQL return the staff first_name and last_name and leave the address line blank or should it leave the address line blank?

Say for example from the above query there were staff records that don't have a correspondingĪddress record (missing an address_id record or the record isn't in the other table). Joins come in three main varieties, and basically differ on how they handle missing data. The most straight forward way of doing this is with WHERE statements that come after the FROM Statements of our queries Working with a SQL database it's not going to be long before you get a request for only a subset of a table rather than whole columns. You can get a comprehensive break down of different format masks here Filtering Query Results Most are intutive, %d/%m/%Y transforms a date to the format DD/MM/YYYY. They function like this:ĭATE_FORMAT(, ) the format mask is usually composed of % followed by some abbreviation of a time value.

So it's best practice to just bring them in with a CONCAT function.Ĭoncat is used to connect two items togetherĭATE_FORMAT alters how dates are ouputted in SQL. Typically we store monetary values in SQL databases as floating point numbers and don't keep '$' symbols in our database, as doing so would convert our numbers into strings and then we wouldn't be able to do numerical operations on them! But for situations like financial reporting those '$' are commonly requested. Before you do anything else, open MySQL workbench and examine the list of databases loaded on your computer:ĬONCAT( '$ ',amount) AS 'Payment Amount ',ĭATE_FORMAT(payment_date, '%d/%m/%Y ') AS 'Payment Date ' FROM sakila. If you’re on a PC and used MySQL Installer to install MySQL, you may already have the Sakila database loaded. MySQL Workbench comes with the Sakila database, which is a sample database purpose built to demonstrate how to use SQL. For the purposes of this demonstration we'll be working with MySQL.

While there are a variety of SQL dialects (MySQL, MS Access, etc) the variations are slight and most operations work in a nearly identical manner. The most straight forward and common way of interfacing with a SQL database is to use a program like MySQL Workbench which is available for mac and windows. SQL Databases are tabular in nature meaning that they are represented as collections of spreadsheets where each row represents one record with each column recording an attribute of that record. SQL which stands for Structured Query Language and a way to query a database, in a structured manner.

Sakila database demo