SQL MYSTERY TUTORIAL Flashcards
(24 cards)
What does SQL stand for and what does it do?
Structured Query Language
It is a way to interact w/ relational databases and tables that allow humans to glean specific, meaningful information
What is a relational database?
Best known type of database, but No single definition for word database
Can have varying amount of structure imposed on data
“Relational” part comes w/ specific rules about how to connect data btw different tables
What are relational databases made of? Describe what they look like.
Made of tables, like spreadsheets
Each column in table has name and data type (text, number)
Each row in the table is a specific instance of what the table is about
What does ERD stand for? What is an ERD?
Entity relationship diagram
Visual representation of the relationships among all relevant tables w/in a database
Describe the ERD
Diagram that shows each table, comprised of a name, column names, & data types
Describe the gold key, blue arrow and gray arrows on ERD
*Gold key indicates the column is the primary key of the corresponding table
*Blue arrow: indicates the column is the foreign key of the corresponding table
*Gray arrow: if two tables are related, the matching columns, i.e., the common identifiers of the two tables, are connected by gray arrow
What is a primary key?
A unique identifier for each ROW in a table
What is a foreign key?
Used for reference data in one table to those in another table
What is a query?
Statements constructed to get data from database
Describe trying a query against the database
- For each boxes below, click the “run” to “execute” the query in the box
- You can edit the queries on the page to explore (not case sensitive, but conventional to capitalize for readability; you can use new lines and white spaces to format command for readability).
- Most database systems require to end query in semicolon (;)
- You can change subject of FROM to any other table from the ERD to learn how many rows that table has
Part 2 of Describing query against database
What do we know about the subject/if you want data for each row in a table, use what?
Use * after ‘SELECT’ Rather than seeing all of them, limit results to first 10
SELECT * FROM person LIMIT 10;
Part 3 of describing a query against a database
What are possible values for a column?
When working w/ data, always see if you can find documentation that explains the _____. Is this always available?
What does the DISTINCT keyword do?
explains the database structure (like the ERD) and valid values. But not always available
DISTINCT keyword can give you a quick look at which values are in the database.
After you run it, delete the word DISTINCT and run it again. (after you try that click “reset” and run one more time before continuing)
What elements does a SQL query have? (6 main ones)
*SQL keywords (ex- SELECT, FROM)
*Column names (like name column)
*Table names (ex- person table)
*Wildcard characters (ex- %)
*Functions
*Specific filtering criteria
What are SQL keywords used for?
*Used to specify actions in your queries
*not case sensitive, but use all caps to easily identify
What are some frequently used SQL keywords and what are they used for?
SELECT- allows grabbing of data from specific columns from the database
FROM- allows us to specify which table(s) we care about
WHERE- clause in a query that is used to filter results by specific criteria
Describe the SELECT SQL keyword
allows grabbing of data from specific columns from the database
- * (asterisk): used after SELECT to grab all column from table
- column_name(s): to select specific columns, put the names of the columns after SELECT and use commas to separate them
Describe the FROM SQL keyword
Allows us to specify which table(s) we care about; to select multiple tables, use the table names and commas to separate them
Describe the WHERE SQL keyword
Used to filter results by specific criteria
SELECT * FROM person WHERE name = ‘Kinsey Erickson’
Note that you need to use single straight quotes (‘) around literal text so the database can tell it apart from table and column names
The AND keyword is used to string together multiple filtering criteria so that the filtered results meet each and every one of the criteria. (There’s also an OR keyword, which returns rows that match any of the criteria.)
True or False: when querying for text values, you must match the data as it is in the database.
True, if it doesn’t match your query may come up “no data found”
Notice that when querying for text values, you must match the data as it is in the database. Try changing ‘Chicago’ to ‘chicago’ and running the statement. Then, see if you can edit this SQL statement to find the first clue based on the prompt above.
How does SQL handle when you only know part of the information you need?
Wildcards: special symbols that represent unknown characters, SQL supports two wildcards
What’s the most common wildcard? and describe it
%: when you place a % wildcard in a query string, SQL system will return results that match the rest of the string exactly, and have anything (or nothing) where the wildcard is
ex) or example, ‘Ca%a’ matches Canada and California.
What’s the least commonly wildcard? and what does it mean?
_ : it means ‘match the rest of the text, as long as there’s exactly one character in exactly the position of the _, no matter what it is
So, ‘B_b’ would match ‘Bob’ and ‘Bub’ but not ‘Babe’ or ‘Bb’.
When using wildcards, which symbol don’t you use? And which one do you use instead?
You don’t use the = symbol; use the LIKE word
Trying out wildcards
Try out some wildcards.
Once you run this command, try variations like ‘Irvin_’ and ‘I%e’ – and then explore some more.
1 SELECT DISTINCT city
2 FROM crime_scene_report
3 WHERE city LIKE ‘I%e’;
