Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 8i Questions/Project
| Oracle 8i Questions/Project [message #6909] |
Sun, 11 May 2003 03:37  |
James Brown
Messages: 7
Registered: May 2003
|
Junior Member |
|
|
I'm having problems finding the solutions to the following problems.....can anybody help or point me in the correct direction?
1. Alter the DEPT table adding two columns:
NO_OF_EMP (Number of Employees) - number(2)
AVG_SAL (Average Salary) - number(7, 2)
2. Create a PL/SQL procedure that receives as parameter the name of the department, calculates the number of employees from that department and writes this values into the DEPT table, at the corresponding record.
3. Create a PL/SQL procedure that receives as parameter the name of the department, calculates the average salary for employees from that department and writes this values into the DEPT table, at the corresponding record.
4. Create a PL/SQL procedure:
increase_salary(val IN NUMBER, name IN CHAR, message OUT CHAR)
If the employee with ENAME name exists, the output parameter message will be Success, else it will be Failure.
If the employee exists, his salary will be increased with the sum val.
5. A simmilar PL/SQL procedure for the commision:
increase_comission(val IN NUMBER, name IN CHAR, message OUT CHAR)
If the employee with ENAME name exists, the output parameter message will be Success, else it will be Failure.
If the employee exists, his comission will be increased with the sum val.
6. Create a PL/SQL function that finds the value of the salary of an employee.
find_salary(name IN CHAR) return number
If the employee does not exist, the function will return 0.
7. Create a PL/SQL function that finds the value of the comission received by an employee.
find_comission(name IN CHAR) return number
If the employee does not exist or the comission is null, the function will return 0.
8. Write a trigger for the EMP table:
CREATE OR REPLACE TRIGGER EMP_MODIFY
AFTER INSERT OR UPDATE OR DELETE ON EMP
When a new employee is inserted, updated or deleted, the DEPT table must be updated: the NO_OF_EMP field must be recalculated (see questions 2 and 3).
9. Create a report that computes the minimum salary by job, having the following columns:
EMP.JOB, Heading: “Job”;
EMP.ENAME, Heading: “Employee Name”
MIN(EMP.SAL), Heading “Salary”
Also, calculate the minimum salary for all employees.
The report must be printed to the REPORT1.SQL file.
10. Create a report having the following columns:
DEPT.DNAME, Heading: “Department Name”
EMP.ENAME, Heading: “Employee Name”
EMP.JOB, Heading: “Job”
EMP.SAL, Heading: “Salary”
|
|
|
|
| Re: Oracle 8i Questions/Project [message #6919 is a reply to message #6909] |
Sun, 11 May 2003 18:04  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
James, lots of people post homework questions here. I can't speak for everyone, but I will only help when someone has actually first attempted to answer the questions. Just posting a laundry list of questions isn't going to help you learn anything - you've got to hammer away at it first. Post some code - however wrong it may be - and we'll work with you.
|
|
|
|
| Re: Oracle 9i Question [message #6920 is a reply to message #6919] |
Sun, 11 May 2003 21:17 |
Nazma Bepat
Messages: 34
Registered: May 2003
|
Member |
|
|
Todd,
I have to write a query that will display the difference between the highest and lowest course costs. Label the column “Highest and Lowest”.
I have attempted to do this, but is not working, can you help me out.
Select difference between highest and lowest cost
From course
Where column label = “Highest and Lowest”
(I agree with you, an attempt must be made in order to get help)
Thanks
Nazma
|
|
|
|
| Re: Oracle 9i Question [message #6921 is a reply to message #6920] |
Sun, 11 May 2003 23:02 |
robin baby
Messages: 13
Registered: May 2003
|
Junior Member |
|
|
what i understood from ur query ,
u have 1 field like cost and u want to find out the differece between the highest and lowest value of the field cost,if this is ur requirment u can use
SELECT MAX(cost)-MIN(cost) “Highest and Lowest”
FROM course;
|
|
|
|
| Re: Oracle 8i Questions/Project [message #6931 is a reply to message #6919] |
Mon, 12 May 2003 10:18 |
James Brown
Messages: 7
Registered: May 2003
|
Junior Member |
|
|
Thanks for the response...I've already turned in the project but wanted to understand more about what I did and did not do correctly. Especially before my final...thanks for the assistance.
Sent attachment already.
|
|
|
|
| Re: Oracle 8i Questions/Project [message #6932 is a reply to message #6919] |
Mon, 12 May 2003 10:46 |
James Brown
Messages: 7
Registered: May 2003
|
Junior Member |
|
|
Here are the codes...I just want to know where I went wrong so I can study for my final. I'm not trying to get answers to turn in without doing the work myself. I've already turned this in for grading.
1. ALTER TABLE DEPTS
ADD NO_EMP Number(2)
ADD AVG_SAL Number(7,2);
11. DECLARE
employee_name VARCHAR2(30);
employee_job_type VARCHAR2(30);
employee_salary_amount NUMBER(7,2);
BEGIN
employee_name :='STEVENS';
employee_job_type :='CLERK';
employee_salary_amount := '800';
DBMS_OUTPUT.PUT_LINE (employee_name ||' '|| 'job is' || ' '|| employee_job_type
||' '|| 'and yearly salary is' ||' '|| employee_salary_amount * 12 ||'.');
END;
/
3. CREATE OR REPLACE PROCEDURE UPDATE_AVG_SAL
(DEPT_NAME CHAR) AS
BEGIN
-- update item avg_sal
UPDATE DEPTS
SET AVG_SAL = AVG(SAL) FROM EMP
SELECT AVG(SAL)
FROM EMP
WHERE 'SALES' = '30';
COMMIT;
END;
/
4. CREATE OR REPLACE PROCEDURE INCREASE_SALARY
(VAL IN NUMBER, NAME IN CHAR, MESSAGE OUT CHAR) IS
DECLARE message VARCHAR 2(7);
BEGIN
SELECT EMP_NO
INTO INCREASE_SALARY
FROM EMP
WHERE ENAME = 'KING'
INCREASE_SALARY (1000, 'KING', message);
If message = 'Success' then
UPDATE SAL
ELSE
message := 'Failure'
END IF;
END;
/
12. DECLARE
current_square BINARY_INTEGER := 3;
current_perimeter NUMBER;
current_area NUMBER;
BEGIN
current_perimeter := 4 * current_square ;
current_area := 3 * current_square ;
DBMS_OUTPUT.PUT_LINE('For a square with side ' || TO_CHAR(current_square) ||
', the perimeter is ' || ROUND(current_perimeter , 4 ) || ' and the area is ' ||
ROUND(current_area , 3) || '.');
END;
/
6. CREATE OR REPLACE FUNCTION
find_salary (NAME IN CHAR)
RETURN NUMBER IS
salary_number
BEGIN
salary_number := NAME IN;
END;
SELECT SAL
FROM EMP
If salary_number = NULL
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
END;
/
7. CREATE OR REPLACE FUNCTION
find_commission (NAME IN CHAR)
RETURN NUMBER IS 0
BEGIN
SELECT find_commission INTO COMM FROM EMP
WHERE EMANE = NAME
IF COMM = NULL
DBMS_OUTPUT.PUT_LINE ('0');
END IF;
END;
/
8. CREATE OR REPLACE TRIGGER emp_modify
AFTER INSERT or UPDATE or DELETE ON emp
FOR EACH ROW
BEGIN
INSERT ENAME INTO emp_modify
IF :NEW.DEPT_NO IS != NULL THEN
UPDATE DEPTS
END IF;
IF :OLD.DEPT_NO IS != NULL THEN
UPDATE DEPTS
END IF;
END;
/
9. CREATE VIEW REPORT1(EMPJOB, EMPNAME, EMPSAL) AS
SELECT JOB, ENAME, SAL FROM EMP
WHERE (SAL, JOB) IN (SELECT MIN(SAL), JOB FROM EMP GROUP BY JOB);
CLEAR COLUMNS
CLEAR COMPUTE
CLEAR BREAK
TTITLE OFF
SELECT * FROM REPORT1
ORDER BY EMPJOB;
SPOOL REPORT1.SQL
COLUMN EMPJOB HEADING 'Job' FORMAT A10
COLUMN EMPNAME HEADING 'Employee Name' FORMAT A15
COLUMN EMPSAL HEADING 'Salary' FORMAT $9990.99
SET LINESIZE 45
SET PAGESIZE 20
TTITLE 'Minimum Salary by Job'
BREAK ON REPORT ON EMPJOB SKIP 1
SET FEEDBACK OFF
COMPUTE MIN OF EMPSAL ON REPORT
/
SPOOL OFF
10. CREATE VIEW REPORT2 (DEPTDNAME, EMPENAME, EMPJOB, EMPSAL ) AS
SELECT DNAME, ENAME, JOB, SAL FROM EMP,DEPTS
WHERE (SAL, DNAME) IN (SELECT SUM(SAL),DNAME FROM DEPTS GROUP BY DNAME);
CLEAR COLUMNS
CLEAR COMPUTE
TTITLE OFF
SELECT *
FROM REPORT2
ORDER BY DEPTDNAME;
SPOOL REPORT2.SQL
COLUMN DNAME HEADING 'DEPARTMENT NAME' FORMAT A18
COLUMN ENAME HEADING 'EMPLOYEE NAME' FORMAT A18
COLUMN JOB HEADING 'JOB' FORMAT A18
COLUMN SAL HEADING 'SALARY' FORMAT $9,990.99
SET LINESIZE 70
SET PAGESIZE 66
SET PAUSE OFF
TTITLE 'EMPLOYEE SALARY BY DEPT REPORT'
BREAK ON REPORT ON DNAME SKIP 1
SET FEEDBACK OFF
COMPUTE SUM OF EMPSAL ON REPORT
/
SPOOL OFF
|
|
|
|
| Re: Oracle 8i Questions/Project [message #6934 is a reply to message #6932] |
Mon, 12 May 2003 12:59 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Examples use the generic EMP and DEPT tables and respective columns:
1. Correct, although more typical syntax would be:
alter table dept
add (no_of_emp number(2), avg_sal number(7,2));
2. No answer provided.
3. Incorrect for many reasons.
create or replace procedure update_avg_sal (p_dname in dept.dname%type)
as
begin
update dept d
set avg_sal = (select avg(sal)
from emp e
where e.deptno = d.deptno)
where dname = p_dname;
commit;
end;
/
4. Incomplete/incorrect.
create or replace procedure increase_salary
(p_value in emp.sal%type, p_name in emp.ename%type, p_message out varchar2)
as
begin
update emp
set sal = sal + p_value
where ename = p_name;
if sql%rowcount = 1 then
p_message := 'Success';
else
p_message := 'Failure';
end if;
commit;
end;
/
Hope that gives you an idea of what you need to change in your approaches to these problems...
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 14:46:42 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
