Querying for that Perfect Outfit
Hello DBD Crew,
Our hack today covers the basics to understanding relational databases and SQL.
SO many job functions these days have to be able to at least pull simple data queries OR put in requests to the IT department.*Trust me*improving the communication between departments will improve your life significantly.
Although all clothes except sweatpants and leggings are irrelevant right now, I’m going to use closets as an analogy.
Tables are different sections in the closet and an outfit is a query. If all the clothing, shoes, and accessories weren’t organized and thrown in one section the amount of mental computing (plus time!) to get ready would be too much.
I mean we have jobs to go to and things to do!
To break down this [zero percent science-based] analogy:
You’ll see visuals of these databases that look similar to the boxes (tables) below.
Developers will use the visual structure of tables and relationships (schema) to set up the database. The database will then be structured and filled with data through a language like SQL!
After the data is set up, analysts like myself will use the visual schema to know where each piece of information is that is needed for business insights.
The main thing you need to know about relational databases… PRIMARY KEYS (or PKs).
Three things a PK must be:
3. Always Present
(opposite of your ex-boyfriend, am I right??)
In our closet [relationship database], every item must be “cataloged” with a unique ID. The different tables are then connected through the Primary key to a foreign key.
ALSO RELATIONSHIP TYPES [vvvv important]
You’ll notice I keep saying everything is important. AND just like your go-to outfit, it’s all about the FIT or it just doesn’t work.
As is anything programming or coding related, the way your tell the database to pull data (the syntax) and order of operations within those statements (as well as the tables themselves) must be written specific to database you are using.
Think of this as how you would talk differently to different individual “assistants” about how to choose an outfit from your closet. From the get-go, the tables (closet sections) and relationships must be communicated to your assistant (database language) in order to get what you want out of your virtual closet. ✔️
Different Type of Table Relationships
You’ll notice in the above schema that there are lines connecting the different tables. There are symbols on the lines that explain what type of relationship the tables have.
The relationships are essentially referring to if there are duplicates within the primary key that you are matching to another table.
- One to one: Vertical bar on both ends of lines
- One to Many: Vertical bar on the “1” side and a Crow’s Foot Notation on the “M” side
- Mandatory: Non-mandatory side can be marked with a circle
- Not possible in Relational Databases: Many to Many
**DBD Extra**– If downloading a SQL server, setting up tables, and writing queries feels overwhelming to you, start with Microsoft Access.
It’s a great way to build a foundation of knowledge about relational databases.
Next Time on ‘Getting Dressed with SQL ???’
We’ll cover basic syntax like SELECT, WHERE, BY, and SUM.
With these query skills you’ll be able to pull a KILLER outfit and a system that would compete with Cher’s closet any day!
Ell and Cam
P.S. We are now looking for other #datalovers to share the shortcuts or knowledge they learned the way. Even between our own team creating hacks and tutorials, it’s so interesting how other members have learned to EXCEL at work with different hacks.
We want this to be a space where you can learn from others and also share the knowledge you have!