If your answer is yes, explain the steps.

gkbiswal

 Profile Answers by gkbiswal Questions by gkbiswal

• Sep 29th, 2007

Yes we can.create or replace trigger trg_testbefore insert on empbeginnull;end;create synonym trg_syn for trg_test;

Answer Question  Select Best Answer

What are Pair Wise & Non-Pair Wise Columns?

Vinod Tuteja

• Jun 6th, 2013

NON PAIRWISE COMPARISON SUBQUERY: "sql SELECT employee_id, manager_id, department_id FROM employees WHERE manager_id IN (SELECT manager_id FROM employ...

Answer Question  Select Best Answer

ambika

• Oct 21st, 2013

Recovery in centralized dbms

su4surya

 Profile Answers by su4surya Questions by su4surya

• Aug 24th, 2009

A centralized database has all its data at one place so there may occur problems of data avalability, and a system crash may lead to whole dataloss. In a distributed database, database is stored on se...

Answer Question  Select Best Answer

What is difference between nested query,sub query and nested query? explain clearly with an example?

Read Best Answer

Editorial / Best Answer

arpitapatel  

 Profile Answers by arpitapatel Questions by arpitapatel

• Member Since Sep-2008 | Sep 24th, 2008

---

Query inside the query is nested query.
It is also called as sub query.
Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

rams

• Apr 3rd, 2012

Both are same

katgeektalk

 Profile Answers by katgeektalk Questions by katgeektalk

• Aug 22nd, 2010

The subquerry is often reffered to as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally excuted first, and its output is used to complete the query condition for the main (or outer) query.

Improve Answer

How to convert rows in column using SQL in Oracle;
for example
sl_no
1
2
3
4
output is 1234;

How to convert one column value into rows using SQL
for example
employee_name
Kumar

output
K
u
m
a
r

Megamind

 Profile Answers by Megamind Questions by Megamind

• Apr 14th, 2014

We can do it via pivot keyword..... it will change the row into column.. its simply a pseudo column

Kandavel Durairaj

• Nov 8th, 2013

Using LISTAGG. Read the oracle documentation for more info.

Answer Question  Select Best Answer

Hi guys try this query

If any student fails in one subject also his result should be Faile r else Pass
condtion is(marks>=40 "PASS" marks

nancy

• Jul 19th, 2016

Code
 

SOnali

• Jun 29th, 2016

Select Name,
when Min(Mark)>40
Then Pass
Else Fail
End
From TableName
Group By Name

Answer Question  Select Best Answer

Construct a query which finds the job with the highest average salary

Shubham kumar

• Oct 21st, 2021

Hope, This Will Help

Code
 

taaznyonker

 Profile Answers by taaznyonker Questions by taaznyonker

• 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...

Answer Question  Select Best Answer

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

 Profile Answers by thirupathireddy Questions by 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

Answer Question  Select Best Answer

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, or

DELETE statement, the function cannot modify any database tables.

 When called from an INSERT, UPDATE, or DELETE statement, the function

cannot query or modify any database tables modified by that statement.

 When called from a SELECT, INSERT, UPDATE, or DELETE statement, the

function 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 as

CREATE) because they are followed by an automatic commit.

planet100

 Profile Answers by planet100 Questions by 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

 Profile Answers by nis1981 Questions by nis1981

• Aug 3rd, 2008

Yes, you can call function in sql query, these functions should be in-built or predefined in the DBMS.

Improve Answer

krishsidd

 Profile Answers by krishsidd Questions by 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

 Profile Answers by yagneswarasankar Questions by 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...

Answer Question  Select Best Answer

The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters

srinivas

• Feb 6th, 2016

How can I differentiate local veritable and formal parameter? In class I have variable called a, in that class one method has formal parameter is "a" and another local variable also "a".

Ravindra Kumar

• Dec 7th, 2015

The actual parameter is when you actually pass real value in calling that function. And, the formal parameter is the parameter as defined on the function signature.

Answer Question  Select Best Answer

Does View occupy memory? If we delete the base table of existing view, What happens?

Read Best Answer

Editorial / Best Answer

Mad Hatter  

 Profile Answers by Mad Hatter Questions by Mad Hatter

• Member Since Nov-2008 | Nov 4th, 2008

---

View does not occupy memory, but Oracle keeps view code in its dictionary (you can check in user_views, for example)
If you delete the base table, the view becomes INVALID.
In order to make it valid again, you have to ether build the deleted table again, or create anotehr view with the same name and structure as deleted table, or create synonym with the same name as deleted table, which will point to the table with the same structure.

pkshve

 Profile Answers by pkshve Questions by pkshve

• Sep 12th, 2010

Views are database objects - they are just SQL code stored in database which will be executed every single time the view is called. Database will check the existance of columns and tables in the base ...

manimehra905

 Profile Answers by manimehra905 Questions by manimehra905

• May 18th, 2010

View does not occupy memory as it only provides a way for watching multiple views of an object: Table. Moreover, it is a virtual table which does not take space.It provides DATA HIDING.For e.g. i...

Improve Answer

ID employee department manager----------------------------------------------1 Suresh c++ NULL 2 Suresh c++ NUll3 Suresh c++ 25 Sarathy testing 26 Rajaraman c# 17 joe Flash 1I have the employee detail table ,In that i want to find the manager for each employee .The employee...

Read Best Answer

Editorial / Best Answer

mktiwary  

 Profile Answers by mktiwary Questions by mktiwary

• Member Since Jun-2008 | Jun 6th, 2008

---

If you take the normal Oracle Employee (emp) table which consists of columns such as Employee Number (EMPNO) and Manager Number (MGR) then the query to find managers for all the employee is:

select e.ename as Employee, m.ename as Manager from emp e, emp m
where e.MGR = m.EMPNO

Nivetha Thinakaran

 Profile Answers by Nivetha Thinakaran Questions by Nivetha Thinakaran

• May 6th, 2015

Code
 

poojamohan

 Profile Answers by poojamohan Questions by poojamohan

• Sep 25th, 2012

How to get the old manager name ,if the employee has been changed to other manager?

Improve Answer

If in a table we dont use primary key or any other unique key then how to avoid duplication of data?

PremG

• Aug 10th, 2015

Yes, by creating Unique index on the respective column we can avoid duplicate values into that particular column. But, with this NULL values can not be restricted.

prakash

• Aug 6th, 2015

By creating unique index it is possible

Answer Question  Select Best Answer

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.

Read Best Answer

Editorial / Best Answer

Answered by: Vijay Raj Jaiswal

• Sep 20th, 2005

---

Hi this is modified answer ,pls post this instead of previous which i post

Primay Key                     Unique key

-----------                  ------------------

There is only one         there may be more than 1
Primary key for           Unique Key in table
1 table

It can not contain           It Can contain Null Value
Null value

Lakshmanan

• Jun 30th, 2017

Primary key is combination of not null and unique. Primary key does not insert a null value and insert value is unique. Unique is one time accepted null value and insert a data is uniquely

ankitagujrati28

 Profile Answers by ankitagujrati28 Questions by ankitagujrati28

• Apr 30th, 2010

Unique key means we have to enter unique value but we can enter null.But in case of Primary key we should have to enter Unique + Not Null value in that particular column.

Improve Answer

SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.

qptopm

 Profile Answers by qptopm Questions by qptopm

• May 12th, 2016

PL/SQL provides the capability of the programming language and supports the above mentioned list. It also supports running of SQL statements inside the PL/SQL blocks. SQL provides access to the database for retrieving data from the objects. However SQL does not have the programming capabilities.

Das

• Nov 4th, 2015

Simply put, like a command prompt in Windows Operating system, we have SQL PLUS tool in Oracle DBA.

Answer Question  Select Best Answer

VIJAI VEDHA

• Sep 28th, 2022

ITS syn NOT sys,

SuryaRS

 Profile Answers by SuryaRS Questions by SuryaRS

• Aug 29th, 2017

TAB will give tables, views whereas CAT will give tables, views, sequences. TAB is having clustered column.

Code
 

Answer Question  Select Best Answer

Bobby

• Apr 18th, 2018

Rowid is pseudo column and it can change internally especially on transactional table

himabindu

• Apr 16th, 2018

Why use rownum < 2?

Answer Question  Select Best Answer

'' Should be used before '%'.

Nazeera Jaffar

• Oct 6th, 2012

The below code selects the column with a substring % (e.g.) reg%istration

Code
 

Nazeera Jaffar

• Sep 30th, 2012

The below code will select the row having column as %

Code
 

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Srinivas4u  

 Profile Answers by Srinivas4u Questions by Srinivas4u

• Member Since Jul-2008 | Jul 16th, 2008

---

I am sure that a group by clause can have all columns which are there in that table,not sure if you are looking for anotehr answer

akshya dash

• Jul 25th, 2011

there can be n no of columns in a group by clause other than group functions for ex select ename, deptno max(sal) from emp group by ename,deptno; here max(sal) is a group function and ename and ...

sasanka

• Jul 21st, 2011

In GroupBy clause contains the no of columns in the select Statement to retrieve the data.

Improve Answer