Guide to SQL

Structured Query Language (SQL) is a commonly language for processing relational databases. Below is a beginner's guide to using SQL.

Opening SQL

One can open SQL from the command line by typing sqlite3 at the prompt.

Exiting out of SQL

SQL commands are prepended with a "." so to exit SQL, type .quit

Executing a SQL script

Supposing one has a file called test.sql, one can execute it by typing .read test.sql at the prompt (inside SQL).

Reading data into SQL

To read data into SQL (e.g. from a CSV or tab-delimited raw file), there are actually two steps:

Create a SQL table which will hold the data
Read the raw data file into SQL's memory

To do step 1, type the following:

CREATE TABLE datname(
  "var1" CHAR,
  "var2" INTEGER,
  ...
  "varN" REAL 
);
where `datname` is whatever you want to call your database in SQL.

To do step 2, type the following:

.mode csv
.import /path/to/file.csv datname

It is also possible to accomplish this interactively in a GUI if you have, e.g. SQLite Studio or similar.

Printing N observations of your database

To print N observations of your database, type

SELECT * FROM datname LIMIT N;

To print N observations of a subset of variables, type

SELECT var1, var2, ..., varK FROM datname LIMIT N;

Deleting observations (and subsetting statements)

One can remove observations under certain conditions as follows:

DELETE FROM datname WHERE condition;

note that condition is some conditional statement, like var1='value1' or var1=10 or var1<=10, etc.

If you leave off the WHERE condition statement, you will delete all observations from the database.

Delete column from table

SQLite does not allow you to drop a column. The workaround is to make a new table that contains only the columns you want to keep, then rename the new table to the original template's name.

-- Create a table called 'datname_temp' with the columns we don't want to drop
CREATE TABLE datname_temp(var1, var2, var5);

-- Copy the data from the columns we want to keep to the new table
INSERT INTO datname_temp SELECT var1, var2, var5 FROM criminals;

-- Delete the original table
DROP TABLE datname;

-- Rename the new table to the original table's name
ALTER TABLE datname_temp RENAME TO datname;

Add column to table

Adding is easier than removing:

ALTER TABLE datname ADD COLUMN newvarname type DEFAULT value;

where newvarname is the new column name; type is the type of values that column will house (e.g. text, integers, real numbers, etc.), and value is the default value of that column (e.g. 'one' if the type is text, '1' if the type is integer, '3.14159' if the type is real, etc.).

Create a one-way frequency table

Create a one-way frequency table as follows:

SELECT var1, COUNT(*) FROM datname GROUP BY var1;

This will then list the unique categories and counts for each category

Compute summary statistics of a variable or formula of variables

SELECT FUNCTION(var1) FROM datname;

where the following are functions:

AVG: average
COUNT: count
SUM: sum
MIN: minimum
MAX: maximum

Summary statistics of functions of variables

For example:

SELECT FUNCTION(var1 + var2) FROM datname;
```python
would apply the `AVG` or `SUM` or `MIN` to the sum of `var1` and `var2`. More complex functions (like square root) are not supported in SQLite, so something like
```python
SELECT AVG(SQRT(var1)) FROM datname;

will not work.

But

SELECT (var1-var2) AS tempvarname FROM datname;

will. AS acts as an alias.

Saving data while using SQL

If one wants to save a database in SQL, the file extension is .sqlite3 (but in principle you can use whatever you want). The way to save data is to issue the .dump command:

.output datname.sqlite3
.dump

The result will be a text file with SQL code in it that will recreate the table you dumped.

SQL Examples (from Chris Albon)

Taken from Chris Albon's archived GitHub repository on machine learning and artifical intelligence.

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

View Table

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	Santa Rosa	0
621	Betty Bob	None	F	Petaluma	1
162	Jaden Ado	49	M	None	0
901	Gordon Ado	32	F	Santa Rosa	0
512	Bill Byson	21	M	Santa Rosa	0
411	Bob Iton	None	M	San Francisco	0

Delete Column

-- Edit the table
ALTER TABLE criminals

-- Add a column called 'state' that contains text with the default value being 'CA'
ADD COLUMN state text DEFAULT 'CA'

View Table

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals

pid	name	age	sex	city	minor	state
412	James Smith	15	M	Santa Rosa	1	CA
234	Bill James	22	M	Santa Rosa	0	CA
632	Stacy Miller	23	F	Santa Rosa	0	CA
621	Betty Bob	None	F	Petaluma	1	CA
162	Jaden Ado	49	M	None	0	CA
901	Gordon Ado	32	F	Santa Rosa	0	CA
512	Bill Byson	21	M	Santa Rosa	0	CA
411	Bob Iton	None	M	San Francisco	0	CA

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'San Francisco', 0);

View Table

--  Select all
SELECT *

-- From the criminals table
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
901	Gordon Ado	32	F	San Francisco	0

Add New Row

-- Add into the table criminals
INSERT INTO criminals 

-- A new row with these values
VALUES (512, 'Bill Byson', 21, 'M', 'Petaluma', 0);

View Table Again

--  Select all
SELECT *

-- From the criminals table
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
901	Gordon Ado	32	F	San Francisco	0
512	Bill Byson	21	M	Petaluma	0

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

Alias Criminals Table A `C`, Then Select All Names From `C`

--  Select all names from the table 'c'
SELECT c.name

-- From the criminals table, now called c
FROM criminals AS c

name
James Smith
Bill James
Stacy Miller
Betty Bob
Jaden Ado
Gordon Ado
Bill Byson
Bob Iton

Create Data With 'pid' As An Auto-Generated Primary Key

-- Create a table of criminals with pid being a primary key integer that is auto-incremented
CREATE TABLE criminals (pid INTEGER PRIMARY KEY AUTOINCREMENT,
                        name, 
                        age, 
                        sex, 
                        city, 
                        minor);

-- Add a single row with a null value for pid
INSERT INTO criminals VALUES (NULL, 'James Smith', 15, 'M', 'Santa Rosa', 1);

View Table

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals

pid	name	age	sex	city	minor
1	James Smith	15	M	Santa Rosa	1

Added More Rows With NULL Values For pid

INSERT INTO criminals VALUES (NULL, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (NULL, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (NULL, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (NULL, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (NULL, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (NULL, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (NULL, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

View Table

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals

pid	name	age	sex	city	minor
1	James Smith	15	M	Santa Rosa	1
2	Bill James	22	M	Santa Rosa	0
3	Stacy Miller	23	F	Santa Rosa	0
4	Betty Bob	None	F	Petaluma	1
5	Jaden Ado	49	M	None	0
6	Gordon Ado	32	F	Santa Rosa	0
7	Bill Byson	21	M	Santa Rosa	0
8	Bob Iton	None	M	San Francisco	0

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, NULL, 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, NULL, 23, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Petaluma', 0);

Write Some SQL Code With Single And Multiline Comments

--  This is a single line of commenting
SELECT name, age

FROM criminals -- It can also be placed at the end of the line

/* This is multiple 
lines of comments so we can include more
details if we need to. */
WHERE name IS NOT NULL

name	age
James Smith	15
Gordon Ado	32
Bill Byson	21

Create Table

-- Create a table of criminals_1
CREATE TABLE criminals_1 (pid, name, age, sex, city, minor);
INSERT INTO criminals_1 VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals_1 VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals_1 VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals_1 VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals_1 VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals_1 VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals_1 VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals_1 VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

View Table

-- Select all
SELECT *

-- From the table 'criminals_1'
FROM criminals_1

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	Santa Rosa	0
621	Betty Bob	None	F	Petaluma	1
162	Jaden Ado	49	M	None	0
901	Gordon Ado	32	F	Santa Rosa	0
512	Bill Byson	21	M	Santa Rosa	0
411	Bob Iton	None	M	San Francisco	0

Create New Empty Table

-- Create a table called criminals_2
CREATE TABLE criminals_2 (pid, name, age, sex, city, minor);

Copy Contents Of First Table Into Empty Table

-- Insert into the empty table
INSERT INTO criminals_2

-- Everything
SELECT * 

-- From the first table
FROM criminals_1;

View Previously Empty Table

-- Select everything
SELECT *

-- From the previously empty table
FROM criminals_2

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	Santa Rosa	0
621	Betty Bob	None	F	Petaluma	1
162	Jaden Ado	49	M	None	0
901	Gordon Ado	32	F	Santa Rosa	0
512	Bill Byson	21	M	Santa Rosa	0
411	Bob Iton	None	M	San Francisco	0

Create A Table

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

Select Everything In That Table

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	Santa Rosa	0
621	Betty Bob	None	F	Petaluma	1
162	Jaden Ado	49	M	None	0
901	Gordon Ado	32	F	Santa Rosa	0
512	Bill Byson	21	M	Santa Rosa	0
411	Bob Iton	None	M	San Francisco	0

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

Create An Index Using The Column 'pid' As The Unique ID

-- Create a index called uid
CREATE INDEX uid

-- For the table 'criminals' and the column 'pid'
ON criminals (pid)

Note: Use 'CREATE UNIQUE INDEX' if you want the index to not contain any duplicates.

Create New Table With Constraints On What Data Can Be Inserted

-- Create a table of criminals
CREATE TABLE criminals 

(

-- With a prisoner ID (pid) that is a primary key and cannot be null
 pid    INT PRIMARY KEY     NOT NULL, 
 
-- With a name variable whose default value is John Doe
 name   TEXT                DEFAULT 'John Doe',
 
-- With an age variable that is an integer and has to be between 0 and 100
 age    INT                 CHECK(0 < age < 100)

);

Add Data To Table

INSERT INTO criminals VALUES (412, 'James Smith', 15);
INSERT INTO criminals VALUES (234, 'Bill James', 22);
INSERT INTO criminals VALUES (632, 'Bill Steve', 23);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL);

View Table

SELECT *

FROM criminals

pid	name	age
412	James Smith	15
234	Bill James	22
632	Bill Steve	23
621	Betty Bob	None
162	Jaden Ado	49
901	Gordon Ado	32
512	Bill Byson	21
411	Bob Iton	None

Get Current Date

-- Select the current date
SELECT date('now');

date('now')
2017-01-19

Get Current Date And Time

-- Select the unix time code '1200762133'
SELECT datetime('now', 'unixepoch');

datetime('now', 'unixepoch')
1970-01-29 10:42:53

Compute A UNIX timestamp into a date and time

-- Select the unix time code '1169229733'
SELECT datetime(1169229733, 'unixepoch');

datetime(1169229733, 'unixepoch')
2007-01-19 18:02:13

Compute A UNIX timestamp into a date and time and convert to the local timezone.

-- Select the unix time code '1171904533' and convert to the machine's local timezone
SELECT datetime(1171904533, 'unixepoch', 'localtime');

datetime(1171904533, 'unixepoch', 'localtime')
2007-02-19 10:02:13

Compute The Day Of The Week

-- Select the the day of this week (0 = Sunday, 4 = Thursday)
SELECT strftime('%w','now');

strftime('%w','now')
4

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

Delete A Table

-- Delete the table called 'criminals'
DROP TABLE criminals

View Table

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals

(sqlite3.OperationalError) no such table: criminals [SQL: "-- Select everything\nSELECT *\n\n-- From the table 'criminals'\nFROM criminals"]

Note: We get an error because the table doesn't exist anymore.

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

View The Table

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	Santa Rosa	0
621	Betty Bob	None	F	Petaluma	1
162	Jaden Ado	49	M	None	0
901	Gordon Ado	32	F	Santa Rosa	0
512	Bill Byson	21	M	Santa Rosa	0
411	Bob Iton	None	M	San Francisco	0

Delete A Table

-- Delete the contents of the table called 'criminals'
DELETE FROM criminals

View The Table

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

View Table

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	Santa Rosa	0
621	Betty Bob	None	F	Petaluma	1
162	Jaden Ado	49	M	None	0
901	Gordon Ado	32	F	Santa Rosa	0
512	Bill Byson	21	M	Santa Rosa	0
411	Bob Iton	None	M	San Francisco	0

Delete Column (Most Common)

-- Alter the table called 'criminals' ALTER TABLE criminals

-- From the table 'criminals' DROP COLUMN age

Delete Column (SQLite)

SQLite (the version of SQL used in this tutorial) does not allow you to drop a column. The workaround is to make a new table that contains only the columns you want to keep, then rename the new table to the original template's name.

-- Create a table called 'criminals_tamps' with the columns we want to not drop
CREATE TABLE criminals_temp(pid, name, sex);

-- Copy the data from the columns we want to keep to the new table
INSERT INTO criminals_temp SELECT pid, name, sex FROM criminals;

-- Delete the original table
DROP TABLE criminals;

-- Rename the new table to the original table's name
ALTER TABLE criminals_temp RENAME TO criminals;

View Table

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals

pid	name	sex
412	James Smith	M
234	Bill James	M
632	Stacy Miller	F
621	Betty Bob	F
162	Jaden Ado	M
901	Gordon Ado	F
512	Bill Byson	M
411	Bob Iton	M

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

View Table

--  Select all
SELECT *

-- From the criminals table
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	Santa Rosa	0
621	Betty Bob	None	F	Petaluma	1
162	Jaden Ado	49	M	None	0
901	Gordon Ado	32	F	Santa Rosa	0
512	Bill Byson	21	M	Santa Rosa	0
411	Bob Iton	None	M	San Francisco	0

Drop Row Based On A Conditional

-- Delete all rows
DELETE FROM criminals

-- if the age is less than 18
WHERE age < 18

View Table Again

--  Select all
SELECT *

-- From the criminals table
FROM criminals

pid	name	age	sex	city	minor
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	Santa Rosa	0
621	Betty Bob	None	F	Petaluma	1
162	Jaden Ado	49	M	None	0
901	Gordon Ado	32	F	Santa Rosa	0
512	Bill Byson	21	M	Santa Rosa	0
411	Bob Iton	None	M	San Francisco	0

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Petaluma', 0);

View Table

--  Select all
SELECT *

-- From the criminals table
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
901	Gordon Ado	32	F	San Francisco	0
512	Bill Byson	21	M	Petaluma	0

Update One Row

-- Update the criminals table
UPDATE criminals

-- To say city: 'Palo Alto'
SET City='Palo Alto'

-- If the prisoner ID number is 412
WHERE pid=412;

Update Multiple Rows Using A Conditional

-- Update the criminals table
UPDATE criminals

-- To say minor: 'No'
SET minor = 'No'

-- If age is greater than 12
WHERE age > 12;

View Table Again

--  Select all
SELECT *

-- From the criminals table
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Palo Alto	No
901	Gordon Ado	32	F	San Francisco	No
512	Bill Byson	21	M	Petaluma	No

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, NULL, 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, NULL, 23, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Petaluma', 0);

Select Name And Ages Only When The Name Is Known

--  Select name and average age,
SELECT name, age

--  from the table 'criminals',
FROM criminals

-- if age is not a null value
WHERE name IS NOT NULL

name	age
James Smith	15
Gordon Ado	32
Bill Byson	21

Create Two Tables, Criminals And Crimes

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

-- Create a table of crimes
CREATE TABLE crimes (cid, crime, city, pid_arrested, cash_stolen);
INSERT INTO crimes VALUES (1, 'fraud', 'Santa Rosa', 412, 40000);
INSERT INTO crimes VALUES (2, 'burglary', 'Petaluma', 234, 2000);
INSERT INTO crimes VALUES (3, 'burglary', 'Santa Rosa', 632, 2000);
INSERT INTO crimes VALUES (4, NULL, NULL, 621, 3500); 
INSERT INTO crimes VALUES (5, 'burglary', 'Santa Rosa', 162, 1000); 
INSERT INTO crimes VALUES (6, NULL, 'Petaluma', 901, 50000); 
INSERT INTO crimes VALUES (7, 'fraud', 'San Francisco', 412, 60000); 
INSERT INTO crimes VALUES (8, 'burglary', 'Santa Rosa', 512, 7000); 
INSERT INTO crimes VALUES (9, 'burglary', 'San Francisco', 411, 3000); 
INSERT INTO crimes VALUES (10, 'robbery', 'Santa Rosa', 632, 2500); 
INSERT INTO crimes VALUES (11, 'robbery', 'Santa Rosa', 512, 3000);

Inner Join

Returns all rows whose merge-on id appears in both tables.

-- Select everything
SELECT *

-- Left table
FROM criminals

-- Right table
INNER JOIN crimes

-- Merged on `pid` in the criminals table and `pid_arrested` in the crimes table
ON criminals.pid=crimes.pid_arrested;

pid	name	age	sex	city	minor	cid	crime	city_1	pid_arrested	cash_stolen
412	James Smith	15	M	Santa Rosa	1	1	fraud	Santa Rosa	412	40000
412	James Smith	15	M	Santa Rosa	1	7	fraud	San Francisco	412	60000
234	Bill James	22	M	Santa Rosa	0	2	burglary	Petaluma	234	2000
632	Stacy Miller	23	F	Santa Rosa	0	3	burglary	Santa Rosa	632	2000
632	Stacy Miller	23	F	Santa Rosa	0	10	robbery	Santa Rosa	632	2500
621	Betty Bob	None	F	Petaluma	1	4	None	None	621	3500
162	Jaden Ado	49	M	None	0	5	burglary	Santa Rosa	162	1000
901	Gordon Ado	32	F	Santa Rosa	0	6	None	Petaluma	901	50000
512	Bill Byson	21	M	Santa Rosa	0	8	burglary	Santa Rosa	512	7000
512	Bill Byson	21	M	Santa Rosa	0	11	robbery	Santa Rosa	512	3000
411	Bob Iton	None	M	San Francisco	0	9	burglary	San Francisco	411	3000

Left Join

Returns all rows from the left table but only the rows from the right left that match the left table.

-- Select everything
SELECT *

-- Left table
FROM criminals

-- Right table
LEFT JOIN crimes

-- Merged on `pid` in the criminals table and `pid_arrested` in the crimes table
ON criminals.pid=crimes.pid_arrested;

pid	name	age	sex	city	minor	cid	crime	city_1	pid_arrested	cash_stolen
412	James Smith	15	M	Santa Rosa	1	1	fraud	Santa Rosa	412	40000
412	James Smith	15	M	Santa Rosa	1	7	fraud	San Francisco	412	60000
234	Bill James	22	M	Santa Rosa	0	2	burglary	Petaluma	234	2000
632	Stacy Miller	23	F	Santa Rosa	0	3	burglary	Santa Rosa	632	2000
632	Stacy Miller	23	F	Santa Rosa	0	10	robbery	Santa Rosa	632	2500
621	Betty Bob	None	F	Petaluma	1	4	None	None	621	3500
162	Jaden Ado	49	M	None	0	5	burglary	Santa Rosa	162	1000
901	Gordon Ado	32	F	Santa Rosa	0	6	None	Petaluma	901	50000
512	Bill Byson	21	M	Santa Rosa	0	8	burglary	Santa Rosa	512	7000
512	Bill Byson	21	M	Santa Rosa	0	11	robbery	Santa Rosa	512	3000
411	Bob Iton	None	M	San Francisco	0	9	burglary	San Francisco	411	3000

Note: FULL OUTER and RIGHT JOIN are not shown here because they are not supported by the version of SQL (SQLite) used in this tutorial.

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Petaluma', 0);

View All Rows

--  Select all
SELECT *

-- From the criminals table
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
412	James Smith	15	M	Santa Rosa	1
412	James Smith	15	M	Santa Rosa	1
901	Gordon Ado	32	F	San Francisco	0
512	Bill Byson	21	M	Petaluma	0

View Rows Where Age Is Greater Than 20 And City Is San Francisco

--  Select all unique
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where age is greater than 20 and city is San Francisco
WHERE age > 20 AND city == 'San Francisco'

pid	name	age	sex	city	minor
901	Gordon Ado	32	F	San Francisco	0

View Rows Where Age Is Greater Than 20 or City Is San Francisco

--  Select all unique
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where age is greater than 20 and city is San Francisco
WHERE age > 20 OR city == 'San Francisco'

pid	name	age	sex	city	minor
901	Gordon Ado	32	F	San Francisco	0
512	Bill Byson	21	M	Petaluma	0

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

Select Based On The Result Of A Select

--  Select name and age,
SELECT name, age

--  from the table 'criminals',
FROM criminals

--  where age is greater than,
WHERE age > 
     --  select age,
    (SELECT age
     --  from criminals
     FROM criminals
     --  where the name is 'James Smith'
     WHERE name == 'James Smith')

name	age
Bill James	22
Stacy Miller	23
Jaden Ado	49
Gordon Ado	32
Bill Byson	21





    'Connected: None@None'



## Create Data


```python
/* Create A Table Of Criminals */
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

/* Create A Table Of Crimes */
CREATE TABLE crimes (cid, crime, city, pid_arrested, cash_stolen);
INSERT INTO crimes VALUES (1, 'fraud', 'Santa Rosa', 412, 40000);
INSERT INTO crimes VALUES (1, 'burglary', 'Petaluma', 234, 2000);
INSERT INTO crimes VALUES (1, 'burglary', 'Santa Rosa', 632, 2000);
INSERT INTO crimes VALUES (1, 'larcony', 'Petaluma', 621, 3500); 
INSERT INTO crimes VALUES (1, 'burglary', 'Santa Rosa', 162, 1000); 
INSERT INTO crimes VALUES (1, 'larcony', 'Petaluma', 901, 50000); 
INSERT INTO crimes VALUES (1, 'fraud', 'San Francisco', 412, 60000); 
INSERT INTO crimes VALUES (1, 'burglary', 'Santa Rosa', 512, 7000); 
INSERT INTO crimes VALUES (1, 'burglary', 'San Francisco', 411, 3000); 
INSERT INTO crimes VALUES (1, 'robbery', 'Santa Rosa', 632, 2500); 
INSERT INTO crimes VALUES (1, 'robbery', 'Santa Rosa', 512, 3000);

Done.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
Done.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.

View Both Tables

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals

Done.

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	Santa Rosa	0
621	Betty Bob	None	F	Petaluma	1
162	Jaden Ado	49	M	None	0
901	Gordon Ado	32	F	Santa Rosa	0
512	Bill Byson	21	M	Santa Rosa	0
411	Bob Iton	None	M	San Francisco	0

-- Select everything
SELECT *

-- From the table 'crimes'
FROM crimes

Done.

cid	crime	city	pid_arrested	cash_stolen
1	fraud	Santa Rosa	412	40000
1	burglary	Petaluma	234	2000
1	burglary	Santa Rosa	632	2000
1	larcony	Petaluma	621	3500
1	burglary	Santa Rosa	162	1000
1	larcony	Petaluma	901	50000
1	fraud	San Francisco	412	60000
1	burglary	Santa Rosa	512	7000
1	burglary	San Francisco	411	3000
1	robbery	Santa Rosa	632	2500
1	robbery	Santa Rosa	512	3000

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

View Table

--  Select all
SELECT *

-- From the criminals table
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	Santa Rosa	0
621	Betty Bob	None	F	Petaluma	1
162	Jaden Ado	49	M	None	0
901	Gordon Ado	32	F	Santa Rosa	0
512	Bill Byson	21	M	Santa Rosa	0
411	Bob Iton	None	M	San Francisco	0

Drop Row Based On A Conditional

--  Select all
SELECT *

-- From the criminals table
FROM criminals

-- Only return the first two rows
LIMIT 2;

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill James	22	M	Santa Rosa	0

Create Two Tables, Criminals And Crimes

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

-- Create a table of crimes
CREATE TABLE crimes (cid, crime, city, pid_arrested, cash_stolen);
INSERT INTO crimes VALUES (1, 'fraud', 'Santa Rosa', 412, 40000);
INSERT INTO crimes VALUES (2, 'burglary', 'Petaluma', 234, 2000);
INSERT INTO crimes VALUES (3, 'burglary', 'Santa Rosa', 632, 2000);
INSERT INTO crimes VALUES (4, NULL, NULL, 621, 3500); 
INSERT INTO crimes VALUES (5, 'burglary', 'Santa Rosa', 162, 1000); 
INSERT INTO crimes VALUES (6, NULL, 'Petaluma', 901, 50000); 
INSERT INTO crimes VALUES (7, 'fraud', 'San Francisco', 412, 60000); 
INSERT INTO crimes VALUES (8, 'burglary', 'Santa Rosa', 512, 7000); 
INSERT INTO crimes VALUES (9, 'burglary', 'San Francisco', 411, 3000); 
INSERT INTO crimes VALUES (10, 'robbery', 'Santa Rosa', 632, 2500); 
INSERT INTO crimes VALUES (11, 'robbery', 'Santa Rosa', 512, 3000);

View All Unique City Names From Both Tables

-- Select city name
SELECT city 

-- From criminals table
FROM criminals

-- Then combine with
UNION

-- Select city names
SELECT city 

-- From crimes table
FROM crimes;

city
None
Petaluma
San Francisco
Santa Rosa

View All City Names From Both Tables

-- Select city name
SELECT city 

-- From criminals table
FROM criminals

-- Then combine with
UNION ALL

-- Select city names
SELECT city 

-- From crimes table
FROM crimes;

city
Santa Rosa
Santa Rosa
Santa Rosa
Petaluma
None
Santa Rosa
Santa Rosa
San Francisco
Santa Rosa
Petaluma
Santa Rosa
None
Santa Rosa
Petaluma
San Francisco
Santa Rosa
San Francisco
Santa Rosa
Santa Rosa

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Petaluma', 0);

View All Rows

Notice that 'James Smith' appears three times

--  Select all
SELECT *

-- From the criminals table
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
412	James Smith	15	M	Santa Rosa	1
412	James Smith	15	M	Santa Rosa	1
901	Gordon Ado	32	F	San Francisco	0
512	Bill Byson	21	M	Petaluma	0

View Unique Rows

--  Select all unique
SELECT distinct *

-- From the criminals table
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
901	Gordon Ado	32	F	San Francisco	0
512	Bill Byson	21	M	Petaluma	0

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill Bayes', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Jack Killer', 23, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Petaluma', 0);

View All Rows

--  Select all
SELECT *

-- From the criminals table
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill Bayes	22	M	Santa Rosa	0
632	Jack Killer	23	F	San Francisco	0
901	Gordon Ado	32	F	San Francisco	0
512	Bill Byson	21	M	Petaluma	0

View Rows Where Age Is Greater Than 30

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where age is greater than 30
WHERE age > 30

pid	name	age	sex	city	minor
901	Gordon Ado	32	F	San Francisco	0

View Rows Where Age Is Greater Than Or Equal To 23

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where age is greater than 23
WHERE age >= 23

pid	name	age	sex	city	minor
632	Jack Killer	23	F	San Francisco	0
901	Gordon Ado	32	F	San Francisco	0

View Rows Where Age Is 23

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where age is greater than 23
WHERE age = 23

pid	name	age	sex	city	minor
632	Jack Killer	23	F	San Francisco	0

View Rows Where Age Is Not 23

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where age is greater than 23
WHERE age <> 23

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill Bayes	22	M	Santa Rosa	0
901	Gordon Ado	32	F	San Francisco	0
512	Bill Byson	21	M	Petaluma	0

View Rows Where Name Begins With 'J'

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where name starts with 'J'
WHERE name LIKE 'J%'

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
632	Jack Killer	23	F	San Francisco	0

View Rows Where Name Contains The String 'ames'

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where name contains the string 'ames'
WHERE name LIKE '%ames%'

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

View Table

--  Select all
SELECT *

-- From the criminals table
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	Santa Rosa	0
621	Betty Bob	None	F	Petaluma	1
162	Jaden Ado	49	M	None	0
901	Gordon Ado	32	F	Santa Rosa	0
512	Bill Byson	21	M	Santa Rosa	0
411	Bob Iton	None	M	San Francisco	0

Select Rows With Names Starting With `G`

--  Select all
SELECT *

-- From the criminals table
FROM criminals

-- If name starts with G
WHERE name LIKE 'G%'

pid	name	age	sex	city	minor
901	Gordon Ado	32	F	Santa Rosa	0

Select Rows With Names Ending With `o`

--  Select all
SELECT *

-- From the criminals table
FROM criminals

-- If name starts ends with o
WHERE name LIKE '%o'

pid	name	age	sex	city	minor
162	Jaden Ado	49	M	None	0
901	Gordon Ado	32	F	Santa Rosa	0

Select Rows With Names Starting With Any Character, Then `ordon`

--  Select all
SELECT *

-- From the criminals table
FROM criminals

-- If name starts with any character then continues with 'ordon'
WHERE name LIKE '_ordon%'

pid	name	age	sex	city	minor
901	Gordon Ado	32	F	Santa Rosa	0

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

Select Rows That Contain An Item In A List

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals

-- Where the city is any of these cities
WHERE city IN ('Santa Rosa', 'Petaluma');

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	Santa Rosa	0
621	Betty Bob	None	F	Petaluma	1
901	Gordon Ado	32	F	Santa Rosa	0
512	Bill Byson	21	M	Santa Rosa	0

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

Select Every Row Where Age Is Between Two Values

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals

-- Where 
WHERE age BETWEEN 12 AND 18

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Petaluma', 0);

Sort By Ascending Age And Then Alphabetically By Name

--  Select all unique
SELECT distinct *

-- From the criminals table
FROM criminals

-- Sort by ascending age
ORDER BY age ASC, name

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
512	Bill Byson	21	M	Petaluma	0
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	San Francisco	0
901	Gordon Ado	32	F	San Francisco	0

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Petaluma', 0);

View All Rows

--  Select all
SELECT *

-- From the criminals table
FROM criminals

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	San Francisco	0
901	Gordon Ado	32	F	San Francisco	0
512	Bill Byson	21	M	Petaluma	0

Sort By Ascending Age

--  Select all unique
SELECT distinct *

-- From the criminals table
FROM criminals

-- Sort by ascending age
ORDER BY age ASC

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
512	Bill Byson	21	M	Petaluma	0
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	San Francisco	0
901	Gordon Ado	32	F	San Francisco	0

Sort By Descending Age

--  Select all unique
SELECT distinct *

-- From the criminals table
FROM criminals

-- Sort by descending age
ORDER BY age DESC

pid	name	age	sex	city	minor
901	Gordon Ado	32	F	San Francisco	0
632	Stacy Miller	23	F	San Francisco	0
234	Bill James	22	M	Santa Rosa	0
512	Bill Byson	21	M	Petaluma	0
412	James Smith	15	M	Santa Rosa	1

Sort Alphabetically

--  Select all unique
SELECT distinct *

-- From the criminals table
FROM criminals

-- Sort by name
ORDER BY name

pid	name	age	sex	city	minor
512	Bill Byson	21	M	Petaluma	0
234	Bill James	22	M	Santa Rosa	0
901	Gordon Ado	32	F	San Francisco	0
412	James Smith	15	M	Santa Rosa	1
632	Stacy Miller	23	F	San Francisco	0

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

Create A View

-- Create a view called 'Santa Rosa'
CREATE VIEW [Santa Rosa] AS

-- That contains everything
SELECT *

-- From the table called 'criminals'
FROM criminals

-- If the city is 'Santa Rosa'
WHERE city = 'Santa Rosa'

View The View

(I know, I know, stupid title)

-- Select everything
SELECT * 

-- From the view called [Santa Rosa]
FROM [Santa Rosa]

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	Santa Rosa	0
901	Gordon Ado	32	F	Santa Rosa	0
512	Bill Byson	21	M	Santa Rosa	0

Create Data

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Petaluma', 0);

View Average Ages By City

--  Select name and average age,
SELECT city, avg(age)

--  from the table 'criminals',
FROM criminals

-- after grouping by city
GROUP BY city

city	avg(age)
Petaluma	21.0
San Francisco	27.5
Santa Rosa	18.5

View Max Age By City

--  Select name and average age,
SELECT city, max(age)

--  from the table 'criminals',
FROM criminals

-- after grouping by city
GROUP BY city

city	max(age)
Petaluma	21
San Francisco	32
Santa Rosa	22

View Count Of Criminals By City

--  Select name and average age,
SELECT city, count(name)

--  from the table 'criminals',
FROM criminals

-- after grouping by city
GROUP BY city

city	count(name)
Petaluma	1
San Francisco	2
Santa Rosa	2

View Total Age By City

--  Select name and average age,
SELECT city, total(age)

--  from the table 'criminals',
FROM criminals

-- after grouping by city
GROUP BY city

city	total(age)
Petaluma	21.0
San Francisco	55.0
Santa Rosa	37.0

Files

SQLoverview.md

Latest commit

History

SQLoverview.md

File metadata and controls

Guide to SQL

Opening SQL

Exiting out of SQL

Executing a SQL script

Reading data into SQL

Printing N observations of your database

Deleting observations (and subsetting statements)

Delete column from table

Add column to table

Create a one-way frequency table

Compute summary statistics of a variable or formula of variables

Summary statistics of functions of variables

Saving data while using SQL

SQL Examples (from Chris Albon)

Create Data

View Table

Delete Column

View Table

Create Data

View Table

Add New Row

View Table Again

Create Data

Alias Criminals Table A C, Then Select All Names From C

Create Data With 'pid' As An Auto-Generated Primary Key

View Table

Added More Rows With NULL Values For pid

View Table

Create Data

Write Some SQL Code With Single And Multiline Comments

Create Table

View Table

Create New Empty Table

Copy Contents Of First Table Into Empty Table

View Previously Empty Table

Create A Table

Select Everything In That Table

Create Data

Create An Index Using The Column 'pid' As The Unique ID

Create New Table With Constraints On What Data Can Be Inserted

Add Data To Table

View Table

Get Current Date

Get Current Date And Time

Compute A UNIX timestamp into a date and time

Compute A UNIX timestamp into a date and time and convert to the local timezone.

Compute The Day Of The Week

Create Data

Delete A Table

View Table

Create Data

View The Table

Delete A Table

View The Table

Create Data

View Table

Delete Column (Most Common)

Delete Column (SQLite)

View Table

Create Data

View Table

Drop Row Based On A Conditional

View Table Again

Create Data

View Table

Update One Row

Update Multiple Rows Using A Conditional

View Table Again

Create Data

Select Name And Ages Only When The Name Is Known

Create Two Tables, Criminals And Crimes

Inner Join

Left Join

Create Data

Alias Criminals Table A `C`, Then Select All Names From `C`

Select Rows With Names Starting With `G`

Select Rows With Names Ending With `o`

Select Rows With Names Starting With Any Character, Then `ordon`