Published

July 7, 2000

SQL

It seems that I’ve been sending queries to databases for decades. Local, remote, clean, not so clean, all types of databases. I’ll circle back to T-SQL later, I’ll touch on BigQuery and others, but I mostly want to focus on using RStudio and SQL.

The reason I want to focus on RStudio:

  • Desktop version is free
  • This entire site is written using Quarto in RStudio
  • I can run my R or python scripts as well as SQL queries in one Quarto document to generate the notes in this section of the site
  • It’s like eating pizza in bed! We can debate if that’s a good or bad thing in another site.

Section Breakdown

RStudio Setup

Setup dplyr

Connect to Big Query - bigrquery & DBI packages

Query Big Query with R

Query Big Query with SQL

SQL Basics

Naming

Caps

Quotes

Indentation

Comments

Naming columns

Naming tables

Query

How to query

Basic query

Data Types

VARCHAR

VARBINARY

STRING

NUMERIC

DATE & TIME…

Database

Table

CREATE TABLE

CREATE TABLE AS

INSERT INTO

INSERT INTO SELECT

UPDATE

DROP

TRUNCATE

ALTER ADD

ALTER DROP

ALTER RENAME

ALTER CONSTRAINTS

TEMPORARY TABLE

Statements

Base

FROM

SELECT

SELECT DISTINCT

WHERE

Arrange

ORDER BY

Assign

AS

Functions

Edit

COALESCE

Reduce

TRIM

LTRIM & RTRIM

Measure & Aggregate

LENGTH

MIN & MAX

AVG

SUM

COUNT

COUNT DISTINCT

Convert

CAST

CAST - Big Query specific

SAFE_CAST

ROUND

Filter

Extract

EXTRACT

Subset

SUBSTR

WHERE

IN & NOT IN

DISTINCT

LIKE

WILDCARD

IS NULL

IS NOT NULL

BETWEEN

CASE

EXISTS

SELECT

SELECT DISTINCT

SELECT TOP

FETCH

ROWNUM

WHERE

HAVING

ANY

ALL

Union & Merge

Merge

CONCAT

CONCAT_WS

||

+

Union

UNION

UNION ALL

Group

GROUP BY

ROLLUP

COALESCE

Joins

INNER or JOIN

LEFT

FULLL

SELF

Suqbuery

WITH

EXISTS

Date & Time

Time

TIMESTAMP

CURRENT_TIMESTAMP

TIMEDIFF

TIMESTAMPDIFF

TIMESTAMP_DIFF

TIMESTAMP_SUB

Date

DATEDIFF

DATE_DIFF

DATE_SUB