WOMEN WHO CODE CLEVELAND PRESENTS INTRODUCTION TO SQL, the sequel!
Presented by Natalie Olivo
WWC Mission
Empower diverse women to excel in technology careers.
06
TABLE OF CONTENTS.
01
02
03
04
05
What is data?
Why RDMS? Why SQL?
Anatomy of an SQL statement
Practice queries
Data import
NEXT STEPS
WHAT IS DATA?
UNSTRUCTURED, SEMI-STRUCTURED, STRUCTURED
UNSTRUCTURED DATA
Source: https://en.wikipedia.org/wiki/Women_Who_Code
Women Who Code (WWCode) is an international non-profit organization that provides services for women pursuing technology careers and a job board for companies seeking coding professionals. The company aims to provide an avenue into the technology world by evaluating and assisting women in developing technical skills.
In addition to training, professional evaluations, meetings, and scholarships, Women Who Code offers networking and mentorship. As of 2023, the organization has held more than 16,000 free events around the world and built a membership of over 343,000 people representing over 147 countries.[3] The current Chief Executive Officer of Women Who Code is Alaina Percival.[4]
SEMI-STRUCTURED DATA
Source: https://www.womenwhocode.com/
STRUCTURED DATA
Snapshot of an Entity Relationship Diagram (ERD)
Why RDMS?
Why SQL?
Relational Database Management System
The RDBMS provides an interface between users and applications and the database, as well as administrative functions for managing data storage, access, and performance.
RDBMS vs Flat file management system
RDBMS
Flat File Management System
Diagram source:
WWC Data Science Track SQL Coding Series Basics I
Database
Database Management System
Who uses SQL?
DATA IMPORT
We will be using free and open-source resources Pgadmin4, postgres
Set up your local environment
Download the practice database
DVD Movie Rental Database
Download pgadmin4
Import the database into pgadmin4
Anatomy of an SQL Query
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
SQL Query Order of Execution
SQL Syntax
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
;
Execution order
FROM and JOIN
WHERE�GROUP BY
HAVING
SELECT
ORDER BY
LIMIT
PRACTICE QUERIES
What Geographic Region does this data cover?
Select all columns in the address table.
Hint:
* can be used to mean all columns
Select all columns in the country table.
What Geographic Region does this data cover?
List each district in the address table
Hint:
SELECT DISTINCT column1
FROM table_name
The SELECT DISTINCT statement is used to return only distinct (different) values.
How many countries are in the country table
Hint:
SELECT COUNT(column_name)
FROM table_name;
The COUNT() function returns the number of rows that matches a specified criterion.
List the address of each store, subqueries
A subquery is a SQL query nested inside a larger query.
A subquery may occur in:
- A SELECT clause
- A FROM clause
- A WHERE clause
SELECT *
FROM address
WHERE address_id IN
(SELECT address_id
FROM store)
List the country of each store, subqueries advanced
A subquery is a SQL query nested inside a larger query.
A subquery may occur in:
- A SELECT clause
- A FROM clause
- A WHERE clause
SELECT *
FROM COUNTRY
WHERE country_id IN
(SELECT country_id
FROM city
WHERE city_id in
(SELECT city_id
FROM address
WHERE address_id in
(SELECT address_id
FROM store)))
How long people check out DVDs? Calculated fields
You can subtract timestamps
SELECT *,
(return_date - rental_date) as rental_time
FROM rental;
You can use DATE_PART to extract a subfield from a date or time value
SELECT *,
(DATE_PART('day', return_date-rental_date)) as rental_time
FROM rental;
How long people check out DVDs? Nulls and Sort
Sort your data by your calculated field “rental_time”
Hint:
ORDER BY [DESC | ASC] [NULLS FIRST | NULLS LAST]
Filter out null values from your sorted data
Hint: You will not be able to reference a calculated field in a WHERE statement
Inner Join Practice
Step 1:
Write the two queries separately
Objective:
Films and Actors
Simple:
Select all columns from table film_actor where the actor_id = 1
Advanced:
Show which actors are in the most movies.
Hint:
Write groupby first
https://www.w3schools.com/sql/sql_view.asp
SQL Views:
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL statements and functions to a view and present the data as if the data were coming from one single table.
A view is created with the CREATE VIEW statement.
https://www.w3schools.com/sql/sql_view.asp
SQL Triggers:
A stored procedure which “reacts” to certain actions we make in the database. The main idea behind triggers is that they always perform an action in case some event happens.
NEXT STEPS
Free resources to keep learning
Jobs near us
Previous Events by WWC Cleveland
March
April
May
June
July
New Leadership Hangout
6 attendees
Art Tech Hack CLE
21 attendees
Tech Mentor/Mentee “Speed Dating”
39 attendees
Bootcamp Discussion
12 attendees
Beginner’s SQL Workshop
47 attendees
Meet your NETWORK DIRECTORS
Natalie O.
Bianca P.
Amanda B.
MACHINE LEARNING ENGINEER
FULL STACK DEVELOPER
IT STAFF PROGRAM MANAGER
THANK YOU!
Do you have any questions?