Inventory Database Examples

create table company
(COCODE                                   CHAR(2),
NAME                                      VARCHAR2(50),
 AC1                                      CHAR(2),
 AC2                                      CHAR(2),
 AC3                                      CHAR(2),
 ST15                                     CHAR(6),
 ST3                                      CHAR(6));


create table customer
(cocode                                   char(2),
cuscode                                   char(4),
cusname                                   varchar2(50),
addr                                      varchar2(150),
stregno                                   varchar2(15),
telephone                                 varchar2(15),
fax                                       varchar2(15),
email                                     varchar2(60));


create table machine
(cocode                                   char(2),
mcode                                     char(2),
name                                      varchar2(50),
dia                                       varchar2(10),
guage                                     varchar2(10),
capacity                                  number(8));


create table product
(cocode                                   char(2),
pcode                                     char(10),
name                                      varchar2(50),
count1                                    varchar2(10),
count2                                    varchar2(10),
count3                                    varchar2(10),
lycra                                     varchar2(10),
c1per                                     number(10,2),
c2per                                     number(10,2),
c3per                                     number(10,2),
lper                                      number(10,2),
c1bname                                   varchar2(10),
c2bname                                   varchar2(10),
c3bname                                   varchar2(10),
lbname                                    varchar2(10));


create table recyarn
(cocode                                   char(2),
docno                                     char(10),
cuscode          char(4),
PCODE                  CHAR(10),
recdate          date,
qty          number(10),
progref                                   varchar2(15),
dcno          char(10),
ref                                       varchar2(15),
FABRIC                                    VARCHAR2(30),
vehicalno                                 varchar2(20),
count1                                    varchar2(10),
count2                                    varchar2(10),
count3                                    varchar2(10),
lycra                                     varchar2(10),
c1bname                                   varchar2(10),
c2bname                      varchar2(10),
c3bname          varchar2(10),
lbname                  varchar2(10),
c1qty          number(10,2),
c2qty                  number(10,2),
c3qty                          number(10,2),
lqty                                      number(10,2),  
c1per                                     number(10,2),
c2per                                     number(10,2),
c3per                                     number(10,2),
lper                                      number(10,2),                  
transferfrom              char(15),
qtytrf              number(10,2),
qtybal            number(10,2),
qtyout                  number(10,2));


CREATE TABLE RETYARN
(DOCNO                                   CHAR(10),
RECDATE                                  DATE,
QTY                                      NUMBER(10),
PROGREF                                  VARCHAR2(15),
DCNO                                     CHAR(10),
vehicalno                                VARCHAR2(20),
CUSCODE                                  CHAR(4),
FABRIC                                   VARCHAR2(30),
PCODE                 CHAR(10),
REF                                      VARCHAR2(15),
count1                                   varchar2(10),
count2                                   varchar2(10),
count3                                   varchar2(10),
lycra                                    varchar2(10),
c1bname                                  varchar2(10),
c2bname                     varchar2(10),
c3bname         varchar2(10),
lbname                 varchar2(10),
c1qty         number(10,2),
c2qty                 number(10,2),
c3qty                         number(10,2),
lqty                 number(10,2),
c1per                                    number(10,2),
c2per                                    number(10,2),
c3per                                    number(10,2),
lper                                     number(10,2),
COCODE                                   CHAR(2),
TRANSFERFROM                             CHAR(15),
QTYTRF                                   NUMBER(10),
QTYBAL                                   NUMBER(10),
QTYOUT                                   NUMBER(10));


CREATE TABLE PRODINFHEAD
(DOCNO                                    CHAR(10),
DOCDATE                                   DATE,
WEIGHT                                    NUMBER(7,2),
MCODE                                     CHAR(2),
PROGREF                                   VARCHAR2(15),
CUSCODE                                   CHAR(4),
SHIFT                                     CHAR(1),
FABRIC                                    VARCHAR2(30),
WORKER                                    VARCHAR2(30),
GUAGE                                     VARCHAR2(10),
DIA                                       VARCHAR2(10),
WASTAGE                                   NUMBER(5,2),
GSM                                       VARCHAR2(10),
REMARKS                                   VARCHAR2(150),
TOTALWGHT                                 NUMBER(10,2),
COCODE                                    CHAR(2),
CHECKBOX                                  CHAR(1),
HOURS                                     VARCHAR2(10),
PCODE                                     VARCHAR2(10),
count1                                    varchar2(10),
count2                                    varchar2(10),
count3                                    varchar2(10),
lycra                                     varchar2(10),
c1bname                                   varchar2(10),
c2bname                                   varchar2(10),
c3bname                                   varchar2(10),
lbname                                    varchar2(10),
c1qty          number(10,2),
c2qty                  number(10,2),
c3qty                          number(10,2),
lqty                  number(10,2),
c1per                                     number(10,2),
c2per                                     number(10,2),
c3per                                     number(10,2),
lper                                      number(10,2),
SNO                                       NUMBER(2),
WGHT                                      NUMBER(10,2));


CREATE TABLE PRODINFDET
(DOCNO                                    CHAR(10),
DOCDATE                                   DATE,
WEIGHT                                    NUMBER(7,2),
MCODE                                     CHAR(2),
PROGREF                                   VARCHAR2(15),
CUSCODE                                   CHAR(4),
SHIFT                                     CHAR(1),
FABRIC                                    VARCHAR2(30),
WORKER                                    VARCHAR2(30),
GUAGE                                     VARCHAR2(10),
DIA                                       VARCHAR2(10),
WASTAGE                                   NUMBER(5,2),
GSM                                       VARCHAR2(10),
REMARKS                                   VARCHAR2(150),
TOTALWGHT                                 NUMBER(10,2),
COCODE                                    CHAR(2),
CHECKBOX                                  CHAR(1),
HOURS                                     VARCHAR2(10),
PCODE                                     VARCHAR2(10),
count1                                    varchar2(10),
count2                                    varchar2(10),
count3                                    varchar2(10),
lycra                                     varchar2(10),
c1bname                                   varchar2(10),
c2bname                                   varchar2(10),
c3bname                                   varchar2(10),
lbname                                    varchar2(10),
c1qty          number(10,2),
c2qty                  number(10,2),
c3qty                          number(10,2),
lqty                  number(10,2),
c1per                                     number(10,2),
c2per                                     number(10,2),
c3per                                     number(10,2),
lper                                      number(10,2),
SNO                                       NUMBER(2),
WGHT                                      NUMBER(10,2));


create table dchead
(DOCNO                                    CHAR(10),
DOCDATE                                   DATE,
WEIGHT                                    NUMBER(10,2),
MCODE                                     CHAR(2),
PROGREF                                   VARCHAR2(15),
CUSCODE                                   CHAR(4),
PCODE                                     CHAR(10),  
SHIFT                                     CHAR(1),
FABRIC                                    VARCHAR2(30),
WORKER                                    VARCHAR2(30),
GUAGE                                     VARCHAR2(10),
DIA                                       VARCHAR2(10),
WASTAGE                                   NUMBER(5,2),
GSM                                       VARCHAR2(10),
REMARKS                                   VARCHAR2(150),
REFNO                                     VARCHAR2(10),
TOTALWGHT                                 NUMBER(7,2),
GEN                                       CHAR(1),
INVNO                                     VARCHAR2(15),
INVDATE                                   DATE,
COCODE                                    CHAR(2),
count1                                    varchar2(10),
count2                                    varchar2(10),
count3                                    varchar2(10),
lycra                                     varchar2(10),
c1bname                                   varchar2(10),
c2bname                                   varchar2(10),
c3bname                                   varchar2(10),
lbname                                    varchar2(10),
c1qty          number(10,2),
c2qty                  number(10,2),
c3qty                          number(10,2),
lqty                  number(10,2),
c1per                                     number(10,2),
c2per                                     number(10,2),
c3per                                     number(10,2),
lper                                      number(10,2));


CREATE TABLE DCDET
(DOCNO                                    CHAR(10),
DOCDATE                                   DATE,
WEIGHT                                    NUMBER(10,2),
MCODE                                     CHAR(2),
PROGREF                                   VARCHAR2(15),
CUSCODE                                   CHAR(4),
PCODE                                     CHAR(10),  
SHIFT                                     CHAR(1),
FABRIC                                    VARCHAR2(30),
WORKER                                    VARCHAR2(30),
GUAGE                                     VARCHAR2(10),
DIA                                       VARCHAR2(10),
WASTAGE                                   NUMBER(5,2),
GSM                                       VARCHAR2(10),
REMARKS                                   VARCHAR2(150),
REFNO                                     VARCHAR2(10),
TOTALWGHT                                 NUMBER(7,2),
GEN                                       CHAR(1),
INVNO                                     VARCHAR2(15),
INVDATE                                   DATE,
COCODE                                    CHAR(2),
count1                                    varchar2(10),
count2                                    varchar2(10),
count3                                    varchar2(10),
lycra                                     varchar2(10),
c1bname                                   varchar2(10),
c2bname                                   varchar2(10),
c3bname                                   varchar2(10),
lbname                                    varchar2(10),
c1qty          number(10,2),
c2qty                  number(10,2),
c3qty                          number(10,2),
lqty                  number(10,2),
c1per                                     number(10,2),
c2per                                     number(10,2),
c3per                                     number(10,2),
lper                                      number(10,2),
SNO                                       NUMBER(2),
WGHT                                      NUMBER(10,2));






CREATE TABLE FABRETHEAD
(cocode                                   char(2),
docno                                     char(10),
cuscode          char(4),
PCODE                  CHAR(10),
recdate          date,
qty          number(10),
progref                                   varchar2(15),
dcno          char(10),
ref                                       varchar2(15),
FABRIC                                    VARCHAR2(30),
vehicalno                                 varchar2(20),
count1                                    varchar2(10),
count2                                    varchar2(10),
count3                                    varchar2(10),
lycra                                     varchar2(10),
c1bname                                   varchar2(10),
c2bname                      varchar2(10),
c3bname          varchar2(10),
lbname                  varchar2(10),
c1qty          number(10,2),
c2qty                  number(10,2),
c3qty                          number(10,2),
lqty                                      number(10,2),  
c1per                                     number(10,2),
c2per                                     number(10,2),
c3per                                     number(10,2),
lper                                      number(10,2),                  
transferfrom              char(15),
qtytrf              number(10,2),
qtybal            number(10,2),
qtyout                  number(10,2));

Useful Triggers for Oracle Developers

set_window_property(forms_mdi_window,window_state,maximize);
set_window_property('WINDOW1',window_state,maximize);
-------------------------------------
DECLARE
A TIMER;
BEGIN
A:=CREATE_TIMER('ABC',200,REPEAT);
END;
-----------------------------------------------
:DUMMY.A:= TO_CHAR(SYSDATE,'DAY   DD-MON-YYYY   HH:MI:SS AM');
-----------------------------------------

DECLARE
A NUMBER;
BEGIN
SELECT MAX(TO_NUMBER(INVNO)) INTO A FROM SHEAD
WHERE SHEAD.CCODE=:SHEAD.CCODE;
IF A IS NULL THEN
A:=1;
ELSIF A IS NOT NULL THEN
A:=A+1;
END IF;
:SHEAD.INVNO:=A;
END;
-------------------------------
key-next-item
DECLARE
a_value_chosen BOOLEAN;
BEGIN
a_value_chosen := Show_Lov('customer');
IF NOT a_value_chosen THEN
Message('You have not selected a value.');
Bell;
RAISE Form_Trigger_Failure;
END IF;
end;
----------------------------------------
when-button-pressed(print)
Declare
     pl_id ParamList;
BEGIN

   Pl_Id := Get_Parameter_List('tmpdata');
   IF NOT Id_Null(Pl_Id) THEN
     Destroy_Parameter_List( pl_id );
   END IF;
    Pl_Id := Create_Parameter_List('tmpdata');
    Add_Parameter(pl_id,'P_ccode',TEXT_PARAMETER,:GLOBAL.CCODE);
    Add_Parameter(pl_id,'P_INVNO',TEXT_PARAMETER,:SHEAD.INVNO);
    Add_Parameter(pl_id,'destype',TEXT_PARAMETER,'screen');
    Run_Product(REPORTS, 'D:\AC\sale_invoice.rdf', SYNCHRONOUS, RUNTIME,FILESYSTEM,Pl_Id, NULL);
End;
--------------------------------------

:amount:=round(nvl(:qty,0),2)*round(nvl(:price,0),2);
IF :AMOUNT >= 50000 THEN
:DISCAUNT:=5;
:TAMOUNT:=NVL(:AMOUNT,0)*5/100;
:STAX:=NVL(:AMOUNT,0)-NVL(:TAMOUNT,0);
ELSIF :AMOUNT >= 30000 THEN
:DISCAUNT:=3;
:TAMOUNT:=NVL(:AMOUNT,0)*3/100;
:STAX:=NVL(:AMOUNT,0)-NVL(:TAMOUNT,0);
ELSIF :AMOUNT >= 10000 THEN
:DISCAUNT:=1;
:TAMOUNT:=NVL(:AMOUNT,0)*1/100;
:STAX:=NVL(:AMOUNT,0)-NVL(:TAMOUNT,0);
ELSE
:DISCAUNT:=0;
:TAMOUNT:=0;
:STAX:=NVL(:AMOUNT,0)-NVL(:TAMOUNT,0);
END IF;
-----------------------------------------

SQL Commands

Create Command:
------------------------
Create command is used to create a table.

Syntax:
---------------
Create table
< table_name> ( col_name1 datatype(size),
col_name2 datatype(size), ,, ,, ,
col_namen datatype(size) );

ex:
------

create table student ( sno number(3), sname varchar2(20), marks number(3));
Output:
---------
Table Created.

Insert command: 
--------------------
Insert command is used to insert the rows into the table.

Syntax:
----------
Insert into < table_name> values ( val1, val2, ....., valn );

Ex:
-----
insert into student values (101, 'ali', 80);
insert into student values (102, 'kiran', 85);
insert into student values (103, 'asif', 66);

For every insert command , the response we get is " 1 row created. "

Using null keyword: 
--------------------------
Null keyword is used , if we do not have value for a column.

Ex:
---
insert into student values (104,'ali', null);
insert into student values (105,null, null);

2nd syntax of insert command: 
----------------------------------------
insert into student( col1, col2, ..coln ) values ( val1, val2, ..., valn );

ex:
-----
insert into student ( sno, sname ) values (106,'haider');

The leftover column will have null value.

Selecting the rows from the table: 
--------------------------------------------

To retrieve the rows from the table select command is used.

Basic Syntax:
----------------
select * from < table_name>;

Ex:
-----

select * from student;

In the above command, * is a special character which is used to display all the information from the table.

To select specific columns: 
-----------------------------------
select col1, col2... , coln from the <table_name>;

ex:
-----
select sno, sname from student;
select sname from student;

Selecting specific rows:
-------------------------------

Where clause is used to filter the rows from the table.

Syntax:
---------

select * from < table_name> where < condition >;

ex:
------

select * from student where marks > 70;

Combination of selecting specific row and selecting specific columns:
------------------------------------------------------------------------------------------

select sno from student where marks > 70;

Using Arithmetic operations with select command: 
-----------------------------------------------------------------

select empno, ename , sal, sal* 12 , deptno from emp;

Using column Alias: 
-------------------------

Column alias is process of providing user definied column heading. select empno, ename , sal, sal* 12 annual_sal , deptno from emp; In the above query annual_sal is the column alias.

Using Distinct keyword: 
-------------------------------

Distinct keyword is used to get the distinct ( unique ) values. Duplicates will be supressed.

ex:
------

select distinct deptno from emp;

Update command: 
-----------------------

This command is used to change the data present in the table.

Syntax:
---------

Update < table_name> set <col_name> = < value> where < condition> ;

ex:
----

update student set marks =95 where sno =101;

Output:
---------

1 row updated.

Updating multiple columns:
-----------------------------------

update student set sname='nabil' , marks =48 where sno =102;

Note:
------

Update command without where clause , will update all the rows.

ex:
---

update student set marks=80;

Using Delete command: 
------------------------------

Delete command is used to delete the rows from the table.

Syntax:
---------

delete from < table_name> where < condition >;

ex:
-----

delete from student where sno=103; Output: 1 row deleted.

Note:
------

Delete command without where clause will delete all the rows.

ex:
----

delete from student;

( All the rows are deleted )

++++++++

Alter command: 
--------------------

Alter command is used to change the structure of the table.
We can perform following activities using Alter command

1) Adding a new column
2) Droping an existing column
3) Modifying a column
4) Renaming a column

Adding a new column:
----------------------------

Alter table < table_name> add ( col_name1 datatype(size), col_name2 datatype(size), ,, ,, ,, col_namen datatype(size) );

ex:
-----

Alter table student add ( city varchar2(10), state varchar2(10)); Table altered.

Note:
The new columns will have null values.

Droping a column:
-----------------------

Alter table < table_name > drop ( col_name1, col_name2,..., col_namen);

Ex:
----

Alter table student drop ( city, state);

Table Altered.

Modifying a column:
-------------------------

By using modifying we can increase and decrease the size of the column.

ex:
---

Alter table student modify ( sname varchar(20)); Table Altered.

Decreasing the size : 
--------------------------

Alter table student modify ( sname varchar(15)); Table altered.

Note:
-------

To decrease the size , the existing table data should fit into new size. By using Modify keyword , we can also change the datatype of the column.

Ex:
-----

Alter table student modify ( sname number(15));

Note:
-------

To change the datatype, column should be empty.

Renaming a column:
--------------------------
Syntax:
-----------

Alter table <table_name> rename column <old_name> to <new_name>;

Ex:
----

Alter table student rename column sno to roll_no; Table Altered.

Truncate command: 
-------------------------

This command is used to remove all the rows from the table.
Truncate table < table_name>;
Truncate table student;

Rename command:
------------------------

This command is used to change the table name.

Syntax:
--------

Rename <old_table_name > to < new_table_name>;

Syntax:
--------

Rename student to stu1; Table renamed. From now, we need to access the table by using the new name.

Drop command: 
--------------------

This command is used to remove the table from the database.

Syntax:
----------------

Drop table < table_name >;

ex:
----

Drop table stu1; Table dropped.

Some Constraints Example

CONSTRAINTS

PRIMARY CONSTRAINTS        : PRIMARY, UNIQUE, CHECK, REFERENCES)
SECONDARY CONSTRAINTS      : NOT NULL, DEFAULT)

CONSTRAINTS BASED ON 2 LEVELS COLUMN LEVEL and TABLE LEVEL
Except Not null all others can be defined as both table and column level.
But not null as only column level.

Eg. For SECONDARY CONSTRAINTS
~~~~~~~~~~~~~~~~~~~~~~~

1. NOT NULL CONSTRAINT

  CREATE TABLE EMP9( ENO NUMBER(3)  NOT NULL,
           ENAME VARCHAR2(10));


   2. DEFAULT CONSTRAINT

CREATE TABLE EMP9(ENO NUMBER(3)  NOT NULL,
        ENAME VARCHAR2(10),DOJ DATE DEFAULT SYSDATE);

   3. PRIMARY CONSTRAINT(COLUMN LEVEL)

UNIQUE

CREATE TABLE EMP9(
ENO NUMBER(3)  NOT NULL CONSTRAINT UNIEMP UNIQUE,
ENAME VARCHAR2(10));

PRIMARY KEY

CREATE TABLE EMP9(
ENO NUMBER(3) CONSTRAINT PKEMP9 PRIMARY KEY,
ENAME VARCHAR2(10));

   4. CHECK CONSTRAINT

CREATE TABLE BANK(
ACNO NUMBER(2) CONSTRAINT PKBANK PRIMARY KEY,
ACTYPE VARCHAR2(2) CONSTRAINT CKBANK CHECK ( ACTYPE IN
        ('SB','CA','RD')),
ACNAME VARCHAR2(10),
AMOUNT NUMBER(4));


   5. REFERENCES

CREATE TABLE EMP9(
ENO NUMBER(3) CONSTRAINT PKE9 PRIMARY       KEY,
JOB VARCHAR2(10),
ENAME VARCHAR2(10),
MGR NUMBER(4) REFERENCES EMP9(ENO));

   6. REFERENCES(REFERING TO DIFFERENT TABLE)

CREATE TABLE DEPT9(DEPTNO NUMBER(2) CONSTRAINT PKDNO PRIMARY  KEY, DNAME VARCHAR2(10), LOC VARCHAR2(10));


CREATE TABLE EMP9( EMPNO NUMBER(4),ENAME VARCHAR2(10),
       SAL NUMBER(7,2),DEPTNO NUMBER(2) CONSTRAINT FKDNO REFERENCES DEPT9(DEPTNO));




7. TABLE LEVEL CONSTRAINTS


     UNIQUE  TABLE LEVEL

CREATE TABLE BANK( ACNO NUMBER(3),ACTYPE VARCHAR2(10),
        BAL NUMBER(7,2),PLACE VARCHAR2(10),CONSTRAINT UNIBANK          UNIQUE(ACNO,ACTYPE));

     PRIMARY KEY(TABLE LEVEL)

CREATE TABLE BANK( ACNO NUMBER(2), ACTYPE VARCHAR2(2) CONSTRAINT   CKBANK CHECK (ACTYPE IN ('SB','CA','RD')),AMOUNT NUMBER)

Computerized Weaving Software in ORACLE

All enterprise strives for optimized results and performance. With computerized system, enterprise can achieve their desired goals.
 Can make accurate intime decisions leading to maximum benefits. Our weaving management software allows to save record at each step
of weaving. You can manage the quality production contracts, yarn records for warp & weft, bags required, conversion rates etc.. Contract
 based yarn buying, local storage and issuance to sizing and beem receiving, article assignment to Looms & Workers. Daily Production,
 Performance & Stop-age records with reasons. Calculate workers efficiency and salary by production. Article delivery and inward / outward
 gate passes.

Always get the current status of all contracts, yarn requirements, Sizing status, Loom Productions, Fabric Stock delivered and returned all
 in one software. Not only this, integrated accounts management system adds recovery options, payments made, expenses done, cash & bank
 statements parties ledgers and more. Software has also built in users management system and is multi terminal supported.

Key Features:

• Contract based orders management.
• Current status of all contracts.
• Warp / Weft Yarn bags report.
• Production Reports.
• Fabric delivery & return records.
• Sizing Reports & and beem status.
• Worker Production and Salary Calculation.
• Integrated Accounting System.
• Contract based Invoice Recoveries.
• Expense records, Party Ledgers, Payments Records.
• Standardized Accounts Reports.
• Current Stock Reports.
• One click Backup option.
• A complete Business Manager.
• Users Management Included.
• Software extendable to multi-terminals.
• Software Applicable in…
• Weaving Units.
• Looms Management.
• Production Management & Performance Tracking.
• Related Businesses.

Some Useful Queries for Beginners

SELECT * FROM EMP;
SELECT EMPNO, ENAME, SAL FROM EMP;
SELECT * FROM EMP WHERE DEPTNO=20;
SELECT * FROM EMP WHERE HIREDATE = ’02-APR-81’;
SELECT * FROM EMP WHERE SAL = 5000;
SELECT * FROM EMP WHERE HIREDATE <= ’23-MAY-86’;
SELECT * FROM EMP WHERE SAL >= 1500;
SELECT * FROM EMP WHERE SAL ! = 3000;
SELECT * FROM EMP WHERE DEPTNO <> 20;
SELECT * FROM EMP WHERE HIREDATE BETWEEN ’01-JAN-81’ AND ’31-
DEC-81’;
SELECT * FROM EMP WHERE DEPTNO IN (20, 30);
SELECT * FROM EMP WHERE JOB IN (‘ANALYST’,’SALESMAN’);
SELECT * FROM EMP WHERE HIREDATE IN (’20-FEB-81’,’03-DEC-81’);
SELECT * FROM EMP WHERE ENAME LIKE ‘%M%’;
SELECT * FROM EMP WHERE JOB LIKE ‘M%ER’;
SELECT * FROM EMP WHERE ENAME LIKE ‘__R%’;
SELECT * FROM EMP WHERE ENAME LIKE ‘%\_%’ ESCAPE ‘\’;
SELECT * FROM EMP WHERE COMM IS NULL;
SELECT * FROM EMP WHERE JOB = ‘MANAGER’ AND HIREDATE < ’10-JUN-
81’;
SELECT * FROM EMP WHERE SAL < 5000 AND COMM IS NULL;
SELECT * FROM EMP WHERE DEPTNO = 30 OR JOB = ‘PRESIDENT’;
SELECT * FROM EMP WHERE COMM IS NULL OR ENAME LIKE ‘A%’;
SELECT * FROM EMP WHERE JOB NOT LIKE ‘M%’;
SELECT * FROM EMP WHERE SAL NOT BETWEEN 2000 AND 3000;
SELECT * FROM EMP ORDER BY ENAME;
SELECT * FROM EMP ORDER BY JOB, ENAME;
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP ORDER BY 3;
SELECT ENAME EMPLOYEE FROM EMP ORDER BY EMPLOYEE;
---------------------------------------------------------------------------------------------------
SELECT UPPER (‘asif khan’), LOWER (‘SQL PLUS’), INITCAP (‘ORACLE’) FROM 
DUAL;
SELECT EMPNO, RPAD (ENAME, 20,’.’), LPAD (SAL, 6,’*’) FROM EMP;
SELECT INSTR (‘PAKISTAN’,’A’, 1) FROM DUAL;
SELECT INSTR (‘PAKISTAN’,’A’, 3) FROM DUAL;
SELECT INSTR (‘PAKISTAN’,’A’, -1) FROM DUAL;
SELECT INSTR (‘PAKISTAN’,’A’, -3) FROM DUAL;
SELECT ROUND (125.978, 1) FROM DUAL;
SELECT ROUND (123.978,-1) FROM DUAL;
SELECT ROUND (123.978, 0) FROM DUAL;
SELECT ROUND (123.978) FROM DUAL;
SELECT ROUND (126.978,-2) FROM DUAL;
SELECT ROUND (156.978,-2) FROM DUAL;
SELECT ROUND (149.978,-2) FROM DUAL;
SELECT TO_CHAR (SYSDATE,’DD-MON-YYYY’) FROM DUAL;
SELECT TO_CHAR (TO_DATE (’27-OCT-17’, ’DD-MON-RR’),’YYYY’) FROM DUAL;
SELECT TO_CHAR (TO_DATE (’27-OCT-17’, ’DD-MON-YY’),’YYYY’) FROM DUAL;
SELECT TO_CHAR (15000, ‘$99,999.9’) FROM DUAL;
SELECT TO_CHAR (50, ‘$99, 9999.9’) FROM DUAL; 
SELECT 3000 + TO_NUMBER (‘5000’) FROM DUAL;
SELECT TO_CHAR (SYSDATE, ‘YYYY YEAR’) FROM DUAL;
SELECT TO_CHAR (SYSDATE, ‘DY DAY’) FROM DUAL;
SELECT EMPNO, DEPTNO, DNAME FROM EMP, DEPT 
WHERE EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO, DEPT.DEPTNO, JOB, DNAME FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB = ‘MANAGER’;
SELECT E.EMPNO, E.ENAME, S.GRADE, D.DNAME FROM EMP E, DEPT D, 
SALGRADE S WHERE E.DEPTNO=D.DEPTNO AND E.SAL BETWEEN S.LOSAL 
AND S.HISAL;
SELECT E.EMPNO, E.ENAME, D.DNAME FROM EMP E, DEPT D 
WHERE E.DEPTNO (+) =D.DEPTNO;
SELECT WORKER.ENAME||’WORK FOR’||MANAGER.ENAME FROM EMP 
WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO;
SELECT WORKER.ENAME||’WORK FOR’||MANAGER.ENAME FROM EMP 
WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO (+);
SELECT COUNT (COMM) FROM EMP;
SELECT JOB, MAX (SAL) FROM EMP GROUP BY JOB;
Q.1. Find the All Employees Who Are Earning More than Mr. JONES
SELECT * FROM EMP 
WHERE SAL> (SELECT SAL FROM EMP
WHERE ENAME = ‘JONES’);
Q2. Display All Employees Which Are Working In a Same Department Of Mr. 
SMITH
SELECT * FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP
WHERE ENAME = ‘SMITH’);
Q3. DISPLAY ALL EMPLOYEES WHO WERE HIRED AFTER MR.FORD WAS 
HIRED.
SELECT * FROM EMP
WHERE HIREDATE > (SELECT HIREDATE FROM EMP
WHERE ENAME = ‘FORD’)
Q4. DISPLAY ALL EMPLOYEES WHO ARE EARNING MORE THAN THE SALARY 
OF THAT EMPLOYEE WHICH HAS HIGHEST SALARY IN DEPARTMENT 30.
SELECT * FROM EMP
WHERE SAL > (SELECT MAX (SAL) FROM EMP
WHERE DEPTNO = 30);
=====================================================================
Questions
1. Which is the subset of SQL commands used to manipulate Oracle Database 
structures, including tables?
2. What operator performs pattern matching?
3. What operator tests column for the absence of data?
4. Which command executes the contents of a specified file?
5. What is the parameter substitution symbol used with INSERT INTO 
command?
6. Which command displays the SQL command in the SQL buffer, and then 
executes it?
7. What are the wildcards used for pattern matching?
8. State true or false. EXISTS, SOME, ANY are operators in SQL.
True
9. State true or false. !=, <>, ^= all denote the same operation.
10. What are the privileges that can be granted on a table by a user to others?
11. What command is used to get back the privileges offered by the GRANT 
command?
12. Which system tables contain information on privileges granted and privileges 
obtained?
13. Which system table contains information on constraints on all the tables 
created?
15. What is the difference between TRUNCATE and DELETE commands?
16. What command is used to create a table by copying the structure of another 
table?
17. What will be the output of the following query?
SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', 
'**'),'*','TROUBLE') FROM DUAL;
18. What will be the output of the following query?
SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );
19. What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;
20. Which date function is used to find the difference between two dates?

Procedure and Function Examples

Procedures:-
A Procedure is a named PL/SQL block which is compiled and stored in
the database for repeated execution.

Basic Syntax :
------------

Create or replace procedure <procedure_name>
is
begin
..............
..............
.............
end;
/
Ex 1:
-----------
Create or replace procedure p1
is
begin
dbms_output.put_line('Hello World');
end;
/

Procedure created.

To execute the procedure:
----------------------------
Exec command is used to execute the procedure.

SQL> Exec p1
Hello World

A procedure can have three types of parameters.
1) IN Parameter
2) OUT Parameter
3) IN OUT Parameter
In Parameters are used to accept values from the user.
Ex 2:
---------
Create a procedure which accepts two numbers and display its sum.

create or replace procedure add_num ( a IN number,
b IN number)
is
c number(3);
begin
c := a+b;
dbms_output.put_line(' The sum is '||c);
end;
/

Procedure created.

To execute the procedure:
--------------------------
SQL> exec add_num (10,20)


Ex 3:
--------

Create a Procedure which accepts an empno and increments his salary by 1000.
create or replace procedure inc_sal ( a in number)
is
begin
update emp set sal = sal+1000
where empno = a;
end;
/

Procedure created.

TO execute the procedure:
---------------------------

SQL> exec inc_sal(7900)

We can improve the above procedure code by using %type attribute in
 procedure parameters.

The above procedure can be re-written as below :

create or replace procedure inc_sal ( a in emp.empno%type)
is
begin
update emp set sal = sal+1000
where empno = a;
end;
/


Create a procedure which accepts deptno and display ename and salary
of employees working in that department.
create or replace procedure display_emp1 (l_deptno emp.deptno%type)
is
cursor c1
is select ename,sal from emp
where deptno = l_deptno;

begin

for emp_rec in c1 loop
dbms_output.put_line(emp_rec.ename||'...'||emp_rec.sal);
end loop;

end;
=====================================================================

Functions:-
Function is a PL/SQL block which must and should return single value.
Syntax:
-----------

Create or replace function <Function_name>
( <Par_name> <mode> <datatype>,
,, ,, ,, )
return datatype
is
Begin
..........
.........
end;
/

ex1:
-----

Create a function which accepts two numbers and display its sum.

create or replace function add_num_f1 ( a number, b number)
return number
is
c number(5);
begin

c :=a+b;
return c;
end;
/
To invoke a function from a pl/Sql block:
---------------------------------------------

declare
n number(5);
begin

n := add_num_f1(20,40);

dbms_output.put_line('The sum is '||n);
end;
/
We can invoke functions from select stmt:
----------------------------------------------
select add_num_f1(30,50) from dual;
Functions can be invoked as part of an expression:
----------------------------------------------------

select 100 + add_num_f1(50,10) from dual;
Ex2:
------

create a function which accepts sal and returns tax value ( 10% of sal is tax).

create or replace function cal_tax ( a number)
is
begin

return a*10/100;
end;
/

Note: A function can return a value using return statement.
Ex 3:
----------

Have a look at the following function:



create or replace function add_num_f2 ( a number, b number)
return number
is
c number(5);
begin

insert into dept values (50,'HR','FAISALABAD')

c :=a+b;
return c;
end;
/
The above function gets created.

The above function can be invoked from the pl/SQL block

declare
n number(5);
begin

n := add_num_f2(20,40);

dbms_output.put_line('The sum is '||n);
end;
/


But, we cannot invoke the above function using select stmt.

ex:

select add_num_f2(30,50) from dual; -- will give us error.

Note: So, functions with dml commands cannot be invoked from select stmt.

------------------------

TO see the list of all the functions
select object_name from user_objects
where object_type = 'FUNCTION';
----------------------

To drop a function

drop function <function_name>;

ex:

drop function add_num_f2;

-----------------------

Functions are mainly used for calculation purposes.
Rest of the activities, prefer procedures.Pro

Oracle DBA Salary Position

Oracle DBA salary and job duties
Back in 1981, the average DBA salary was about age+10, and a DBA right out of college could expect to earn $32k/year, a nice salary back when gasoline was 80 cents a gallon.  Today, inflation and increased demand have increased the DBA pay scale:
Low Tier DBA -
An Oracle DBA without college and less than 10 years experience earns about age*2, a 25 year-old earning about $50k/year.
Middle Tier DBA
A DBA with a Bachelors degree and ten years full-time work experience can earn up to age*3, with a 35 year-old DBA earning about $105/year.
Senior DBA
A senior DBA with a Masters degree, 20 years experience managing mission critical database can earn up to age*4, with a 45 year-old DBA manager earning up to $180k/year.
Superstar DBA
For those DBA Superstars with advanced degrees and specialized skills (RAC, Oracle Apps), the pay is often as high as age*6 and a 35 year-old superstar can earn up to $210k per year.

Some Useful Forms Builder Questions

      How to execute the user defined triggers in forms?


Explain What is a predefined exception available in forms?


Explain What is a Check Box?


Can we change the default value of the radio button group at run time?


Explain What is the difference between NAME_IN and COPY?


Explain What is a Layout Editor?


Explain EXIT_FORM is a restricted package procedure?


Explain some built-in routines used to manipulate images in image_item?


Explain What are the types of TRIGGERS?


How to Give built-in routine related to a record groups?


Explain What is a Navigable item?


Can we change the default value of the radio button group at run time?


Explain What is a Navigable item?


Can we change the color of the push button in design time?


Explain When does an on-lock trigger fire?


Explain What does an on-clear-block Trigger fire?


Explain How do you pass the parameters from one form to another form?


Explain What is the difference between a POST-FIELD trigger and a POST-CHANGE trigger?


Explain What is an User Exits?


Explain What package procedure is used for calling another form?


Explain Committed block sometimes refer to a BASE TABLE?


Explain the built-in routine used to count the no of rows in a group?


Explain What is the built-in function used for finding the alert?


Explain What are the types of TRIGGERS?


Explain the Abbreviation for the following File Extension: 1. FMB 2. MMB 3. PLL


Explain what is a display item?


Explain What are the menu items that oracle forms 4.0 supports?


Explain System.effective_date system variable is read only True/False?


Explain What are the types of Pop-up window?


Explain What are the triggers associated with a check box?


Explain What is the content view and stacked view?


Explain What Enter package procedure does?


Explain What package procedure used for invoke sql *plus from sql *forms?


Explain What are the main types of TRIGGERS?


Explain What is the usage of an ON-INSERT,ON-DELETE and ON-UPDATE TRIGGERS?


Explain What package procedure used for invoke sql *plus from sql *forms?


Explain What is an SQL *FORMS?


Explain What are the types of canvas-views?


Explain some built-in routines used to manipulate images in image_item?


Explain What SYNCHRONIZE procedure does?


Explain What is the usage of an ON-INSERT,ON-DELETE and ON-UPDATE TRIGGERS?


Explain When is PRE-QUERY trigger executed?


Explain What are the menu items that oracle forms 4.0 supports?


Explain What is a predefined exception available in forms 4.0?


Can we attach an alert to a field?


Explain What are the main types of TRIGGERS?


Explain EXIT_FORM is a restricted package procedure? a. True b. False?


Explain When is PRE-QUERY trigger executed?


Explain the built-in routine used to count the no of rows in a group?


Explain How do we trap the error in forms 3.0 ?


Explain What are the types of Pop-up window?


Explain What Enter package procedure does?


Explain What does an on-clear-block Trigger fire?


Explain How image_items can be populate to field in forms 4.0?


Explain What is a window?


Explain What are the types of canvas-views?


Explain What is the difference between a POST-FIELD trigger and a POST-CHANGE trigger?


Explain What is an User Exits?


Explain What is the maximum size of a form?

Some Useful PL/SQL Questions

State the difference between implicit and explicit cursor's.


How to avoid using cursors? What to use instead of cursor and in what cases to do so?


What is trigger,cursor,functions in pl-sql and we need sample programs about it?


What is difference between stored procedures and application procedures,stored function and application function?


What is pl/sql?what are the advantages of pl/sql?


What is Raise_application_error ?


Explain the usage of WHERE CURRENT OF clause in cursors ?


How we can create a table in PL/SQL block. insert records into it??? is it possible by some procedure or function?? please give example...


What is the starting "oracle error number"? 2)What is meant by forward declaration in functions?


Name the tables where characteristics of Package, procedure and functions are stored ?


How to disable multiple triggers of a table at at a time?


In a Distributed Database System Can we execute two queries simultaneously ? Justify ?


How many types of database triggers can be specified on a table ? What are they ?


Can Commit,Rollback ,Savepoint be used in Database Triggers?If yes than HOW? If no Why?With Reasons


In pl/sql functions what is use of out parameter even though we have return statement.


Explain how procedures and functions are called in a PL/SQL block ?


How to debug the procedure ?


What will the Output for this Coding? Declare Cursor c1 is select * from emp FORUPDATE; Z c1%rowtype; Begin Open C1; Fetch c1 into Z; Commit; Fetch c1 in to Z; end;


Explian rowid,rownum?What are the pseduocolumns we have?


State the advantage and disadvantage of Cursor?
What is Pragma EXECPTION_INIT ? Explain the usage ?


Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?


What is TTITLE and BTITLE?


What is ref cursor?


What are two parts of package ?


What are the Restrictions on Cursor Variables?


How we can create a table through procedure ?


What will happen after commit statement ?


Can we declare a column having number data type and its scale is larger than pricesion ex: column_name NUMBER(10,100), column_name NUMBAER(10,-84)


What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?


What is a database trigger ? Name some usages of database trigger ?


Give the structure of the function ?


What is difference between a PROCEDURE & FUNCTION ?


What is SQL Integrity?


What are the return values of functions SQLCODE and SQLERRM ?


The most important DDL statements in SQL are?


What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?


What is SQL Deadlock?


The IN operator may be used if you know the exact value you want to return for at least one of the columns.

Some Useful SQL Questions

Find out nth highest salary from emp table

Difference between procedure and function.

Whet are the difference between primary key and foreign key?

What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?

Difference between database triggers and form triggers?

Display Odd/ Even number of records

Which datatype is used for storing graphics and images?

What is difference between SQL and SQL*PLUS?

Can you use a commit statement within a database trigger?

What is difference between SUBSTR and INSTR?

Which is more faster - IN or EXISTS?

What is an UTL_FILE.What are different procedures and functions associated with it?

What WHERE CURRENT OF clause does in a cursor?

What is a cursor?

What is the purpose of a cluster?

Difference between an implicit & an explicit cursor.

Differentiate between TRUNCATE and DELETE

There is a % sign in one field of a column. What will be the query to find it?

What should be the return type for a cursor variable.Can we use a scalar data type as return type?

What a SELECT FOR UPDATE cursor represent.
Can a primary key contain more than one columns?

minvalue.sql Select the Nth lowest value from a table

Can a function take OUT parameters. If not why?

Display the number value in Words?

How you will avoid your query from using indexes?

What is difference between Rename and Alias?

What are various joins used while writing SUBQUERIES?

Difference between NO DATA FOUND and %NOTFOUND

What is syntax for dropping a procedure and a function .Are these operations possible?

What are various privileges that a user can grant to another user?

What is OCI. What are its uses?

What is difference between a formal and an actual parameter?

Can the default values be assigned to actual parameters?

Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

How do you find the number of rows in a Table ?

What are different modes of parameters used in functions and procedures?

How you were passing cursor variables in PL/SQL 2.2?

What is a pseudo column. Give some examples?

Display the records between two range?

What are ORACLE PRECOMPILERS?
How will you delete duplicating rows from a base table?

When do you use WHERE clause and when do you use HAVING clause?

maxvalue.sql Select the Nth Highest value from a table

There is a string 120000 12 0 .125 , how you will find the position of the decimal place?

What is difference between UNIQUE and PRIMARY KEY constraints?

What are various constraints used in SQL?

What are different Oracle database objects?

Explain Difference between database triggers and form triggers?

what is a index?with example? what is subquery with example? what is deadlock in SQL with example? what is cursor advantatage?

How you will avoid duplicating records in a query?

The maximum number of columns a table can have in oracle SQL ?

Can cursor variables be stored in PL/SQL tables.If yes how. If not why?

How you open and close a cursor variable.Why it is required?

Explain What is difference between SUBSTR and INSTR?

When I have 6 values for any column, in select statement I get display of 6 rows selected while if I have 5 values for one column i don't get display of 5 rows selected, how to get that display?

How to pass cursor variables in PL/SQL 2.2?

What is a view?

Explain What is difference between SQL and SQL*PLUS?

What are cursor attributes?

Explain Which datatype is used for storing graphics and images?