Oracle SQL & PL/SQL

This clause is used to provide comparison of data, which restricts the rows from a table. A WHERE clause contains a condition that must be met and should directly follow the from clause. Comparison to date type data is not case sensitive. Character values are case sensate and Date values are format Sensitive (DD-MON-YY). The WHERE clause can compares..       Ø   Values in columns         Ø   Literal Values         Ø   Arithmetic Expressions         Ø   Function Examples :- Display all those employees, who are working as CLERK. Ans : Select * from emp where job = ‘CLERK’; Display all those employees, who are working in deptno 20. Ans : Select * from emp where deptno=20; Display all those employees, who have been hired on 3 rd -dec-81. Ans : Select * from emp where hiredate=’3 rd -dec-81’; Display empno,ename,job,sal,deptno for all those empl...

A literal is a constant which represent fixed value.     Types :       Ø   Text literal         Ø   Number literal         Ø   Integer literal         Ø   Interval literal Literal is printed for each row that is retrieved by select statement. Date and character literals must be enclosed in single quote (‘). Literals increase the readability of the output.    Examples :=         Select ename ‘ is designated as ‘ from emp;         Select ‘ I am a ‘ boy from dual; (Here I am a is Literal and boy is an alias.         Select ename, ‘ is designated as ‘ , job from emp;         Select ename || ‘ is designated as ‘ || job from emp; ( Columns on either side   of || operator are combine to make a single column ) Note :   Where we use a literal, th...

Column alias renames a column heading in a query. Its an alternate name for an exiting oracle object. The column alias is specified in the SELECT list by declaring the alias after the column name by using the space separator. Alias heading appears in UPPER casing by default. The alias should be declared in double quotes (“ “). Alias is declared after the column with minimum of one space. The AS keyword can be used between the column name and alias. An alias effectively renames the SELECT list item for duration of the query only. An alias cannot be used, any where in the SELECT list for operational purpose. Note  : Never consider Alias as a column Example :=          1)     Select 20 + 10 Mysum from dual;           2)    Select ename, sal salary, comm commission from emp;           3)    Select sal “Basic Salary” from emp;     ...

This clause is used to provide a condition on group functions, which may filter the data based on the data retrieved at group function. You must need group by clause while declaring having by clause.     Example:=                 1)    Display deptno and total sal of those dept whose total sal is greater  than 9000. Ans : Select deptno, sum(sal) from emp         Group by deptno         Having sum(sal)> 9000;       2)   Display deptno and number of employees working at each dept, only for those departments where more than 3 employees are working.        Ans :  Select deptno, count(*) from emp                 Group by deptno                 Having count(*)>3;  3) Calcul...

This clause is used to group the data over a specified column by eliminating duplicates.    Group by clause always arranged the data in ascending order .    The column which is used at group by clause, the same column can be used for the retrieving the data.    Group by clause will group the data over a single or multiple columns.     Examples :=    1)    Display total salary of all the three departments from the “ emp ” table.    Ans : Select sum(sal) from emp         Group by deptno ;            2)    Display deptno and total salary of all the 3 departments from “emp”table.    Ans : Select deptno,sum(sal) from emp            Group by deptno;    3)   Display deptno and total salary, which is being paid to dept 20.    Ans :...