Update: Testing Your DIY Amazon Alexa / Echo Device (Raspberry Pi)

Hey there folks, happy weekend! Hope this post finds you well. This is a follow up to my previous post on how to make your own Amazon Alexa / Echo device from a Raspberry Pi.

First, an Apology for the Cliff Hanger

I left you hanging at the end of that post, and I’m very sorry about that. It was a Friday morning at almost 4 a.m., and I was ready for bed. So, here we are on the weekend! I’ve now had the chance to do some further validation and documentation in testing our DIY Amazon Alexa / Echo from a Raspberry Pi.

The samples I’ve provided aren’t super in depth, but serve as proof of concept.

Recap: Where We Left Off

Here’s where we were at the end of the last article:

The first terminal will set Alexa up to be listening on port 3000 (remember the local host URLs from earlier?) The second window deals with setting up a Java client and logging into Amazon with the Security profile we set up. Logging in and confirming will enable you to initiate the connection to Alexa, paving the way for the third terminal, which enables the wake word detection and actual running of the Alexa service. Woohoo! I’ll update with examples later.

I’ve booted up our trusty Raspberry Pi back up, and opened 3 command line terminals.

Preparing for Test

1. Companion Service

First item of business, is to run the companion service, first command line terminal. Type the below:

cd Desktop/alexa-avs-sample-app/samples/companion service && npm start

After returning the above line of code, successful output should end with, “Listening on Port 3000” and “Successfully retrieved registration code for xxxxxxx / xxxxxxx ”

Below, steps 1 & 2 combined since I was a bit slow on thinking to photograph in the moment. 🙂

2017-02-26-001-Running-Alexa-AVS-Companion-Service

2. Java Client / Authorizing Device

The second piece for testing here is the Java client and authorizing the device with Amazon. (Remember the security profile setup from the previous How to Build Article?) In the second command line terminal, type the following:

cd Desktop/alexa-avs-sample-app/samples/javaclient && mvn exec:exec

A few things should happen here, outside of Matrix-like code waterfalls. First, a window prompting you to login to Amazon to enable the security profile for your device should appear. Second, after you click through to the browser (or paste URL into browser), you should see a log-in screen like the below. Enter your credentials, approve access, and close the window after you see a screen that displays the message, “device tokens ready”

2017-02-26-002-Verify-Raspberry-Pi-Alexa-Voice-Service

After you’ve completed the above steps, you should have a window (slightly hidden in the center, photo above) with a bearer token and a button to listen. At this point, I just ran a simple test saying, “hello” and Alexa said, “hello” back. We’re almost there!

3. Wake Word Detection / Connecting to AVS Client

The final piece is connecting to the AVS client and enabling wake word detection, which means we don’t have to press the “Listen” button every time we want to do something. Last piece of command line!

cd Desktop/alexa-avs-sample-app/samples/wakeWordAgent/src && ./wakeWordAgent -e kitt_ai

After the script runs, the last line of code output should read, “Connected to AVS Client”. You’re now ready to use Alexa.

2017-02-26-004-AVS-Client-Connected-Wake-Word-Detection-On

Testing!

Below is one of the quick tests I ran on Pi Alexa. Si

mple time check and request for a joke. Now we’re cooking with gas!

 

Wrap Up

Building an Alexa has been a very instructive process. I don’t know about you, but I’m ready for actual skill development now. Stay tuned as I recap the changes through my first Alexa skill, a remix of the Space Geek sample, and begin working through more advanced skills and concepts such as analytics integration. Cheers!

DSC_0005 Cover Photo by Zach Doty for Build Your Own Amazon Alexa x Echo from Raspberry Pi

How to Build Your Own Amazon Alexa with a Raspberry Pi

Howdy, it’s been a minute since my last post on Alexa Skill Development. I left us hanging, as I mentioned in one of my previous SQL posts, work craziness, sickness, and general life stuff happening imposed a brief, involuntary hiatus on writing.

Enough self talk to make future self feel better. Let’s build our own Amazon Alexa with a Raspberry Pi! What a step up from our previous articles. 🙂

But first! A couple of notes: This is NOT necessary for you to do in learning Alexa Skill Development. You can use a regular Echo instead of this expedition into Computer Homebrew 102. Also, I developed my first Alexa skill without an Echo or comparable device at all. One more thing, this is a long post that is both deep and wide ranging. Don’t say I didn’t warn you.

1. The Materials List

To get started, you’ll need a few things:

  1. Raspberry Pi, version 2 or later
  2. Power source
  3. Micro SD card (Recommended at least 8 gb in storage size)
  4. Speaker with a line-in 3.5mm cable (or headphones of similar line-in spec)
  5. USB microphone
  6. Keyboard and Mouse
  7. HDMI cable to connect to a monitor or TV
    1. Obviously, you need a monitor or TV. 🙂

 

A few notes on the Raspberry Pi: I recommend you get a pre-assembled kit, for time and convenience, if nothing else. I previously purchased from CanaKit on Amazon, and have been pleased with it. If, for some reason, you are building your own or need to reformat, the two articles below from RaspberryPi.org are simple, fast instructions to get you up to speed.

 

Formatting a MicroSD card 32 GB or less:

https://www.raspberrypi.org/documentation/installation/noobs.md

Downloading New Out of Box Software (NOOBS) for Raspberry Pi, in preparation of your first boot.

https://www.raspberrypi.org/downloads/noobs/

 

Okay, we’re going to proceed assuming that you’ve taken care of the above, and have successfully booted your Pi, connected all requisite hardware, connected to the internet, and so on. Below is where I’m at.

2017-02-20-001-Alexa-Raspberry-Pi-Successful-Boot

2. Installing Other Utilities

Another note here, it is possible to SSH (read: a bit like Microsoft’s RDP remote desktop application, but command line style) your way into the Raspberry Pi. I will not be doing that. This may sound a bit silly, but I don’t have a multi-screen dock for my laptop (capped at 2), and I’m happy for the Pi and other monitor to be a standalone computer. (Perks of a wide desk.) Also, the Wi-Fi USB card I’m using is very slow at the moment. Also, an edit after I’ve written this post offline: Linux-esque command line work is a royal pain in the you-know-what. Remarkable computing has advanced to where it is today. More on Raspberry Pi intricacies later.

Enough of my discourse, let’s continue setting up our Raspberry Pi / DIY Alexa to-be. Task time.

Navigate to your Pi’s command line module.

  1. We’re going to update the GET. Type:
    1. sudo apt-get update (Pro tip: don’t misspell sudo as I’ve laughingly done on the first try.)
    2. In the new line created by default, type: clear and hit enter. This will clear your command line.

2017-02-20-003-Alexa-Raspberry-Pi-Get-Update

3. Registering Your Raspberry Pi as an Alexa Voice Service Device Type

Alright, head to the Amazon Developer Portal and log in with your credentials.. (If you’ve followed along with previous posts, you have a login! If not, see this article on setting up an AWS account.)

C-2017-02-23-001-Alexa-Raspberry-Pi-Registration

Once you’ve logged in, click on the Alexa navigation item, then click “Get Started”. You should land on a page that has a “Register Product Type” drop-down. Select Device and continue.

C-2017-02-23-002-Alexa-Raspberry-Pi-Registration

You’ll be taken to a new screen / process to create a new device type. Choose and type your  device type ID and display name. For ease of process here, I’ve used the same value for device type ID and display name.

C-2017-02-23-003-Alexa-Raspberry-Pi-Registration

Once you’ve selected your ID and name of choice, click next. Now we need to create a new security profile. Click the dropdown menu that reads, “Select Security Profile” and click, “Create New Profile”. If you’ve filled out all the fields properly, you should have the option to submit this device / product. Hooray! Below is what I see after I submit the new device.

C-2017-02-23-004-Alexa-Raspberry-Pi-Registration

Create a Security Profile Name and Description of your choosing and click “Next”.

C-2017-02-23-005-Alexa-Raspberry-Pi-Registration

You’ll now see a new screen with your created security profile. Below, you should see some tabs such as, “General”, “Web Settings”, “Android/Kindle Settings”, and so forth. Click on “Web Settings” and then click “Edit”.

C-2017-02-23-006-Alexa-Raspberry-Pi-Registration

You should now be able to edit the “Allowed Origins” and “Allowed Return URLs” fields. Enter the following values into each of the fields, respectively:

1) https://localhost:3000

2) https://localhost:3000authresponse

Once you’ve entered the above values, click “Next”.

C-2017-02-23-007-Alexa-Raspberry-Pi-Registration

Now we need to add some additional detail about our device type.

  • Upload an image (I used this one, they’re picky about sizing)
  • Choose a category (I selected “Other”)
  • Provide a short description to your pleasing
  • Select “No” for plans on making product available to general public
  • Select “No” for product directed to children

C-2017-02-23-008-Alexa-Raspberry-Pi-Registration

Click “Next” to proceed. The next screen asks about applying for access to Amazon Music. I’m selecting “No” for the purposes of this exercise.

4. Enable Login With Amazon for your new Device / Product

Sweet! After you click, “Submit”, you should see the below. Now head over to the “Apps & Services” section, and select the “Login with Amazon” item, once the Apps & Services screen has loaded.

C-2017-02-23-009-Alexa-Raspberry-Pi-Registration

From the drop-down menu of existing security profiles, select the profile we just created. You’ll need to enter a consent privacy notice URL and a consent logo image. Because we’re doing anything public facing, you can enter any URL you’d like here. For the image, I used the same image from the device creation process. Click “Save” and let’s keep moving.

C-2017-02-23-011-Alexa-Raspberry-Pi-Registration

Upon a successful save, you should see a table with a column labeled “Oauth2 Credentials”. Click, “Show Client ID and Client Secret”, copy and save the information somewhere safe. Next stop, Raspberry Pi.

5. Install the Application on Your Raspberry Pi

Flip back over to your Raspberry Pi and type the following two bullet point texts as unique lines into command line.

  • cd Desktop
  • git clone https://github.com/alexa/alexa-avs-sample-app.git

It should take a minute or two to process, and you should see the below.

C-2017-02-23-012-Alexa-Raspberry-Pi-Sample-App-Setup

Next, type the below point texts as unique lines into command line:

  • cd alexa-avs-sample-app/
  • nano automated_install.sh

After returning the two above lines, you should see something like the below. Remember what we entered in the Developer Console a few steps ago? You’ll need this information here. (Highlighted boxes.) Update from 2/25/2017: I actually had quite a bit of trouble with this step. Linux command line / text emulator is not my friend yet. The trouble I had here was making and saving my changes to the below. However, the program is surprisingly helpful! It will prompt you to verify credentials before install proceeds.

C-2017-02-23-014-Alexa-Raspberry-Pi-Sample-App-Setup

After you’ve made the appropriate changes, command line language of CTRL + X should help you save the changes. Close out the command line window and re-open. Type the following bullet texts:

  • cd Desktop
  • cd alexa-avs-sample-app
  • . automated_install.sh

When prompted for the AVS + Raspberry Pi License and Agreement, obviously answer and/or “y” to proceed, and answer yes and appropriate and desired throughout the setup process. E.g., you’ll be asked about language preference, audio input settings, enabling wake word detection, and so forth. (It takes anywhere from 30 min to 1 hour.)

Once the install runs, it’s time to start the Alexa service!

6. Running the Alexa Service / Finishing Touches

To test and run the Alexa service, we need 3 command line terminals open. The command paths for these are at the end of the install. (As I type this, the latest install on my Pi is approaching 45 minutes, so I’ll have to give you the bare bones for now based on past tests and some generous documentation.

The first terminal will set Alexa up to be listening on port 3000 (remember the local host URLs from earlier?) The second window deals with setting up a Java client and logging into Amazon with the Security profile we set up. Logging in and confirming will enable you to initiate the connection to Alexa, paving the way for the third terminal, which enables the wake word detection and actual running of the Alexa service. Woohoo! I’ll update with examples later.

Update — 2/25/2017:

So, the whole process took a looooong time. At about 3 am of the night I wrote / finished this post, the Pi finally finished unpacking and installing everything. Below is our success confirmation! I’ll be testing this evening…

Wrap-Up

Goodness, what a ride, if you stuck it through the whole article. You could do this, or just get a darn Echo. After all this, I recommend you buy an Echo. 🙂

Other miscellaneous updates from 2/25/2107: By the time I got to the wrap up, it was 3:30 in the morning, yikes! I wasn’t feeling very descriptive, nor was I yesterday either. This was an incredibly long process that took several days from start to finish. Was it worth it? Yes. I’m still going to test the capabilities tonight and hopefully have some follow up’s in the coming days. Will I be primarily using an Echo moving forward? Yes.

The BETWEEN Statement: SQL Statement Fundamentals

Hey there, folks! Welcome back to our journey in learning SQL. Our last few of posts covered the ORDER BY clause, LIMIT statement and the COUNT function. (See full list of SQL tutorials here.)

Today, we’re going to cover the BETWEEN statement. This is the start of some deeper material. In addition to the BETWEEN statement, we’ll also soon be covering IN and LIKE statements.

 

About the BETWEEN Statement

The BETWEEN statement (rather, operator) is used to match a value against a specified range of values. Maybe we want to get transactions between a certain dollar amount.

For example, value BETWEEN low AND high;

More about the BETWEEN statement. If the value is greater than or equal to the low value and  less than or equal to the high value, the expression returns true, or vice versa. Also, the BETWEEN operator can be rewritten by using the greater than or equal to ( >=) or less than or equal to ( <=) operators as the following statement.

value >= low and value <= high;

One other way to think about the BETWEEN statement is that takes two WHERE statements and lumps them into one. Think back to the WHERE statement, as we would say first:

SELECT column1 FROM table WHERE column1 >= 2 AND column1 <= 7;

 

To BETWEEN or NOT BETWEEN

Conversely, you can extend the usage of the original BETWEEN operator to NOT BETWEEN. This is similar in concept, except working toward exclusion, instead of inclusion. So, if we want to check if a given value is outside of a range, we can use the NOT BETWEEN operator as below.

value NOT BETWEEN low AND high;

Again, similarly to the double WHERE statements, NOT BETWEEN simplifies the burden into a single statement. For sanity, we’ll skip the SELECT WHERE example.

 

Using BETWEEN in pgAdmin / PostgreSQL

Below, we’ve taken the BETWEEN statement for a road test on our address table.

SELECT address_id,address FROM address

WHERE address_id BETWEEN 10 AND 20;

2017-02-18-001-BETWEEN-SQL-Statement-Example

How sweet was that? Syonara, comparison operators. Conversely, if we convert the above query into a NOT BETWEEN operator, we should see the below statement return the following results.

SELECT address_id,address FROM address

WHERE address_id NOT BETWEEN 10 AND 20;

2017-02-18-002-NOT-BETWEEN-SQL-Statement-Example

Okay, that was cool. Let’s hold up for a quick second. What about non-integer columns, such as data? Caveat: there’s a lot more to data types than our humble example below. For the purposes of this article, we can take a YYYY-MM-DD date and place the values into strings.

SELECT rental_id,rental_date,inventory_id FROM rental

WHERE rental_date BETWEEN ‘2005-05-24’ AND ‘2005-05-27’;

2017-02-18-003-BETWEEN-Non-Integer-SQL-Statement-Example

Voila!

Wrap-Up

Alright, for those of you reading this later, it’s 2 a.m. on a Friday night / Saturday morning, and I’m about ready to call it a night. Sorry I’m not providing a better conclusion for you today. I’m excited to delve further into more complex SQL queries. Keep track of my shared journey of a beginner learning SQL. Cheers!

 

 

DSC_0163 Zach Doty Cover Photo for ORDER BY SQL Clause

ORDER BY Clause: SQL Statement Fundamentals

Welcome back to our SQL learning journey! It’s been a week and a half since my last post on using LIMIT. Work got crazy and I got the flu! 🙁 But we’re back in action. Today, we’ll delving deeper into SQL statements in PostgreSQL: ORDER BY.

Let’s jump in. Why would you need a statement that orders data? Whenever you query data from a table, PostgreSQL will by default return the rows in the order they were inserted into the table. (Read: not the order you want.)

To sort the result set from your SQL query, you can use the ORDER BY clause in the SELECT statement and specify a certain ascending or descending order.

Sample ORDER BY Clause Syntax

Here’s an example of the ORDER BY clause would look like within a SELECT statement.

SELECT column_1, column_2

FROM table_name

ORDER BY column_1 ASC / DESC;

Some important notes for the ORDER BY clause:

  • Specify the column you want to sort by with the ORDER BY clause
    • If you sort the results by multiple columns, use a comma to separate between the two columns
  • Use ASC to sort the results in ascending order
  • Use DESC to sort the results in descending order
  • Should you leave the ORDER BY clause blank, it will use ASC (ascending) by default

ORDER BY Clause Examples in SQL Statements

Alright, first up. We’ll do a basic SELECT statement, adding both ORDER BY and LIMIT clauses. Code and screen shot below. The query should select the first and last name columns from the customer table, ordered by last name ascending (A > Z), returning only the first 10 rows.

SELECT first_name,last_name  FROM customer

ORDER BY last_name ASC

LIMIT 10;

2017-02-16-001-ORDER-BY-SQL-Statement-Example

 

Let’s try another example. What if we want to do an advanced / multiple sort? Let’s try it out, and change it up ever so slightly from the first sample query.

SELECT first_name,last_name

FROM customer

ORDER BY first_name ASC,

last_name DESC;

2017-02-16-002-ORDER-BY-SQL-Statement-Example-Multiple

If we scroll down to the first set of duplicate first names, we’ll see the last name has been presented in descending order (Z > A).

More Details About the ORDER BY Clause

Did you know? In PostgreSQL, you can ORDER BY columns that aren’t explicitly selected within the SELECT statement? E.g. We could only select first and last name, but order by their address_id value. Interesting, yes? Important caveat, other SQL programs, such as MySQL, may not let you do this.

Below:

SELECT first_name FROM customer

ORDER BY last_name ASC;

2017-02-16-003-ORDER-BY-SQL-Statement-Example-Other-Sort

Wrap-Up

Thanks for joining me and hopefully you’ve learned the essentials for the ORDER BY clause. Feel free to check out the entire journey on how to learn SQL, thanks!

Getting Started with Alexa Development 02: Signing Up to Alexa Development Portal

Welcome back to our journey in learning how to program Amazon Alexa Skills via Echo voice search. In the previous article, we walked through the process of setting up an Amazon Web Services (AWS) account. Today, we’ll set up an account at the Alexa Development portal, a distinct entity from the AWS portal.

 

Without further ado, let’s jump in. Go to https://developer.amazon.com/public/solutions/alexa.

2017-02-03-001-Alexa-Developer-Home-Screen

You should land on something like the above screen. Click on the “Sign In” button, you can create a new account from this screen if you need.

Important Note: If you already have an Amazon.com account (regular old Amazon shopping account), use those credentials to log in.

Obviously, if you’re a returning Alexa Development Portal user, you can skip the account creation process shown below. If you’re creating a new account, you’ll need to fill out a screen that will likely resemble the below, and click “Save and Continue” when you’ve finished.

2017-02-03-002-Alexa-Developer-Account-Creation

Next, you should be presented with an App Distribution and Services Agreement screen. Be sure to give it a quick read. If you want to use the services, then you’ll need to agree by clicking save and continue. 🙂

The final registration step addresses payments and whether you plan to monetize the apps you develop. For the purposes of my usage, and this learning, I checked “No” to both options before proceeding.

2017-02-03-003-Alexa-Developer-Account-Creation-Monetization

Once you finish that step, you should find yourself in the Amazon Developer Console! Good thing we got the hard material out of the way first, huh?

This should wrap up a pretty quick introductory section for setups. Feel free to visit my previous article on getting set up with AWS, or go to my learning home page on how to start developing Alexa Skills. Thanks and see you at the next article!

bluebonnets Zach Doty cover photo for SQL COUNT Function

SQL Statement Fundamentals: The COUNT Function

 

Hello, hello, hello SQL fans! (Or gracious friends and family perusing the site. 🙂 ) The journey into learning SQL continues, and today we’ll cover the COUNT function. Jumping right into it, here’s our working definition of the SQL COUNT function:

The COUNT function should return the number of input rows that match a specific condition of a query.

Rather, this would appear to work similarly in concept to the COUNTIF(s) formula(s) in Excel.

COUNT Statement Syntax Examples

Here’s what a simple COUNT SQL statement might look like:

1. Basic COUNT (*) FROM SQL Statement

SELECT COUNT (*) FROM table;

Breaking it down a bit, the COUNT () function returns the number of rows returned by a SELECT clause. When you apply the COUNT () statement to the entire table, pgAdmin/PostgreSQL will scan the entire table in a sequential manner.

Additionally, you can specify a certain column count in your COUNT statement for better readability:

2. COUNT (column) FROM SQL Statement

SELECT COUNT(column) FROM table;

Similar to the COUNT(*) function, the COUNT(column) function returns the number of rows returned by a SELECT clause. However, if you have empty or NULL values, the COUNT function will not take those into account.

3. COUNT (DISTINCT column) FROM SQL Statement

If we do a bit of application from our past learnings, we can make a COUNT with DISTINCT SQL statement:

SELECT COUNT(DISTINCT column) FROM table;

Applying the COUNT SQL Function

Alright, so let’s work our way toward applying what we’ve learned. To start, let’s do the traditional probe of the table before diving in, to familiarize ourselves. Below, we’ve done a basic,

SELECT * FROM address;

2017-02-01-001-SELECT-ALL-Starting-SELECT-COUNT

As we get familiar with the table, we can scroll down and see this particular table has 605 rows in it. This will be a reference point as we continue.

Moving forward, we’ll execute a basic SELECT COUNT (*) FROM address; SQL query. Below, you’ll see a slightly different result was returned. Inst3ead of 605 rows, 603 was returned. At this point, kindly reference our note about empty / NULL  values being excluded from the COUNT function.

2017-02-01-002-SELECT-ALL-COUNT-1

We’ve established a general proof of concept for the SELECT COUNT statement, considering the reduced load on the server and yourself, for a quick count. Let’s now try calling specific columns. In our first exploration of the address table, we saw a number of columns, including the district. Let’s say we want to get a count for how many districts/states our customer base covers.

2017-02-01-003-SELECT-COUNT-DISTINCT-1

Above, a count of 378 distinct district values has been returned. Wow, what coverage!

A quick aside for future usage, you can also nest the column reference in its own set of parentheses, as shown and returned below.

2017-02-01-004-SELECT-COUNT-DISTINCT-Nested

Wrap-Up

There you have it! We’ve learned a bit about the COUNT function, what it does and how to use it. It will likely come in handy for future articles, particularly when we delve into group-by excercises. If you missed it, here’s the previous article on learning how to use SELECT WHERE and another recent article on learning how to use SELECT DISTINCT. Also, to start from the beginning, here’s my running list of articles on how to learn SQL. Cheers!

DSC_0071 Zach Doty Cover Photo for Amazon Alexa Skill Development AWS Account Setup

Getting Started with Amazon Alexa Development: Signing Up To Amazon Web Services (AWS)

Welcome back, folks, to our foray into Amazon Alexa Skill Development. If you’ve visited the blog recently, you’ll notice I’ve been juggling a few subjects for a minute, including SQL. For context, here’s our first article, starting Amazon Alexa skill development from the absolute beginning. (Following my haphazard skill development of Silly Marketing Strategies at the beginning of the year.)

In the previous article, we prepared ourselves for skill development by downloading public sample materials from Amazon’s Alexa Skills Kit Github page. Now, we’ll look at Amazon Web Services, one of the world’s largest (is it the largest?) public cloud computing platforms.

First, navigate to https://aws.amazon.com/. Click on “Create an AWS Account”, or equivalent, if you’re seeing something different. Note: this sign-up shouldn’t incur cost for you today, unless you choose otherwise. (Quick disclaimer, I’ll obviously try to supply the most accurate information possible. However, I cannot ultimately guarantee its accuracy. That you must do for yourself.)

2017-01-30-001-AWS-Home-Screen

Here, you’ll sign up and create a new account or log in. Note: if you have a regular Amazon login, I believe you can use that here. Because I have an existing Amazon account, most of my steps will follow accordingly, however, I’ll try to recreate where I can, like below.

2017-01-30-002-AWS-Signup-Login-Screen

If you’re creating a new account, then you’ll be prompted to choose between a company account and a personal account. Below is a preview of what the personal account signup page might look like.

2017-01-30-003-AWS-Account-Creation

Next, you’ll be prompted to set up payment options. (Obviously, we’re s electing a free account for the purposes of this educational exploration.)

2017-01-30-004-AWS-Payment-Information

You’ll next be asked to verify your phone number via a call requesting a PIN shown on screen. From there, you should be able to proceed  to the support plan selection screen, upon which I recommend choosing the free Basic version. After selecting all the appropriate options, you should be able to create your account!

2017-01-30-005-AWS-Account-Creation-Confirmation

Now that you’ve created an account, click “Sign In to the Console” or “Complete Sign Up”. You’ll re-enter your login credentials and proceed. You should now land on the Developer Console root page. (Note: Amazon, like Google, runs a ton of UI tests, so what you see may be slightly different than the below.)

2017-01-30-006-AWS-Developer-Console-Landing

The first order of business in our new account is to secure it. Click on your name in the top right and in the resulting dropdown menu, select “My Security Credentials”.

2017-01-30-007-AWS-Security-Preferences

Manage / Activate your MFA, and select a virtual device. This means you’ll need to perform some setup so you can scan a QR code with your phone (via Google QR code scanner app). I’ve skipped some illustrations and details here, because I’m not sharing my details, nor should you. What a somber ending to the article! In the next post, we’ll cover signing up for the Alexa Developer Portal, as we get move toward becoming proficient Amazon Alexa Skill Developers.

 

DSC_0030 copy Zach Doty Cover Photo For SQL Statement SELECT WHERE

SQL Query Fundamentals: SELECT WHERE

Welcome back, travelers! The journey continues in learning SQL. In case you missed it, the past couple of posts were learning to use SELECT DISTINCT and Restoring an SQL database with table schema only.

Today we’re going to learn about using the SELECT clause with the WHERE statement for essential SQL queries.

A Quick Recap, or, Why SELECT WHERE is Important

As previously mentioned, we’ve covered a range of beginning SQL topics. Mainly, we’ve learned about using the SELECT statement to query all (*) or query specific columns of data from a table. Because we’ve been working with a pared down table with only a few hundred rows, it’s not a problem in this “academic” setting to return all the rows. But what if we’re working in a larger database? A recent keyword research dataset 75,000 rows long comes to mind. (Though I would imagine that too, would be a small dataset in the grand scheme of things, but I digress.)

When we start working with larger databases, granularity will become vitally important. This is where (pun not intended) the SELECT WHERE statement comes in.

Sample Syntax of the SELECT WHERE Statement

With proof of concept in mind, let’s jump into it headlong. Below is a syntax example, demonstrating what a SELECT WHERE SQL query might look like.

SELECT column_1,column_2

                FROM table_name

                WHERE condition1;

The SELECT statement is old news to you adventurers that have been following along. The WHERE portion of this query will be the power in this article.

More About the SELECT WHERE SQL Statement

The WHERE clause appears right after the FROM clause of the SELECT statement. The conditions listed within the WHERE clause are used to filter the rows returned from the SELECT statement. Because we’re working in pgAdmin / PostgreSQL, we’ll have available standard operators to construct the conditions. Better still, some (or most) of the operators we’ll look at are fairly universal, so these operators should work in MySQL, Microsoft SQL, Amazon Redshift, etc.

List of SELECT WHERE Operators

Below is a list of common SELECT WHERE operators. Again, most of these should be fairly universal, regardless of the SQL database management program you’re using.

OperatorDescription
=Equal to
Greater than
Less than
>=Greater than or equal to
<=Less than or equal to
<> or !=Not equal to
ANDLogical operator AND
ORLogical operator OR

 

Plugging this back into SQL statements, we’ll be using the operators on the left to filter down and return only specific rows in our queries.

Sample SELECT WHERE Statements

Let’s  cover some guiding examples that will help us apply the SELECT WHERE operators. To start, we’ll kill two birds with one stone: jogging the memory by utilizing a previous query and exploring the table we’ll be querying. Below, we’ll be a bit naughty by calling on all columns from the table.

SELECT * FROM customer;

2017-01-30-004-SELECT-WHERE-CustomerID-OR

Above, we’ll see our results and some candidate columns to query the heck out of. Let’s keep moving.

 

Example 1: The Basic SELECT WHERE Statement with 1 Condition, Returning Only Customers with a Certain Name

Okay, let’s say that we want to only return customers of a certain name, say, “James”. The SELECT WHERE statement will help us make quick work of this database need.

SELECT last_name,first_name

                FROM customer

                WHERE first_name = ‘James’;

If all goes correctly, we should only get back a customer with the first name James. ‘Ello James!

2017-01-30-002-SELECT-WHERE-First-Name-Equals

Side note 1: you don’t have to return the columns you’re filtering against. For example, we could return the email column and still filter by name.

 

Example 2: A SELECT WHERE Statement Using 2 Conditions, Returning Customers with a Certain First AND Last Name

Perhaps you’ll want to do something more targeted with your data. I know this is a narrow and frankly creepy example of calling out one name, but think maybe of a City/State or Source/Medium pairing? Anyway, with the sample dataset we have, below we use the AND logical operator to combine two conditions into one query.

SELECT last_name,first_name

                FROM customer

                WHERE first_name = ‘Jared’ AND last_name = ‘Ely’;

If executed properly against this particular sample dataset, we should be returned only the values for one fictional Mr. Jared Ely.

2017-01-30-003-SELECT-WHERE-First-Name-and-Last-Name

Quick side note 2: that we should have mentioned sooner: we’re using single quotes here because the values we’re querying against are string values. As such, the single quotes help us match format, et cetera.

 

Example 3: Another SELECT WHERE Statement Using 2 Conditions, Returning Customer ID’s where payment was in certain dollar amount ranges

Let’s say we are trying to identify a range of customers in our database. In this third example, we want to query Customer ID’s and names in a certain range. We could think of it as a feeble attempt to get our first customers or our most recent customers for a special email flight. Below, we’ll exercise the OR operator to accomplish our desired output.

SELECT customer_id,first_name,last_name

                FROM customer

                WHERE customer_id <= 2 OR customer_id >=20 ;

2017-01-30-004-SELECT-WHERE-CustomerID-OR

Understanding the Subtle Differences Between AND / OR

Quick note about differences between the AND / OR operators. If you’re trying to filter data from two different columns, then AND is your filter. If you’re trying to get distinct values within a single column, then the OR operator will be best suited for the job.

 

Wrap-Up

What a world of possibilities we’ve opened! I found myself needing to slow down and pay more attention to detail in this area of learning. The nuances of selecting certain columns but filtering by others when practicing threw me for a loop once or twice.

Thanks for joining, in the next article, I’ll be covering some introductory material around the COUNT function. In the meantime, check  out our running list of posts on how to learn SQL.

Using the SQL SELECT clause with the DISTINCT Keyword

Introducing the SELECT DISTINCT SQL Statement

Alright, welcome back to our journey with SQL! If you’re just tuning in, we:

In this article, we delve slightly deeper into SQL queries, with the consideration that SQL databases and tables can have a lot of duplicate data, and you might not always want that duplication! This is where today’s subject comes in: using the SELECT clause with the DISTINCT keyword.

Sometimes when you’re managing a database or table, you only want unique (distinct) values when executing SQL queries. Thus, you can get around a large number of duplicate values using the DISTINCT keyword.

The Basic SELECT DISTINCT Syntax

Here’s the general format of what a SELECT DISTINCT query might look like:

SELECT DISTINCT column_1,column_2  FROM table_name;

Next, we’ll take a look a very simplistic example of why you might want to only pull unique values from a database.

Why Use the DISTINCT Keyword?

So we’ve generally been working from a popular public SQL “sample” or “sandbox” database that deals with DVD rentals. In one of the tables, “film”, there are a number of columns containing a wide range of information. For example, we can see below querying the release_year column of the film table, a few films were released in 2006. If we’re looking for a unique list, this is not a good start!

2017-01-30-001-SELECT-DISTINCT-Displaying-Duplicate-Table-Values

Using the SELECT DISTINCT Query in pgAdmin

Working off our example above, we want to see if 2006 is the only release year. To accomplish this, let’s try the below query:

SELECT DISTINCT release_year FROM film;

Below, the query in action through pgAdmin, after hitting F5 to execute and refresh. We can see 2006 is the only unique release year in this table. Zoinks!

2017-01-30-002-SELECT-DISTINCT-Displaying-Unique-Table-Values

Let’s try another example. Perhaps we’re interested in gathering pricing information for some revenue forecasting and analysis. Below is a query we would use to get the distinct rental prices from the film table:

SELECT DISTINCT rental_rate FROM film;

2017-01-30-003-SELECT-DISTINCT-Displaying-Unique-Rental-Rate-Table-Values

We see that there are only 3 price points in this table. What fun in simplicity!

Wrap-Up

Thanks for joining! Next post, we’ll be looking at the SELECT WHERE statement. Check the SQL learning & education page for a running list of articles. Cheers!