The CREATE VIEW Command
Views are the logical Windows for users to see the results of operations on one or more base tables or views. A view is a virtual table, just like a table which is in a 2 dimensional structure of rows and columns. These are derived from stored data structures and computed again for each query referring to them.
Views are created using the CREATE VIEW command. The format of the command is
CREATE VIEW <view_name> [<view column names>] AS <query specification>;
The view column names are optional. The <query specification> is the SELECT for selecting the rows and columns which will constitute the view. Views are typically used in queries, and are required to provide restricted access, or more relevant window to the user wanting to see the data.
CREATE VIEW DPT100 (E_Code,Ename,Basic) AS SELECT E_Code,Ename,Basic FROM Employee WHERE Basic>5000;
The view DPT100 will include only the employee having basic greater than 5000 in employee table.
Alias means another name. The table alias means to rename a table in a particular SQL statement. The renaming is a temporary change and the actual table name does not change in the database.
Query : Get the employees name who have some basic pay.
SELECT Ename FROM emp First, Emp Second WHERE First.Basic = Second.Basic AND First.Eno <> Second.Eno;
Renaming Using AS :
We can rename attribute and also the table using AS operator. Consider the same example,
SELECT Ename AS Employee_Name FROM emp AS First, Emp AS Second WHERE First.Basic = Second.Basic AND First.Eno <> Second.Eno;
A Comment is the text which appear within the SQL statement such that they do not affect to the SQL statement in execution.
A comment can be written in two ways :
- Begin the comment with /* and ended with */.
- Begins with (2 hyphens) and proceed with the text of the comment.
Describe an Oracle table, view, synonym, package or function.
DESCRIBE table_name DESC table_name;
Clear screen is used to clear the screen at SQL* PLUS prompt.
|Joins in SQL||Questions on SQL – Part 1|