Data Problems

gistfile1.txt

Schema management; how do you manage "n" database schemas being managed?

Production Example
------------------
UPDATE project_affiliation pa
   SET hasCommented = 1
 WHERE ISNULL(pa.endedAt)
   AND hasCommented <> 1
   AND EXISTS (
    SELECT 1
    FROM screen s
    INNER JOIN conversation cv on cv.screenID = s.id
    INNER JOIN comment c on c.conversationID = cv.id
    WHERE s.projectID = pa.projectID AND c.userID = pa.userID
    );

-- Extra info
-- Explain plan
+----+--------------------+-------+------+----------------------------------------------------------+-------------------------------------+---------+--------------------------+----------+-------------+
| id | select_type        | table | type | possible_keys                                            | key                                 | key_len | ref                      | rows     | Extra       |
+----+--------------------+-------+------+----------------------------------------------------------+-------------------------------------+---------+--------------------------+----------+-------------+
|  1 | PRIMARY            | pa    | ALL  | NULL                                                     | NULL                                | NULL    | NULL                     | 54252816 | Using where |
|  2 | DEPENDENT SUBQUERY | s     | ref  | PRIMARY,projectID_isArchived_clientFilename              | projectID_isArchived_clientFilename | 4       | invisionapp.pa.projectID |       10 | Using index |
|  2 | DEPENDENT SUBQUERY | cv    | ref  | PRIMARY,screenID                                         | screenID                            | 4       | invisionapp.s.id         |        1 | Using index |
|  2 | DEPENDENT SUBQUERY | c     | ref  | conversationID_createdAt,conversationID_userID_createdAt | conversationID_createdAt            | 4       | invisionapp.cv.id        |        1 | Using where |
+----+--------------------+-------+------+----------------------------------------------------------+-------------------------------------+---------+--------------------------+----------+-------------+


Given the following schema, solve the problems below:

+---------------+  +------------+
| project       |  | screen     |
+---------------+  +------------+
| id            |  | id         |
| name          |  | name       |
+---------------+  | project_id |
                   +------------+

DML
---

1) Retrieve a list of project names and a count of screens associated with the project,
   for projects that contain "awesome" in the name:
   
SELECT p.name,
       (SELECT COUNT(*) FROM screen s WHERE s.project_id = p.id) screen_count
  FROM project p
 WHERE p.name LIKE '%awesome%'

-- or --

SELECT p.name,
       COUNT(s.id) screen_count
  FROM project p LEFT OUTER JOIN screen s ON p.id = s.project_id
 WHERE p.name LIKE '%awesome%'
 GROUP BY p.name

-- or --

SELECT p.name,
       COUNT(s.id) screen_count
  FROM project p LEFT JOIN screen s ON p.id = s.project_id
 WHERE p.name LIKE '%awesome%'
 GROUP BY p.id

2) Retrieve a list of project names for projects that have more than 42 screens, ordered by name:

SELECT p.name
  FROM project p INNER JOIN screen s ON p.id = s.project_id
 GROUP BY p.id
 HAVING COUNT(s.id) > 42
 ORDER BY p.name


3) Retrieve all screen ids for a project that has any screen with the name of "foo":
SELECT s.id
  FROM project p
       INNER JOIN screen s ON p.id = s.project_id
 WHERE EXISTS (SELECT 1
                 FROM screen s2
                WHERE p.id = s2.project_id
                  AND s2.name = 'foo')

DDL
---

1) Add the column "has_screens" to the project table and populate the column with the correct value (1 or 0):

ALTER TABLE project ADD has_screens BIT NULL DEFAULT 0;
UPDATE project SET has_screens = 0;
ALTER TABLE project CHANGE has_screens BIT NOT NULL DEFAULT 0;

2) Create an index on name column of the project table:

ALTER TABLE project ADD INDEX idx_name (name)

Programming
-----------

1) Write a routine that returns all pairs of numbers in an array that differ by a given value.  For the following array:
{-3,0,1,3,5} and a value of 4, the following should be returned: {-3,1}, {1,5}

2) Write a routine that returns an array of start and end indexes given a start (inclusive) and end index (inclusive) and an increment.  For example the following inputs: start=1,end=25,increment=10 should return {1,10}, {11,20}, {21,25}