8/20/2014
sql_cheatsheet.html
Oracle Relational SQL Cheatsheet.
Types
CHAR(n) CHARACTER(n)
Creating and Deleting Tables
NCLOB
CREATE TABLE
Fixed length string of character n. <table name>
CREATE TABLE part
(<column definition (part_number CHAR(4), part_name
Character string of maximum length n, but
list>,
VARCHAR(25),
of varying size.
(<column name>)); PRIMARY_KEY(part_number));
Integers.
Numbers of precision p, with s digits after
CREATE TABLE
the decimal point.
CREATE TABLE department
<table name>
(department_number CHAR(4)
Date information.
( <colum_definition
CONSTRAINT prim_dept PRIMARY_KEY
Time information.
[ <constraint > ], ..
department_name VARCHAR2(10));
);
Binary Large Object.
DROP TABLE
Character binary large object.
Delete table from database.
<table_name>;
National character sets.
BFILE
Read only external file.
VARCHAR2(n)
NUMBER
NUMBER(p,s)
DATE
TIME
BLOB
CLOB
RAW/LONG RAW
to_char(x)
to_number(x)
to_date(x)
to_multi_byte()
to_single_byte()
chartorowid(x)
rowidtochar(x)
Changing Tables.
ALTER TABLE<table_name>
Binary data, used for import and export.
ADD(CONSTRAINT <constriant_name>
Conversions
PRIMARY_KEY (<column_names>));
Creates a primar
key constraint fo
column.
Converts it's argument to the appropiate
type.
ALTER TABLE
employee
ADD
(department_num
VARCHAR(12));
Converts between single & multi byte
international strings.
ALTER TABLE <table_name>
ADD(<colmn_definition>);
Converts character strings to ROWID's &
back.
Creates a foreign
key constraint fo
column, mapping
hextoraw(x)
Converts between hex and RAW binary
ALTER TABLE <table_name>
a column on anot
rawtohex(x)
format (see types).
ADD (CONSTRAINT <constraint_name>
table. Optional FOREIGN_KEY(<colum_name>)
Operators
DELETE CASCAD
REFERENECES
Usual comparisons. != & <> & ^= are all
maintains =,>,<,>=,<=,!=,<>
foriegn_table_name(<foreign_column_name>)
negative equality tests.
integrity by delet
[ON DELETE CASCADE]);
rows in table whe
AND OR NOT
Boolean operations.
row in foreign ta
SELECT emp_id, name, dept_no FROM
is deleted. BETWEEN
epmployee WHEREemp_id BEWTEEN1 AND
ALTER TABLE
4;
Relax constraint.
DISABLE CONSTRAINT name;
SELECT emp_id, name, dept_no FROM
IN
ALTER_TABLE
Delete constraint
epmployee WHERE emp_id IN (1,2,3,4);
forever.
Regexp match. % = n characters, _ = 1 DROP CONSTRAINT<constraint_name>;
LIKE
character, \ escapes.
Modifying and deleting rows
Constraints
NULL/NOT NULL
Allow/don't allow missing
values.
INSERT
INTO<table_name>
(<colum_name, ..>)
VALUES (<value,
..>);
[CONSTRAINT <constraint
For candidate keys
name>
UNIQUE (<column_name>,..)] alternatives to primary key.
PRIMARY KEY
[CONSTRAINT
<constraint_name>
CHECK (condition)];
FOREIGN KEY
UPDATE
<table_name>
This is the key field for lookup.
SET <column> =
<value>, ..
WHERE
Verification/validation.
<condition>;
This is an index to another
table.
Single Valued Functions
lpad(<string>,<width>,
Pad a string to the right or left
http://www.yagc.ndo.co.uk/cheatsheets/sql_cheatsheet.html
DELETE FROM
<table_name>
[WHERE
<conditon>]
INSERT INTO employee
(employee_number, employee_name,
salary)
VALUES ('7092', 'FORD', 175,66);
UPDATE wine_list
SET note = ''Ideal as an aperitif'
WHERE wine_name = 'Ch.HautRian';
DELETE FROM members
WHERE name
LIKE 'Sharon%';
Querying with Select.
1/2
8/20/2014
sql_cheatsheet.html
[<char>]); rpad(<string>,
<width>,[<char>]);
lower(<string>);
uppper(<string>);
initcap(<string>);
with the given width with the given
char.
Uppercase, lowercase, or inital
upcase the string.
length(<string>);
Returns length, in chars of the
string.
substr(<string>,<start>,
<end>);
Returns a substring from start
index, to end index.
abs(<number>)
sign(<number>)
Absolute value and sign number.
ceil(<number>)
floor(<number>)
Ceiling and floor: Highest and
lowest integer with smallest
difference from float.
mod(<number0>,
<number1>)
round(<number0>,
<number1>)
trunc(<number0>,
<<number1>)
Project and Join. SELECT <columns> FROM <table>
WHERE <criterion>;
Sorting. SELECT .<clauses>
ORDER BY <column [DESC|ASC],..>;
Remainder of x / y; Round x to y
decimal places. Truncate x to y
decimal places
Grouping.
SELECT <select_clauses>
GROUP BY <column [DESC|ASC]..>
sqrt(<number>)
Square root.
HAVING <criterion>; greatest(<experession>,..) Largest and smallest from a list of
least(<expression>,...)
dates, numbers or strings.
vsize(<expression>)
sysdate()
add_months(<date>,
<integer>)
last_day(<date>)
The storage size in bytes for x.
SELECT dept,
AVG(salary)
FROM emp
GROUP BY dept HAVING
avg(salary)>80000
ORDER BY avg(salary
DESC;
SELECT
Column concatenation formatting. firstname||','||lastnam
SELECT <column>||<string>||<column>
Add given number of month to
full_name
<column_alias>... ...;
dates;
FROM team;
Current system date
Return the last day of the month.
months_between(<date0>, Return the number of months
<date1>)
betwwen two dates/
On the fly calculations.
SELECT <expression> FROM DUAL;
new_time(<date>,
<current_timezone>,
<other_timezone>)
Convert date from one timezone to
another.
nvl(<column>,<value>)
Substitute <value> for NULL in the
column.
soundex(x)
SELECT
emp_table.emp_id,
emp_table.dept_no,
dept_table.description
FROM
employee_db.emp_ta
employee_db.dept_ta
WHERE
emp_table.dept_no =
dept_table.dept_no;
SELECT *
FROM emp
ORDER BY empid
DESC; or SELECT
empid, lastname
FROM emp
ORDER BY 2;
Column aliasing. SELECT <column>
AS <alias_column>..;
Return soundex string for fuzzy
matching.
Subqueries. SELECT ...
For every instance of <value> in
decode(<column>,<value>,
WHERE column = (<subquery>);,
column return the matching
<return>,<value>,
<return> value. A bit like a
<return>...)
case/switch.
SELECT 7 * 9
FROM DUAL;
SELECT name,
NVL(spouse,'unmarrie
AS spouse
FROM
emp_db,emp_table;
SELECT empid, dept,
salary
FROM emp
WHERE dept = (
SELECT dept
FROM emp WHERE empid =
78483);
Group functions.
avg()
stddev() variance()
sum(x)
count(x)
max(x)
min(x)
http://www.yagc.ndo.co.uk/cheatsheets/sql_cheatsheet.html
Average of all numbers in column.
Standard deviation f all numbers in
column.
Variance of all numbers in column.
Sum total of all numbers in the column.
Toal number of items in a culumn.
Maximum value found in a column. Minimum value found in column.
2/2
© Copyright 2024 ExpyDoc
