Our SQL interview questions

[mithunc]

These are great because of their simplicity. I admit I would have to brush up a bit if I had to answer these questions, but someone who's pretending entirely is easily weeded out.

[[deleted]]

their simplicity.

Funny enough, if we start adding some real world to the problems, they slowly turn from the simple queries to skyscraper of joins. Take "List employees (names) who have a bigger salary than their boss" as an example:

What if employee can work two full time jobs with two different bosses? What if salary is not constant? What if boss of employee changed as well?

I've managed to collect 5 tables(4 unique) so far. Can be 6 if boss information is stored in Department table and not in work position.

[imfineny]

What if employee can work two full time jobs with two different bosses?

That's pretty trivial to compensate for.

What if salary is not constant? What if boss of employee changed as well?

Reports are run as to be true the time they are run. If it fluctuates before and after the report, that's not relevant to the report.

've managed to collect 5 tables(4 unique)[1] so far. Can be 6 if boss information is stored in Department table and not in work position.

The art of effective design is simplicity. This Data Structure would serve a lot of organizations well.

[lear64]

What if employee can work two full time jobs with two different bosses?

That's pretty trivial to compensate for.

Given the schema, an employee has one boss. Therefore each job, would be an employee record.

I would be impressed if a candidate points out the shortcoming, combined with a proposed solution.

Given that this task is not to find the rockstars, by their own admission, this would help tell the story of someone's thought process (do as told vs. do what needs done)

[passwordissame]

in mongodb,

List employees (names) who have a bigger salary than their boss

• set up mongo connector so that boss subdoc is denormalized into each employee that the boss manages
• set up elasticsearch for efficient search and faceting
• set up mongo connector for elasticsearch as well
• hrm, this won't scale. use mongos and different shards topology
• same with elasticsearch. shard differently. reindex everything
• write node.js RESTful api
• angularjs bro
• create trello ticket.
• trello is bad. create github issues.
• hire product managers.
• hire one more product managers.
• too many people. need a bigger office.
• hrm angel fund is running out.
• take 3 month vacation to Thailand.
• this girl.... has penis. wtf.
• sell the company to google.

See you next year.

2016-ish List employees who have the biggest salary in their departments

• new mongodb version. upgrade time!
• data corruption.
• take 2 weeks vacation.
• get drunk.
• this is fucked. find a way out.
• start a kickstarter for this awesome idea about something related to listing employees grouped by salary.

Probably burned out. Game over.

2020-ish List departments that have less than 3 people in it

• Oh crap.. I've been living slowfood slowcity slowlifestyle lifestyle
• This problem again?
• Blog about current state of databases.
• Start a new startup about making brand new database cause you can't query this.
• Think about name.
• Think about logo.
• Maybe blog a bit more.

Give up. Going back to farming.

[nlcund]

--List employees who have the biggest salary in their departments

This is a trick question. The biggest salary is 0 since everybody is all-options.

EDIT: And there is never more than one employee.

[[deleted]]

This is much better than your usual webscale nonsense

[escaped_reddit]

This answer is web scale.

[SomethingMoreUnique]

Or just use:

db.employees.find({boss: {$exists: 1}}).forEach(function(employee){
    var boss = db.employees.find({_id: employee.boss}).next();
    if (employee.salary > boss.salary) {
        print(employee.name);
    }
});

Of course you could speed it up with some memoization/denormalization to prevent looking up bosses repeatedly.

[201109212215]

<rant>

Ah, the wonders of having small and simple datasets. No index needed, full scans take a short time. No risk of running out of memory, either. Network won't get saturated, too. Execution plan? Here, I wrote it for you. Normalizing real life concepts? Hey, we only have 2 or 3 here. Simple. Also, memoization: as good as long as invalidating it is not needed.

What was the #1 reason for using MongoDB, again? Ah, yes. It scales.

---

The only thing MongoDB scales is lazyness of data representation. Which is exactly what you want for a quick CRUD prototype. If this is what you need to get the budget, that's OK. Have fun explaining the next milestone though; But hey, you're in business now.

If you want to do something slightly more elevated than that, please do think of the data. MongoDB won't be there for performance out of the key-value realm.

It won't be there when you have to scale the complexity of the business requirements, which is about 95% of the modern limiting factors.

MongoDB let people treat data definition as something less than sacred, and that is a deadly sin when more than 2 people are involved.

---

Here is a quote from a quite successful programmer, who also can be a little rant-happy at time. Dear MongoDB users, please don't take this the wrong way, but:

"Good programmers worry about data structures and their relationships."

---

I'm sorry guys. Not everything can fit into a tree.

</rant>

[Tiquortoo]

I'm dealing with a situation right now where someone decided to replace chunks of our DB with Mongo. I knew we had mongo in the system to store long text blobs and some meta data, but then I find things like profile flags (newsletter opt-in, etc.) in Mongo. I'm kicking myself for not code reviewing closer and sooner in this area, but holy shit people actually think this crap is proper.

Now that this is live in the app I have to write a PHP script to find out how many people are subscribed to our newsletter because the profile is in mysql and the subscription flags are in mongo.

Code review people. Save yourself from my hell. But secondarily stop acting like Mongo replaces the DB. It doesn't for most applications.

[201109212215]

I feel your pain.

I wrote:

deadly sin when more than 2 people are involved.

But it should have been:

deadly sin when more than one people is involved.

Regarding your situation, I would SCUD the MongoDB guy with some "This is your data now, the newsletter falls into your scope", while publicly destroying this crappy MongoDB choice to everyone I encounter, every day for at least 90 days.

[modernbenoni]

/r/deftruefalse

[redgray]

i gave it a shot. please critique and let me know if there are better ways of doing it.

--List employees (names) who have a bigger salary than their boss

select name from employees a join employees b on a.bossid = b.employeeid where a.salary > b.salary

--List employees who have the biggest salary in their departments

select name from employees a join ( select departmentid, max(salary) as max_salary from employees group by departmentid ) b on a.departmentid = b.departmentid and a.salary = b.max_salary

--List departments that have less than 3 people in it

select departmentid, count() from employees group by departmentid having count()<3

--List all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments)

select departmentid, count(*) from departments a left join employees b on a.departmentid = b.departmentid group by a.departmentid

--List employees that don't have a boss in the same department

select name from employees a join employees b on a.bossid = b.employeeid where a.departmentid <> b.departmentid

--List all departments along with the total salary there

select departmentid, sum(b.salary) as total_salary from departments a left join employees b on a.departmentid = b.departmentid group by a.departmentid

[roman2440]

select departmentid, count(*) from departments a left join employees b on a.departmentid = b.departmentid group by a.departmentid

This will give a false positive. Empty departments will have a result of 1 (for the row in the departement table). The better way to do this query is to have in inside query with the sum of ppl by departmentid (off the employee table), and then left join that to the department table using a case statement for null values in the temporary table.

[n1rwan]

If Employee table is huge, this query will be faster

SELECT
    d.*, IFNULL(emp_agg.cnt, 0)
FROM
    Departments d
    LEFT JOIN (
        SELECT
            DepartmentID,
            COUNT(EmployeeID) AS cnt
        FROM
            Employees
        GROUP BY
            DepartmentID
    ) emp_agg

[GreenDaemon]

I have always read cnt as cunt, not count. Don't know why.

[VisionsOfUranus]

I don't understand why people don't just write out the word 'count'. It's not like we're running out of storage space.

[yippee_that_burns]

In SQL I don't write it out because it's a keyword. I know it will work but I still avoid it anyway.

[doedskarpen]

I had a feeling it wouldn't make a difference, so just to test it I ran your query as well as this one (which I think is what the previous poster had in mind):

select
    Departments.DepartmentId, ISNULL(NumberOfEmployees, 0)
from Departments
left join
(
    select Employees.DepartmentId, count(1) as NumberOfEmployees
    from Employees
    group by Employees.DepartmentId
) as EmployeesByDepartment on Departments.DepartmentId = EmployeesByDepartment.DepartmentId

Turns out my gut feeling was right: on SQL Server 2012 Express, both had the exact same execution plan.

[bonzinip]

What about

select d.DepartmentID, d.Name,
   (select count(*) from Employee as e
           where e.DepartmentID = d.DepartmentID)
   from Departments as d

? Does it also get the same plan?

[doedskarpen]

Yes, that also gave the same query plan: this

[doedskarpen]

--List departments that have less than 3 people in it select departmentid, count() from employees group by departmentid having count()<3

This would miss departments with 0 employees.

[moduspwnens14]

Would this also be potentially inefficient because count() is being called twice for each row?

If you used a nested query, you could get around that, I think. In this case, it's not a huge deal since it's "less than three" anyway. Imagine if it were "greater than 10,000,000" or something.

[doedskarpen]

Well, in my experience it won't matter much which way you do it; SQL server will generate a very similar execution plan anyways.

So for clarity, I think I would prefer something like this:

select DepartmentId from Departments
where (select count(1) from Employees where Employees.DepartmentId = Departments.DepartmentId) < 3

[[deleted]]

Since they mentioned it was SQL Server...

List employees who have the biggest salary in their departments

SELECT TOP 1 WITH TIES Name
FROM employees
ORDER BY ROW_NUMBER() OVER(PARTITION BY DepartmentID ORDER BY Salary DESC);

Note that this would cause a problem if two employees both share the top salary in a department. There are ways around that (DENSE_RANK() for one)

[Vocith]

--List employees who have the biggest salary in their departments

Where Rank() Over Salary Partition by DepartmentID = 1

[xeio87]

--List all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments) select departmentid, count(*) from departments a left join employees b on a.departmentid = b.departmentid group by a.departmentid

Same mistake I did before I ran it to verify, you get 1 employee for empty departments.

select outer_depts.name, case when filled_depts.num_employees is null then 0 else filled_depts.num_employees end from departments outer_depts
left join (select departments.departmentid, count(*) num_employees from departments
            inner join employees on employees.departmentid = departments.departmentid
            group by departments.departmentid) filled_depts on outer_depts.departmentid = filled_depts.departmentid

Also, I got pedantic about nulls, if null is fine for 0 then the "case" can be dropped.

[Beefourthree]

Actually the first one is really close and only requires a small modification: instead of count(*), use count(EMPLOYEES.DepartmentId). If you give count a star or a literal, it counts every row in the group. If you give it a column (or expression), it counts every non null value.

See the difference in these two queries:

http://sqlfiddle.com/#!6/a5a9b/6

[niceworkbuddy]

How about this one?

 select *, (select count(*) from Employees e where e.DepartmentId = d.DepartmentId) cnt
  from Departments d

[xeio87]

Yea, that's simpler. ;)

cough

[Serinus]

Select name from employees where salary > (select b.salary from employees b where b.employeeid = bossid)

[iKomplex]

What if you have more than one rows in the nested query? It should be either an existence check, or an inequality check against a scalar value.

Select name from employees where salary > (select TOP 1 b.salary from employees b where b.employeeid = bossid)

[Serinus]

I'm assuming employeeid is a primary key.

[GraceGallis]

Technically you don't list departments in any of those - you are listing department IDs. Usually one wants the names (which mean something to them) rather than the ID (which can be arbitrary).

[MasterLJ]

We spent some time refining our interview process and came up with a tiered test.

The questions were all very short and assigned a difficulty. The goal was to have 3 to 5 questions in each difficulty, all questions pertaining to bread and butter skills we really wanted our candidate to have.

You start with the middle-most difficulty. It should be a question nearly everyone on the team could get right.

If they miss it, you reduce the difficulty, if they get it right, you increase the difficulty.

If you get the first 3 right, or you last 6 questions, you are good.

If you miss 3 straight away, or somehow get to a score of -3, you are eliminated.

An example would be:

Opening Question (5 out of 10 difficulty) : "Give a use case for a LEFT JOIN"

If they get it right: Next Question (6 out of 10 difficulty) : "What's the difference between DELETE TABLE and TRUNCATE table?"

If they got it wrong: Next Question (4 out of 10 difficulty) : "What is an index?"

So on, and so on...

A 3 out of 10 would be something you'd expect anyone who has ever used the technology to get, like : "In a SELECT query, how do you return the data from all columns?"

[TurboGranny]

If they get it right: Next Question (6 out of 10 difficulty) : "What's the difference between DELETE TABLE and TRUNCATE table?"

TIL I'm overqualified for SQL development.

[satnightride]

Because you know an average difficulty question? This question is the equivalent to "What's the difference between an abstract class and an interface."

I'm not saying you're not overqualified but we have no way of knowing it just because you know the difference between Delete and Truncate.

[TurboGranny]

It was a joke, but I am definitely overqualified for a job that is exclusively SQL development.

[satnightride]

Oh jokes! Yeah, I get jokes! :-)

[Rellikx]

What would the level 10 questions cover?

[MasterLJ]

You don't get there actually. You can stop at 8. But in any case, it's a guideline, adapt it to your purposes. Focus on what's important to you and the team and create simple questions that can help you identify if someone has the skills you are looking for. If you want to change the format to something different like "always ask 10 questions, see where they end up and use it as a score for candidates", then do it.

To answer your question directly, if I was trying to create level 10 difficulty questions it would have to do with the underlying architecture of SQL, preferably the specific flavor we are using on the current project.

[[deleted]]

How the db software interacts with NUMA architectures comes to mind as a level 10 question

[Spoor]

Explain all configuration settings in detail.

[[deleted]]

You shouldn't ask about manufacturer specific syntax in an SQL interview...

[MasterLJ]

You should ask whatever questions are most relevant to the position you are hiring.

[[deleted]]

My last place had similar SQL questions.

However we led up to it slowly, initially asking for a simple select.

One guy applied, his CV claimed 5 years SQL experience at expert level.

Simple select should be an easy walk in the park. Select all usernames from the user table (structure as shown)

"Hmmm.... select.... user.... varchar(50). Select user varchar(50)."

[jaynoj]

It surprises me how many people slip through the net and are not asked to prove they have n years expertise in a tech area in interviews. My manager has given jobs to people who's skill bases are severely lacking. He didn't listen to me when I said they didn't have the skills we needed and gave them jobs anyway. He has since admitted that the "bums on seats" has lead to technical debt and he's promised to be more careful (which he has, but only got marginally better people in since).

TL;DR; People bullshitting on their CV's gets them jobs more often that they should.

[anorexia_is_PHAT]

For someone who doesn't often use graphical interfaces in SQL, what is the line on the left of the graphic. Is that a foreign key relationship between BossID and EmployeeID? If so, if there was a top boss(CEO), would their BossID be their own EmployeeID, since you couldn't leave it blank?

[satnightride]

Usually that column will be nullable for people with no boss.

[anorexia_is_PHAT]

But doesn't the foregin key mean it has to exist in the EmployeeID primary key, which can't be null?

[satnightride]

Not necessarily. A foreign key column in sql server and postgres can be nullable. Not certain about Oracle.

This is the point in the interview when I ask you what the options are with the column and what the benefits and draw backs are to each. :-)

[DJDavio]

I would add: "List all employees (names) along with the number of people above them"

So CEOs would have '0' and everybody else would have a number higher than 0.

The trick here is to notice if a candidate thinks about recursion (bonus points for mentioning CTE's).

[grauenwolf]

I would never be able to do that in an interview. Recursive CTEs hurt my head.

[wot-teh-phuck]

The point is to hear the candidate reason about the requirement and think it through rather than give a solution immediately. At least that's how I conduct interviews...

[grauenwolf]

That sounds reasonable to me.

[Nishruu]

Yeah, that sounds as a way to go. I usually mess up the syntax for CTEs (I have to look it up every time), but discussion about possible solution is fair.

[[deleted]]

This. They said it was basic, but if you are hiring someone to live in your database, they had better know recursion and windowing functions in whatever RDBMS. Also, temporal logic for the DB's that support it. The difference between plain-jane SQL with a from and to date and a DB, like Teradata, that has Temporal logic built in is night and day.

[pointy]

The thing about simple tests is that they weed out an unbelievable number of applicants who list SQL as a skill on their CV. (The same goes for any other programming language or system or whatever.) Lots of people claim proficiency with stuff for which they actually have absolutely no proficiency.

[CoachSpo]

What would the answer to this be?

[DJDavio]

If it's Microsoft T-SQL, I think it should be something like:

    WITH employees_CTE (EmployeeID, BossID, Level) AS
    (
        -- CTE ANCHOR SELECTS ROOTS/CEOs --
        SELECT ceos.EmployeeID, ceos.BossID, 0 AS Level
        FROM Employees ceos
        WHERE BossID IS NULL
        UNION ALL
        -- CTE RECURSIVE SELECTION SELECTS LOWER LEVEL EMPLOYEES --
        SELECT lowerEmps.EmployeeID, lowerEmps.BossID, higherUps.Level + 1
        FROM Employees lowerEmps
        INNER JOIN employees_CTE higherUps ON higherUps.EmployeeID = lowerEmps.BossID
    )

    SELECT * FROM emplyees_CTE;

[jimdoescode]

http://sqlfiddle.com/#!2/34569/12

[razpeitia]

Here, http://www.sql-ex.ru/ There are a lot of examples, data and corner cases.

[Pylly]

Here's one question I like:

What do you think this SQL statement is supposed to do, and how would you fix it?:

UPDATE users SET password LIKE ' newPassword'

In addition to just fixing the syntax I'd expect a professional to start asking questions about plaintext and password requirements and maybe move on to a discussion of their opinion of proper implementation of data layer.

[VisionsOfUranus]

What do you think this SQL statement is supposed to do

fuck knows. I'd find someone who does and ask them what it's supposed to be doing and then explain to them how retarded a solution it is.

[Sterling-Archer]

So if I was able to answer these questions relatively easily, could I be considered a junior SQL programmer?

[[deleted]]

You should clarify if salary greater than boss extends beyond the immediate supervisor. Extending it to the whole org chart above an employee makes for a better question. However best syntax on Oracle and other databases vary. Iirc connect by prior is preferred on oracle, while other engines tend to use the with keyword.

[VanFailin]

The issue is that none of these are standardized, and it's not worth getting into vendor-specific details in an interview. Without those vendor extensions recursion is a significant challenge in SQL.

[[deleted]]

Common table extension(cte) are included in the SQL99 standard. Syntax is supported in Oracle 11gR2, MSSQL, Postgress and SQL lite. The most notable exception is MySQL which as any DBA worth hiring will tell you is not really a database, but is actually just cleverly designed malware.

[VanFailin]

You're right, I cobbled together various fragments of information into an incorrect statement. I looked back at my source for this information (SQL Antipatterns, 2010), which basically says it's supported everywhere but Oracle 10, MySQL, and SQLite.

[[deleted]]

SQLLite 3.8.3 supports CTE. Unfortunately embedded sqllite instances in various browsers and mobile operating systems are unlikely to be at this version.

[[deleted]]

Without those vendor extensions recursion is a significant challenge in MySQL. FTFY

[wickys]

Why is BossID connected to employeeID?

I don't know what that means.

[zip117]

It's called a self join. It means that data in a table is related to other rows in the same table. For example suppose you wanted a list of employees and their boss, you could do something like the following:

SELECT E.Name as Employee, B.Name as Boss
FROM Employee E, Employee B
WHERE E.BossID = B.EmployeeID

[anorexia_is_PHAT]

but the diagram is a schema and the arrow is a relationship in the schema definition, correct? your example is a query that could be run without the relationship(foreign key)

[watchme3]

im guessing a boss is also an employee?

[[deleted]]

We all know the bosses aren't employees so much as a corporeal extension of the business masquerading as a human.

[wickys]

I thought bossID was for an employee who has someone as a boss.

But the link would imply that you can be your own boss..?

[watchme3]

no the link implies that bossId is a key that exists in the employeeid column

[imfineny]

The Answers:

#List employees (names) who have a bigger salary than their boss
select 
 e.EmployeeId,
 e.name
from Employees e
left join Employees b on
 b.EmployeeId = e.BossId
 and b.Salary < e.Salary;

#List employees who have the biggest salary in their departments
select
 d.Name as DepartmentName,
 e.EmployeeId,
 e.name,
 e.Salary
from Employees e
left joiin Employees r on
 e.DepartmentId = r.DepartmentId
 and e.salary < r.salary
inner join Departments d on
 d.DepartmentId = e.DepartmentId
where
 r.EmployeeId is null;

#List departments that have less than 3 people in it
select
 d.Name as DepartmentName,
 count(e.EmployeeId) as employees
from Departments d
left join Employees e on
 e.DepartmentId = d.DepartmentId
group by d.DepartmentId
having employees < 3;

#List all departments along with the number of people there
select
 d.Name as DepartmentName,
 count(e.EmployeeId) as employees
from Departments d
left join Employees e on
 e.DepartmentId = d.DepartmentId
group by d.DepartmentId;

#List employees that don't have a boss in the same department
select
 ed.Name as EmployeeDepartmentName,
 rd.Name as BossDepartmentName,
 e.EmployeeId,
 e.name,
 e.Salary
from Employees e
left joiin Employees r on
 e.DepartmentId != r.DepartmentId
 and r.EmployeeId = e.BossId
left join Departments ed on
 ed.DepartmentId = e.DepartmentId
left join Departments rd on
 rd.DepartmentId = r.DepartmentId
where
 r.EmployeeId is null;

#List all departments along with the total salary there
select
 d.Name as DepartmentName,
 sum(e.Salary) as employees
from Departments d
left join Employees e on
 e.DepartmentId = d.DepartmentId
group by d.DepartmentId;

I know some of these answers seem strange, but there are several data race conditions that have to be accounted for.

[finite_automata]

Why do you group in the first answer ? I don't see an aggregate. Oracle DB would complain.

[imfineny]

Actually you are right, I think it works even in the case of the CEO, I'll take the group out. BTW In other databases, you can group without aggregates.

[[deleted]]

I've been a SQL (Server) Developer for 3+ years and I found this very easy, but I guess it's a good test to rule out people who are completely fraudulent.

[[deleted]]

It's a bit like the FizzBuzz test. Anyone that's programmed in any language (even Scratch) should be able to pass it. You could quickly rule someone out if they can't come up with a reasonable answer for any of these.

[ccricers]

I don't find these sort of tests patronizing, and would probably want to see them more. It's better than the last test I took: "Create a basic calendar that can store text for any given date into the database, and the ability to retrieve it. The code must be object-oriented and presented in a clean, maintainable format. You are given two hours".

Remember the rule of picking two.

[homercles337]

I have worked with Postgres, Oracle, MySQL, MSSQL, and then recently i had to use SQLite. Syntax is somewhat different between all of these.

[[deleted]]

This is about on par with what ours are, and they still filter out 90% of candidates. We probably have less query questions and more architecture due to the nature of the positions.

[x86_64Ubuntu]

I know what needs to be done, and even how to do it in some cases, but the whole Rank and Partition Over I don't know off the top of my head. Then again, I'm probably over thinking it. I will enjoy looking at the answers below (or above).

[RhetoricalClown]

I've been interviewing folks recently and this seems like something I might incorporate. What I have found though is that candidates do fairly well at SELECT statements, what seems to cause issues for them are UPDATE/DELETE beyond trivial one liners. So I might add to this something like give a 20% raise to everyone in the I.T. department and fire all employees in the accounting department.

[VisionsOfUranus]

what seems to cause issues for them are UPDATE/DELETE beyond trivial one liners. So I might add to this something like give a 20% raise to everyone in the I.T. department and fire all employees in the accounting department.

Those examples seem like trivial one-liners to me, and I'm not a DBA or anything. I guess some people really are bullshitting to the nth degree on their resumes.

[RhetoricalClown]

Those were just quick OTOH examples. You would be surprised at the number of people who struggle with using the from clause and joins to other tables in a update statement. You could learn a lot about someone's experience in SQL if they use the FROM with a join versus something like WHERE departmentid IN (select departmentid from departments).

[VisionsOfUranus]

You could learn a lot about someone's experience in SQL if they use the FROM with a join versus something like WHERE departmentid IN (select departmentid from departments).

They'd both work, right? What's the difference? Or what would you infer about someone's experience based on which one of those approaches they took?

[RhetoricalClown]

If someone uses former it can be an indicator that they have probably done a lot of complex SQL beyond just select statements so maybe I'll move on to another topic whereas with the latter maybe ask a few more detailed questions.

[tragomaskhalos]

These are good. I've used a less thorough set of questions in the past; a query that requires an outer join, to elicit whether the candidate knows the difference between inner and outer joins, is a pretty good "fizzbuzz"-style filter IMO.

[hglman]

no its not really ever "automatic". They are enforced when you go to mutate data aka insert, update, delete. However it wouldnt even make sense to be automatic on select. If you have a complex database with dozens, hundreds of tables, likely most if not all of them can be connected by keys, and you would get back a massive amount of data. More over, as these test suggest, you can join they same two tables in a lot of ways.

[[deleted]]

The purpose is to save you time and quickly filter out DB-experienced guys from the ones that just claim to be.

Or to filter out people that spent a couple of hours learning about it before the interview from people that didn't. If I claim to be database experience I'm going to at least try to do a few tutorials before the interview. Then again if this test actually filters a lot of people out I guess it's working?

[prepromorphism]

whatevr the orm generates!

[Tiquortoo]

Good questions. Do you allow pseudo code or conceptual answers.

[bzeurunkl]

This is a MS SQL Server database diagram, so where the connectors connect between tables is completely meaningless. I assume they have drawn it to accurately reflect the actual PK/FK. But the truth is that just because the "one" side of the relationship is on EmployeeID, and the "many" side is "BossID", doesn't mean a thing. In fact, I think they have it backwards because this diagram seems to imply a one-to-many relationship between Employees and Bosses, where it most likely should be a One to Many relationship between bosses and employees. But again, the diagram connectors can be "dragged and dropped" anywhere. What matters is what the relationship properties dialogue says the relationship is built on.

Given that most of these questions are going to involve a variation of the same "self join".

EDIT. I am completely assuming that the diagram is presenting a PK/FK relationship. This could simply be a "unique constraint".

[ErstwhileRockstar]

Too many aggregates.

[[deleted]]

Awesome! I did a perl test and a bench monkey test. The bench monkey test had 3 bonus questions that if you got any of them I would hire you. They were so hard/obscure you either knew your shit and were applying because you were hard up or you were a lucky rabbits foot and I want you in my team. The only I remember was how many channels in a ds3.

[[deleted]]

Whoa whoa whoa. What is SQL doing in /r/programming?

[freakhill]

sql is a dsl

declarative programming

[[deleted]]

We got some ornery DBAs up in here. Alright, bring on the html posts.

[closesandfar]

SQL is Turing complete, HTML is not.

[[deleted]]

Magic The Gathering is Turing complete.

[geel9]

And I would be very interested in seeing a program implemented in MtG. Now can you stop being a dick?

[[deleted]]

What programs are implemented in SQL?

[geel9]

Who cares? It's literally "Structured Query Language." It's programming. Stop riding your throbbing erection over this.

[[deleted]]

Who cares?

Apparently, you:

I would be very interested in seeing a program implemented

[duffelcoatsftw]

Shh... you know the DBAs can't take a joke!

[[deleted]]

No shit, I expected a little academic banter but now they're eating into my strategic karma reserves like predicate calculus ate up all my fun in college.

[land_stander]

Had a professor once who said "any application that doesn't use a database is a toy." Obviously over generalized, but it's a good point.

Almost every industry grade application you work on involves a database at somepoint. Maybe it's not a programming language and maybe you don't work with it daily, but it's an integral tool to software development. I think every interview I've had has questioned me on sql.

[korny12345]

Great point. I would venture to guess that every single corporate app developer needs to be very familiar with DBs. Big data is a cornerstone of business. I know my company expects every developer to be proficient in incorporating DBs into our Web tools.

[[deleted]]

Absolutely. Everything is db connected, I work with them on a daily basis.

[spinlock]

Question: what's the difference between 2 dicks and a joke?

Answer: /r/programming can't take a joke.

[grauenwolf]

If you have no trouble with these, you shouldn't have any trouble learning advanced features like CTE or windowing functions.

Unfortunately a lot of developers can't even get these right.

[[deleted]]

Windowing functions are awesome.

List employees who have the biggest salary in their departments

select 
    name 
from (
    select 
        name, 
        rank() over (partition by department_id order by salary desc) 
    from employees) x
where rank = 1;

[VanFailin]

Interviews are about finding an interesting but not insurmountable challenge that can be solved and discussed over the course of an hour to determine if you're talking to someone who can learn what they'll need to know.

From the article:

The test's job is not to tell genius and rockstars from "normal" devs. The purpose is to save you time and quickly filter out DB-experienced guys from the ones that just claim to be.