Endless SQL Possibilities

Capitalization, Indentation, and Semicolons

can use lowercase in SQL queries but capitalization and indentation helps read the information more easily.

SELECT field1
FROM table
WHERE field1 = condition;

Semicolon — statement terminator and is part of the American National Standards Institute (ANSI) SQL-92 standard

not all SQL databases adopted or enforce the semicolon
if a statement works without it, its fine

WHERE conditions

WHERE clause narrows down your query so that the database returns only data within an exact value

Examples:

If you’re looking for a specific customer with the last name Chavez, WHERE clause would be:

WHERE field1 = ‘ Chavez ’

If you’re looking for all customers with the last name that begins with “Ch”, WHERE clause:

WHERE field1 LIKE ‘ Ch% ’

% and * signs are wildcards

SELECT all columns

SELECT * to select all columns

Comments

Comments are text placed between certain characters, /* and */, or after two dashes - -

SELECT
    field1 /* this is the last name column */
FROM
    table -- this is the customer data table
WHERE
    field1 LIKE 'Ch%';

Comments can also be added outside of a statement as well as within a statement

-- This is an important query used later to join with the accounts table 
SELECT
        rowkey, -- key used to join with account_id
    Info.date, -- date is in string format YYYY-MM-DD HH:MM:SS
    Info.code -- e.g., 'Pub-###'

FROM Publishers

Example of a query with comments

-- Pull basic information from the customer table
SELECT
	customer_id, --main ID used to join with customer_addresss
	first_name, --customer's first name from loyalty program
	last_name --customer's last name
FROM
	customer_data.customer_name

can use # in place of - - but not all SQL databases support it.

Aliases

Done with SQL AS clause

Example:

SELECT 
	my_table_alias.actual_column_name AS my_column_alias
FROM
	actual_table_name AS my_table_alias

Putting SQL to Work as a Data Analyst

Scenario 01

You want to pull all the columns: empID, firstName, lastName, jobCode, and salary. Because you know the database isn’t that big, instead of entering each column name in the SELECT clause, you use SELECT *. This will select all the columns from the Employee table in the FROM clause.

SELECT
	*
FROM
	Employee

Now, you can get more specific about the data you want from the Employee table. If you want all the data about employees working in the 'SFI' job code, you can use a WHERE clause to filter out the data based on this additional requirement.

SELECT
	*
FROM
	Employee
WHERE
	jobCode = 'SFI'

Result:

Scenario 02

Suppose you notice a large salary range for the 'SFI' job code. You might like to flag all employees in all departments with lower salaries for your manager. Because interns are also included in the table and they have salaries less than $30,000, you want to make sure your results give you only the full time employees with salaries that are $30,000 or less. In other words, you want to exclude interns with the 'INT' job code who also earn less than $30,000. The AND clause enables you to test for both conditions.

SELECT
	*
FROM
	Employee
WHERE
	jobCode <> 'INT' 
      AND salary <= 30000;

Results: