SQL Flashcards
Database
A set of data stored in a computer and is usually structured into tables
Relational database
A type of database that uses a structure that allows us to identify and access data in relation to another piece of data in the database
Schema
The set of columns and data types for those columns that make up the table
Relational Database Management System
A program that allows you to create, update, and administer a relational database
Most use SQL as the language
Popular RDBMS
MySQL
PostgreSQL
OracleDB
SQL Server
SQLite
Each using slight variations of the core SQL language
Common data types
INTEGER (positive or negative whole number)
TEXT (text string)
DATE (YYYY-MM-DD)
REAL (decimal)
Statement
A text that the database recognizes as a valid command
Clauses / commands
Written in capital letters and perform a specific task
Parameter
A list of columns, data types, or values that are passed into a clause as an argument
SELECT
statement used to fetch data from a database
Every query will begin with SELECT
CREATE TABLE
creates a new table in a database. It allows you to specify the name of the table and the name of each column
The parameters must include each column name and data type
INSERT
used to insert new rows into a table
Must be structured as follows
INSERT INTO table name (columns to add data)
VALUES (the values to be added to the specific columns)
*
Wildcard used with SELECT that allows us to pull all columns from a table
ALTER TABLE
lets you add columns to a table in a database. Must be followed with ADD COLUMN
ALTER TABLE table name
ADD COLUMN column name data type
NULL
A special value in SQL that represents missing / unknown / blank data
UPDATE
edits a row in a table. Must be used with SET and WHERE
UPDATE table name
SET column name = new update
WHERE identifier column name = specific row
DELETE FROM
statement deletes one or more rows from a table. Must be used with WHERE
DELETE FROM table name
WHERE column name IS row identifier
Can be used with IS NULL
Constraints
Are added after specifying the data type when creating columns for a table
CREATE TABLE table name ( column name, data type, constraint)
Common constraints
PRIMARY KEY (same as unique but can only have one primary key column in a table)
UNQIUE (data entry must be unique, multiple columns can have unique constraints)
NOT NULL (data must be entered)
DEFAULT ‘text’ (if no entry provided insert a default)
Create table
CREATE TABLE ( )
Add a new row to table
INSERT INTO
VALUES
Queries data from a table
SELECT
FROM
Changes an existing table
ALTER TABLE
ADD
Edits a row in a table
UPDATE
SET
WHERE
Deletes rows from a table
DELETE FROM
WHERE
AS
Added with SELECT, it will return the column you want renamed to what you’ve defined
The columns are not being renamed in the table but rather appearing only in the result
DISTINCT
is used to return only unique values in the output, it will filter out all duplicate values in a specific column
SELECT DISTINCT
FROM
WHERE
can be used to restrict the results of our queries
SELECT
FROM
WHERE
Operators
Used to create a condition that can be evaluated (typically with WHERE)
= equal to
!= not equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to
LIKE
a special operator used with WHERE to search for a specific pattern in a column
SELECT
FROM
WHERE LIKE
Wildcard characters used with LIKE
_
%
BETWEEN
operator used with WHERE to filter the result within a certain range. It accepts two values that are either numbers, text, or dates.
must be used with AND
it will go up to the second value defined in the range
SELECT
FROM
WHERE BETWEEN AND
AND
Operator used with WHERE to make the result more specific to multiple conditions
SELECT
FROM
WHERE BETWEEN AND
AND
OR
Same use as AND however OR tests if any of the conditions are true. Whereas AND all conditions must be true
SELECT
FROM
WHERE
OR
ORDER BY
Sorts our results either alphabetically or numerically based upon the column type. Can also define ascending (ASC) or descending (DESC)
SELECT
FROM
ORDER BY ASC;
LIMIT
used to limit the max number of rows returned in a query
It always goes at the end of a query
SELECT
FROM
LIMIT
CASE
Allows us to create different outputs. It is how to do If-Then logic. It takes the place of an argument entered into a SELECT
It is used with WHEN and THEN to create the If Then logic
ELSE closes the logic
END closes the Case
Use AS to return the new output with a clean name
SELECT
CASE
WHEN THEN
WHEN THEN
ELSE
END AS
FROM
Aggregates
Calculations performed on multiple rows of a table
Count the number of rows
COUNT( )
SELECT COUNT
FROM
Sum the values in a column
SUM( )
Return the largest / smallest value
MAX( ) / MIN( )
Get the average of the values in a column
AVG( )
Round the values in the column
ROUND( )
SUM( )
Takes the name of a column as an argument and returns the sum of all values in that column
SELECT SUM
FROM
ROUND( )
Takes two arguments
- Column name
- Integer
It will round the values to the number of decimal places specified by the integer
GROUP BY
a clause used with aggregate functions in collaboration with SELECT to arrange data into groups. This can be used instead of multiple WHERE statements
It comes after WHERE but before ORDER BY or LIMIT
SELECT xxxx, AVG(yyyy)
FROM
GROUP BY xxxx
ORDER BY xxxx
Column references
We can use simple column reference numbers (1, 2, 3 etc) with GROUP BY and ORDER BY when we want to organize our result when our columns are aggregated
HAVING
performs a filter or limiting function similar to WHERE but on aggregated data
When we want to limit a query based upon values of individual rows - WHERE
When we want to limit results based on aggregate - HAVING
Always comes after GROUP BY but before ORDER BY and LIMIT
SELECT xxxx, yyyy, COUNT(zzzz)
FROM table
GROUP BY 1, 2
HAVING COUNT(zzzz) > 10
JOIN
combines two tables. When specifying the column to join on you need to do table then column
This is an inner join that will only keep the records that are matched in both tables
SELECT
FROM
JOIN
ON table.column = table.column
LEFT JOIN
same as JOIN but will keep all records from the left table and drop any records that don’t match in the right table
SELECT
FROM left
LEFT JOIN right
ON left.column = right.column
Primary keys
Uniquely identifies each row for a table
None of the values can be null
Each value must be unique
A table cannot have more that one primary key column
Foreign key
When the primary key of another table appears in a given table
CROSS JOIN
will combine all rows of one table with all rows of another table. Does not require ON
SELECT
FROM table1
CROSS JOIN table2
UNION
Stacks one dataset on top of the other
Tables must have the same number of columns and the same data types
SELECT
FROM table1
UNION
SELECT
FROM table2
WITH
allows us to combine two tables but one of the tables is the result of another calculation
WITH calculation table name AS (
SELECT …
…
…
)
SELECT *
FROM calculation table name
JOIN table
ON
