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

WebPageTest Error With HTTPS / HTTP2 Enabled Site

Symptoms

One situation I ran into when I migrated 1keydata.com to HTTPS with HTTP2 enabled was that I had an issue with the page load speed testing tools WebPageTest and GTMetrix. On both tools, https://www.1keydata.com/ failed to load, yet when I visit the site using a browser, the website loaded up fine. This is the first time I have seen a difference in behavior between these types of testing tools and a browser. Given the highly unusual nature of this difference, I decided to look into the issue further. Below is what the test result page on WebPageTest looked like:

WebPageTest Error Result Page

Continue reading

WordPress Permalink Stops Working

I recently migrated to a new Linux server with a different version of Apache, and for a while I could not get the WordPress permalinks to work. After a lot of trial and error, I figured out what the problem was, and I hope the information I share here will help someone in the future.

Below are the relevant system information:
OS: Ubuntu 14.04 LTS
Web Server: Apache 2.4.18

What was the initial symptom?

The home page of the blog loaded OK. However, when I clicked on any link to go into any post, category, or month, I got a 404 error.

For those of you who wants to know the answer right away without reading through my journey of getting there, go to the Fix section.

Things I tried

I tried a number of ways to resolve this issue. They were as follows:

Continue reading