June 27, 2014 at 7:58 am
I just had an interview. Someone gave me 3 tables and asked me to write a query.
I struggled it and could not come up with the answer. I was told I was not qualified for the job immediately.
When I got back home and I came up the answer in 10 minutes.
I felt so embarrassed and I am in doubt of my ability as a database developer.:(
June 27, 2014 at 8:05 am
Loner (6/27/2014)
I just had an interview. Someone gave me 3 tables and asked me to write a query.I struggled it and could not come up with the answer. I was told I was not qualified for the job immediately.
When I got back home and I came up the answer in 10 minutes.
I felt so embarrassed and I am in doubt of my ability as a database developer.:(
Shrug it off as nerves. Sometimes technical questions like that in an interview just throw you. That doesn't mean you are not a good t-sql developer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2014 at 8:15 am
Sean Lange (6/27/2014)
Loner (6/27/2014)
I just had an interview. Someone gave me 3 tables and asked me to write a query.I struggled it and could not come up with the answer. I was told I was not qualified for the job immediately.
When I got back home and I came up the answer in 10 minutes.
I felt so embarrassed and I am in doubt of my ability as a database developer.:(
Shrug it off as nerves. Sometimes technical questions like that in an interview just throw you. That doesn't mean you are not a good t-sql developer.
Nerves happen.
I have interviewed people who would probably be embarrassed to know they came in with their fly down.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2014 at 8:20 am
SQLRNNR (6/27/2014)
Sean Lange (6/27/2014)
Loner (6/27/2014)
I just had an interview. Someone gave me 3 tables and asked me to write a query.I struggled it and could not come up with the answer. I was told I was not qualified for the job immediately.
When I got back home and I came up the answer in 10 minutes.
I felt so embarrassed and I am in doubt of my ability as a database developer.:(
Shrug it off as nerves. Sometimes technical questions like that in an interview just throw you. That doesn't mean you are not a good t-sql developer.
Nerves happen.
I have interviewed people who would probably be embarrassed to know they came in with their fly down.
Funny I don't remember you interviewing me. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2014 at 8:27 am
Sean Lange (6/27/2014)
SQLRNNR (6/27/2014)
Sean Lange (6/27/2014)
Loner (6/27/2014)
I just had an interview. Someone gave me 3 tables and asked me to write a query.I struggled it and could not come up with the answer. I was told I was not qualified for the job immediately.
When I got back home and I came up the answer in 10 minutes.
I felt so embarrassed and I am in doubt of my ability as a database developer.:(
Shrug it off as nerves. Sometimes technical questions like that in an interview just throw you. That doesn't mean you are not a good t-sql developer.
Nerves happen.
I have interviewed people who would probably be embarrassed to know they came in with their fly down.
Funny I don't remember you interviewing me. 😀
Of course there was that one bloke that had a boogie hanging pretty low.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2014 at 11:43 am
Loner:
Do you remember the question? Can you post it here? Each of us will try to solve it and see how many minutes it took. Maybe for some even 60.
June 27, 2014 at 12:36 pm
Here is the question.
I need to get the user who can access all the color.
That means the result is userid 3.
CREATE TABLE Color (colorid INT, NAME VARCHAR(100))
INSERT INTO Color(Colorid, Name)
SELECT 1, 'Black'
UNION
SELECT 2, 'White'
UNION
SELECT 3, 'Blue'
UNION
SELECT 4, 'Green'
UNION
SELECT 5, 'Red'
CREATE TABLE User (Userid INT, NAME VARCHAR(100))
Insert Into User (Userid, Name)
SELECT 1, 'Bill'
UNION
SELECT 2, 'Jim'
UNION
SELECT 3, 'Tom'
CREATE TABLE UserColor (Userid INT, Colorid INT)
INSERT INTO UserColor(Userid, Colorid)
SELECT 1, 1
UNION
SELECT 1, 2
UNION
SELECT 2, 1
UNION
SELECT 2, 2
UNION
SELECT 2, 3
UNION
SELECT 3, 1
UNION
SELECT 3, 2
UNION
SELECT 3, 3
UNION
SELECT 3, 4
UNION
SELECT 3, 5
June 27, 2014 at 12:41 pm
Here you go. Not gonna work through this one this time because it is similar to two blog posts I did already.
http://jasonbrimhall.info/2011/11/03/another-color-wheel/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2014 at 12:52 pm
Here is one way. This was a very quick stab at this but it works.
select u.Name, u.Userid
from MyUser u
join UserColor uc on uc.Userid = u.Userid
group by u.NAME, u.Userid
having COUNT(*) = (select COUNT(*) from Color)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2014 at 1:26 pm
I came up with this, not as good as Sean.
SELECT a.userid, u.NAME
FROM ( SELECT a.userid
FROM (SELECT u.*,
ROW_NUMBER() OVER(PARTITION BY u.userid ORDER BY u.colorid ) AS Row
FROM UserColor u ) a
INNER JOIN (SELECT COUNT(*) cnt FROM #Color) c on a.row = c.cnt)
INNER JOIN User u ON a.Userid = u.Userid
June 27, 2014 at 1:40 pm
Loner (6/27/2014)
I came up with this, not as good as Sean.SELECT a.userid, u.NAME
FROM ( SELECT a.userid
FROM (SELECT u.*,
ROW_NUMBER() OVER(PARTITION BY u.userid ORDER BY u.colorid ) AS Row
FROM UserColor u ) a
INNER JOIN (SELECT COUNT(*) cnt FROM #Color) c on a.row = c.cnt)
INNER JOIN User u ON a.Userid = u.Userid
I don't know about the interview you were in but often times the quality of the solution is not really important. It is more important that it work. You have a couple syntax issues but this basically works (no alias on your outer query).
Being able to get the result is the biggest piece. If there are performance concerns or things you can do to improve it, that for me is not a deal breaker. The little things like doing it faster can be taught on the job. You should be proud of yourself that you are able to put together a query that will get the correct result. There are millions of people who can't do that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2014 at 1:51 pm
Oh yea...interview nerves can be awful.
I interviewed once for an accounting position with a law firm. It was quite conservative and required very conservative clothing. I bought an expensive suit and made sure my hair and makeup and all else were in top shape.
I went to the interview and felt I had answered questions well...but couldn't read any enthusiasm in the interviewer at all.
I left the law building and was looking down while waiting to cross the busy street and noticed that I had on two different shoes!
Attention to detail was not successful.
June 27, 2014 at 1:54 pm
Loner,
Don't feel bad. I failed my Microsoft interview so hard I excused MYSELF from the interviewing process. To give you an idea: I forgot GETDATE(). I was that unnerved. I felt like a complete and utter fricking moron and wondered for months if I had any business being in this industry.
I'm not convinced it worked in my favor long term, but, it happens. You live with it. You move on.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 27, 2014 at 2:25 pm
I did this but now I'm embarrassed at seeing how short and sweet your guys is.
;WITH COLOR_CTE AS (
SELECT DISTINCT u1.Userid,u1.Name,
COUNT(uc.Colorid) OVER (PARTITION BY u1.Userid) AS UserCount
FROM USER1 as u1
INNER JOIN UserColor AS UC ON UC.UserID = u1.Userid)
SELECT DISTINCT cc.Name, cc.UserID
FROM COLOR_CTE cc
WHERE cc.UserCount=(SELECT COUNT(*) FROM COLOR)
***SQL born on date Spring 2013:-)
June 27, 2014 at 2:29 pm
Ok, I couldn't resist.
I took some liberties and modified the data. So here is the setup script
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Color]') AND type in (N'U'))
DROP TABLE Color;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SUser]') AND type in (N'U'))
DROP TABLE SUser;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserColor]') AND type in (N'U'))
DROP TABLE UserColor;
GO
CREATE TABLE Color (ColorID INT, ColorName VARCHAR(100));
INSERT INTO Color(ColorID, ColorName)
SELECT 1, 'Black'
UNION
SELECT 2, 'White'
UNION
SELECT 4, 'Blue'
UNION
SELECT 8, 'Green'
UNION
SELECT 16, 'Red';
CREATE TABLE SUser (Userid INT, UserName VARCHAR(100));
Insert Into SUser (Userid, UserName)
SELECT 1, 'Bill'
UNION
SELECT 2, 'Jim'
UNION
SELECT 3, 'Tom';
CREATE TABLE UserColor (Userid INT, Colorid INT);
INSERT INTO UserColor(Userid, Colorid)
SELECT 1, 1
UNION
SELECT 1, 2
UNION
SELECT 2, 1
UNION
SELECT 2, 2
UNION
SELECT 2, 4
UNION
SELECT 3, 1
UNION
SELECT 3, 2
UNION
SELECT 3, 4
UNION
SELECT 3, 8
UNION
SELECT 3, 16 ;
And here is a working solution (not as simple as Sean's solution)
DECLARE @ColorSelect VARCHAR(100) = 'Black,Blue'
,@ColorID TINYINT;
DECLARE @Color TABLE (ColorName VARCHAR(20));
INSERT INTO @Color
SELECT Item
FROM AdminDB.dbo.stringsplitter(@ColorSelect,',');
SELECT @ColorID = SUM(ColorID)
FROM Color c
INNER JOIN @Color c2
ON c.ColorName = c2.ColorName;
WITH presel as(
SELECT SUM(cp1.colorID) AS ColorID,UserID
FROM UserColor cp1
GROUP BY UserID)
SELECT s.UserName,c.ColorName AS UserColors
FROM SUser s
INNER JOIN presel p
ON s.userid = p.userid
INNER JOIN UserColor uc
ON p.userid = uc.userid
INNER JOIN Color c
ON uc.Colorid = c.colorid
WHERE 1=1
/* If we only want to list the colors specified */
--AND c.Name IN (SELECT ColorName FROM @Color)
/* If we want all colors less than the Sum of Colors input */
--AND uc.ColorID <= @ColorID
/* If we simply want a list of all colors associated to the user who has
the colors available in the input */
AND p.colorID & @ColorID = @ColorID;
This might make a good article 😉
Anyway, I changed it so somebody could input different colors in delimited form. Then take that input and split it using a string splitter of your liking (cough the DelimitedSplit8k comes to mind).
Then do some binary math to compare the values and get the results.
The predicate has a few different options based on possible desired outputs from the query.
You'll notice the liberty taken with the data. I changed the color ids to binary favorable values - think 2^n. This makes it much easier using binary operators and comparisons.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply