Download the records and meetings tables used in discussion here. To load it into SQLite3, you should run the following command (in the same folder as disc10.sql):

sqlite3 --init disc10.sql

Now, you should be able to see what the table looks like:

sqlite> .schema
CREATE TABLE records(
  name,
  division,
  title,
  salary,
  supervisor
);
CREATE TABLE meetings(division, day, time);

Question 1

Write a recursive select statement to compute the powers of two from $2^0, 2^1, \cdots 2^{10}$. Hint: Be careful about your stopping condition!

WITH twos(exp) AS (
  -- YOUR CODE HERE
)

SELECT exp FROM twos;
1
2
4
8
16
32
64
128
256
512
1024

Toggle Solution

WITH twos(exp) AS (
  SELECT 1 UNION
  SELECT 2 * exp FROM twos WHERE exp < 1024
)
SELECT exp FROM twos;

Question 2

Write a SQL statement that finds all perfect squares from 156 to 1145.

CREATE TABLE squares AS
  WITH naturals(n) AS (
    SELECT __________ UNION
    SELECT __________
      FROM naturals
      WHERE ____________________;
  )
  SELECT __________ AS n
    FROM ______________, ______________
    WHERE ____________________;

SELECT * FROM squares
169
196
...
1024
1089

Toggle Solution

CREATE TABLE squares AS
  WITH naturals(n) AS (
    SELECT 1 UNION
    SELECT n + 1
      FROM naturals
      WHERE n < 1145
  )
  SELECT b.n AS n
    FROM naturals AS a, naturals AS b
    WHERE a.n * a.n = b.n AND b.n > 156;

Question 3

We can use SQL to determine the anagrams of a word! Specifically, let’s use SQL to find the anagrams of the word cats.

WITH given(char, weight) AS (
  SELECT 'c', 1 UNION
  SELECT 'a', 10 UNION
  SELECT 't', 100 UNION
  SELECT 's', 1000
)
SELECT ________________________________________
FROM __________________________________________
WHERE _________________________________________;

Toggle Solution

WITH given(char, weight) AS (
  SELECT 'c', 1 UNION
  SELECT 'a', 10 UNION
  SELECT 't', 100 UNION
  SELECT 's', 1000
)
SELECT a.char || b.char || c.char || d.char
FROM given AS a, given AS b, given AS c, given AS d
WHERE a.weight + b.weight + c.weight + d.weight = 1111;