SQL VIEWS

• View


  A virtual table that enables us to have a look at the data in the table without blocking the table from being accessed by other programs and thereby helping us to avoid deadlock situations
• Views


  • They are similar to real tables but hold no data
  • They contain rows and columns similar to the real table, but do not contain any data of their own
  • They are actually a query by joining one or more tables
  • A View is a definition built on top of one or more real tables(that hold data)
  • If any changes occur in the underlying tables, the same changes is reflected in the View
• CREATE VIEW - Single Table Syntax


  • CREATE VIEW view_table_name1 AS SELECT cName1, cName2 FROM table_name1
  • CREATE VIEW view_table_name1 AS SELECT cName1, cName2 FROM table_name1 WHERE (Condition)
  • CREATE VIEW view_table_name1 AS QUERY
• CREATE VIEW - Multiple Tables Syntax


  • CREATE VIEW view_table_name1 AS SELECT cName1, cName2 FROM table_name1 INNER JOIN table_name2 ON cName1 = cName2
  • CREATE VIEW view_table_name1 AS SELECT cName1, cName2 FROM table_name1 INNER JOIN table_name2 ON cName1 = cName2 WHERE (Condition)
• Modules table


  • Module_ID
  • Title
  • Assessment_ID
  • Staff_ID
• CREATE VIEW oldStudents


  SELECT Student_Name, State FROM Students WHERE State = 'CO'

  
• CREATE VIEW newStudents


  SELECT Student_Name, State FROM Students
• Views


  • Once created views are stored as part of the schema
  • A view always shows up-to-date data
  • The database engine recreates the view, every time a user queries it
• CREATE VIEW seniorStudents


  SELECT Title, Student_Name, State FROM Students INNER JOIN Modules USING(Module_ID)
• CREATE VIEW fakeStudents


  SELECT Title, Student_Name, State FROM Students INNER JOIN Modules USING(Module_ID) WHERE State = 'CO'

  
• DROP VIEW


  Used to delete a view
• ALTER VIEW


  Used to modify or update the already created VIEW without dropping it
• Views


  • They can encapsulate a complex query into a simpler, reusable virtual table
  • They enable users to easily access summarised information without needing to understand the underlying joins or calculations
  • They can aid in report generation by providing a ready-made data source that reflects the most current sales figures, directly derived from the base tables
  • They help in maintaining data security by restricting access to only the aggregated or specific data needed, without exposing the entire underlying tables
• CREATE VIEW CustomerOrdersSummary


  SELECT c.id AS CustomerID, c.company AS CompanyName, COUNT(o.id) AS TotalOrders, SUM(od.quantity * od.unit_price) AS TotalAmountSpent FROM Customers c INNER JOIN Orders o ON c.id = o.customer_id INNER JOIN order_details od ON o.id = od.order_id GROUP BY c.id, c.company
• Aliases


  For readability, to shorten queries and reduce likelihood of typo errors, and for disambiguation when columns have identical names across joined tables
• CREATE VIEW ProductSalesReport


  SELECT p.id AS ProductID, p.product_name AS ProductName, SUM(od.quantity) AS TotalUnitsSold, SUM(od.quantity * od.unit_price) AS TotalSales FROM products p INNER JOIN order_details od ON p.id = od.product_id GROUP BY p.id, p.product_name
• CREATE VIEW EmployeeSalesPerformance


  SELECT e.id AS EmployeeID, CONCAT(e.first_name, ' ', e.last_name) AS EmployeeName, COUNT(o.id) AS TotalSalesOrders, SUM(od.quantity * od.unit_price) AS TotalSalesValue FROM employees e INNER JOIN orders o ON e.id = o.employee_id INNER JOIN order_details od ON o.id = od.order_id GROUP BY e.id, CONCAT(e.first_name, ' ', e.last_name)
• CREATE VIEW DetailedCustomerOrders


  SELECT c.id AS CustomerID, c.company AS CustomerName, o.id AS OrderID, o.order_date AS OrderDate, p.product_name AS ProductName, od.quantity, od.unit_price, (od.quantity * od.unit_price) AS TotalPrice FROM customers c JOIN orders o ON c.id = o.customer_id JOIN order_details od ON o.id = od.order_id JOIN products p ON od.product_id = p.id