-
How do you create our own schema? which privileges are required?
Ajay Nerumati
- Jan 29th, 2007
Hi All,A Schema in oracle is a logical representation of all the Oracle objects owned by a user.To make my explanation simpe , let me give an example.Let's say you own a bike, a computer, mobile. Now ...
JKreddy
- Jan 24th, 2007
HI all.. To create/update schema one should have SYSADMIN privilages.. ie high level system admin privilages, And these privilages are also necessary for editing the DUAL table in the DB.. thx and regard's jk.m.reddy
-
You have EMP and dept table .how do you get the entire department and corresponding employee details and the department which are not alloted it?what is referential integrity constraint?wat is triggers,stored procedures,stored functions explain with examples
deviji2000
- Oct 15th, 2007
hi, v use joins to get details from both the tables thz kind of joins r called outer joins to get the missed statements.select e.eno, e.ename, e.deptnofrom emp e, d...
Nandakumar
- Jan 28th, 2007
Yes, you can get all the departments along with the employee details, plus the department for which no employee exists. Here you go using outerjoin, select a.employee_id, a.email, a.salary, a.departm...
-
What is the difference between NULL, NOT NULL and NVL with examples?
Prabhat Sahu
- Nov 28th, 2018
Handling NULL values: NULL: It is a value which is Unavailable / Unassigned / Unknown / Inapplicable. A NULL is not the same as "Zero" or a "Blank space". If a row lacks the data for a particular co...
jamesravid
- Jul 18th, 2007
NVL is a built in function provided by oracle which accepts two parameters. NVL function returns the first parameter if the first parameter is not null and it returns the second parameter if the...
-
Using the following Customer, Country and Orders tables: Customer Table c_id c_name c_country_id 1 John 1 Country Table country_id Name 1 US 2 UK Orders Table o_id c_id Qty o_deliver_country_id 1 1 10 1 2 1 20 2
I need the following output in SQLc_id c_name c_country_lives qty o_deliver_country 1 john US 10 US 2 john US 20 UK
Imran_Javed
- May 9th, 2008
SELECT c.c_id, c.c_name , ord.name AS "c_country_lives", ord.qty, ord.name AS "o_deliver_country"FROM customer_c c, (SELECT * &nbs...
vishalb
- Apr 25th, 2008
SELECT X.o_id, X.c_name, X.[Name], X.qty , MT.[name] FROM(SELECT OT.o_id, CT.c_name, CUT.[Name], OT.qty , OT.o_deliver_country_id --CUT.o_deliver_countryFROM Orders OT Join Customer CT on ...
-
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 ...
Oracle SQL Interview Questions
Ans