Skip to content

code_archive.md

Latest commit

 

History

History
335 lines (251 loc) · 10.7 KB

code_archive.md

File metadata and controls

335 lines (251 loc) · 10.7 KB
Raw

Biscayne Code Archive

Key:

  • 📚 = Explanation in README.md file.
  • 🎨 = Diagram in README.md file.

Archive:

Week 6: JS
Monday - 03/25:
Tuesday - 03/26:
Wednesday - 03/27:
Thursday - 03/28:
Friday - 03/29:
Week 7: Node | Express | HTTP Request/Response Cycle
Monday - 04/01:
Tuesday - 04/02:
Wednesday - 04/03:
Friday - 04/05:
Week 8: SQL and Full-Stack CRUD
Monday - 04/08:
Tuesday - 04/09:
Wednesday - 04/10:
Thursday 04/11:
Week 10: React
Monday - 04/22:
Tuesday - 04/23:
Wednesday - 04/24:
Thursday - 04/25:
Friday - 04/26:
Week 11: Redux
Monday - 04/29:
Tuesday - 04/30:
Wednesday - 05/01:
Week 12: SQL JOINs | SQL Aggregate Functions | Redux-Saga
Monday - 05/06:

  • Straightforward Redux Feedback Loop Solution

  • Abstract Redux Feedback Loop Solution

  • One-to-Many Joins 
    DROP TABLE IF EXISTS "things";
DROP TABLE IF EXISTS "people";


CREATE TABLE "people" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(500)
);

INSERT INTO "people"
  ("name")
  VALUES
  ('Matt'),
  ('Alf'),
  ('Miss Piggy');

CREATE TABLE "things" (
  "id" SERIAL PRIMARY KEY,
  "thing" VARCHAR(500) NOT NULL,
  "person_id" INT REFERENCES "people"
);

INSERT INTO "things"
  ("thing", "person_id")
  VALUES
  ('Violin', 2),
  ('Ball', NULL),
  ('Sock', 2),
  ('Rock', 1),
  ('Frog', 2);
  
  
-- Query that joings the people and things tables
-- together:
-- Select everything from the people table,
-- then join the things table into that dataset
-- by matching rows from each table ON the
-- people.id and things.person_id values:
SELECT * FROM "people"
  INNER JOIN "things" -- INNER: Where things match.
    ON "people"."id" = "things"."person_id";
  
-- Query that selects all of Alf's things:
SELECT * FROM "people"
  INNER JOIN "things" -- INNER: Where things match.
    ON "people"."id" = "things"."person_id"
  WHERE "person_id" = 2;

-- If we need both id column's values in JS-land, we'll
-- need to alias them so they don't overlap each other:
SELECT
  "people"."id" AS "person_id", -- ALIASING!
  "people"."name" AS "person_name",
  "things"."id" AS "thing_id",
  "things"."thing"	
FROM "people"
  INNER JOIN "things"
    ON "people"."id" = "things"."person_id";
  
-- SELECT all the people, do not discard any people! Show
-- us what things they have:
SELECT * FROM "people"
  LEFT JOIN "things"
    ON "people"."id" = "things"."person_id";
    
-- If we don't care about the data, we just want to know
-- how many rows our query selects:
-- "How many things does Alf have?"
SELECT COUNT(*) FROM "people"
  INNER JOIN "things"
    ON "people"."id" = "things"."person_id"
    WHERE "people"."id" = 2;
  • Many-to-Many JOINs 
    DROP TABLE IF EXISTS "users_hobbies";
DROP TABLE IF EXISTS "users";
DROP TABLE IF EXISTS "hobbies";


CREATE TABLE "users" (
  "id" SERIAL PRIMARY KEY,
  "username" VARCHAR(500)
);

CREATE TABLE "hobbies" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(500)
);

CREATE TABLE "users_hobbies" (
  "id" SERIAL PRIMARY KEY,
  "user_id" INT REFERENCES "users",
  "hobby_id" INT REFERENCES "hobbies",
  "skill_level" INT
);

INSERT INTO "users"
  ("username")
  VALUES
  ('matt'),
  ('alf_88'),
  ('miss_PIGGY');

INSERT INTO "hobbies"
  ("name")
  VALUES
  ('Climbing'),
  ('JavaScripting'),
  ('Baking'),
  ('Knitting'),
  ('Napping');
  
INSERT INTO "users_hobbies"
  ("user_id", "hobby_id", "skill_level")
  VALUES
  (1, 3, 5), -- 'matt' >--< 'Baking'
  (2, 3, 8), -- 'alf_88' >--< 'Baking'
  (1, 1, 3), -- 'matt' >--< 'Climbing'
  (3, 5, 8), -- 'miss_PIGGY' >--< 'Napping'
  (2, 2, 10); -- 'alf_88' >--< 'JavaScripting'
  
SELECT * FROM "users"
  JOIN "users_hobbies"
    ON "users"."id" = "users_hobbies"."user_id"
  JOIN "hobbies"
    ON "users_hobbies"."hobby_id" = "hobbies"."id";
    
-- What hobby is 'matt' best at? We only care about:
-- user's id, user's name, hobby's id, hobby's name, and skill_level
SELECT
  "users"."id" AS "user_id",
  "users"."username",
  "hobbies"."id" AS "hobby_id",
  "hobbies"."name",
  "users_hobbies"."skill_level"
FROM "users"
  JOIN "users_hobbies"
    ON "users"."id" = "users_hobbies"."user_id"
  JOIN "hobbies"
    ON "users_hobbies"."hobby_id" = "hobbies"."id"
  WHERE "users"."id" = 1
  ORDER BY "users_hobbies"."skill_level" DESC
  LIMIT 1;

-- dbRes.rows would be:
-- [
--   {
--     user_id: 1,
--     username: 'matt',
--     hobby_id: 3,
--     name: 'Baking',
--     skill_level: 5
--   }
-- ]

  • SQL Relationships Diagram

Tuesday - 05/07:
Wednesday - 05/08:
Week 13: Auth and Solo Project Scoping
Monday - 05/13:
Tuesday - 05/14:
Week 14 & 15: Solo Projects
Friday - 05/24:
Wednesday - 05/29: