Common SQL face questions

1, a group of call records (total 5 million):
ID caller number called number call start time call end time talk length
1 98290000 0215466546656 2007-02-01 09:49:53.000 2007-02-01 09:50:16.00023
2 98290000 021546654666 2007-02-01 09:50:29.000 2007-02-0109:50:41.000 12
3 98290000 021546654666 2007-02-01 09:50:58.000 2007-02-0109:51:12.000 14
4 68290900 0755133329866 2007-02-01 10:04:31.000 2007-02-01 10:07:13.000162
5 78290000 0755255708638 2007-02-01 10:48:26.000 2007-02-01 10:49:23.00057
6 78290000 0755821119109 2007-02-01 10:49:39.000 2007-02-01 10:52:55.000196
7 78290000 035730928370 2007-02-01 11:30:45.000 2007-02-0111:31:58.000 73
8 78290000 0871138889904 2007-02-01 11:33:47.000 2007-02-01 11:35:00.00073
9 68290000 035730928379 2007-02-01 11:52:20.000 2007-02-0111:54:56.000 156
10 68290000 0298521811199 2007-02-01 12:44:45.000 2007-02-01 12:45:04.000 19
Call record ID that is greater than 10 seconds between two calls of the same number

1 CREATE TABLE Tonghua2 ( 3Idint   , 4Calling number varchar ( the), 5Called Number varchar ( the), 6 Call Start time datetime,7 Call End time datetime,8Talk durationint   , 9 ) Ten TRUNCATE TABLE Tonghua One INSERT INTO Tonghua A Select   1,'98290000','0215466546656','2007-02-01 09:49:53.000','2007-02-01 09:50:16.000', at  -UNION ALLSelect   2,'98290000','021546654666','2007-02-01 09:50:29.000','2007-02-01 09:50:41.000', A  -UNION ALLSelect   3,'98290000','021546654666','2007-02-01 09:50:58.000','2007-02-01 09:51:12.000', -  theUNION ALLSelect   4,'68290000','0755133329866','2007-02-01 10:04:31.000','2007-02-01 10:07:13.000',162  -UNION ALLSelect   5,'78290000','0755255708638','2007-02-01 10:48:26.000','2007-02-01 10:49:23.000', $  -UNION ALLSelect   6,'78290000','0755821119109','2007-02-01 10:49:39.000','2007-02-01 10:52:55.000',196  -UNION ALLSelect   7,'78290000','035730928370','2007-02-01 11:30:45.000','2007-02-01 11:31:58.000', the  +UNION ALLSelect   8,'78290000','0871138889904','2007-02-01 11:33:47.000','2007-02-01 11:35:00.000', the  -UNION ALLSelect   9,'68290000','035730928379','2007-02-01 11:52:20.000','2007-02-01 11:54:56.000',156  +UNION ALLSelect   Ten,'68290000','0298521811199','2007-02-01 12:44:45.000','2007-02-01 12:45:04.000', + 

Unwind Code

The above is the creation of the table

1 SELECT a.* from Tonghua as A INNER joins Tonghua as B on a.id = b.id-12 WHERE   UNION SELECT b.* from Tonghua as B INNER joins Tonghua as A on a.id = b.id-13 WHERE  

Unwind Code

1 Select   a.*,b.* from   2 tonghua   A,tonghua   b   where  b.id> a.id   and   b.id= (select   min (id) from      tonghua   where  id> a.id    3 and   (a. Calling number =b. Calling number   or   a. Calling number =b. Called Number  or a. Called number =b. Calling number   or   a. Called Number =   4   and DATEDIFF (minute,a. Call end time, B. Call start time) >=5 and   DATEDIFF ( Second,a. Call end time, B. Call start time) >=

Unwind Code

These are the methods that I think are right.

2. Use a SELECT statement to find out the names of all students with 80 points (80 points) or more, and write out all the possible options.

1 Use Qqmdb2 Go3  ifOBJECT_ID ('SC') isNotNULL4 drop table SC5 CREATE TABLE SC6  (7NameChar(Ten),8KcChar(Ten),9Scoredecimal(5,2)Ten   ) One   A INSERT INTO SC -Values'Zhang San','language', the), -('Zhang San','Mathematics',Bayi), the('Zhang San','English', -), -('John Doe','language', -), -('John Doe','Mathematics', the), -('John Doe','English', -), +('Harry','language', -), -('Harry','Mathematics', -), +('Harry','English', -)

Unwind Code

The above is the creation of the table

1 Select name from SC GROUP by name have min (score) >=

1 Select  from where score >= GROUP by name has COUNT (score) >=3  -The first root score is greater than 80 minutes, because it is 3 family, So to score count>3

Unwind Code

1 Select  from where a.name=b.name and B.name=c.name and A.name=c.name and A.KC<>B.KC and A.KC<>C.KC and B.KC<&G T;C.KC and a.score>= and b.score>= and c.score>=

Unwind Code

1 Select  from where inch (Selectfromwhere score<)-It is not recommended to use this

Unwind Code

3. The 3 countries with the highest number of fans per team are required

Fans (Team,nationality,fancount)

' Barcelona ', ' Germany ', 12000
' Barcelona ', ' Spain ', 18000
' Barcelona ', ' China ', 13000
' Barcelona ', ' USA ', 15000
' Barcelona ', ' Italy ', 11000
' Real Madrid ', ' France ', 19000
' Real Madrid ', ' Germany ', 13000
' Real Madrid ', ' USA ', 17000
' Real Madrid ', ' Korea ', 16000
' Real Madrid ', ' UK ', 12000

1 Select  from where 2 Select  from where t1.team=t2.team3and   t2.fancount>=t1.fancount) <=3  -- Sub-queries Sacrifice performance

Unwind Code

There are some questions about borrowing, according to the ISBN, Time, do some inquiries ...

Common SQL face questions