Title: Select An Entire Table
Slug: select_entire_table
Summary: Select an entire table in SQL.
Date: 2016-05-01 12:00
Category: SQL
Tags: Basics
Authors: Chris Albon
Note: This tutorial was written using Catherine Devlin's SQL in Jupyter Notebooks library. If you have not using a Jupyter Notebook, you can ignore the two lines of code below and any line containing %%sql
. Furthermore, this tutorial uses SQLite's flavor of SQL, your version might have some differences in syntax.
In [1]:
# Ignore
%load_ext sql
%sql sqlite://
Out[1]:
In [2]:
%%sql
/* 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);
Out[2]:
In [3]:
%%sql
-- Select everything
SELECT *
-- From the table 'criminals'
FROM criminals
Out[3]:
In [4]:
%%sql
-- Select everything
SELECT *
-- From the table 'crimes'
FROM crimes
Out[4]: