Posts

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.

Operator Description
= Equal to
Greater than
Less than
>= Greater than or equal to
<= Less than or equal to
<> or != Not equal to
AND Logical operator AND
OR Logical 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.

DSC_0037 (2) Zach Doty Cover Photo for SQL SELECT Clauses & Statements

The SELECT Clause, SQL Statement Fundamentals

Getting Started in SQL Statement Fundamentals

Howdy, all, welcome back to our journey learning SQL. This post will deal with basic SQL statements. In fact, most of these SQL statements should be applicable to most major types of SQL databases (MySQL, Oracle, and so forth.)

The SELECT Statement (or Clause)

First up, we’ll start with the “Hello World” of SQL: SELECT. We’ll look at the formal conventions of the SELECT statement and some examples using the statement. A quick aside: SELECT is also often known as a clause in SQL settings. For the purpose of this article, the educational materials I’m walking through proposes clause and statement may be used interchangeably for these purposes.

SELECT is one of the most common tasks in querying data tables with SQL. Further, it has many clauses that may be combined to form a powerful query. Let’s look at the basic form of a SELECT statement. Below, you will use the SELECT statement to call in a column or some column names, separated by a comma if multiple columns, then FROM a table.

SELECT column1,column2,column3 FROM table_name;

So, breaking down again the select statement.

  1. Specify a list of columns in the table which you want to query via the SELECT clause
  2. Use a comma between each column you are querying, if multiple columns
    1. If you want to query all columns in a data, save yourself some time by using the * asterisk wildcard as a shortcut for selecting all columns
  3. After you’ve called in the appropriate columns in the SELECT clause, follow it with FROM, where you indicate the appropriate table name

 

Sidebar 1: Random facts about the SELECT statement and SQL language

Time for a TV timeout! Did you know that that the SQL language is case insensitive? So if you use “SELECT” or “select”, you should get the same results. For the purposes of this education and sharing, SQL clauses / keywords / statements will be typed in all uppercase caps to make the code easier to read and stand out among all this text. 🙂

Okay, just one more sidebar note! It’s generally not encouraged to use the asterisk (*) select all columns wildcard in queries. Why? If you have a robust table with a ton of columns and a great depth of data beneath those columns, you could be placing unnecessary load on yourself, the SQL server and the SQL application (pgAdmin / PostgreSQL).

 

Application Example 1 for the SELECT Statement

Let’s jump into executing actual SQL commands against databases in pgAdmin!

Below, I’m going to open the file tree, select “dvdrental”, then click “Tools” in the top menu, and select “Query Tool” to execute arbitrary SQL queries and statements.

 2017-01-22-001-pgAdmin4-PostgreSQL-Open-Query-Tool

You should then see the screen below if you are in pgAdmin 4. If you are in pgAdmin 3, then it should appear as a new window.

2017-01-22-002-pgAdmin4-PostgreSQL-Query-Tool-Start

Let’s have some fun, why not go against our own advice and query a whole table? Below, you can see in the query window, we’ve typed:

SELECT * FROM actor;

Into layman’s terms from above, we’re selecting (SELECT) all columns (*) from (FROM) table actor (actor).

Important: My image example doesn’t show it below, please, put a semicolon at the end of the line! (I got hasty making screen shots. 🙂 )

2017-01-22-003-pgAdmin4-PostgreSQL-Query-All-Columns-Actor

After you’ve typed the query, go to the lightning bolt above the window, and click “Execute/Refresh”. I’m just going to punch F5, because I’m about that keyboard shortcut life. In the future, I’ll likely introduce a command or action, note its keyboard shortcut and use that shortcut moving forward for any other examples.

The query should run and refresh. I now have a new tab in pgAdmin, with data output returned from my query. Let’s take a look below.

2017-01-22-004-pgAdmin4-PostgreSQL-Query-All-Columns-Actor-Execute

Okay, so we’ve got four columns returned: actor_id, first_name, last_name, and a last_updated. You’ll also note that below the column names are quick descriptions of the data type for each column. And of course, we see our beloved celebrity data returned below, all 200 rows.

Let’s examine further the data types listed below each column name. The integer below actor_id is pretty simple, numbers. Next, the character varying, below first_name and last_name. Character varying is essentially just string text. The (45) denotes the limit on character count length. Last, the timestame with YYYY-MM-DD and military style HH:MM:SS.XX time, without time zone. We won’t worry too much about the timestamp for now.

If you’re somewhat knowledgeable in SQL, you may rightly decry our glossing over of data types. For beginners, data types will be covered in more detail later. Data types will become increasingly important later, as we execute statements such as, WHERE, in which data types make or break the query. Promise, we’ll cover data types in more detail later.

 

Application Example 2 for the SELECT Statement

So we kind of broke our rules in the first SELECT statement SQL query example. However, some rules were made to be bent or broken, yes? In this example, we’ll follow best practices a bit more closely and select a column or columns by name from a table within the dvdrental database.

Remembering our SELECT column1,column2,column3 FROM table_name format, consider the below, and see it typed in (with closing semicolon on the statement!

SELECT first_name,last_name FROM actor;

2017-01-22-006-pgAdmin4-PostgreSQL-Query-Two-Columns-Actor

Before we execute and refresh via F5, please note that I’ve not included spaces between the column names and comma in the statement. Alright, below is what we see when we execute and refresh.

2017-01-22-007-pgAdmin4-PostgreSQL-Query-Two-Columns-Actor-Execute

In our screen shot, we see at bottom right, confirmation of the query execution. In the output window, we’ll only see what was queried: first_name, last_name. So we’ve left out the actor_id and last_updated columns.

One more note on our output, you’ll notice that all 200 rows were returned for this query. If you think about enterprise level data, that could be 200 million rows, zoinks! As we progress through our material, we’ll look at the aforementioned WHERE statements and other conditions / methods to limit or control the rows in query output.

Perfect Practice Makes Perfect

For the educational benefit, we’ll reinforce and apply what we’ve learned one more time. Let’s say that we’re a business and marketing analyst back in time when DVDs were still used (it’s okay to laugh!) We need to send a New Year’s promotional email (It’s January 2017 when this post was originally published) to all existing customers. We’re going to build and execute a query to that effect.

Below, you can see we’re still in the dvdrental database, in the arbitrary query code input window, with statement: SELECT first_name,last_name,email FROM customer;

2017-01-22-008-pgAdmin4-PostgreSQL-Application-Query-Existing-Customers

One last quick note on syntax and formatting: you can go multi-line! In the below screenshot, we have typed the same query, but added formatting. Explained: SQL will read your code as one line until it runs into the closing semi-colon (;). A common practice is that for every keyword, a new line is created in the query. (Of course, the statement is not closed via semi-colon until appropriate.) I’ve also taken one more step below from various ranging coding practices (CSS, C++, etc.) and indented the ongoing portion of the query to help visually break up the code a bit.

2017-01-22-009-pgAdmin4-PostgreSQL-Application-Query-Existing-Customers-Multi-Line

Wrap-Up

Woohoo, we did it! We ran our first basic SQL queries in pgAdmin / PostgreSQL. We learned how to select all columns within a table and select separate desired columns within a table. Be sure to re-visit my other articles on learning SQL, visit the previous article on restoring an SQL database with table schema only.

In our next post, we’ll learn about using a SELECT DISTINCT statement.

Restoring a SQL Database with Table Schema Only in pgAdmin x PostgreSQL

Howdy! Welcome back to our shared journey of learning SQL. Last time, we learned about creating, deleting and completely restoring SQL databases in pgAdmin. Today, we’ll learn about how to restore a database, but only its table schema.

Specifically, we’ll restore the table names and preferences for types of data within those tables. However, the actual data itself won’t be ported in.

Think of it as taking your house or apartment, and recreating, but you don’t move in with the furniture. (Maybe a sibling instead? 🙂 ) According to the material I’m working through, this method of database restoration is very common and is something we should have down pat.

 

Database Table Schema-Only Restoration, Method 1

An easy method to start this is to right click on the Databases header near the top of the file tree, and to select “Create” > “Database”. With a fresh new database, we’ll have flexibility to do some more management on the “front” side of things. (Knowing I may be abusing terminology a bit, but makes sense to me while writing this.)

 

2017-01-21-001-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-P1

For this example, I’ve finished the new database creation by naming (“OnlySchema”) and saving a new database. Below, you can see the new database in the file tree. Also, if we click through the tree as such, “Schemas” > “public” > “Tables”, we’ll notice there are no tables! (Compare that path exploration to the dvdrental database tables, where you can find 15 tables.)

 

2017-01-21-002-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-P2       2017-01-21-003-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-p3

Anyway, let’s get on with restoring table schema only!

  1. Right click on “OnlySchema” and select “Restore”
  2. Select the “Custom or tar” option for the Format field
  3. Select the file via the dialogue, or paste in your file path
    • Up to this point, you should notice we’ve taken the exact same steps for a full database restore. However, #4 is where things are slightly different. Pay attention!
  4. As shown below, click “Restore Options”, and activate the radio button for “Only schema” to yes
  5. Click “Restore” and refresh!

 

2017-01-21-004-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-p4

After the refresh, we should see something like below, where if we select the tables option and view “Properties”, we’ll see 15 empty named tables.

 

2017-01-21-005-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-p5

 

Method 2: Schema-Only Restoration onto a Database with existing tables, data

What if we’re working with a database that contains existing tables and data? And suppose we need to restore only the schema? Perhaps an error was committed in formatting / management and must be corrected.

Fortunately, this method of restoration is extremely similar to Method 1. For this scenario, there’s one added step toward the end:

  1. Right click on “OnlySchema” and select “Restore”
  2. Select the “Custom or tar” option for the Format field
  3. Select the file via the dialogue, or paste in your file path
  4. Click “Restore Options”, and activate the radio button for “Only schema” to yes
    • Here’s our jump off point, the next step is what differs from Method 1.
  5. Scroll down the dialogue box slightly. You should see a field and radio button for “Clean before restore”. Activate the radio button to “Yes”
  6. Click “Restore” and refresh!

 

2017-01-21-006-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-p6

After running the restore job and refreshing, we can verify the Schema Only and data clean prior to restoration occurred correctly. Below, we can click through the file tree down from “dvdrental” > “Schemas” > “public” > “Tables” and check the table properties to see that there are zero (0) rows of data.

 

2017-01-21-007-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-p7

 

Wrap Up

I do hope these articles are serving a useful introduction to navigating pgAdmin, and getting familar with the foundations of PostgreSQL and databases. In the forthcoming articles, we’ll start learning about basic SQL syntax. To keep track of all the shared SQL posts and learnings, visit the SQL Education page for a list of articles to date. Cheers!

DSC_0322 Zach Doty Cover Photo for SQL Database Creation, Restoration, Deletion

Creating, Restoring and Deleting SQL Databases with pgAdmin 4

Howdy! Let’s continue the journey into learning Structured Query Language (or SQL). Previously, I shared my notes on the absolute beginning point for starting SQL, and also getting started with PostgreSQl and pgAdmin.
Now, we venture into some basics of database management via SQL:
• Creating a database with pgAdmin
• Restoring a database with SQL commands
• Deleting a database

Creating a new SQL Database in pgAdmin via Graphical User Interface

So, let’s retrace our steps on creating a new database. After you’ve successfully started pgAdmin and accessed your databases, right click “Databases” and go to “Create” > “Database”. Then choose your desired name and save.

2017-01-16-001-pgAdmin4-PostgreSQL-Create-Additional-Database

Give it a second, and your brand spankin’ new database should be created! (Alternate method, you could select the “postgres” database in the file tree and select the option to create a new, arbitrary SQL query. In the Query window, type:

CREATE DATABASE dvdtwo;

2017-01-16-002-pgAdmin4-PostgreSQL-Create-Additional-Database-Query-Tool

If you run and refresh you should see the output of the code’s successful run, with no results, and a new database in the file tree. Joy!

2017-01-16-003-pgAdmin4-PostgreSQL-Create-Additional-Database-Query-Tool-Success

 

Deleting (Dropping) Databases in pgAdmin

Let’s build some character and delete the database we just created! Fortunately, deleting (also known as dropping a table is super simple in pgAdmin / PostgreSQL. Below, in the interface, we right click the name of the newly created database and click “Delete/Drop”, and click Okay. Obviously, be sure you don’t do this accidentally.

2017-01-16-004-pgAdmin4-PostgreSQL-Delete-Drop-Database-User-Interface

 

Gracefully Restoring a Database

In the second SQL article, we previously restored a database. We’ll revisit that process in the pgAdmin interface again here. Practice, practice, practice! Let’s restore the dvdtwo database created earlier in this article for good practice. Below, we’ll right click the “dvdtwo” tree header and select the “Restore” option.

2017-01-16-005-pgAdmin4-PostgreSQL-Restoring-Database-2-User-Interface

In the resulting dialogue box, I attempted to use the interface’s file browser. However, I was met with some bugs/error messages. Thus, I copied the file path from Windows Explorer, pasted it into the file path name field and began the restore job. It took a few moments to run, then I refreshed the database and saw tables populated under dvdtwo > Schemas > public > tables. Hooray!

Wrap-Up

Thanks for tuning in! Next article, I’ll share my learnings for restoring a database, but with only the table schema. So to be more specific, I’ll restore the framing and architecture of the sample database, but without the data. This seems a bit like copying and duplicating sheets in Excel, but with blanks for data, so you can populate at your discretion. See you soon.

 

PS- If you’re just arriving at this article, here’s my running list of articles that detail how to learn SQL.

A scenic shot of Palo Duro Canyon from a hiking trail.

Starting SQL from the Absolute Beginning

What is this?

To be honest, I haven’t done much blogging or writing lately. Life (read: work) happens, and it gets busy. However, I’m feeling invigorated with a strong desire to expand my horizons. One such manifestation (snooze, I know) of this is to learn a bunch of new things, for where technology and industry are headed.

Thus, I present to you, learnings on SQL from a course I’m taking! Frankly, this should be boring to a majority of people. However, this is a way of self-accountability, and sharing what I learn along the way about Structured Query Language (SQL) programming, and hopefully how it applies to a bunch of awesome things! If you haven’t bounced already, here we go.

Square 0: What are Databases?

As the title/URL describes, I’m starting from an absolute beginning. While I have a range of experience (or lack thereof in some cases) with HTML, CSS, JavaScript, PHP, Regex, etc., I am completely new to SQL. That said, this isn’t square 1, but square 0.

Databases are systems that allow users to organize and store large amounts of data. Databases can have a wide variety of users and use cases. Potential database / SQL users include analysts in marketing, business or sales. Additionally, technically-focused personnel such as data scientists, software engineers and web developers may also use databases for a range of purposes.

Discussing the Transition from Spreadsheets to Databases

Many practitioners in business have some familiarity with a spreadsheet program, such as Microsoft Excel, Google Sheets or Open Office.

Spreadsheets are often useful for a one-time analysis piece or quick charting, particularly with small data set sizes. Additionally, usage of a spreadsheet program ensures that a wide range of folks with varying expertise can access, use and manage the spreadsheet data.

So where do databases come in? Databases are great for ensuring data integrity and/or handling large & robust data sets/ Databases are also great for use cases where you need to quickly merge different data sets or automate actions with your data for frequent usage or re-use. Additionally, databases are widely used in powering websites and other applications.

In summary, the transition from spreadsheet to data comes when you bring massive amounts of data to life! The hope of the educational materials are that institutional knowledge in spreadsheets can be leveraged to help understand and master SQL database usage.

What are some quick hacks to translating spreadsheets to databases? The first is that tabs of a spreadsheet can be set equal in your mind to tables. Within each table is contained a set of rows, and a set of columns.

What are some of the top SQL database / data warehouse platforms options?

For your enlightenment and education, below are some common SQL database and data warehouse platforms.

  • Postgre SQL (Preferred for example use: free and open source, widely used on the internet, multi-OS)
  • Amazon Redshift data warehouse
  • MariaSQL
  • Microsoft Access
  • Microsoft SQL Server (MS SQL Server Express)
  • MySQL
  • Oracle Database
  • SQLite

 

In addition to straight database programs and data warehouses, there a range of other programs that utilize the SQL programming language for core functions. Below is a short (and rather incomplete list) of other popular SQL applications and uses.

Other Applications of SQL

  • Looker
  • MemSQL
  • Periscope Data
  • Hive (On top of Hadoop)
  • Google BigQuery
  • Facebook Presto

 

A Bit More About SQL

SQL stands for Structured Query Language. It can be applied in a wide range of manners, including PostGreSQL that will be used in these examples. Further, SQL is the programming language that will be used to communicate with databases in this experience.

To start, a sample SQL statement:

SELECT customer_id, first_name, last_name

FROM sales

ORDER BY first_name

 

Review

Here’s a quick overview of what I learned, and have consequently shared.

  • What’s a database?
  • How is a database different from a spreadsheet?
  • Why should a database be used?
  • What are some database / data warehouse platforms that operate with SQL?

If you’re just arriving at this article, here’s my running list of articles that detail how to learn SQL.

Upcoming Educational Material on SQL

  • PostgreSQL Installation
  • Databases and Tables Basics
  • SQL Syntax and Statement Fundamentals
  • GROUP BY Clause

 

  • JOINS
  • Advanced SQL
  • Additional SQL Commands

 

  • Create Databases and Tables through SQL
  • Views
  • PostgreSQL with Python
DSC_0034 Zach Doty Cover Photo How to Learn SEO in Five Minutes

How to Explain SEO in 5 Minutes

Today, you’re going to learn how to explain SEO to anyone in five minutes! Is it a comprehensive, end-all, be-all example? No. Will it help someone quickly understand the basics of a complex practice? Yes. Let’s jump in. This is, after all, a five minute guide!

Starting Point: SEO is not a black box.

For context, I often hear that SEO is a “black box” and it’s anyone’s guess as to how you rank in search engines. I’m always happy to hear that! It means we have a chance to explain what SEO is really about. Google isn’t a black box. Rather, its algorithms and search engines are so complex at a large scale, it’s difficult to comprehend on a daily basis.
With introductions out of the way, here’s how you do it:
A Job Application
Consider SEO like a job application. You have a resume, references, you deal with a recruiter and a hiring manager. And you want a great career!
In SEO, you have a website, backlinks, the search engine and the user. You want to be found and loved!

Your Resume
Your resume is your website. You control the content and tailor the experience to each opportunity. You balance who you are and what you do.
Your website balances your brand story and products or services. You target keywords to pages of your site, just like your jobs are targeted to certain functions and skill sets.
Which resume would you choose to interview for a Marketing Director?

A. “I’m Leroy, I’m awesome. I’m really cool, I enjoy long walks on the beach and like Ethiopian food.”

B. “Kevin is a seasoned marketing professional who has managed and implemented effective marketing promgrams.”

By the same token, which website would you do business with? One with “high quality products and synergistic solutions”? Or a site that speaks in plain, compelling English?
Your References
Your references are your backlinks, or 3rd party websites linking to your own. You can influence these endorsements and relationships, but not necessarily control them. On both sides of this comparison, quality trumps quantity.

Which of the following references holds the most weight for a candidate?

A. “Little Michael is my favorite nephew, he eats a lot at Thanksgiving, you should hire him.”

B. “Jake paid me some money to recommend him. I guess he’s cool.”

C. “Ryan took our marketing department to the next level with smart strategy. He works well with the team implementing our campaigns.”

Ryan is the best choice. A bunch of low quality or fake recommendations will do a job seeker little good if not harm. By the same token, it’s better to have a few excellent backlinks than a bunch of spammy or fake endorsements.
The ultimate goal
When you’re a job seeker, interviews are great. But you really want the career! Likewise, it’s great to rank well in the search engines, but you want new, loyal customers that convert on your website.

Above all else, optimize your website for your users. The search engine results will follow.

Where to next?

That’s how to explain and understand SEO in five minutes! Obviously, there’s much, much more to learn. However, this will get you on the right path. Dive deeper into SEO with a fantastic Beginner’s Guide from Moz.
DSC_0265 Zach Doty Cover Photo How to Win SEO Long Term

Winning Long Term SEO for Years

A few years ago, the average CMO tenure was 23 months. Yikes. That’s barely long enough to get acquainted and jamming with your staff. Happily, a recent indicates a CMO’s average tenure now sits at 45 months. It’s no secret that personnel and vendors come and go with the executive. For agencies and in-house digital marketers, burgeoning attention and spend in digital means unprecedented opportunity. Naturally, there is tremendous potential for fruitful, long term, agency-marketer-executive relationships. Yet success seems anything but simple, we can’t just ride off into the sunset. (Darn, I like Westerns.) Long term digital marketing engagements still seem precarious at best, especially for Search Engine Optimization (SEO). The “quick wins” and short-sighted quarterly business environment is still firmly entrenched. With a bit of work and love, long-term client-marketer-executive relationships can be profitable foundations that bolster your agency or brand for the next 10 years.

Succinctly, winning SEO for years is a bit like a strong friendship. It’s not perfect, but an active exchange of trust and vulnerability makes something great. It’s not always “up and to the right”. Every friendship is different, but there are guiding principles and helpful tidbits all can apply.

1. Be a Good Wingman: No Paid Links, period. Just don’t do it. More profound material that follows, but an astounding number of folks still buy crappy links. The alleged 3 month benefit isn’t worth your 5 or 10+ year relationship. Consider the latency of SEO: bad backlinks bought today probably won’t help much this quarter. However, they could be indexed in 1-2+ years and derail your campaign at a crucial moment. Because you’re a star marketer, you may find yourself explaining this to other. Less enlightened (don’t tell them that!) colleagues, staff and supervisors need to know that optimizing for the user, not the search engine, brings the win home. Provide an enchanting user experience, and results will follow. Bonus: If you want to scope out your link situation, check out Link Detox or use a great SEO software platform to audit your link profile quality.

2. Be a master of reporting and storytelling. A common agency story runs like this, stop me if you’ve heard this one before: 3 months into an engagement, the client loves the work, everyone is excited! 6 months: communication tapers off and so does the work. At 7-8 months, an email arrives, “Can we get an audit of our billings and work?” Next month, “We’re not seeing the value…can we talk?”

Learning to communicate and report intelligently will help you avoid the account/campaign death spin and 9at least) double the life of your engagement. How you say and show success is the lifeblood of your agency’s (or brand’s marketing) future. In agencies, you’ll often hear, “We don’t have time to educate the client.” True, but you surely can’t afford to leave them in the dark. Build small bits of education into your reporting at each step. Your client (or executive) should be educated enough that they could dispose of you, but so satisfied that they’ll never want to.

Reporting over a long time horizon is tricky. Before you have a full year of data, seasonality can make your work look bad if you’re uninformed on market trends. (What if your 6 month evaluation is in a demand trough?) Furthermore, it’s not realistic to primarily report on the same metric month after month. (E.g, keyword rankings) The client or executive may deem organic traffic the determining KPI at your campaign start. However, fascination is fleeting. Revenue or return on investment will likely be your KPI in 12 months’ time.

Use a consistent mix of metrics to paint a rich picture of your phenomenal digital marketing campaign. When you forecast, it’s common to use upper and lower bounds (like economists and weathermen!) to ensure you don’t look like too big of an idiot in any case. Similarly, use “upside metrics” such as revenue generated from organic and ROAS/ROI and “downside metrics” like cost per acquisition and results decay analysis if your client or executive stopped working with you. Assuming sound communication, your reporting should become easier over time. When showing CMO’s and presidents year-over-year, 3, 5 year trends of their performance, we don’t have to do much selling.

3. Learn to live through redesigns. The shelf life of a website is (and should be, to a degree) these days. Blanket statements are a great way to get in trouble. However, many sites (looking at you, Fortune 500 companies) are on the cusp of major changes for mobile friendliness. If you’re around for more than a couple of years, you will see the client’s site go through a redesign. Ideally, you will start the redesign process small pieces at a time. When you present conversion improvement data, mash up clickstream analytics with heatmapping data for compelling improvements.

However, if you’re not regularly testing, you should start. If you approach a redesign without aforementioned experience, here are a few things to keep in mind.

  • You must exercise discretion and attention to detail. Choose carefully which battles to fight. Web design & development projects are notorious for being past schedule, over budget and largely dissatisfying on the whole. Don’t be the unnecessary logo critic and save your clout for things that matter to SEO.
  • Here’s what you do need in a redesign: a seat at the table, the power to veto content, technical matters and the ability to test after completion. Conventional wisdom in design is at best, a starting point. Best practices will not transfer from one client to the next.
  • Here’s what you don’t need: Approval on typography, creative direction of promo tiles and so on. Did we mention you should pick your battles?

A redesign done well can bolster traffic, interest and conversions. A poor redesign can break a site, its rankings and the business behind it. Mind your technical details such as URL aliases, file extensions, redirects, robots files, sitemaps and more. Don’t know what I just rattled off? Send me a message, I’m happy to explain.

4. Visible Progress. Related to reporting, what tangible value can you demonstrate on a regular basis? Clients and executives often refer to digital marketing as “black box” and “murky”. Their perception is understandable. However, many facets of digital marketing are so precise and complex at scale that simple explanations are very difficult in quick meetings. What pieces of content or site changes can you point to? Given that digital is complex, your client or executive must have concrete, simple wins they can shop as their own.

5. Deep involvement in the client’s business. Don’t get left out in the cold. Many agencies suffer volatility because they enter late in the marketing game with a client. Brand managers suffer irrelevance by thinking small, too late. When a client or executive says sales or down, don’t nod and make a cursory note in your moleskin. Unleash a barrage of intelligent questions about their customer intelligence, acquisition, retention and sales process. Your ability to engage on a deep business level (down to P&L and EPS) will be a large determining factor in your long term success.

This is not an all-inclusive guide to winning SEO long term. However, it’s my hope that this will a jump off point for your team as it conquers its next digital marketing success.