Tricky SQL Test: Part 2

This is Part 2 of the three part series detailing the ‘tricky SQL test’ I took to get a potential job.  Read Part 1 of the series here.  This one is much easier than Part 1.  I got most of the credit for this one, but I couldn’t figure out the group by to limit the number previous results.

The Test

Given a table events with the follow structure:

    cameraId INT NOT NULL,
    warningType INT NOT NULL,
    warningLevel INT NOT NULL,
    warningTime DATETIME NOT NULL,
    UNIQUE INDEX warningTime (warningTime)

insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 2, 8, '2017-09-13 13:42:00');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 4, -46, '2017-09-13 14:19:57');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 2, 7, '2017-09-13 15:48:30');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (3, 2, 3, '2017-09-13 13:54:39');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 3, 38, '2017-09-13 14:32:36');

Write an SQL query that, for each camera and warning type (cameraId, warningType), returns the most recent value (in terms of time).  the table should be ordered by cameraId (in ascending order), and warningType (in ascending order).

For example, given the following data:

cameraId   | warningType | warningLevel | warningTime
2          | 2           | 5            | 2017-09-13 13:42:00
2          | 4           | -46          | 2017-09-13 14:19:57
2          | 2           | 7            | 2017-09-13 15:48:30
3          | 2           | 3            | 2017-09-13 13:54:39
2          | 3           | 38           | 2017-09-13 14:32:36

Your query would return:

cameraId | warningType | warningLevel
2        | 2           | 7
2        | 3           | 38
2        | 4           | -46
3        | 2           | 3

The names of the columns in the rowset don’t matter, but their order does.

How to change keyboard shortcuts in Oracle SQL Developer

It is quite easy to assign keyboard shortcuts to actions in SQL Developer.  I recently configured shortcuts to convert text to all UPPER and all lower case.

Simply go to Tools → Preferences and navigate to Shortcut Keys.  On the right there will be a lengthy list of actions to choose from.  For converting to upper/lower case look in the Code Editor category for Convert Selection to Lower/Uppercase.

Tricky SQL Test: Part 1

Not long ago I went out looking for some freelance work to do in my spare time.  I found a list of “the top destinations for freelance work.”  I picked one of them and signed up.  I made it thought the initial screening, then it was on to THE TEST.  This is a bit of an “Ohh crap” moment.  In technology and I.T. It is common practice to have applicants take tests.  You can tell much about your prospective employer by these test.  Some of them give you tests relative to the job they are interviewing for.  Others give you tests which are unnecessarily difficult.  I’ve found employers in the second usually do this for two reasons. One, they want to test how you solve problems and come to conclusions.  Two, they are hiring for a low or entry level position.  Since people applying for these positions don’t generally have much experience to promote, so they are given difficult tests.

In the first of a three article series I will review some of the tests I’ve encountered.  The tests are for SQL.  The platform for these particular tests is MySQL.

The first test looks at calculating H-Indexes.  Don’t worry, I didn’t know what an H-Index was when I took this test either.  If you care to read up before going  any further check out H-Indexs according to Wikipedia.

The Test:

You are given two tables, author and publication, with the following structure:

CREATE TABLE `character` (
 `name` VARCHAR(30) NOT NULL,
 UNIQUE INDEX `id` (`id`)

CREATE TABLE `location`
  `characterId` INT NOT NULL,
  `locationName` VARCHAR(50) NOT NULL,
  `visited` INT NOT NULL,
  UNIQUE INDEX `IX_Location` (`characterId`, `locationName`)

insert into `character` (`id`, `name`) values (1, 'Homer');
insert into `character` (`id`, `name`) values (2, 'Marge');
insert into `character` (`id`, `name`) values (3, 'Bart');
insert into `character` (`id`, `name`) values (4, 'Lisa');
insert into `character` (`id`, `name`) values (5, 'Maggie');

insert into `location` (`characterId`, `locationName`, `visited`) values (1, 'Springfield Nuclear Plant', 30);
insert into `location` (`characterId`, `locationName`, `visited`) values (1, 'Shelbyville', 12);
insert into `location` (`characterId`, `locationName`, `visited`) values (1, 'Moes Tavern', 5);
insert into `location` (`characterId`, `locationName`, `visited`) values (1, 'Kwik-E-Mart', 3);
insert into `location` (`characterId`, `locationName`, `visited`) values (3, 'Krusty Burger', 0);
insert into `location` (`characterId`, `locationName`, `visited`) values (4, 'Springfield Elementary', 2);
insert into `location` (`characterId`, `locationName`, `visited`) values (4, 'The Jazz Hole', 2);

Records in the table character represent characters.  Records in the table location represents a location visited by a character from the character table.  For each character, the number of locations visited is known.

The H-index of a character is the largest number H such that the character has visited at least H locations, each of which has been visited at least H times.

Write a SQL query that returns a table containing all the characters (characterId and name) appearing in the table character and their respective H-indices (hIndex).  The table should be ordered by h_index (In descending order), characterId (In descending order).

 characterId | characterName | hIndex
           1 | Homer         |       3
          15 | Lisa          |       2
          42 | Bart          |       0
          24 | Maggie        |       0
          12 | Marge         |       0

In this example, considering the locations visited by Homer, no four locations have been visited four times or more, so Homers’s H-Index has to be lower than 4.  However, there are at lest three locations that have been visited three times or more (In fact, there are four such locations: Springfield Nuclear Plant, Shelbyville, Moes Tavern and Kwik-E-Mart).  Therefore, Homer’s H-Index is 3.

The author Double has published one title (Single) that has been cited once, so Double’s H-Index is 1.

Lisa has visited two locations (Springfield Elementary and The Jazz Hole) twice, so Lisa’s H-Index is 2.

The remaining characters have not visited anything, so they should not appear in the result table.  Or appear with a value of 0.

Fidget Spinners!

I know I’m about three months late to the party, but I just got my first fidget spinner a couple of weeks ago.  This thing is awesome!  I am a fidgety person.  When I am sitting at my desk I usually have something out to mess with while I tinker around on the computer.  I don’t know… it helps me think, or it helps me procrastinate.

The one I purchased consists of four ball bearings.  One on each post and one in the middle.  The one in the middle has a plastic cap to get a better grip on.  The other three don’t seem to serve much purpose except to grab on the the end and dangle the thing.

According to the Wikipedia article “The toy has been advertised as helping people who have trouble with focusing or fidgeting by relieving nervous energy or psychological stress. As of May 2017, there is no scientific evidence that they are effective as a treatment for autism or ADHD.”  But, I don’t need scientific evidence, this thing is awesome.

I got one on Amazon here.

Fidget Spinner
Fidget Spinner In Action!


Hello! Welcome to my blog. This is actually the 4th or 5th blog I’ve started. All of which are no longer published. But this time I really mean it! Maybe. Let’s hope I’m not screwing around on the server one day and blow this thing up without a backup.

Recently my work has me learning a lot of new things in the area of computer technology. I am also learning a lot about new industries that I have not had much exposure to before. When I am not working I like tinker with things, both computers and mechanical gadgets. I want to document what I learn and how I solve the problems I encounter. I also want to take note of the interesting things I encounter on the side.

Secondly, I have been pondering life a lot recently. I recently became a father and life has changed a lot for me in the last six months. I’ve decided it might be helpful to write my thoughts down more often if only to maintain a little sanity and keep things in perspective. I have been thinking a lot about the concept of ‘pay it forward.’ This is more than just paying for the order of the person behind you in the drive thru. To me, this involves spending time and sharing knowledge with others with the goal of that person benefiting from what has been shared. Maybe someone will stumble across my writing here and it will help them solve a problem or give them a perspective that helps with an issue. So, that’s it. Enjoy!