Foundations Flashcards
What is SQL?
a programming language designed to manage data stored in relational databases
What is a relational database?
a type of database that stores and provides access to data points that are related to one another
What is a table?
a collection of data organized into rows and columns
What is a column?
a set of data values of a particular type
What is a row?
a single record in a table
What are common data types?
integer, text, date, real
What is an integer data type?
a positive or negative whole number
What is a text data type?
a text string
What is a date data type?
the date formatted as YYYY-MM-DD
What is a real data type?
a decimal value
What is a statement?
text that the database recognizes as a valid command, always ends in a semicolon (;)
What does CREATE TABLE do?
a clause also known as a command; performs specific tasks in SQL. conventionally written in capital letters.
What is the point of the tablename in a statement?
refers to the name of the table that the command is applied to
What is a parameter?
a list of columns, data types, or values that are passed to a clause as an argument
How do we write statements?
they can be written all on one line, or split up across multiple lines if it makes it easier to read
What does CREATE do?
allows us to create a new table in the database
What is an example of creating a table?
CREATE TABLE celebs (
id INTEGER,
name TEXT,
age INTEGER,
);
What does INSERT do?
it inserts a new row into a table
When do we use INSERT?
when we want to add new records
What is an example of an INSERT?
INSERT INTO celebs (id, name, age)
VALUES (1, ‘Justin Beiber’, 29);
What does INSERT INTO do?
a clause that adds the specified row or rows
What does SELECT do?
used to fetch data from a database
What does SELECT return?
a new table called the result set
What is an example of a SELECT statement?
SELECT name FROM celebs;
What is the use of the *?
a special wildcard character that we can use to refer to “all”
What does ALTER TABLE do?
adds a new column to a table
What is an example of an ALTER TABLE?
ALTER TABLE celebs
ADD COLUMN twitter_handle TEXT;
What does NULL mean?
a special value in SQL that represents missing or unknown data
What does CTE stand for?
Common Table Expression
What is a CTE?
essentially a named subquery, it functions as a virtual table that only its main query can access
Why do we use CTEs?
it helps save space (and money) in our database because we are only allowed a certain amount of helper tables
What does UPDATE do?
edits a row in a table
What is an example of an UPDATE statement?
UPDATE celebs
SET twitter_handle = ‘@taylorswift13’
WHERE id=4;
What does DELETE do?
(FROM) deletes one or more rows from a table
What is an example of a DELETE statement?
DELETE FROM celebs
WHERE twitter_handle IS NULL;
What does IS NULL represent?
a condition in SQL that returns true when the value is NULL and false otherwise
What are constraints?
add information about how a column can be used, are invoked after specifying the data type for a column
What is a primary key?
columns can be used to uniquely identify the row
What happens when we attempt to insert a row with an identical value to a row already in a table?
it will result in a constraint violation
What does UNIQUE represent?
columns have a different value for every row (tables can have many different unique columns)
What does NOT NULL represent?
a column must have a value
What does DEFAULT represent?
column take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column
What does querying mean?
retrieving information stored in a database
What do line breaks mean?
they don’t mean anything specific in SQL
What does AS represent?
a keyword in SQL that allows you to rename a column or table using an alias; can be anything you cant as long as they are inside of single quotes
What is an example of an AS statement?
SELECT imdb_rating AS ‘IMDb
FROM movies;
What does DISTINCT represent?
used tor return unique values in the output
What is an example of a DISTINCT statement?
SELECT DISTINCT tools
FROM inventory;
What does WHERE represent?
filters the result set to only include rows where the following condition is true
What is an example of a WHERE statement?
SELECT *
FROM movies
WHERE imdb_rating > 8;
What does LIKE represent?
can be a useful operator when you want to compare similar values
Selecting all movies that start with ‘Se’ and end with ‘en’ and have exactly one character in the middle?
SELECT *
FROM movies
WHERE name LIKE ‘Se_en’;
What is a FOREIGN KEY?
a field in one table, that refers to the PRIMARY KEY in another table
What is % used for?
it is another wildcard character that can be used with LIKE; matches zero or more missing characters in the pattern.
What is an example of using %?
SELECT *
FROM movies
WHERE name LIKE ‘A%’;
What does the example ‘%a’ mean?
it matches all movies that end with ‘a’
When can we not test for null values?
it is not possible to test for null values with comparison operators such as = and !=
What does BETWEEN represent?
used in a WHERE clause to filter the result set within a certain range
What does BETWEEN accept?
two values that are either numbers, text, or dates
What is an example of a BETWEEN statement?
SELECT *
FROM movies
WHERE year BETWEEN 1990 and 1999;
What values are retrieved when the values are text?
the result set is within an alphabetical range
What does AND represent?
it combines multiple conditions in a where clause to make the result set more specific and useful
SQL example: only return 90’s romance movies
SELECT *
FROM movies
WHERE year BETWEEN 1990 and 1999
AND genre = ‘romance’;
What does OR represent?
it is used to combine multiple conditions and displays a row if any condition is true
What does ORDER BY represent?
used to list the data in our result in a particular order; can be done either alphabetically or numerically
What is an example of an ORDER BY expression?
SELECT *
FROM movies
ORDER BY name;
What is a feature we can use with ORDER BY?
DESC or ASC
What does LIMIT represent?
a clause that lets you specify the maximum number of rows the result set will have
What is an example of a LIMIT query?
SELECT *
FROM movies
LIMIT 10;
Where does ORDER BY have to go?
it always goes after WHERE (if present)
Why is the LIMIT statement important?
it saves screen space and it makes our queries run faster
What are CASE statements?
allows us to create different outputs (usually in the SELECT statement); SQL’s way of handling IF-THEN logic
What is an example of a CASE statement query?
SELECT name,
CASE
WHEN imdb_rating > 8 THEN ‘Fantastic’
WHEN imdb_rating > 6 THEN ‘Poorly Received’
ELSE ‘Avoid at All Costs’
END as ‘New Column Name’
FROM movies;
What are aggregates?
calculations performed on multiple rows of a table
What does COUNT() do?
count the number of rows
What does SUM() DO?
sum of the values in a column
What does MAX/MIN() do?
retrieves the largest/smallest value
What does AVG() do?
returns the average of the values in a column
What does ROUND() do?
rounds the values in the column
What is an example of COUNT()?
SELECT COUNT(*)
FROM table_name;
What is an example of SUM()?
SELECT SUM(downloads)
FROM fake_apps;
What is an example of MAX/MIN()?
SELECT MAX(downloads)
FROM fake_apps;
What arguments does ROUND() take?
it takes two arguments: a column name, and an integer
What is an example of ROUND()?
SELECT ROUND(price, 0)
FROM fake_apps;
What does GROUP BY represent?
a clause in SQL that is used to aggregate functions. it is used in collaboration with the SELECT statement to arrange identical data into groups
Where do we place GROUP BY in our queries?
come after any WHERE statements, but before ORDER BY or LIMIT
What is an example of GROUP BY?
SELECT year, AVG(imdb_rating)
FROM movies
GROUP BY year
ORDER BY year;
What does HAVING represent?
allows you to filter which groups to include and which to exclude
When will we use the WHERE?
when we want to limit the results of a query based on values of the individual rows
When will we use HAVING?
when we want to limit the results of a query based on an aggregate property
What does JOIN do?
used to combine tables
What is an example of a JOIN query?
SELECT *
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;
What is another name for an INNER JOIN?
a simple join
What does INNER JOIN do?
our result only includes rows that match on our ON condition
What does a LEFT JOIN do?
will keep all rows from the first table, regardless of where there is a matching row in the second table
What do cross joins not require?
an ON statement
What does UNION represent?
used to stack one dataset on top of the other
What is an example of a UNION query?
SELECT *
FROM table_1
UNION
SELECT *
FROM table_2
What are the rules for appending?
must have the same number of columns and columns must have the same data types and same order as first table
What does dbo stand for?
database owner
