composite Datatype

COMPOSITE DATATYPE ARE TWO TYPES

--PL/SQL Records
--PL/SQL collection
-->INDEX BY table
-->Nested Tables
-->VARRAY
-------------------------------------------------------------

***************************
DECLARATION OF PL/SQL RECORDS
***************************

DECLARE
TYPE t_Rec1Type IS RECORD (
Field1 NUMBER,
Field2 VARCHAR2(5));
TYPE t_Rec2Type IS RECORD (
Field1 NUMBER,
Field2 VARCHAR2(5));
v_Rec1 t_Rec1Type;
v_Rec2 t_Rec2Type;
BEGIN
/* Even though v_Rec1 and v_Rec2 have the same field names
and field types, the record types themselves are different.
This is an illegal assignment which raises PLS-382.
v_Rec1 := v_Rec2;*/

v_Rec1.Field1 := v_Rec2.Field1;
v_Rec2.Field2 := v_Rec2.Field2;
END;
/
----------------------------------------------------------------

***************************
RECORD USING SELECT STATEMENT
***************************

DECLARE
TYPE EMPRecord IS RECORD (
A EMP.ENAME%TYPE,
B EMP.SAL%TYPE,
C EMP.MGR%TYPE);
EMPL EMPRecord;
BEGIN
SELECT ENAME,SAL,MGR
INTO EMPL
FROM EMP
WHERE COMM = 0;
DBMS_OUTPUT.PUT_LINE('ENAME:>'||EMPL.A);
DBMS_OUTPUT.PUT_LINE('SALARY:>'||EMPL.B);
DBMS_OUTPUT.PUT_LINE('MANAGER:>'||EMPL.C);
END;

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

***************************

RETRIEVING DATA USING ROWTYPE
***************************

DECLARE
TYPE EMPRecord IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
EMPL EMPRecord;
BEGIN
SELECT *
INTO EMPL(100)
FROM EMP
WHERE COMM = 0;
DBMS_OUTPUT.PUT_LINE('ENAME:>'||EMPL(100).ENAME);
END;
------------------------------------------------------------------

***************************
USING COUNT ATTRIBUTE
***************************

DECLARE
TYPE NUMBRec IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
Numbers NUMBRec;
Total NUMBER;
BEGIN
FOR Counter IN 1..50 LOOP
Numbers(Counter) := Counter;

END LOOP;
Total := Numbers.COUNT;
DBMS_OUTPUT.PUT_LINE('THE COUNT IS :>'||TOTAL);

END;
------------------------------------------------------------------

***************************
"DELETE" STATEMENT
***************************

DECLARE
TYPE t_ValueTable IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
Val t_ValueTable;
BEGIN
Val(1) := 'One';
Val(3) := 'Three';
Val(-2) := 'Minus Two';
Val(0) := 'Zero';
Val(100) := 'Hundred';
DBMS_OUTPUT.PUT_LINE('Before DELETE, COUNT=' || Val.COUNT);
Val.DELETE(100); -- Removes 'Hundred'
DBMS_OUTPUT.PUT_LINE('After first DELETE, COUNT=' || Val.COUNT);
Val.DELETE(1,3); -- Removes 'One' and 'Three'
DBMS_OUTPUT.PUT_LINE('After second DELETE, COUNT=' || Val.COUNT);
Val.DELETE; -- Removes all remaining values
DBMS_OUTPUT.PUT_LINE('After last DELETE, COUNT=' || Val.COUNT);
END;
/
------------------------------------------------------------------

***************************
INSERTING THROUGH PL/SQL RECORD
***************************

CREATON OF TABLE BEFORE EXAMPLE
CREATE or REPLACE TABLE temp_table(CAR_COL VARCHAR2(40));

/*INSERTING using "EXIST" ATTRIBUTE*/
DECLARE
TYPE NameTable IS TABLE OF EMP.ENAME%TYPE
INDEX BY BINARY_INTEGER;
EMPL NameTable;
BEGIN
-- Insert rows into the table.
EMPL(1) := 'Scott';
EMPL(3) := 'Joanne';

-- Check to see if rows exist.
IF EMPL.EXISTS(1) THEN
INSERT INTO temp_table(char_col) VALUES('Row 1 exists!');
ELSE
INSERT INTO temp_table (char_col) VALUES('Row 1 doesn''t exist!');
END IF;

IF EMPL.EXISTS(2) THEN
INSERT INTO temp_table (char_col) VALUES('Row 2 exists!');
ELSE
INSERT INTO temp_table (char_col) VALUES('Row 2 doesn''t exist!');
END IF;
END;
/

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

***************************
USING 'FIRST'&'LAST'&'NEXT' ATTRIBUTE
***************************

DECLARE
TYPE NameTable IS TABLE OF EMP.Ename%TYPE INDEX BY BINARY_INTEGER;
Names NameTable;
IndX BINARY_INTEGER;
BEGIN
Names(43) := 'Mason';
Names(50) := 'Junebug';
Names(47) := 'Taller';

-- Assigns 43 to v_Index.
Indx := Names.FIRST;
DBMS_OUTPUT.PUT_LINE('INDEX='||Indx );

-- Assigns 47 to v_Index.
Indx := Names.NEXT(Indx);
DBMS_OUTPUT.PUT_LINE('INDEX='||Indx );

-- Assigns 47 to v_Index.
Indx := Names.PRIOR(Indx);
DBMS_OUTPUT.PUT_LINE('INDEX='||Indx );

-- Assigns 50 to v_Index.
Indx := Names.LAST;
DBMS_OUTPUT.PUT_LINE('INDEX='||Indx );

END;
---------------------------------------------------------------------------

***************************
A MORE PRACTICAL EXAMPLE
***************************

DECLARE
TYPE EMPTABLE IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
MYEMPTABLE EMPTABLE;
COUNTER NUMBER(3):=104;
BEGIN
FOR I IN 100..COUNTER
LOOP
SELECT * INTO MYEMPTABLE(I) FROM EMP WHERE COMM=0;
END LOOP;
FOR I IN MYEMPTABLE.FIRST..MYEMPTABLE.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(MYEMPTABLE(I).ENAME);
END LOOP;
END;
---------------------------------------------------------------------------

Control Statement using tables

****************************
Control statement1 (if)
****************************

DECLARE
NAME VARCHAR2(30);
BEGIN
SELECT ENAME
INTO NAME
FROM EMP WHERE SAL=5000;
IF NAME='KING' THEN
DBMS_OUTPUT.PUT_LINE('THE FIRST TWO LETTER OF THE MAN IS -----> ' ||SUBSTR(NAME,1,2));
END IF;
END;

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

****************************
Control statement2 (if-then-else-elsif)
****************************

DECLARE
NAME VARCHAR2(30);
SALARY NUMBER(5);
BEGIN
SELECT ENAME,SAL
INTO NAME,SALARY
FROM EMP WHERE SAL=5000;
IF NAME='KING' THEN
DBMS_OUTPUT.PUT_LINE('THE FIRST TWO LETTER OF THE MAN IS -----> ' ||SUBSTR(NAME,1,2));
ELSIF NAME='FORD' THEN
DBMS_OUTPUT.PUT_LINE('THE SALARY OF THE MAN IS -----> ' ||SALARY);
ELSE
DBMS_OUTPUT.PUT_LINE('SORRY NO RESULT FOUND');
END IF;
END;
--------------------------------------------------------------

****************************
CONTROL STATEMENT3(case-when-then)
****************************

DECLARE
GRD CHAR:=UPPER('&GRADE');
STD VARCHAR2(30);
BEGIN
STD:=
CASE GRD
WHEN 'A' THEN 'EXCELLENT'
WHEN 'B' THEN 'VERY GOOD'
WHEN 'C' THEN 'GOOD'
ELSE 'NO SUCH GRADE'
END;
DBMS_OUTPUT.PUT_LINE('GRADE::'||GRD||'---'||'STANDERD>>'||STD);
END;
--------------------------------------------------------------

****************************
HANDLING NULL VALUE
****************************

DECLARE
X NUMBER(3):=12;
Y NUMBER(3):=0;
BEGIN
X:=12*NULL;
IF X IS NULL THEN
DBMS_OUTPUT.PUT_LINE('THE VALUE OF X IS NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('THE VALUE OF Y IS::'||Y);
END IF;
END;
--------------------------------------------------------------

****************************
USING NORMAL "LOOP-EXIT WHEN"
****************************

DECLARE
B NUMBER(5);
CNTR NUMBER(10):=1;
BEGIN
SELECT EMPNO INTO B FROM EMP WHERE SAL=5000;
LOOP
INSERT INTO STUD(ROLL) VALUES(B+CNTR);
CNTR:=CNTR+1;
EXIT WHEN CNTR>3;
END LOOP;
END;
-------------------------------------------------------------

****************************
USING NORMAL "WHILE LOOP"
****************************

DECLARE

B NUMBER(5);
CNTR NUMBER(10):=1;
BEGIN
SELECT EMPNO INTO B FROM EMP WHERE SAL=5000;
WHILE CNTR<=3
LOOP
INSERT INTO STUD(ROLL) VALUES(B+CNTR);
CNTR:=CNTR+1;
END LOOP;
END;
-------------------------------------------------------------

****************************

USING NORMAL "FOR LOOP"
****************************

DECLARE
B NUMBER(5);
CNTR NUMBER(10):=1;
BEGIN
SELECT EMPNO INTO B FROM EMP WHERE SAL=5000;
FOR CNTR IN 1..3
LOOP
INSERT INTO STUD(ROLL) VALUES(B+CNTR);
END LOOP;
END;
-------------------------------------------------------------

****************************
EXAMPLE OF NESTED LOOPS
****************************

DECLARE
B NUMBER(5);
CNTR NUMBER(10):=1;
BEGIN
SELECT EMPNO INTO B FROM EMP WHERE SAL=5000;
<>
LOOP
<>
LOOP
EXIT WHEN CNTR>3;
DBMS_OUTPUT.PUT_LINE('YOUR DATA WILL BE INSERTED'||CNTR);
CNTR:=CNTR+1;
END LOOP INNER_LOOP;
INSERT INTO STUD(ROLL) VALUES(B+CNTR);
CNTR:=CNTR+1;
EXIT WHEN CNTR>7;
END LOOP OUTER_LOOP;
END;

Interacting with Oracle Server

****************************************

USED TABLE IN EXAMPLES

*****************************************

CREATE TABLE STUD(NAME VARCHAR2(30),ROLL NUMBER(5),DOB DATE,PRIMARY KEY(ROLL));
--------------------------------------------------------

***************************

SELECT STATEMENT IN PL/SQL

***************************

SET SERVEROUTPUT ON
DECLARE
NAME VARCHAR2(30);
ID NUMBER(5);
BEGIN
SELECT ENAME,EMPNO
INTO NAME,ID
FROM EMP
WHERE SAL=5000;
DBMS_OUTPUT.PUT_LINE('ENAME->'||NAME||'***** EMPLOYEE NUMBER->'||ID);
END;
--------------------------------------------------------

***************************

ANOTHER EXAMPLE USING %TYPE ATTRIBUTE

***************************

SET SERVEROUTPUT ON
DECLARE
NAME DEPT.DNAME%TYPE;
ID DEPT.DEPTNO%TYPE;
BEGIN
SELECT DNAME,DEPTNO
INTO NAME,ID
FROM DEPT
WHERE LOC='DALLAS';
DBMS_OUTPUT.PUT_LINE('DEPARTMENT NAME->'||NAME);
DBMS_OUTPUT.PUT_LINE('DEPARTMENT NUMBER->'||ID);

END;
--------------------------------------------------------

***************************

INSERTING DATA THROUGH PL/SQL

***************************

DECLARE
STNAME VARCHAR2(30):='ARKA';
STROLL NUMBER(5):=12;
STDOB DATE:='16-MAY-1985';
BEGIN
INSERT INTO STUD
VALUES(STNAME,STROLL,STDOB);
END;
--------------------------------------------------------

***************************

UPDATING DATA THROUGH PL/SQL

***************************

DECLARE
STROLL NUMBER(5):=20;
BEGIN
UPDATE STUD
SET ROLL = STROLL
WHERE NAME='ARKA';
END;
--------------------------------------------------------

***************************

DELETING DATA THROUGH PL/SQL

***************************

DECLARE
STROLL NUMBER(5):=20;
BEGIN
DELETE FROM STUD
WHERE ROLL=STROLL;
EMP;
--------------------------------------------------------

nested block

************************************************

USING SQL FUNCTION IN PL/SQL BLOCK

************************************************

SET SERVEROUTPUT ON
DECLARE
A VARCHAR2(30):=LOWER('ARKA BHATTACHARYA');
BEGIN
DBMS_OUTPUT.PUT_LINE('THE LOWERCASE NAME ::::'||A);
DBMS_OUTPUT.PUT_LINE('THE UPPERCASE NAME ::::'||UPPER(A));
END;

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

************************************************

NESTED PL/SQL BLOCK

************************************************

<>
DECLARE
A NUMBER(5):=10;
B NUMBER(6):=20;
BEGIN
DECLARE
A NUMBER(5):=10;
B NUMBER(6):=20;
BEGIN
B:=A+B+OUTER.B;
DBMS_OUTPUT.PUT_LINE('THE SUM IS ' ||B);
END;
DBMS_OUTPUT.PUT_LINE('THE SUM IS ' ||B);
END;

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

control statement

TO COMMENT A CODE

(--) ---->Single line comment
(/* */)-->Multiple line comment
--------------------------------------

/*GREATEST AMONG THREE (USE OF IF)*/

DECLARE
A NUMBER(2):=&A;
B NUMBER(2):=&B;
C NUMBER(2):=&C;
BEGIN
IF A>B AND A>C THEN
DBMS_OUTPUT.PUT_LINE(A||'IS LARGEST');
ELSIF B>A AND B>C THEN
DBMS_OUTPUT.PUT_LINE(B||'IS LARGEST');
ELSE
DBMS_OUTPUT.PUT_LINE(C||'IS LARGEST');
END IF;
END;
----------------------------------------

/*looping using EXIT WHEN statement*/

declare
counter number(2):=0;
begin
loop
DBMS_OUTPUT.PUT_LINE('the count is'||counter);
counter:=counter+1;
exit when counter=10;
end loop;
end;
/

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

/*looping using WHILE */

declare
counter number(2):=0;
begin
WHILE COUNTER<=10
loop
DBMS_OUTPUT.PUT_LINE('the count is'||counter);
counter:=counter+1;
end loop;
end;
/
-----------------------------------------

/*looping using WHILE
BUT NOT INITIALIZING THE COUNTER */

declare
counter1 number(2);
begin
WHILE COUNTER1<=10
loop
DBMS_OUTPUT.PUT_LINE('the SECOND count is'||counter1);
counter1:=counter1+1;
end loop;
end;
/
-----------------------------------------

/*looping using FOR INCREMENT SINGLE */

declare
counter1 number(2);
begin
FOR COUNTER1 IN 1..10
loop
DBMS_OUTPUT.PUT_LINE('the count is'||counter1);
end loop;
end;
/

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

/*LOOPING USING GOTO*/

DECLARE
A NUMBER(2):=0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(A);
A:=A+1;
IF A>10 THEN
GOTO END_OF_LOOP;
END IF;
END LOOP;
<>
DBMS_OUTPUT.PUT_LINE('THE LOOP IS END');
LOOP
A:=A-1;
DBMS_OUTPUT.PUT_LINE(A);
EXIT WHEN A<=5;
END LOOP;
END;
-----------------------------------------

/*Using NULL as a statement*/

DECLARE
v NUMBER := 7;
BEGIN
IF v<5 THEN
dbms_output.put_line('less than 7');
ELSIF v>7 THEN
dbms_output.put_line('Greater than 7');
ELSE
NULL; -- Do nothing
END IF;
END;
/

control statement PL/SQL

****************************
TO COMMENT A CODE
****************************

(--) ---->Single line comment
(/* */)-->Multiple line comment
--------------------------------------

****************************
IF STATEMENT
****************************

/*GREATEST AMONG THREE (USE OF IF)*/
DECLARE
A NUMBER(2):=&A;
B NUMBER(2):=&B;
C NUMBER(2):=&C;
BEGIN
IF A>B AND A>C THEN
DBMS_OUTPUT.PUT_LINE(A||'IS LARGEST');
ELSIF B>A AND B>C THEN
DBMS_OUTPUT.PUT_LINE(B||'IS LARGEST');
ELSE
DBMS_OUTPUT.PUT_LINE(C||'IS LARGEST');
END IF;
END;
----------------------------------------

****************************
Looping using EXIT WHEN statement
****************************

declare
counter number(2):=0;
begin
loop
DBMS_OUTPUT.PUT_LINE('the count is'||counter);
counter:=counter+1;
exit when counter=10;
end loop;
end;
/

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

****************************
Looping using WHILE
****************************

declare
counter number(2):=0;
begin
WHILE COUNTER<=10
loop
DBMS_OUTPUT.PUT_LINE('the count is'||counter);
counter:=counter+1;
end loop;
end;
/
-----------------------------------------

****************************
Looping using WHILE BUT
NOT INITIALIZING THE COUNTER
****************************

declare
counter1 number(2);
begin
WHILE COUNTER1<=10
loop
DBMS_OUTPUT.PUT_LINE('the SECOND count is'||counter1);
counter1:=counter1+1;
end loop;
end;
/
-----------------------------------------

****************************
Looping using FOR INCREMENT SINGLE
****************************

declare
counter1 number(2);
begin
FOR COUNTER1 IN 1..10
loop
DBMS_OUTPUT.PUT_LINE('the count is'||counter1);
end loop;
end;
/

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

****************************
LOOPING USING GOTO
****************************

DECLARE
A NUMBER(2):=0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(A);
A:=A+1;
IF A>10 THEN
GOTO END_OF_LOOP;
END IF;
END LOOP;
<>
DBMS_OUTPUT.PUT_LINE('THE LOOP IS END');
LOOP
A:=A-1;
DBMS_OUTPUT.PUT_LINE(A);
EXIT WHEN A<=5;
END LOOP;
END;
-----------------------------------------

****************************
Using NULL as a statement
****************************

DECLARE
v NUMBER := 7;
BEGIN
IF v<5 THEN
dbms_output.put_line('less than 7');
ELSIF v>7 THEN
dbms_output.put_line('Greater than 7');
ELSE
NULL; -- Do nothing
END IF;
END;
/

PL/SQL normal declaration

***********************************************
To see the output of a PL/SQL program output
***********************************************
set serveroutput on

An Complete Pl/SQl program with 3 block
DECLARE
v_StudentID NUMBER(5) := 7369; -- Numeric variable initialized
-- to 10,000
v_FirstName VARCHAR2(20); -- Variable length character string
-- with maximum length of 20
BEGIN
/* Start of executable section */
-- Retrieve first name of student with ID 10,000
SELECT ename
INTO v_FirstName
FROM emp
WHERE empno=v_StudentID;
dbms_output.put_line (v_Firstname);
EXCEPTION
/* Start of exception section */
WHEN NO_DATA_FOUND THEN
-- Handle the error condition
dbms_output.put_line ('There is an error');
END;


****************************************************

Using BIND Variable

****************************************************
set serveroutput on
VARIABLE g_salary NUMBER
BEGIN
SELECT sal
INTO :g_salary
FROM emp
WHERE ename = 'SMITH';
END;
/
PRINT g_salary

******************************************************

Using Non PL/SQL variable

******************************************************
set serveroutput on
VARIABLE g_monthly_sal NUMBER
DEFINE p_annual_sal = 50000
SET VERIFY OFF
DECLARE
v_sal NUMBER(9,2) := &p_annual_sal;
BEGIN
:g_monthly_sal := v_sal/12;
END;
/
PRINT g_monthly_sal
*****************************************************

****************************************************

DECLARATION OF VARIABLE

****************************************************
set serveroutput on buffer 25600000
DECLARE
a varchar2(30):='Arka Bhattacharya';
b number(3):=10;
c date:= '15-sep-1999';
C1 DATE := SYSDATE + 7;
d NUMBER(6) DEFAULT 100;
e VARCHAR2(30) NOT NULL := 'Oxford';
f char:='P';
g long:=3200;
h BINARY_INTEGER:=1;
I CONSTANT NUMBER(3,2) := 8.25;
J BOOLEAN NOT NULL := TRUE;
K emp.ename%TYPE;
l b%TYPE:=10;
m boolean := FALSE;
BEGIN
DBMS_OUTPUT.PUT_LINE(a);
end;
--------------------------------------------------------------------

****************************************************

DETAILED EXAMPLE
****************************************************

DECLARE
a varchar2(20):='Arka Bhattacharya';
b number(3):=10;
c date:= '15-sep-1999';
C1 DATE := SYSDATE + 7;
d NUMBER(6) DEFAULT 100;
e VARCHAR2(30) NOT NULL := 'Oxford';
f char:='P';
g long:=3200;
h BINARY_INTEGER:=1;
I CONSTANT NUMBER(3,2) := 8.25;
K emp.ename%TYPE:='SMITH';
l b%TYPE:=10;
BEGIN
DBMS_OUTPUT.PUT_LINE(a);
DBMS_OUTPUT.PUT_LINE(b);
DBMS_OUTPUT.PUT_LINE(c);
DBMS_OUTPUT.PUT_LINE(c1);
DBMS_OUTPUT.PUT_LINE(d);
DBMS_OUTPUT.PUT_LINE(e);
DBMS_OUTPUT.PUT_LINE(f);
DBMS_OUTPUT.PUT_LINE(g);
DBMS_OUTPUT.PUT_LINE(h);
DBMS_OUTPUT.PUT_LINE(i);
DBMS_OUTPUT.PUT_LINE(K);
DBMS_OUTPUT.PUT_LINE(L);

end;
*******************************************************

Books on Oracle

oracle 9i dba fundamental1
http://www.esnips.com/doc/84f85f55-0f0d-49e4-9eea-2f84241c9a70/Oracle-9i-Fundamentals-I

oracle 9i dba fundamental 2
http://www.esnips.com/doc/da8a4241-c39d-4e97-956c-3d4da843ca11/Oracle-9i-Fundamentals-II

oracle sql
http://www.esnips.com/doc/770497fe-5158-422f-a801-a6fe6c2dd64d/Oracle-RDBMS--SQL-Tutorial-(Very-good)

oracle pl/sql
http://www.esnips.com/doc/f9f628fd-8c1d-40a5-9b5f-cdb1c1a2467d/Oracle-9i---Introduction-to-oracle-9i-PL-SQL---Part-1
http://www.esnips.com/doc/f69ad628-85dc-4c90-9242-ea5dff7cc026/Oracle-9i---Introduction-to-oracle-9i-PL-SQL---Part-2
http://www.esnips.com/doc/d268649f-4bb7-4d2a-8fe8-b8016a233da0/Oracle-9i---Introduction-to-oracle-9i-PL-SQL---Part-3

oracle9i sql
http://www.esnips.com/doc/fdcd6521-7fa3-49f4-ba29-62a4d23e0928/Oracle-9i-Introduction-To-SQL-Part-I
http://www.esnips.com/doc/5f9a7fb5-8d78-4d0a-ac3a-195425bb88a4/Oracle-9i-Introduction-To-SQL-Part-II
http://www.esnips.com/doc/5f35561d-c985-4719-b4c1-c8739cd38afd/Oracle-9i-Advanced-SQL-vol2

oracle 9i performance tuning
http://www.esnips.com/doc/34b4f388-9f5c-48a8-8365-bebc0954819c/Oracle-9i-Performance-and-Tuning-Volume-1
http://www.esnips.com/doc/7c073f29-0263-48f3-b8b0-22d3385659d2/Oracle-9i-Performance-and-Tuning-Volume-2

Some good problem

***************************

Some good problem
***************************

1. Display MGR and ENAME from EMP table.
2. Display the records where the job is either SALESMAN or DEPT NO =20.
3. Display the record where MGR is blank.
4. Display all the records where names begin with ‘A’.
5. Display the records whose ENAME begins with the letter in between ‘K’ to ‘M’.
6. Display all the records whose ENAME contain 5 characters.
7. Display total number of employees department wise.
8. Display the department that contains maximum employees.
9. Display the employee who getting maximum salary.
10. Display the last 4 character of each employee
11. Display the year and the total number of employee hired during a year.
12. Display the year and the total number of employee for the year in which maximum numbers of employee were hired.
13. Display the record for all employees who have the same job as EMP NO=7838.
14. Display all the records where job contain the letter “E”
15. Display the record where the salary of the employee is greater than the salary obtained by ‘TURNER”
16. Select all employee who are either ‘clerk’ or ‘salesman’ and have salary greater than 1500.
17. Select all the employee whose names start with ‘Th’ or ‘Lh’.
18. Display names ,annual salary ,comm. of those salesperson whose monthly salary >comm..
19. Select employees who are with the company for more than 25 years
20. Display details of those employees who have join the company on same date.
21. Display details of highest paid employee in the EMP table.
22. Display the 2^nd highest paid employee in the EMP table
23. Display DEPT NO which do not have clerk.
24. Update sal of all employees by 2000 who are working in the company for more than 15 years and drawing sal<4000.
25. Display ENAME , HIREDATE in ascending order.
26. Suppose a table contain thousand data and you are needed to select the 2^nd last record from the table .write the query.
27. Print the list of employees displaying last salary if exactly 1500 .display on target if less than 1500.
28. Display DEPT NO and the name of the employee who gets minimum salary in each department.
29. Display ENAME ,annual salary and comm. Of every employee in DEPT NO 30 and fill the comm. Field with 0 if it doesn’t contain any value.
30. Display details of that employee who have no manager.
31. Display details of those employees who are clerk and whose salary lies between 1000 and 2000
32. . List the employee by name , salary, and department name for every employee in the company except clerks , sort on salary , displaying the highest salary first.

32. Display Name and Total Remuneration for all employees.
33. List the employee name and salary increased by 20%.
34. Display each employee name with hire date and salary review date .assuming review date is first year after hire date
35. Find out how many managers are there without listing them.
36. Find the average salary and avg Total Remuneration for each job types. Remember Salesman earn comm..
37. Find the job that was filled in the first half of 1983 and the same job that was filled during the same period of 1984.
38. List all employees by NAME and NUMBER along with their manager’s details.
39. Write the query to display details for any employee who earns a salary greater that the average for their department .Sort in Department Number order.
40. Find out the employee name , salary , dept no, who earns greater than every employee in Department no 30.
41. Find the Department having maximum employees.
42. Find the employee who earns more than ‘MILLER”
43. Find all the person who are not MANAGERS
44. Find the name of person getting same salary in different department .
45. Find the job whose average salary is equal to maximum average salary of any job.
46. Find out the details of all the person who have been assigned same job in different DEPT NO.
47. Find the department which has more than 3 employees.
48. Write a query which will return the day of the week ,for any date entered in format :DD:MM:YY.
49. Check whether all employee number are indeed unique.
50. List lowest paid employees working for each manager. Exclude any group where the min salary is <1000.sort>
51. Show the records of employees working in DALLAS location.
52. List the following details for employees who earn $36,000 a year or who are clerk.
53. List all the employees by name, and number along with their manager’s name and number.
54. Find the employees who joined the company before their managers.
55. Find the job with the highest average salary.
56. Find the employees who have at least one person reporting to them.
57. Find all employees whose department is not in DEPT table.
58. Find Department which has no employees.
59. Display the following information for the department with the highest annual remuneration bill.
60. In which year did most people join the company?Display the year and number of employees.