Oracle SQL & PL/SQL

INTRODUCTION : - SQL usually pronounced as “Sequel” stands for Structured Query Language. SQL is the native language of the Oracle Server. It is the language used to communicate with the database. Again SQL consists of SQL statements and SQL*Plus commands. SQL statements are used to talk to the database. When you enter a SQL statement, it is stored in a part of the memory called the SQL Buffer and remains there until a new statement is entered. SQL*Plus is an Oracle tool that recognizes and submits  SQL statements to the Oracle server for execution and contains its own command language. Features of SQL:- It is very easy to write, it can be used by all kinds of users with little or not programming experience. It is a non-procedural language. It reduces the amount of time required for creating and maintaining systems. It is based on American National Standards Iinstitute (ANSI) standard SQL. It manipulates data and ...

An Object is a reusable application component that developers need to be aware of, rather than how it works. Object are basic entities in a system. They could represent a person, place, bank account, or any item that is handled by program. Every object consists of an attribute and one or more methods. An attribute could be any property of the object. Class:           It is a collection of attributes and functions (method) to plan the object. Object Table : ·    Object table are created by using the user defined data types. ·        In an object table each row or record is treated as an object. ·        Each row in an object table has an object Identified (OID), which is unique through out       the   database. ·       The rows or objects of an object table can be referenced by other objects with in the  database. ·    ...

This clause in a select statement arranges the data on a specified column in ascending or in descending order. This clause will work after the execution of select statement. By default it arranges the data in ascending order. In order to arrange the data in descending order , an option called DESC is used. Data is arranged in order over a required column for temporary. Order by clause can be specified with one or more columns.       Examples :-         Display empno, ename and salary of all the employees arranging salaries in ascending order.           Ans : Select empno, ename, sal from emp order by sal;           Display ename, job, salary, deptno, commission, and hiredate of all those employees  who are working as CLERK, SALESMAN and they have been hired in the year 81 and they don’t earn any commission. Arrange the data in ascending order of salary.    ...

This operator tests for NULL values. It is the only operator that can be used to test for NULL’s. The negation is IS NOT NULL.       Examples :-        Select ename, deptno, comm from emp where comm IS NULL;         Select ename, deptno, comm from emp where comm IS NOT NULL;         Select ename, deptno, job, mgr from emp where mgr IS NULL;         Select ename, deptno, job, mgr from emp where mgr IS NOT NULL; HOME

This operator is used to test for values in a specified list. This operator can be used upon any data type. The negation of the operator is NOT IN.       Examples :-       Select ename, sal, job from emp where ename IN(‘ALLEN’ , ‘SCOTT’);         Select ename, sal, job from emp where ename NOT IN(‘ALLEN’ , ‘SCOTT’);         Select ename, sal, job, deptno from emp where deptno IN(10,30);         Select ename, sal, job, deptno from emp where deptno NOT IN(10,30);         Select ename, sal, hiredate from emp where hiredate IN(’02-APR-81’ , ’12-JAN-83’);         Select ename, sal, hiredate from emp where hiredate NOT IN(’02-APR-81’ , ’12-JAN-83’); HOME

This operator is used to display rows based on a range of values. The declared range is inclusive. The lower limit should be declared first. The range that you specify contains a lower limit and an upper limit. Examples :-           Select ename, sal, job from emp w here sal BETWEEN 1000 AND 2000;           Select ename, sal, job from emp where sal NOT BETWEEN 1000 AND 2000;           Select ename, sal, job from emp where job BETWEEN ‘MANAGER’ AND ‘SALESMAN’;          Select ename, sal, job from emp where job NOT BETWEEN ‘MANAGER’ AND ‘SALESMAN’;          Select ename, sal, job, hiredate from emp where hiredate BETWEEN ’02-APR-81’ AND ’12-JAN-83’;          Select ename, sal, job, hiredate from emp where hiredate NOT BETWEEN ’02-APR-81’ AND ’12-JAN-83’; HOME

Supressing Duplicate Rows : = Sometimes, your query results contain duplicate rows. You can eliminate such rows by adding  the keyword DISTINCT immediately after the keyword SELECT. Multiple columns can be declared after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and represents a DISTINCT combination of the columns. When DISTINCT is implemented upon a column which may contain NULL values, all the NULLS in the column together treated as one DISTINCT group. A select statement should have one DISTINCT keyword. Examples :=           Ø   Select DISTINCT deptno from emp;             Ø   Select DISTINCT deptno,mgr from emp;   Ø   Select DISTINCT deptno,job from emp;        HOME