Requires Free Membership to View
In Common SQL questions, part 1, we were introduced to three types of common SQL questions:
- novice questions
- homework questions
- serious SQL questions
2. Homework questions
Notice anything about the following questions?
- Write a query to get the second highest amount in the table
- List last name and hire date of any employee in the same department as Zlotkey
- I always get puzzled up when asked some typical questions in interview like the nth highest salary in the EMP table
- In SQL from two different tables select the first five highest salary
- How can we find the second highest salary from three departments named D1, D2 and D3
- How to select last ten max(sal) from EMP
- List the highest salary, the lowest salary, the average salary and the total salaries of employees
- What are the different way of selecting 3rd highest salary from EMP table?
- How to get Top 5th, 6th, Nth salary from emp table of oracle
- Get the doctor with the highest count of visits
- How to find the 2nd, 3rd, 4th, ... nth max of a column in a table
- How to find out the fifth max salary from employee table
- I want to know how to retrieve third highest and lowest from a column
- Need help finding the second largest pay and second smallest pay from the salary table
- How do I get the top 3 highest salary from the emp table
- Dear Sir! How can I get the second maximum salary?
We will only occasionally answer a homework question. However, sometimes the answer will not really be what the student is looking for. For example,
Find the three smallest numbers from a column of numbers.
Answer: The three smallest numbers (16 December, 2005)How to select nth row from a table if they are not in any order?
Answer: The Nth row in a table (30 September, 2005)What is the query to select empname and his managername from an emp table?
Answer: Select empname and managername (17 January, 2006)How can I select the third, fourth and fifth rows, out of a 100 rows?
Answer: Select 3rd, 4th, 5th of 100 rows (10 April, 2002)
Then there are more "substantial" homework questions, which require more advanced SQL, such as complex joins and subqueries. We usually don't touch these, for obvious reasons. Hint: no, it's not because they always somehow seem to involve bosses.
- Which are the workers that live in the same city as their bosses?
- Display the name of the manager who is having maximum number of subordinates.
- Find employees in each department who make more money than their immediate manager.
We answer obvious homework questions only if there's something interesting for all readers, or some subtle nuance about SQL that's worth discussing. For example,
I want to display all the employees who report to a person.
Answer: All employees under a given manager (23 April, 2007). This question was selected because it's a very common scenario (especially if not involving bosses and employees, but similar structures). It's a tough problem, and there is comprehensive discussion.
Finally, there are the "esoteric" homework questions. We speculate that these are questions from university assignments. We never answer these, either.
- Why are query languages such as SQL based on relational calculus rather than relational algebra?
- SQL is a set oriented language. Explain this statement and outline why in some circumstances, a navigational query language might be prefered.
- Explain what is meant by query decomposition and query optimization.
In Common SQL questions, part 3, we'll see some serious questions that come up often, which we do try to answer.
This was first published in July 2007
Join the conversationComment
Share
Comments
Results
Contribute to the conversation