SQL*PLUS cheat sheet - comments?
Date: 8 Oct 1993 00:31:36 -0700
Message-ID: <29350o$rm6_at_lindy.Stanford.EDU>
Hi, I put together this SQL*PLUS cheat sheet as
a project and also to learn at the same time.
Any comments or suggestions would be appreciated!
Thanks,
- Mark
SQL QUERY STATEMENTS - Retrieving Data -
Select:
-select * from emp; (retrieves all columns of data from the EMP table)
-select ename, job from emp; (retrieves only selected columns (ename, job))
-select ename "Employee", job from emp; (provides "alias" name for column name)
-select distinct job from emp; (retrieves only distinct values (no duplicates))
Where Clause - to narrow the rows returned to specific criteria: (used in SQL Select, Insert, Update, and Delete statements) (Compound Where Clauses are formed by using logical operators between Clauses)
-select deptno, dname, loc from dept where loc = 'Dallas';
Subqueries - allows a where clause to use a query result as criteria.
-select ename, job from emp where deptno = (select deptno
from emp where ename = 'Smith');
ANY or ALL operators - used when subqueries return more than one row.
- "{relational operator} ANY" returns any true result from the sub-query back to the main query.
- "{relational operator} ALL" returns results back to the main query only when all the sub-query results will cause the where clause to evaluate as true.
Join Query - allows columns to be joined together from two or more tables.
-select ename, job, loc from emp, dept where emp.deptno = dept.deptno;
Join Query using where clause - joins columns and selects rows by criteria.
- select ename, job, loc from dept, emp where dept.deptno = emp.deptno and dname = 'RESEARCH';
Result Ordering -
order by:
-select ename, job from emp order by sal; (sorts rows by sal (ascending order))
-select ename, job, deptno from emp order by deptno, sal desc; (ordering on
(more than one column in descending order)
group by: (columns are grouped into individual rows - by each group)
-select job, avg(sal) from emp group by job; (returns average salary per job)
Having clause - equivalent to a where clause for a 'group by' result.
- select job, avg(sal) from emp group by job having avg(sal) < 2000;
Storing/Updating Data -
Insert:
-insert into dept (deptno, dname, loc)
values (50, 'IS', 'San Francisco')
- enclose character strings or dates in single quotes *
or
-insert into dept values (50, 'IS', 'San Francisco') *need to account for
all fields/columns*
-insert into newdept [col1, col2] select col1, col2 from dept [where ...]
- columns and datatypes must match between the two tables *
Delete:
-delete from dept; (deletes all rows in dept table)
-delete from dept where deptno = 50; (deletes rows based on where clause)
- columns and datatypes must match between the two tables *
Delete:
Update: (updates column fields to new value(s))
-update dept set dname = 'Networking'; (changes dname field in all rows)
-update dept set dname = 'Networking'
where deptno = 50; (changes column fields that meet where clause criteria)
Create table:
-create table anytable ( col1 char(8) not null, col2 number(10), col3 date )
see explanations below: ^Datatype ^Constraints ^Length
-create table localemp as select * from emp where deptno = 50;
(results from the select clause are copied into the new table)
Datatypes:
Char (character values)
Varchar2 (same as char without padding out of defined column length w/blanks)
Number (numerical values)
Date (date values)
Constraints: "Not Null" after a column definition specifies
that the column must have a value for every row.
"Primary Key" one column per table that
must have a unique non-null value for that row.
Create view: (creates alternate view of selected data or stores a complex query, which can be queried with a select statement. When creating a view with an alias column name do not use double quotes)
-create view bigbucks as select ename employee, job, sal from emp
where sal >= 3000;
Rename: (object - table, view, synonym)
-rename table emp to employees;
Alter table add: (used to add new columns to a table)
-alter table dept add (numb_of_emp number(10));
Alter table modify: (change a column's defined characteristics)
-alter table dept modify (loc varchar2(32));
Drop (object) (deletes table or view)
-drop view bigbucks;
Operators:
relational operators (used within expressions)
- "=" equals
- "!=" not equal
- < or <= less than or less than or equal
- > or >= greater than or greater than equal
- in (matches rows that have at least one value of a defined set) select * from abc where a = ('a','b');
- null (matches rows that have a null value (i.e. no value) in the column
- not null (matches rows that do not have a null value in the column
- like: character pattern matching - "_" * must use "like" and pattern
"%" matching symbols together *
"_" (underscore) matches any single character per underscore.
"%" (percent sign) matches any number of characters at or after it's
position in the value.
- select ename, job from emp where job like 'ANALY__'; (varchar2)
- select ename, job from emp where job like 'ANALY____'; (char(9))
- select ename, job from emp where job like 'AN%';
logical operators (used within compound where clauses)
- and
- select ename, job from emp where job = 'CLERK' and hiredate > '03-DEC-81';
- or
- select ename, job from emp where job = 'CLERK' or job like '%MAN%';
- not (negates conditional expression, also used in single expressions)
- select ename, job from emp where sal >= 3000 and not job = 'PRESIDENT'
- select ename, job from emp where job = 'CLERK' or job like '%MAN%';
set operators
- union (merges two result sets into one combined set)
- intersect (selects just the common values of two result sets)
- minus (selects values in the first result set not present in the second)
expression operators
- arithmetic: + (plus), - (minus), * (multiply), / (divide)
- character: || (concatenate)
Functions:
(f=function, c=char values or column, n=numeric values)
single row character
- initcap - f(c): capitalizes the first letter of a word leaving rest lowercase
- upper - f(c): changes a string to all UPPERCASE letters
- lower - f(c): changes a string to all lowercase letters
- length - f(c): returns the character length of a string
- lpad - f(c1,n,c2): pads c1 with n characters to the left. c2=pad char.
- rpad - f(c1,n,c2): pads c2 with n characters to the right. c2=pad char.
- substr: f(c,n1,n2): extracts a sub-string starting at n1. n2=substring length
- instr: f(c1,c2,n1,n2): returns position of c2 in c1. n1=starting position in c1. n2=occurrence of c2
- decode: f(expr,c1,trans1,c2,trans2,...,default): evaluates expression and substitutes 'translation' if 'c' is present. 'default' returned if no match.
single row number
- round - round(n1,n2): rounds n1 to n2 precision of decimal places
- trunc - trunc(n1,n2): truncates n1 to n2 decimal places. default is 0.
- abs - abs(n): returns the absolute value of n
- nvl - nvl(expr1,expr2): if a value is null then expression2 is substituted for expr1. If not, expr1 is used. expr1 must = the datatype of expr2.
group functions: (provide group summary information)
- min(sal): returns minimim value - count(*): returns count of all rows
- max(sal): returns maximum value - count(sal): returns count of non-null rows
- avg(sal): returns average value - sum(sal): returns sum of rows note - avg, sum functions work with numeric values only. count, max, min work with numeric, character and date values.
conversion functions (convert datatypes from one type to another)
- to_char - to_char(expr, format mask): to_char(hiredate, 'MM/DD/YY') will
change standard DD-MON-YY output to "10/07/93" style.
- to_date - to_date(chardate, format mask): to_date('10/07/93','MM/DD/YY') will
convert to a date datatype that can be stored.
- to_number - f(c): converts a char numeric value to numeric.
date functions (to manipulate date values)
(general date arithmetic: use "+" or "-" with {n | date} in expr)
- add_months(date, n) - add_months(sysdate, +2): returns current month + 2
- last_day(date) - last_day('07-OCT-93'): returns last day in date's month.
- next_day(date, day) - next_day('07-OCT-93', 'Sat'): returns date of next day after date.
- months_between(date1, date2) - returns number of months between two dates.
SQL*PLUS COMMANDS - Working with SQL statements:
-start: starts (i.e. "runs") a SQL command file
-get: moves a SQL command file into the SQL buffer
-run or "/": runs the SQL commands currently in the buffer
-save: saves the commands currently in the buffer into a ".sql" command file.
-parameters: (using an ampersand followed by a number in a command file allows
parameter substitution when using "start" to run the script)
- rem Parameter example SQL command file saved as 'manparam.sql'
- select * from emp where mgr = &1
then: "start manparam 7698" SQL*PLUS substitutes the first parameter
(i.e. "7698") for the corresponding ampersand and runs the command.
Line Editing Commands:
(will work with SQL commands only. SQL*PLUS commands are not captured in buffer)
-list (l): list the current SQL commands in the buffer. The current line is
marked with an asterisk.
-change (c): c /old/new - changes "old" to "new" on current line.
c /typo/ deletes the string "typo" from current line.
-append (a): a from emp - appends to end of current line. Need two spaces
between command and text to equal one space on line.
-input (i): add more command lines after the current line. Hit return on a line
by itself to end.
-del: delete the current line from the buffer.
-n "text": replaces text on the command line "n".
-clear buffer (cl buff): clears contents of SQL command buffer.
Report/Formatting commands:
-describe (object): describes columns, datatypes in order defined.
-column: (formats column display with heading and format specification)
- column loc format a8 heading 'City'/ column sal format 9999 heading 'Bucks'
Then: a "select" naming a column defined with "column" will be displayed
in the alphanumeric or numeric format with the new heading.
-ttitle: (provides report title information at the top of each page)
- ttitle [left|center|right] 'text' skip [left|center|right] 'text'
(skip forces 'text' to skip to next line)
-btitle: (same syntax as ttitle - provides report title at bottom of page)
-break: (defines a control break where values in a column change from the
previous row - used by SQL*PLUS to perform some formatting actions Note: for output to be meaningful the column should be sorted.) syntax: break on {expr|col} [skip n| skip page] [duplicates|nodup] - break on job skip 2
- select job, ename from emp order by job; (will skip 2 lines between jobs and not display duplicates [default])
- ttitle [left|center|right] 'text' skip [left|center|right] 'text'
(skip forces 'text' to skip to next line)
-compute: (performs a calculation at a defined control break)
(standard group functions can be used for the calculation type)
- compute count of ename on job
- select job, ename from emp order by job; (performs the count calculation when a break is detected for the job column. The result is printed on a separate line before the break "skip".)
Setting Options:
set pause {on | off}:
set pagesize: (controls scrolling on screen) (i.e. 24)
define _EDITOR=<host_editor_name>
'login.sql' file: "saved" file with a collection of SQL*PLUS commands to
execute when entering SQL*PLUS.
Other Commands:
spool [filename] {on | off}: toggles capturing SQL*PLUS output to a file. "host <ls>" or "!<dir>": execute the host operating system command.
Help:
"help commands" or "help menu"
for an overview of SQL, SQL*PLUS and PL/SQL topics.
--
~ "If the human brain were simple enough
~ for us to understand then
Mark AuBuchon ~ we would be to simple to understand it"
email: mya_at_lindy.stanford.edu ~ - Prozac pharmacologist developer
Received on Fri Oct 08 1993 - 08:31:36 CET