Reference no: EM131092141

1.a) Create a emp table with empno, ename,job,sal
And solve the following query
a> retrieve all records from emp
b> list name of employee having sal < 3000
c> list name of employee, job, whose name start with letter "S"
d> List name of the emloyee whose name start with letter A and ends with letter D
e>List all manager and analyst with salary over 2500 from emp table

1.a) Create a emp table with empno, ename,job,sal And solve the following query
a. retrieve all records from emp.
b. list name of employee having sal < 3000

c.list name of employee, job, whose name start with letter "S"

d.List name of the emloyee whose name start with letter A and ends with letter D.

e.List all manager and analyst with salary over 2500 from emp table.

1.b) Write syntax and example for following function
a> INITCAP()
b> ROUND()
c> REPLACE()
1.c)a) Update salary of clerks by 0.1 .

b) Insert a row in the new table .

c) Delete new inserted row
d) Using LPAD function pad the job types with 15 characters by ‘*' symbol.
e) Using the length of each employee name from employee table.

b.Insert a row in the new table .

c.Delete new inserted row

d.Using LPAD function pad the job types with 15 characters by ‘*' symbol.

e.Using the length of each employee name from employee table.

2) The HRD manager has decided to raise sal for all employee in detpno=20 by 0.05% whenever such raise is given to employee as record for same is maintained in emp_ raised table it includes empno, date when raised was given and actual raise. Write PL/SQL block to update and insert a record in emp_raised table.

3)Write a pl/sql block using pl/sql table which shows use of its all attributes.
4)Create a table copyemp from scott.emp table and perform following operations on the opyemp table:

Create a table copyemp from scott.emp table

a) Update salary of clerks by 0.1 .

b) Insert a row in the new table .

c) Delete new inserted row

d) add a column "Telephone_No" of data type ‘number' and size ='10' to the employee table.

e) create a view as empview from employee table where basic_sal equal to 3950 and job='Clerk'.

f) Drop the above created empview.

g)Select the average salaries of each department where deptno is either 30or 20 from employee table.

g) Select the name of the lowest paid employee from employee table

h) Define the "Deptno" as not null constraint from dept table.

i) Display Empno, Ename and Annual Salary of each employee from employee table with a column name "Annual Salary" on the basis of basic_sal.

j) List the names and hiredate of employee in deptno 20, display date formatted as ‘12/03/03'.

k) List the name of employee whose hiredate is in the month of December using substr function.

l) Display all jobs types in smaller case from employee table.
5. Create a table emp2 from scott.emp table and perform following operations on the copyemp table

. Create a table emp2 from scott.emp table

a.Update salary of clerks by 0.1 .

b.Insert a row in the new table .

c.Delete new inserted row

d.Add a column "Country" of data type ‘Char' and size = ‘15' to Client_mast table.

e.Define the "Deptno" as not null constraint from dept table.

f.Display Empno, Ename and Annual Salary of each employee from employee 2 table with a column name "Annual Salary" on the basis of basic_sal.

g.Add two columns "HOD" and "Strength" of datatype varchar2 and size= 15 to the Dept table.

h.add check constraint for deptno field to the Dept table check deptno from the 10,20,30,40.

i.create a view as empview from employee table where basic_sal equal to 3950 and job='Clerk'.

j.Drop the above created empview.

k.Select the average salaries of each department where deptno is either 10 or 20 from emp2 table.

l.Display the total number of rows in the employee table.

6) Create a table emp3 from scott.emp table and perform following operations on the emp3 table

m.Update salary of clerks by 0.1 .
n.Insert a row in the new table.
o.Delete new inserted row.

p.List the names and hiredate of employee in deptno 20, display date formatted as ‘12/03/03'.

q.List the name of employee whose hiredate is in the month of December using substr function.
r.Count the number of people in department number 30.

s.produce the following output using employee table:
i. EMPLOYEE
ii. Kim (Manager)
iii. Bruce (Analyst)

t.Display the top three earners with name and salary in the company.

u.Display the ASCII value of "ORACLE

v.Display all jobs types in smaller case from employee table.
w.Display the Total number of working days of each employee from employee table.

x.Display the date, 20 days after today/s date.

y.Count all distinct job types from employee table.

Query 7) write a PL/SQL block to accept a employee number from user and display name and salary and job of the employee
Query 8) write a PL/SQL block to accept employee number and delete that record.
Query 9) write a PL/SQL block to increment Commision of all employee by 300Rs.
Query 10) write a PL/SQL block to accept department number ,name,location and insert into depatment table.

Query 11) Write a PL/SQL block to accept the employee name and update the salary of employee as follows
SAL Increment
>2000 200Rs
2000-3000 400Rs
>3000 & <5000 500Rs

Query 12) Write a PL/SQL Block to update the salary of the given employee number by 0.5% using SQL % found cursor found attribute
Query 13) Display employee name, salary, job of all employee of department 10 using cursor and cursor % is open attribute. Also display total sal.
Query. 15] Write a PL/SQL Block to display employee name,salary, job and department number using parameterized cursor.
Query.15] Write a Cursor Block to display employee number,employee name,employee salary of the top three maximum salary employee detail.
Query 16 ) Write a cursor block to display all employee detail whose commission is null.

Query.17] Write a PL/SQL Block to Except a empno and Display the Detail of Employee give a suitable message if Record is not Found of that empno.
Query 18) Write a PL/SQL Blockto handle too_many_row exception.

Query.19] create a procedure to pass empno as argument and display the employee detail.Write the PL/SQl Block to call this procedure.
Query 20] Write a Procedure to Update the Salaries of all employee by 1% .

Query.21] Create a Function get_sal passing empno as argument and return the total salary of that employee with commission.
Query 22) Create a Trigger which will not allow any transaction on Employee table.
Query.23] Create a Trigger which will not allow any DML Operation on Saturday & Sunday and allow transaction only during 8:00 a.m t0 18:00 p.m.