Oracle interview questions: complex queries and instance analysis

Oracle interview questions: complex queries and instance analysis

When you need to perform complex processing on the queried results, you can obtain the expected results by means of joint queries and subqueries. The following is a typical question, which is intended to inspire readers.

1. Question requirements:

 

Table NBA records 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
Celts 2008
Lakers 2009

Lakers 2010

Write an SQL statement to query the names of consecutive champions and the start and end times of the consecutive years. The results are as follows:

TEAM BEGIN END
-------------------------------------------
Bulls 1991 1993
Rocket 1994 1995
Bulls 1996 1998
Lakers 2000 2002
Lakers 2009 2010
2. Question Analysis:

This question can be divided into two steps:

Step 1: Find out all teams that continuously win the championship

Second

 

Step

: Computing

 

Start and end years of the winning team

3. The Code implements "1" to create a table and insert some data:

-- Create tablecreate table NBA(  team NUMBER,  year NUMBER)tablespace USERS  pctfree 10  initrans 1  maxtrans 255  storage  (    initial 64K    minextents 1    maxextents unlimited  );

For convenience, Here we only use numbers to replace the team and insert some test data at will:


"2" we observed that if the team won the championship for more than one year in a row, the team that appeared in the previous year will still appear in the next year, that is, the team (year) = team (year + 1); for example, the following record segment:

Piston 1990
Bull 1991
Bull 1992
Bull 1993
Rocket 1994

The specific SQL statement is:

select n2.team,n2.year from (select * from nba) n1  join(select * from nba) n2on n1.team=n2.teamwhere n1.year=n2.year+1

The execution result is:


At the end of "3", the above query results are used as subqueries to process the data. Because the following year of the championship is not the champion, 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 nba) n1  join(select * from nba) n2on n1.team=n2.teamwhere n1.year=n2.year+1) nngroup by (nn.year-rownum)order by BeginYear;

The final execution result is as follows:

4. Summary

In the face of similar interview questions or questions, although there are not many knowledge points involved (subqueries, connection queries, etc.), there are some data conversion and nesting of queries, it may cause some readers with poor psychological quality to feel helpless. Therefore, in the face of similar complex problems, we should learn to use the "sharding method" for decomposition and solve the problem step by step.