SQL View
Explanation: A SQL view is a virtual table that simplifies data access by showing selected columns from one or more tables, but without physically storing the data. It restricts access and can be updated or deleted like a regular table.
Create View:
You can easily create a View in Structured Query Language by using the CREATE VIEW statement. You can create the View from a single table or multiple tables.
Syntax to Create View from Single Table:
CREATE VIEW View_Name AS
SELECTColumn_Name1, Column_Name2, ….., Column_NameN
FROMTable_Name
WHEREcondition;
In the syntax, View_Name is the name of View you want to create in SQL. The SELECT command specifies the rows and columns of the table, and the WHERE clause is optional, which is used to select the particular record from the table.
You can create a View from multiple tables by including the tables in the SELECT statement.
Syntax to Create View from Multiple Table:
CREATE VIEWView_Name AS
SELECTColumn_Name1, Table_Name1.Column_Name2, Table_Name2.Column_Name2, ….., Table_NameN.Column_NameN
FROMTable_Name1, Table_Name2, ….., Table_NameN
WHEREcondition;
Example:
Create a View from Single table
Let’s consider the Student_Details table, which consists of Stu_ID, Stu_Name, Stu_Subject, and Stu_Marks columns. The data of the Student_Details is shown in the following table:
Table: Student_Details
Suppose, you want to create a view with Stu_ID, Stu_Subject, and Stu_Marks of those students whose marks are greater than 85. For this issue, you have to type the following query:
CREATEVIEW Student_View AS
SELECTStudent_ID, Stu_Subject, Stu_Marks
FROMStudent_Details
WHEREStu_Marks > 85;
Select * FROM Student_View;
Output:
Create a View from Multiple tables
Let’s consider two tables, Student_Details and Teacher_Details. The Student_Details table consists of Stu_ID, Stu_Name, Stu_Subject, and Stu_Marks columns. And, the Teacher_Details table consists of Teacher_ID, Teacher_Name, Teacher_Subject, Teacher_City columns. The data of the Student_Details and Teacher_Details is shown in the following two tables:
Table: Student_Details
Table: Teacher_Details
Suppose, you want to create a view with Stu_ID, Stu_Name, Teacher_ID, and Teacher_Subject columns from the Student_Details and Teacher_Details tables.
CREATEVIEW Student_Teacher_View AS
SELECTStudent_ID, Student_Details.Stu_Name, Teacher_Details.Teacher_ID, Teacher_Details.Teacher_Subject
FROMStudent_Details, Teacher_Details
WHEREStu_Subject = Teacher_Details.Teacher_Subject;
Select* FROM Student_Teacher_View;
Output:
Update an SQL View:
We can also modify existing data and insert the new record into the view in the Structured Query Language. A view in SQL can only be modified if the view follows the following conditions:
⦁ You can update that view which depends on only one table. SQL will not allow updating the view which is created more than one table.
⦁ The fields of view should not contain NULL values.
⦁ The view does not contain any subquery and DISTINCT keyword in its definition.
⦁ The views cannot be updatable if the SELECT statement used to create a View contains JOIN or HAVING or GROUP BY clause.
⦁ If any field of view contains any SQL aggregate function, you cannot modify the view.
Syntax:
CREATEOR REPLACE VIEW View_Name AS
SELECTColumn_Name1, Column_Name2, ….., Column_NameN
FROMTable_Name
WHEREcondition;
Example:
If we want to update the above Student_View and add the Stu_Name attribute from the Student table in the view, you have to type the following Replace query in SQL:
CREATE OR REPLACEVIEW Student_View AS
SELECT Student_ID, Stu_Name, Stu_Subject, Stu_Marks
FROM Student_Details
WHERE Stu_Subject = ‘Math’;
The above statement updates the existing Student_View.
Select * FROM Student_View;
Output:
Drop SQL View:
We can also delete the existing view from the database if it is no longer needed. The following SQL DROP statement is used to delete the view:
DROP VIEW View_Name;
Course Video
Adventure Database:
Q1. Retrieve the country code, name, and currency code for each country that has a defined currency in the AdventureWorks database using [Person].[CountryRegion] and [Sales].[CountryRegionCurrency] tables.
Output:
Explanation: This view provides a simple association between country regions and their respective currencies. It allows you to see which currency is used in each country region.
Q2. Fetch details about currency rates including rate date, currency codes, average rate, end-of-day rate, currency name, and associated country codes for the AdventureWorks database using [Sales].[CurrencyRate], [Sales].[Currency], [Sales].[CountryRegionCurrency] tables.
Output:
Explanation: This view combines exchange rate information with currency details and links them to the country region codes. It helps to understand the exchange rates of currencies in the context of their usage in different country regions.
Q3. Obtain details about products including product ID, name, category, subcategory, sales order ID, order quantity, and unit price from the AdventureWorks database [Production].[Product], [Production].[ProductSubcategory], [Production].[ProductCategory], [Sales].[SalesOrderDetail] tables.
Output:
Explanation: This view provides information about products, including their categories and subcategories, and links this information to sales order details. Using the `WHERE` clause for joins ensures that the relationships between the tables are correctly applied and allows you to analyze product performance in the context of sales orders.
Northwind Database:
Q1. Retrieve customer details such as customer ID, company name, contact name, order ID, order date, shipping city, and shipping country from the Northwind database Using [Customers] and [Orders] tables.
Output:
Explanation: This view provides a summary of customers and their orders. It combines customer details with their associated orders, which is useful for understanding customer activity, analyzing sales performance, and managing customer relationships.
Q2. Fetch product details including product ID, product name, order ID, unit price, quantity, and discount from the Northwind database using [Products] and [Order Details] tables.
Output:
Explanation: This view provides detailed information about products and their inclusion in specific orders. It links product details with order details, including unit prices, quantities, and discounts. This is useful for analyzing sales data, tracking product performance in orders, and managing inventory.
