-
How to obtain employees who earn sal greater than avg sal in the given deptment in classic emp, dept tables
Imran_Javed
- May 9th, 2008
SELECT e.EMPNO, e.ENAME, e.deptno, e. SAL, e1.avg_salFROM emp e, (SELECT AVG(sal) avg_sal, deptno FROM emp GROUP BY deptno ) e1WHERE e.deptno =e1.deptno AND e.sal >e1.avg_sal;
Yoga
- Sep 26th, 2007
Select ename, deptno, sal
from emp where sal > (select avg (sal) from emp) group by ename, deptno, sal -
How to truncate the table with cascade option?
I tried "truncate table cascadebut I got the error storage keyword is missing (says either drop storage or reuse storage) Can any one help me out.
vemurisasidhar
- Sep 23rd, 2007
If u want to delete a table with constraints i think u need to first disable the constraints on the table and the use truncate table ;alter table disable constraint primary key cascade;
-
How to avoid duplication in database if we are adding data from .dat file ?
aytasirs
- Nov 15th, 2012
There is no option to avoid duplicates when inserting data from flatfiles using sql*loader.
The best way is load the data into staging table and write the
query to eliminate the duplicate rows. -
What is the Hirerachy of SQL?
-
Redundancy of data is many times benificial, Justify the statement.
artbria
- Aug 25th, 2008
A table is said to be redundant (that is data is repeated) if it is in a denormalized format. For ex, we have a table, which displays item, item no, item cost etc. which is denormalized. To avoid redu...
patilpravin_1981
- Jan 29th, 2008
A person can have same name in database therfore it is difficult to identify the records thats why DBMS provide data integrity to enter valid data & unique data enter into database. which is use to avoid data redudancey in database.
-
How to Create a table in SQL with a table name depending on the date the code is run ?
For e.g. Create table tempOCT as Select....If i run the code in any day of Nov the table name should be tempNOV.
vishal gupta
- Nov 21st, 2007
You can do one this?in the code itself that is going to create the table at run time incorporate the logic to get the currect date,?take it as string variable and then in the code itself you can write the query?Create table as(..)Might help you.Vishal.
Waseem Mehmood
- Nov 20th, 2007
I think it is not possible, i try to concate date with table name but no vail.You can not rename the table name by cancating date
-
Oracle Insert statement execution
Explain the internal process and steps how SQL statement is executed in Database ?
ananth.dikshit
- Aug 27th, 2008
In Order to execute any SQL statement first we need to start an oracle instance. for that we need to connect to any tool(SQL* Plus) and that will start?a user process. This user Process will contact ...
gpsaravanan202
- Dec 12th, 2007
The execution flow of sql statement is internally done by means of internal cursor.these are steps followed in executing the statementParsebindexecutefetchclose cursor....More details can be studied in oracle supplied packages concept...
-
Unique Index and Simple Index
What is difference between unique index and simple index?is it possible, two rows can have the same unique index?
sampra
- Mar 6th, 2012
Index create on Primary key or unique key is Unique index where as index created on any other element is simple index
srinivasa.dinesh
- Jan 29th, 2008
Unique Index:If the index is for a primary or unique key then the owner, name, type, and columns cannot be modified. These properties are all derived from the key constraint. Changing the name of the ...
-
Copying table with constraints and data
How to copy a table with constraints and data to another table.
krishnaindia2007
- Jun 21st, 2008
Using the following query you may copy table structure and data but you can't copy constraints.Create table emp_copy as select * from emp;
ugly1
- Jan 25th, 2008
below shown query will copy table with constraints and data into new table. here table_copy is the new table and old_table is the table whose data and constraints are to be copied.create table table_copy as (select * from old_table)
-
Establishing many to many relationship in SQL
How will you establish many to many relationship in SQL?Ramco Systems dt. 02 Feb' 2008
krishnaindia2007
- May 16th, 2008
Cartesian product is an example for many to many reolationship in SQL.For exampleselect a.ename, a.job, a.sal, b.dname from emp a, dept bresults in many to many relationship.
rakeshn
- Feb 20th, 2008
EmployeeDepartmentlets say one employee can work in multiple department.It needs many to many relationshipCreate another table EmpDeptMap.it will have foreign key with employee and Deptartment too.In this table (empid+deptid) is the primary key
-
Get fieldnames from Empty Table
How to get field names from empty table by using SELECT statement;
-
Describe table without DESC Command
How to desc table data without using desc command
saradajasti
- Mar 24th, 2008
Select column_name name,data_type typefrom user_tab_columnswhere table_name =
-
Display time with sysdate
How to display time with sysdate?
-
CBO & RBO
What is CBO and RBO? What is diff between these two?
krishnaindia2007
- Jun 21st, 2008
The job of optimizer is to decide the most efficient method to execute a query. There are two types of optimizers in oracle RBO or Rule Based Optimizer since v6 CBO or Cost Based Optimizer sinc...
-
Sub query and correlated sub query
What is the difference between sub query and correlated sub query
krishnaindia2007
- Jun 20th, 2008
In sub query the inner query is executed only once. Depeding upon the results of inner query , outer query is evaluated.In correlated subquery the inner query is evaluated once for each row processed by the parent statement or outer query.
tariq_oracle
- Apr 13th, 2008
subquery: required in those cases where requisted data are not known and always depend on inner qu...
-
Alias in Joins
Why do we use aliases during joining two tables?
krishnaindia2007
- Jun 20th, 2008
Alias name it temporary name given to a tablen or columns of a table.It has its existence until the satement is executed. For example in the following example instead of typing tablenames ...
-
Arrange Date Week Wise
How to arrange date in week wise like from Monday to Sunday?
madhupriya.nukala
- Jun 26th, 2008
SELECT TO_CHAR(hiredate,'DAY') FROM emp ORDER BY to_char(hiredate-1,'D');
jannaat
- Apr 28th, 2008
Consider there is table H and you have 3 columns name week date so sql statement is select name ,week,date from H group by week order by date
-
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...
-
Base table of View table
How do we view the query used while creating a View table? i.e How to find the dependencies of a view table.
sinhasaurabh014
- Sep 4th, 2008
If you have toad, you can see everything. Just navigate to view throgh schema browser and check under the tabs "view". select your view and check all the properties from it.
vermapradeep1980
- Aug 30th, 2008
Try this one.. select text from user_view where view_name ='viewname'pradeep
-
Securing SQL Scripts
How to secure SQL scripts so that they cannot be seen by others but can only be executed.
Oracle SQL Interview Questions
Ans