Oracle face questions: Complex queries and instance resolution

When the query to the results of complex processing, you can use the union query, sub-query, etc. to get the desired results. The following is a specific classic question, hoping to inspire readers.

1. Title Requirements:

Table NBA records the team

Name and year of the winning team:

TEAM year
--------------------------------------------------
Piston 1990
Bull 1991
Bull 1992
Bull 1993
Rocket 1994
Rocket 1995
Bull 1996
Bull 1997
Bull 1998
Spurs 1999
Lakers 2000
Lakers 2001
Lakers 2002
Spurs 2003
Piston 2004
Spurs 2005
Heat 2006
Spurs 2007
Celtic 2008
Lakers 2009

Lakers 2010

Please write out an SQL statement to find out which of the successive champions have been won during this period, and the result of the successive years is as follows:

TEAM BEGIN END
-------------------------------------------
Bull 1991 1993
Rockets 1994 1995
Bull 1996 1998
Lakers 2000 2002
Lakers

2. Topic Analysis:

This problem can be broken down into two steps:

The first step: Find all the teams that win the Championship

Second

Step

: Calculate

The starting and ending year for a team that won the championship continuously

3. The code implements "1" to build a table and insert some data:

--Create tablecreate table NBA (team number, year number  ) tablespace USERS  pctfree  Initrans 1  Maxtrans 255  Storage  (    initial 64K    minextents 1    maxextents unlimited  );

for the sake of convenience, here are the numbers instead of the team, inserting some test data:


"2" We observed that, if the winner is more than one year in a row, the team that appeared in the previous year will still be there,That is: Team(year)=team (year+1);For example, the following record fragment:

Piston 1990
Bull 1991
Bull 1992
Bull 1993
Rocket 1994

The specific SQL statements are:

Select N2.team,n2.year from (SELECT * from NBA) N1  joins (SELECT * from NBA) N2on N1.team=n2.teamwhere n1.year=n2.year+1

The result of the execution is:


"3" Finally, the above query results as a subquery, the data processing, because the winner of the last year of the following year is not a champion,So the end year is

Max (year) + 1:

Select Max (Nn.team) team,min (nn.year) Beginyear,max (nn.year) +1 endyear from (select N2.team,n2.year from (SELECT * from NB A) N1  join (SELECT * from NBA) N2on N1.team=n2.teamwhere n1.year=n2.year+1) Nngroup by (Nn.year-rownum) Order by Beginye Ar

The following is the final execution result:

4. Summary thinking

In the face of similar interview topics or problems, although the knowledge points involved (sub-query, connection query, etc.) are not many, but because there are some data conversion and query nesting, may cause some poor psychological quality of readers unprepared. Therefore, in the face of similar complex problems, we should learn to use the "split method" to decompose, step by step to solve the problem.

I wish you success!

Oracle face questions: Complex queries and instance resolution