Category Archives: SQL

  1. Pages:
  2. 1
  3. 2
  4. 3
  5. 4
  6. 5

POW – Explain That SQL #1

Posted on by
1

Someone asked me to quickly write them some code in any language that would accomplish a certain task. The following, which is in PostgreSQL, is the second solution that I came up with after doing the first one in my favorite language, JavaScript:

SELECT ARRAY_TO_STRING(ARRAY(
    SELECT CHR(num
        + CASE
            WHEN num < 26 THEN 65
            WHEN num BETWEEN 26 AND 51 THEN 71
            ELSE -4
          END
      )
    FROM (
      SELECT FLOOR(RANDOM() * 62)::INT AS num,
        generate_series(1, 12)
    ) AS t
  ), '')

The question is, what does this SELECT statement actually do? If you were asked to write that as a JavaScript, substituting the 12 for a variable passed into the function, what would your function look like?

As usual, the answer to this Problem of the Week will be made available a week from today, on Wednesday, August 22, 2012.

The answer to this Problem of the Week can now be found here.

POW – Fill In The Blanks In SQL

Posted on by
1

One of the things I always find interesting is the fact that DBAs almost always use some type of auto-increment method in order to generate unique IDs for records. Sometimes, though, you may have a table in which you don’t want to use the default method of generating the next ID in the sequence. Sometimes the goal is to re-use old IDs that are no longer in use. The goal this week will be to create a SELECT statement in any flavor of SQL you want, that will fetch the smallest non-negative positive integer that has not yet been used. The column will be called id while the table will be called stars. What is your most efficient way that you can think of the accomplish this task using just a SELECT statement (not a self-written function)?

The answer to this Problem of the Week is here.

PostgreSQL – Sum Of An Array

Posted on by
Reply

Something I had to do today was figure out how to find the SUM of an array in PostgreSQL. I went through many different ways, but this seemed to be the best:

SELECT SUM(t) FROM UNNEST(ARRAY[1,2,3,4,5,6,7,8,9]) t;

If you know of a shorter or better way to do this for a variable size array please let me know in the comments section below. If it is better, I will post it and of course give you all the credit. 8)

PostgreSQL – Granting Access To All Views

Posted on by
Reply

Yet another request came to me today for granting SELECT permissions. This time, the request was to give the user the correct privileges to use a view. Basically, you can use the same format that is used for tables as is used for views:

SELECT 'GRANT SELECT ON ' || quote_ident(schemaname) || '.' || quote_ident(viewname) || ' TO john_doe;'
FROM pg_views
WHERE schemaname = 'public';

After running a query like this one, you will be given all of the GRANT statements needed to give SELECT permissions to all of the views in the public schema.

  1. Pages:
  2. 1
  3. 2
  4. 3
  5. 4
  6. 5