By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,154 Members | 1,050 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,154 IT Pros & Developers. It's quick & easy.

i m having no idea how to do it

P: 1
i have completed these q. in sql but unable to do it in pl/sql let me help or give me path to do it




Exercises :



Q 1) Display department number and sum of the salaries of employees in each department.

select deptname,deptno,sum(sal) from emp group by deptno,deptname;
/


Q 2) Display the employee with the maximum salary

SELECT ename,sal FROM emp
wHERE sal = (SELECT MAX(sal) FROM emp);
/


Q 3) Create a table t1 with fields c11, c12, c13 all numbers, Create a table t2 with c21, c11,c23 as fields (all numbers)

such that c11 is the primary key of t1 and the foreign key in t2. insert 5 records in each table.

creating t1:-
create table t1(c11 number NOT NULL PRIMARY KEY,c12 number,c13 number);/
/

creating table t2
create table t2(t1 number,c21 number,c23 number,FOREIGN KEY (t1) REFERENCES t1(c11))
/

Q 4) After doing Q2 delete table t1.

delete from t1;

delete from t1
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.SYS_C0010480) violated - child record
found

A foreign key references is where one field "links" to another table.

The database will maintain referential integrity - this means that there must be a corresponding record in the other table - in this example each track must be on an album that exists in the album table.

The field (or fields) linked to must be unique - usually the other field is the primary key of the other table.

DROP TABLE t2;
DROP TABLE t1;


Q 5) Just like employee table emp, create another table emp1(same structure as emp). Insert 5 records same as emp and

5 different records not in emp, Then write one single select query to select records from emp and emp1 for each condition given below:



a) Repeated records are also displayed

ans:-
select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,DEPTN AME from emp
union all
select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,DEPTN AME from emp1
/


b) Repeated records are not displayed

ans:-
select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,DEPTN AME from emp
union
select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,DEPTN AME from emp1
/



Q 6) Display the department name of employee having the minimum salary.

select deptname,ename from emp where sal = (SELECT min(sal) FROM emp);



Q 7) After finishing off Questions 1 to 6 in SQLPLUS, write a procedure for each with name same as the question number.
Aug 3 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.