Foundational-Relational-Database-Exercises

---

authors:

• "deonna" team_size: 2 goal_id: 96 title: 'Exercises: Foundational Relational Database Knowledge' created_at: '2016-10-13T20:21:12Z' labels:
• foundational published: true level: '1' redirect_from: "/goals/96"

---

Foundational Relational Database Exercises

Challenge Rating

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

• Can use problem decomposition to split difficult problems into smaller parts
• Have done basic algebra
• Have used spreadsheet software like Excel or Google Sheets
• Are familiar with the concept of a database
• Are interested in schema design for relational databases
• Are interested in how relational databases are structured
• Are interested in learning about writing SQL queries

Description

Gain foundational knowledge of relational databases and SQL by working through a series of exercises.

You'll be learning and writing SQL, doing database design, and establishing important foundational conceptual knowledge.

Create a repository to use as your artifact, and copy your SQL solutions for each exercises over into the repository. This way you'll be able to reference them later, and your project will be more easily reviewable :).

SQL Foundations

Learn SQL query fundamentals by working through bite-sized tutorials on SQLBolt and SQLZOO. Execute tutorial queries directly on SQLBolt and SQLZOO to yield the desired data.

SQL Foundations Practice Exercises

Once you gain more confidence in your ability to construct queries with tutorial data, start working through database exercises on LeetCode.

Exercises to Reinforce Conceptual Knowledge

To solidify your conceptual knowledge of databases, visit HackerRank to build foundational knowledge for working with relational databases. These challenges also expose you to non-relational/NoSQL databases as well as the theoretical underpinnings of relational databases.

Work on a subset of "Easy" and "Medium" level problems in different domains, including:

• Relational Algebra
• Database Normalization
• SQL query construction

Run your responses against the automated test suites provided by LeetCode and HackerRank while using SQL reference materials to construct your answers.

Context

Foundational knowledge for working with relational databases is important if you are creating or maintaining web applications with persistent storage.

Sometimes the overhead of learning how to use a new SQL client or a library (for example, the Sequelize ORM, pg-promise, or the KnexJS query builder) detracts from learning these foundational concepts.

This project provides the opportunity to focus on this core knowledge so working with SQL clients and ORMs will be easier in a 'real-world' environment.

The exercises to reinforce conceptual knowledge emphasize the importance of knowing how to make solid DB design decisions.

Being able to write SQL queries is an important skill, but being able to design a schema that minimizes redundancy and maximizes reliable and painless access to data will pay dividends.

Specifications

It is recommended that you complete these specs in order. The LeetCode exercises build on the SQL Foundations exercises.

DAY 1-2

SQL Foundations

• SQLBolt exercises completed
  • Solutions added to artifact repo
• Exercises in "Tutorials: Learn SQL in stages" section on SQLZOO completed
  • Solutions added to artifact repo

DAY 2-3

SQL Foundations Practice Exercises

• 10 TOTAL "Easy" and "Medium" level database challenges on LeetCode completed
  • Solutions added to artifact repo

DAY 4-5

Exercises to Reinforce Conceptual Knowledge

• 10 Relational Algebra challenges on HackerRank completed
  • Solutions added to artifact repo
• 5 Database Normalization challenges on HackerRank completed
  • Solutions added to artifact repo

Other

• SQL files are well formatted and readable.
• All the SQL keywords are capitalized.
• The artifact produced is properly licensed, preferably with the MIT license

Stretch

• Exercises under the "Assessments: More involved examples for confident users" subtopic on SQLZOO completed
  • Solutions added to artifact repo
• Actual database and web API created with PostgreSQL and Node.js
  • Follow this tutorial (thanks @nodatall!)
  • GET /api/puppies Returns ALL puppies
  • GET /api/puppies/:id Returns a SINGLE puppy
  • POST /api/puppies Adds a puppy
  • PUT /api/puppies/:id Updates a puppy
  • DELETE /api/puppies/:id Deletes a puppy

Resources

• 18+ Best Online Resources for Learning SQL and Database Concepts
• SQLBolt
• SQLZOO
• SQL Teaching
• What are 1NF, 2NF and 3NF in database design?