-
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
-
What is CoRelated SubQuery
A subquery within a subquery
rjk1203
- Sep 19th, 2008
A Correlated Subquery?which executes?with value returned by the child Query. Unlike subquery it executes per result from child query.Ex: removing duplicate rows from a table Subquery executes?once for the entire child?query's result
-
Display deptno wise first three lowest salaries in ascending order of deptno and desc order of salary
vishnu vardhan reddy
- Sep 16th, 2008
select e.* from( select enmae,sal, dense_rank() over(partition by deptno order by sal) ranksal from emp)e where e.ra...
hiasat
- Aug 4th, 2008
SELECT empno,ename,salFROM ( select empno,ename,sal from emp order by sal asc )WHERE rownum
-
What is a self-join and when can it be used
krishnaindia2007
- Jun 22nd, 2008
Join the table to itself is called self join. It is used when you need to join the data with the same table data.Ex:- SELECT A.ENAME,A.SAL ,B.ENAME,B.SAL FROM EMP A,EMP B WHERE A.MGR = B.EMPNO
-
How to delete a duplicate records in a table without using rowid
Praveen2384
- Sep 9th, 2008
Delete from table_name where rownum in (select min(rownum) from table_name group by column2,column3);where column2 and column3 are repeating columns.
Karthik
- Mar 14th, 2007
Hi,If you have primary key Constrains in the tableFollowing is query will select the duplicate ----------------------------------------Select * from X a, X bWhere a.1 != b.1 ( Primary Key) And a 2 = b...
-
Base table of View table
How do we view the query used while creating a View table? i.e How to find the dependencies of a view table.
sinhasaurabh014
- Sep 4th, 2008
If you have toad, you can see everything. Just navigate to view throgh schema browser and check under the tabs "view". select your view and check all the properties from it.
vermapradeep1980
- Aug 30th, 2008
Try this one.. select text from user_view where view_name ='viewname'pradeep
-
Oracle Insert statement execution
Explain the internal process and steps how SQL statement is executed in Database ?
ananth.dikshit
- Aug 27th, 2008
In Order to execute any SQL statement first we need to start an oracle instance. for that we need to connect to any tool(SQL* Plus) and that will start?a user process. This user Process will contact ...
gpsaravanan202
- Dec 12th, 2007
The execution flow of sql statement is internally done by means of internal cursor.these are steps followed in executing the statementParsebindexecutefetchclose cursor....More details can be studied in oracle supplied packages concept...
-
Redundancy of data is many times benificial, Justify the statement.
artbria
- Aug 25th, 2008
A table is said to be redundant (that is data is repeated) if it is in a denormalized format. For ex, we have a table, which displays item, item no, item cost etc. which is denormalized. To avoid redu...
patilpravin_1981
- Jan 29th, 2008
A person can have same name in database therfore it is difficult to identify the records thats why DBMS provide data integrity to enter valid data & unique data enter into database. which is use to avoid data redudancey in database.
-
How many null value a unique key constraint can have?
I think there can be only one null value for a unique key constraint but some say there can be more than one null value.
ammupriyaa
- Aug 22nd, 2008
Can have any number of NULL values, since oracle?maintains internally different values for each?NULL values.
-
Ampersand in SQL Output
How to display & in Oracle SQL output
sailajabetha
- Aug 18th, 2008
Use concatenate operator ...SQL> select 'a'||'&'||'b' from dual;'A'---a&b
-
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;
-
Having following table named RATING :EmpID,ReviewSeason,CustID1,s1,c11,s1,c21,s2,c32,s2,c22,s3,c43,s3,c5Write a SQL statement, return EmpID who has been REVIEWED more than once.
Harshita
- Apr 18th, 2006
Select empID from RATING group by ReviewSeason having count(empID)>1
-
Select 1 from dualunionselect 'A' from dual; I guess the query will select zero rows. Is it wrong or right ? if wrong thenWhat will be the result set for the query?Thanks in advance.
nis1981
- Aug 3rd, 2008
Inorder to use UNION SET OPERATOR the datatype and the number of columns should match. Here you are trying to get the union of a character datatype and a number, hence you get an error.
Renuka Rajput
- Jul 29th, 2006
i would like to add one more point that not only datatypes but also structure of both the select statement should be same.if we write query asselect 1,2 from dualunionselect 1 from dualit will still results in error and the error isexpression must have same datatype as corresponding expression
-
What is the Hirerachy of SQL?
Oracle SQL Interview Questions
Ans