Happy Friday!
Who else is ready to be DONE with this work week? ✋?
Before totally checking out to binge watch our ‘bottom of the list’ shows (i.e. Bachelor – Listen to Your Heart), we’ll cover the basics to writing a SQL query.
Reviewing our Closet/Relational Database from last hack:
If you didn’t get a chance to read it yet, click here!
The database holds multiple tables and the tables include columns, one of which is the primary or foreign key. For today, we’ll focus on the Details table for our examples with the columns UniqueID, Description, Color, Fabric, and ComfortLevel.
Column Naming 101
- No spacing
- Can use letters and underscores – no numbers
- First character must be a letter not an underscore
- Limit underscores
- Use PascalCase, where the first letter of each word in a compound word is capitalized
Sample Data We Will Query From ⬇️
SELECT * FROM Details
SELECT – SQL statement that is asking the database to pull certain information and output.
*– After SELECT you state the columns you want to show. An asterick pulls ALL the columns in the table.
FROM – another syntax word used to list the table you are pulling from
Details – The table you’re pulling from.
THE RESULT
All rows and columns in the Details table – the original data shown from the sample data above!
SELECT Fabric FROM Details
SELECT– SQL statement that is asking the database to pull certain information and output.
Fabric– The column you want to show in output.
FROM– another syntax word that defines the table the data is pulled.
Details– The table you’re pulling from.
THE RESULT
SELECT UniqueID, Description, ComfortLevel, Color FROM Details WHERE Color=‘Blue’
UniqueID, Description, ComfortLevel, Color– The columns you want to show in output.
WHERE– The SYNTAX to filter the data shown in output
=- ‘Equal to’ operator
‘Blue’– The string (or text) within column that you want to filter by
THE RESULT
SELECT AVG(ComfortLevel) FROM Details
AVG(ColumnName)– To aggregate the numbers in the column within parentheses.
ComfortLevel -The columns you want to show in output.
THE RESULT
Eek… that comfort level is WAYYY too low right now –> ordering sweatpants right meow.
Also, you can use this same format with other total options: SUM, COUNT, MIN, MAX, and STDEV.
?We Covered the Basics ?
I swear this will be useful finding that perfect post-quarantine outfit OR when your boss says ‘hey, what’s the average sale price for category B?” and your IT contact is too busy slash *not responding* to take the time out to answer you.
NOW you’ll be able to dive right into SQL and use your new syntax friends to find out the average! ?
In order to make more content fit for your skill levels, we’ve added a new poll (remember: we ❤️ data).
Head to our home page and rate your skillzz.
Extra TGIF, my friends. ‘Till next week…
