SQL Notes
Our SQLite database tables must be indexed by a ____________________.
number
What does .headers on do?
output the name of each column
Use the _________________ modifier to retrieve a specific data set between two ranges
BETWEEN
.width auto
adjusts and normalizes column width
The ____________ function returns the number of rows that meet a certain condition.
count
The _______________ function returns the number of rows that meet a certain condition.
count
The term "query" refers to any SQL statement that retrieves __________ from your database.
data
We define specific columns in our table, and then we store any number of what we refer to as '__________' as rows in our database.
records
We can look at the structure, or "_______________", of our database (i.e. the tables and their columns + column data types) with the ._______________ command.
schema
All SQL statements that you write in your terminal, inside the sqlite prompt, sqlite3>, must be terminated with a ____________.
semi-colon ;
How do you create a pets_database?
sqlite3 pets_database.db
How do you delete Lil' Bub from our cats table (sorry Lil' Bub)?
sqlite> DELETE FROM cats WHERE id = 2;
If we really didn't care about a specific column and we just wanted the total number of rows in our database we can call ________________. * means everything.
COUNT(*)
__________________ will count the rows where at least one column has data in it.
COUNT(*)
If you have duplicate data (for example, two cats with the same name) and you only want to select unique values, you can use the _________________ keyword.
DISTINCT
To delete a table from a database use the _______________ statement?
DROP TABLE
Like its name suggests, this command groups your results by a given column.
GROUP BY
Use the ___________________ function to group your results according to the values in a given column
GROUP BY
_______________ is a great function for aggregating results into different segments — you can even use it on multiple columns!
GROUP BY
Let's use the AS keyword to rename the column. This is called "aliasing the return value".
SELECT AVG(net_worth) AS average_net_worth FROM cats;
The count function returns the number of rows that meet a certain condition. Here's how it works:
SELECT COUNT(column_name) FROM table_name;
Data storage isn't very useful if we can't ________________, _________________, and ___________________ that data.
manipulate, view, and analyze
When we have multiple words in a column name, we link them together using _____________ rather than spaces. Aka called snake_case
underscores
We can use the _____________ keyword to select data based on specific conditions.
WHERE
To get a complete list of commands, you can type ________ into the sqlite prompt.
.help
To get a list of SQL commands type ___________________.
.help
You can exit out of the sqlite prompt with the ______________ command.
.quit
The only command that doesn't require, and in fact doesn't even work with, a ; is the ______________.
.quit command
We can look at the structure, or "schema", of our database (i.e. the tables and their columns + column data types) with the ______________ command.
.schema
To write SQL in our text editor and execute that SQL against a specific database, we'll create files in our text editor that have the _________________ extension.
.sql
If we don't care about a specific column and we just wanted the total number of rows in our database we can call ______________.
COUNT(*)
Use comparison operators, like _______________, to select specific data
< or >
_______________ is called "aliasing the return value".
AS
__________________ functions perform a calculation on specified values, queried from a database table.
Aggregate
When creating database table column names we need to include the type of data they will be storing. _________ means we'll store a number.
INTEGER
Use the ____________ function to count the number of records that meet a certain condition
COUNT
___________________ will count the number of records that meet certain condition.
COUNT
The ___________ function returns the number of rows that meet a certain condition.
COUNT( )
We can use the __________________ function to calculate the total number of rows in a table that are not NULL.
COUNT( )
Now that we have a pets_database, how do you create a cats table along with id, name, age, breed, and owner_id columns.
CREATE TABLE cats ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER, breed TEXT, owner_id INTEGER );
Once you create a database (which you can do with the sqlite3 database_name.db command), we create a table using the following statement:
CREATE TABLE table_name;
We create a table using the following statement:
CREATE TABLE table_name;
To delete table rows, we use the _______________ keyword.
DELETE
Use ___________ statements to delete data from a database table
DELETE
We specify the names of the columns we want to SELECT and then tell SQL the table we want to select them _____________.
FROM
Use the ______________ command to insert data (i.e. rows) into a database table.
INSERT INTO
How do you add another cat named Maru who is 3 years old and is a 'Scottish Fold' into a table?
INSERT INTO cats (name, age, breed) VALUES ('Maru', 3, 'Scottish Fold');
Let's insert our new cat into the database. Our abandoned kitty has a breed, but no name or age as of yet:____________________________.
INSERT INTO cats (name, age, breed) VALUES (NULL, NULL, "Tabby");
Typing: If it's a number and contains no letter or special characters or decimal points then we should store it as an integer.
Integer
The ________ and ________ aggregator functions return the minimum and maximum values from a specified column respectively.
MIN( ), MAX( )
The __________ and __________ aggregator functions return the minimum and maximum values from a specified column respectively.
MIN( ), MAX( )
The _____________ and _______________ aggregator functions return the minimum and maximum values from a specified column respectively.
MIN() , MAX()
Let's say the administrator of our Pets Database has found a new cat. This kitty doesn't have a name yet, but should be added to our database right away. We can add data with missing values using the _____________ keyword.
NULL
Use the __________ data type keyword to insert new records into a table
NULL
We can use the COUNT() function to calculate the total number of rows in a table that are not _____________.
NULL
___________ means empty.
NULL
_______________ our table rows makes our data that much easier to access, update, and organize.
Numbering
This modifier allows us to order the table rows returned by a certain SELECT statement.
ORDER BY
SQLite comes with a data type designation called "________________________".
Primary Key
__________________ are unique and auto-incrementing, meaning they start at 1 and each new row automatically gets assigned the next numeric value.
Primary keys
______________ is anything that's a plain old decimal like 1.3 or 2.25.
Real
________________________ like SQLite store data in a structure we refer to as a table.
Relational Databases
All of our cats have a name so we can call COUNT on the name column like this:
SELECT COUNT(name) FROM cats;
To select unique name values from the cats table use ____________________.
SELECT DISTINCT name FROM cats;
The sum, SUM(), function returns the sum of all of the values in a particular column. Here's how it works:
SELECT SUM(column_name) FROM table_name;
A basic SELECT statement works like this: _______________________________.
SELECT [names of columns we are going to select] FROM [table we are selecting from];
Here's an boilerplate SELECT statement using BETWEEN:______________________.
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
We want to select all the rows in our table, and we want to return the data stored in any and all columns (id, name, age, breed) in those rows. To do this, we could pass the name of each column explicitly:
SELECT id, name, age, breed FROM cats;
________________________ are SQL statements that retrieve minimum and maximum values from a column, sum values in a column, get the average of a column's values, or count a number of records that meet certain conditions.
SQL aggregate functions
The __________ function returns the sum of all of the values in a particular column.
SUM()
The __________, function returns the sum of all of the values in a particular column.
SUM()
The ________________ statement uses a WHERE clause to grab the row you want to update.
UPDATE
To update, or change, data in our table rows. We do this with the ________________ keyword.
UPDATE
Use ___________ statements to update data within a database table
UPDATE
Primary Key columns are _______________________.
auto-incrementing
Primary keys are unique and _____________________, meaning they start at 1 and each new row automatically gets assigned the next numeric value.
auto-incrementing
The schema reflects the __________________ of the database, which is reflected as the CREATE statement necessary to create that structure.
current structure
If a user signs up for our app and we proceed to lose all their information immediately, how will we know if an existing user is signing back into our app? We need a way to take our Ruby objects, store them in a _________________________________________.
database and retrieve them at the appropriate time
When we create _______________, we need to specify some column names, along with the type of data we are planning to store in each column.
database tables
We define columns in a CREATE statement by including a name and a _____________ to let SQLite know the kind of data we will be storing there.
datatype
Our SQLite database tables _______________________________.
must be indexed by a number
Typing gives us the ability to perform all kinds of operations with _____________________.
predictable results
The term "___________" refers to any SQL statement that retrieves data from your database.
query
A ___________ is just information referring to one specific entity.
record
Typing not only informs our database of the kind of data we plan to store in a column but it also ______________ it.
restricts
To find out if you already have SQLite installed. Open up the terminal and type in: ____________________. If you get back: /usr/bin/sqlite3, you have a working version.
which sqlite3
A faster way to get data from every column in our table is to use a special selector, known commonly as the '_________', * selector.
wildcard
To select all of the rows from a cats table input:
SELECT * FROM cats;
The average, AVG(), function returns the average value of a column. Here's how it works:
SELECT AVG(column_name) FROM table_name;
SQL (______________________________) is a language for managing data in a database.
Structured Query Language
As a full-stack web developer, you'll frequently be working with databases to_______________________ with your applications.
manage the data associated
SQLite will store decimals up to ____ characters long.
15
You delete a table from a database with the __________________ statement.
DROP TABLE
The schema reflects the current structure of the database, which is reflected as the ______________ statement necessary to create that structure.
CREATE
.width
NUM1, NUM2 # customize column width
Use the ___________________ modifier to order tables by specific SELECT statements.
ORDER BY
Use the _________ Clause to select data from specific table rows
WHERE
Aggregate functions perform a ________________ on specified values, queried from a database table.
calculation
Every table we create, regardless of the other column names and data types, should be defined with an ______________________ column, including data type and primary key designation.
id INTEGER PRIMARY KEY
Let's say that, after creating a database and creating a table to live inside that database, we decide we want to add or remove a column. We can do so with the __________________ statement.
ALTER TABLE
Let's say that, after creating a database and creating a table to live inside that database, we decide we want to add or remove a column. We can do so with the ___________________ statement.
ALTER TABLE
To add a new "breed" column to our cats table type: _______________.
ALTER TABLE cats ADD COLUMN breed TEXT;
Let's say we want to add a new column, breed, to our cats table. sqlite> ____________ cats ____________ breed ______________;
ALTER TABLE, ADD COLUMN, TEXT
Use the _________ and __________ modifier to sort queries in ascending and descending orders
ASC, DESC
True or False: If you tried, for example, to SUM all of the cats in the above table, SQLite would actually attempt to convert, or cast, their type to something it can SUM. It would try to convert anything it can to an INTEGER and ignore alpha characters.
True
Create a table called "Test Table".
create table test_table(id);
Since we created the cats table with an "id" column whose type is INTEGER PRIMARY KEY, we _____________ have to specify the id column values when we insert data.
don't
What does .mode column do?
now we are in column mode, enabling us to run the next two .width commands
The practice of explicitly declaring a type is known as "_______________."
typing
To list all the tables in the database we'll use the __________________ command. Type it into the sqlite prompt and hit enter.
.tables
The _____________, function returns the average value of a column. Here's how it works:
AVG()
The average, AVG(), function returns the average value of a column. Here's how it works:
AVG()
The average, ______________, function returns the average value of a column.
AVG()
__________ is generally used for holding binary data. For now, we will not use it.
BLOB
As long as you have defined an id column with a data type of _____________________________, a newly inserted row's id column will be automatically given the correct value.
INTEGER PRIMARY KEY
If we want to select extremes from a database table--for example, the employee with the highest paycheck or the patient with the most recent appointment--we can use ORDER BY in conjunction with _________________.
LIMIT
Use the _________________ modifier to determine the number of records to retrieve from a dataset
LIMIT
______________ is used to determine the number of records you want to return from a dataset.
LIMIT
SELECT * FROM cats ORDER BY age DESC returns all of the cats in order from oldest to youngest. Setting a LIMIT of 1 returns ___________________, i.e. oldest, cat on the list.
just the first
Use _______________ statements to select data from a database table
SELECT
We use _____________ to retrieve database data, or rows.
SELECT
To select only rows representing data meeting certain conditions (specifically a cat named Maru):
SELECT * FROM cats WHERE name = "Maru";
We can even select the mysterious, nameless kitty with the following query:
SELECT * FROM cats WHERE name IS NULL;
The * selector means: "Give me all the data from all the columns for all of the cats" Using the wildcard, we can SELECT all the data from all of the columns in the cats table like this:
SELECT * FROM cats;
To select just certain columns from a table, such as names, use the following: ____________________.
SELECT name FROM cats;
The sum, ___________, function returns the sum of all of the values in a particular column.
SUM()
When creating database table column names we need to include the type of data they will be storing. _________ means we'll be storing plain old text.
TEXT
SQLite is a good starting point to learn about datatypes because it only has four basic categories of data types; they are:
TEXT INTEGER REAL BLOB
__________ is any alphanumeric characters which we want to represent as plain text.
Text
When using ORDER BY, the default is to order in ___________________ order.
ascending
Before we're able to store any actual data in a table, we'll need to define the_____________ in the table as well as the specific type of__________ each column will store.
columns, data
We can execute .sql files against our database in the ______________________.
command line
ALWAYS use ________________ letters when referring to columns in our database.
lowercase
We will always use _____________ letters when referring to columns in our database.
lowercase
SQL is a programming language like any other, so we can write SQL in our ____________ and execute it.
text editor
When we have multiple words in a column name, we link them together using ____________ rather than spaces. We call this convention "______________".
underscores, snake_case