Foundations Flashcards
(100 cards)
1
Q
What is SQL?
A
a programming language designed to manage data stored in relational databases
2
Q
What is a relational database?
A
a type of database that stores and provides access to data points that are related to one another
3
Q
What is a table?
A
a collection of data organized into rows and columns
4
Q
What is a column?
A
a set of data values of a particular type
5
Q
What is a row?
A
a single record in a table
6
Q
What are common data types?
A
integer, text, date, real
7
Q
What is an integer data type?
A
a positive or negative whole number
8
Q
What is a text data type?
A
a text string
9
Q
What is a date data type?
A
the date formatted as YYYY-MM-DD
10
Q
What is a real data type?
A
a decimal value
11
Q
What is a statement?
A
text that the database recognizes as a valid command, always ends in a semicolon (;)
12
Q
What does CREATE TABLE do?
A
a clause also known as a command; performs specific tasks in SQL. conventionally written in capital letters.
13
Q
What is the point of the tablename in a statement?
A
refers to the name of the table that the command is applied to
14
Q
What is a parameter?
A
a list of columns, data types, or values that are passed to a clause as an argument
15
Q
How do we write statements?
A
they can be written all on one line, or split up across multiple lines if it makes it easier to read
16
Q
What does CREATE do?
A
allows us to create a new table in the database
17
Q
What is an example of creating a table?
A
CREATE TABLE celebs (
id INTEGER,
name TEXT,
age INTEGER,
);
18
Q
What does INSERT do?
A
it inserts a new row into a table
19
Q
When do we use INSERT?
A
when we want to add new records
20
Q
What is an example of an INSERT?
A
INSERT INTO celebs (id, name, age)
VALUES (1, ‘Justin Beiber’, 29);
21
Q
What does INSERT INTO do?
A
a clause that adds the specified row or rows
22
Q
What does SELECT do?
A
used to fetch data from a database
23
Q
What does SELECT return?
A
a new table called the result set
24
Q
What is an example of a SELECT statement?
A
SELECT name FROM celebs;
25
What is the use of the *?
a special wildcard character that we can use to refer to "all"
26
What does ALTER TABLE do?
adds a new column to a table
27
What is an example of an ALTER TABLE?
ALTER TABLE celebs
ADD COLUMN twitter_handle TEXT;
28
What does NULL mean?
a special value in SQL that represents missing or unknown data
29
What does CTE stand for?
Common Table Expression
30
What is a CTE?
essentially a named subquery, it functions as a virtual table that only its main query can access
31
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
32
What does UPDATE do?
edits a row in a table
33
What is an example of an UPDATE statement?
UPDATE celebs
SET twitter_handle = '@taylorswift13'
WHERE id=4;
34
What does DELETE do?
(FROM) deletes one or more rows from a table
35
What is an example of a DELETE statement?
DELETE FROM celebs
WHERE twitter_handle IS NULL;
36
What does IS NULL represent?
a condition in SQL that returns true when the value is NULL and false otherwise
37
What are constraints?
add information about how a column can be used, are invoked after specifying the data type for a column
38
What is a primary key?
columns can be used to uniquely identify the row
39
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
40
What does UNIQUE represent?
columns have a different value for every row (tables can have many different unique columns)
41
What does NOT NULL represent?
a column must have a value
42
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
43
What does querying mean?
retrieving information stored in a database
44
What do line breaks mean?
they don't mean anything specific in SQL
45
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
46
What is an example of an AS statement?
SELECT imdb_rating AS 'IMDb
FROM movies;
47
What does DISTINCT represent?
used tor return unique values in the output
48
What is an example of a DISTINCT statement?
SELECT DISTINCT tools
FROM inventory;
49
What does WHERE represent?
filters the result set to only include rows where the following condition is true
50
What is an example of a WHERE statement?
SELECT *
FROM movies
WHERE imdb_rating > 8;
51
What does LIKE represent?
can be a useful operator when you want to compare similar values
52
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';
53
What is a FOREIGN KEY?
a field in one table, that refers to the PRIMARY KEY in another table
54
What is % used for?
it is another wildcard character that can be used with LIKE; matches zero or more missing characters in the pattern.
55
What is an example of using %?
SELECT *
FROM movies
WHERE name LIKE 'A%';
56
What does the example '%a' mean?
it matches all movies that end with 'a'
57
When can we not test for null values?
it is not possible to test for null values with comparison operators such as = and !=
58
What does BETWEEN represent?
used in a WHERE clause to filter the result set within a certain range
59
What does BETWEEN accept?
two values that are either numbers, text, or dates
60
What is an example of a BETWEEN statement?
SELECT *
FROM movies
WHERE year BETWEEN 1990 and 1999;
61
What values are retrieved when the values are text?
the result set is within an alphabetical range
62
What does AND represent?
it combines multiple conditions in a where clause to make the result set more specific and useful
63
SQL example: only return 90's romance movies
SELECT *
FROM movies
WHERE year BETWEEN 1990 and 1999
AND genre = 'romance';
64
What does OR represent?
it is used to combine multiple conditions and displays a row if any condition is true
65
What does ORDER BY represent?
used to list the data in our result in a particular order; can be done either alphabetically or numerically
66
What is an example of an ORDER BY expression?
SELECT *
FROM movies
ORDER BY name;
67
What is a feature we can use with ORDER BY?
DESC or ASC
68
What does LIMIT represent?
a clause that lets you specify the maximum number of rows the result set will have
69
What is an example of a LIMIT query?
SELECT *
FROM movies
LIMIT 10;
70
Where does ORDER BY have to go?
it always goes after WHERE (if present)
71
Why is the LIMIT statement important?
it saves screen space and it makes our queries run faster
72
What are CASE statements?
allows us to create different outputs (usually in the SELECT statement); SQL's way of handling IF-THEN logic
73
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;
74
What are aggregates?
calculations performed on multiple rows of a table
75
What does COUNT() do?
count the number of rows
76
What does SUM() DO?
sum of the values in a column
77
What does MAX/MIN() do?
retrieves the largest/smallest value
78
What does AVG() do?
returns the average of the values in a column
79
What does ROUND() do?
rounds the values in the column
80
What is an example of COUNT()?
SELECT COUNT(*)
FROM table_name;
81
What is an example of SUM()?
SELECT SUM(downloads)
FROM fake_apps;
82
What is an example of MAX/MIN()?
SELECT MAX(downloads)
FROM fake_apps;
83
What arguments does ROUND() take?
it takes two arguments: a column name, and an integer
84
What is an example of ROUND()?
SELECT ROUND(price, 0)
FROM fake_apps;
85
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
86
Where do we place GROUP BY in our queries?
come after any WHERE statements, but before ORDER BY or LIMIT
87
What is an example of GROUP BY?
SELECT year, AVG(imdb_rating)
FROM movies
GROUP BY year
ORDER BY year;
88
What does HAVING represent?
allows you to filter which groups to include and which to exclude
89
When will we use the WHERE?
when we want to limit the results of a query based on values of the individual rows
90
When will we use HAVING?
when we want to limit the results of a query based on an aggregate property
91
What does JOIN do?
used to combine tables
92
What is an example of a JOIN query?
SELECT *
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;
93
What is another name for an INNER JOIN?
a simple join
94
What does INNER JOIN do?
our result only includes rows that match on our ON condition
95
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
96
What do cross joins not require?
an ON statement
97
What does UNION represent?
used to stack one dataset on top of the other
98
What is an example of a UNION query?
SELECT *
FROM table_1
UNION
SELECT *
FROM table_2
99
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
100
What does dbo stand for?
database owner
