The SQL Job Interview

Jitbit Software recently shared the SQL questions that ask potential employees at interviews. They give you a small schema and ask you to write the SQL queries to achieve a few goals. I looked through the questions. Some were easy. Others had me sweating. I bet I could hack through most of them. But if I was under pressure in an interview, I don't think I could solve them all. Oh oh.

It seems this test is to weed out developers who do database programming, but just click buttons on an ORM tool. You need more than that kind of knowledge if you want to tune SQL statements. Personally I don't want to tune SQL. There are specialists for that. I like writing apps that serve business needs. Yeah I try to make sure my SQL works fast enough to not be a problem. I even submit my queries to DBA peer review just in case I overlook anything.

Maybe it is time to brush up on the old plain SQL skills. The first step should be to install a free Oracle database on the computer I use the most. Used to have one on there. Then I got a new laptop, and haven't got around to doing this chore. The time is now. My SQL skills may depend on it. By the way, I don't ever use an ORM myself. I roll all my own SQL. Turns out that my SQL statements are usually quite basic indeed.

You Are Doing It Wrong

A friend got me into playing this online game. It is turn based. Slowly I have risen through the ranks. My character level is up. Often I want to compare my progress against the other players. Fine. I go to the ratings page.

Most of the time, I get an error message stating that I have accessed the ratings page too often. Then there is some whining about the page taking a long time to generate. I guess I am supposed to come back after a few days. What the heck is this? Some noob database programmers I guess.

From what I gather, the game was hacked together with some PHP. I think I may be able to get my hands on the code. There must be a MySQL database on the back end. Now there are around 1000 player total. I imagine they are running a query that orders players by level and maybe experience.

How hard could this query be? I find there are usually only 10 players on at a given time. I bet most of them are not clicking on the ratings page. Can we somehow cache this data? Heck they could even generate a static HTML page that gets refreshed every so often. Performance problem solved.

From a database point of view, why not create a materialized view? Then refresh that view at your leisure, as resources allow. Nobody should need up to the minute real-time player rakings. And if they do, charge them a premium. Right now I scraped the ratings pages. Going to play with the data a bit. Then I need that source code.

Database Design

One of the developers on our team was assigned to design and code a new feature in the system. This particular feature was not too complex. However in the past, this developer only did some coding. He called me up and asked me how he should implement the changes. I told him he should understand the requirements, and put together a design. He was very nervous about this. He shouldn't be. The guy has a PhD in computer science. He is was also hired as a senior developer. This should be a cake walk.

The guy came up with a design to add a column to one of our tables to track the timing of when to do some follow on tasks. That sounded good until he wanted to set the value in a trigger. Unfortuantely the trigger was on the table he was trying to modify. He found this did not work, as it was illegal in the specific trigger he was modifying. Then he decided he needed a small additional table to track the timing.

Unfortunately, the database changes went into review by an external team. They found the design lacking. This external team came up with all kinds of other designs to solve the problem. Unfortunately this was late in the development life cycle. That's where I got dragged into the scene. I took a look at all the proposed designs. Each of them would work. In the end, the main driver for sticking with the original design was that the work was already complete.

The outside team got some recommendations in though. There were be a history of the timing. There would be extra columns tracking who did what. Now that little table is getting bigger and more complex. It still will achieve the business objective. And the work is just about done. The moral of the story is that you have to practive doing designs before you can produce solid ones.

Hitting Max Users

My boss called me up this morning. The customer had a high priority issue. Our software was not allowing them to create new users in the system. The only lead I had was that our software reported a -1985 error. Now I know that number is not one of our internal error numbers. So it had to be an Oracle error. ORA-01985 means we have reached LICENSE_MAX_USERS. Oh boy.

I could not interrogate the production database. No rights. A DBA eventually got on and found that the parameter was set to 2001. Whoa. We have more than 2k users? Well the solution is to bump up the parameter. Initially the boss man said we could rev this param value up to 5000. Then after hearing the current value was 2001, he thought 3001 was a good new number.

I played around in a development database. Figured someone could issue an ALTER SYSTEM SET LICENSE_MAX_USERS = 1. Luckily a DBA came on and added a SCOPE BOTH to the end of that command to ensure it sticks after the database is rebooted. I am not a DBA. So I don't know much about the server parameter file (spfile). I just know that when we makes changes like this, it needs to stick immediately and permanently. That must be what SCOPE BOTH controls.

TNS Names Alias Cache

Our DBA team keeps a TNSnames.ora file out on a network drive. They maintain it with all the databases anyone would need to connect to. All the developers use this copy of the file to map TNS alias names. Today one developer found out that when he first boots up, our application cannot seem to properly access that file.

The developer said he must first access the network share with Windows Explorer before any of our apps can use the share to get to the TNSnames.ora file. He thinks that maybe Windows need to cache the network share information or something. That did not sound like a sound theory. But the evidence was plain. He said the first time after a reboot, he always get a database not found error. The second time he tries, our apps work fine.

Well there may be some truth to this theory. However he went on to generalize that maybe the users of our system might encounter some problem when cached data is not present, and a database call times out. That just does not seem to have a leg to stand on though.

For starters, we put the TNSnames.ora file on the local C: disk drive of our customer' computers. So there should be no delay in accessing that file. Sure the database server is far away, accessed over the network. However I cannot believe that some delay will cause a timeout resulting in random application errors.

Garbled Data

Our test team was trying to recreate some problems detected by our customers. They were having a hard time. So they took a look around at the data in the different tables in this part of the subsystem. They found the main table that drives the processing. Unfortunately, most of the data seemed to be all garbled up.

They were using SQL Developer. One of the testers wondered if this could be a problem with the query tool. That was doubtful. But hey. It could not hurt to try viewing the data with another tool. So another tester pulled out TOAD and tried to bring up the data. Most of the data seemed empty.

The testers figured that SQL Developer was just displaying all the data, regardless of how strange it was. TOAD on the other hand, was just supressing the display of weird looking data. This did not help us understand why the data was bad. It just was an interesting observation.

The data in the main table was SQL*Loaded from a file the testers were creating. Further inspection of this file showed that the positioning was incorrect. Perhaps that was the source of the weird looking data. Further research is required.

Ad Hoc Reporting

I had written a script to restore some data that had been lost. A team convened to run my script. The thing completed very quickly. After some spot checks, we determined my script was a success. The DBAs left the call shortly after that. There was one last task left. My boss wanted me to write some SQL to produce a comma separated file with the data.

That sounded simple enough. SPOOL the data out to a file. The main command was a big SELECT statement which included all the column values separated by commas. I gave it a try in development and found the lines were wrapping. So I SET LINESIZE to 200. We run the job in the production environment.

Ooops. The headers are showing up on every page. Should have SET HEADING OFF. Then we import the data into Microsoft Excel. Some rows are not lining up correctly. The LINESIZE needed to be bigger. Maybe set it to 2000? Is that too high to work? Also some of the data had commas within the values. Oh man this is getting tricky.

In the end I resorted to a tool like PL/SQL Developer to do the trick. It can export data with ease.