Challenge 06: SQL Scripts

challenge-06-sql.md

Noteful Challenge - SQL Database

For this challenge you will create a Postgres database for the Noteful App. You'll write a SQL script that you can use to create and seed a database a database

Requirements

• Create a noteful-app database
• Create a SQL (.sql) file that creates a notes tables and inserts sample data
• Create sample queries

Create the Database

In your shell, create a noteful-app database using createdb command

Create a table SQL script

• Create a project folder to hold your work
• In the project folder, create noteful-app.1.sql file.
• Add the following command to the file to verify you can connect to your database and run a command.

SELECT CURRENT_DATE;

In your shell, type in the appropriate variation of the following command.

psql -U dev -f ./scratch/noteful-app.1.sql

You should get back the current date.

• In the .sql script file implement a SQL statement which creates a new notes table with the following columns

  • An id field that is a primary key and auto-increments
  • A title text field which can not be null
  • A content text field which can be null
  • A created date field which automatically populates with the current datetime.

Hint: You will get an error if you try to CREATE a table which already exists. Add the following statement to the top of your .sql script to drop the table if it exists and then run your create command.

DROP TABLE IF EXISTS notes;

Populate the Notes table

In the .sql script file, after the CREATE command, add a statement which in inserts sample data into the table. You can use the sample notes from the previous challenges.

Query the Notes SQL script

In your project folder, create another script file name notes-queries.sql, or something similar.

Create queries to perform the following tasks

• Select all the notes
• Select all the notes and limit by 5
• Select all the notes and change the sort order. Experiment with sorting by id, title and date. Try both ascending and descending.
• Select notes where title matches a string exactly
• Select notes where title is LIKE a string. In other words the title contains the word or phrase (e.g cats or ways)
• Update the title and content of a specific note.
• Insert a new note. Try providing incomplete data like missing content or title fields.
• Delete a note by id

Bonus Challenge

When you create a table with a primary key, Postgres creates a sequence field to keep track of the next id. Alter the sequence field so that the IDs start at 1000.

Example solution

Here's one solution to solve this challenge.