Skip to content

Instantly share code, notes, and snippets.

@clgrogan

clgrogan/gist:4a62c4f45d71296c113a3fddda9984d7 Secret

Created December 10, 2019 22:14
Show Gist options
  • Save clgrogan/4a62c4f45d71296c113a3fddda9984d7 to your computer and use it in GitHub Desktop.
Save clgrogan/4a62c4f45d71296c113a3fddda9984d7 to your computer and use it in GitHub Desktop.
Download ZIP
Koans Brothers
Raw
gistfile1.txt
id | title | genre | year_published | available
------+------------------------------+---------------------+------------------+-------------
1 | A Tale of Two Cities | Historical drama | 1859 | 1
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1
()
Meditate on the "and" operator: select all books published before 1900 that are available has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available
------+------------------------------+------------------+------------------+-------------
1 | A Tale of Two Cities | Historical drama | 1859 | 1
6 | The Gilded Age | Novel | 1873 | 1
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1
8 | Beowulf | Epic | 1000 | 1
()
Meditate on the "or" operator: select all books that start with 'The' OR are available has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available
------+------------------------------+---------------------+------------------+-------------
1 | A Tale of Two Cities | Historical drama | 1859 | 1
4 | Never Let Me Go | Speculative fiction | 2005 | 1
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1
6 | The Gilded Age | Novel | 1873 | 1
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1
8 | Beowulf | Epic | 1000 | 1
()
Meditate on the "INNER JOIN" keyword to link tables has expanded your awareness.
Awareness expander:
===================
id | name | id | type | customer_id | book_id | datetime
-------+---------------+------+----------+---------------+-----------+------------------
20001 | Paula Vasquez | 1 | Checkout | 20001 | 4 | 2015-10-18 17:32
20001 | Paula Vasquez | 2 | Checkin | 20001 | 4 | 2015-11-27 18:11
20000 | Aaron Meyers | 3 | Checkout | 20000 | 3 | 2015-10-22 14:56
20000 | Aaron Meyers | 4 | Checkin | 20000 | 3 | 2015-10-29 8:22
20003 | Matt Nguyen | 5 | Checkout | 20003 | 3 | 2015-10-29 10:03
20003 | Matt Nguyen | 6 | Checkin | 20003 | 3 | 2015-11-04 11:15
20001 | Paula Vasquez | 7 | Checkout | 20001 | 1 | 2015-11-15 12:00
20001 | Paula Vasquez | 8 | Checkin | 20001 | 1 | 2015-12-08 19:34
20002 | Debra Olson | 9 | Checkout | 20002 | 5 | 2015-11-15 08:44
20002 | Debra Olson | 10 | Checkout | 20002 | 6 | 2015-11-15 08:44
20002 | Debra Olson | 11 | Checkin | 20002 | 5 | 2015-12-01 09:20
20002 | Debra Olson | 12 | Checkin | 20002 | 6 | 2015-11-20 10:13
20002 | Debra Olson | 13 | Checkout | 20002 | 2 | 2015-12-30 11:08
20000 | Aaron Meyers | 14 | Checkout | 20000 | 3 | 2015-12-15 15:57
20000 | Aaron Meyers | 15 | Checkout | 20000 | 6 | 2015-12-15 15:57
20001 | Paula Vasquez | 16 | Checkout | 20001 | 1 | 2015-12-15 13:05
20001 | Paula Vasquez | 17 | Checkin | 20001 | 1 | 2015-12-30 14:59
20000 | Aaron Meyers | 18 | Lost | 20000 | 3 | 2015-12-30 16:46
()
Meditate on the "INNER" being implicit has expanded your awareness.
Awareness expander:
===================
id | name | id | type | customer_id | book_id | datetime
-------+---------------+------+----------+---------------+-----------+------------------
20001 | Paula Vasquez | 1 | Checkout | 20001 | 4 | 2015-10-18 17:32
20001 | Paula Vasquez | 2 | Checkin | 20001 | 4 | 2015-11-27 18:11
20000 | Aaron Meyers | 3 | Checkout | 20000 | 3 | 2015-10-22 14:56
20000 | Aaron Meyers | 4 | Checkin | 20000 | 3 | 2015-10-29 8:22
20003 | Matt Nguyen | 5 | Checkout | 20003 | 3 | 2015-10-29 10:03
20003 | Matt Nguyen | 6 | Checkin | 20003 | 3 | 2015-11-04 11:15
20001 | Paula Vasquez | 7 | Checkout | 20001 | 1 | 2015-11-15 12:00
20001 | Paula Vasquez | 8 | Checkin | 20001 | 1 | 2015-12-08 19:34
20002 | Debra Olson | 9 | Checkout | 20002 | 5 | 2015-11-15 08:44
20002 | Debra Olson | 10 | Checkout | 20002 | 6 | 2015-11-15 08:44
20002 | Debra Olson | 11 | Checkin | 20002 | 5 | 2015-12-01 09:20
20002 | Debra Olson | 12 | Checkin | 20002 | 6 | 2015-11-20 10:13
20002 | Debra Olson | 13 | Checkout | 20002 | 2 | 2015-12-30 11:08
20000 | Aaron Meyers | 14 | Checkout | 20000 | 3 | 2015-12-15 15:57
20000 | Aaron Meyers | 15 | Checkout | 20000 | 6 | 2015-12-15 15:57
20001 | Paula Vasquez | 16 | Checkout | 20001 | 1 | 2015-12-15 13:05
20001 | Paula Vasquez | 17 | Checkin | 20001 | 1 | 2015-12-30 14:59
20000 | Aaron Meyers | 18 | Lost | 20000 | 3 | 2015-12-30 16:46
()
Meditate on join conditions has expanded your awareness.
Awareness expander:
===================
id | name | id | type | customer_id | book_id | datetime
-------+---------------+------+----------+---------------+-----------+------------------
20001 | Paula Vasquez | 1 | Checkout | 20001 | 4 | 2015-10-18 17:32
20001 | Paula Vasquez | 2 | Checkin | 20001 | 4 | 2015-11-27 18:11
20000 | Aaron Meyers | 3 | Checkout | 20000 | 3 | 2015-10-22 14:56
20000 | Aaron Meyers | 4 | Checkin | 20000 | 3 | 2015-10-29 8:22
20003 | Matt Nguyen | 5 | Checkout | 20003 | 3 | 2015-10-29 10:03
20003 | Matt Nguyen | 6 | Checkin | 20003 | 3 | 2015-11-04 11:15
20001 | Paula Vasquez | 7 | Checkout | 20001 | 1 | 2015-11-15 12:00
20001 | Paula Vasquez | 8 | Checkin | 20001 | 1 | 2015-12-08 19:34
20002 | Debra Olson | 9 | Checkout | 20002 | 5 | 2015-11-15 08:44
20002 | Debra Olson | 10 | Checkout | 20002 | 6 | 2015-11-15 08:44
20002 | Debra Olson | 11 | Checkin | 20002 | 5 | 2015-12-01 09:20
20002 | Debra Olson | 12 | Checkin | 20002 | 6 | 2015-11-20 10:13
20002 | Debra Olson | 13 | Checkout | 20002 | 2 | 2015-12-30 11:08
20000 | Aaron Meyers | 14 | Checkout | 20000 | 3 | 2015-12-15 15:57
20000 | Aaron Meyers | 15 | Checkout | 20000 | 6 | 2015-12-15 15:57
20001 | Paula Vasquez | 16 | Checkout | 20001 | 1 | 2015-12-15 13:05
20001 | Paula Vasquez | 17 | Checkin | 20001 | 1 | 2015-12-30 14:59
20000 | Aaron Meyers | 18 | Lost | 20000 | 3 | 2015-12-30 16:46
()
Meditate on limiting columns in joins has expanded your awareness.
Awareness expander:
===================
name | datetime
---------------+------------------
Paula Vasquez | 2015-10-18 17:32
Paula Vasquez | 2015-11-27 18:11
Aaron Meyers | 2015-10-22 14:56
Aaron Meyers | 2015-10-29 8:22
Matt Nguyen | 2015-10-29 10:03
Matt Nguyen | 2015-11-04 11:15
Paula Vasquez | 2015-11-15 12:00
Paula Vasquez | 2015-12-08 19:34
Debra Olson | 2015-11-15 08:44
Debra Olson | 2015-11-15 08:44
Debra Olson | 2015-12-01 09:20
Debra Olson | 2015-11-20 10:13
Debra Olson | 2015-12-30 11:08
Aaron Meyers | 2015-12-15 15:57
Aaron Meyers | 2015-12-15 15:57
Paula Vasquez | 2015-12-15 13:05
Paula Vasquez | 2015-12-30 14:59
Aaron Meyers | 2015-12-30 16:46
()
Meditate on aliasing tables has expanded your awareness.
Awareness expander:
===================
id | name | id | type | customer_id | book_id | datetime
-------+---------------+------+----------+---------------+-----------+------------------
20001 | Paula Vasquez | 1 | Checkout | 20001 | 4 | 2015-10-18 17:32
20001 | Paula Vasquez | 2 | Checkin | 20001 | 4 | 2015-11-27 18:11
20000 | Aaron Meyers | 3 | Checkout | 20000 | 3 | 2015-10-22 14:56
20000 | Aaron Meyers | 4 | Checkin | 20000 | 3 | 2015-10-29 8:22
20003 | Matt Nguyen | 5 | Checkout | 20003 | 3 | 2015-10-29 10:03
20003 | Matt Nguyen | 6 | Checkin | 20003 | 3 | 2015-11-04 11:15
20001 | Paula Vasquez | 7 | Checkout | 20001 | 1 | 2015-11-15 12:00
20001 | Paula Vasquez | 8 | Checkin | 20001 | 1 | 2015-12-08 19:34
20002 | Debra Olson | 9 | Checkout | 20002 | 5 | 2015-11-15 08:44
20002 | Debra Olson | 10 | Checkout | 20002 | 6 | 2015-11-15 08:44
20002 | Debra Olson | 11 | Checkin | 20002 | 5 | 2015-12-01 09:20
20002 | Debra Olson | 12 | Checkin | 20002 | 6 | 2015-11-20 10:13
20002 | Debra Olson | 13 | Checkout | 20002 | 2 | 2015-12-30 11:08
20000 | Aaron Meyers | 14 | Checkout | 20000 | 3 | 2015-12-15 15:57
20000 | Aaron Meyers | 15 | Checkout | 20000 | 6 | 2015-12-15 15:57
20001 | Paula Vasquez | 16 | Checkout | 20001 | 1 | 2015-12-15 13:05
20001 | Paula Vasquez | 17 | Checkin | 20001 | 1 | 2015-12-30 14:59
20000 | Aaron Meyers | 18 | Lost | 20000 | 3 | 2015-12-30 16:46
()
Meditate on multiple joins has expanded your awareness.
Awareness expander:
===================
id | type | datetime | id | name | title
------+----------+------------------+-------+---------------+---------------------------
1 | Checkout | 2015-10-18 17:32 | 20001 | Paula Vasquez | Never Let Me Go
2 | Checkin | 2015-11-27 18:11 | 20001 | Paula Vasquez | Never Let Me Go
3 | Checkout | 2015-10-22 14:56 | 20000 | Aaron Meyers | Moby Dick
4 | Checkin | 2015-10-29 8:22 | 20000 | Aaron Meyers | Moby Dick
5 | Checkout | 2015-10-29 10:03 | 20003 | Matt Nguyen | Moby Dick
6 | Checkin | 2015-11-04 11:15 | 20003 | Matt Nguyen | Moby Dick
7 | Checkout | 2015-11-15 12:00 | 20001 | Paula Vasquez | A Tale of Two Cities
8 | Checkin | 2015-12-08 19:34 | 20001 | Paula Vasquez | A Tale of Two Cities
9 | Checkout | 2015-11-15 08:44 | 20002 | Debra Olson | The Left Hand of Darkness
10 | Checkout | 2015-11-15 08:44 | 20002 | Debra Olson | The Gilded Age
11 | Checkin | 2015-12-01 09:20 | 20002 | Debra Olson | The Left Hand of Darkness
12 | Checkin | 2015-11-20 10:13 | 20002 | Debra Olson | The Gilded Age
13 | Checkout | 2015-12-30 11:08 | 20002 | Debra Olson | Oliver Twist
14 | Checkout | 2015-12-15 15:57 | 20000 | Aaron Meyers | Moby Dick
15 | Checkout | 2015-12-15 15:57 | 20000 | Aaron Meyers | The Gilded Age
16 | Checkout | 2015-12-15 13:05 | 20001 | Paula Vasquez | A Tale of Two Cities
17 | Checkin | 2015-12-30 14:59 | 20001 | Paula Vasquez | A Tale of Two Cities
18 | Lost | 2015-12-30 16:46 | 20000 | Aaron Meyers | Moby Dick
()
Meditate on the "LEFT JOIN" keyword has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available | datetime
------+------------------------------+---------------------+------------------+-------------+------------------
1 | A Tale of Two Cities | Historical drama | 1859 | 1 | 2015-11-15 12:00
1 | A Tale of Two Cities | Historical drama | 1859 | 1 | 2015-12-08 19:34
1 | A Tale of Two Cities | Historical drama | 1859 | 1 | 2015-12-15 13:05
1 | A Tale of Two Cities | Historical drama | 1859 | 1 | 2015-12-30 14:59
2 | Oliver Twist | Novel | 1837 | 0 | 2015-12-30 11:08
3 | Moby Dick | Novel | 1851 | 0 | 2015-10-22 14:56
3 | Moby Dick | Novel | 1851 | 0 | 2015-10-29 10:03
3 | Moby Dick | Novel | 1851 | 0 | 2015-10-29 8:22
3 | Moby Dick | Novel | 1851 | 0 | 2015-11-04 11:15
3 | Moby Dick | Novel | 1851 | 0 | 2015-12-15 15:57
3 | Moby Dick | Novel | 1851 | 0 | 2015-12-30 16:46
4 | Never Let Me Go | Speculative fiction | 2005 | 1 | 2015-10-18 17:32
4 | Never Let Me Go | Speculative fiction | 2005 | 1 | 2015-11-27 18:11
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1 | 2015-11-15 08:44
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1 | 2015-12-01 09:20
6 | The Gilded Age | Novel | 1873 | 1 | 2015-11-15 08:44
6 | The Gilded Age | Novel | 1873 | 1 | 2015-11-20 10:13
6 | The Gilded Age | Novel | 1873 | 1 | 2015-12-15 15:57
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1 | None
8 | Beowulf | Epic | 1000 | 1 | None
()
Meditate on ONE-TO-MANY relationships has expanded your awareness.
Awareness expander:
===================
id | name | id | type | customer_id | book_id | datetime
-------+---------------+------+----------+---------------+-----------+------------------
20001 | Paula Vasquez | 1 | Checkout | 20001 | 4 | 2015-10-18 17:32
20001 | Paula Vasquez | 2 | Checkin | 20001 | 4 | 2015-11-27 18:11
20001 | Paula Vasquez | 7 | Checkout | 20001 | 1 | 2015-11-15 12:00
20001 | Paula Vasquez | 8 | Checkin | 20001 | 1 | 2015-12-08 19:34
20001 | Paula Vasquez | 16 | Checkout | 20001 | 1 | 2015-12-15 13:05
20001 | Paula Vasquez | 17 | Checkin | 20001 | 1 | 2015-12-30 14:59
()
Meditate on MANY-TO-ONE relationships has expanded your awareness.
Awareness expander:
===================
id | type | customer_id | book_id | datetime | id | title
------+----------+---------------+-----------+------------------+------+----------------------
7 | Checkout | 20001 | 1 | 2015-11-15 12:00 | 1 | A Tale of Two Cities
8 | Checkin | 20001 | 1 | 2015-12-08 19:34 | 1 | A Tale of Two Cities
16 | Checkout | 20001 | 1 | 2015-12-15 13:05 | 1 | A Tale of Two Cities
17 | Checkin | 20001 | 1 | 2015-12-30 14:59 | 1 | A Tale of Two Cities
()
Meditate on MANY-TO-MANY relationships has expanded your awareness.
Awareness expander:
===================
first_name | last_name | title
----------------+-------------+------------------------------
Charles | Dickens | A Tale of Two Cities
Charles | Dickens | Oliver Twist
Mark | Twain | The Gilded Age
Charles Dudley | Warner | The Gilded Age
Mark | Twain | The Adventures of Tom Sawyer
()
Meditate on changing data with "UPDATE" has expanded your awareness.
Awareness expander:
===================
Query executed successfully and did not return any data.
()
Meditate on removing data with "DELETE" has expanded your awareness.
Awareness expander:
===================
Query executed successfully and did not return any data.
()
Meditate on using subqueries with IN has expanded your awareness.
Awareness expander:
===================
id | type | customer_id | book_id | datetime
------+----------+---------------+-----------+------------------
18 | Lost | 20000 | 3 | 2015-12-30 16:46
14 | Checkout | 20000 | 3 | 2015-12-15 15:57
15 | Checkout | 20000 | 6 | 2015-12-15 15:57
4 | Checkin | 20000 | 3 | 2015-10-29 8:22
3 | Checkout | 20000 | 3 | 2015-10-22 14:56
()
Meditate on WITH to create a Common Table Expression query has expanded your awareness.
Awareness expander:
===================
id | type | customer_id | book_id | datetime
------+----------+---------------+-----------+------------------
18 | Lost | 20000 | 3 | 2015-12-30 16:46
14 | Checkout | 20000 | 3 | 2015-12-15 15:57
15 | Checkout | 20000 | 6 | 2015-12-15 15:57
4 | Checkin | 20000 | 3 | 2015-10-29 8:22
3 | Checkout | 20000 | 3 | 2015-10-22 14:56
()
Meditate on using subqueries for deleting data has expanded your awareness.
Awareness expander:
===================
Query executed successfully and did not return any data.
()
Meditate on counting rows with COUNT has expanded your awareness.
Awareness expander:
===================
count(*)
------------
8
()
Meditate on counting within groups with GROUP BY has expanded your awareness.
Awareness expander:
===================
genre | count(*)
---------------------+------------
Epic | 1
Historical drama | 1
Novel | 3
Satire | 1
Speculative fiction | 2
()
Meditate on MAX has expanded your awareness.
Awareness expander:
===================
max(datetime)
------------------
2015-12-30 16:46
()
Meditate on MIN has expanded your awareness.
Awareness expander:
===================
min(datetime)
------------------
2015-10-18 17:32
()
Meditate on getting the maximum within groups has expanded your awareness.
Awareness expander:
===================
genre | max(year_published)
---------------------+-----------------------
Epic | 1000
Historical drama | 1859
Novel | 1873
Satire | 1876
Speculative fiction | 2005
()
Meditate on joining data with aggregates has expanded your awareness.
Awareness expander:
===================
name | count(*)
---------------+------------
Aaron Meyers | 5
Debra Olson | 5
Matt Nguyen | 2
Paula Vasquez | 6
()
Meditate on aliasing columns: present the year_published column as "Publication Date" has expanded your awareness.
Awareness expander:
===================
title | Publication Date
------------------------------+--------------------
A Tale of Two Cities | 1859
Oliver Twist | 1837
Moby Dick | 1851
Never Let Me Go | 2005
The Left Hand of Darkness | 1969
The Gilded Age | 1873
The Adventures of Tom Sawyer | 1876
Beowulf | 1000
()
Meditate on sorting with "ORDER BY": sort by customer name has expanded your awareness.
Awareness expander:
===================
id | name
-------+---------------
20000 | Aaron Meyers
20002 | Debra Olson
20003 | Matt Nguyen
20001 | Paula Vasquez
()
A koan has damaged your karma! Your query is incorrect.
Karma damager:
EXPECTED RESULT
===============
id | title | genre | year_published | available
------+------------------------------+---------------------+------------------+-------------
8 | Beowulf | Epic | 1000 | 1
1 | A Tale of Two Cities | Historical drama | 1859 | 1
3 | Moby Dick | Novel | 1851 | 0
2 | Oliver Twist | Novel | 1837 | 0
6 | The Gilded Age | Novel | 1873 | 1
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1
4 | Never Let Me Go | Speculative fiction | 2005 | 1
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1
ACTUAL RESULT
=============
id | title | genre | year_published | available
------+------------------------------+---------------------+------------------+-------------
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1
4 | Never Let Me Go | Speculative fiction | 2005 | 1
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1
6 | The Gilded Age | Novel | 1873 | 1
2 | Oliver Twist | Novel | 1837 | 0
3 | Moby Dick | Novel | 1851 | 0
1 | A Tale of Two Cities | Historical drama | 1859 | 1
8 | Beowulf | Epic | 1000 | 1
Meditate on multiple sorts: sort by genre, then title in presentation.sql.
Curts-MBP:sql-koans cgrogan$ python src/path_to_enlightenment.py
Meditate on upper-case queries has expanded your awareness.
Awareness expander:
===================
1
-----
1
()
Meditate on lower-case queries has expanded your awareness.
Awareness expander:
===================
1
-----
1
()
Meditate on selecting all columns from a table has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available
------+------------------------------+---------------------+------------------+-------------
1 | A Tale of Two Cities | Historical drama | 1859 | 1
2 | Oliver Twist | Novel | 1837 | 0
3 | Moby Dick | Novel | 1851 | 0
4 | Never Let Me Go | Speculative fiction | 2005 | 1
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1
6 | The Gilded Age | Novel | 1873 | 1
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1
8 | Beowulf | Epic | 1000 | 1
()
Meditate on selecting one column (title) from a table has expanded your awareness.
Awareness expander:
===================
title
------------------------------
A Tale of Two Cities
Oliver Twist
Moby Dick
Never Let Me Go
The Left Hand of Darkness
The Gilded Age
The Adventures of Tom Sawyer
Beowulf
()
Meditate on selecting some columns (title, available) from a table has expanded your awareness.
Awareness expander:
===================
title | available
------------------------------+-------------
A Tale of Two Cities | 1
Oliver Twist | 0
Moby Dick | 0
Never Let Me Go | 1
The Left Hand of Darkness | 1
The Gilded Age | 1
The Adventures of Tom Sawyer | 1
Beowulf | 1
()
Meditate on adding a new book has expanded your awareness.
Awareness expander:
===================
Query executed successfully and did not return any data.
()
Meditate on adding two new books has expanded your awareness.
Awareness expander:
===================
Query executed successfully and did not return any data.
()
Meditate on filtering results with WHERE: select all novels has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available
------+----------------+---------+------------------+-------------
2 | Oliver Twist | Novel | 1837 | 0
3 | Moby Dick | Novel | 1851 | 0
6 | The Gilded Age | Novel | 1873 | 1
()
Meditate on the != operator: select all books that are not novels has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available
------+------------------------------+---------------------+------------------+-------------
1 | A Tale of Two Cities | Historical drama | 1859 | 1
4 | Never Let Me Go | Speculative fiction | 2005 | 1
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1
8 | Beowulf | Epic | 1000 | 1
()
Meditate on the <> operator: select all books that are not novels has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available
------+------------------------------+---------------------+------------------+-------------
1 | A Tale of Two Cities | Historical drama | 1859 | 1
4 | Never Let Me Go | Speculative fiction | 2005 | 1
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1
8 | Beowulf | Epic | 1000 | 1
()
Meditate on the "in" operator: select all novels and satires has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available
------+------------------------------+---------+------------------+-------------
2 | Oliver Twist | Novel | 1837 | 0
3 | Moby Dick | Novel | 1851 | 0
6 | The Gilded Age | Novel | 1873 | 1
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1
()
Meditate on the "not in" operator: select all books that are not novels or satires has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available
------+---------------------------+---------------------+------------------+-------------
1 | A Tale of Two Cities | Historical drama | 1859 | 1
4 | Never Let Me Go | Speculative fiction | 2005 | 1
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1
8 | Beowulf | Epic | 1000 | 1
()
Meditate on the > operator: select all books published after 1900 has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available
------+---------------------------+---------------------+------------------+-------------
4 | Never Let Me Go | Speculative fiction | 2005 | 1
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1
()
Meditate on the "like" and % operators: select all books that start with 'The' has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available
------+------------------------------+---------------------+------------------+-------------
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1
6 | The Gilded Age | Novel | 1873 | 1
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1
()
Meditate on the "like" and % operators: select all books that contain 'of' has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available
------+------------------------------+---------------------+------------------+-------------
1 | A Tale of Two Cities | Historical drama | 1859 | 1
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1
()
Meditate on the "and" operator: select all books published before 1900 that are available has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available
------+------------------------------+------------------+------------------+-------------
1 | A Tale of Two Cities | Historical drama | 1859 | 1
6 | The Gilded Age | Novel | 1873 | 1
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1
8 | Beowulf | Epic | 1000 | 1
()
Meditate on the "or" operator: select all books that start with 'The' OR are available has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available
------+------------------------------+---------------------+------------------+-------------
1 | A Tale of Two Cities | Historical drama | 1859 | 1
4 | Never Let Me Go | Speculative fiction | 2005 | 1
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1
6 | The Gilded Age | Novel | 1873 | 1
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1
8 | Beowulf | Epic | 1000 | 1
()
Meditate on the "INNER JOIN" keyword to link tables has expanded your awareness.
Awareness expander:
===================
id | name | id | type | customer_id | book_id | datetime
-------+---------------+------+----------+---------------+-----------+------------------
20001 | Paula Vasquez | 1 | Checkout | 20001 | 4 | 2015-10-18 17:32
20001 | Paula Vasquez | 2 | Checkin | 20001 | 4 | 2015-11-27 18:11
20000 | Aaron Meyers | 3 | Checkout | 20000 | 3 | 2015-10-22 14:56
20000 | Aaron Meyers | 4 | Checkin | 20000 | 3 | 2015-10-29 8:22
20003 | Matt Nguyen | 5 | Checkout | 20003 | 3 | 2015-10-29 10:03
20003 | Matt Nguyen | 6 | Checkin | 20003 | 3 | 2015-11-04 11:15
20001 | Paula Vasquez | 7 | Checkout | 20001 | 1 | 2015-11-15 12:00
20001 | Paula Vasquez | 8 | Checkin | 20001 | 1 | 2015-12-08 19:34
20002 | Debra Olson | 9 | Checkout | 20002 | 5 | 2015-11-15 08:44
20002 | Debra Olson | 10 | Checkout | 20002 | 6 | 2015-11-15 08:44
20002 | Debra Olson | 11 | Checkin | 20002 | 5 | 2015-12-01 09:20
20002 | Debra Olson | 12 | Checkin | 20002 | 6 | 2015-11-20 10:13
20002 | Debra Olson | 13 | Checkout | 20002 | 2 | 2015-12-30 11:08
20000 | Aaron Meyers | 14 | Checkout | 20000 | 3 | 2015-12-15 15:57
20000 | Aaron Meyers | 15 | Checkout | 20000 | 6 | 2015-12-15 15:57
20001 | Paula Vasquez | 16 | Checkout | 20001 | 1 | 2015-12-15 13:05
20001 | Paula Vasquez | 17 | Checkin | 20001 | 1 | 2015-12-30 14:59
20000 | Aaron Meyers | 18 | Lost | 20000 | 3 | 2015-12-30 16:46
()
Meditate on the "INNER" being implicit has expanded your awareness.
Awareness expander:
===================
id | name | id | type | customer_id | book_id | datetime
-------+---------------+------+----------+---------------+-----------+------------------
20001 | Paula Vasquez | 1 | Checkout | 20001 | 4 | 2015-10-18 17:32
20001 | Paula Vasquez | 2 | Checkin | 20001 | 4 | 2015-11-27 18:11
20000 | Aaron Meyers | 3 | Checkout | 20000 | 3 | 2015-10-22 14:56
20000 | Aaron Meyers | 4 | Checkin | 20000 | 3 | 2015-10-29 8:22
20003 | Matt Nguyen | 5 | Checkout | 20003 | 3 | 2015-10-29 10:03
20003 | Matt Nguyen | 6 | Checkin | 20003 | 3 | 2015-11-04 11:15
20001 | Paula Vasquez | 7 | Checkout | 20001 | 1 | 2015-11-15 12:00
20001 | Paula Vasquez | 8 | Checkin | 20001 | 1 | 2015-12-08 19:34
20002 | Debra Olson | 9 | Checkout | 20002 | 5 | 2015-11-15 08:44
20002 | Debra Olson | 10 | Checkout | 20002 | 6 | 2015-11-15 08:44
20002 | Debra Olson | 11 | Checkin | 20002 | 5 | 2015-12-01 09:20
20002 | Debra Olson | 12 | Checkin | 20002 | 6 | 2015-11-20 10:13
20002 | Debra Olson | 13 | Checkout | 20002 | 2 | 2015-12-30 11:08
20000 | Aaron Meyers | 14 | Checkout | 20000 | 3 | 2015-12-15 15:57
20000 | Aaron Meyers | 15 | Checkout | 20000 | 6 | 2015-12-15 15:57
20001 | Paula Vasquez | 16 | Checkout | 20001 | 1 | 2015-12-15 13:05
20001 | Paula Vasquez | 17 | Checkin | 20001 | 1 | 2015-12-30 14:59
20000 | Aaron Meyers | 18 | Lost | 20000 | 3 | 2015-12-30 16:46
()
Meditate on join conditions has expanded your awareness.
Awareness expander:
===================
id | name | id | type | customer_id | book_id | datetime
-------+---------------+------+----------+---------------+-----------+------------------
20001 | Paula Vasquez | 1 | Checkout | 20001 | 4 | 2015-10-18 17:32
20001 | Paula Vasquez | 2 | Checkin | 20001 | 4 | 2015-11-27 18:11
20000 | Aaron Meyers | 3 | Checkout | 20000 | 3 | 2015-10-22 14:56
20000 | Aaron Meyers | 4 | Checkin | 20000 | 3 | 2015-10-29 8:22
20003 | Matt Nguyen | 5 | Checkout | 20003 | 3 | 2015-10-29 10:03
20003 | Matt Nguyen | 6 | Checkin | 20003 | 3 | 2015-11-04 11:15
20001 | Paula Vasquez | 7 | Checkout | 20001 | 1 | 2015-11-15 12:00
20001 | Paula Vasquez | 8 | Checkin | 20001 | 1 | 2015-12-08 19:34
20002 | Debra Olson | 9 | Checkout | 20002 | 5 | 2015-11-15 08:44
20002 | Debra Olson | 10 | Checkout | 20002 | 6 | 2015-11-15 08:44
20002 | Debra Olson | 11 | Checkin | 20002 | 5 | 2015-12-01 09:20
20002 | Debra Olson | 12 | Checkin | 20002 | 6 | 2015-11-20 10:13
20002 | Debra Olson | 13 | Checkout | 20002 | 2 | 2015-12-30 11:08
20000 | Aaron Meyers | 14 | Checkout | 20000 | 3 | 2015-12-15 15:57
20000 | Aaron Meyers | 15 | Checkout | 20000 | 6 | 2015-12-15 15:57
20001 | Paula Vasquez | 16 | Checkout | 20001 | 1 | 2015-12-15 13:05
20001 | Paula Vasquez | 17 | Checkin | 20001 | 1 | 2015-12-30 14:59
20000 | Aaron Meyers | 18 | Lost | 20000 | 3 | 2015-12-30 16:46
()
Meditate on limiting columns in joins has expanded your awareness.
Awareness expander:
===================
name | datetime
---------------+------------------
Paula Vasquez | 2015-10-18 17:32
Paula Vasquez | 2015-11-27 18:11
Aaron Meyers | 2015-10-22 14:56
Aaron Meyers | 2015-10-29 8:22
Matt Nguyen | 2015-10-29 10:03
Matt Nguyen | 2015-11-04 11:15
Paula Vasquez | 2015-11-15 12:00
Paula Vasquez | 2015-12-08 19:34
Debra Olson | 2015-11-15 08:44
Debra Olson | 2015-11-15 08:44
Debra Olson | 2015-12-01 09:20
Debra Olson | 2015-11-20 10:13
Debra Olson | 2015-12-30 11:08
Aaron Meyers | 2015-12-15 15:57
Aaron Meyers | 2015-12-15 15:57
Paula Vasquez | 2015-12-15 13:05
Paula Vasquez | 2015-12-30 14:59
Aaron Meyers | 2015-12-30 16:46
()
Meditate on aliasing tables has expanded your awareness.
Awareness expander:
===================
id | name | id | type | customer_id | book_id | datetime
-------+---------------+------+----------+---------------+-----------+------------------
20001 | Paula Vasquez | 1 | Checkout | 20001 | 4 | 2015-10-18 17:32
20001 | Paula Vasquez | 2 | Checkin | 20001 | 4 | 2015-11-27 18:11
20000 | Aaron Meyers | 3 | Checkout | 20000 | 3 | 2015-10-22 14:56
20000 | Aaron Meyers | 4 | Checkin | 20000 | 3 | 2015-10-29 8:22
20003 | Matt Nguyen | 5 | Checkout | 20003 | 3 | 2015-10-29 10:03
20003 | Matt Nguyen | 6 | Checkin | 20003 | 3 | 2015-11-04 11:15
20001 | Paula Vasquez | 7 | Checkout | 20001 | 1 | 2015-11-15 12:00
20001 | Paula Vasquez | 8 | Checkin | 20001 | 1 | 2015-12-08 19:34
20002 | Debra Olson | 9 | Checkout | 20002 | 5 | 2015-11-15 08:44
20002 | Debra Olson | 10 | Checkout | 20002 | 6 | 2015-11-15 08:44
20002 | Debra Olson | 11 | Checkin | 20002 | 5 | 2015-12-01 09:20
20002 | Debra Olson | 12 | Checkin | 20002 | 6 | 2015-11-20 10:13
20002 | Debra Olson | 13 | Checkout | 20002 | 2 | 2015-12-30 11:08
20000 | Aaron Meyers | 14 | Checkout | 20000 | 3 | 2015-12-15 15:57
20000 | Aaron Meyers | 15 | Checkout | 20000 | 6 | 2015-12-15 15:57
20001 | Paula Vasquez | 16 | Checkout | 20001 | 1 | 2015-12-15 13:05
20001 | Paula Vasquez | 17 | Checkin | 20001 | 1 | 2015-12-30 14:59
20000 | Aaron Meyers | 18 | Lost | 20000 | 3 | 2015-12-30 16:46
()
Meditate on multiple joins has expanded your awareness.
Awareness expander:
===================
id | type | datetime | id | name | title
------+----------+------------------+-------+---------------+---------------------------
1 | Checkout | 2015-10-18 17:32 | 20001 | Paula Vasquez | Never Let Me Go
2 | Checkin | 2015-11-27 18:11 | 20001 | Paula Vasquez | Never Let Me Go
3 | Checkout | 2015-10-22 14:56 | 20000 | Aaron Meyers | Moby Dick
4 | Checkin | 2015-10-29 8:22 | 20000 | Aaron Meyers | Moby Dick
5 | Checkout | 2015-10-29 10:03 | 20003 | Matt Nguyen | Moby Dick
6 | Checkin | 2015-11-04 11:15 | 20003 | Matt Nguyen | Moby Dick
7 | Checkout | 2015-11-15 12:00 | 20001 | Paula Vasquez | A Tale of Two Cities
8 | Checkin | 2015-12-08 19:34 | 20001 | Paula Vasquez | A Tale of Two Cities
9 | Checkout | 2015-11-15 08:44 | 20002 | Debra Olson | The Left Hand of Darkness
10 | Checkout | 2015-11-15 08:44 | 20002 | Debra Olson | The Gilded Age
11 | Checkin | 2015-12-01 09:20 | 20002 | Debra Olson | The Left Hand of Darkness
12 | Checkin | 2015-11-20 10:13 | 20002 | Debra Olson | The Gilded Age
13 | Checkout | 2015-12-30 11:08 | 20002 | Debra Olson | Oliver Twist
14 | Checkout | 2015-12-15 15:57 | 20000 | Aaron Meyers | Moby Dick
15 | Checkout | 2015-12-15 15:57 | 20000 | Aaron Meyers | The Gilded Age
16 | Checkout | 2015-12-15 13:05 | 20001 | Paula Vasquez | A Tale of Two Cities
17 | Checkin | 2015-12-30 14:59 | 20001 | Paula Vasquez | A Tale of Two Cities
18 | Lost | 2015-12-30 16:46 | 20000 | Aaron Meyers | Moby Dick
()
Meditate on the "LEFT JOIN" keyword has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available | datetime
------+------------------------------+---------------------+------------------+-------------+------------------
1 | A Tale of Two Cities | Historical drama | 1859 | 1 | 2015-11-15 12:00
1 | A Tale of Two Cities | Historical drama | 1859 | 1 | 2015-12-08 19:34
1 | A Tale of Two Cities | Historical drama | 1859 | 1 | 2015-12-15 13:05
1 | A Tale of Two Cities | Historical drama | 1859 | 1 | 2015-12-30 14:59
2 | Oliver Twist | Novel | 1837 | 0 | 2015-12-30 11:08
3 | Moby Dick | Novel | 1851 | 0 | 2015-10-22 14:56
3 | Moby Dick | Novel | 1851 | 0 | 2015-10-29 10:03
3 | Moby Dick | Novel | 1851 | 0 | 2015-10-29 8:22
3 | Moby Dick | Novel | 1851 | 0 | 2015-11-04 11:15
3 | Moby Dick | Novel | 1851 | 0 | 2015-12-15 15:57
3 | Moby Dick | Novel | 1851 | 0 | 2015-12-30 16:46
4 | Never Let Me Go | Speculative fiction | 2005 | 1 | 2015-10-18 17:32
4 | Never Let Me Go | Speculative fiction | 2005 | 1 | 2015-11-27 18:11
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1 | 2015-11-15 08:44
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1 | 2015-12-01 09:20
6 | The Gilded Age | Novel | 1873 | 1 | 2015-11-15 08:44
6 | The Gilded Age | Novel | 1873 | 1 | 2015-11-20 10:13
6 | The Gilded Age | Novel | 1873 | 1 | 2015-12-15 15:57
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1 | None
8 | Beowulf | Epic | 1000 | 1 | None
()
Meditate on ONE-TO-MANY relationships has expanded your awareness.
Awareness expander:
===================
id | name | id | type | customer_id | book_id | datetime
-------+---------------+------+----------+---------------+-----------+------------------
20001 | Paula Vasquez | 1 | Checkout | 20001 | 4 | 2015-10-18 17:32
20001 | Paula Vasquez | 2 | Checkin | 20001 | 4 | 2015-11-27 18:11
20001 | Paula Vasquez | 7 | Checkout | 20001 | 1 | 2015-11-15 12:00
20001 | Paula Vasquez | 8 | Checkin | 20001 | 1 | 2015-12-08 19:34
20001 | Paula Vasquez | 16 | Checkout | 20001 | 1 | 2015-12-15 13:05
20001 | Paula Vasquez | 17 | Checkin | 20001 | 1 | 2015-12-30 14:59
()
Meditate on MANY-TO-ONE relationships has expanded your awareness.
Awareness expander:
===================
id | type | customer_id | book_id | datetime | id | title
------+----------+---------------+-----------+------------------+------+----------------------
7 | Checkout | 20001 | 1 | 2015-11-15 12:00 | 1 | A Tale of Two Cities
8 | Checkin | 20001 | 1 | 2015-12-08 19:34 | 1 | A Tale of Two Cities
16 | Checkout | 20001 | 1 | 2015-12-15 13:05 | 1 | A Tale of Two Cities
17 | Checkin | 20001 | 1 | 2015-12-30 14:59 | 1 | A Tale of Two Cities
()
Meditate on MANY-TO-MANY relationships has expanded your awareness.
Awareness expander:
===================
first_name | last_name | title
----------------+-------------+------------------------------
Charles | Dickens | A Tale of Two Cities
Charles | Dickens | Oliver Twist
Mark | Twain | The Gilded Age
Charles Dudley | Warner | The Gilded Age
Mark | Twain | The Adventures of Tom Sawyer
()
Meditate on changing data with "UPDATE" has expanded your awareness.
Awareness expander:
===================
Query executed successfully and did not return any data.
()
Meditate on removing data with "DELETE" has expanded your awareness.
Awareness expander:
===================
Query executed successfully and did not return any data.
()
Meditate on using subqueries with IN has expanded your awareness.
Awareness expander:
===================
id | type | customer_id | book_id | datetime
------+----------+---------------+-----------+------------------
18 | Lost | 20000 | 3 | 2015-12-30 16:46
14 | Checkout | 20000 | 3 | 2015-12-15 15:57
15 | Checkout | 20000 | 6 | 2015-12-15 15:57
4 | Checkin | 20000 | 3 | 2015-10-29 8:22
3 | Checkout | 20000 | 3 | 2015-10-22 14:56
()
Meditate on WITH to create a Common Table Expression query has expanded your awareness.
Awareness expander:
===================
id | type | customer_id | book_id | datetime
------+----------+---------------+-----------+------------------
18 | Lost | 20000 | 3 | 2015-12-30 16:46
14 | Checkout | 20000 | 3 | 2015-12-15 15:57
15 | Checkout | 20000 | 6 | 2015-12-15 15:57
4 | Checkin | 20000 | 3 | 2015-10-29 8:22
3 | Checkout | 20000 | 3 | 2015-10-22 14:56
()
Meditate on using subqueries for deleting data has expanded your awareness.
Awareness expander:
===================
Query executed successfully and did not return any data.
()
Meditate on counting rows with COUNT has expanded your awareness.
Awareness expander:
===================
count(*)
------------
8
()
Meditate on counting within groups with GROUP BY has expanded your awareness.
Awareness expander:
===================
genre | count(*)
---------------------+------------
Epic | 1
Historical drama | 1
Novel | 3
Satire | 1
Speculative fiction | 2
()
Meditate on MAX has expanded your awareness.
Awareness expander:
===================
max(datetime)
------------------
2015-12-30 16:46
()
Meditate on MIN has expanded your awareness.
Awareness expander:
===================
min(datetime)
------------------
2015-10-18 17:32
()
Meditate on getting the maximum within groups has expanded your awareness.
Awareness expander:
===================
genre | max(year_published)
---------------------+-----------------------
Epic | 1000
Historical drama | 1859
Novel | 1873
Satire | 1876
Speculative fiction | 2005
()
Meditate on joining data with aggregates has expanded your awareness.
Awareness expander:
===================
name | count(*)
---------------+------------
Aaron Meyers | 5
Debra Olson | 5
Matt Nguyen | 2
Paula Vasquez | 6
()
Meditate on aliasing columns: present the year_published column as "Publication Date" has expanded your awareness.
Awareness expander:
===================
title | Publication Date
------------------------------+--------------------
A Tale of Two Cities | 1859
Oliver Twist | 1837
Moby Dick | 1851
Never Let Me Go | 2005
The Left Hand of Darkness | 1969
The Gilded Age | 1873
The Adventures of Tom Sawyer | 1876
Beowulf | 1000
()
Meditate on sorting with "ORDER BY": sort by customer name has expanded your awareness.
Awareness expander:
===================
id | name
-------+---------------
20000 | Aaron Meyers
20002 | Debra Olson
20003 | Matt Nguyen
20001 | Paula Vasquez
()
Meditate on multiple sorts: sort by genre, then title has expanded your awareness.
Awareness expander:
===================
id | title | genre | year_published | available
------+------------------------------+---------------------+------------------+-------------
8 | Beowulf | Epic | 1000 | 1
1 | A Tale of Two Cities | Historical drama | 1859 | 1
3 | Moby Dick | Novel | 1851 | 0
2 | Oliver Twist | Novel | 1837 | 0
6 | The Gilded Age | Novel | 1873 | 1
7 | The Adventures of Tom Sawyer | Satire | 1876 | 1
4 | Never Let Me Go | Speculative fiction | 2005 | 1
5 | The Left Hand of Darkness | Speculative fiction | 1969 | 1
()
Meditate on "DESC" keyword has expanded your awareness.
Awareness expander:
===================
id | type | customer_id | book_id | datetime
------+----------+---------------+-----------+------------------
18 | Lost | 20000 | 3 | 2015-12-30 16:46
17 | Checkin | 20001 | 1 | 2015-12-30 14:59
13 | Checkout | 20002 | 2 | 2015-12-30 11:08
14 | Checkout | 20000 | 3 | 2015-12-15 15:57
15 | Checkout | 20000 | 6 | 2015-12-15 15:57
16 | Checkout | 20001 | 1 | 2015-12-15 13:05
8 | Checkin | 20001 | 1 | 2015-12-08 19:34
11 | Checkin | 20002 | 5 | 2015-12-01 09:20
2 | Checkin | 20001 | 4 | 2015-11-27 18:11
12 | Checkin | 20002 | 6 | 2015-11-20 10:13
7 | Checkout | 20001 | 1 | 2015-11-15 12:00
9 | Checkout | 20002 | 5 | 2015-11-15 08:44
10 | Checkout | 20002 | 6 | 2015-11-15 08:44
6 | Checkin | 20003 | 3 | 2015-11-04 11:15
4 | Checkin | 20000 | 3 | 2015-10-29 8:22
5 | Checkout | 20003 | 3 | 2015-10-29 10:03
3 | Checkout | 20000 | 3 | 2015-10-22 14:56
1 | Checkout | 20001 | 4 | 2015-10-18 17:32
()
Curts-MBP:sql-koans cgrogan$
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment