Women Who Code Cleveland presents: Introduction to SQL

Work in progress: Google Slides

This workshop focuses on composing SQL queries for READING data from a database. This is valuable for those interested in Data Analysis.

Pre-Requisites

• Download pgadmin4
• Download the DVD movie rental database
• Import database into pgadmin4

the following may be helpful for Mac users

# unzip to convert to tar
unzip dvdrental.zip -d dvdrental

What will be covered in this workshop?

• What is structured data?
• Why RDBMS? Why SQL?
• Import data troubleshooting
• Anatomy of an SQL statement
• Practice Exercises
• Next Steps

Why SQL?

SQL or Structured Query Language is a way to access relational data stored in a database. Relational data preceeds SQL, but both started in the 70s, and have dominated the market ever since.

A benefit to using a database and SQL is its persistence feature, meaning any updates or changes made to the database will be consistent among all users (humans, and other creatures).

Data is powerful

Think of your favorite apps.

• A user experience based on a database which stores different interactions with the app

Think of decision makers, like business owners and project managers.

• These folks use all information available, much of which is stored in databases to deliver value, coordinate inventory, staffing, and logistics

Think of Researchers.

• Use increasingly larger, (hopefully) more representative data to test hypotheses, quantify observations, validate trends

SQL Notes

We will be answering questsions about the DVD Rental database such as:

• What Geographic region do these DVD Rental shops serve?
  • Select all columns in the address table
  • Select all columns in the country table
  • List each district in the address table
  • How many countries are in the country table?
  • List the address of each store (subqueries simple)
  • LIst the country of each store (subqueries advanced)
• How long do people check out DVDs?
  • Sort rentals table by longest rental time
  • Filter out Null values
• Films and Actors
  • Select all columns from the table film_actor where the actor_id =1 (simple)
  • Show which actors are in the most movies (inner join, group by)

---

SQL can be used to create, read, update, and delete databases AND database permissions, adminstered through ROLES and USERS.

Here are some commands for manipulating databases:

UPDATE - updates data in a database

DELETE - deletes data from a database

INSERT INTO - inserts new data into a database

CREATE DATABASE - creates a new database

ALTER DATABASE - modifies a database

CREATE TABLE - creates a new table

ALTER TABLE - modifies a table

DROP TABLE - deletes a table

CREATE INDEX - creates an index (search key)

DROP INDEX - deletes an index

Jobs in Cleveland Ohio that require SQL

• SQL Analyst at [Turn5: A fast-growing e-commerce company](https://careers-turn5.icims.com/jobs/2434/sql-developer/job
• Database Analyst at The Cuyahoga County Prosecutor's Office • $72,571 - $87,048
• Developer/Data Analyst Senior Progressive Insurance • $68,100-$90,800

and more:

Resources

• Database Analyst salaries by state

WWC Data Science Track Events:

• SQL Coding Series - Basics I
• SQL Coding Series - Basics II
• SQL Coding Series - Intermediate I
• SQL Coding Series - Intermediate II
• SQL Coding Series - Advanced