Excellent SQL statement

Note: copy a table (only copy structure, source table name: a new table name: B)
 
 
SQL: select * into B from a where 1 <> 1
 
Description: copy a 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, Submitter, and 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: External join query (table name 1: Table a 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
 
Note: Five minutes in advance of the schedule
 
SQL: select * from Schedule where datediff ('minute ', f Start Time, getdate ()> 5
 
Note: Two associated tables are used to delete information that is already unavailable to the primary table in the secondary table.
 
SQL:
 
Delete from info where not exists (select * from infobz where info. infid = infobz. infid)
 
Note :-
 
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 (select 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') then comment '/01', 'yyyy/MM/dd')-1, 'yyyy/mm ')) y,
 
Where x. NUM = Y. NUM (+)
 
And x. INBOUND_QTY + NVL (Y. STOCK_ONHAND, 0) <> X. STOCK_ONHAND) B
 
Where a. NUM = B. NUM
 
Note :-
 
SQL:
 
Select * from studentinfo where not exists (select * from student where studentinfo. id = student. id) and Department name = '"& strdepartmentname &"' and major name = '"& strinclusionname &" 'order by gender, student source, total score of the College Entrance Examination
 
Note: Go to the telephone fee statistics of each unit for one year from the database (two sources of the fixed telephone fee congratulation fertilizer List)
 
SQL:
 
SELECT a. userper, a. tel, a. standalone, TO_CHAR (a. telfeedate, 'yyyy') AS telyear,
 
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '01', a. factration) as jan,
 
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '02', a. factration) as fri,
 
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '03', a. factration) as mar,
 
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '04 ', a. factration) as apr,
 
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '05 ', a. factration) as may,
 
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '06', a. factration) as jue,
 
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '07 ', a. factration) as jul,
 
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '08 ', a. factration) as agu,
 
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '09', a. factration) as sep,
 
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '10', a. factration) as oct,
 
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '11', a. factration) as nov,
 
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '12', a. factration) AS DEC
 
FROM (SELECT a. userper, a. tel, a. standalone, B. telfeedate, B. factration
 
From telfeestand a, telstmb
 
WHERE a. tel = B. telfax)
 
Group by a. userper, a. tel, a. standalone, TO_CHAR (a. telfeedate, 'yyyy ')
 
Notes: Four-table join query problems:
 
SQL: select * from a left inner join B on. a = B. B right inner join c on. a = c. c inner join d on. a = d. d where .....
 
Note: Obtain 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
 
FROM Handle
 
Where not HandleID IN (SELECT a. HandleID-1 FROM Handle)