-
Average Monthly Salary
Write a query to display the average, monthly salary bill for each job type within a department.
-
Department Wise Average Salary
How to query the average salary for all the departments employing more than 3 people?
abhinav.abinav
- Jan 13th, 2010
Select deptno,avg(sal),count(*) from emp group by deptno having count(*)>3;
-
I have a table with a column city. The values are “delhi” and “hyderabad”. I need to update Hyderabad with delhi and delhi with Hyderabad. Define a query for that
emraan
- Dec 31st, 2009
Update e set a = case a when 'city' then 'hyd' when 'hyd' then 'city' endperfect and testedImran Baig+91 - 9247519684
sharmasl
- Mar 5th, 2009
Update States set city= decode(city,`Delhi`,`Hyderabad`,`Hyderabad`,`Delhi`,city);By using this query we can?inter change the?citys.Thanks,Salil Sharma
-
Difference between decode and case.in which case we are using case and in which case we are using decode?with an example.
Sirajuddin84
- Dec 21st, 2009
The main problem with DECODE is that we can compare only discrete values and not ranged values. So they introduced the CASE statements from oracle 8i onwards. This CASE was able to handle ranged value...
joyfun23
- Nov 17th, 2009
select 'RAGHAV' from dual where dummy =(select CASE when 'A' ='A'then 'X' else 'Y'end from dual) CASE Works in where clause and Decode too!!
-
Co-Related Sub Query
What are the disadvantages of co-related sub query?
sure_prince
- Dec 10th, 2009
-> It is another way of performing queries upon the data with a simulation of Joins.-> In This the information from the outer Select Statement Pratcipates As a Condition in the Inner Select Statement.-> Depending on the Supplied value the condition is qualified for the specific Record.
-
Display time with sysdate
How to display time with sysdate?
-
Ref Cursors
How many ref cursors can be dynamically opened in stored procedures for a session?
kondapallikalyan
- Nov 24th, 2009
We have two types of ref cursors1. Week typed2. Strong typedEx: Type <Ref cursor Name> is ref cursor /* this is week typed*/Ex:Type <Ref cursor Name> is ref cursor return /* this is strong type
sunraajkumar
- Jun 10th, 2009
more then one query open same cursor name at same time (use unlimited query)
-
SQL Soundx
What is soundx in SQL?
sure_prince
- Aug 14th, 2009
This is Soundex Function is useful to select the data according to the sound of
the given value but not compare with the casing of that letters.For Example:
SQL> SELECT * FROM emp WHERE Soundex(Ename)=Soundex('Analyst');
It select all the Analyst details in emp table in anuy case...sunraajkumar
- Jul 1st, 2009
Soundex function is used to find the phonetic representation of stringSyntax----------Soundex(CHAR)Query--------SELECT Empname FROM Employee WHERE SOUNDEX(EMPNAME)=SOUNDEX('SMYTHE')
-
On index why u need indexing ? where that is stroed and what u mean by schema object?for what purpose we are using view
jaspreet.nagra
- Jul 14th, 2009
Sometime the special index you get automatically with every primary key and unique constraint might not help in certain situations. If this happens then maybe it's time to explicitly create a seco...
SateeshReddy
- Oct 9th, 2006
Hello frnd,why cant we create an index on the index....i think according to the database concepts we can create it. it may called as multilevel indexes...like B trees and B+trees are examples for that...
-
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.
-
SELECT Statement in CASE
How to use select statement in Case?
sunraajkumar
- Jun 3rd, 2009
Example sample query: if then else conditionSELECT empno, ename,
job, sal,
CASE job WHEN 'manager' THEN sal *0.10
WHEN 'analyst' THEN sal *0.20
WHEN 'president' THEN sal *0.30
ELSE sal END "Revised Salary" from emp; -
How you will avoid your query from using indexes?
SELECT * FROM empWhere emp_no+' '=12345;i.e you have to concatenate the column name with space within codes in the where condition.SELECT /*+ FULL(a) */ ename, emp_no from empwhere emp_no=1234;i.e using HINTS
jyothi_rayala
- Jun 11th, 2009
One way is to diable the index on the column using syntaxAlter index indexname on table_name disable.One way is to apply /*+no_index */ hint to the query For eg: select ename /*+no_index */from empwhere ename ='smith'
Srini
- Oct 10th, 2007
Its so distressing that no body could provide a better answer than a novice like me.In the where clause change the order of conditions in such a way that:? 5 = value cond1 & value =5or value + 1 = 5
-
How to compare the total salary of all employee with a individual employee salary for a particular department.
sumanta.bala
- Jun 5th, 2009
SELECT ename,deptno,sal,TRUNC (AVG (sal) OVER (PARTITION BY deptno)) avg_sal FROM scott.emp
-
Why output give error message when i write select stmt, ROWNUM=2 in where clause.?
vinoth.jeevanandam
- Mar 3rd, 2009
You cannot use =, >, >= on rownum operator. Instead you can have,select employee_id from (select row_number() over(order by employee_id asc) as rn, employee_id from employees) where rn=2;select employee_id from (select rownum as rn, employee_id from employees) where rn=2;
krishnaindia2007
- Jun 22nd, 2008
We should not use >, >=, = with rownum.It do not show any error message but returns 0 rows.Here select ename,job,salfrom emp where rownum = 2;Rownum always assigns rownum 1 to first ...
-
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 ...
-
How to add positive and negative numbers in a single column and output them to two different columns
-
Retrieve Names from Table
How to retrieve names from table starting with a particular character as a first letter, using with substring, instring without using LIKE function.
askmahantesh
- Nov 26th, 2008
Consider the table EMPLOYEES with the column ENAME. Try with the following queries.Select ename from employeeswhere Instr(ename,'&A') = 1group by ename;'A' --> your starting cha...
-
Date Conversions
How to convert any kind of date that comes in any format from the user to 'yyyymmdd' in Oracle?
todeepakmalhotra
- Nov 24th, 2008
To convert a date in a specified format you need to?convert it into character..select to_char(hire_date, 'yyyymmdd') date from employees;
-
Print number of columns and column names
How to print the number of columns in a particular table also the column names without using JDBC Result set meta data using pure SQL code.
nareannl
- Oct 17th, 2008
Select count(column_name),column_name from user_tab_columns where table_name='tablename';
-
Differentiate between TRUNCATE and DELETE
TRUNCATE deletes much faster than DELETETRUNCATEDELETEIt is a DDL statementIt is a DML statementIt is a one way trip,cannot ROLLBACKOne can RollbackDoesn't have selective features (where clause)HasDoesn't fire database triggersDoesIt requires disabling of referential constraints.Does not require
amarparash
- Sep 27th, 2008
DELETE is a DML command so it works on row level, where TRUNCATE is a DDL command, so it works on object level, TRUNCATE actually deletes the table and recreates it, so whatever people say that it works faster or frees occupied space or high water mark or oil mark.The actual internal work was that.
Shaveta Chawla
- Sep 7th, 2007
One main freature is when we have used auto increment clause the would return to next value after delete statement. e.g: if id =1000then after delete statement when we insert any record the value will be 1001 but if we use truncate statement then auto increment value will come to 0 positon
Oracle SQL Interview Questions
Ans