Today’s Date in SQL

One use case that happens often in SQL is the need to retrieve today’s date. This could be you are doing an analysis based on recent data, or it could be you are building a script that needs to retrieve the date dynamically.

Unfortunately there is no standard SQL that allows you to retrieve today’s date across different database systems. So in this post we have gathered the SQL statement needed to retrieve today’s date for the most popular database systems:

Oracle

SELECT SYSDATE FROM DUAL;

SQL Server

SELECT GETDATE();

MySQL

SELECT CURDATE();

DB2

SELECT current date FROM sysibm.sysdummy1;

Hive

SELECT CURRENT_DATE;

SparkSQL

SELECT CURRENT_DATE or SELECT CURRENT_DATE();

BigQuery

SELECT CURRENT_DATE;

PostgreSQL

SELECT CURRENT_DATE;

SQLite

SELECT DATE();

Posted in SQL

Can You Have Multiple WHERE Clauses in SQL

Technically the answer is yes, it is possible to have multiple WHERE clauses in a SQL statement. For example, if you are using a subquery, you can certainly employ a WHERE clause within the subquery, and then again in the outer query. Another example is when you are combining two SQL statements with a UNION / UNION ALL / INTERSECT / MINUS. In this case, both SQL statements can have their own WHERE clause.

Continue reading
Posted in SQL

What does WHERE 1=1 mean in SQL

Sometimes when you look at other people’s SQL code, you’ll see 

WHERE 1=1

as part of the code. If you haven’t seen this before, you might be wondering why people do this. After all, 1=1 always evaluates to TRUE, so that apparently makes this line of code redundant. As it turns out, there are two reasons why this is done:

Reason 1: Testing

Continue reading
Posted in SQL

Install a database on Windows PC

You are inspired by the SQL tutorial and you decided you want to install a database on your Windows PC. So, you do a Google search to see how this is done. Then you realize that there is a large variety of relational databases you can install. Not only that, as you browse through the different pages, you find the installation instructions for all of them are complicated. First you need to figure out which version you should download and install, then you have to go through multiple pages to find the right instructions. Pretty soon you realized that installing a database on your PC feels like a major project in itself.

This blog shows a simpler way. Assuming your goal is simply to have a database so you can practice your SQL skills, my recommendation is to go with SQLite3 for its ease of installation, its ease of use, and its small storage requirements. One thing to note with SQLite3 is that it does not have all the SQL features (here is a short list of common SQL features not available in SQLite3). Having said that, the truly important features are included.

Continue reading

Prepare For a SQL Interview

For data-related roles such as data scientists or data analysts, one of the interviews will inevitably be on SQL. This makes sense because SQL is what you use to extract data from databases, and as such is considered as an important fundamental skill set for the role.

One question people generally ask is, “How do I prepare for a SQL interview?” Here we will provide the answer to that question. We’ll start of with the general format of a SQL interview, then go into how to prepare for the interview, and finally we provide a few tips on what to do during the actual interview.

General format

In a SQL interview, the first five minutes or so will be spent on doing introductions. For this part, you should have a short script ready (no more than two minutes) that goes into your background and why you are interested in the role.

Continue reading

Posted in SQL

Change Blog “Posted On” Date To “Last Updated On” Date in WordPress

Over the years you may have published a large number of blog posts.

If there are posts that are more than three years old, you may find that some of the content on those old posts is no longer valid and needs to be updated.

On WordPress, you can do this easily. All you need to do is to go into the “Edit Post” mode in WordPress, change your content, and hit “Update” to have the new content show up.

The problem with this approach is that WordPress still shows the original post date to the readers. This is usually not what we want because users may look at the old date and quickly assume that the information contained there is out of date, and that is exactly the opposite of what we want.

Continue reading

Composite Key In SQL

Composite key, or composite primary key, refers to cases where more than one column is used to specify the primary key of a table. In such cases, all foreign keys will also need to include all the columns in the composite key. Note that the columns that make up a composite key can be of different data types.

Below is the SQL syntax for specifying a composite key:

Continue reading

Migrating From HTTPS To HTTP

HTTPS to HTTP

Why Migrate From HTTPS To HTTP

While moving from HTTP to HTTPS is a growing trend, there may be a time when you want to convert HTTPS to HTTP. For us, the main driver was page load time. After we migrated to HTTPS, the average page load time went up substantially, thus negatively impacting user experience. We tried several methods to improve page load time, but found little success. As a result, we decided reverting to HTTP was the best way to proceed.

Do I Still Need an SSL Certificate After Migrating From HTTPS To HTTP?

Continue reading