September 30, 2014

Lessons I learned from SQL Interview

So today I had a technical interview with a company in Seattle. It was my first technical interview after two months of job hunting in the west coast. I am writing this posts to remember what I made a mistake in SQL as people usually say we learn lessons from mistakes and failure. 

First, About the rule that "GROUP BY function only allows you to SELECT two columns at a time". 
Remark: This does NOT include the new column you generate by using COUNT!!
Typical way you write a query with GROUP BY is like this:

SELECT Column1, Column 2 FROM TableName
GROUP BY Column1; 

HOWEVER, if you want to COUNT something else besides Column 1 and Column 2, you can write:

SELECT Column1, Column2, COUNT(Column3) 
FROM TableName
GROUP BY Column 1; 
SQL will not generate the errors when you put the third elements "COUNT(Column3) before "FROM TableName GROUP BY Column1" because "COUNT(Column3)" is not considered as the third column in the "GROUP BY". 

How about if you use JOIN to make it more complex. Then your query would be:
SELECT Table1.Column1, Table2.Column2, COUNT (Table2.Column3))
FROM Table1 JOIN Table 2 
ON Table1. Primarykey = Table2. Foreignkey
GROUP BY Column1; 

The reasons why you might better use Table1.Column1 instead of Column1 in this query when you have two tables is that the names of two columns in two tables might be the same. 

Second, About the data type of the date
Remember as the data type of the date is VARCHAR which means characters, you cannot use WHERE Date = 2004 to select the years. Assuming that the date in the table would be like "01-JAN-2004". You should write the query:
SELECT Column1, Column2 FROM Table1
WHERE Date LIKE "%2004" ; 

A little bit about the interview process:
The interviewer first asked me about a few questions about Tableau. For example: If I have performance issues with Tableau? If I have experience sharing the dashboard online with other people? How can I get the data from outside resource to Tableau? How can I connect two data at the same time in Tableau? How to change the data format when I upload the workbook to the public profile? 
Then we came to the chatting board to start the SQL questions. I listed the lessons I learned from the interview above. 

All the best to college students like me in the job market : ) 

No comments:

Post a Comment