SQL Master article: the introduction of subtle SQL statements

Statement Description: Copy table (copy structure only, source table name: A new table name: b)

Sql:select * into B from a where 1<>1

Description: Copy table (copy data, source table name: A target table name: b)

Sql:insert into B (A, B, c) select d,e,f from B;

Description: Displays the article, the author, and the last reply time

Sql:select a.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where Table.title=a.title) b

Description: Outer join query (table name 1:a table name 2:b)

Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left-out JOIN b on a.a = B.C

Description: Schedule five minutes advance reminder

Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

Description: Two related tables, delete the information in the primary table that has not been in the secondary table

Sql:

Delete from info where NOT EXISTS (SELECT * from Infobz where Info.infid=infobz.infid)

Description:--

Sql:

Select A.num, A.name, B.upd_date, b.prev_upd_date from TABLE1, (select X.num, X.upd_date, Y.upd_date prev_upd_date from (SE Lect NUM, Upd_date, Inbound_qty, Stock_onhand from TABLE2 WHERE to_char (upd_date, ' yyyy/mm ') = To_char (sysdate, ' yyyy/mm ')  X, (SELECT NUM, Upd_date, Stock_onhand from TABLE2 WHERE to_char (upd_date, ' yyyy/mm ') = To_char (To_date (To_char, Sysdate, ' yyyy/mm ') ¦¦ '/01 ', ' yyyy/mm/dd '-1, ' yyyy/mm ')) Y, where x.num = Y.num (+) and X.inbound_qty + NVL (y.stock_onhand,0) & lt;> x.stock_onhand) B WHERE a.num = B.num

Description:--

Sql:

SELECT * from Studentinfo where does exists (SELECT * from student where studentinfo.id=student.id) and system name = ' "&strdepart mentname& "' and professional name = ' &strprofessionname& ' ORDER by sex, source of students, college entrance examination total score
Description: From the database to go to a year of units telephone fee statistics (telephone fee quota of two tables from the list of chemical fertilizers)

Sql:

　　Select a.userper, a.tel, a.standfee, to_char (a.telfeedate,  ' yyyy ')  AS  Telyear, sum (Decode (To_char (a.telfeedate,  ' mm '),  ',  a.factration))  as jan,  sum (Decode (To_char (a.telfeedate,  ' mm '),  ',  a.factration))  as fri, sum ( Decode (To_char (a.telfeedate,  ' mm '),  ',  a.factration))  as mar, sum (Decode (TO_ CHAR (a.telfeedate,  ' mm '),  ' ",  a.factration))  as apr, sum (Decode ( a.telfeedate,  ' mm '),  '  a.factration ')  as may, sum (Decode ( a.telfeedate,  ' mm '),  ' ( a.factration))  as jue,sum (Decode (To_char,   ' mm '),  ' modified ',  a.factration)  as jul, sum (decode To_char (a.telfeedate,  ' mm ') ,  ',  a.factration)  as agu, sum (decode To_char (a.telfeedate,  ' mm '),  ' 09 ' ,  a.factration))  as sep, sum (Decode (To_char (a.telfeedate,  ' mm '),  ' a ',  a.factration))  as oct, sum ( Decode (To_char (a.telfeedate,  ' mm '),  ' one ',  a.factration))  as nov,sum (Decode ( a.telfeedate,  ' mm '),  ' ( a.factration))  AS DEC FROM  (select  A.userper, a.tel, a.standfee, b.telfeedate, b.factration from telfeestand a ,  telfee b where a.tel = b.telfax)  a group by a.userper,  a.tel, a.standfee, to_char (a.telfeedate,  ' yyyy ')    

Note: Four table joint Inquiry question:

Sql:select * from a left inner join B in a.a=b.b right inner join C in a.a=c.c inner join D on A.A=D.D where ....

Description: Get the smallest unused ID number in the table

Sql:

Select (Case when EXISTS (SELECT * from Handle b WHERE b.handleid = 1) THEN MIN (Handleid) + 1 ELSE 1 end) as Handleid Handle WHERE not Handleid in (SELECT a.handleid-1 from Handle a)