Create table with primary key

Create ACCOUNT table with PRIMARY KEY named ACCOUNT_PKEY on ACCOUNT_ID column

CREATE TABLE ACCOUNT
(
 ACCOUNT_ID NUMBER(6) CONSTRAINT ACCOUNT_PKEY PRIMARY KEY,
 ACCOUNT_NAME VARCHAR2(255),
 BILLING_COUNTRY VARCHAR(50),
 BILLING_CITY VARCHAR(50),
 ...
)

Compare more than one value

Get employees have minimum salary in their departments.

SELECT ENAME, JOB, DEPTNO, SAL
FROM EMP
WHERE (SAL,DEPTNO) IN (SELECT MIN(SAL), DEPTNO
                    FROM EMP
                    GROUP BY DEPTNO)

Get all constraints and types

List all tables with their constraints and constraint types.

SELECT OWNER,
       TABLE_NAME,
       CONSTRAINT_NAME,
       DECODE(CONSTRAINT_TYPE, 'C','Check',
                               'P','Primary Key',
                               'U','Unique',
                               'R','Foreign Key',
                               'V','With Check Option') TYPE,
       STATUS
FROM dba_constraints
ORDER BY OWNER, TABLE_NAME, CONSTRAINT_NAME

Get objects modified recently

Get database objects that are modified in last 7 days.

SELECT OWNER,
 OBJECT_NAME,
 OBJECT_TYPE,
 TO_CHAR(LAST_DDL_TIME,'YYYY/MM/DD HH24:MI:SS') LAST_MODIFIED_DATE,
 TO_CHAR(CREATED,'YYYY/MM/DD HH24:MI:SS') CREATED_DATE,
 STATUS
FROM dba_objects
WHERE  (SYSDATE - LAST_DDL_TIME) < 7

Self join

You have to use alias for self join.
List all accounts with their parent account.

SELECT t1.ACCOUNT_NAME, t2.ACCOUNT_NAME AS PARENT_ACCOUNT
FROM ACCOUNT t1, ACCOUNT t2
WHERE t1.PARENT_ACCOUNT_ID = t2.ACCOUNT_ID(+)
Use outer join here to list accounts without parents.

Outer join

List all accounts and their contacts, show contact with NULL value if an account has no contacts.
SELECT t1.ACCOUNT_NAME, t2.CONTACT_NAME
FROM ACCOUNT t1, CONTACT t2
WHERE t1.ACCOUNT_ID = t2.ACCOUNT_ID(+)

In SQL Server:
SELECT t1.ACCOUNT_NAME, t2.CONTACT_NAME
FROM ACCOUNT t1 LEFT JOIN
CONTACT t2 ON t1.ACCOUNT_ID = t2.ACCOUNT_ID

DUAL Table

System table of owner SYS, used to select one value from system function, user-defined function which return scalar value
SELECT SYSDATE FROM DUAL;

SYSDATE
---------
02-MAY-09

SELECT SYSDATE FROM ACCOUNT;
SYSDATE
---------
02-MAY-09
02-MAY-09
.......

SELECT fnGetAccountNameByAccountID(100000) FROM DUAL;
fnGetAccountNameByAccountID is a user-defined function to return scalar value (Account Name) based on AccountID parameter.

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