Some Basic SQL Query

Some SQL Queries

1. Get 2nd Minimum Number Of a Table:

SELECT MIN(number) FROM testTable  WHERE number > ( SELECT MIN( number )

                FROM testTable  )

2. Get 2nd Maximum Number Of a Table:

SELECT Max(number) FROM testTable  WHERE number > ( SELECT Max( number )

                FROM testTable  )

3. Not in query

SELECT * FROM PARCELS WHERE PARCELID NOT IN (SELECT PARCELID FROM FIRES);

or, more efficiently,

SELECT * FROM PARCELS P WHERE NOT EXISTS (SELECT NULL FROM FIRES F WHERE P.PARCELID = F.PARCELID);

4. Example: Find the parcels thathave not obtained a permit:

SELECT * FROM PARCELS WHERE (PID, WPB) NOT IN (SELECT PID, WPB FROM PERMITS);

or, more efficiently,

SELECT * FROM PARCELS P WHERE NOT EXISTS (SELECT NULL FROM FIRES F

WHERE P.PARCELID = F.PARCELID);

5. Get Table names from db

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'