Create a emp table with empno

Assignment Help Data Structure & Algorithms
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.

Reference no: EM131092141

Questions Cloud

Generalized by introducing a third parameter : The two-parameter gamma distribution can be generalized by introducing a third parameter g, called a threshold or location parameter: replace x in (4.8) by x - g and  This amounts to shifting the density curves in Figure 4.27 so that they begin the..
Program to convert this temperature in centigrade degree : Temperature of city in Fahrenheit degree is input through the keyboard Draw a flow chart; write an algorithm and program to convert this temperature in centigrade degree.
Applications of the forecasting methods : A disaster is a serious disruption of the functioning of a community or a society involving widespread human, material, economic or environmental losses and impacts
Calculate the change in income : Show graphically what happens when lump sum taxes are increased. Clearly indicate the distance of the curve shifts, and the amount of the income change. Is the effect of lump sum taxes on income greater or less in this model, as compared to the si..
Create a emp table with empno : 1.Create a emp table with empno, ename,job,sal  And solve the following query
Simulation case study-phoenix boutique hotel group : Review "Simulation Case Study: Phoenix Boutique Hotel Group" for this topic's case study, in which you provide guidance to Phoenix Boutique Hotel Group (PBHG) founder Bree Bristowe.
Implement bellman ford algorithm : Implement Bellman Ford Algorithm. Find an application that can best be solved by bellman ford algorithm.
Discuss your relationship to white privilege : Based on McIntosh's article, define white privilege. Based on Martin's article, what constitutes white identity?
Survey of constituency regarding public policy : A congresswoman wants to survey her constituency regarding public policy. She asks one of her staff members to obtain a sample of residents of the district. The frame she has available lists 9012 Democrats, 8302 Republicans, and 3012 Independents.

Reviews

Write a Review

Data Structure & Algorithms Questions & Answers

  Given algorithm looks for a value in a nondecreasing sequenc

Given algorithm looks for a value in a nondecreasing sequence and returns the index of the value if it is found or 0 if it is not found.

  Designing asp.net assignment

Design an ASP.NET assignment with Visual Studio.NET 2005. Create an event handler that assigns the entry in the TextBox control to Label control's Text property when the consumer clicks the Button control.

  Describe the alternatives that were considered during stage

Were CASE or object-oriented design techniques used for the project?

  Write operations for binary file operations

C++: templates, char arrays and their null terminated representation, sizeof operator, seekp, seekg, read and write operations for binary file operations, eof() function, proper opening and closing of files with different arguments, code to proces..

  Use sequential search algortithm to locate the number

These numbers should be stored in an array. Use the sequential search algortithm to locate the number entered by the user. If the number is in the array, the program should display a message.

  Describe how to implement a simple parser

Describe the algorithm, write pseudo code or optionally implement the solution. Describe how to implement a simple parser that does well-formedness checking on XML, such as the following: x, y and z.

  Please be sure to detail specific relationships of muscle

Please read this articles both are critical for the discussion on the topic on Effects of Pronation on Knee Injury Risk.

  Calculate the cost of installing fiber optic cable

Write a program that will calculate the cost of installing fiber optic cable at a cost of $0.87 per foot for a company. Your program should display the company name and the total cost

  Write efficient backtracking algorithm to inputs integers

Write efficient backtracking algorithm which inputs the integer N, and outputs all of the ways which a group of ascending positive numbers can be summed to N.

  Show steps needed to look up ann arbor using binary search

Show steps needed to look up Ann Arbor using binary search on the following list: Ann Arbor, Berkeley, Cambridge, Eugene, Madison, New Haven, Pasadena, Santa Cruz, Stony Brook, Westwood, and Yellow Springs.

  Part 1 mining information from sales data is a common task

part 1 mining information from sales data is a common task in both traditional and online stores. past sales

  Create a data flow diagram of the current system

Create a data flow diagram of the current system. Create a system flowchart of the existing system. Analyze the internal control weaknesses in the system.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd