SQL

Articles tagged SQL

How to use DATEDIFF() to calculate date differences in MySQL

When working with customer data or upon time series data science projects, you’ll often find the need to calculate the difference between two dates in your MySQL queries. The MySQL...

How to use DATE_ADD() and DATE_SUB() to add and subtract from dates

When working with customer data in ecommerce it’s very common for data scientists to need to add and subtract from date values directly within MySQL queries. For example, you might...

How to use CASE for flow control in SQL statements

The SQL CASE statement is used for flow control, much like an if, then, else statement. If the statement finds a match on the chosen condition it will return the...

How to use DATE_FORMAT() to reformat dates in MySQL

Most SQL databases store dates in the datetime format. This is really useful because MySQL, and other SQL dialects, make it very easy to convert datetime objects to a wide...

How to use string functions in SQL statements

MySQL includes a large number of string functions and operators that you can use in SQL statements to both query data and reformat column values. In this simple example, I’ll...

How to use ORDER BY to sort an SQL result set

When you create an SQL SELECT statement, the data probably won’t be returned or sorted in the order you want, so the ORDER BY clause is used to control the...

How to use GROUP BY and HAVING in SQL statements

The SQL GROUP BY clause groups row-based data into aggregated data, reducing the number of rows in the dataset, and is commonly used to perform aggregate calculations.

How to use BETWEEN in SQL statements to return data between two values

In SQL, when you want to SELECT data that lies between two values, there are a number of different SQL operators you can use to return the correct data. However,...

How to use SELECT, FROM, WHERE, and AND in SQL statements

The SELECT statement is the most simple of all SQL queries and allows you to retrieve the precise data you want from one or more tables, or even databases. In...

How to import a MySQL database

SQL is one of the most widely used languages in data science so it’s important to know at least the basics required in order to fetch the data you’ll need...

How to backup a MySQL database using mysqldump, SSH and SCP

If you need to create a backup of a remote MySQL database, you can use the mysqldump command. The mysqldump application is known as a client utility and installed alongside...

How to unserialize serialized PHP arrays using Python

If you regularly work with ecommerce data, you’re likely to have encountered PHP serialized arrays or objects. Serialization is a process used to take a complex data structure, such as...

How to analyse Pandas dataframes using SQL with PandaSQL

If, like me, you’ve come from a background where you made heavy use of SQL, then getting to grips with filtering, subsetting, and selecting data in Pandas can be a...

How to use SQLite in Python

SQLite is a relational database management system (RDBMS) that is easy to access within Python and other languages. Unlike MySQL, PostgreSQL, and other databases, SQLite uses a serverless design, so...

How to query MySQL and other databases using Pandas

For years, I used to spend much of my time performing Exploratory Data Analysis directly in SQL. Over time, the queries I wrote became very complicated, and it was often...

How to import data into BigQuery using Pandas and MySQL

Google BigQuery is a “serverless” data warehouse platform stored in the Google Cloud Platform. The serverless approach means you don’t have to maintain a server yourself and Google looks after...

How to connect to MySQL via an SSH tunnel in Python

Many MySQL databases are configured to accept connections from other servers on the local network and will reject connections from remote machines. Ordinarily, you could work around this by creating...

How to set up a Docker container for your MySQL server

Like most people who work in ecommerce data science, I regularly need to access data stored in a database - usually MySQL or MariaDB, but sometimes also MSSQL. Although it...