Learning of SQL Day 28

 

Day 28: Views

Introduction:

• A view is a virtual table based on the result set of an SQL statement. It contains rows and columns, just like a real table, and can be used to simplify complex queries, enhance security, and present data in a specific format.

Key Concepts:

• View: A virtual table created by a SELECT statement.

• CREATE VIEW: The SQL statement used to create a view.

• ALTER VIEW: The SQL statement used to modify an existing view.

• DROP VIEW: The SQL statement used to delete a view.

SQL Commands and Examples:

1. Creating a View:

   • Define a view using the CREATE VIEW statement.

sql

-- Create a view named StudentCourses that shows students and their enrolled courses
CREATE VIEW StudentCourses AS
SELECT Students.StudentID, Students.FirstName, Students.LastName, Courses.CourseName
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.CourseID;

2. Using a View:

   • Query the view as if it were a table.

sql

-- Select all columns from the StudentCourses view
SELECT * FROM StudentCourses;

3. Modifying a View:

   • Modify an existing view using the ALTER VIEW statement.

sql

-- Modify the StudentCourses view to include the Credits column
ALTER VIEW StudentCourses AS
SELECT Students.StudentID, Students.FirstName, Students.LastName, Courses.CourseName, Courses.Credits
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.CourseID;

4. Dropping a View:

   • Delete a view using the DROP VIEW statement.

sql

-- Drop the StudentCourses view
DROP VIEW StudentCourses;

Practice Exercise:

1. Use the Students and Courses tables.

2. Write a query to create a view named StudentDetails that shows StudentID, FirstName, LastName, and CourseName.

3. Write a query to select all columns from the StudentDetails view.

4. Write a query to modify the StudentDetails view to include the Credits column.

5. Write a query to drop the StudentDetails view.

sql

-- Create a view named StudentDetails
CREATE VIEW StudentDetails AS
SELECT Students.StudentID, Students.FirstName, Students.LastName, Courses.CourseName
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.CourseID;

-- Select all columns from the StudentDetails view
SELECT * FROM StudentDetails;

-- Modify the StudentDetails view to include the Credits column
ALTER VIEW StudentDetails AS
SELECT Students.StudentID, Students.FirstName, Students.LastName, Courses.CourseName, Courses.Credits
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.CourseID;

-- Drop the StudentDetails view
DROP VIEW StudentDetails;

Important Tips:

• Views can simplify complex queries and make them easier to understand.

• Views can be used to enhance security by restricting access to specific data.

• Always consider the performance implications when using views, especially with complex queries.

Understanding and using views allows you to create simplified and secure representations of your data.