|
Oracle PL/SQL |
1.
Write a procedure that has the
user enter two numbers and then calculates the sum, difference, product and
quotient of the two numbers. Create the procedure and execute it. Show the
output from the procedure.
2. Create the baseball table from the student folder
and insert the data.
a.
Write a procedure to find and
output the name of the player who has hit the most home runs and the number of
home runs he has hit.
b.
Write a procedure to find and
output the average number of hits for the players in the table.
c.
Write a procedure to show the
current age in years for the players in the table who are now alive.
d.
Run and execute the procedures
and show the output.
3. Create the planet table and insert the data.
a.
Write a procedure that allows
the user to input the name of a planet and then calculates the circumference of
the planets orbit (where circumference = 2 * p *
radius, p = 3.14159 and radius is the distance of the planet
from the sun.
b.
Run and execute the procedure
and show the output. Write a procedure that calculates the maximum or minimum
of three numbers based on the user’s input of three numbers and request for the
maximum or minimum?
4.
Write a procedure to define a
table/array to hold the numbers between one and 20. Output the even numbers,
one per line.
5. Create a table called yourlastnameemp. The table should have
five columns – firstname (varchar2), lastname (varchar2), hours (number), rate
(number(6,2), tax (number).
a.
Write a procedure to insert
data into the table, where the data is inserted when the procedure is executed.
Insert five records into the table.
b.
Write a procedure to calculate
the salary for each record in the table where salary is the hours times the
rate minus the tax.
c.
Write a procedure to calculate
the total hours, taxes and salary for all the records in the table.
6. Create a student table called yourlastnamestudent. The table should have five columns – firstname
(varchar2), lastname (varchar2), score1 (number), score2 (number), score3
(number).
a.
Insert ten records into the
table.
b.
Write a procedure to display
all the information on each student and the average score for each student.
c.
Write a procedure to display
the highest average of the averages and to display the names of the student(s)
with the highest average.
d.
Update the scores by adding
ten to each and then update the scores by setting any that are over 100 to 100.
e.
Display all the information
with the average and display all the students with the highest average.
7. Create the world tables from the student folder and
insert the data.
a.
Write a procedure to find and
output the names of the countries that have higher than average internet usage.
b. Run and execute the procedures and show the output.
8. Develop
a procedure that will generate customer reports for Eddie’s Equipment Rental.
Eddie’s rents out the following equipment for either a full-day or a half-day:
|
Eqipment ID |
Equipment |
Half-Day Price |
Full-Day Price |
|
1 |
Rug cleaner |
$16 |
$24 |
|
2 |
Lawn mower |
$12 |
$18 |
|
3 |
Paint sprayer |
$20 |
$30 |
The customer bill will also include a $30 deposit. The program should display a receipt indicating that it is from Eddie’s, the equipment, time of rental , price, deposit and total amount tendered. It should also insert into a table the date and time of the rental.
9.
Set up the planet and moon tables from the student
folder. Create a package that will do the following:
a.
Print out a list of planets in order of distance from the
sun along with their moons.
b.
Print out the distance they travel in their orbits, using
the formula c=2pr where c is the
circumference of the orbit and r is the radius of the orbit (distance from the
sun)
c.
Print out the distance the moons travel around their
planets using the above formula.
d.
Print out the density of the planets and their moons
based on the formula d=m/(4pr3/3)
where d is density, m is the mass of the object and r is its radius.
10.
Use or create the baseball table. Write a procedure that
will calculate the age at death of people in the baseball table. Include an
exception section that will let you know if the person is not yet deceased.
11. Use or
create the world tables.
a.
Write a function that creates an information systems development
index (isdi) based on the following formula: isdi= ((175-hdi)/100) + 3 *
internet users+ 2 * computers)/6. Round your results to two decimal places.
b.
Write another function that creates an electronic index
(edi) based on the following: edi:= (((175-hdi)/100)+ radio + 2 * TV +
phone_lines + 2 * Fax + 3 * cell + isdi(hdi))/10. Round to two decimal places.
Back to Oracle Page | Solutions | Back to Chris Riddiough's Home Page