Pdf Sql Query Interview Questions And Answers
Top 50 SQL Interview Questions & Answers. Example: SQL Server. SQL stands for Structured Query Language, and it is used to communicate with the.
Sql Queries For Practice With Answers Free Download
Advanced SQL Interview Questions and AnswersHere are some complex SQL interview problems that are for people who are looking for more advanced and challenging questions, along with the answers and complete explanations. Try to figure out the answer to the questions yourself before reading the answers.Suppose we have 2 tables called Orders and Salesperson shown below:SalespersonOrdersIDNameAgeSalary1Abe611400002Bob34440005Chris34400007Dan41520008KenJoe3838000NumberorderdatecustidsalespersonidAmount108/2//0346042400502/3//2//6/9897150Now suppose that we want to write SQL that must conform to the SQL standard.We want to retrieve the names of all salespeople that have more than 1 order from the tables above. You can assume that each salesperson only has one ID. If that is the case, then what (if anything) is wrong with the following SQL?:SELECT NameFROM Orders, SalespersonWHERE Orders.salespersonid = Salesperson.IDGROUP BY salespersonidHAVING COUNT( salespersonid ) 1The answer and explanation to advanced SQL question 1There is definitely something wrong with the SQL above, and it is probably something that most beginner SQL programmers may not notice. The problem is that the SQL Standard says that we can not select a column that is not part of the group by clause unless it is also contained within an aggregate function.
Sql Query Practice Questions Answers Pdf
If we try to run the SQL above in SQL Server, we would get an error that looks like this:Column 'Name' is invalid in the select list because it isnot contained in either an aggregate function orthe GROUP BY clause. You might be confused now, so let’s explain what that error means in plain English and through some simple examples. The most important thing you should take out of this discussion is understanding exactly why we get that error, and how to avoid it. This is why almost all database implementations return an error when the SQL above is run (with the notable exception of MySQL) – and this is why the SQL does not conform to the Standard. In SQL Server running the SQL above will return the same error that we showed earlier.Let’s explain even further in case the problem with that SQL is not crystal clear.
The order of operations in which things will happen with the SQL above is:1. The 2 tables are joined on the condition that theStarbucksEmployees.StoreID column value is equal to theStarbucksStores.storeid column values.2. Groups are then created for each city - which means thateach distinct city will have it's own 'group'.
So, there willbe a total of 3 groups one each for San Francisco, New York,and Los Angeles.3. The data we are interested in is selected from each groupthat is created in step 2.Because we end up with different groups based on the city, when we select a count(.), that will find the total count of rows in each and every group. But, the problem is that when we select HourlyRate, there will be multiple values for the HourlyRate within each group. For example, for the group created by the city of San Francisco there will be 4 different values for the HourlyRate – 14, 10, 11, and 13. So the question is which value of the HourlyRate should be selected from each group?
Well, it could be any one of those values – which is why that SQL results in an error. This is because what we are asking for is NOT specific enough – hopefully this is crystal clear now to you.If the same HourlyRate were part of an aggregate function like MAX then it would simply return the highest HourlyRate within each group. And that is why having an aggregate function would fix the SQL error – because only one value will be selected from any given group.So, this SQL is perfectly fine because we are more specific in what we ask for – but this SQL would only work for you if you actually want the highest HourlyRate for each city.