Database Administrator Interview Question - Intermediate Level

Level 2: The Researcher
You now can navigate a database backend at will, and depending on the rest of your skill sets, can access things that may or may not have you locked up in a federal penitentiary. However, when someone comes asking you for a question, you are the person they rely on for answers fast and accurately. You may end up with some odd overtime, but hey at least the medical’s good right?

• What is a ‘join’?

Well when two tables love each other very much...not that much happens actually. However when you need to search across multiple tables simultaneously, a join can help make that happen. For example, if you were searching for information on a particular product and one table has the description while the other has pricing information, you can use a join to search across both tables simultaneously using a single query.

• What is a foreign key?

When using a join or other type of query that goes across multiple tables, it can sometimes be difficult to make sure they are talking on the same page. A primary key can help with this, but sometimes this is impractical, and thus you need a secondary value that is consistent across multiple tables. For example, say that in a series of tables for product listings you have your primary key assigned to an auto-increment ID based on when the product was entered (a typical setup), and then none of these rows are able to line up with their counterparts in other tables. So if you have one table for product listings, another for price information, another for reviews, etc. — this could be a fairly major problem. However, if you know for a certainty that your part numbers for these products are going to be unique values, you can use that as a foreign key and suddenly everything lines up all nice and neat. This is possible since it exists in more than one table, and since is being referenced from outside its own table; it is designated ‘foreign’. This does not mean it still could not be the primary key for that particular table as well, it just means it has a reference that can be looked to from another point of view.

• What is SQL Injection?

Also known as asking a question and getting the answer you want, rather than the answer they want to give you (anybody that has tried to navigate certain nameless support phones knows that this isn’t necessarily a bad thing); however in the context of a database application, this can be “a very bad thing”TM. For

instance, say that you are on an online banking website. You’re at the login screen, and it is waiting for you to enter your login and password so it can display your particular financial information. But what if you want to see the listing of everybody else that banks at this particular location? Depending on how the bank’s site is hardened against such an attack, you could get their personal information, current balances, PIN numbers, or even worse, enter your own data directly into the database — able to create new accounts, set up transaction history, active balances, the list goes on and on.

• What is input sterilization?

One of the main answers to SQL Injection, input sterilization allows the database to selectively ignore data coming in from an input field and strip out non- required data. For example, if a field is expecting only a numeric value, there is no need for letters or symbols to be present in the user input. Therefore, these values can be safely ignored but still keep the functionality of the form intact. While not an end-all beat-all, it goes a long way to helping mitigate attacks on this vector.

• SQL Vs NoSQL

NoSQL (Also called Not Only SQL), is a different form of database than the standard relational type. While it can use a lot of the same kinds of query language, it doesn’t necessarily use the same type of table structure that standard relational databases use, and thus in some cases can be more efficient. That efficiency depends greatly on its application however, and many times you will see NoSQL used in Big Data crunching and analysis applications that require real-time feedback.

• What is ‘Big Data’?

DATA. If you’ve ever shopped on Amazon or at a Walmart, searched on Google or been on Facebook for more than 10 minutes, then you’ve seen Big Data in action. Big Data is essentially looking at the forest for the forest instead of the trees. An individual person is a unique entity with a specific set of actions and reasons for why they do what they do. Tracking an individual person’s actions can sometimes be useful, however it’s also a shot in the dark. But multiply that by many, many millions and suddenly the individual actions don’t matter as much — yet patterns start to emerge. A good example of this was published in the New York Times: Walmart discovered that just prior to a major storm, there was a run on the usual items such as bottled water, batteries and flashlights — but also strawberry pop tarts. This pattern was consistent across the board, so they were able to bundle these items together in certain parts of the store and increase profits. Amazon Suggestions, Google Analytics and other entities that run off of Big Data are huge moneymakers for their respective entities for being able to consistently give (relatively) accurate recommendations to users based on their past interests or purchases.

• What is a ‘Flat File’?

A flatfile is a catch-all term used for concepts like Comma Separated Values (.csv). While there are a lot of different ways to create such a file, they all share ideas that they can be created and manipulated easily and without necessarily

requiring a standard database application. These can also be used to transfer data from system to system due to their lightweight status. In some cases, these have been replaced by XML files, however XML can when compared to certain kinds of flatfiles, be very large.

• I have a database that was built in MySQL, and I need the data to be moved over to Microsoft SQL Server. How would I do this?

The easy answer would be to contact Microsoft Tech Support and bring your checkbook. A more difficult answer would be to bring it down to a .csv file and then import it into SQL Server, but without a specialty conversion utility you may lose some program-specific specific tricks, thus requiring some rebuilding once the conversion is complete. This is not saying that this would work in all cases, but it is at least an option.

• What is the difference between ‘=’ and ‘LIKE’?

When crafting a query, or using programming to display data in certain ways depending on the values being returned, you may want to think that these can be used interchangeably. There is one big difference, however: equal means equal. The value being returned must match the value it is being compared to 100%. LIKE, however, can be used with a number of different wildcard mechanics, allowing you to be a bit more flexible in your rules.

• What is a Null Value?

A Null Value is an absence of data. This one is a bit misleading sometimes, because depending on who you ask, it can be considered many possible things. “Null equals 0”- Not in this context, because 0 is a value. “Null equals Empty” — closer, but again sometimes an empty value can still be considered a value depending on how the field is structured. If a column allows for null values, and no value is submitted, then it allows it to be Null.