SQL
SQL - Structured Query Language
Allows users to query, manipulate, and transform data from a relational database.
Most commonly, a data model is designed first, then data is deconstructed by the process of "normalization". This aims to turn your data in a basic form like into separate tables. E.g. one table for customers and one for orders
RDBMS
SQL is a language which has many Relational Database Management Systems (RDBMS) that all support the common SQL language standard but differ in the additional features and storage types it supports:
SQLite, MySQL, Postgres, Oracle, Microsoft (MS) SQL Server
Table - collection of related data entries; it consists of columns and rows
fixed # of labelled columns (a.k.a. attributes/properties/fields)
any # of rows of data (a.k.a. record) (individual entries that exist on a table)
Each table is identified by a name (ie. 'Cars')
SQL Statements
SQL keywords are NOT case sensitive:
select
===SELECT
Each statement ends in a ; (like Java)
SELECT
selects data from db
UPDATE
updates data in db
DELETE
deletes data from db
INSERT INTO
inserts new data into db
CREATE DATABASE
creates new db
ALTER DATABASE
modifies db
CREATE TABLE
creates new table
ALTER TABLE
modifies table
DROP TABLE
deletes table
CREATE INDEX
creates an index (search key)
DROP INDEX
deletes an index
> SELECT
colloquially refered to as queries, which in itself is just a statement which declares what data we're looking for, where to find it in the db.
> SELECT DISTINCT
gets distinct values in specified column(s) of a table
> WHERE
used to filter rows that are retrieved from table
can be paired with select / update / delete
The is built using operators (ex. follows above):
=
equal ('abc' | 123)
where Company='BMW'
>
greater than
where Price>50000
<
less than
where Price<50000
>=
greater than or = to
where Price>=50000
<=
less than or = to
where Price<=50000
<>
not equal ('abc' | 123)
where Color<>'Blue'
BETWEEN
between a certain range
where Price BETWEEN 10000 AND 20000
LIKE
search for a pattern
where Company LIKE 's%'
(starts with s)
%
match sequence of chars (only w/ LIKE/NOT LIKE)
where col_name LIKE '%AT%'
matches = ("AT", "ATTIC", "CAT", "BATS", ... )
-
used anywhere in a str to match a single char
where col_name LIKE 'AN_'
matches=("AND", but not "AN")
IN
specify multiple possible values for col
where Company IN ('BMW', 'Tesla')
note: text columns must use
'single-quotes'
and numeric columns should not.
You can chain multiple conditions in a WHERE clause using logical operators:
AND
OR
NOT
Now, we can construct more complex clauses:
> ORDER BY
sorts the result-rows in ASC (default) or DESC order
You can sort multiple columns too:
clauses LIMIT and OFFSET are commonly used with the ORDER BY clause.
Multi-Table Queries with JOINs
Up to now, we've been working with a single table, but entity data in the real world is often broken down into pieces and stored across multiple orthogonal tables using a process known as normalization
Database Normalization
every entity is organized into its own table
process of organizing data in a database
This includes creating tables and establishing relationships between those tables
Tables that share info about a single entity need to have a primary key that identifies that entity uniquely across the database; this is usually an auto-incrementing integer (but could be string/hash/...)
$ JOINs
used to combine rows from 2 or more tables, based on a related column b/n them.
> (INNER) JOIN
Returns records that have matching values in both tables
matches rows from the first table and the second table which have the same key (as defined by the ON constraint) to create a result row with the combined columns from both tables.
You can write either
INNER JOIN
orJOIN
> LEFT (OUTER) JOIN
Returns all records from the left table, and the matched records from the right table
The result is NULL in the consecutive tables when there is no match
> RIGHT (OUTER) JOIN
Returns all records from the right table, and the matched records from the left table
The result is NULL in the consecutive tables when there is no match
> FULL (OUTER) JOIN
simply means that rows from both tables are kept, regardless of whether a matching row exists in the other table
NULLs
It's always good to reduce the possibility of
NULL
values in databases bc they require special attention when constructing queries, constraints and when processing the results.Alternatives to NULL values include:
0 (for numerical data)
'' (for text data)
But if your database needs to store incomplete data, then
NULL
values can be appropriate if the default values will skew later analysis
Queries with expressions
you can also use expressions to write more complex logic on column values in a query.
The use of expressions save time but can also make the query harder to read; so it's recommended that you use a descriptive alias AS
keyword adfter the SELECT part of the query.
Queries with aggregates
Aggregate Function - SQL performs a calculation on multiple values and returns a single value
SQL provides many aggregate functions:
![Screen Shot 2022-07-02 at 4.54.32 PM](/Users/dev/Library/Application Support/typora-user-images/Screen Shot 2022-07-02 at 4.54.32 PM.png)
GROUPED AGGR. FUNCTIONS
In addition to aggregating across all the rows, you can instead apply the aggregate functions to individual groups of data within that group
This would then create as many results as there are unique groups defined as by the
GROUP BY
clause, which groups rows that have the same values into summary rows.
Note:
if you want to add a WHERE clause after a GROUP BY clause which is already preceded by a WHERE clause, use the HAVING clause (treat exactly like WHERE):
Order of execution of a Query
(SQL BEDMAS)
FROM
,JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT / OFFSET
Inserting Rows
SCHEMAS - describes the structure of each table, and the datatypes that each column of the table can contain. (think: mongoose schema)
> INSERT INTO
Used to insert new rows in a table. Can be written 2 ways:
Specify both the column names and the values to be inserted
If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table.
Note: you don't need to insert the ID field as it's an auto-incrementing field and will be generated automatically when a new row is inserted into the table.
Updating Rows
> UPDATE (+ SET)
used to modify the existing records in a table
Similar to the
INSERT
statement, you have to specify exactly which table, columns, and rows to update.the data you are updating has to follow the schema of those columns.
Deleting Rows
> DELETE
used to delete existing records in a table.
The
WHERE
clause specifies which record(s) should be deleted.If you omit the
WHERE
clause, all records in the table will be deleted.
Creating Tables
> CREATE TABLE
used to create a new table in a db
add a IF NOT EXISTS clause to avoid SQL errors.
Create Table Using Another Table
You can also create a copy of an existing table using
CREATE TABLE
The new table gets the same column definitions. All columns or specific columns can be selected.
If you create a new table using an existing table, the new table will be filled with the existing values from the old table
Altering Tables
> ALTER TABLE
used to add, delete, or modify columns in an existing table.
also used to add and drop various constraints on an existing table.
1. Adding Columns - ADD
required: column name and datatype
optional: table constraints and default values.
2. Removing Columns - DROP COLUMN
Dropping columns is as easy as specifying the column to drop, however, some databases (ie. SQLite) don't support this feature.
Instead, you may have to create a new table and migrate the data over
3. Renaming Columns - RENAME TO
4. Alter Column Datatype - (varies from rdbms to rdbms)
Dropping Tables
> DROP TABLE
used to remove an entire table including all of its data
differs from the DELETE statement in that DROP TABLE also removes the table schema from the db entirely
use with IF EXISTS to avoid errors
> UNION
used to combine the result-set of two or more
SELECT
statementsEvery
SELECT
withinUNION
must have the same # of columns of the same data types and in the same order.The
UNION
operator selects only distinct values by default. To allow duplicate values, useUNION ALL
:
> EXISTS
used to test for the existence of any record in a subquery
> SELECT INTO
copies all (or some) columns from one table into a new table:
> CASE ... END
The
CASE
statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement).Once a condition is true, it will stop reading and return the result.
If no conditions are true, it returns the value in the
ELSE
clause
> CREATE DATABASE
used to create a new SQL db
> DROP DATABASE
used to drop an existing SQL database (use w/ IF EXISTS for safety)
Constraints
SQL constraints are used to specify rules for the data in a table.
They're used to limit the type of data that can go into a table. (for accuracy and reliability reasons).
If there is any violation between the constraint and the data action, the action is aborted.
Constraints can be applied to a column or an entire table.
The following constraints are commonly used:
NOT NULL
ensures that column can't have NULL value
UNIQUE
ensures that all values in a column are different
PRIMARY KEY
combo of NOT NULL + UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY
prevents actions that would destroy links between tables
CHECK
ensures that values in a column satisfy a specific condition
DEFAULT
sets a default value for a column
CREATE INDEX
create and retrieve data from the database very quickly
Single line commentz: -- here
-- here
Multi line commentz: /* here */
/* here */
Last updated