sql and Plsql practice questions.

Q1) Display those who have joined in 81.
Q2) Display those whose name start with A and job ends with K.
Q3) Display those whose salary includes decimal digits.
Q4) Display those whose salary is even number.
Q5) Display those who joined in 1st 3 days of any month.
Q6) Display those whose names keep exactly 4 characters.
Q7) Update salary by 10% increment for those whose name and job keeps 5 characters each.

Exercises on Independent Sub Queries
------------------------------------
1.  Display those employees whose department is sales.

2.  Display those employees whose grade is 3.

3.  Display those employees whose dept is sales or accounting.

4.  Delete those employees who are in boston's  dept.	

5.  Display those employees whose name starts with 'J'  and his 
    dept  ends with 'S'.

6.  Display those emp who are working under  'KING'.
 
7.  Display those who are working under  JONES / CLARK.

8.  Display those employees whose manager joined in the year 81.

9.  Display those employees  whose joining year same as their managers'
    joining year i.e 81.

10. Display those employees whose managers' department is sales.

11. Dislay those who are working under jones/clark  while the year of
    joining is 81 for both employee and managers.

12.  Display employees whose dept is Accounting and grade is 3/4.

13.  Display those employees whose dept location is new york / chicago.

14.  Display those employees whose job is  clerk & located at chicago. 

15.  Display those employees whose grade is 3 or 4.

16.  Display those employees who joined in 82 and their manager in 81.

17.  Display  scott employee's  manager details. 

18.  Display those whose manager's  salary grade = 5.

19.  Display details of those who are  managers for SALESMAN employees.

Correlated Subqueries :
---------------------
1.  Display those  employees whose dept. is same as their manager's  dept.
2.  Display those employees whose sal is greater than  thier dept avg sal.
3.  Display those emp who have joined before their managers.
4.  Display those managers whose sal is greater than their employees.
---------------------------------------------------------------
5.  Display those employees whose job = 'MANAGER' and do not have
     subordinates.
6.  Display those employees whose sal is same as their manager's sal.
7.  Display those employees who joined in same date as their  manager's.
8.  Dislay those managers who joined before their employees.
9.  display those managers whose sal more than their employees  salary
     and manager's salary with 4 digits  and employee's with 3.
10. Display managers whose salary is less than their employees' salary.                

=============================================

pl sql
====
Programs

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

 

01. Write a program block to take input of one character from user

    and if it is Y/y then delete records from employee table and 

    display proper message for success or failure.

 

02. Accept input of three numbers and display lowest.

 

03. Accept input of one word and display if it keeps even number

    of characters, otherwise display message.

 

04. Accept the input of one string and display first word only.

 

05. Accept the input of one string and display last word only.

 

06. Accept a word and display middle character if the word keeps

    odd number of characters.

 

07. Accept input of one name and display the name as many times

    as the no.of characters present in the word.

 

08. Accept input of one word and display each character as many

    times as its position within the word.

 

09. Accept input of one word and display it in reverse.

 

10. Accept input of one number and display 'VALID' if it is prime

    number else display INVALID.

 

11. Accept input of two values and display  power value by calculating

    first value to the power of second value.

 

12. Accept input of a string and display it without space.

 

13. Accept input of a string and display the total no. of words present

    in the string.

 

14. Accept two numbers and print only those numbers which are prime

    numbers with in the range  of two numbers.

 

15. Accept one string and display the string in reverse word order.

 

===================
1.  Accept input of employee number and display name and job.

 

2.  Accept input of salary and delete all employees having 

      same salary.

 

3.  Insert new record in Department Table by accepting deptno,

     dname, loc values by checking the existence of deptno.

 

4.  Accept deptno and delete all the employees of the entered

    deptno.

 

5.  Accept employee details to insert a new record in emp 

     table with the following checks:

                - Employee name must start with alphabet.

                    - Employee number must not exist in table.

=======================
1.   Calculate simple interest by accepting input of amount,

      rate, year with following validations.

                 - Amount must be in multiple of 500.

                 - rate in % must be 5,10

                 - No. of years must be 1,3,5

 

2.   Accept grade to display no.of employees and total salary

      for the grade.

================
1.  Display all the employee names from emp table.

 

2.  Display total salary paid by company without sum().

 

3.  Display all employee names and their manager names.

 

4.  Display all employee names with manager name,

    department name and grade.

=========================
1.  Display Name, department Name, Grade for all employees.

 

    Solutions:  1) One cursor  2) Three cursors

 

2.  Display all Department names with total no. of employees

    and total salary paid by each department.

 

        Department      Total Employees     Total salary 

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

        Accounting            3                 10000

 

    Solutions : 1) One cursor 2) Two cursors 

                3) One cursor + one select stmt.

 

3.  Take input of year and display the following info.

 

                - No. of Employees joined before : 

                - No. of Employees joined in     :

        - No. of Employees joined after  :

 

    Solutions : 1) with Cursor  2) without cursor

================
1.  Display sum of salary  paid by Company using %type

    datatype.

 

2.  display all employee names, jobs using %rowtype.

 

 

3.  Display Department name followed by Employee names

    as :

         SALES

                ........AAA

                ........BBB

         ACCOUNTING   

                                ........AAA

                ........BBB

 

4.  Display Manager and followed by employee names as:

 

                XXX 

                ........AAA

                ........BBB

                YYY   

                                ........AAA

                ........BBB

 

5.  Take input of grade and display names of department

    which have atleast 3 employees are available for the

    given grade.

 

6.  Display all the employee names in reverse order.

 

7.  Display grades and followed by employees.