Interesting //

By: Fiebert  09-12-2011
Keywords: Brain Teaser, Interview Tests

I was asked this question a few months ago and liked it due to its simplicity, and how it can be done on paper which is how we’ve been administering our interview tests (this is currently not on my test).

Imagine you have a packaging system and there is a table that defines what packages a user can access. There are two columns uid (user id) and pid (package id). Every row in this table provides a specific user with access to a package, for example there may be rows [1,1] or [3,5] where as user 1 has access to package 1 or user 3 has access to package 5. A user can have access to multiple packages which would result in rows like [1,1], [1,2], [1,3], etc… However, a user will be considered a “Super User” if they have a pid of -1 assigned to their user id. Often times a user is given access to multiple packages only to later be given “Super User” access. Write a query for this table to display all user access however if a user is a “Super User”, only display the one row for them demonstrating that they are a “Super User”.

Now, more often then not people will come up with some hackish query involving “UNION” or “IN”. When they do, tell them both are horrible for performance if not for any reason other then there are multiple executions within one query. Ask them to try and rewrite it using joins and not “UNION” or “IN”. This brain teaser is simple and should demonstrate their confidence with joins and ability to think beyond “Step 1, Step2″ answers. There is no one right answer, I’ve included mine below:

SELECT DISTINCT a.uid, isnull(b.vid,a.vid)FROM permissions AS a
LEFTJOIN(SELECT uid, vid FROM permissions WHERE vid=-1)AS b
ON a.uid=b.uid

Keywords: Brain Teaser, Interview Tests

Other products and services from Fiebert


Rants //

The problem is this time that I don’t have the option to edit the code after the query has been returned, I need the actual query results to have NULL removed from them. Now this isn’t the first time I’ve gotten NULL as a result and usually my code has an if statement in there to swap out the NULL values with zero. You can use COALESCE in any part of your query, it doesn’t only have to be where you specify the columns you want returned.



This would return true for every row giving us every single credit card number in the table, and a similar attack would work equally well on an INSERT, DELETE or UPDATE query. PHP has two built in functions addslashes and stripslashes, however you must add slashes to every incoming variable and strip the slashes off of every outgoing variable.


The Site //

First, I’m going to redesign the site to fit more of where I see it going, then I’ll show you my current project cinder, which is basically a random name for the existing project that I came up with right now. Whether or not it worked is another story but I’ve got “The Job” so I think it is about time to repurpose the site. It goes along with the whole computer agnostic trend the web has taken these days.


Database //

A common issue among dataservers is disk space so it only makes sense to monitor for thresholds so that you can fix an issue before it becomes an emergency. We will construct the remote query dynamically in the code before openrowset and the remote instance will be passed into the instance upon execution. When you must maintain hundreds of machines, space trending is practically a requirement.


Wordpress //

I’ve totally redesigned / restructured the site from the last theme with high hopes that I can make a solid foundation to build on in the future. I like it because it’s fully extensible and very easy to build on using just the theme alone. I’ve spent a lot of time lately working with wordpress and it’s pretty awesome.