These are the basic specs for Relational Databases, broken into 5 stages. If you complete these specs, try taking on some of the Stretch specs.
Setup repo, complete a Khan Academy project, and install PostgreSQL. For instructions on installing PostgreSQL, see the guides in Resources > Guides.
- Artifact repo is created on GitHub.
- The artifact repo is properly licensed, preferably with the [MIT license][mit-license].
- Solution SQL for the Khan Academy project: [Design a store database][ka-proj-store-db] is added to a file in your repo named
store_db.sql
- SQL in
store_db.sql
has consistent indentation. - All the SQL keywords in
store_db.sql
are written in ALL CAPS. - PostgreSQL is installed on your computer with Homebrew.
Complete another Khan Academy project, use the SQL Designer tool, and install the [Postico app][postico] (this is recommended, but not required).
- Solution SQL for the Khan Academy project: [Data dig][ka-proj-data-dig] is added to a file in your repo named
data_dig.sql
- SQL in
data_dig.sql
has consistent indentation. - All the SQL keywords in
data_dig.sql
are written in ALL CAPS. - Schema diagrams are created for both the "Store database" and "Data dig" database using the [SQL Designer tool][sql-designer].
- Screenshots of each schema diagram are added to repo as
store_db_schema.png
anddata_dig_schema.png
, respectively.
Create schemas and mock data for the "Music Player" app from the Init 3 goal. Then run queries against them. Read the Generating Mock Data section to learn how to generate fake data.
- Folder
music-player/
exists in your repo. - Database schema SQL (using PostgreSQL syntax) for the Music Player from [Init 3: Hello, Web Servers][hello-web-servers] is included in the file
music-player/schema.sql
. - Music Player schema includes table and appropriate columns for Artists
- Music Player schema includes table and appropriate columns for Albums
- Music Player schema includes table and appropriate columns for Songs
- Music Player schema includes table and appropriate columns for Playlists
- Mock data is generated for the Music Player and added to the file
music-player/mock_data.sql
. - Sample queries against the Music Player database are added to the file
music-player/queries.sql
. - Queries against the Music Player use the
WHERE
keyword (at least once) - Queries against the Music Player use the
LIMIT
keyword (at least once) - Queries against the Music Player use the
ORDER BY
keyword (at least once) - Queries against the Music Player use the
LIKE
keyword (at least once) - Queries against the Music Player use the
AND/OR
keyword (at least once) - Queries against the Music Player use the
HAVING
keyword (at least once) - Queries against the Music Player use the
IN
keyword (at least once) - Queries against the Music Player use the
JOIN
keyword (at least once)
Create a database for a fictional bike shop. Use the Table Schema for Bike Shop provided.
- Folder
bike-shop/
exists in your repo. - Database schema SQL (using PostgreSQL syntax) for the Bike Shop is included in the file
bike-shop/schema.sql
. - Bike Shop schema matches the specifications in Table Schema for Bike Shop.
Complete the final Khan Academy project. Design and create schemas for the "Bossggle" app from the Init 1 goals.
- Solution SQL for the Khan Academy project: [App impersonator][ka-proj-app-impersonator] is added to a file in your repo named
app_impersonator.sql
- SQL in
app_impersonator.sql
has consistent indentation. - All the SQL keywords in
app_impersonator.sql
are written in ALL CAPS. - Folder
bossggle/
exists in your repo. - Database schema SQL (using PostgreSQL syntax) for the Bossggle game from [Init 1: Bossggle Word Game][bossggle-word-game] is included in the file
bossggle/schema.sql
.
Bossggle schema supports the following features (i.e. you could write SQL to show this data):
- List all words guessed
- Count all correct words guessed
- Count all incorrect words guessed
- List all words guessed and their scores
- Get sum of scores for all words guessed
- Show 10 most recently guessed words
Pick 2-3 real-world apps (for example: Twitter, Instagram, Google Drive, etc.) and design a sample database schema for each of them. Then generate some mock data and run queries against them. Your schemas don't have to be exactly what these apps would use, but they should roughly approximate the basic features.
- 2 or 3 real-world apps are chosen and folders are created for each of them.
For each real-world app chosen there exists:
- A SQL schema in the file
<app name>/schema.sql
. - A set of mock data
INSERT
statements in the file<app name>/mock_data.sql
. - A set of example queries in the file
<app name>/queries.sql
. - Queries against the real-world apps use the
INSERT
keyword (at least once) - Queries against the real-world apps use the
UPDATE
keyword (at least once) - Queries against the real-world apps use the
DELETE
keyword (at least once) - Queries against the real-world apps use the
WHERE
keyword (at least once) - Queries against the real-world apps use the
LIMIT
keyword (at least once) - Queries against the real-world apps use the
ORDER BY
keyword (at least once) - Queries against the real-world apps use the
JOIN
keyword (at least once) - SQL in all real-world app SQL files have consistent indentation.
- All the SQL keywords all real-world app SQL files are written in ALL CAPS.