Oracle SQL Interview Questions
Difference between nested query,sub query and nested query?
What is difference between nested query,sub query and nested query? Explain clearly with an example?
Read Best Answer
Editorial / Best Answer
Answered by: arpitapatel
View all answers by arpitapatel
Member Since Sep-2008 | Answered On : 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.
Both are same
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.
How many columns can be in the group by clause
Read Best Answer
Editorial / Best Answer
Answered by: Srinivas4u
View all answers by Srinivas4u
Member Since Jul-2008 | Answered On : 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
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 ...
In GroupBy clause contains the no of columns in the select Statement to retrieve the data.
Is there any way we can change the column name in a table
Read Best Answer
Editorial / Best Answer
Answered by: Praveena
Answered On : Dec 14th, 2005Yes we can alter Column name in a table using the following query: alter
Regards,
Praveena
u can change the column name and also column definition that is whether the data type and its size by the ALTER command.
ALTER TABLE
yes, it's possible to change the column name by using the following query in SQL server
SP_RENAME ' <TABLE_NAME.OLD_COLUMN_NAME> ' , ' NEW_COLUMN_NAME '
Thanks & Regards
K.Santhosh
Does view occupy memory? If we delete the base table of existing view, what happens?
Read Best Answer
Editorial / Best Answer
Answered by: Mad Hatter
View all answers by Mad Hatter
Member Since Nov-2008 | Answered On : 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.
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 ...
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...
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
Answered by: ajaysinghnegi
View all answers by ajaysinghnegi
Member Since Mar-2009 | Answered On : 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.
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...
Having is used for agregate results while where is used for unagregated results.
What is difference between unique and primary key constraints?
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
Answered On : Sep 20th, 2005Hi 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
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.
Primay Key Unique key----------- ...
User Panel
Categories
Interview Coaching
Ads
Connect
Ads
Interview Question
Latest Questions
Ads
Interview & Career Tips
Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.
NH
Editorial / Best Answer
Answered by: Mohan
Answered On : Jun 2nd, 2006Hi,
Advantages of views:
1. View the data without storing the data into the object.
2. Restict the view of a table i.e. can hide some of columns in the tables.
3. Join two or more tables and show it as one object to user.
4. Restict the access of a table so that nobody can insert the rows into the table.
Disadvatages:
1. Can not use DML operations on this.
2. When table is dropped view becomes inactive.. it depends on the table objects.
3. It is an object, so it occupies space.
Pls. add , if I miss any of them.
Thanks,
Mohan
View when called always contacts the base table, on which it is built to get the data .so it always goes to the server thats why it degrades the performance of the server.
DML operation can performed on views.