SQL Basics- Part 2

TABLE:

Table is Collection of relative data. Columns represent fields for data and rows represent data.

In example given below: ID, NAME, ADDRESS represents columns which is used to store specific information for every record.

Table: EMP

ID	NAME	ADDRESS	COUNTRY	DOB
1	XX	Pune	IN	01.01.1960
2	YY	Helsinki	FI	31.12.2000

So while creating any table, we need to specify fields for that table and what type of value that field is going to store for records.

Record (row of data) is each individual entry that exists in that table. In above example there are 2 records.

·         In Oracle, there can be maximum 1000 columns per table. (750 for DB2)

·         (Refer http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_limits.htm for maximum limits for data types, table name, column name etc.)

How to Create Table

Consider above table EMP.

Columns in table are:

Column             Type                             SQL Data Type

ID                     Number                         BIGINT (INT range is 32767 so BIGINT preferred)

EMPNAME       String                           VARCHAR(50)

ADDRESS        String                           VARCHAR(300)

COUNTRY         String                           CHAR(2)

DOB                 Date                             DATE

We used EMPNAME instead of NAME because NAME is DB2 keyword.

Why name and address are VARCHAR?

EMPNAME, ADDRESS normally vary considerably in length, the choice of a varying-length data type seems appropriate. If you choose a data type of CHAR(50) for EMPNAME, for example, the result is a lot of wasted, unused space. 50 bytes space is always used irrespective of length of name if data type is CHAR.

Note: Using VARCHAR saves disk space, but it incurs a 2-byte overhead cost for each value. Using VARCHAR also requires additional processing for varying-length rows. Therefore, using CHAR is preferable to VARCHAR, unless the space that you save by using VARCHAR is significant. So do not use  VARCHAR(n) unless n is less than 18.

Syntax:

CREATE TABLE table-name

(

Columnname1   data-type ,

Columnname1   data-type

);

CREATE TABLE EMP

(

ID                     BIGINT,

EMPNAME       VARCHAR(50),

ADDRESS        VARCHAR(300),

COUNTRY         CHAR(2),

DOB                 DATE

);

How to Insert records in table

Syntax for Insert

INSERT INTO table-name (column-names) VALUES (row values separated by comma)

(column-names) is optional

Row values: As per column sequence in table or column names specified in (column-names)

1.     INSERT INTO EMP VALUES ( 1, 'XX', 'Pune', 'FI', '1960-06-01' );

2.     INSERT INTO EMP (ID,EMPNAME,ADDRESS,COUNTRY,DOB)

VALUES (2,'YY',           'Helsinki',          'FI','2000-12-31');

3.     INSERT INTO EMP (ID, EMPNAME) VALUES (3, ‘ZZ’);

4.     INSERT INTO EMP VALUES(4, null, null, null, null);

NOTE:

·         Null values same for “char varchar date int”

·         Null value in middle takes 1 byte (in case of char also 1 bytes) (For Oracle DB)

·         If ending columns have null then 0 bytes for that field (For Oracle DB)

QUERY:

Select * from emp;

·         * is meta character [All columns]

Relational Operator as precedence

1.     GREATER THAN >

2.     GREATER THAN EQUAL TO >=

3.     LESS THAN <

4.     LESS THAN EQUAL TO <=

5.     NOT EQUAL TO <> / ^= /! =

6.     EQUAL TO =

Other Predicates in where clause:

1.     BETWEEN A AND B : ONLY 2 VALUES mutually inclusive /faster than < = >

2.     NOT BETWEEN A AND B : ONLY 2 VALUES mutually exclusive

3.     IN (SET) : LIST OF VALUES discrete

4.     LIKE : A CHARACTER PATTERN

5.     IS NULL

LOGICAL CONDITION:

1.             AND

2.             OR

3.             NOT

PRECEDENCE OF OPERATORS:

1.     Arithmetic [BY PRECEDENCE ( ) ** * / + -]() ** /*+-   ** =  Exponentiation

2.     Concatenation

3.     Comparison Cond.

4.     Is [not] null, like, [not] in

5.     [Not] between

6.     NOT

7.     AND

8.     OR

Alias:

Select salary*12 from emp;     salary*12 is computed/virtual column

Select salary * 12 “ANNUAL” from emp;              ANNUAL is column alias

You can make alias case sensitive as in “Annual” or long character “Annual Salary”.

DISTINCT | UNIQUE:

Select DISTINCT empname from emp;

Select UNIQUE empname from emp;

Combination of columns can also be used for Distinct, unique.

Select DISTINCT empname, country from emp;

Sorting:

Order by

Select * from emp order by empname;

Select * from emp order by empname desc;

Wild Cards:

Wild cards are used for search. If you want to search name starting with A, name ending with A, name contains A, you can use wild card search option in where clause.

% Denotes 0 or many characters

_  Denotes 1 character (Underscore)

Select * from emp where empname like ‘A%’ (Select all employees with name starting with A)

Select * from emp where empname like ‘%A’ (Name ending with A)

Select * from emp where empname like ‘%A%’ (Name contains A)

Select * from EMP where empname like ‘t_’ (Name: ta, tb etc. t and any 1 character)

Delete

delete from emp;

ANSI: delete emp;

DDL

All DDL’s are auto commit by default.

CREATE, ALTER, DROP, TRUNCATE (added as SQL 2008 standards)

Transaction Control:

Savepoint:

It is used to save savepoint in long transactions.

Oracle Syntax:

Savepoint savepoint-name;

DB2 Syntax:

Savepoint savepoint-name on rollback retain cursors|locks;

On rollback retain cursors|locks: behaviour upon rollback to this savepoint.( cursors are unaffected by a rollback to savepoint operation/ Locks acquired since the savepoint are not tracked, and are not rolled back (released) upon rollback to the savepoint).

UPDATE new_emp set name = UPPER(name);

Savepoint s2 on rollback retain cursors;

update emp set name = lower(name) where employee_id =180;

Rollback to s2;

LOCKS:

1.     Locks are used for avoiding destructive intervention between concurrent transactions.

2.     Record locking is automatic.

3.     Lock is held for only user modified data.

4.     Default locking is row level locking (Cell level locking in Oracle 10G) unless

a.     Isolation level is uncommitted read/repeatable read

b.    The table locksize attribute is TABLE.

c.     Explicit table lock acquired.