SQL Advanced (6)

SQL Advanced (6) SQL view (view) view is a visual table. This chapter explains how to create, update, and delete views.
SQL CREATE View Statement What is a view? In SQL, a view is a table of visualizations based on the result set of an 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 databases. We can add SQL functions, WHERE, and JOIN statements to the view, and we can also submit the data as if they came from a single table.
Note: The design and structure of a database is not affected by functions, where, or join statements in the view.
SQL CREATE VIEW Syntax

CREATE VIEW view_name asselect column_name (s) from table_namewhere condition

Note: Views always display the most recent data. Each time a user queries the view, the database engine rebuilds the data by using SQL statements.
The SQL CREATE View instance can use the view from within a query, inside 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 wish to submit to the user.
Sample database Northwind has some views that are installed by default. The view "Current Product List" lists all products that are in use from the product table. This view is created using the following SQL:

CREATE VIEW [Current Product List] asselect productid,productnamefrom productswhere discontinued=no

We can query the above view:

SELECT * FROM [current Product List]

Another view of the Northwind sample database selects products with all units in the product table that are higher than the average unit price:

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. Notice that this view picks up the data from another view called Product Sales for 1997:

We can query the above view like this:

SELECT * FROM [Category Sales for 1997]

We can also add conditions to the query. Now, we just need to see all the sales of the "Beverages" Category:

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 syntaxcreate or replace view view_name Asselect column_name (s) from table_namewhere condition

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

CREATE VIEW [Current Product List] asselect productid,productname,categoryfrom productswhere discontinued=no

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

SQL DROP View Syntaxdrop View view_name

A null value of SQL NULL is an unknown data that is missing.
By default, the columns of the table can hold NULL values.
This chapter explains the is null and is not NULL operators.
SQL NULL value if a column in the table is optional, we can insert a new record or update an existing one without adding a value to the column. This means that the field will be saved with a NULL value.
NULL values are handled differently than other values.
NULL is used as a placeholder for unknown or non-applicable values.
Note: Cannot compare NULL and 0; they are not equivalent.
For the NULL value processing of SQL, see the "Persons" table below:
City

Id	LastName	FirstName	Address
1	Adams	John		London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas		Beijing

If the "Address" column in the "Persons" table is optional. This means that if you insert a record with no value in the Address column, the address column is saved with a NULL value.
So how do we test for NULL values?
You cannot use a comparison operator to test for NULL values, such as =, &lt, or <>.
We must use the is null and is not NULL operator.
SQL is null how do we just pick a record with a NULL value in the "Address" column?
We must use the IS NULL operator:

SELECT lastname,firstname,address from Personswhere Address is NULL

Result set:

LastName	FirstName	Address
Adams	John
Carter	Thomas

Tip: Always use is NULL to find null values.
SQL is not null how do we pick a record that does not have a NULL value in the "Address" column?
We must use the is not NULL operator:

SELECT lastname,firstname,address from Personswhere Address was not NULL

Result set:

LastName	FirstName	Address
Bush	George	Fifth Avenue

In the next section, we learn about the ISNULL (), NVL (), Ifnull (), and coalesce () functions.
SQL NULL functions sql ISNULL (), NVL (), Ifnull (), and coalesce () functions See the "Products" table below:

p_id	ProductName	UnitPrice	UnitsInStock	UnitsOnOrder
1	Computer	699	25	15
2	Printer	365	36
3	Telephone	280	159	57

If "UnitsOnOrder" is optional, and can contain NULL values.
We use the following SELECT statement:

SELECT productname,unitprice* (Unitsinstock+unitsonorder) from products

In the example above, if there is a "unitsonorder" value of NULL, then the result is null.
Microsoft's ISNULL () function is used to specify how NULL values are handled.
NVL (), Ifnull (), and coalesce () functions can also achieve the same result.
Here, we want the NULL value to be 0.
The following, if "UnitsOnOrder" is null, is not conducive to the calculation, so if the value is null then ISNULL () returns 0.
SQL Server/ms Access

SELECT productname,unitprice* (Unitsinstock+isnull (unitsonorder,0)) from products

Oracle
Oracle does not have the ISNULL () function. However, we can use the NVL () function to achieve the same result:

SELECT productname,unitprice* (UNITSINSTOCK+NVL (unitsonorder,0)) from products

Mysql
MySQL also has a function like ISNULL (). But the way it works is a little different from Microsoft's ISNULL () function.
In MySQL, we can use the ifnull () function, just like this:

SELECT productname,unitprice* (Unitsinstock+ifnull (unitsonorder,0)) from products

Or we can use the coalesce () function, just like this:

SELECT productname,unitprice* (Unitsinstock+coalesce (unitsonorder,0)) from products

SQL Advanced (6)