SQL MYSTERY TUTORIAL

Question 1

What does SQL stand for and what does it do?

Answer 1

Structured Query Language

It is a way to interact w/ relational databases and tables that allow humans to glean specific, meaningful information

Question 2

What is a relational database?

Answer 2

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

Question 3

What are relational databases made of? Describe what they look like.

Answer 3

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

Question 4

What does ERD stand for? What is an ERD?

Answer 4

Entity relationship diagram

Visual representation of the relationships among all relevant tables w/in a database

Question 5

Describe the ERD

Answer 5

Diagram that shows each table, comprised of a name, column names, & data types

Question 6

Describe the gold key, blue arrow and gray arrows on ERD

Answer 6

*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

Question 7

What is a primary key?

Answer 7

A unique identifier for each ROW in a table

Question 8

What is a foreign key?

Answer 8

Used for reference data in one table to those in another table

Question 9

What is a query?

Answer 9

Statements constructed to get data from database

Question 10

Describe trying a query against the database

Answer 10

1. For each boxes below, click the “run” to “execute” the query in the box
2. 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).
3. Most database systems require to end query in semicolon (;)
4. You can change subject of FROM to any other table from the ERD to learn how many rows that table has

Question 11

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?

Answer 11

Use * after ‘SELECT’ Rather than seeing all of them, limit results to first 10
SELECT * FROM person LIMIT 10;

Question 12

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?

Answer 12

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)

Question 13

What elements does a SQL query have? (6 main ones)

Answer 13

*SQL keywords (ex- SELECT, FROM)
*Column names (like name column)
*Table names (ex- person table)
*Wildcard characters (ex- %)
*Functions
*Specific filtering criteria

Question 14

What are SQL keywords used for?

Answer 14

*Used to specify actions in your queries
*not case sensitive, but use all caps to easily identify

Question 15

What are some frequently used SQL keywords and what are they used for?

Answer 15

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

Question 16

Describe the SELECT SQL keyword

Answer 16

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

Question 17

Describe the FROM SQL keyword

Answer 17

Allows us to specify which table(s) we care about; to select multiple tables, use the table names and commas to separate them

Question 18

Describe the WHERE SQL keyword

Answer 18

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.)

Question 19

True or False: when querying for text values, you must match the data as it is in the database.

Answer 19

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.

Question 20

How does SQL handle when you only know part of the information you need?

Answer 20

Wildcards: special symbols that represent unknown characters, SQL supports two wildcards

Question 21

What’s the most common wildcard? and describe it

Answer 21

%: 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.

Question 22

What’s the least commonly wildcard? and what does it mean?

Answer 22

_ : 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’.

Question 23

When using wildcards, which symbol don’t you use? And which one do you use instead?

Answer 23

You don’t use the = symbol; use the LIKE word

Question 24

Trying out wildcards

Answer 24

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’;