Sometimes we need a little reminder, below is a lookup sheet for some the more common commands, and how to use them.
Types |
|
CHAR(n) |
Fixed length string of character n. |
VARCHAR2(n) |
Character string of maximum length n, but of varying size. |
NUMBER |
Integers. |
NUMBER(p,s) |
Numbers of precision p, with s digits after the decimal point. |
DATE |
Date information. |
TIME |
Time information. |
BLOB |
Binary Large Object. |
CLOB |
Character binary large object. |
NCLOB |
National character sets. |
BFILE |
Read only external file. |
RAW/LONG RAW |
Binary data, used for import and export. |
Conversions |
|
to_char(x) |
Converts it’s argument to the appropiate type. |
to_multi_byte() |
Converts between single & multi byte international strings. |
chartorowid(x) |
Converts character strings to ROWID’s & back. |
hextoraw(x) |
Converts between hex and RAW binary format (see types). |
Operators |
|
=,>,<,>=,<=,!=,<> |
Usual comparisons. != & <> & ^= are all negative equality tests. |
AND OR NOT |
Boolean operations. |
BETWEEN |
SELECT emp_id, name, dept_no FROMepmployee WHEREemp_id BEWTEEN1 AND4; |
IN |
SELECT emp_id, name, dept_no FROMepmployee WHERE emp_id IN (1,2,3,4); |
LIKE |
Regexp match. % = n characters, _ = 1 character, \ escapes. |
Constraints |
|
NULL/NOT NULL |
Allow/don’t allow missing values. |
[CONSTRAINT <constraint name> |
For candidate keys – alternatives to primary key. |
PRIMARY KEY |
This is the key field for lookup. |
[CONSTRAINT<constraint_name> |
Verification/validation. |
FOREIGN KEY |
This is an index to another table. |
Single Valued Functions |
|
lpad(<string>,<width>,[<char>]); rpad(<string>,<width>,[<char>]); |
Pad a string to the right or left with the given width with the given char. |
lower(<string>);uppper(<string>);initcap(<string>); |
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>) |
Remainder of x / y; Round x to y decimal places. Truncate x to y decimal places |
sqrt(<number>) |
Square root. |
greatest(<experession>,..)least(<expression>,…) |
Largest and smallest from a list of dates, numbers or strings. |
vsize(<expression>) |
The storage size in bytes for x. |
sysdate() |
Current system date |
add_months(<date>,<integer>) |
Add given number of month to dates; |
last_day(<date>) |
Return the last day of the month. |
months_between(<date0>,<date1>) |
Return the number of months betwwen two dates/ |
new_time(<date>,<current_timezone>, |
Convert date from one timezone to another. |
nvl(<column>,<value>) |
Substitute <value> for NULL in the column. |
soundex(x) |
Return soundex string for fuzzy matching. |
decode(<column>,<value>, |
For every instance of <value> in column return the matching <return> value. A bit like a case/switch. |
Creating and Deleting Tables |
|
CREATE TABLE<table name> |
CREATE TABLE part (part_number CHAR(4), part_name VARCHAR(25),PRIMARY_KEY(part_number)); |
CREATE TABLE<table name> |
CREATE TABLE department (department_number CHAR(4) CONSTRAINT prim_dept PRIMARY_KEY, department_name VARCHAR2(10)); |
DROP TABLE<table_name>; |
Delete table from database. |
Changing Tables. |
|
ALTER TABLE<table_name> |
Creates a primary key constraint for a column. |
ALTER TABLE <table_name> |
ALTER TABLEemployee ADD(department_number VARCHAR(12)); |
ALTER TABLE <table_name> |
Creates a foreign key constraint for a column, mapping to a column on another table. Optional ON DELETE CASCADEmaintains referential integrity by deleting rows in table when row in foreign table is deleted. |
ALTER TABLE |
Relax constraint. |
ALTER_TABLE |
Delete constraint forever. |
Modifying and deleting rows |
|
INSERT INTO<table_name> |
INSERT INTO employee (employee_number, employee_name, salary) VALUES (‘7092’, ‘FORD’, 175,66); |
UPDATE<table_name> |
UPDATE wine_list SET note = ”Ideal as an aperitif’ WHERE wine_name = ‘Ch.Haut-Rian’; |
DELETE FROM<table_name> |
DELETE FROM members WHERE name LIKE ‘Sharon%’; |
Querying with Select. |
|
Project and Join. |
SELECTemp_table.emp_id, emp_table.dept_no, dept_table.description FROMemployee_db.emp_table, employee_db.dept_table WHEREemp_table.dept_no = dept_table.dept_no; |
Sorting. |
SELECT * FROM emp ORDER BY empid DESC; -or- SELECT empid, lastname FROM emp ORDER BY 2; |
Grouping. |
SELECT dept, AVG(salary) FROM emp GROUP BY dept HAVINGavg(salary)>80000 ORDER BY avg(salary)DESC; |
Column concatenation – formatting. |
SELECTfirstname||’,’||lastname full_name FROM team; |
On the fly calculations. |
SELECT 7 * 9 FROM DUAL; |
Column aliasing. |
SELECT name, NVL(spouse,’unmarried’) AS spouse FROMemp_db,emp_table; |
Subqueries. |
SELECT empid, dept, salary FROM emp WHERE dept = ( SELECT dept FROM emp WHERE empid = 78483); |
Group functions. |
|
avg() |
Average of all numbers in column. Standard deviation f all numbers in column. Variance of all numbers in column. |
sum(x) |
Sum total of all numbers in the column. Toal number of items in a culumn. |
max(x) |
Maximum value found in a column. Minimum value found in column. |
Leave a Reply