How to create table emp1 based on emp, except comm column?

sampra

 Profile Answers by sampra Questions by sampra

• Mar 6th, 2012

CREATE TABLE emp1 AS (SELECT empno, ename, hiredate, s

nathsambu

 Profile Answers by nathsambu Questions by nathsambu

• Dec 19th, 2008

Create table emp1 as select * from emp;

Answer Question  Select Best Answer

How to return more than one row through functions? Explain with example.

rajivgupta780184

 Profile Answers by rajivgupta780184 Questions by rajivgupta780184

• Oct 31st, 2008

create or replace function f1 return sys_refcursor as
r1 sys_refcursor;
begin open r1 for select * from emp;
return(r1);
end;
call the function as: select f1 from dual;
Thanks & Regards
RAJIV GUPTA (ORACLE CONSULTANT)
YOGIK TECHNOLOGIES PVT LTD.

raghuprasad

 Profile Answers by raghuprasad Questions by raghuprasad

• Sep 1st, 2008

Eg: SELECT empname,sal        FROM    employee_table       WHERE sal<MAX(sal) AND sal>MIN(sal);Hope the above statement executes.

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

kuntal sarkar  

 Profile Answers by kuntal sarkar Questions by kuntal sarkar

• Member Since Feb-2008 | Feb 9th, 2008

---

x+(x+6)+(x+12)+(x+18)+(x+24)=100
or, x=8

Parthvi Bhagat

• Apr 24th, 2021

45

hitzbab

• Apr 24th, 2018

X=8+(8+6)+(14+6)+(20+6)+(26+6)=100

Improve Answer

I'm using oracle9i to create a table which stores lyrics in long datatype. But if i store a long string of text and display it from the table, it displays only 80 chars. So someone help me how to store the whole lyrics txt file

ramyaselvi16

 Profile Answers by ramyaselvi16 Questions by ramyaselvi16

• Jun 24th, 2008

Variable length character values up to 2 gigabytes. Only one LONG column is allowed per table. You cannot use LONG datatype in functions, WHERE clause of SELECT, in indexing and subqueries.

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 16th, 2008

If text is not visible use SET  LONG  2000.

Answer Question  Select Best Answer

artbria

 Profile Answers by artbria Questions by 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

 Profile Answers by patilpravin_1981 Questions by 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.

Answer Question  Select Best Answer

ambika

• Oct 21st, 2013

Recovery in centralized dbms

su4surya

 Profile Answers by su4surya Questions by su4surya

• Aug 24th, 2009

A centralized database has all its data at one place so there may occur problems of data avalability, and a system crash may lead to whole dataloss. In a distributed database, database is stored on se...

Answer Question  Select Best Answer

ngoudjou albert

• May 22nd, 2012

We use the selfjoin on the table employees to retrieve the job before thier managers

Code
SELECT * FROM employees e , employees m
WHERE e.manager_id=m.employee_id
AND e.hire_date < m.hire_date;

janardhan g

• Aug 7th, 2011

Select x.ename from emp x,emp y where x.mgr=y.empno and x.hiredate

Answer Question  Select Best Answer

farhan

• Apr 20th, 2012

Code
SELECT * FROM emp  WHERE sal >(SELECT avg(sal) FROM emp)

uma

• Mar 6th, 2012

Select * from emp a
where salary >(select avg(salary) from emp b where a.dept_no = b.dept_no);

Answer Question  Select Best Answer

Vamsikrishna Sudanagunta

• Nov 16th, 2022

Print first names and the last names of the artists who had zero sales or no sales?

Ekansh

• Jul 12th, 2022

Just add the ; in the end of quest :)

Answer Question  Select Best Answer

Sai

• Aug 22nd, 2017

A view doesnt hold any data it just shows you the statements .... A materialized view holds data and it is used to connect to other server to access data from particular table and it just brings data...

annaiah

• Jun 26th, 2014

In views query result in not stored in disk or database but materialized views allow to store query result in disk or database when we create views using any table rowid of view is same as original ta...

Answer Question  Select Best Answer

mudassir

• Mar 26th, 2014

I have created VIEW in oracle with more than one table and i tried to delete the record by using the command "delete viewname where fieldname = 3 and the record is get delete. Can you plz explain why this happed

fayaz ahmad khan

• Sep 23rd, 2013

View is used for simplicity and for security of a table in simply a query can also called a view and a stored query which is used a lot of time then it can waste our time so a view is used to reduce a time confusion

Answer Question  Select Best Answer

supratim.kundu

 Profile Answers by supratim.kundu Questions by supratim.kundu

• Mar 4th, 2010

An equi join is used to retrieve all the columns from both the tables. An inner join is used to retrieve selected columns from tables.

patilpravin_1981

 Profile Answers by patilpravin_1981 Questions by patilpravin_1981

• Jan 28th, 2008

Inner jon Or equi join  is The query that retrives the rows that exact match using (=) sign in where clause

Answer Question  Select Best Answer

Bibhudatta Pand

• Jun 18th, 2019

Suppose below is the city table contains NAME SEX Bangalore FEMALE Bangalore MALE Bangalore MALE Bangalore FEMALE Bangalore MALE Bangalore MALE Hyderabad MALE Hyderabad MALE Hyderab...

Karthik K Shetty

• Jun 20th, 2016

Code
SELECT (SELECT COUNT(NAME) FROM TABLE_NAME WHERE UPPER(SEX) LIKE M%) AS COUNT_OF_MALE,
           (SELECT COUNT(NAME) FROM TABLE_NAME WHERE UPPER(SEX) LIKE F%) AS COUNT_OF_FEMALE
FROM DUAL;

Answer Question  Select Best Answer

Odd number of records:select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);Output:-135Even number of records:select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)Output:-246

RAMESH

• Oct 12th, 2011

FOR ODD:- "sql SELECT * FROM (SELECT E.*, ROWNUM rn FROM departments E) WHERE MOD (rn, 2) = 1; FOR EVEN:- "sql SELECT * ...

patilpravin_1981

 Profile Answers by patilpravin_1981 Questions by patilpravin_1981

• Jan 30th, 2008

select rownum from dual where rownum=(select rownum/2 0 from dual)

Answer Question  Select Best Answer

select rownum, empno, ename from emp where rowid in (select rowid from emp where rownum <=&upto minus select rowid from emp where rownum<&Start);

patilpravin_1981

 Profile Answers by patilpravin_1981 Questions by patilpravin_1981

• Jan 30th, 2008

Select * from employees where salary between(1000 and 5000);

LRANI

 Profile Answers by LRANI Questions by LRANI

• Jul 31st, 2007

Hi,minus is used for finding the missing records between 2 tables and not finding the records between the range.It is BETWEEN operator that displays the records between 2 ranges from single table.

Answer Question  Select Best Answer

SELECT customer_no, payments from customer C1WHERE 3<=(SELECT COUNT(*) from customer C2WHERE C1.payment <= C2.payment)

ankitagujrati28

 Profile Answers by ankitagujrati28 Questions by ankitagujrati28

• May 3rd, 2010

Select rownum,cust_no,payments from (select cust_no,payments from customerwhere rownum<4order by payments desc);

thirupathireddy

 Profile Answers by thirupathireddy Questions by thirupathireddy

• Feb 2nd, 2010

SELECT rownum "rank", customer_no, payments FROM (SELECT customer_no, paymentsFROM customer ORDER BY payments DESC)WHERE rownum <4;

Answer Question  Select Best Answer

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...

qptopm

 Profile Answers by qptopm Questions by qptopm

• Nov 7th, 2016

It behaves like a table column, but is not actually stored in the table.

Answer Question  Select Best Answer

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

 Profile Answers by ankitagujrati28 Questions by ankitagujrati28

• May 3rd, 2010

OUTER JOIN is the way to collect records in case of lack of true join condition.

patilpravin_1981

 Profile Answers by patilpravin_1981 Questions by patilpravin_1981

• Jan 29th, 2008

outer join is that type of jon which retrives the matach & alson unmatached recores also from the tables

Answer Question  Select Best Answer

HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause. The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.

Mushtaque

• Aug 25th, 2013

WHERE clause can contains condition that must be met and should directly follow the from clause. And HAVING clause can precede GROUP BY clause,but it is more logical to declared in after GROUP BY clau...

Santhoshkandula

 Profile Answers by Santhoshkandula Questions by Santhoshkandula

• May 11th, 2011

   we have to apply the condition Before grouping the records then you have to use WHERE clause.   we have to apply the condition after grouping the records then you have to apply HAVING clause.  Thanks & Regards    K.Santhosh

Answer Question  Select Best Answer

By using DISTINCT

kethinenisarath

 Profile Answers by kethinenisarath Questions by kethinenisarath

• Apr 9th, 2011

Hi! I think its best option to avoid duplicating records in a queryselect * from empwhere sal in ( select sal from emp                  &nbs...

kethinenisarath

 Profile Answers by kethinenisarath Questions by kethinenisarath

• Mar 29th, 2011

HIselect * from empwhere sal in (select  sal  from emp                      group by ...

Answer Question  Select Best Answer

Suresh Babu Nallamothu

• Nov 8th, 2018

No,Because only one primary key is available for the one table. If you want to use morethan one primary key in a table,it is called as composite primary key.

Code
 

Mark Haynes

 Profile Answers by Mark Haynes Questions by Mark Haynes

• Sep 23rd, 2011

Yes, a primary key can contain more than one one column. Per EF Cobb's definition, a primary key is the sole candidate key on a relation variable (table) formed from one or more attribute values (c...

Answer Question  Select Best Answer

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

• Sep 20th, 2005

---

Hi 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

Lakshmanan

• Jun 30th, 2017

Primary key is combination of not null and unique. Primary key does not insert a null value and insert value is unique. Unique is one time accepted null value and insert a data is uniquely

ankitagujrati28

 Profile Answers by ankitagujrati28 Questions by ankitagujrati28

• Apr 30th, 2010

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.

Improve Answer

SELECTCONNECTRESOURCES

ankitagujrati28

 Profile Answers by ankitagujrati28 Questions by ankitagujrati28

• Apr 30th, 2010

A user can give all database privilages to another user, which are also has that user.

patilpravin_1981

 Profile Answers by patilpravin_1981 Questions by patilpravin_1981

• Jan 29th, 2008

objectes privileges such asselect,delete,update,connect

Answer Question  Select Best Answer

A view is stored procedure based on one or more tables, it’s a virtual table.

Suresh Babu Nallamothu

• Nov 8th, 2018

A view is a virtual table,Because it doesn't have its own data and it doesn't occupy the database memory.

ankitagujrati28

 Profile Answers by ankitagujrati28 Questions by ankitagujrati28

• Apr 30th, 2010

A view is a mirror image of one or more table. View provides data access privilages.

Answer Question  Select Best Answer

Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.

Asish

• Jan 7th, 2018

Rename: Renaming to a object.

Allows: Referring to a object to avoid the ambiguity on entity or attribute.

Pandurang

• Dec 15th, 2017

Rename is used to change the name of the table permanently.
Alias is the alternate name of the table

Answer Question  Select Best Answer

TABLESVIEWSINDEXESSYNONYMSSEQUENCESTABLESPACES etc

suresh

• Mar 13th, 2015

Table,view,procedure,function,package,synonums,triggers,index,sequencess

ankitagujrati28

 Profile Answers by ankitagujrati28 Questions by ankitagujrati28

• Apr 30th, 2010

There are 5 objects:-1.Table2.View3.Synonyms4.Sequences5.Index

Answer Question  Select Best Answer