SQLLesson.md

Core SQL

Challenge Rating

This goal will likely be within your ZPD if you...

• Are familiar with basic command line operations
• Are interested in learning about writing SQL queries
• Are interested in learning how to install postgres & use the psql command line tool.

Description

A day-by-day structured deep dive into the basics of SQL. Complete lessons, and read tutorials.

This goal makes heavy use of external resources: the Introduction to SQL course on Khan Academy, Tutorial's Point, SQL Zoo and SQL Bolt

For the first few days, you'll be working on existing courses and completing tutorials. Then you'll use your skills to complete a set of SQL exercises and submit your queries as your artifact.

Fork the sql-exercises repo, and use the fork as your artifact. Complete all the exercises without looking at solutions on the internet.

Context

In order to build web applications, it's important to get a solid grounding into the basics of SQL. Proficiency in SQL is a core skill needed to build the backend.

Becoming a full stack web developer means that you need to be comfortable with writing SQL queries and understanding relational database design.

Specifications

Day 1: SQL Basics & Advanced SQL Queries

• Complete the SQL Basics course
• Complete the Advanced SQL queries course

Day 2: Relational Queries

• Complete the Relational Queries course
• Complete the Modifying databases with SQL course

Day 3: Install Postgres and Complete Tutorials with psql

• Install Homebrew by following instructions listed here
• Install Postgres by following instructions listed here
• Learn the psql command line tool by working through the following tutorials
  • complete the tutorial for createdb here
  • complete the tutorial for selecting the database here
  • complete the tutorial for dropping the database here
  • complete the tutorial for create table here
  • complete the tutorial for drop table here
  • complete the tutorial for schema here
  • complete the tutorial for insert here
  • complete the tutorial for select here
  • complete the tutorial for expressions here
  • complete the tutorial for where clause here
  • complete the tutorial for and and or operators here
  • complete the tutorial for update queries here
  • complete the tutorial for delete queries here
  • complete the tutorial for the like clause here
  • complete the tutorial for the limit clause here
  • complete the tutorial for the order by clause here
  • complete the tutorial for the group by clause here

Day 4: Mini Project & Exercises

• Exercises 1-11 in the sql-exercises repo are complete and written to the appropriate file:
  • sql/01-create-table-student.sql
  • sql/02-create-table-friend.sql
  • sql/03-create-table-like.sql
  • sql/04-load-table-learner.sql
  • sql/05-load-table-friend.sql
  • sql/06-load-table-like.sql
  • sql/07-query-friends-gabriel.sql
  • sql/08-query-likes-grade-two-or-more.sql
  • sql/09-mutual-likes.sql
  • sql/10-not-liked.sql
  • sql/11-liked-but-does-not-like.sql
  • [Stretch] sql/12-find-friends-in-common.sql
  • [Stretch] sql/13-popular-students.sql

Day 5: Exercises on SQL Bolt

• Complete the SQL Bolt tutorial

General

• All major features are added via pull requests with a clear description and concise commit messages.
• SQL files are well formatted and readable.
• All the SQL keywords are capitalized.

Stretch

• Complete the Codeacademy course on SQL
• Complete the SQL Zoo quizzes