SQL Drills

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

Navigation

Home Report An Issue Relational Database Management SystemsPhoenix 1.7

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

Commit Your Progress

DockYard Academy now recommends you use the latest Release rather than forking or cloning our repository.

Run git status to ensure there are no undesirable changes. Then run the following in your command line from the curriculum folder to commit your progress.

$ git add .
$ git commit -m "finish SQL Drills exercise"
$ git push

We’re proud to offer our open-source curriculum free of charge for anyone to learn from at their own pace.

We also offer a paid course where you can learn from an instructor alongside a cohort of your peers. We will accept applications for the June-August 2023 cohort soon.

Navigation

Home Report An Issue Relational Database Management SystemsPhoenix 1.7