-
Highest Average Salary
Construct a query which finds the job with the highest average salary
Shubham kumar
- Oct 21st, 2021
Hope, This Will Help
Codetaaznyonker
- Mar 31st, 2010
If you want to get only highest average salary of particular dept, then you can use SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno and if want the dept number too, with the average sal, then query ould...
-
Pluks and Zinks
There is a table with pluks and zinks. Each pluk only resides on a single zink and each zink may contain multiple pluks. How do you find how many pluks are on each and every zink?
-
Get 10 and 11th records in the output using SQL in 5 ways ?
I have to tables A and B. My records in this table are in such a way that
Table A Table B
10 15
11 16
12 10
13 11
Question :- I want only the 10 and 11 records as my output.
Can anyone help me out ( 5 ways... -
How will you delete duplicating rows from a base table?
Delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name); ordelete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);
thirupathireddy
- Feb 2nd, 2010
DELETE FROM table_name a WHERE rowid > (SELECT min(rowid) FROM table_name b WHERE a.column_name=b.column_name)
syam sundar
- Aug 18th, 2007
Delete table_name t1where rowid>(select rowid from table_name t2)and t1.column1=t2.column1
-
Display Odd/ Even number of records
Odd number of records:select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);Output:-135Even number of records:select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)Output:-246
RAMESH
- Oct 12th, 2011
FOR ODD:- "sql SELECT * FROM (SELECT E.*, ROWNUM rn FROM departments E) WHERE MOD (rn, 2) = 1; FOR EVEN:- "sql SELECT * ...
-
Hey there SQL MASTERS, here are some questions i was posed in an interview/assessment thing, dunno how well i did....Coud u answer these questions? i dont know how easy/difficut they are for u, all i know is that i had SERIOUS trouble with them! thanks alot!EMPLOYEEEmployee_id number NOT NULL (Primary Key)Employee_name VarChar(30) NOT NULL Dept_id number NOT NULL (Foreign Key to DEPARTMENT)Manager_id number NOT NULL (Foreign Key to EMPLOYMENT)Salary number NOT NULLDate_of_Birth date NOT NULLDEPARTMENTDept_id number NOT NULL (Primary Key)Dept_name (varchar30) NOT NULLAbove is the info given...Following are some of the questions posed....i.e we had to write the code using SELECT, UPDATE, INSERT etc...1. Update the salary to double its current value for every employee who worksin the COMPUTER department or has the string MC at the start of their name.2. Add your details (John Smith for example)to the EMPLOYEE table with an Employee_id one higher than the exsisting highest. Your Salary is $100,000 and you will work in the accounts department under \'Jed Teolut\' (Hint: Embed Subqueries in the values clause).3. List the name of each employee and the name of his/her department... I thought it was this.... am i right?SELECT Employee_name, Dept_idFROM EMPLOYEEORDER BY 2, 3;4. List names of employees in PERSONNEL dept whose salarie exceed $20,000a) using a joinb) sub-query (\'in\' or \'any\' clause)c) Using a correlated (exists clause)5. Update the salary of each manager to be double the average salary of the employees he/she manages (assume manager have a manager_id of NULL sub-query)6. List the highest salary, the lowest salry, the average salary and the total salaries of employees.
ieddy
- Jan 7th, 2009
#5 ??Update x Set x.Salary = (Select? avg(e.salary)*2 'Salary' --, m.Employee_id --, m.Employee_Name,m.salary??? From (employee m join employee e on m.employee_id = e.manager_id and m.manager_id...
diana
- Oct 26th, 2007
#5 -UPDATE xSET salary = ( SELECT AVG(a.salary)*2 FROM employees a JOIN employees b ON ...
-
What is the difference between ROWNUM and ROWID
sampra
- Feb 26th, 2008
the ROWNUM is a number(like serial no) which is dynamically changes.but the ROWID does not change.Because it is having the unique address,which is generated by the oracle
g_sidhu
- Feb 7th, 2008
Rowid: Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn. Rownum: For each row returned by ...
-
How to select the all first rows which are having same id.for example:id name salary11. suresh 1000011. suresh 1200012. rajesh 1200012. rajesh 13000now i want to get rows as below given format11. suresh 1000012. rajesh 12000
nis1981
- Aug 10th, 2008
Select t1.id, t1.name, t1.salfrom test t1, test t2where t1.id = t2.idand t1.sal < t2.sal;
-
Data stored in Database
How data is stored in database?
anjali
- Jul 28th, 2011
All data are stored in o and 1 format means binary form
-
How to retrieve duplicate rows/records in a table without using DISTINCT and GROUP BY clause?
May
- Apr 28th, 2021
It clearly mentioned "without using group by".
fire_starter
- Oct 9th, 2011
You can use union A union result will eliminate the duplicate rows ( for info, if one wants the duplicate rows preserved, that the UNION ALL should be used). For example we have a table called test_...
-
Which datatype is used for storing graphics and images?
LONG RAW data type is used for storing BLOB's (binary large objects).
-
How to select the recently UPDATED records from the table?
Ayyappa
- Aug 24th, 2012
Hello leelakrishna302 ... Your query ...
gives only latest inserted records not the latest updated records..Code -
What is the basic difference in function and store procedure.where we use function and where we need to use Store Procedure.could you please explain me in brief.
sampra
- Mar 6th, 2012
Procedure wont retrun any value but it can return many values on condition. but function always returns a values
shravanam
- Oct 28th, 2007
A procedure is used to perform a specific task. It can also return a value but its not mandatory.But a function is used to perform some calculations and it is mandatory to return atleast one value.
-
How we can get the salary whose is earning more than one person and also i want his name also to display...Ex:-If there is 5 members are there who is earning more than the JonesOutput I want is:Jones 2500 hari 2600Jones 2500 king 5000Jones 2500 rakesh 6000Jones 2500 rajesh 6500Jones 2500 ramesh 7000
Imran_Javed
- May 9th, 2008
SELECT e.ename || ' '|| e.sal || ' ' || e1.ename || ' '|| e1.salFROM EMP E , emp e1WHERE e.sal < e1.sal
venkat2182
- Aug 9th, 2007
Hi....I hope u know about "self-join". The following query will give the above result.SELECT e1.name,e1.salary,e2.name,e2.salary FROM emp_test e1,emp_test e2WHERE e1.name='Jones' FRO...
-
-
What is the result ofselect * from table1, table2there is a common column between them but no where caluse has been specified
jaspreet.nagra
- Jul 14th, 2009
Assume you have following two tables:SQL> select * from aa;DAT NAME---- --------1 Amit2 JasSQL&g...
dmadhusudhanarao
- Aug 2nd, 2006
assume that 2 table are emp,deptnow write query like thisselect * from emp,deptthe out put is emp has 14 records and dept has 4 recordsthen output is 14x4=56 records you will simply.
-
When we retrieve data from table in SQL Queries, and want to select second, third,........rows from table, then what is the coding of that program?
sau326
- Jul 19th, 2010
SELECT TOP 2 employeenumber FROM employees WHERE employeenumber NOT IN(SELECT TOP 1 employeenumber FROM employees)Regards,Saurabh
Imran_Javed
- May 9th, 2008
SELECT *FROM (SELECT empno, ename, sal , rank() over(ORDER BY empno) e FROM emp)WHERE e IN (2,3);
-
-
How to find number of user in the database.
Kannan
- Jan 6th, 2007
Sybase:sp_who will help to find the available users.....select * from sysusers - it used to find the no.of users in ur DB
-
How to delete redundant data from a table & How do we come to know that data or rows is being duplicated with same datavalue in Table.
hindol_saha
- Mar 27th, 2007
Using Self join like.Delete from emp where id in (select id from emp a,emp b &nbs...
madhuk17
- Feb 18th, 2007
Delete from emp a where rowid not in(select min(rowid) from emp b where a.empno=b.empno);
Oracle SQL Interview Questions
Ans