SQL Guide - Tecnología y Desarrollo de Sistemas, Aplicaciones y Software

Guia de SQL

- Tecnología y Desarrollo de Sistemas, Aplicaciones y Software

Sql Query Language

Rapid SQL guide

PRIMARY KEYS : select DISTINCT ...
select ALL ... ( Opposite of ALL )
where field IN ( select_query)
where field IN ( const_list )
where field BETWEEN x AND y

Relational operators: = > < >= <= <> != /* != not standard */ LIKE

Boolean operators: AND OR NOT

Aggregate functions: COUNT SUM AVG MAX MIN
where NOT ((odate = 10/03/1990 AND snum > 1002) OR amt > 2000.00)
where (amt < 1000 OR NOT (odate = 10/02/1990 AND CNUM > 20003))
where city IN ('Barcelona','LONDON')
where comm IN (0.10,0.20)
where COMM BETWEEN 0.10 AND 0.20
where cname LIKE 'G%'
where city IS NULL

AGGREGATE FUNCTIONS : select sum(amt) from orders;
select avg(amt) from orders;
select COUNT(DISTINCT snum) from orders;
select count(*) from customers;
select count(all rating) from customers;
select (max(blnc + amt) FROM orders;

GROUP BY: select snum, MAX(amt) from orders group by snum;
select snum,odate,MAX(amt) from orders group by snum,odate;

HAVING: select snum, odate, MAX(amt) from orders group by snum,odate having MAX(amt) > 3000.00;
select snum,max(date) from orders group by snum having odate = 10/03/1988;
select snum, MAX(amt) from orders group by snum having snum in (1002,1007); Formatiing o/p
select snum, comm * 100 from sales;
select snum,'%',comm*100 from sales;

ORDERING: select * from orders order by cnum DESC;
select snum,odate,MAX(amt) from orders group by snum,odate order by snum
select sname,comm from sales order by 2 desc; /* will sort by comm descending */
select snum,COUNT(DISTINCT onum) FROM orders group by snum order by COUNT(DISTINCT onum) DESC;

MULTIPLE TABLES: table.fieldname

JOIN : select cust.cname,sales.sname,sales.city from sales,cust where sales.city = cust.city;

REFERENTIAL INTEGRITY : select cust.cname,sales.sname from cust,sales where sales.snum = cust.snum

EQUIJOINS : select sname,cname from sales,cust where sname < cname and rating < 200;

JOIN OF MORE THAN TWO TABLES : select onum,cname,orders.cnum,orders.snum from sales,cust,orders where cust.city <> sales.city and orders.cnum = cust.cnum and orders.snum = sales.snum;

ALIASES, JOIN A TABLE TO ITSELF : select first.cname,second.cname,first.rating from cust first,cust second where first.rating = second.rating;

SUBQUERIES : select * from orders where snum = ( select snum from sales where sname = 'Motika');
select onum,amt,odate from orders where snum in (select snum from orders where cnum = 2001)

CORRELATED SUBQUERY : select * fron customers outer where 10/03/1990 in (select odate from orders inner where outer.cnum = inner.cnum);

EXISTS : select cnum,cname,city from customers where EXISTS (select * from customers where city = 'San Jose');
select cnum,cname,city from customers where NOT EXISTS (select * from customers where city = 'San Jose');

Operators: ANY SOME ALL

UNION: select_query union select_query select_query union all select_query

INSERT

insert into TABLE /* Assume all fields */ values (data_values); /* Done */
insert into TABLE(field_list) /* rest of the fields are inserted as null */ values (data_list); /* Done */
insert into TABLE select_query
insert into cust (city,cname,cnum) values ('London','Hoff',2001);
/* Syntax for multiple records */ insert into cust (city,cname,cnum) values ('London','Hoff',2001), ('Pune', 'Flop', 2002) ; /* Done */
insert into TABLE from data_file; /* Done blindly */

DELETE

delete from TABLE
delete from TABLE where condition

UPDATE

update TABLES set field = value where condition
update sales set comm = comm - .01

CREATE TABLE

CREATE TABLE <table-name> (<column-name> <data-type> [(size)], . );
create table sales (snum integer NOT NULL UNIQUE REFERENCES sales (snum), sname char(10) PRIMARY KEY, city char(10) CHECK (city IN ('London', .)) DEFAULT = 'New York', odate char(10) NOT NULL CHECK (odate LIKE '__/__/____'); comm decimal CHECK (comm < 1), FOREIGN KEY (snum) REFERENCES sales (snum)), UNIQUE (cnum,snum));

UPDATE OF sales cascades DELETE OF sales restricted

CREATE INDEX

create [UNIQUE] index <index-name> ON <table-name> (<column-name> [,<column-name>].);

create index clientgroup on customers (snum);

DROP INDEX

drop index <index-name>;

ALTER TABLE: alter table <table-name> ADD <column-name <data-type> <size>;

DROP TABLE: drop table <table-name>;

FOREIGN KEY CONSTRAINT: foreign key <coulmn-list> REFERENCES <pktable> [<column-list>]

VIEW:
create view <view-name> <view-field-list> as select_query
create view LondonStaff AS select * from sales where city='London';
create view Ratingcoutn (rating,number) as select rating,count(*) from customers group by rating;
Views can be update by update command, but only fields specified in the view can be updated.

UNION and UNION ALL are not allowed, nor order by

DROP VIEW: drop view <view-name>

UPDATING VIEWS: Views may be readonly or updatable A view is updatable if It is based on one table Includes primary key Has no aggregate functions does not have DISTINCT does not have GROUP BY or HAVING does not have subqueries if it is defined on another view that view should be updatable Should not have constants (comm * 100 not allowed) all fields with NOT NULL should be included if INSERT is to be perforemrd

GRANTS: grants can be on
select insert update delete references (can define foreign keys)
GRANT select on cust to ADRIAN;
GRANT select, insert, update(city,comm) on cust to DIANE, ADRIAN;
GRANT ALL privileges on cust to stephen
GRANT select on cust to adrian with grant option; Adrain can give grant on select option to others

REVOKE: revoke insert on orders from adrian;

TYPICAL SYSTEM PRIVILEGES: RESOURCE right to create tables CONNECT right to log in and right to create views and synonyms DBA do anyhting you like SUPER DUPER USER
SYNONYM /* Non standard */
create synonym clients for diane.cust;
create public synonym cust for cust
drop synonym cust;

DBSPACE:
Provide details of how much database space will be used.
reate dbspace sample (pctindex 10; pctfree 25);
10% dbspace for index, and keep 25% free space for tables to expaned.

COMMIT

COMMIT WORK;

ROLLBACK WORK;

SET AUTOCOMIT ON; SET AUTOCOMIT OFF;

SYSTEM CATALOG
tables kept by system.
SYSTEMCATALOG Tables (base and views) tname, owner, numcolumns,type (Btree or Index),comment SYSTEMCOLUMNS Columns of tbales tname,cname,datatype,cnumber,tabowner,comment SYSTEMTABLES Catalog View of SYSTEMCATALOG SYSTEMINDEXES Indexes on tables iname,iowner,tname,cnumber,tabowner, numcolumns,cposition,isunique SYSTEMUSREAUTH Users of database username,password,resource,dba SYSTEMTABAUTH Object privliges of user username,grantor,tname,owner,selauth,insauth,delauth SYSTEMCOLAUTH Column privileges of users username,grantor,tname,cname,owner, updauth,refauth (refernece auth) SYSTEMSYNONS Synonyms for tables synonym,synowner,tname,tabowner

Comment on table Chris.ordrers is 'Current Customer orders'; Comment on column orders.onum is "Whew";

SQL ELEMENTS
<seperator> <comment> - <space> - <newline> <comment> -- <string> <newline> <space> ' ' <newline> '\n' <identifier> {Alpha}{AlphaDigit} only 18 characters <underscore> '_' <percent sign> '%' <delimiter> ,() < > .:=+*-/ <> >= <= or a <string> <string> ".*"
<query> SELECT statment <subquery> ( query ) <value expression> <primary> - <primary> <operator> <primary> - <primary> <operator> <value-expression> <operator> + - - - / - * <primary> <column-name> - <literal> - <aggergate function> - <built-in constant> - <nonstandard function> <literal> <string> - <mathematical expression> <built-in constant USER - <implementation-defined constant> <table name> <identifier> <column spec> [<table name> - <alias>]<column name> <grouping column> <column spec> - <integer> <ordering column> <column spec> - <integer> <colconstraint> NOT NULL - UNIQUE - CHECK (<predicate>) - PRIMARY KEY - REFERENCES <table name>[(column name)] <tabconstraint> UNIQUE(column list>) - CHECK (<predicate>) - PRIMARY KEY (<columnd-list>) - FOREIGN KEY (<column list>) REFERENCES <table name>[(<column-list>)] <defvalue> DEFAULT VALUE = <value expression> <data type> Legal data types are <size> <cursor name> <identifier> <index name> <identifier> <synonym> <identifier> (* nonstandard *) <owner> <Authorization ID> <column list> <column spec> .,.. <value list> <value expression> <table reference>i {<table name>[<alias>]) .,..
Predicates
<predicate> ::= [NOT] { <comparision predicate> - <in predicate> - <null predicate> - <between predicate> - <like predicate> - <quantifed predicate> - <exists predicate> } [AND - OR <predicate>]
<comparision predicate> ::= <value expression> <relational op> <value expression> <subquery>
<relational op> ::= = - < - > - >= - <= - <>
<between predicate> ::= <value expression> [NOT] BETWEEN <value expression> AND <value expression>
<in predicate> ::= <value expression> [NOT] IN <value list> - subquery
<like predicate> ::= <charvalue> [NOT] LIKe <pattern> [ESCAPE <escapechar>]
<null predicate> ::= <column spec> IS [NOT] NULL
<quantified predicate> ::= <value expression><relational op> <quantifier><subquery>
<quantifer> ::= ANY - ALL - SOME
<exists predicate> ::= EXISTS (<subquery>)
Syntax
SELECT * - {[DISTINCT - ALL] <value expression>.,..} FROM {<table name> [<alias>]}.,.. [ WHERE <preidcate> ] [ GROUP BY { <coulumn name> - <integer> }.,..] [ HAVING <predicate> ] [ ORDER BY { <column name> - <integer>} .,..]
[ { UNION [ALL]
SELECT .. }]

UPDATE <table name> SET { - }.,.. <column name> = <value expression> [ WHERE <predicate> ]

INSERT INTO <table name> [(<column name>.,..)] { VALUES (<value expression>.,..)} - <query>;

DELETE FROM <table name> [ WHERE <predicate>

CREATE TABLE <table name> ({<column name><data type>[<size>] [<colcnstrnt>.]}.,..); [<tabconstrnt>].,..);

SQL datatypes: CHAR (or CHARACTER) DEC (or DECIMAL) NUMERIC INT (or INTEGER) SMALLINT FLOAT REAL DOUBLE PRECISION

Keywords: ADD ALL ALTER AND ANY AS ASC AUDIT AUTHORIZATION AVG BEGIN BETWEEN BY CATALOG CHAR CHARACTER CHECK CLOSE COLUMN COMMENT* COMMIT CONNECT* CONTINUE COUNT CREATE CURRENT DATABASE DATE DBA DBSPACE DEC DECIMAL DECLARE DEFAULT DELETE DESC DISTINCT DOUBLE DROP END ESCAPE EXISTS FETCH FLOAT GROUP HAVING IN INDEX INSERT INT INTEGER INTO IS KEY LIKE LONG MAX MIN MODIFY NOT NULL NUMERIC OF ON OPEN OR ORDER PRECISION PRIMARY PRIVILEGES PUBLIC REAL REFERENCES RESOURCE REVOKE ROLLBACK SELECT SET SMALLINT SOME SQL SQLCODE SQLERROR SQLWARNING SUM SYNONYM TABLE TABLESPACE* TIME TIMESTAMP* TO TITLE UNION UNIQUE UPDATE USER VALUES VARCHAR VIEW WHENEVER WHERE WITH WORK