Bitmap index
An index that maintains a binary string of ones and zeros for each distinct value of a column within the index.
Materialized View
A materialized view can help speed queries by storing data in a previously joined
or summarized format. Unlike a traditional view, this stores only the query and runs that query every time the view is accessed; a materialized view stores the results of the query in addition to the SQL statements of the view itself. Because the materialized view already contains the results of the view’s underlying query,
Using a materialized view can be as fast as accessing a single table.
OR
What is materialized view?
A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term)
Alias
An alternate name for a column, specified right after the column name in a SELECT statement, seen in the results
Of the query.
DML (Data Manipulation Language)
Includes INSERT, UPDATE, DELETE, and MERGE statements that operate specifically on database tables. Occasionally, SELECT statements are included in the SQL DML category.
DUAL
A special table, owned by the Oracle SYS user, that has one row and one column.
It is useful for ad hoc queries that don’t require rows from a specific table.
NULL
A NULL is usually used to represent a value that is unknown,
not applicable, or not available.
Why order by clause maintains column number values instead of column names?
Every Column have unique number in the table, when we write ORDER BY Clause with number then it refers to that unique number and display the result.
You can see the column Id as per below query
SELECT COLUMN_NAME, COLUMN_ID FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'EMP';
What is difference between SQL and SQL*PLUS?
SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.
There are 10 rows in Table A and 0 Rows in table B.
Select * from A,B
How many rows will be retrieved.
Ans : it will not select any row.coz according to this query it has to give cartesian product.that means it will select m into n rowsso 10*0=0
No Rows Selected.
What is the 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 a column, which do not exist once the SQL statement is executed.
What is the Difference between stored procedures and anonymous procedure?
An external procedure, also sometimes referred to as an external routine, is a procedure stored in a dynamic link library (DLL), or libunit in the case of a Java class method. You register the procedure with the base language, and then call it to perform special-purpose processing. A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. This will be stored in oracle database.
We have EMP and dept table .how do you get the entire department and corresponding employee details and the department which are not allotted it?
We use joins to get details from both the tables that’s kind of joins r called outer joins to get the missed statements.
select e.eno, e.ename, e.deptno
from emp e, dept d
where (+)e.deptno=d.deptno;
What is Complex View?Where we can use?
Views containing any group functions or joining tables are known as complex views.
Write query for the following questions
________________________________________
select * from emp where (rowid,0) in (select rowid,mod(rownum,2) from emp)
select * from emp where (rowid,0) not in (select rowid,mod(rownum,2) from emp)
How to retrieving the data from 11th column to n th column in a table.
select * from emp where rowid in ( select rowid from emp where rownum <=&upto minus select rowid from emp where rownum <&startfrom)
An index that maintains a binary string of ones and zeros for each distinct value of a column within the index.
Materialized View
A materialized view can help speed queries by storing data in a previously joined
or summarized format. Unlike a traditional view, this stores only the query and runs that query every time the view is accessed; a materialized view stores the results of the query in addition to the SQL statements of the view itself. Because the materialized view already contains the results of the view’s underlying query,
Using a materialized view can be as fast as accessing a single table.
OR
What is materialized view?
A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term)
Alias
An alternate name for a column, specified right after the column name in a SELECT statement, seen in the results
Of the query.
DML (Data Manipulation Language)
Includes INSERT, UPDATE, DELETE, and MERGE statements that operate specifically on database tables. Occasionally, SELECT statements are included in the SQL DML category.
DUAL
A special table, owned by the Oracle SYS user, that has one row and one column.
It is useful for ad hoc queries that don’t require rows from a specific table.
NULL
A NULL is usually used to represent a value that is unknown,
not applicable, or not available.
Why order by clause maintains column number values instead of column names?
Every Column have unique number in the table, when we write ORDER BY Clause with number then it refers to that unique number and display the result.
You can see the column Id as per below query
SELECT COLUMN_NAME, COLUMN_ID FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'EMP';
What is difference between SQL and SQL*PLUS?
SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.
There are 10 rows in Table A and 0 Rows in table B.
Select * from A,B
How many rows will be retrieved.
Ans : it will not select any row.coz according to this query it has to give cartesian product.that means it will select m into n rowsso 10*0=0
No Rows Selected.
What is the 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 a column, which do not exist once the SQL statement is executed.
What is the Difference between stored procedures and anonymous procedure?
An external procedure, also sometimes referred to as an external routine, is a procedure stored in a dynamic link library (DLL), or libunit in the case of a Java class method. You register the procedure with the base language, and then call it to perform special-purpose processing. A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. This will be stored in oracle database.
We have EMP and dept table .how do you get the entire department and corresponding employee details and the department which are not allotted it?
We use joins to get details from both the tables that’s kind of joins r called outer joins to get the missed statements.
select e.eno, e.ename, e.deptno
from emp e, dept d
where (+)e.deptno=d.deptno;
What is Complex View?Where we can use?
Views containing any group functions or joining tables are known as complex views.
Write query for the following questions
________________________________________
select * from emp where (rowid,0) in (select rowid,mod(rownum,2) from emp)
select * from emp where (rowid,0) not in (select rowid,mod(rownum,2) from emp)
How to retrieving the data from 11th column to n th column in a table.
select * from emp where rowid in ( select rowid from emp where rownum <=&upto minus select rowid from emp where rownum <&startfrom)
Posts
1 comment:
Brilliant post. The way you have displayed the difference between both these statement is really remarkable. Just looking at the table one can easily understand the basic difference between both of them. Not only this the examples that you have used justified the performance of both the statements.
Post a Comment