-
View - Base Tables
Does View occupy memory? If we delete the base table of existing view, What happens?
Read Best Answer
Editorial / Best Answer
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
- 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
- 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...
-
Write sql query to get the below required output
I have table with col1 values , col2 1st row value should be same col1 1st row, then col 2 row onwards row each should added each row values eg: col1 values 10,20,30. Etc, in col2 values display like 10, 30,60 etc. how we can write sql query ?
roshan.gwr
- Mar 13th, 2015
Using Lag we can get it.
Select col1,col1+lag(col1,1,0) over (order by col1) col2 from table;Vinod Kumar G M
- Nov 7th, 2014
SELECT COL_NAME , SUM(COL_NAME) OVER ( ORDER BY COL_NAME) FROM TABLE_NAME
-
-
What are different Oracle database objects?
TABLESVIEWSINDEXESSYNONYMSSEQUENCESTABLESPACES etc
suresh
- Mar 13th, 2015
Table,view,procedure,function,package,synonums,triggers,index,sequencess
-
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 the difference b/w ORACLE and SQL
..........
sampra
- Mar 6th, 2012
SQL is a language to insert update delete the record where oracle is a database
vijaypati
- Mar 3rd, 2010
SQL stands for Structured Query Language. SQL is used for accessing and manipulating databases. SQL can execute queries against a database. SQL can Retrive, insert, update & delete from ...
-
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!!
-
Replace and Translate
How and in what way REPLACE is different from TRANSLATE?
zahar
- Jun 10th, 2011
REPLACE : replace function is: replace( string1, string_to_replace, [ replacement_string ] )string1 is the string to replace a sequence of characters with another set of characters.string_to_repl...
sandip88
- Feb 9th, 2010
Translate does character by character substitution,where replace can substitute a singele character with a word
-
Which is more faster - IN or EXISTS?
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.
rohitosu
- Mar 9th, 2012
As a rule of thumb IN should be used when the sub query will return small result set. It should be kept in mind that the sub query should not return null values. EXISTS should be used when the result set returns large result set. Other factors that might affect query performance is the indexes
Vineet Arya
- Sep 17th, 2011
Which is more faster - IN or EXISTS? It totally depends upon the query. For some queries Exist is efficient and for some IN is efficient Exits is correlated sub query, in which the outer query run f...
-
Create a query that displays the employees' last names and commision amounts.If an employee does not earn commmission, put "No Commission." Label the column COMM.
pollysingh544
- Feb 22nd, 2010
Select last name,decode(commision,0,'No Commision',commision) comm from employee
-
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
-
Find manager for employee
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- 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.EMPNOpoojamohan
- Sep 25th, 2012
How to get the old manager name ,if the employee has been changed to other manager?
-
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...
-
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,...
-
Avoid Data Duplication
If in a table we dont use primary key or any other unique key then how to avoid duplication of data?
-
What are various constraints used in SQL?
NULLNOT NULLCHECKDEFAULT
-
What is a pseudo column. Give some examples?
It is a column that is not an actual column in the table.eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.
Amol Kulkarni
- Dec 3rd, 2016
Pseudo columns are used to pass additional information on a row. A pseudo column is not actually part of a row like any other columns. but exists due to the row property, they cannot be updated or mod...
-
Suppose there are two fields in table(Employee) say Name and Salary and there are in total 100 records in table. Now my query gives the name of an employee whose salary is 10th among 100 salaries. this query should be solve in SQL not in PL/SQL
abhikum.402
- Aug 13th, 2012
Select emp_name from (select *,Dense_RANK()over (order by Emp_sal) SalayRank from Emp_a)A
where SalayRank=10 -
Can we use aggregate function in another aggregate functionsuppose we got count of a column by using the group by clause so we get different counts in the same statement how to get the maximum of count
gkbiswal
- Sep 29th, 2007
We can use aggregate function inside another aggregate function.Example:SQL> select max(Count(*)) from emp group by deptno;MAX(COUNT(*))------------- 6DB Version: Oracle9i Enterprise Edition Release 9.0.1.1.1
Read Best Answer
Ans