-
Create table based on another table
How to create table emp1 based on emp, except comm column?
-
Return Multiple Rows
How to return more than one row through functions? Explain with example.
-
-
Oracle long datatype
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
-
-
-
-
-
-
-
-
-
-
Display Odd/ Even number of records
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
-
Display the records between two range?
select rownum, empno, ename from emp where rowid in (select rowid from emp where rownum <=&upto minus select rowid from emp where rownum<&Start);
-
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)
-
What is a pseudo column. Give some examples?
It is a column that is not an actual column in the table.eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.
-
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.
-
When do you use WHERE clause and when do you use HAVING clause?
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.
-
How you will avoid duplicating records in a query?
By using DISTINCT
-
-
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.
-
What are various privileges that a user can grant to another user?
SELECTCONNECTRESOURCES
-
What is a view ?
A view is stored procedure based on one or more tables, it’s a virtual table.
-
What is difference between Rename and Alias?
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.
-
What are different Oracle database objects?
TABLESVIEWSINDEXESSYNONYMSSEQUENCESTABLESPACES etc