-
ALTER Row
We are UPDATING a field in SQL and ALTER the row also. After giving the COMMIT command the system is crashed. What will happen to the commands given, whether it will UPDATE and ALTER the table or not?
neha.awasthi1
- May 30th, 2011
Since alter command is DDL and DDl are self commit command so the changes will appear.
Raj_P
- Sep 1st, 2010
It depends on the sequence of the transactions. 1) DML -----> DDL -----> Crash = You will get the data.2) DDL -----> DML -----> Crash = You will lost the data.
-
SQL Syntax for deleting duplicate records in a table
jayshree13
- May 11th, 2011
Delete from emp e where rowid<(select max(rowid) from emp m where e.name=m.ename);
dj_dj_dj
- Mar 18th, 2010
Basically deleting duplicate records while creating table sounds inefficiency of the SQL DBA.To avoid duplication one can provide the column datatype as Unique or Primary key.........Which is the best method upto my knowledge to prevent duplication entry of data.RegardsDharmendra Jaiswal.
-
In SQL. (DB2)there is one Dept table and other Emp tableQuestion- select Emp Name from Emp whose Dept No. is not in Dept table and it is present in Emp table Plz tell the Query?
jayshree13
- May 13th, 2011
Select e.ename,e.deptno,d.deptno from emp e,dept d where e.deptno=d.deptno(+);
-
Consider that we r having table name emp....now the question is 1.how should we print the odd rows from that table?2.how should we print the rows where comm=NULL.Reply ASAP
jayshree13
- May 13th, 2011
Ans:1. select * from emp where (rowid,1) in (select rowid,mod(rownum,2) from emp);Ans:2: select * from emp where comm is null;
vishnu vardhan reddy
- Sep 16th, 2008
Select e.* from( select rownum rn ,ename,sal from emp) e where mod(rn,2)=0
-
Can I retrieve column name using the column number of that column in the table? If yes then how?
Santhoshkandula
- May 11th, 2011
No, It's not possible because select statement only selects the values. Thanks & Regards K.Santhosh
RK
- Apr 24th, 2006
FOR EXAMPLE TO RETRIEVE 2ND COLUMN(ENAME) IN EMP TABLE:SELECT COLUMN_NAME FROM USER_TAB_COLUMNSWHERE TABLE_NAME ='EMP'AND COLUMN_ID = 2
-
What are various joins used while writing SUBQUERIES?
Self join-Its a join foreign key of a table references the same table. Outer Join--Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don't satisfy the join condition.Equi-join--Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table.
Santhoshkandula
- May 11th, 2011
There are 5 types of joins. Those are 1. Equi Join / Inner Join 2. Non-Equi Join 3. Outer Join ( Left Outer Join, Right Outer Join, Full Outer Join ) 4. Self Join 5. Cross Join
-
Changing Rows into Colums
How to change rows to columns and columns into rows?
Santhoshkandula
- May 11th, 2011
create table #temptable(rowid int,colorname varchar(25),Hexa varchar(7),R tinyint,G tinyint,B tinyint) GO insert into #temptable values(1,'Violet','#8B00FF',139,0,255); insert into #te...
-
Can we call a function in sql query and what are the pre requisites to call a function in sql query?
Read Best Answer
Editorial / Best Answer
Answered by: Jayakumar M
- Nov 8th, 2005
Yes. We can call Functions from SQL statements.
To be callable from SQL statements, a stored function must obey the following
"purity" rules, which are meant to control side effects:
When called from a SELECT statement or a parallelized INSERT, UPDATE, orDELETE
statement, the function cannot modify any database tables.
When called from an INSERT, UPDATE, or DELETE statement, the functioncannot query or modify any database tables modified by that statement.
When called from a SELECT, INSERT, UPDATE, or DELETE statement, thefunction cannot execute SQL transaction control statements (such as
COMMIT),session control statements (such as
SET ROLE), or system control statements(such as
ALTER SYSTEM). Also, it cannot execute DDL statements (such asCREAT
E) because they are followed by an automatic commit.planet100
- Apr 19th, 2011
I have a question... what if the function returns long??i am not able to use in the select query.It says " ORA-00997: illegal use of LONG datatype " .Is there any other way to address this, ...
nis1981
- Aug 3rd, 2008
Yes, you can call function in sql query, these functions should be in-built or predefined in the DBMS.
-
Find Base Table of the View
Views are created on the basis of base tablesWhat is the query to know the base table of the view? How to know the details of base table?
Prem_p
- Apr 28th, 2011
Guys,?you don't need a dba privilege for this.Run the query:select * from USER_DEPENDENCIES where ?type='VIEW' and name=''This will give you the dependent object information (name , type of the obje...
Balakrishna12207
- Feb 7th, 2011
SELECT * FROM dba_views WHERE View_name=;Text column can contain "The details of view and table"
-
Matrix Query
Create a query to display the total number of employees, of that total, the number of employees hired in 1995, 1996, 1997, 1998. Create appropriate column heading from the employees table.
Prem_p
- Apr 28th, 2011
SELECT count(e.empno) Emp_Count,to_char(e.hiredate,'YYYY') hired_On from empoyee ewhere to_char(e.hiredate,'YYYY') in (1995, 1996, 1997, 1998)group by rollup(to_char(e.hiredate,'YYYY')) order by 2
mlodugu
- Apr 10th, 2011
How about this?SELECT SUM(sal) Total_sal, extract(year from hiredate) YrFROM scott.emp GROUP BY Rollup(extract(year from hiredate))
-
How you will avoid duplicating records in a query?
By using DISTINCT
kethinenisarath
- Apr 9th, 2011
Hi! I think its best option to avoid duplicating records in a queryselect * from empwhere sal in ( select sal from emp &nbs...
-
Produce a List of 5 Highest Revenue Transactions
You need to produce a list of the five highest revenue transactions from the Orders table in the Sales database. The Orders table is defined as follows:CREATE TABLE Orders ( OrderID Int IDENTITY NOT NULL, SalesPersonID Int NOT NULL, RegionID Int NOT NULL, OrderDate Datetime NOT NULL, OrderAmount Int NOT NULL ) Which statement will produce the report correctly?...
kethinenisarath
- Mar 8th, 2011
A List of 5 Highest Revenue Transactions select * from (SELECT OrderID, SalesPersonID, RegionID, OrderDate, OrderAmount, dense_rank() over(partition by ORDERID order by OrderAmount desc) "...
krishsidd
- Nov 25th, 2010
insert into orders values(101,12301,11,sysdate,1000); insert into orders values(102,12302,12,sysdate,500); insert into orders values(103,12303,13,sysdate,1700); insert into orders values(104,12304,...
-
How to add new column in the existing table at our desired location?
panwar_5
- Jan 8th, 2011
This can be done eaisly using the keyword after,suppose you want to add a column "name" after the field "id".Alter table emp add name varchar2 after id
Ramkiran_79
- Aug 6th, 2010
You cannot have user defined syntaxes in Oracle, still it is implemented, following error will be thrown. 1* alter table employee add (age number(10)) after enameSQL> /alter table employee add (a...
-
Write query for the following questions1. sql query for selecting alternate rows in a table2. sql query for deleting alternate rows in a table
yagneswarasankar
- Dec 2nd, 2010
SQL> select last_name,salary,rn from (select b.*,rownum rn from (select last_name,salary from employees)b) where mod(rn,2) = 0;LAST_NAME &...
kowmudiswarna
- Sep 25th, 2007
select*from emp where (rowid,1) in(select rowid,mod(rownum,2) from emp);
-
In a table a column is having value like 'Steven Kovy'. without using concat() function and concatenate operator "||" how to display like 'Kovy Steven'
krishsidd
- Nov 26th, 2010
Space included in name in answer submitted by ashishkumar2403
select Rpad(rpad(Substr('Steven Kovy',INSTR('Steven Kovy',' ')+1), length(Substr('Steven Kovy',INSTR('Steven Kovy',' ')+1))+1,' ' ), length('Steven Kovy'),substr('Steven Kovy',1,instr('Steven Kovy',' '))) xfrom dual;yagneswarasankar
- Nov 23rd, 2010
1* select rpad(lpad('Steven Kovy',(length('Steven Kovy') +1),''''),length('Steven Kovy')+2,'''') Name from dualSQL> /NAME-------------'Steven Kovy'
-
List all the employees who have atleast one person reporting to themList the employee details if and only if more than 10 employees are presently in department 10
krishsidd
- Nov 26th, 2010
CREATE TABLE EMP ( EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL...
yagneswarasankar
- Nov 24th, 2010
1 select manager_id,last_name,count(employee_id) over (partition by manager_id ) cnt from employe 2* order by cntSQL> /MANAGER_ID LAST_NAME &nb...
-
How to give select privilege on all the objects owned by an user(say user1) to another user say(user2) using a single sql statement?
yagneswarasankar
- Nov 24th, 2010
SQL> conn sample/sample;Connected.SQL> select count(1) from girija.employees;select count(1) from girija.employees &n...
-
SQL
What are the data types in oracle?
MahanamShakyamuni
- Nov 19th, 2010
In SQL, there are mainly varchar2, char, number, date...then each of them has subtypes like number has integer, real etc..Following website gives all details:For SQL datatypes refer:http://download....
pradeepnagaraja
- Nov 18th, 2010
Different data type used in Oracle are number, char, varchar, varchar2, clob, blob, long.
-
Data Conversion
Have 2 table Emp1 (Column Name: Ename), 2nd table Emp2 (Column Name: First and Column Name: LastTable Name :Emp1ENameHunt, MichaelGraham, SmithNelson, Ryan Table Name: Emp2First LastMichael HuntSmith GrahamRyan NelsonNeed to write a SQL query to convert the data from Emp1 to Emp2 in this way.Can any body help me.Thanks
yagneswarasankar
- Nov 16th, 2010
SQL> select * from name;NAME--------------------Girija,Sankar 1* create table split_name as(select substr(name,1,(instr(name,',') -1)) "First",substr(name,(instSQL> /Tabl...
katgeektalk
- Aug 22nd, 2010
SELECT substr(ename instr(ename, ',')+2) First ,substr(ename, 1, instr(ename, ',')-1) Last FROM emp1;has to be +2 because of the space after comma
-
Enlarge Work Area Size
How to enlarge the size of work area in iSQL*plus?
viji_info
- Oct 22nd, 2010
If you are working isql plus..Go to the preference which lay on the right hand corner, then Edit the column width and height as much you want... Try it.
Oracle SQL Interview Questions
Read Best Answer
Ans