剑知北美生活快报   版面列表   admin登录
JiansNet Logo


Best Relational Database Interview Questions

by JC, published: 2012-09-09 23:08 viewed: 794 times
想了解更多的美国生活窍门?请订阅: JC写的剑知北美生活快报。
Below are some tough sql database interview questions, for hiring any serious software engineer.

--Most consumer-facing web companies these days use one of the major open source databases, either MySQL or PostgreSQL. Explain what database indexes are and how they work.

--How do you boost the performance of a database?

--What is a join and explain different types of joins.

--Explain when and how transactions should be used.

--What's the fastest way to get a thousand records into the database?

--How can you tell if a query will scale for production?

--What's the difference between a primary key and a unique key?

Also, it is better to ask the interview candidate to explain specific statements on their resumes. If they say they tuned queries or improved performance, you'd ask how? What they did in specific? What tools they've used, etc.
Comments (2)
1. JC 2012-01-13 13:22
For the question of "what's the fastest way to get a thousand records into the database?", I think the answer should be bulk-loading into the database.

Typically, databases are implemented using B/B+ trees, there is a HUGE cost to insert one item at at time, since the data structure needs to be adjusted each time. Bulk-loading should pre-allocate enough space, therefore, making it easy to lump all records efficiently into the db.
2. JC 2012-09-09 23:08
Here is a challenging database question: "how to delete duplicate records? (only id is different)" A proper answer with using just one sql statement is like the following:

DELETE
FROM Foo1
WHERE Foo1.ID IN

-- List 1 - all rows that have duplicates
(SELECT F.ID
FROM Foo1 AS F
WHERE Exists (SELECT Field1, Field2, Count(ID)
FROM Foo1
WHERE Foo1.Field1 = F.Field1
   AND Foo1.Field2 = F.Field2
GROUP BY Foo1.Field1, Foo1.Field2
HAVING Count(Foo1.ID) > 1))
AND Foo1.ID NOT IN

-- List 2 - one row from each set of duplicate

(SELECT Min(ID)
FROM Foo1 AS F
WHERE Exists (SELECT Field1, Field2, Count(ID)
FROM Foo1
WHERE Foo1.Field1 = F.Field1
   AND Foo1.Field2 = F.Field2
GROUP BY Foo1.Field1, Foo1.Field2
HAVING Count(Foo1.ID) > 1)
GROUP BY Field1, Field2);

The strategy here is to return two lists: The first, List 1, is a list of all rows that have duplicates, and the second, List 2, is a list of one row from each set of duplicates. This query simply deletes all rows that are in List 1 but not in List 2.
本文版权属于美国剑知信息网。如需转载,请先同我们联系。
订阅JC写的剑知北美生活快报,您会了解到更多的美国生活窍门。
Related Articles:
• Best Computer Science Interview Books
• Top IT Skills and Salaries In USA
• Solution To Interview Question: Design An Elevator System
• Jobs Sponsoring H1B Visa
美国职业发展 | 返回顶部 | 返回首页
About Us | Advertise with Us | Privacy Policy
Copyright © 2007-2016, All Rights Reserved.