SQL Reference

sql reference
standards: sql92 and sql99, sql 2003

use single quotes for string literals.
(use '' for literal single quote)
double quotes in sql92 are used around identifiers
(identifiers examples are table and field names)
sql server uses [] around identifiers
boolean literals: TRUE, FALSE, NULL (no quotes)
date literals: `31-JAN-94' (oracle)
date literals: '2004-10-12' (mysql)
date literals: '10/12/2004' (mysql)

use -- for comments (sql 92)
/* */ (non-standard? works in t-sql)

not equal
<> - standard, oracle, sql server
!= - non-standard, oracle, sql server
^= - non-standard, oracle

equal
= (not == like in code)

data manipulation:

select, insert, update, delete
-to query recods
select (* or fields) from (table-name) [as alias]
[where (condition)]
[order by (columns) [ASC | DESC]]
[group by (columns)]

asc goes up. (the default)
1
2
3

desc goes down
3
2
1

-to add data to a table
insert into (table-name) values(value, value2,...)
insert into (table-name) (column1, column2, ...) values(value, value2,...)

- update a record
update (table-name) set (column-name)=(value) [where (condition)]

- to remove a record
delete from (table-name) [where (condition)]

data definition:

create, drop, alter
multi database ddl example generator:

- create a table
create table (table-name) ((column-name) data_type)

- To change the fields in a table.
alter table (table-name) [add drop modify] ((column-name) data_type);

- drop a table
drop table (table-name)

- To add an index (not a sql standard?)
create index (index_name) on (table_name) (columns);

****
Constraint clause
This clause is part of the create or alter table commands. The format is:
CONSTRAINT constraintname the constraint specifications

The following are some typical constraint specifications:

**oracle?
PRIMARY KEY--Attribute is unique, not null and indexed
UNIQUE--Attribute is unique and indexed but not the key
NOT NULL--Attrbute value cannot be left blank
REFERENCES--Foreign key constraint. The attribute must match a value of a specified attribute

**sql server?
(1) Primary Key,
(2) Foreign Key,
(3) Unique Constraint,
(4) Default Constraint and
(5) Check Constraint (sql server used to have a rule constraint)

1 PRIMARY KEY
2 UNIQUE
3 FOREIGN KEY
4 CHECK
5 NOT NULL


**********JOINS:
CROSS JOIN - never used.
INNER JOIN - exclusive
OUTER JOIN - inclusive
SELF JOIN - ?

3 types of outer joins: (mysql syntax)

LEFT OUTER JOIN (*=)
RIGHT OUTER JOIN (=*)
FULL OUTER JOIN

right outer join means column on right side of equals doesn't have to exist and the record will still show up.

LEFT OUTER JOIN rateplans ON rateplans.ID = transactions.RatePlan

***** UNIONS

*** data control language (DCL)
grant
revoke

********SQL************
(Informix)
- To see stuff about the table (doesn't work on sql server)
info status for (table_name)

- To unload a table
unload to "(path-name)" select ...

- To load a table
load from (file_name).unl insert into (table_name);

***************************
Database Compatiblity Issues

article on converting from mysql to oracle.

type mappings from sql server to oracle

different date formats
different blob formats
different reserve words and variable name lengths
different name spaces. (sql server server.db.owner.table)
(mysql server.db.table)
different syntax and support for transactions, stored procedures, triggers, etc.

different methods for automatically creating unique keys
mySql has AUTO_INCREMENT
access has auto number
sql server has Identity columns.
Oracle has sequences. (also has OID)

different methods for getting the automatically generated unique key
sql server(sql) - SCOPE_IDENTITY()
mysql/php - mysql_insert_id()

*programatically you can create a universal unique id.
this will allow you to merge databases. (like april)
is this functionality available in sql server or oracle(OID)?

*********** First 10 records

MySQL and PostgreSQL: select * from table limit 10
Microsoft SQL Server and Access: select top 10 * from table
Oracle 8i: select * from (select * from table) where rownum <= 10 oracle ?: SELECT * FROM Employees where rownum <= 10 DB2: select * from table fetch first 10 rows only Informix: select first 10 * from table ***** RANGES mysql: select * from table LIMIT 0 , 30 sql server: SELECT TOP 30 * FROM table (to get a range: in memory table w/ primary key) use key to get range insert into in memory table fed from select this gets rows 300-400 SELECT * FROM ( SELECT TOP 100 * FROM (SELECT TOP 400 * FROM MyTableSrt ORDER BY SortCol) a1 ORDER BY SortCol DESC ) a2 ORDER BY SortCol db2/400: (ibm ref)
SELECT * FROM SISSHLD250.CINMEX96 where NEAGT6LVL='" + TextBox2.Text + "' fetch first 10 rows only



*************************** subselect example:
INSERT INTO PTFCOMFD/CIUSRF SELECT 'THOMAAX', UFMODU, UFFUNC,
UFEFCN, UFEFYY, UFEFMM, UFEFDD, UFEXCN, UFEXYY, UFEXMM, UFEXDD FROM
ciusrf WHERE UFUSID='ATWOOTX' and UFMODU='IBS' and UFFUNC='CCP'


***************************
Types:
BLOB - Binary large object
CLOB - Character Large object


***************************Transactions:
ACID (atomicity, consistency, isolation, and durability).

atomic - all together
consistency - rules may be broken during transaction
(account may be negative, but in end it is consistent)
isolation - locks the data. solves concurrency issues.
durability - survives failures (creates transaction log)

nested transactions allows nested beds of atomic transactions.
example trip

**** mysql (?)
BEGIN WORK (or START TRANSACTION)
COMMIT
ROLLBACK

*** sql server
BEGIN TRAN [transaction name]
COMMIT TRAN
ROLLBACK TRAN
SAVE TRAN

********************** built-in libraries
getdate() - get current datetime (sql server)
now() - get current datetime (mysql)


******************** system tables:

SQL Server system tables (tables start with sys)
IBM system catalog
Oracle data dictionary