First, what is SQL?
SQL, also known as Structured Query Language, is a programming language made to manage stored data in relational databases. SQL uses simple, declarative statements to run. These simple statements help maintain the integrity of databases and help keep the data secure and accurate.
For example, if we wanted to get all the data from the cats database, we would run something like this:
This would return a table from a relational database.
What is a relational database?
A relational database is a database that organizes information into one or more tables. A table is a collection of data sorted into columns and rows. The table here is cats. A column is a set of data values, so cats would probably have a column id, name, and age. A row is a single record in the table.
All data stored in a relational database has a specific data type. Here is a list of the common data types:
- INTEGER: positive or negative whole number
- DATE: date is formatted as yyyy-mm-dd
- REAL: a decimal value
- TEXT: a text string
What is a statement? Clauses? Parameters?
A statement is text the database recognizes as a valid command. It's important to understand they will always end in a semicolon.
Let’s break down the statement below:
- CREATE TABLE is a clause. Clauses are written in all caps and can also be called commands.
- table_name is the name of the table that the clause is applied to.
- (column_1 data_type, etc) is a parameter. A parameter is a list of columns, values, or data types that are passed to a command as arguments.
Now that we have the basics out of the way let’s start learning about different clauses.
CREATE
Create statements allows us to create new tables. Let’s go over the example below:
- CREATE TABLE is telling SQL we want to make a new table.
- cats is the name of the table.
- id INTEGER and name TEXT are the parameters. id is the first column that store data type, INTEGER and name is the second column and stores data type, TEXT.
INSERT
The insert statement inserts a new row into the table. Let’s break down the example below:
- INSERT INTO is the clause that adds rows.
- cats is the table name.
- (id, name) is a parameter identifying the columns the data will be inserted into.
- VALUES is a clause that indicated the data being uses. “1” will be the id and “Kimchi cat” will be the name.
SELECT
The select statement is used to get data from the database. Let’s look at an example:
- Select is a clause that shows the statement is a query. You are going to use SELECT every time you query from a database.
- name specifies the column we are grabbing data from. You can also use * in place if name if you wanted to grab all the data from that table.
- FROM cats specifies the table we are querying the data from.
ALTER
The ALTER TABLE statement adds a new column to the table. Take a look at the example below:
- ALTER TABLE is a clause that lets you make specified changes.
- cats is the name of the table that is being changed.
- ADD_COLUMN is a clause that lets you add a new column to the table. As you can probably guess, the new columns name is age and it stores the data type integer.
- It should be known that the rows that existed before will have NULL as the value for age.
UPDATE
The UPDATE statement edits a row in a table. You would use this if you wanted to update existing data. For example:
- UPDATE is a clause that edits a row in the table.
- cats is the table that is being altered.
- SET is a clause that indicated the column to edit. In this case, the column is age.
- WHERE is a clause that indicated which rows to update. We are only updating the row with the id, 1.
DELETE
The DELETE FROM statement deletes one or more rows from a table. Let's take a look at this clause in action:
- DELETE FROM is the clause that lets you delete rows from tables.
- cats is the name of the table we are deleting rows from.
- WHERE is a clause that lets you select which rows you want to delete. In this case, we want to delete the rows that don't have a value for age or in other words, where the age column IS NULL.
CONSTRAINTS
Constraints add information about how a column can be used and are invoked after specifying the data type for a column. If you arent getting it, don’t worry. We are going to break about the example below:
- PRIMARY KEY columns can be used to identify a row. If you attempt to insert a row with an identical value to a row already in the table, it will result in a constraint violation.
- UNIQUE columns have different values for every row. Kinda like PRIMARY KEY except a table can have multiple UNIQUE columns.
- NOT NULL columns must have a value. If it doesn't, the new row will not be inserted.
- DEFAULT columns take an additional argument that will be the default value for an inserted row if the inserted row does not specify a value.
That wraps up our SQL manipulation crash course! Thanks for reading!
