SQL Intro and Data Patterns

1. Course Overview

This course is all about data. How it’s stored, types of storage, how we access it, and how we manage it.

Topics and Technologies

• SQL Databases (primary data structure focus) database type
  • postgresql
  • supabase
  • csv files
• NOSQL database type
  • document driven
  • Mongo DB
  • JSON files
• Postman API Tool
• Content Management Systems Data Management Tool
• Directus
• Supabase Firebase Alternative
• Render Hosting Provider

Assessments

1. In Class Open Book Test on SQL (Friday March 22) individual
2. Create a CMS Dashboard (Wednesday March 28) individual
3. Deploy an App with User Authentication (April 2, 2024) in pairs

Common Data Files

• csv (Comma Separated Values): A table filetype that is plain text and uses commas to organized columns and rows.
  • Can be used in javascript
  • emulates the kind of structure used in sql tables
  • generally you won’t have to import csvs into something like svelte, but you will import them into databases
  • Tutorial: How to read csv files in node
• json (Javascript Object Notation): A document key-value pair type of data structure.
  • This is the way that data is organized and passed from APIs
  • this document driven appraoch is how NOSQL databases like Mongo DB organize data
  • Tutorial: [https://blog.logrocket.com/reading-writing-json-files-node-js-complete-tutorial/]

Activity: Create csv and json data files

This should be done in small groups.

1. Research and discuss what types of data are better in nosql vs sql databases
   • find 2 examples of each
2. Divide up the different examples amongst each other
3. Outline the data fields (in figjam, pen and paper, or a document) and the types of data needed for each field
4. Create a small dataset with 3 entries for your data type based on the data field outline
   • tip: experiment using ai to generate the sample data, practice prompting it with your specific data requirements
   • add the data to a csv or json file (depending on what you’re working on)
5. Review the data structures and pick 1 to discuss with the class
   • outline the type of file and why that was a better choice
   • outline your fields and their types

Class Debrief

• Describe your data: as per the 2 prompts in step 5

1. Introduction to Relational Databases

CRUD Operations and SQL

C - CREATE

• SQL Statement: INSERT INTO
• How it does the job:
  • INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);
  • Specifies the table where you want to add the new data
  • Lists the columns you are filling in
  • Provides the corresponding values to put into those columns

R - READ we will focus on this

• SQL Statement: SELECT
• How it does the job:
  • SELECT column1, column2, … FROM table_name;
  • Lets you choose which columns you need
  • Specifies the table from which to fetch data
  • Can add WHERE conditions to filter which rows you get back
  • Can use ORDER BY to sort results

U - UPDATE

• SQL Statement: UPDATE
• How it does the job:
  • UPDATE table_name SET column1 = new_value, column2 = new_value2 … WHERE condition;
  • Specifies the table you want to modify
  • SET is used to indicate which columns get changed, and what their new values will be
  • WHERE is super important to target only the rows you want to update, otherwise you might change the whole table!

D - DELETE

• SQL Statement: DELETE
• How it does the job
  • DELETE FROM table_name WHERE condition;
  • Specifies the table where you’re removing data
  • WHERE is critical to avoid deleting everything! It pinpoints the specific rows to be removed

2. Import CSVs into a Database

• Supabase documentation
• Note that this is running the create commands in the background for you
• Depending on the interface (and content) that you’re working with, you may or may not get a 1 click solution
  • Tutorial on different ways to import data

Instructions

1. Download this repo (download zip is fine, we won’t be using git for this)
   • it contains a bunch of csv files that have been extracted from the sql file inside them
2. Create a new project in supabase (we will use this for the first half of cpnt-200)
3. Upload the csv files to the database
   • name the tables using these conventions:
     • no capitals
     • underscores for spaces
   • add a primary key (it’ll be the int8 value and is likely called “id”)

3. SQL Queries

SELECT

• Purpose: This is how you tell the database what columns (or calculated data) you want to get back in your results.
• Example: SELECT name, price FROM products;
  • This would give you the ‘name’ and ‘price’ columns from the ‘products’ table.
• Think of it like: Picking specific items off a grocery list.

FROM

• Purpose: This specifies where to find the data, meaning which table to look in.
• Example: SELECT name, price FROM products;
  • Here, we’re telling the database to get the ‘name’ and ‘price’ data from the ‘products’ table.
• Think of it like: Telling someone which store to go shopping at.

ORDER BY

• Purpose: Controls how your results are sorted. You can sort by a column in either ascending (A-Z, low to high) or descending (Z-A, high to low) order.
• Example: SELECT name, price FROM products ORDER BY price DESC;
  • This would give you products listed from highest price to lowest.
• Think of it like: Alphabetizing your book collection or sorting your clothes by size.

COUNT

• Purpose: This is an ‘aggregate function’, meaning it works on a group of rows. It’s used to count how many rows match your criteria.- Example: SELECT COUNT(*) FROM customers WHERE city = ‘Los Angeles’;
  • This would tell you how many customers are located in Los Angeles.
• Think of it like: Counting how many jellybeans are in a jar.

Activity: Your First Queries

all of these are using SELECT and FROM to query data

1. Select all of the information about all of the films in the database
2. Select the first name, last name, and email addresses of all customers
3. Select the number of payments using COUNT()