Orale create views SQL statements and how to use them

SQL CREATE VIEW Statement
What is a view?
In SQL, the view is a visualized table based on the result set of the SQL statement.

The view contains rows and columns, just like a real table. A field in a view is a field from a real table in one or more database tutorials. We can add SQL functions, WHERE, and JOIN statements to the view, and we can submit data just like these from a single table.

Note: The design and structure of a database are not affected by functions, where, or join statements in the view.

SQL CREATE VIEW syntax
CREATE VIEW View_name as
SELECT column_name (s)
From table_name
WHERE condition

Note: The view always displays the most recent data. Whenever a user queries a view, the database engine rebuilds the data by using an SQL statement.
SQL CREATE VIEW Instance
Views can be used from within a query, within a stored procedure, or from within another view. By adding functions, joins, and so on to the view, we can accurately submit the data we want to submit to the user.

The sample database Northwind has some views that are installed by default. The view "Current Product List" lists all the products that are in use from the product table. This view is created using the following SQL:

CREATE VIEW [Current Product List] as
SELECT Productid,productname
From Products
WHERE Discontinued=no We can query the above view:

Another view of the select * from [Current Product List]northwind sample database selects all products in the product table that are above the average unit price:

CREATE VIEW [Products Above Average Price] As
SELECT Productname,unitprice
From Products
WHERE unitprice> (SELECT AVG (UnitPrice) from products)

We can query the above view like this:

SELECT * FROM [Products Above Average Price] Another view instance from the Northwind database calculates the total sales for each category in 1997. Note that this view picks up data from another view named "Product Sales for 1997":

CREATE VIEW [Category Sales for 1997] as
SELECT DISTINCT categoryname,sum (productsales) as Categorysales
From [Product Sales to 1997]
GROUP by CategoryName

We can query the above view like this:

SELECT * FROM [Category Sales to 1997] We can also add conditions to the query. Now we just need to look at all the sales for the "Beverages" Category:

SELECT * FROM [Category Sales to 1997]
WHERE categoryname= ' beverages '

SQL Update View
You can use the following syntax to update the view:

SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name as
SELECT column_name (s)
From table_name
WHERE condition

Now, we want to add the "Category" column to the current Product List view. We will update the view through the following SQL:

CREATE VIEW [current Product List] as
SELECT productid,productname,category
From Products
WHERE Discontinued=no

SQL undo View
You can delete a view from the drop view command.

SQL DROP VIEW Syntax
DROP VIEW view_name
OR REPLACE VIEW ' <your_view_name> '

The following is a tutorial on the foreigner website

Your_view_name> '

As

... The second is the normal SQL select. This select can contain a WHERE clause or other need, and you can place the SELECT statement on something else. The program is open-ended. This actually depends on the purpose of the view.

As you can see in our view, we are formatting the surnames and names. It's a very common thing to do there's a point of view that we've done save there are written in each query where this is a required function. You can also see that we have taken the Birth date column and calculated the age.

Execution view
Execute an SQL view
The following example shows all the code from the view. You can also do a select*, or further restrict the column you want to see. You can also add additional line restrictions to the view because of our approach.

SELECT FIRSTNAME,
LASTNAME,
Birth_dttm,
FULLNAME_FL,
Age
From Vw_students1
WHERE ' is ' is ' not NULL

/

Creating a View containing one or more SQL Tables
Another key advantage of a view is this it allows us to join multiple tables together.

CREATE OR REPLACE VIEW vw_occupied_seats_by_class
as
SELECT
      ;  c.course_designater_fk as  "COURSE",  
       B.seat_num, br>        (A.firstname | | ' ' || A.lastname) as "STUDENT"         
 
from          students          a
         JOIN classregistration b
              on  a.student_id = B.STUDENT_ID_FK
        JOIN CLASSES c
             on  c.classes_num = b. Classes_num
/

Above is a simple view this provides us with a listing of occupied/unoccupied seats to our classes. As you can to the examples below, we can use this view in a variety of different ways. Note this for each scenario, we did not need to join any tables. The grunt work is already done.

Using our View
View a single class

SELECT COURSE,
Seat_num,
STUDENT
From Vw_occupied_seats_by_class
WHERE COURSE = ' Perl100 ' and STUDENT <> ' 1 '
/

COURSE Seat_num STUDENT
----------------------------------
PERL100 1 Madge Lowdown
Perl100 2 Robert Frapples
Perl100 3 Mary Lamacker
PERL100 4 Helga Joens
Perl100 5 Maggie Jomomma
Perl100 6 Mary Meigh
PERL100 7 JONES
Perl100 8 Bob JONES
PERL100 9 Ted Applebee
Perl100 Jon Nesbitt
Perl100 Mary Lamacker
Perl100 Mark Jackson
Count open seats by class

SELECT
COURSE,
COUNT (Seat_num) "# Open seats"
From Vw_occupied_seats_by_class
GROUP by COURSE

/

COURSE # Open seats
----------------------------
dbOrchestra100 16
Perl100 12
Column Name Considerations
The column name must is unique in a view. Note the following example.

CREATE OR REPLACE View vw_name_conflict
As
SELECT
A.classes_num,
B.classes_num

From CLASSES A
JOIN Classesregistration b
On a.classes_num = B.classes_num

/

Duplicate column name ' Classes_num '

Here are how to resolve this issue. Create a unique name using "as".

CREATE OR REPLACE VIEW vw_name_conflict
As
Select A.classes_num "Classes_classes_num",
B.classes_num "Classregistration_classes_num"

From CLASSES A
JOIN ClassRegistration b
On a.classes_num = B.classes_num

/

Drop a View
DROP VIEW courseregistration. Vw_name_conflict
/