PL/SQL :: Using A Subquery To Display Names And Max Salaries By Department / HR Schema
Mar 14, 2013
Using the HR schema, I'm trying to write a query which will display the names and salaries of those employees who earn the highest salaries in their respective departments.
I wrote the query:
SELECT first_name, last_name, salary
FROM employees
WHERE salary =
(SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);
However, this query does not execute correctly but I'm not sure why?
I used Scott schema.I take 2 blocks ,one block having Deptno,Job and also 1st block is a non database block and Another Block Empno,ename,job,sal,deptno, is a database block,my question is when i enter Deptno,Job then Display Employees Details and also Department name
Anand basic Anand pascal Juliana cobol Kamala dbase Mary oracle Mary c Partick c++ Qadir assembly Qadir c Ramesh dbase Remitha c Remitha assembly Revathi pascal Revathi basic
Write a script to display the first and last names of the targets that have id less than 3.
DECLARE fname targets.first_name%TYPE; lname targets.last_name%TYPE; CURSOR id IS SELECT target_id FROM targets; BEGIN FOR val IN id LOOP IF val < 3 THEN
[code]....
I tried using without %TYPE but could not get the result.
TAB1 with data no name 1 abc 2 abc 3 xyz 4 xyz 5 cvb
now I would like to create a form with name counts as
abc 2 xyz 2 cvb 1
I would like to create a datablock to display all the unique names, not sure where I should be writing the query to display unique names. I would like to create a textitem box to display the counts when the form is compiled, but not sure which trigger to use to write the query.
My aim is to display the missing itemnames for each itemcode.
For each ITEM_CODE there should be four items i.e Pen,Pencil,Scale and Marker If any itemcode missed any of the itemnames those records should display in the output.
So the output should be like this
101 Marker Because the item Marker is missed. 102 Pencil Because the items Pencil and Marker are missed. 102 Marker Because the items Pencil and Marker are missed.
For itemcode 103 no records should be displayed because it having all four itemnames.
I have 20 tables. In all 20 tables, some of column names are same and some are different. I need to find all column names in all 20 tables that have same names.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/******** schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
I have employee interface table something like this.
emp_idemp_name Job_title supervisor_name 1AJ Engineer BJ 2CK Analyst ND 3BJ Manager TR 5TR VP IT JD 6ND S Manager MD 7MD VP Telecom SK 8SK VP Eng JR
I want to idenitfy the VP for each employee. The logic I have to apply is check for hte supervisor of each employee to see if the supervisor has designation starting with 'VP'. If no, I have check the supervisor of the supervisor and so on. I tried using a recursive query using connect_by_root but in the above example for employee ND it lists the VP as both MD and SK. I need it to show on MD who is the lower in the hierarchy.
I am a Java person but since my app uses the Oracle DB I am to do this task.
I am trying to track an employee's department changes by adding a Department change counter which is set to 1 whenever the department changes otherwise it is set to a 0. How can I accomplish this update of the counter? Below is the table creation sql and sample data for insert.
CREATE TABLE EMPL_DEPT (EMPLID VARCHAR2(11) NOT NULL, EFFDT DATE, EFFSEQ SMALLINT NOT NULL, EMPL_RCD SMALLINT NOT NULL, DEPT_ID VARCHAR2(12) NOT NULL, [code]..........
I WANT to RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT.is any OTHER WAY to DO THIS due to performance in Oracle 10g
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]...
I would like to create a hierarchical tree with Department number as a node and all the employees (only employee names) under that department as shown below:
[-]Department - 10 KING JAMES ----- [-]Department - 20 MARY | | and so on...
I have created a hierarchical tree 'HT_DEPTNO' under block 'BL_EMP'. I also created a Record Group 'RG_HTREE' with query as shown below:
SELECT 1, LEVEL, E.ENAME, D.DEPTNO||' - '||D.DNAME DEPARTMENT, D.DEPTNO FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO START WITH E.DEPTNO = 10 CONNECT BY PRIOR E.EMPNO=E.MGR
Department EmpName ____________________________________ Sales empname1 Sales empname2 Marketing empname3 Development empname4
Now I want to count the number of employees in every department. I want the output to be
Department Total ______________________ Sales 2 Marketing 1 Development 1
I am retrieving names of the department through a subquery
The query I am trying to execute is:
SELECT Department, Employee FROM ( SELECT ...query from other table) AS Department, count( A.EmpName) AS Employee FROM Employer A, EmployeeInfo B WHERE (A.EmpID = B.EmpID AND A.EmpCategory like 'Category2') GROUP BY Department
I know that you cannot group by using aliases and hence a little work around, but still the query isn't working.
I have an Oracle 10g database, on the App Serv I have an image file that has 20,000 .jpg files that has an id number as each image name.I have successfully queryed the image file and posted one image to my web page matching the image id number. sample:
the &1 is the matching id number that is input from the user.My task now is to select multiple images using a department field in the spriden table to pull the needed id numbers.I have not been successful in the proper format to pass the id number to the <img src field.
A single master schema where many developers are accessing. all share same password.
now i would like to trace all the changes made by each users. so i create a individual users for all and grant permission to access that schema.do i have a possibility of auditing the changes did by each user for that particular schema
We have an application with many separate databases (one per customer). Given they share the same business requirements (service hours, change mgmt etc), we're interested in potentially consolidating the separate DBs (which are relatively small) into separate schemas within a fewer no of databases to reduce the overhead.
Our issue is that the application is hard-coded to use a specific administrator and application connection user name. Changing this is unfortunately not an option.
Given this limitation, is there any possibility to map a generic user into a customer-specific schema based on the database service that they connect to? Each customer connects to different database services but may use the same user name. We considered using private synonyms but this seems to acheive the opposite (i.e. many different users could connect and map to a single users schema). One thing to point out is that where there is a single user name, it is acceptable for a single password to be used across the different customer DBs as they will be a single admin/user.
I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.
How could I do this without using export import. I am using TOAD 9.0.1.
move the tables with data present in the user scott(full) to another schema named test. In my case scott is in user tablespace and for test schema i have created different tablespace named test_tbs.
