Missing Document Title
theme: Next, 1
SQL (Structured Query Language) is a descriptive computer language designed for updating, retrieving, and calculating data in table-based databases.
Mozilla Developer Network
- Random Access (don't have to rewrite or append)
- Can store multiple related data in one place
- Supports multiple users
Updates to the database allow for multiple changes to execute at once. Either all of them fail, or all of them succeed.
^ For instance, if our application is a banking application and we want to
withdraw $10 from Andy's account and add it to Barbara's account, we might
first write a statement to decrease Andy's balance by 10. Then a second
statement to increase Barbara's balance by 10. If these two statements aren't
guaranteed to succeed in pairs, we can decrease
Andy's balance without correspondingly increasing Barbara's. In an
system, either we would both decrease Andy's balance and increase Barbara's
balance - or - if an error occurs, their balances are as they were at the
Any rules the database enforces are applied when asking for changes.
^ An example is when creating a user in a system, we require the user to have
an email address and a password. If we attempt to create a row in the
table, but the
Isolation allows for multiple database requests to be handled concurrently (e.g., reading and writing to multiple tables/rows at the same time)
Durability ensures that once data is written to the database, the database does not lose the information in the case of a database, computer, or system crash.
Once a database says that row updates, a power loss to the system should not lose the updated data.
- SQL (relational) databases store and arrange data into tables.
- These tables consist of rows where each row has the same set of columns.
For an analogy, you can think of a database as an Excel spreadsheet, a table as "sheet" within that spreadsheet, rows as the rows of the sheet, and columns as named versions of the familiar "Column A", "Column B" style of spreadsheets.
- Tables are the containers for our data.
- Usually, a database has multiple tables, one for each "thing" we are storing.
For instance, let's imagine we are designing and building a system to manage books for a library. We could create a database named
Library and in that database, there would be one table, called
Books table, we would design columns representing the specific data about books we wish to track. Each row in the
Books table would represent a unique book in our collection.
- Columns are the part of the table that defines the structure (what we often call the
- This is where we define the attributes of the "thing" represented by the table.
- Every column has a data type that defines and restricts what type of data we can place into each column.
- In our
Bookstable, we will want to store specific details:
In a database, we have a few choices for the data type.
N represents the largest number of characters this column can store. If we supply less than
N characters, spaces will fill the remaining space. The appended spaces ensures the column is always
N characters long
N represents the largest number of characters the column can store. However, the width of the data is variable. If we supply less than
N characters, the column is not filled with spaces.
Allows for a variable number of characters but has a much larger limit than what a
varchar can support. In some cases many megabytes, or gigabytes of text.
In our case,
text make the most sense for our columns.
See the Postgres docs for more details.
Rows are where our data is stored.
Each row represents one instance of a "thing", in our case one "book".
Much like one
objectis an instance of a class (this is an important concept)
In our example, a row of data would contain 'The Cat in the Hat' (
Title), 'Dr. Suess' (
Author), and 'kids' (
All the commands are in the
Introduction to SQLlesson, so feel free to copy + paste.
First, we need to create a database:
We can run this command from any directory, it doesn't matter.
This creates a new, empty database.
The databases, tables, columns, rows, relationships, and data are stored and managed by the database.
We do not see where it keeps the data or how the database structures the data.
Eventually, the data won't even be on the same computer where our code runs!
To connect to that database and start running queries against it, use the command:
Like our command terminal/shell but now the commands go to the database instead of our operating system.
SQL databases use the
Language to both define the
schema (structure) of our database and as a way to create, read, update, and delete data within it.
We call the statements we ask a database to do for us a
query, even if the statement's purpose is to create tables, or delete rows.
Let's start by creating the table with only the
PrimaryAuthor, and the
YearPublished columns for our
CREATE TABLE "Books" ("Title" TEXT NOT NULL,"PrimaryAuthor" TEXT,"YearPublished" INT,"Id" SERIAL PRIMARY KEY);
SERIAL PRIMARY KEY
|BOOLEAN||Stores a |
|DATE||Stores a year, month, and day together. Use |
|TIMESTAMP||Stores a precise time, Use |
The structure of our tables is not set in stone.
Can be modified at a later date by using the
ALTER TABLE query.
ALTER TABLE "Books" ADD COLUMN "Genre" VARCHAR(15);
To create a new row in our database, we need to use
The format for
INSERT statements is similar to:
INSERT INTO "TableName" ("ColumnA", "ColumnB", "ColumnC")VALUES ('columnAValue', 'columnBValue', 'columnCValue');
- Table Name
- List of columns
- List of values in the same order
INSERT INTO Books (Title, PrimaryAuthor, YearPublished, Genre)VALUES ('Night of the Living Dummy', 'R. L. Stine', 1993, 'horror');INSERT INTO Books (Title, PrimaryAuthor, YearPublished, Genre)VALUES ('A Shocker on Shock Street', 'R. L. Stine', 1995, 'horror');INSERT INTO Books (Title, PrimaryAuthor, YearPublished, Genre)VALUES ('The Lost World', 'Michael Crichton', 1995, 'sci-fi');
SELECT statements allow us to query and return a new view of the data.
SELECT * FROM "Books";
This query will give us back all the columns (
*) from all the rows in the
Regardless if there are ten rows or ten million rows, this statement will return them all.
SELECT (some of the columns)
Often we do not want all the columns from the table. We can specify specific columns.
SELECT "Title", "PrimaryAuthor" FROM "Books";
While this will still return all the rows, we will only see the
PrimaryAuthor columns for all those rows.
SELECT "Title" FROM "Books";
books.Select(book => book.Title);
See the number of books:
SELECT COUNT(*)FROM "Books";
See the average, largest, and smallest year of publication.
SELECT AVG("YearPublished"), MAX("YearPublished"), MIN("YearPublished")FROM "Books";
We can use the
WHERE clause to help filter down our table to only see rows that satisfy the conditions supplied.
SELECT "Title", "PrimaryAuthor" FROM "Books" WHERE "Genre" = 'horror';
books.Where(book => book.Genre == "horror").Select(book => book.Title);
SELECT * FROM "Books";SELECT "Title", "PrimaryAuthor" FROM "Books";SELECT "Title", "PrimaryAuthor" FROM "Books" WHERE "Genre" = 'horror';SELECT "Title", "PrimaryAuthor" FROM "Books" WHERE "Genre" = 'fantasy' OR "Genre" = 'sci-fi';SELECT "Title", "PrimaryAuthor" FROM "Books" WHERE "Genre" = 'horror' ORDER BY "Title";SELECT "Title", "PrimaryAuthor" FROM "Books" WHERE "Title" LIKE 'The Lord of the Rings%' ORDER BY "Title";
Sometimes we want to use a different name for a column than the name in the table itself. SQL allows us to do this with the
We can use an alternate name for a column by using the
SELECT "Title" AS "BookTitle" FROM "Books";
We can also rename the table itself:
SELECT "Title" AS "BookTitle" FROM "Books" AS "BooksTable";
To change data in our database, we use an
UPDATE statement. The general structure of an
UPDATE "TableName"SET "ColumnA" = 'new value'WHERE "ColumnB" = 'some value';
The above update statement will update all rows that have
ColumnB = 'some value' and change
ColumnA to contain
The same syntax for
WHERE clauses from
SELECT apply here. We can use multiple conditions and combine them with
Word of warning. If the
WHEREclause is left off, it will update all rows.
This might cause a bad day
UPDATE "Books" SET "Genre" = 'children horror' WHERE "PrimaryAuthor" = 'R. L. Stine';
To remove one or many rows, we can use the
DELETE FROM "Books" WHERE "YearPublished" = 1995;
As with the
UPDATE statement, the
DELETE statement can use the same
WHERE syntax to filter rows to delete.
Word of warning. If the
WHEREclause is left off, it will delete all rows.
This might cause a bad day
Here are some useful postgres database references about SQL