Chapter 10 – SQL INSERT and UPDATE

Section I – INSERT

When to use INSERT

The INSERT statement is used primarily in three instances.  One is when you wish to enter a single row into a dataset.  The second is when you wish to load the results of a SELECT statement all at once into a table.  (In some systems, such as Access, you can actually create a table using an INSERT statement).

(more…)

Advertisements

Chapter 9 – SQL CREATE, DROP, and ALTER

Section I – Usage

When to Use

So far we’ve spent our time discovering how SQL can be used to aggregate, sort, and manipulate data for analysis and presentation purposes.  But everything we’ve done is under the assumption the data was already available.  In this lesson we’ll learn about the other part of SQL, architecture!

SQL Architecture consists of creating, deleting (dropping), and altering tables, views, and indexes.  While most DBMSs will come with an out-of-the-box SQL administration tool (such as SQL Server Management Studio), SAS does not.  SAS does allow you to create a table manually using the GUI, however it is slow and cumbersome, so we’ll focus on the actual SQL syntax.

(more…)

Chapter 8 – SQL Case When

Section I – Usage

What is the CASE expression

The CASE expression is essentially the SQL version of “If-Then-Else”.  A CASE expression allows you to return a result that is conditionally evaluated for each row in your table.  Instead of using If [true] Then [NewValue], use the WHEN-THEN clauses when you want to execute a CASE expression that meet the criteria. The Else statement is optional but highly suggested.

The CASE expression can be executed in two manners.  One is be evaluating the operand following the CASE keyword, which will then require you to account for each possible result, the other is to use the operand in each WHEN expression. 

(more…)

Chapter 7 – SQL Joins

Section I – Usage

What is a Join?

Up to this point, we’ve only used single-table queries.  If the only analysis we ever did were on single-table databases, the queries we’ve learned so far would be all we would need!  However, analysis is never that easy.  Most production databases will be normalized in a snowflake schema, which require lengthy joins, and even datamarts using star schemas will require multiple table queries.

So, how does an analyst cope?  With Joins.  SQL utilizes seven joins, six distinct, which allow us to combine multiple input-tables into a single output-table. 

(more…)

Chapter 6 – Group By

Section I – Usage

When to Use

In SQL, data aggregations are done using one or more aggregation functions and sometimes the GROUP BY statement coupled with the HAVING statement. Using these functions, we can return data in a more usable format for reporting and analysis purposes. The HAVING statement allows us to constrain the results of an aggregation, similar to a WHERE statement.

(more…)

Chapter 5 – SQL Functions, Math, and Coalesce

Section I – Overview

As with most every computer programming language, SQL supports the use of functions to manipulate data.  There are two types of functions in SQL; Those that produce strings and those that produce numbers.  However, it’s important to realize that functions in SAS SQL can be different than functions in other database systems, so please be careful when trying to use queries in other systems.

(more…)

Chapter 4 – SQL WHERE Statement

Section I – Structure and Output

WHERE Structure

As with all SAS procedures, the WHERE statement allows you to constrain the results of a procedure based on one or more conditional logic statements.  Every row that meats the criteria set forth in the statement will be in the resulting dataset.  Multiple constraining statements can be grouped together using Boolean operators (and/or), as shown in the DeMorgan’s Laws section.

(more…)