I need to obtain the top 2 departments in terms of total salary.
I have the following tables (Dno corresponds to DeptNo). I've found tutorials for obtaining the salaries of the top three employees, and summing the salaries for each dept. is easy, but I can't seem to combine the two.
DNo Fname Salary
2 Tom 10000
3 Mike 20000
2 Harry 30000
DeptNo DeptName
1 Administration
2 Special
3 Finance
I am building a search for use in one of our major applications. I have written a PL/SQL package that deals with it. I would like to present the requirement list to the group and see what, if anything, you may have done differently than I have.
1.) The search interface must have a single box, like google.
2.) Multiple search terms will be separated by a comma.
3.) The table has the following columns:
-- Name
-- Title
-- addr
-- addr2
-- city
-- state
-- zip
-- phone
-- email
4.) Number of Search Terms per query will be unlimited. (for now, as practicality dictates)
5.) Each search term will be checked against various columns.
6.) Search terms must not have a preference in order. Name, Address = Address, Name
7.) Records will be returned only for the rows where all search terms are found.
Tablespace freespace query which will give output in terms of maxsize and not size allocated ?
View 14 Replies View Relatedwhen in sqlplus I execute:
select * from mytable;
in sqlplus I get the returned rows of this table. But this is not the case when I do in plsql the following:
create or replace procedure myproc is
a varchar2(50);
begin
a := 'select * from mytable';
execute immediate a;
end;
there is nothing I can see. Is there a way to catch this result set and see it on the screen? Something that is not creating new structures like cursors, etc?
How do I get my list of user session info? I thought there was a user_session_parameter view or something? Basically, I did an ALTER SESSION ... and want to verify it was set correctly.
View 8 Replies View Related i backup my database using rman into my external disk using this command
RMAN> run
2> {
3> backup database format 'H:ackupkp_%u' tag='sh';
4> backup archivelog ALL format 'H:ackuparc_%u' Delete input;
5> }
Starting backup at 06-NOV-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=F:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF
[code]...
I'm attempting to use dynamic SQL to execute a function that returns a user-defined collection. The problem is that I don't know how to use dynamic SQL to handle user-defined types...or if this is even possible?
The code I have is as follows:
CREATE OR REPLACE PACKAGE qi_test IS
TYPE typ_qi_data IS RECORD(
iQIFlag NUMBER(1),
iIPFlag NUMBER(1),
iRiskIndicator NUMBER(1),
iDenominator NUMBER(8),
iNumerator NUMBER(8)
[code]........
I want to be able to execute the above function using dynamic SQL. Initially tried:
DECLARE
f2_data qi_test.typ_qi_data_tab;
BEGIN
EXECUTE IMMEDIATE 'begin :1 := qi_test.get_f2_data; end;'
USING OUT f2_data;
[code]......
...but this just produces "PLS-00457: expressions have to be of SQL types". So it looks like I can't do it this way if the returned data type is user defined. I know it would be easier in this instance to just use something like:
f2_data := qi_test.get_f2_data;
...rather than EXECUTE IMMEDIATE, but it's the principle that I need to get right as it forms part of a much bigger piece of work.
Is there a way to view/obtain the password once a database link has been created. Some sys dba table?
View 7 Replies View RelatedI want to backup all archive logs,but it raise error,why?
run{
allocate channel dup type disk;
sql 'alter system archive log current';
backup format '/u01/app/oracle/duplicate/al_t%t_s%s_p%p' archivelog all;
release channel dup;
}
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 02/13/2012 05:04:04
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /u01/app/oracle/duplicate/1_33_769179320.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
I need to configure rman backup via oracle enterprise manager.Database is not in archive mode, rman shutdown the database and made a "startup mount" for backup.
I set up a backup on disk and the test is successful When I plan the backup job, database shutdown but the backup is not working and after database is not started.
here is the log of the backup job :
shutdown immediate;
SP2-0640: Not connected
-> ->Connected
Database closed
Database dismounted.
ORACLE Instance shut down.
startup mount
[code]....
Select * from one:
ID SALARY
----------------------
10 1000
20 2000
30 3200
Select * from two:
ID SALARY
----------------------
10 1000
20 2000
40 4000
10 3000
20 4000
30 3000
When i try to find ID,MAX(SALARY) from these two tables, i am getting this output:
Select id,max(salary)
from
(select * from one
union
select * from two)
group by id
order by max(salary) desc;
OUTPUT:
ID MAX(SALARY)
--------------------------------------
20 4000
40 4000
30 3200
10 3000
I want OUTPUT to be like this:
ID MAX(SALARY)
--------------------------------------
20 4000
40 4000
I'm running a query like the below but now i would like to make the last line actually say Grand Total. Instead of just total.
SELECT decode (grouping (farinva_invh_Code),0,null,'Total') farinva_invh_Code,
--decode ( grouping (amt),0,null,'GrantTotal')Grant_Total,
farinva_invh_Code,
spriden_id,
--spriden_last_name "last Name"
[Code]....
How does the following underlined query works to find the nth highest salary
SELECT DISTINCT(A.SALARY) FROM EMPLOYEE A
WHERE &SALARY = (SELECT COUNT(DISTINCT(B.SALARY)) FROM EMPLOYEE B
WHERE A.SALARY<=B.SALARY);
A.Create object to get the salary hike of an employee on the basis of completion of years in an organization.
Conditions:-
Create two tables with name as "Employee_yourname" and "Employee_Hike_Yourname".
"Employee_yourname" can have columns like EMPID, NAME, SALARY, DATE_OF_JOIN.
"Employee_Hike_Yourname" will have 3 more columns for new salary, Current salary and completed period in organization.
Consider the employees who have completed minimum one year in organization.
Salary hike for employee with tenure >= 1yr and < 2yrs should be 10% of current salary.
If tenure is greater than or equal to two years and less than 3 years then salary hike should be 20% of current salary.
For employees having tenure more than 3 years hike should be 30% % of current salary
I want to display the maximum sum of salary among sum of salary of each department.
deptno sal
10 1000
10 500
10 100
20 2000
20 200
30 500
30 1500
30 2000
30 200
Sum of salary for each department.
10 1600
20 2200
30 4200
The output should be
30 4200
Because this the highest sum of salary compare to sum of salary of reaming departments.
how to find the nth highest salary
View 11 Replies View Relatedi was given a task to find the second highest employee sal from emp table.
View 5 Replies View RelatedI created a PROCEDURE in that i am calling function which calculate sum of salary...I just want Output in format for that which function i need to use...?
Actual Output:::
DEPt_Name SALARY
ACCOUNTING 8750
RESEARCH 10875
SALES 11600
I want Output in well alignment column...i WANT Output IN column format but my output in not geting in that format...Is there any function to align output...I want Output in well alignment column
I have a created data block using employees table so am trying to validate item salary using max_salary and min_salary from table jobs how to do this kind of validation.
[How can i validate input number into a Field of type char in oracle form?]
I have to write function that receives department name and an aggregation operation (average, maximum, minimum) and apply the operation on the salary of employees working on the given department and return the result.
here is my select statement:
select distinct d.deptno, d.deptname, max(e.salary)
from employee e join department d
on e.deptno=d.deptno
where d.deptname=upper('finance')
group by d.deptno, d.deptname;
[code]...
in this query, i am stuck in this little query but cant get answer lets suppose
select * from emp;
EMPNO ENAME JOB SAL
---------------------- ---------- --------- ----------------------
7788 SCOTT ANALYST 3000
7902 FORD ANALYST 3000
7876 ADAMS CLERK 1100
7934 MILLER CLERK 1300
7900 JAMES CLERK 950
[Code]....
now if i want to see min salary takers group by job then i use
select x.job, min(x.sal), count(*) from emp x group by x.job;
JOB MIN(X.SAL) COUNT(*)
--------- ---------------------- ----------------------
CLERK1 800 1
SALESMAN 1000 5
CLERK 950 3
PRESIDENT 5000 1
MANAGER 2450 5
Developer 2975 1
ANALYST 3000 2
The above result give me minimum salary but total number of JOB holders, You can see only one SALEMAN getting 1000 but count show total number of SALESMAN. Similarly 3 MANAGERS are getting minimum and same salary but count show total number of MANAGERS.
My question is how can i get number of person on min salary?
Possibly my data should be like as this
JOB MIN(X.SAL) COUNT(*)
--------- ---------------------- ----------------------
CLERK1 800 1
SALESMAN 1000 1
CLERK 950 1
PRESIDENT 5000 1
MANAGER 2450 3
Developer 2975 1
ANALYST 3000 2
I wanted to know the latest salary(last 2 updated records ) getting by emp- id 252 based on below mentioned information
source desti sal emp id MGR
1-Jan 1-Feb 1000 252 venkat
2-Jan 2-Feb 4000 252 venkat
2-Feb 2-Feb 5000 252 venkat
i have table structure (emp_no, emp_sal,emp_startwork)
i want query to show the monthly salary such as jan month salary Feb month salary
write a query to see how many(no) employees getting salary 3000 & what are their names respectively.
View 2 Replies View RelatedTo find all the employees whose salaries greater than avg(salary) of the department.
Quote
select empname,salary,deptid from salaries t1 where
salary > (select avg(salary) from salaries t2 where t1.deptid = t2.deptid);
Unquote
Its not diplaying all departments
i have employee table i want to update salary with all employee 5 percent
View 4 Replies View RelatedHow do i check in Table B whether it is converted correctly into words taking input or reference from table A
Consider below example:-
Table A Table B
$125 Dollar One Hundred twenty Five only
$45,542 Dollar Forty Five Thousand Five Forty Two Only
$145.56 Dollar One Forty Five and fifty six cents Only
$145,253
$35,256.65
$560,250.67
/* Formatted on 2013/08/11 18:46 (Formatter Plus v4.8.8) */
CREATE PROCEDURE p_get_name (
p_empno IN OUT NUMBER,
p_name OUT VARCHAR2,
p_err OUT NUMBER
[code].......
Note:- I want to print ename and salary of emp using empno as a input but i dont want to declare extra variable for salary , i want to print salary using empno but when i execute this procedure. It gives value of empno in salary. Don't Know Why , how can i print salary of emp using empno as input without declaring extra variable for salary.
based on the following information
grade lowsal highsal
------ ----- ------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
for the employee table to assign grade for each employee based on his salary the following plsql procedure is giving error:
-----------------------------------------------------------
CREATE OR REPLACE PROCEDURE GRADE(EID IN NUMBER,BONUS OUT NUMBER) IS
vGRADE NUMBER(8,2);
vSAL NUMBER(8,2);
BEGIN
vGRADE=1
SELECT SAL INTO vSAL FROM EMP WHERE EMPNO=EMPID;
IF vSAL<= 700 THEN
vGRADE:=1;
ELSEIF vSAL<= 1201 THEN
[code]....
My example: I'm given an Allowance throughout the week. It happens to be 10 dollars but it can vary from day to day.I can create a running total with SUM(Amt) Over etc...This is the CUMUL column in the example below.
On certain days I've spent different percentages of the allowance. (The SPENT Column which is a field in the database)I can't manage to create the AMTLEFT column in the example below.The AmtLeft column seems to be a kind of running total that 'refers to itself' so this is where I'm stumped.
Week,Day,Amt,Cumul,Spent,AmtLeft
1,Mon,10,10,0%,10
1,Tue,10,20,50%,10
1,Wed,10,30,0%,20
1,Thu,10,40,0%,30
[code]...
My imaginary SQL would look something like at this point (if I have it right):
SELECT Week, Day, Amt, Sum(Amt) Over (Partition By Week, Order By Day) AS Cumul, Spent FROM AllowancesTable
How to get the last column AmtLeft?