-
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...
-
Can you use a reference cursor as an input parameter in a procedure with out declaring it explicitly?
gwilson5
- Sep 4th, 2010
A cursor variable is a variable that points to or refernces an underlying cursor.? Unlike an explicit cursor, which names the PL/SQL work area for the result set, a cursor variable is a reference to ...
-
Analytical Function
WE using Last_value()analytical function but the result do not match according to me?
Raj_P
- Sep 1st, 2010
I am working on Oracle 9i and as far as I know the function "Last_Value" doesnt work in 9i. We are using the "First_Value" function with order by clause using descending order. Select First_Value(FieldName) Over (Partition by FieldName Order by TrandDate Desc) From .
Avashisth
- Feb 20th, 2010
Analytical function are the builtin function in Oracle, so during execution it will be faster then other simple function. These function are basically used for reporting and most of the time to improv...
-
Column Level Constraint
What are Column Level Constraint and Table Level Constraint? How do they differ from one another?
katgeektalk
- Aug 22nd, 2010
You can create the constarin at either the column or table level. Constrain defined at the column level are included when the column is defined. Table-level constrains are defined at the end of the ta...
hindol_saha
- Jan 20th, 2010
The NOT NULL constraint can be define only at column level,It enforces that a value must be define for this column such tht the column may not be NULL for any row in any circumstance.
-
How can we find the number of day's between any two given date's by using the dual table only?
saishradha
- Aug 13th, 2010
Below is the correct query according to me..
select to_date('19-aug-2010', 'dd-mon-yyyy') - to_date('10-aug-2010', 'dd-mon-yyyy') as numberofdays from dual;
Let me know if it does not work..
Sairam..
vikas_lomate
- Aug 13th, 2010
Use below query to find number of days between two given dates -SQL> select to_date('30-aug-2010','dd-mon-rrrr') - to_date('20-aug-2010','dd-mon-rrrr') num_days from dual;NUM_DAYS
----------
10
SQL>
-
HAVING vs WHERE Clause
Where clause restricts rows, what does having clause restricts ?1. only group results.2. rows results.3. both rows and groups result.
Read Best Answer
Editorial / Best Answer
ajaysinghnegi- Member Since Mar-2009 | Mar 27th, 2009
1) HAVING clause can only be used to filter out the aggegate functions whereas WHERE clause cannot filter out the aggegate functions.
2) HAVING clause can be used with GROUP BY function where as WHERE clause cannot be used with group by function.gubba123
- Aug 8th, 2010
Example: Write a query to display the Managers who are working for more than one Departments:Select emp_id, count(distinct dept_id) From emp Where...
Tartauris
- Feb 16th, 2010
Having is used for agregate results while where is used for unagregated results.
-
SQL Queries- Say for Eg.Two Tables - EMP and DeptEMP Tables consist of Empno, Deptno, Mgrid, Empname, LocationDept Table Consist of Deptno, Deptname.1. Write a query to select Department name for which there is more that 20 employees in a specific Department. List the deptname and no of employees.Deptno in Emp table is the primary key for deptno column in Dept Table.2. Write a query to select Manager name for each employee. MGRID column in the emp table is the empid of the Manager. There should be self join query on the emp table.
bhagyashree010
- Jul 20th, 2010
SELECT COUNT(e.ename), d.dname FROM emp e, dept d WHERE d.deptno=e.deptnoGROUP BY d.dname HAVING COUNT(e.ename) > 20It is the same...only diff is...use "dname" in group by clause , not "deptno"Thanks
isha123
- Aug 9th, 2006
Select e.empno "Subordinate",e.ename "Emp Name",e.mgrID "ManagerID",m.ename "Manager Name",m.empno "Employee Number" from emp e JOIN emp m where (e.mgrid=m.empno);
-
When we retrieve data from table in SQL Queries, and want to select second, third,........rows from table, then what is the coding of that program?
sau326
- Jul 19th, 2010
SELECT TOP 2 employeenumber FROM employees WHERE employeenumber NOT IN(SELECT TOP 1 employeenumber FROM employees)Regards,Saurabh
Imran_Javed
- May 9th, 2008
SELECT *FROM (SELECT empno, ename, sal , rank() over(ORDER BY empno) e FROM emp)WHERE e IN (2,3);
-
Write a query to update third column such that
There is a table having the following columns :-student id marks1 marks2 maxmarks1 10 20 202 25 30 303 30 10 304 35 25 355 20 40 40write a query to update column maxmarks such that maxmarks column contains whatever be the greater value among marks1...
indra.prasad
- Jul 12th, 2010
CREATE TABLE aa(name varchar(10), marks1 int, marks2 int, maxmarks int);INSERT INTO aa VALUES ('a',10,20,null);INSERT INTO aa VALUES ('b',20,25,null);UPDATE aa SET maxmarks= CASE WHEN marks1 > marks2 THEN marks1 ELSE marks2 END;
Palash Biswas
- May 20th, 2010
Declare @tbl_Test TABLE([Name] Nvarchar(255),Mark1 int,mark2 int,maxmark int) Insert into @tbl_Test([Name] ,Mark1 ,mark2) VALUES ('a',50,20) Insert into @tbl_Test([Name] ,Mark1 ,mark2) VALUES ('b',10,...
-
SQL Buffer
Which command displays the SQL command in the SQL buffer, and then executes it?
ajitsankpal
- Jun 29th, 2010
RUN is the command which displays the query in SQL buffer and executes it.
-
Change Default Display
How to change the default display? For deptno 10 show "Financial Department", for deptno 20 show "Account Department", for deptno 30 show "Management Information System", for deptno 40 show "Electronic Data Processing"
kiran_marla
- Jun 20th, 2010
Use decoding to display names.select decode(deptno,10,'Financial Department',20,'Account Department',30,'Management Information System',40,'Electronic Data Processing'...
Kushy14
- Jun 19th, 2010
SELECT DECODE (deptno,10,'Financial Department',20,'Account Department',30,'Management Information System',40,'Electronic Data Processing') FROM dept;
-
How do i get the dates of a particular month using the SQL query statement?
rashok1983
- May 24th, 2010
SELECT TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1,rownum,TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum from all_objectsWHERE TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum <= TO_DATE('12/30/2003', 'MM/DD/YYYY')
MANU
- May 18th, 2006
Hi,You can use date functions available to get the dates of a particular monthEx: SELECT col1 FROM TABLE A WHERE TO_CHAR(DATECOLUMN,'MONYY') = 'JUN05'ORSELECT * FROM TABLE A WHERE TO_CHAR(DATECOLUMN,'MON') = 'FEB'Hope this was useful.....
-
Display Time in SQL
How to display current system time in SQL.
g.sreemayee
- May 19th, 2010
select to_char(sysdate,'HH24:MM:SS') from dual;select current_timestamp from dual;These two will give the current time of the system.1. ll give the time only.2. ll give the time with the date.
-
Average Department Salary
How to Get the employee details whose salary is less than the average salary of the department.
dj_dj_dj
- May 20th, 2010
select emp_name, avg(salary)from employees group by emp_namehaving avg(salary) < (Select avg(salary) &...
g.sreemayee
- May 19th, 2010
SELECT * FROM emp e WHERE sal> (SELECT AVG(sal) FROM emp GROUP BY deptno HAVING deptno=e.deptno) ORDER BY deptno;
-
Intent Shared Lock
Which one of the following is descriptive of the effect of an Intent Shared Lock on other transactions? CHOICE1: A Read Lock that prevents writes CHOICE2: A Read Lock that prevents other reads CHOICE3: A Read Lock that prevents more restrictive Locks CHOICE4: A Read Lock that allows other reads CHOICE5: A Read Lock that allows writes (Dirty Reads)
manimehra905
- May 18th, 2010
Read lock that allows other read as it has been made the shared lock intentionally.
hmounir
- May 3rd, 2010
A Read Lock that allows other reads Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control.No other transactions can modify the data whi...
-
SQL Database Name Length
What could be the maximum size of a database name in MSQL?
vinodsahani
- May 18th, 2010
Max Length in MySql Database 64 Table 64 Column 64 Index 64 Stored Function or Procedure 64 Trigger 64 View 64 Event 64 Tablespace 64 Log File Group 64 Alias 255
-
TOAD
What does Toad mean? How it's work?
vinodsahani
- May 18th, 2010
Toad is a freeware, powerful, low-overhead tool used by database designer and application developers for faster and easier and simplifies day-to-day administration of various relational databases.
Waseem Mehmood
- May 7th, 2010
It is just a Tool that uses for writing database statements and PL/SQL.
-
There is a string 120000 12 0 .125 , how you will find the position of the decimal place?
INSTR('120000 12 0 .125',1,'.')output 13
-
Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?
SELECT customer_no, payments from customer C1WHERE 3<=(SELECT COUNT(*) from customer C2WHERE C1.payment <= C2.payment)
ankitagujrati28
- May 3rd, 2010
Select rownum,cust_no,payments from (select cust_no,payments from customerwhere rownum<4order by payments desc);
thirupathireddy
- Feb 2nd, 2010
SELECT rownum "rank", customer_no, payments FROM (SELECT customer_no, paymentsFROM customer ORDER BY payments DESC)WHERE rownum <4;
-
What is a OUTER JOIN?
Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they don’t satisfy the join condition.
ankitagujrati28
- May 3rd, 2010
OUTER JOIN is the way to collect records in case of lack of true join condition.
patilpravin_1981
- Jan 29th, 2008
outer join is that type of jon which retrives the matach & alson unmatached recores also from the tables
Oracle SQL Interview Questions
Read Best Answer
Ans