SQL Drills

Mix.install([
  {:jason, "~> 1.4"},
  {:kino, "~> 0.8.0", override: true},
  {:youtube, github: "brooklinjazz/youtube"},
  {:hidden_cell, github: "brooklinjazz/hidden_cell"}
])

Navigation

Return Home Report An Issue

SQL Drills

Drills help you develop familiarity and muscle memory with syntax through repeated exercises. Unlike usual problems, Drills are not intended to develop problem solving skills, they are purely for developing comfort and speed.

This set of drills is for using SQL and PostgreSQL. Follow the instructions for each drill and complete them as quickly as you can.

To complete this exercise, you may need to switch to the postgres user from your command line.

$ sudo -i -u postgres

PostgreSQL

From the command line, create a drills PostgreSQL Database.

Example Solution

createdb drills

From the command line, open the PostgreSQL prompt.

Example Solution

psql

From the PostgreSQL prompt, view your list of databases.

Example Solution

\l

From the PostgreSQL prompt, connect to your drills database.

Example Solution

\c drills

SQL

From your PostgreSQL prompt, create an authors table. authors should have an ID primary key field, and a NAME VARCHAR field. Every fields should be NOT NULL.

Example Solution

CREATE TABLE authors (
   ID    INT              NOT NULL,
   NAME  VARCHAR (255)    NOT NULL,
   PRIMARY KEY (ID)
);

From your PostgreSQL prompt, create a books table. books should have a TITLE field up to 30 characters, and a CONTENT field of unlimited length. Both fields should be NOT NULL.

books should also store an optional AUTHORID FOREIGN KEY field that REFERENCES the authors table by ID.

Example Solution

CREATE TABLE books (
    ID INT NOT NULL,
    TITLE VARCHAR(30) NOT NULL,
    CONTENT TEXT NOT NULL,
    AUTHORID INT,
    PRIMARY KEY (ID),
    FOREIGN KEY (AUTHORID) REFERENCES authors(ID)
);

From the PostgreSQL prompt, view both of your tables.

Example Solution

\dt

From the PostgreSQL prompt, create an author in the authors table.

Example Solution

INSERT INTO authors (ID,NAME) VALUES (1, 'Patrick Rothfuss');

From the PostgreSQL prompt, create a book in the books table that belongs to the author you previously created.

Example Solution

INSERT INTO books (ID,TITLE,CONTENT,AUTHORID) VALUES (1, 'Name of the Wind', 'My name is Kvothe, pronounced nearly the same as quothe', 1);

From the PostgreSQL prompt, select all authors.

Example Solution

SELECT * FROM authors;

From the PostgreSQL prompt, select all books.

Example Solution

SELECT * FROM books

From the PostgreSQL prompt, select all books that belong to the author you previously created.

Example Solution

SELECT * FROM books b WHERE b.authorid = 1

Mark As Completed

file_name = Path.basename(Regex.replace(~r/#.+/, __ENV__.file, ""), ".livemd")

save_name =
  case Path.basename(__DIR__) do
    "reading" -> "sql_drills_reading"
    "exercises" -> "sql_drills_exercise"
  end

progress_path = __DIR__ <> "/../progress.json"
existing_progress = File.read!(progress_path) |> Jason.decode!()

default = Map.get(existing_progress, save_name, false)

form =
  Kino.Control.form(
    [
      completed: input = Kino.Input.checkbox("Mark As Completed", default: default)
    ],
    report_changes: true
  )

Task.async(fn ->
  for %{data: %{completed: completed}} <- Kino.Control.stream(form) do
    File.write!(
      progress_path,
      Jason.encode!(Map.put(existing_progress, save_name, completed), pretty: true)
    )
  end
end)

form

Commit Your Progress

Run the following in your command line from the curriculum folder to track and save your progress in a Git commit. Ensure that you do not already have undesired or unrelated changes by running git status or by checking the source control tab in Visual Studio Code.

$ git checkout -b sql-drills-exercise
$ git add .
$ git commit -m "finish sql drills exercise"
$ git push origin sql-drills-exercise

Create a pull request from your sql-drills-exercise branch to your solutions branch. Please do not create a pull request to the DockYard Academy repository as this will spam our PR tracker.

DockYard Academy Students Only:

Notify your teacher by including @BrooklinJazz in your PR description to get feedback. You (or your teacher) may merge your PR into your solutions branch after review.

If you are interested in joining the next academy cohort, sign up here to receive more news when it is available.

Up Next