SQL Basics - 1

One Language that I have been using from my first project till this very day and still have no clue about is PL/SQL. So without wasting much time i would like to jump into the nitty gritties of core PL/SQL commands. I have Oracle 10g and DB2 EXPRESS C installed in my machine and using TOAD for oracle/DB2 for executing the commands. If a certain command is specific to Oracle or DB2 i will highlight the same.

Create Command :(DDL script)

create table ACCNTS (accnt_no number(16), accnt_name varchar2(100), balance number(10,2))

This will create an ACCNTS table with accnt_no, accnt_name, balance columns.

The equivalent command in DB2 would be

create table ACCNTS (accnt_no numeric(16), accnt_name varchar(100), balance numeric(10,2))

as number and varchar2 are not valid datatypes in db2

INSERT Command : (DML script)

insert into ACCNTS (accnt_no,accnt_name, balance) values (1, 'tatha', 10.00)

this command will insert the said values in the ACCNTS table. Its not necessary to give the column names as we are inserting all the values. If we choose not to insert all the values then we have to mention the column names and its not bound by any order.

It is also possible to insert data

SELECT Command : (DML script)

select * from ACCNTS or select accnt_no,accnt_name from ACCNTS.

This very obvious what this command will do.

WHERE Clause :

the where clause acts as a filter criteria

select * from ACCNTS where accnt_no > 10 or

select accnt_no from ACCNTS where accnt_name='tatha'

DELETE Command :

To delete all the rows of a table the command

delete from ACCNTS , for selective deletion the where clause can be used

UPDATE Command :

To update the values in a table the update command is used

update ACCNTS set accnt_name = 'Roy' where accnt_no=11

DISTINCT Clause :

distinct can be used when we require to eliminate duplicates. Suppose we have two records in the ACCNT table with name as tatha, and we want to view distinct names, the query is

select distinct accnt_name from ACCNTS

ORDER BY Clause :

To order the result we use the ORDER BY clause. The order can be both DESC and ASC. Suppose we want to order the balance in a descending order

select * from ACCNTS order by balance desc;

AS SELECT Clause

It is also possible to create a table from the data of another table, for example if we want to create a table ACCNT_DET which has accnt_no and accnt_name from ACCNTS

create table ACCNT_DET (accnt_no,accnt_name) as select accnt_no, accnt_name from ACCNTS

This command doesnt work in DB2 database, the equivalent command in DB2 database consists of two commands

Create table new_table like old_table;
Insert into new_table select * from old table

The second command can be used in Oracle to insert into a table values from another table like

Insert into ACCNT_DET select accnt_no_accnt_name from ACCNTS