SQL FAQ: Common SQL questions, part 2

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin? Oracle tutorials, tips and FAQs SearchOracle.com podcasts Do I need to buy an Oracle license for a development database? Special Report: Collaborate '09 IOUG on 2009 Collaborate conference: what's hot, what's not How to create a database link in Oracle Most clicked stories of 2007 How do I do that in Oracle? Counting a row's NULL columns Data integration challenges and solutions revealed How many redo log files? Oracle tutorials, tips and FAQs Research Oracle and SQL Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to use the Oracle Database SQL Reference Manual How to use SQL Developer to run SQL statements How to work with the Oracle database home page How to use SQL*Plus in Oracle How to use SQL Developer to work with an Oracle database How to view and edit table column definitions How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join Oracle and SQL Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary 10g  (SearchOracle.com) delimiter  (SearchOracle.com) extent  (SearchOracle.com) field  (SearchOracle.com) flexfield  (SearchOracle.com) foreign key  (SearchOracle.com) Oracle  (SearchOracle.com) row  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

In Common SQL questions, part 1, we were introduced to three types of common SQL questions:

1. novice questions
2. homework questions
3. 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.