SQL Views

These notes provide a brief overview of views. For more details, raed CH.7 Using SQL Views in Kroenke (13th or 14th Edition).

SQL views are virtual tables constructed from other tables or views. A view has no data of its own, but obtains data from the other tables, and it only has a definition.

SELECT statements are used to define views, with nearly all SELECT statement options available. Views can then be used as if they were regular tables in other SELECT statements. Basic Syntax:

CREATE VIEW view_name AS
  <SELECT STATEMENT>

Continuing our older Midn and Grades example, the following creates a view of those students with F's in NL110. Views can be compared to tables created by subqueries.

Midn (Alpha, Last, First, Birthdate, SATM, SATV, HighSchool, HighSchoolCity, HighSchoolState, HighSchoolGraduate)
Grades (Alpha, AcYr, Sem, BlkNbr, Course, Title, Section, 6Week, 12Week, EndOfTerm, Exam, CourseGrade, Repeated)

CREATE VIEW nl110 AS
  SELECT * FROM Grades
   WHERE Course='NL110' AND CourseGrade='F';

SELECT * FROM nl110;

SQL Views are excellent for security purposes, as they can hide column information and table names to users and scripts using the database. Additionally they can be used to:

• Display the results of computations (and allow direct queries across those computations)
• Hide complicated SQL syntax
• Layer built-in functions
• Provide a level of isolation between the data and the user's view of the data
• Assign different processing permission to different views of the same table

Using views to create a level of abstraction

One of the greatest advantages to using views is the level of abstraction that they can offer. You can create a simple view such as:

CREATE VIEW GradesApp AS
  SELECT * FROM Grades;

and then have all of your scripts and applications use GradesApp. We can now alter the underlying tables used by an application without needing to change the application code. Consider this single SQL statement, requiring no changes to any code referencing our GradesApp view, to point an application to a newly redesigned Grades table:

ALTER VIEW GradesApp AS
  SELECT * FROM newGradesTable;

Updating data via views

Depending on how the view was created we can update the underlying information via the view itself. We can directly update those views based on a single table with

• No computed columns
• All non-NULL columns are present in the view

We can also update views based on a single table, primary key in view, with some non-NULL columns missing, but we will only be able to update non-computed columns and perform deletes. Inserts will fail!

SQL Views Problems

1. Using our existing Grades and Midn tables:
   
   

   Midn (Alpha, Last, First, Birthdate, SATM, SATV, HighSchool, HighSchoolCity, HighSchoolState, HighSchoolGraduate)
   Grades (Alpha, AcYr, Sem, BlkNbr, Course, Title, Section, 6Week, 12Week, EndOfTerm, Exam, CourseGrade, Repeated)

   Write the SQL needed to create two views
   • View for faculty - Everything from Midn and Grades except birthdate and high school information.
   • View for students - Alpha, Last, First, Birthdate and class information but not Grades.
2. Create a view to display three columns: Alpha, Name (presented like "First last") and GPA. Create the GPA table First using GPA.sql.
   
   

   Midn (Alpha, Last, First, Birthdate, SATM, SATV)
   GPA (Alpha, GPA)

3. Write the SQL to create a view that provides Alpha, Name, GPA, Course, course title, and course grade for all students.
   
   

   Midn (Alpha, Last, First, Birthdate, SATM, SATV, HighSchool, HighSchoolCity, HighSchoolState, HighSchoolGraduate)
   Grades (Alpha, AcYr, Sem, BlkNbr, Course, Title, Section, 6Week, 12Week, EndOfTerm, Exam, CourseGrade, Repeated)
   GPA (Alpha, GPA)

More SQL Views Problems

For these problems you will need to refer to the SD321.all_sales table, which already has sales data populated for you (if you want to copy it to your own schema here you go!). Even though this table resides in a different schema than your mAlpha schema, you can create SQL Views in your mAlpha schema referring to it!

The schema for all_sales tables is given below:

all_sales(SaleID, EmployeeID, Sale_cents, Trans_Date)

Here is the code to create a view in your schema (mAlpha) pointing to the SD321.all_sales table and converting cents column (good for accuracy in storage) to dollars (good for humans to read)!

DROP VIEW IF EXISTS all_sales_dollars_view;

CREATE VIEW all_sales_dollars_view AS
  SELECT SaleID, EmployeeID, ROUND(Sale_cents/100, 2) AS Sale_Dollars, Trans_Date
    FROM SD321.all_sales;

1. From the all_sales_dollars_view above, create four views to do some quarterly calculations. For each quarter in last year, create a view that returns the EmployeeID, total (dollars) sales for the employee for that quarter, the quarter number and applicable year for the total. (Checkout MySQL Documentation for some useful functions for working with dates, including YEAR(), MONTH(), AND CURDATE()!)
2. Create a single view to get all these stats in "one table"!
3. Create a view to track our top sales rep per quarter!