CREATE TABLE

The following query is used to create Employee table:

CREATE TABLE EMP
(
  EMPNO NUMBER(4) NOT NULL,
  ENAME VARCHAR2(50),
  JOB VARCHAR2(50),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2) NOT NULL
)

Get table information

There's a lot of ways to get table information.
To me, this is a simple way I often use.
DESCRIBE TABLE_NAME
OR
DESC TABLE_NAME

SQL Server:
sp_help Table_Name

Get datafile information

SELECT  FILE_NAME,
 d.TABLESPACE_NAME,
 d.BYTES datafile_size,
 NVL(SUM(e.BYTES),0) bytes_used,
 ROUND(NVL(SUM(e.BYTES),0) / (d.BYTES), 4) * 100 percent_used,
 d.BYTES - NVL(SUM(e.BYTES),0) bytes_free
FROM  DBA_EXTENTS e, DBA_DATA_FILES d
WHERE d.FILE_ID = e.FILE_ID (+)
GROUP BY FILE_NAME,d.TABLESPACE_NAME, d.FILE_ID, d.BYTES, STATUS
ORDER BY d.TABLESPACE_NAME,d.FILE_ID

EXISTS

Find accounts without contact

SELECT *
FROM ACCOUNT A
WHERE NOT EXISTS (SELECT 1
                FROM CONTACT C
                WHERE C.ACCOUNT_ID = A.ACCOUNT_ID)
You can also query like that:
SELECT *
FROM ACCOUNT A
WHERE A.ACCOUNT_ID NOT IN 
        (SELECT DISTINCT ACCOUNT_ID
         FROM CONTACT)

  

INTERSECT

Get the same records exists in two tables.
I never use this in my job.

SELECT CITY
FROM ACCOUNT
INTERSECT
SELECT CITY
FROM CONTACT

Get database triggers

SELECT TABLE_OWNER,
TABLE_NAME,
TRIGGER_NAME,
TRIGGER_TYPE,
TRIGGERING_EVENT,
STATUS
FROM dba_triggers
ORDER BY TABLE_NAME, TRIGGER_NAME

Get table space information

SELECT TABLESPACE_NAME,
 INITIAL_EXTENT,
 NEXT_EXTENT,
 MIN_EXTENTS,
 MAX_EXTENTS,
 PCT_INCREASE,
 STATUS,
 CONTENTS
FROM  dba_tablespaces
ORDER  BY TABLESPACE_NAME

Create mutiples linked servers

When you need to work with multiple databases from Oracle, for example: 100 databases with name like that DB01, DB02, ..., DB100. All of them use the same user name and password to query data such as scott/tiger. Your job is to connect them from SQL Server. First, you need to create 100 linked servers. It takes much time to create 100 linked servers like that by using create linked server screen.
You can do this by this script.

DECLARE @LINK_NAME VARCHAR(10)
DECLARE @SERVER_NAME VARCHAR(50)
DECLARE @COUNT INT
SET @COUNT = 1;
WHILE @COUNT < 101
BEGIN
SET @LINK_NAME = 'DB' + RIGHT('00' + CAST(@COUNT AS VARCHAR),2);
SET @SERVER_NAME = @LINK_NAME + '{Some name}';
EXEC master.dbo.sp_addlinkedserver @server = @LINK_NAME
, @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=@SERVER_NAME;
EXEC sp_addlinkedsrvlogin @LINK_NAME, 'false', 'domain\user', 'scott', 'tiger';

SET @COUNT = @COUNT + 1;
END

Get database information

SELECT  NAME,
       CREATED,
       LOG_MODE,
       CHECKPOINT_CHANGE#,
       ARCHIVE_CHANGE#
FROM     v$database

SQL Server: sp_databases

Get all user defined sequences

SELECT SEQUENCE_OWNER,
SEQUENCE_NAME,
MIN_VALUE,
MAX_VALUE,
INCREMENT_BY,
CYCLE_FLAG,
ORDER_FLAG,
CACHE_SIZE,
LAST_NUMBER
FROM dba_sequences
WHERE SEQUENCE_OWNER NOT IN ('SYS','SYSTEM')
ORDER BY SEQUENCE_OWNER,SEQUENCE_NAME

Get CPU Usage by Session

SELECT NVL(ss.USERNAME,'ORACLE PROC') UserName,
se.SID,
VALUE CPUUsage
FROM v$session ss,
v$sesstat se,
v$statname sn
WHERE se.STATISTIC# = sn.STATISTIC#
AND NAME LIKE '%CPU used by this session%'
AND se.SID = ss.SID
ORDER BY VALUE DESC

Get DB Version

SELECT *
FROM v$version;

SELECT *
FROM product_component_version;

SQL Server:
SELECT @@VERSION;

Get all DB objects

Get all DB objects including: table, view, stored procedure, function
SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE, OWNER
FROM all_objects
WHERE OBJECT_TYPE IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION')
ORDER BY OBJECT_TYPE, OBJECT_NAME

If you want to exclude system objects:
SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE, OWNER
FROM all_objects
WHERE OBJECT_TYPE IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION')
AND OWNER NOT IN ('SYS', 'SYSTEM')
ORDER BY OBJECT_TYPE, OBJECT_NAME

Get columns of a table

SELECT *
FROM all_tab_cols
WHERE TABLE_NAME = 'Your table name'
AND OWNER = 'Your owner name'

all_tab_cols is a system object in Oracle DB.

SQL Server:
using this is a simple way: sp_help 'Your table name'

Online database

I think there's a lot of reasons you want to create an online database. To me, sometimes I want my applications to be able to run everywhere as an SaaS (Software as a Service) with an Internet connection.
Some of online databases I have worked on: Salesforce.com, dabble.com, and Google Spreadsheet
Google spreadsheet is free as you knew but you need some skills about Google code such as Google API, Google Visualization, etc to be able to work on it.
dabble.com is also free if you make your dabase public as Common Creative License => I am using this way.
Salesforce.com is not free but you can test it with 20MB data when you register a free developer account.

UNION & UNION ALL

CONTACT DATA:
ACCOUNT_ID | CONTACT_NAME| JOB_TITLE
---------------------------------------------------
10000000001 | ABC | CEO
10000000001 | DEF | CIO
10000000002 | OPQ | CEO
10000000002 | XYZ | HR

SELECT JOB_TITLE
FROM CONTACT WHERE ACCOUNT_ID = 10000000001
UNION
SELECT JOB_TITLE
FROM CONTACT WHERE ACCOUNT_ID = 10000000002

Result:
JOB_TITLE
------------
CEO
CIO
HR

SELECT JOB_TITLE
FROM CONTACT WHERE ACCOUNT_ID = 10000000001
UNION ALL
SELECT JOB_TITLE
FROM CONTACT WHERE ACCOUNT_ID = 10000000002

Result:
JOB_TITLE
-------------
CEO
CIO
CEO
HR

HAVING

SELECT SALES_REGION, AVG(REVENUE)
FROM ACCOUNT
GROUP BY SALES_REGION
HAVING AVG(REVENUE) > 20000

List all SALES_REGION has average revenue greater than 20000

Note that HAVING is condition for group.
This query is NOT valid:
SELECT SALES_REGION, AVG(REVENUE)
FROM ACCOUNT
WHERE AVG(REVENUE) > 20000
GROUP BY SALES_REGION

Equi Join

List all accounts with their contacts

SELECT ACCOUNT_NAME, CONTACT_NAME
FROM ACCOUNT, CONTACT
WHERE ACCOUNT.ACCOUNT_ID = CONTACT.ACCOUNT_ID

SQL Server: can use INNER JOIN like that

SELECT ACCOUNT.ACCOUNT_NAME, CONTACT.CONTACT_NAME
FROM ACCOUNT INNER JOIN CONTACT ON ACCOUNT.ACCOUNT_ID = CONTACT.ACCOUNT_ID

Convert to number

SELECT TO_NUMBER(ZIP_CODE)
FROM ACCOUNT;

SQL Server:
SELECT CAST(ZIP_CODE AS INT)
FROM ACCOUNT;
You can also use CONVERT function in SQL Server

DECODE

SELECT ACCOUNT_NAME, BILLING_ADDRESS,
DECODE(COUNTRY_CODE, 'US', 'United States', 'VN', 'Vietnam', 'Undefined')
FROM ACCOUNT;

Explanation:
IF COUNTRY_CODE = 'US' THEN Return 'United States'
ELSE IF COUNTRY_CODE = 'VN' THEN Return 'Vietnam'
ELSE Return 'Undefined

SQL Server:
CASE COUNTRY_CODE WHEN 'US' THEN 'United States'
WHEN 'VN' THEN 'Vietnam'
ELSE 'Undefined'
END;

SYSDATE

SELECT SYSDATE FROM DUAL;

Return current date in server.
SYSDATE
---------
10-APR-09
SQL Server: SELECT GETDATE();

Math Functions

• ROUND(col/value,n)
• TRUNC(col/value,n)
• CEIL(col/value)
• FLOOR(col/value)
• POWER(col/value,n)
• EXP(n)
• SQRT(col/value)
• SIGN(col/value)
• ABS(col/value)
• MOD(value1,value2)
• LOG(m,n)
• SIN(n)
• SINH(n)
• TAN(n)
• TANH(n)
• COS(n)
• COSH(n)
  

String Functions

• LTRIM(col/value[, 'char(s)'])
• RTRIM(col/value[,'char(s)'])
• LENGTH(col/value)
• TRANSLATE(col/value,from,to)
• REPLACE(col/value,string,replacement_string)
  

String Functions

• LOWER(col/value)
• UPPER(col/value)
• INITCAP(col/value)
• CONCAT(col1/value1, col2/value2)
• LPAD(col/value,n[, 'string'])
• RPAD(col/value,n[,'string'])
• SUBSTR(col/value, pos[,n])
• INSTR(col/value,'string')
  
• INSTR(col/value, 'string',pos,n)

WHERE

SELECT EMP_ID, EMP_NAME
FROM EMPLOYEE
WHERE SAL > 10000;

Operators:
=, >, >=, <, <=, BETWEEN ... AND, IN (List), LIKE, IS NULL, IS NOT NULL, !=, <>, NOT BETWEEN .. AND, NOT IN, NOT LIKE, AND, OR

LIKE wildcards: %, _
IN with numbers: IN (123, 456, 789, 678)
IN with string: IN ('ABC', 'DEF', 'GHI', 'CDE')

ORDER BY

Default: ASC

SELECT ACCOUNT_ID, ACCOUNT_NAME
FROM ACCOUNT
ORDER BY ACCOUNT_ID;

SELECT ACCOUNT_ID, ACCOUNT_NAME
FROM ACCOUNT
ORDER BY ACCOUNT_ID DESC;

NVL

Convert NULL value

SELECT EMPLOYEE_NAME, SALARY*12 + NVL(COMM, 0) ANNUAL_SALARY
FROM EMPLOYEE;

SELECT NVL(LastModifiedDate, '2009-01-01')
FROM ACCOUNT;

SELECT NVL(REVENUE, 0)
FROM ACCOUNT;

SELECT NVL(BILLING_ADDRESS, 'DBA')
FROM ACCOUNT;

SQL Server: ISNULL(BILLING_ADDRESS, 'DBA');

Concatenation Operator

Without literal
SELECT EMPNO || ENAME EMPLOYEE
FROM EMP;

With literal
SELECT EMPNO || '-' || ENAME EMPLOYEE
FROM EMP;

SQL Server: + operator
SELECT EMPNO + '-' + ENAME EMPLOYEE
FROM EMP;

SELECT

List department number, employee name, and manager number in EMP table:

SELECT DEPTNO, ENAME, MGR
FROM EMP;

With Alias
SELECT ENAME, SAL*12 ANNSAL, COMM
FROM EMP;

SELECT * FROM EMP;