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
- Create a
noteful-app
database - Create a SQL (
.sql
) file that creates anotes
tables and inserts sample data - Create sample queries
In your shell, create a noteful-app
database using createdb
command
- 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 newnotes
table with the following columns- An
id
field that is a primary key and auto-increments - A
title
text field which can not benull
- A
content
text field which can benull
- A
created
date field which automatically populates with the current datetime.
- An
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;
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.
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
anddate
. 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.gcats
orways
) - Update the title and content of a specific note.
- Insert a new note. Try providing incomplete data like missing
content
ortitle
fields. - Delete a note by id
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.
Here's one solution to solve this challenge.