Create View that joins Two Tables

Before attempting to create a view that joins two tables in SQL, a student must have a solid understanding of the following fundamental SQL concepts:

1. Basic SQL SELECT Statement
   • Understand how to retrieve data using SELECT.
   • Be familiar with column selection, aliasing (AS), and filtering (WHERE).

   ✅ Example:

   SELECT first_name, last_name FROM students;
       

2. Understanding Tables and Relationships
   • Recognize how tables are structured (rows and columns).
   • Identify primary keys (PK) and foreign keys (FK), which define relationships between tables.
   • Understand one-to-many and many-to-many relationships.

   ✅ Example:

   -- A students table with a primary key (student_id)
   CREATE TABLE students (
       student_id INT PRIMARY KEY,
       first_name VARCHAR(50),
       last_name VARCHAR(50)
   );
   
   -- An enrollments table with a foreign key referencing students
   CREATE TABLE enrollments (
       enrollment_id INT PRIMARY KEY,
       student_id INT,
       course_name VARCHAR(100),
       FOREIGN KEY (student_id) REFERENCES students(student_id)
   );
       

3. SQL Joins
   • INNER JOIN – Retrieves matching records from both tables.
   • LEFT JOIN – Retrieves all records from the left table and matching records from the right.
   • RIGHT JOIN – Retrieves all records from the right table and matching records from the left.
   • FULL OUTER JOIN – Retrieves all records when there is a match in either table.

   ✅ Example:

   SELECT s.student_id, s.first_name, e.course_name
   FROM students s
   INNER JOIN enrollments e ON s.student_id = e.student_id;
       

4. Creating a View
   • Understand that a view is a stored query that provides a virtual table.
   • Know that views are read-only by default (unless an INSTEAD OF trigger is used).
   • Be aware that views must follow referential integrity when joining tables.

   ✅ Example:

   CREATE VIEW student_courses AS
   SELECT s.student_id, s.first_name, e.course_name
   FROM students s
   INNER JOIN enrollments e ON s.student_id = e.student_id;
       

5. View Restrictions and Performance Considerations
   • Indexing: Understand how indexes on foreign keys improve join performance.
   • Complexity: Joins with aggregations (GROUP BY), filters (WHERE), and calculations can impact performance.
   • Updatability: Some views cannot be updated directly if they contain joins, aggregates, or DISTINCT.

   ✅ Example:

   -- This view is NOT updatable because it contains aggregation
   CREATE VIEW course_counts AS
   SELECT course_name, COUNT(student_id) AS num_students
   FROM enrollments
   GROUP BY course_name;
       

6. Testing and Query Execution
   • Test queries before creating the view to ensure they return the correct data.
   • Use EXPLAIN PLAN to analyze performance.

>
EXPLAIN PLAN FOR
SELECT s.student_id, s.first_name, e.course_name
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id;

Conclusion Before creating a view that joins two tables, a student must understand:

• Table relationships and primary/foreign keys
• SQL join types (INNER, LEFT, RIGHT, FULL OUTER)
• The syntax and purpose of views
• Performance implications and restrictions on updatable views

Create a view that joins two tables. Given the following two tables, create a view that will join them, based on CustomerID, and present them as if they were a single table.
CustomerInfo

CustomerID

Lastname

Firstname

Address

City

State

ZipCode

PhoneNumbers

CustomerID

PhoneNumber