1. Tables

1.0: Table can have up to 254 columns

1.1: Data types:

1.1.0: char(n): n<= 255 bytes (memory occupation)

         I.e. char(40): fixed length (= 40 bytes) string

1.1.1: varchar2(n): n<=2000 bytes

        I.e. varchar2(80): variable length(<=80 bytes) string

1.1.2: number(o,d): o<=38, -84<=d<=127 (o: overall no of digits, d: no of digits to the right of decimal point)

        I.e. number(5,2) <= 999.99

1.1.3: date: default format –“DD-MMM-YY” i.e. “07-OCT-08”

1.1.4: long: char-data, length up to 2GB; only one long column allowed in one Table.

1.2 Queries

1.2.0: string operations:

* need to surround the string by apostrophe, e.g. where LOCATION = ‘DUBLIN’

** like/ not like operator – for pattern matching, % - wide card (sub string), _ position mark (one char).

I.e. where LOCATION not like ‘%U_B%’

Ø      Upper(): convert any letters in string to uppercase

Ø      Lower(): convert any letters in string to lowercase

Ø      Initcap():  convert init letter of each word in to uppercase

Ø      Length(): return the length of

Ø      Substring(, n [, m]): clips out m char piece of , start from n; if m not specified, the end of string assumed.

1.2.1: aggregate functions -- are statistic functions to compute a single value from a set of attribute value of a column.

Count: counting rows – return no of rows

             i.e. select count(*) from riskpref where lower(prefnom) like ‘eodcount’;

max: maximum value of a column

min: minimum value of a column

          i.e. select max(prefvaleur), min(prefvaleur) from riskpref;

sum: compute the sum of values (only applicable to data type number)

avg: compute the average value for a column (only applicable to data type number)

1.3 Data Definition in SQL

Example:

create table wen_project(

pno number(3) constraint pk_prj primary key,

pname varchar2(60) unique,

pmgr number(4) not null,

persons number(5),

buget number(8,2) not null,

pstart date default('01-JAN-05'),

pend date,

constraint not_same_date unique (pstart,pend) – no two projects have the same start, and end dates

)

1.4 Data Modification in SQL

1.4.0: insertions:

Ø      Insert  columns + values

i.e. insert into wen_project (pno, pname,pmgr,persons,buget,pstart)

      values (313,'DBS',1234,4,150000.42,'10-OCT-94');

Ø      Insert values

i.e. insert into wen_project

      values (313,'DBS',1234,4,150000.42,'10-OCT-94',null);

Ø      Insert columns + query

i.e. create table wen_oldproject(

      pno number(3) not null,

      pname varchar2(60) unique,

      pmgr number(4)

      );

      insert into wen_oldproject (pno,pname,pmgr)

      select pno,pname,pmgr from wen_project where pstart < '10-JAN-95';

1.4.1: updates