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.