Orale create view SQL statements and usage

What is a SQLCREATEVIEW statement? In SQL, a view is a visualized table based on the SQL statement result set. A view contains rows and columns, just like a real table. A field in a view is a field from one or more real tables. You can add SQL functions, WHERE statements, and JOIN statements to the view, and submit data, just like a single table. Note: the database design and structure are not subject to functions, where, or

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

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

Note: the database design and structure will not be affected by functions, where, or join statements in the view.

SQL CREATE VIEW syntax
Create view view_name
SELECT column_name (s)
FROM table_name
WHERE condition

Note: The view always displays the latest data. Whenever a user queries a view, the database engine uses SQL statements to recreate the data.
SQL CREATE VIEW instance
You can use a view within a query, within a stored procedure, or within another view. By adding functions and joins to the view, we can precisely submit the data we want to submit to users.

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

Create view [Current Product List]
SELECT ProductID, ProductName
FROM Products
WHERE Discontinued = No we can query the above view:

SELECT * FROM [Current Product List] Another view of the Northwind Sample Database selects Products with prices higher than the average unit prices in the Products table:

Create view [Products Above Average Price]
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice> (select avg (UnitPrice) FROM Products)

We can query the preceding view as follows:

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

Create view [Category Sales For 1997]
Select distinct CategoryName, Sum (ProductSales) AS CategorySales
FROM [Product Sales for 1997]
Group by CategoryName

We can query the preceding view as follows:

SELECT * FROM [Category Sales For 1997] You can also add conditions to the query. Now, we only need to check all the sales volume of the "Beverages" class:

SELECT * FROM [Category Sales For 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
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]
SELECT ProductID, ProductName, Category
FROM Products
WHERE Discontinued = No

SQL revocation View
You can use the drop view command to delete a VIEW.

SQL DROP VIEW Syntax
Drop view view_name
Or replace view' '

The following is a tutorial on foreigners.

Your_view_name>'

AS

... Followed by normal SQL SELECT. This SELECT statement can contain a WHERE clause or other requirements, and can be placed on other tasks for the SELECT statement. This solution is endless. This actually depends on the purpose of the view.

As you can see in our opinion, we are formatting the last name and name. This is a very common thing. There is a point of view, that is, we have already saved the WHERE statement written in every query. 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 make a SELECT * or further limit the column you want to see. You can also add additional row restrictions because of our practices.

Select firstname,
LASTNAME,
BIRTH_DTTM,
FULLNAME_FL,
AGE
FROM VW_STUDENTS1
WHERE AGE IS NOT NULL

/

Creating a View containing one or more SQL Tables
Another key advantage of a view is that 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
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 that provides us with a listing of occupied/unoccupied seats for our classes. as you can see from the examples below, we can use this view in a variety of different ways. note that for each scenario that 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 be 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
Join classesregistration B
ON a. CLASSES_NUM = B. CLASSES_NUM

/

Duplicate column name 'classes _ num'

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

Create or replace view vw_NAME_CONFLICT
AS
SELECT a. CLASSES_NUM "CLASSES_CLASSES_NUM ",
B. CLASSES_NUM "CLASSREGISTRATION_CLASSES_NUM"

From classes
Join classregistration B
ON a. CLASSES_NUM = B. CLASSES_NUM

/

Drop a View
Drop view courseregistration. VW_NAME_CONFLICT
/