r****r
发帖数: 1839 | 1 下礼拜要面试，职位要用很多SQL Server的东西，从一般用户到DBA的活都可能
涉及到。恳请有这方面资料的各位老少爷们/姐们帮个忙，或告知一下网址。
这个职位要用MS Analysis Services Cube，以前用过DB2，Oracle11g，但这个
Cube了解为0. | w*****m
发帖数: 20421 | | M****r
发帖数: 143 | 3 SSAS 不是临阵磨枪能搞定的，你还是省省时间吧。 | i****a
发帖数: 36252 | 4 Chrome + Ad Blocker = no avator, no ads, no JY8 pictures = much safer for
office
【在 w*****m 的大作中提到】
: 请把你的光腚照换掉，大家在上班，不要害人。
| w*****m
发帖数: 20421 | 5 就冲他那么恶心，一点不CONSIDERATE别人，有推荐也不告诉他 | r****r
发帖数: 1839 | 6 没人当你是哑巴。艺术照片怎么就恶心了？你内心黑暗不要赖别人。
【在 w*****m 的大作中提到】
: 就冲他那么恶心，一点不CONSIDERATE别人，有推荐也不告诉他
| C***y
发帖数: 2546 | 7 找本mdx的书看看吧
【在 r****r 的大作中提到】
: 下礼拜要面试，职位要用很多SQL Server的东西，从一般用户到DBA的活都可能
: 涉及到。恳请有这方面资料的各位老少爷们/姐们帮个忙，或告知一下网址。
: 这个职位要用MS Analysis Services Cube，以前用过DB2，Oracle11g，但这个
: Cube了解为0.
| m*********y
发帖数: 389 | 8 Here are some questions I copied from online.. Obviously LouZhu is a lazy
ass... these questions are everywhere... :-)
SQL Interview questions
Below is a list of questions in this blog post so you can test your
knowledge without saying answers. If you would like to see questions and
answers please scrool down.
Question: What type of joins have you used?
Question: How can you combine two tables/views together? For instance one
table contains 100 rows and the other one contains 200 rows, have exactly
the same fields and you want to show a query with all data (300 rows). This
sql interview question can get complicated.
Question: What is the difference between where and having clause?
Question: How would apply date range filter?
Question: What type of wildcards have you used? This is usually one of
mandatory sql interview question.
Question: How do you find orphans?
Question: How would you solve the following sql queries using today's date?
First day of previous month
First day of current month
Last day of previous month
Last day of current month
Question: You have a table that records website traffic. The table contains
website name (multiple websites), page name, IP address and UTC date time.
What would be the query to show all websites visited in the last 30 days
with total number or visits, total number if unique page view and total
number of unique visitors (using IP Address)?
Question: How to display top 5 employees with the higest number of sales (
total) and display position as a field. Note that if both of employees have
the same total sales values they should receive the same position, in other
words Top 5 employees might return more than 5 employees.
Question: How to get accurate age of an employee using SQL?
Question: This is SQL Server interview question. You have three fields ID,
Date and Total. Your table contains multiple rows for the same day which is
valid data however for reporting purpose you need to show only one row per
day. The row with the highest ID per day should be returned the rest should
be hidden from users (not returned).
Question: How to return truly random data from a table? Let say top 100
random rows?
Question: How to create recursive query in SQL Server?
General SQL Interview questions and answers
Question: How long have you used SQL for? Did you have any breaks?
Answer: SQL skills vary a lot depending on the type of job and experience of
the candidate so I wouldn’t pay too much attention to this sql interview
question but it is always worth having this information before asking SQL
tasks so you know if you deal with someone who is truly interested in SQL (
might just have 1 year experience but be really good at it and at answering
the questions) or someone who doesn’t pay too much attention to gain proper
knowledge and has been like that for many years (which doesn’t always mean
you don’t want them).
Basic SQL Interview questions and answers
Question: What type of joins have you used?
Answer: Joins knowledge is MUST HAVE. This interview question is quite nice
because most people used inner join and (left/right) outer join which is
rather mandatory knowledge but those more experienced will also mention
cross join and self-join. In SQL Server you can also get full outer join.
Question: How can you combine two tables/views together? For instance one
table contains 100 rows and the other one contains 200 rows, have exactly
the same fields and you want to show a query with all data (300 rows). This
sql interview question can get complicated.
Answer: You use UNION operator. You can drill down this question and ask
what is the different between UNION and UNION ALL (the first one removes
duplicates (not always desirable)… in other words shows only DISTINCT rows
….Union ALL just combines so it is also faster). More tricky question are
how to sort the view (you use order by at the last query), how to name
fields so they appear in query results/view schema (first query field names
are used). How to filter groups when you use union using SQL (you would
create separate query or use common table expression (CTE) or use unions in
from with ().
Question: What is the difference between where and having clause?
Answer: in SQL Where filters data on lowest row level. Having filters data
after group by has been performed so it filters on "groups"
Question: How would apply date range filter?
Answer: This is tricky question. You can use simple condition >= and <= or
similar or use between/and but the trick is to know your exact data type.
Sometimes date fields contain time and that is where the query can go wrong
so it is recommended to use some date related functions to remove the time
issue. In SQL Server common function to do that is datediff function. You
also have to be aware of different time zones and server time zone.
Question: What type of wildcards have you used? This is usually one of
mandatory sql interview question.
Answer: First question is what is a wildcard? Wildcards are special
characters that allow matching string without having exact match. In simple
word they work like contains or begins with. Wildcard characters are
software specific and in SQL Server we have % which represent any groups of
characters, _ that represent one character (any) and you also get [] where
we can [ab] which means characters with letter a or b in a specific place.
Question: How do you find orphans?
Answer: This is more comprehensive SQL and database interview question.
First of all we test if the candidate knows what an orphan is. An Orphan is
a foreign key value in "child table" which doesn’t exist in primary key
column in parent table. To get it you can use left outer join (important:
child table on left side) with join condition on primary/foreign key columns
and with where clause where primary key is null. Adding distinct or count
to select is common practise. In SQL Server you can also you except which
will show all unique values from first query that don't exist in second
query.
Question: How would you solve the following sql queries using today's date?
First day of previous month
First day of current month
Last day of previous month
Last day of current month
Answer: These tasks require good grasp of SQL functions but also logical
thinking which is one of the primary skills involved in solving sql
questions. In this case I provided links to actual answers with code samples
. Experienced people should give correct answer almost immediately. People
with less experience might need more time or would require some help (Google
).
Intermediate SQL Interview questions and answers
Question: You have a table that records website traffic. The table contains
website name (multiple websites), page name, IP address and UTC date time.
What would be the query to show all websites visited in the last 30 days
with total number or visits, total number if unique page view and total
number of unique visitors (using IP Address)?
Answer: This test is mainly about good understanding of aggregate functions
and date time. In this we need to group by Website, Filter data using
datediff but the trick in here is to use correct time zone. If I want to do
that using UTC time than I could use GetUTCDate() in sql server and the
final answer related to calculated fields using aggregate functions that I
will list on separate lines below:
TotalNumberOfClicks = Count(*) 'nothing special here
TotalUniqueVisitors = Count(distinct Ipaddress) ' we count ipaddress fields
but only unique ip addresses. The next field should be in here but as it is
more complicated I put it as third field.
TotalNumberOfUniquePageViews = Count(distinct PageName+IPAddress) 'This one
is tricky to get unique pageview we need to count all visits but per page
but only for unique IP address. So I combined pagename with ipaddress to
counted unique values. Just to explain one page could receive 3 vists from 2
unique visits and another page could receive one visit from ip that visited
previous page so Unique IP is 2, PageView is 3 (1 visitor 2 pages and 1
visitor 1 page) and visits is 4
Question: How to display top 5 employees with the higest number of sales (
total) and display position as a field. Note that if both of employees have
the same total sales values they should receive the same position, in other
words Top 5 employees might return more than 5 employees.
Answer: Microsoft introduced in SQL Server 2005 ranking function and it is
ideal to solve this query. RANK() function can be used to do that, DENSE_
Rank() can also be used. Actually the question is ambiguous because if your
two top employees have the same total sales which position should the third
employee get 2 (Dense_Rank() function) or 3 (Rank() Function)? In order to
filter the query Common Table Expression (CTE) can be used or query can be
put inside FROM using brackets ().
Now that we covered basic and intermediate questions let's continue with
more complicate ones:
Advanced SQL Interview Questions and answers
Question: How to get accurate age of an employee using SQL?
Answer: The word accurate is crucial here. The short answer is you have to
play with several functions. For more comprehensive answer see the following
link SQL Age Function. Calculate accurate age using SQL Server
Question: This is SQL Server interview question. You have three fields ID,
Date and Total. Your table contains multiple rows for the same day which is
valid data however for reporting purpose you need to show only one row per
day. The row with the highest ID per day should be returned the rest should
be hidden from users (not returned).
Answer: Removing duplications is not so easy however SQL Server provides
ranking functions and the candidate can use dense_rank function partition by
Date and order by id (desc) and then use cte/from query and filter it using
rank = 1. There are several other ways to solve that but I found this way
to be most efficient and simple.
Question: How to return truly random data from a table? Let say top 100
random rows?
I must admit I didn't answer correctly this sql interview question a few
years back.
Answer: Again this is more SQL Server answer and you can do that using new_
id() function in order by clause and using top 100 in select. There is also
table sample function but it is not truly random as it operates on pages not
rows and it might not also return the number of rows you wanted.
Question: How to create recursive query in SQL Server?
Answer: The first question is actually what is a recursive query? The most
common example is parent child hierarchy for instance employee hierarchy
where employee can have only one manager and manager can have none or many
employees reporting to it. Recursive query can be create in sql using stored
procedure but you can also use CTE (Common table expression) for more
information visit SQL Interview question - recursive query (microsoft). It
might be also worth asking about performance as CTE is not always very fast
but in this case I don't know which one is would perform betters.
I will try to find time to add more questions soon. Feel free to suggest new
questions (add comments). | r****r
发帖数: 1839 | 9 我在花功夫看MSDN上面T-SQL的reference，也挺不错，把T-SQL一些特别的命令
学了一下，感觉不错。网址是：
http://msdn.microsoft.com/en-us/library/ms189826(v=sql.90).aspx
This
【在 m*********y 的大作中提到】
: Here are some questions I copied from online.. Obviously LouZhu is a lazy
: ass... these questions are everywhere... :-)
: SQL Interview questions
: Below is a list of questions in this blog post so you can test your
: knowledge without saying answers. If you would like to see questions and
: answers please scrool down.
: Question: What type of joins have you used?
: Question: How can you combine two tables/views together? For instance one
: table contains 100 rows and the other one contains 200 rows, have exactly
: the same fields and you want to show a query with all data (300 rows). This
|
|
