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();

The post Today’s Date in SQL appeared first on Technology Tips.

However, notice in each of those cases there are also multiple SELECT statements as well, and each WHERE is associated with a single SELECT. In fact, in a SELECT statement, there can only be a single WHERE clause. This also applies to DELETE and UPDATE statements as well.

Usually when people ask this question, though, the concern is not really whether it’s possible to have multiple WHERE clauses in a SQL statement, but whether it’s possible to specify multiple conditions in a SQL statement. The answer to this question is yes. You would do this using a single WHERE statement. Assuming there are two conditions and you want both of them to be true, you can use AND to connect the two conditions. If you want either one of them to be true, you can use OR.

While it is possible to use a subquery with multiple WHERE clauses to specify multiple conditions,

SELECT * FROM
(SELECT * FROM SALES_TABLE
WHERE REGION = 'EAST')
WHERE YEAR = 2021
-- This query has two WHERE clauses

this is not an efficient query to use. A better way to achieve the same result is as follows:

SELECT * FROM SALES_TABLE
WHERE REGION = 'EAST' and YEAR = 2021;

The post Can You Have Multiple WHERE Clauses in SQL appeared first on Technology Tips.

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

The first reason is to make it easy to test SQL statements. When a developer is working on building a SQL statement, often there is a lot of trial and error that happens. By having the WHERE 1=1 clause, this makes the testing of the different WHERE conditions easier. 

WHERE 1=1
--and gender='F'
and product_id=135209
--and credit_card='Visa'

By having each condition on a different line, it’s easy to comment out different conditions using two dashes to explore the various possibilities.

Reason 2: Programmatic 

The second use case for using WHERE 1=1 is when you want to general SQL in a programmatic fashion. For example, you might have a selection box for users to select what category of product they want to view. In SQL, this will be implemented as part of the WHERE clause. If the code already has 

WHERE 1=1

then the program will only need to add an additional “AND [new condition]” clause after this, and not have to worry about putting in logic to determine whether this is the first condition that needs to be added (i.e., need to add the WHERE clause) or there is already a WHERE clause and you simply need to add an AND clause. 

We’ve talked about two reasons, and we will also mention one supposed reason that actually isn’t true. Some people believe that WHERE 1=1 will speed up the performance of the SQL query. This is not the case, as modern SQL optimizers simply ignore this clause.

Personally I do not put in WHERE 1=1 in my SQL because to me this requires an extra effort and accomplishes no real goal. On the other hand we all have our programming habits, so there are occasions when I see a code that contains WHERE 1=1. When I get this, I simply leave it there for the same reason (there is no real benefit in taking the time to change it). 

So next time you see WHERE 1=1, you understand why people use it. Whether you decide to adopt this pattern is entirely up to you. 

The post What does WHERE 1=1 mean in SQL appeared first on Technology Tips.

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.

There are two ways to install SQLite3: The first is to install download and install directly from SQLite’s official website, the second is to install via the Anaconda package. Either method works, and we’ll provide instructions for doing both below.

Download from SQLite’s official website

Step 1: Go to SQLite’s official download page at https://sqlite.org/download.html.

Step 2: Click on the file that starts with sqlite-tools-win32 to download it to your machine (highlighted in red below).

Step 3: Create a new directory on your drive. If you want the files to reside in C:\sqlite3, navigate to C:\ using Command Prompt, then type in “mkdir sqlite3”.

Step 4: Double click on the downloaded ZIP file, then extract all the files into the C:\sqlite3 directory (or the directory you specified). There are three files, and all three of them combined require about 3 MB of space.

Step 5: Now the installation is complete. To run SQLite3, open Command Prompt, navigate to C:\sqlite3, and enter “sqlite3” to get into the user interface.

Use the Anaconda distribution

Step 1: SQLite3 is part of the Anaconda distribution. Anaconda bills itself as the “data science toolkit” and is primarily a distribution of Python and R. At the same time, it includes SQLite3 as a lightweight database. Go to Anaconda site and download the Anaconda distribution.

Step 2: Double click on the Anaconda download to start the installation.

Step 3: Follow the prompts. You can safely select the default value when prompted.

Step 4: Once installation is completed, you can start using SQLite3. To get to SQLite3, start with the Windows prompt at the lower left hand side of your screen, go to Anaconda3 (64-bit), then Anaconda Prompt (see image below).

Step 5: A window that looks similar to Command Prompt will show up. Type in “sqlite3”, and you are inside the database. Now you can practice your SQL.

As an alternative, you can also use the Command Prompt. However in this case you’ll need to navigate to the exact path where the SQLite.exe file is located. On my machine this is “C:\Users\C\Anaconda3\envs\new\Library\bin”. Since this is more troublesome to get to compared to just simply using the Anaconda Prompt, I recommend using the Anaconda Prompt.

Note that when you just type in “sqlite3” (after either installation method), you’ll get a message saying “Connected to a transient in-memory database.” This means whatever you store in the database will disappear when you exit. To connect to a physical database, type in the database name after “sqlite3.” For example, if your database name is “flower,” you’ll want to type “sqlite3 flower.db” and you’ll be connected to the flower database.

The post Install a database on Windows PC appeared first on Technology Tips.

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.

After that, the interviewer will dive into the questions. You should expect to answer multiple questions. This can be in the form of a single large question with multiple parts (i.e., the first part is easy, followed by parts with increasing difficulty), or it could be several questions that are unrelated to one another.

Often questions are related to the industry the company is in. For example, if you are interviewing with a social networking company, expect the questions to be related to social networking.

Usually the interview will be conducted over an online coding tool such as Coderpad or CodeSignal. Once you receive the question, you will then code your SQL in the tool. The interviewers will be logged into the same tool and can see in real time what you are typing in. Sometimes you can run the code to see if you get the right answer, but more often the interviewer will just be looking at your code to determine its quality. It is important to note that in the vast majority of the cases, the interviewer does not care about what flavor of SQL you use.

How to prepare

You’ll want to brush up on the following:

• Aggregate functions and group by
• Join (when to use outer join)
• Union: A good number of questions will require you to answer in parts and then combine the results at the end. This is where UNION becomes handy.
• Window functions: Inevitably you’ll run into a problem that requires you use Window functions to solve.
• How the company works. This is useful because the SQL questions are usually related to the company itself. Understand how the company works can allow you to understand the question faster.

Get plenty of rest the night prior to the interview, and schedule your day of the interview so you are refresh for the interview. While this is true for all interviews, it is especially so for a SQL (or any other coding) interview because you are expected to be able to think on your feet, and a fresh mind gives you the best chance to be successful doing that.

There are also areas that you do NOT need to prepare for. Note that I have never seen the following topics:

• String functions
• Date functions
• Anything related to NULL
• Any of the “SQL background” type of questions such as “What is RDBMS?” or “What is SQL”?

I suspect that you don’t see the first three types because they are often database-dependent, and interviewers are usually interested in your general SQL skills, not in how good you are with a particular flavor of SQL.

For the “SQL background” type questions, you’ll often see those questions listed in the top results when you Google “SQL interview questions”, but in reality you never get them — interviewers are interested in how you can use SQL to solve problems, not whether you know SQL stands for Structured Query Language.

Interview Tips

You are prepared for the interview. Below are six tips for you to pass your interview:

1. Ask for clarifications. If there is anything in the interview question you are unsure of, make sure you ask the interviewer for clarification as opposed to make assumptions yourself. This is especially important if the question is domain-specific and you are not familiar with the domain.

2. Break down the question. When you get a question, break it down, and write your code based on how you are breaking things down. I recommend that you use WITH clauses to componentize your code. While it is possible to use subqueries for this purpose, I find using the with clauses to be easier for the interviewee to proceed and for the interviewer to follow.

3. Look at the instructions carefully. The reason is you don’t want to miss out on any filtering conditions. Did the question ask for only a particular time period? Did the question ask for top 10 overall or top 10 by country? These are details that the interviewer will pay attention to.

4. Talk out loud. Let the interviewer know your thought process. This makes it easy for the interviewer to follow your train of thought. And in case you are stuck or is going down the wrong path, the interviewer can guide you in the right direction.

5. Perfection is not necessary. If you have an idea on how to do something but at the same time you know it’s not the best way to proceed (and you don’t know what that “better way” is right at that moment), you should nevertheless go with the answer you know. Once you have something that works, the interviewer may ask (or you might want to mention yourself) that there could be a better way. The extra time you’ve bought for yourself may allow you to come up with the more elegant answer.

6. Ask for help. If you are really stuck, don’t be afraid to ask for help. Nothing is more awkward than a long silence. In most cases, the fact that you asked for help doesn’t mean that you have failed the interview. In fact, some questions are designed so that the interviewer expects the interviewee to struggle. As long as you can proceed to the right answer after the interviewer provides a hint, you should still be okay.

Summary

A technical SQL interview can be daunting, even for people who have used SQL for a while. By following the tips in this post on how to prepare and how to answer questions, you should be able to pass your SQL interview in flying colors.

The post Prepare For a SQL Interview appeared first on Technology Tips.

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.

What we want to do is to update the publish date to the most recent date it is published, and change the WordPress code so that it shows the “Last Updated On” date instead of the “Posted On” date. This way, not only will users see that the post is recent, but it will also bring the blog post up to the top of your home page so it gets more attention from your readers.

There are two things you need to do:
1. Change the “Published On” field on your Edit screen. You need to do this for each post you update.
2. Modify the PHP code of the theme you are using so that it shows “Last Updated On” instead of “Published On.” You’ll only need to do this once.

Change the “Publish On” field on your Edit screen

This is a fairly easy thing to do. On the upper right-hand corner in the “Edit Post” mode, you’ll find the publish timestamp in a drop down box (see figure below).

Click on the “Edit” link (highlighted in red), and you will see five drop-down boxes that allow you to change the year, month, date, hour, and minute of the post date.

Make the appropriate changes and click the “OK” button. Now the Post Date of the blog entry will be changed to what you just specified.

Modify the PHP code

Changing the PHP code? This may sound terrifying if you are not a PHP programmer, but you should not worry because you will not be altering any functionality. What you’ll be changing is simply the text output of your PHP code.

You can modify the PHP code directly in the WordPress Dashboard. The steps are as follows:

1. Login to your WordPress Dashboard.
2. Click on “Appearance” on the left navigation.
3. Click on “Editor” in the sub-menu that shows up under “Appearance.”
4. On the right, click on “template-tags.php.”
5. Change “Posted On” to “Last Updated On” in the text box (see image below).
6. Click “Update File” to save the change.

Now your post will have a “Last Updated” date instead of “Posted” date.

Please note that the location of this code may be in different places depending on the particular theme your blog uses. If your theme does not have the “template-tags.php” file or if that file does not contain what you are looking for, try looking for it in other files.

If you have shell access to your hosting environment, you can edit the relevant file directly.

The post Change Blog “Posted On” Date To “Last Updated On” Date in WordPress appeared first on Technology Tips.

Below is the SQL syntax for specifying a composite key:

CREATE TABLE TABLE_NAME
(COLUMN_1 DATA_TYPE_1,
COLUMN_2 DATA_TYPE_2,
…
PRIMARY KEY (COLUMN_1, COLUMN_2, …));

Some database-specific examples are shown below. In all cases the composite key created consists of COL1 and COL2.

MySQL

CREATE TABLE SAMPLE_TABLE
(COL1 integer,
COL2 varchar(30),
COL3 varchar(50),
PRIMARY KEY (COL1, COL2));

Oracle

CREATE TABLE SAMPLE_TABLE
(COL1 integer,
COL2 varchar(30),
COL3 varchar(50),
PRIMARY KEY (COL1, COL2));

SQL Server

CREATE TABLE SAMPLE_TABLE
(COL1 integer,
COL2 nvarchar(30),
COL3 nvarchar(50),
PRIMARY KEY (COL1, COL2));

Composite Key Example

An example is when we want to record the history of student class registration and grade history. For this, we want to record which student took what class during what quarter, along with the date of registration, date of drop (if the class was dropped), and the final grade. Our table will look like the following:

Table Class_History

In this table, the first three fields, Quarter_ID, Student_ID, Course_ID, together form a composite key. Each of them individually cannot uniquely identify each record, but together the combination of all three does uniquely identify each record.

The post Composite Key In SQL appeared first on Technology Tips.

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?

It is important to note that even after you migrate from HTTPS to HTTP, you’ll still need a valid SSL certificate. This is because since the initial request will come via HTTPS, you’ll still need to have a valid certificate. Otherwise, your visitors will see a “your connection is not secure” type of error, which is enough to turn most of them away before they even see anything on your site.

How To Migrate From HTTPS To HTTP

The amount of work required to convert HTTPS to HTTP is actually fairly small. The steps are below. In this post, the commands we use are for Apache 2.4.18 / Ubuntu. If you use a different web server / OS combination, please make sure you use the appropriate command.

1. Set up a redirect to point your HTTPS pages to HTTP pages. To do this, modify the Apache2 config file for your domain under /etc/apache2/sites-available/. Add the following line under <Virtualhost *.443>:

Remember to replace your-site-name.com with your own domain name.

2. Remove the HTTP to HTTPS redirect you may have set up when you went to HTTPS. This will not apply if you set up your site initially with HTTPS. In our existing configuration, that means we needed to remove the redirect under <Virtualhost *:80>. This will look something like the following:

You should remove that line.

3. In Apache, change the Virtualhost directive for your HTTPS port. To do this, change

<Virtualhost *:443>

into

<Virtualhost your-site-name.com:443>

If you don’t do this step, your visitors will see an infinite redirect loop and your page never loads.

4. Restart the web server. In our configuration, this is done by the following command:

Depending on your OS and Web Server, the command to restart the web server may be different.

Once you have completed the above steps, you have successfully migrated from HTTPS to HTTP. The caveat is that the client browser must not already have a cached version of you page. If your page is already cached, your visitors will see an infinite redirect loop. There are two ways to resolve this:

a. The visitor has to manually clear the browser cache. In fact, before you can test your own changes, You’ll need to manually clear the browser cache. If you do not, you’ll see an infinite redirect loop even if your setting changes were correct.

b. Use a different URL for your pages. This approach will ensure that the HTTP version loads. This approach has two disadvantages: First, the amount of effort to set up the appropriate redirects may be large. Second, changing URL’s may impact your site’s SEO traffic negatively. I do not recommend this approach if you have a large number of pages or already have a significant amount of traffic from search engines.

I looked into ways to force browsers to clear cache from the server side, but found that this is not possible, even when I modified the Header information. This means that, after you complete your HTTPS to HTTP migration, there will be a subset of your visitors who will have difficulties accessing your site, and that number can be substantial if you have a high percentage of return visitors. You’ll then need to weigh the relative impact of losing repeat traffic and the benefit of changing to HTTP before proceeding.

5. Update all your internal and external links. Technically the migration of your site from HTTPS to HTTP is complete if you executed steps 1-4 above, as all HTTPS calls will be redirected to their HTTP counterpart. However, you should still convert all the internal links that are HTTPS (don’t forget to update your non-relative links and your canonical URL) and all external links that are HTTPS (for example, update all your social profile links). That way, you preserve your web server resources by saving an unnecessary call.

The post Migrating From HTTPS To HTTP appeared first on Technology Tips.

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:

On the other hand, when I switch the user agent on WebPageTest to IE, the page test loaded fine. Also, it’s worth noting that the site itself renders fine in a browser. The only thing that appears strange was that sometimes at the beginning of a page load, I would see a screen showing up temporarily before the actual page loads on Chrome. In all cases, though, the web page proceeded to load correctly, so initially I didn’t think much of this issue. During the course of my investigation, though, I decided to look into this as it may be related to the issue I was seeing. Using a screen video capture software, I was able to capture what that temporary page looks like:

The error message that showed up was ERR_SPDY_INADEQUATE_TRANSPORT_SECURITY.

Synopsis

It turned out that the issue was with the SSL Cipher Suite that was set up on the server. Below is part of the screenshot from the SSLTest result for https://www.1keydata.com/:

Notice the “Server negotiated HTTP/2 with blacklisted suite” message. The specific SSL Cipher Suite that was causing the issue was also listed: “TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA.”

Resolution

What’s happening here is the Chrome (and Firefox) is hitting a blacklisted SSL Cipher Suite when it is trying to connect via HTTP2. This only happens if HTTP2 is configured for the website. The resolution is to set up the Apache server in a way so that an acceptable SSL Cipher Suite that is not on the blacklist is called by Chrome/Firefox *before* the blacklisted SSL Cipher Suite. As it turned out, the order you use when you list the SSLCipherSuite items is important.

The fix for Apache 2.4.18 on Ubuntu 14.06 LTS is as follows:

Open your site configuration file

 
and insert “ECDHE-RSA-AES128-GCM-SHA256:” right in front of your SSLCipherSuite directive:

 
After this, restart your Apache server:

 
And now everything should be okay. Tests run on both WebPageTest and GTMetrix should complete without any problems. The temporary page with the ERR_SPDY_INADEQUATE_TRANSPORT_SECURITY error message should no longer exist.

Reference: http://sparanoid.com/note/http2-and-ecdsa-cipher-suites/

The post WebPageTest Error With HTTPS / HTTP2 Enabled Site appeared first on Technology Tips.

Google Chrome: 65.29% (+1.14%)
Firefox: 14.66% (-0.59%)
IE: 12.76% (-0.50%)
Safari: 3.42% (-0.05%)
Edge: 1.05% (+0.06%)

Google Chrome continued its strength in March, gaining 1.14% to 65.29%. The gain came at the expense of Firefox, IE, and Safari, all of which saw its market share decline in March. If you think you have read this statement before, you are correct, because the same thing happened in February as well. Microsoft Edge finally broke 1% in March.

In March, Microsoft has finally started giving Edge the extension functionality–a functionality that likely has prompted most Windows 10 users to go to Chrome and Firefox. The number of extension is quite limited at this time, though it should grow over time. My impression is that, “it’s best late than never.” If Microsoft can manage to keep half of its new Windows 10 users on Edge because of the extension functionality, it will do a lot better than it is today, when only 12.51% of its Windows 10 users are using a Microsoft browser.

The post March 2016 Browser Market Share appeared first on Technology Tips.