Orale How a database uses SQL statements to create general usage of views

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,
(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   10        Jon nesbitt     
Perl100  11         Mary lamacker   
perl100  12         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_clas Ses_num "       ,
         B. classes_num   "Classregistration_classes_num"
  
    from          classes         a
           JOIN classregistration b
                on  a.classes_num = b.classes_num

/  
 & nbsp
Drop a View
Drop View courseregistration. Vw_name_conflict
/