TASK#3: Views in MS SQL Server 2022
1. What is a View?
• A View is a virtual table that is created based on a SELECT query from one or more tables.
• It does not store data physically; it just presents data dynamically when queried.
• Used to simplify complex queries and enhance security by restricting access to specific columns.
2. Why Use Views?
• Simplifies Queries: Reduces the need to write complex joins repeatedly.
• Enhances Security: Can restrict access to specific columns instead of the whole table.
• Data Abstraction: Provides a customized view of the database without exposing table details.
• Consistency: Ensures consistent query results, even when the underlying tables change.
3. Types of Views in SQL Server
1. Simple View – Based on a single table, does not contain functions or aggregates.
2. Complex View – Can include multiple tables, joins, aggregates, and functions.
3. Indexed View – A materialized view that stores data physically for faster performance.
Step-by-Step Guide to Execute Views in MS SQL Server 2022
Step 1: Create a Sample Database & Tables
CREATE DATABASE SchoolDB;
USE SchoolDB;
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name NVARCHAR(100),
Age INT,
Grade CHAR(2),
Marks INT
);
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName NVARCHAR(100),
StudentID INT FOREIGN KEY REFERENCES Students(StudentID)
);
Step 2: Insert Sample Data
INSERT INTO Students VALUES (1, 'Alice', 20, 'A', 85);
INSERT INTO Students VALUES (2, 'Bob', 21, 'B', 75);
INSERT INTO Students VALUES (3, 'Charlie', 22, 'A', 90);
INSERT INTO Courses VALUES (101, 'Mathematics', 1);
INSERT INTO Courses VALUES (102, 'Science', 2);
INSERT INTO Courses VALUES (103, 'History', 3);
Step 3: Create a Simple View
CREATE VIEW StudentInfo AS
SELECT StudentID, Name, Age, Grade FROM Students;
Step 4: Query the View
SELECT * FROM StudentInfo;
Step 5: Create a Complex View (Join Multiple Tables)
CREATE VIEW StudentCourses AS
SELECT s.StudentID, s.Name, c.CourseName
FROM Students s
JOIN Courses c ON s.StudentID = c.StudentID;
Step 6: Query the Complex View
SELECT * FROM StudentCourses;
Step 7: Modify an Existing View
ALTER VIEW StudentInfo AS
SELECT StudentID, Name, Age, Grade, Marks FROM Students;
Step 8: Drop a View
DROP VIEW StudentInfo;
Task#3 - (2.5 Marks)
Objective: Students will create and manipulate SQL views to understand their usage, benefits, and
modifications.
• Instructions
1. Use the given database schema (SchoolDB) or create it if not already done.
2. Follow each step carefully and execute the SQL queries.
3. Capture screenshots of your SQL execution and output.
4. Upload a SQL File and the screenshots of results.
Step 1: Create a View for High Scoring Students: Create a view named HighScorers that includes students
with marks greater than 80.
1. Open MS SQL Server Management Studio (SSMS) and connect to your database.
2. Ensure you are using SchoolDB by running:
USE SchoolDB;
3. Create the HighScorers view:
CREATE VIEW HighScorers AS
SELECT StudentID, Name, Age, Grade, Marks
FROM Students
WHERE Marks > 80;
4. Test the view by running:
SELECT * FROM HighScorers;
Step 2: Modify the StudentCourses View to Include Grades: Modify the existing StudentCourses view to
include the Grade column from the Students table.
1. Modify the view using:
ALTER VIEW StudentCourses AS
SELECT s.StudentID, s.Name, s.Grade, c.CourseName
FROM Students s
JOIN Courses c ON s.StudentID = c.StudentID;
2. Test the view by running:
SELECT * FROM StudentCourses;
Step 3: Write Queries to Retrieve Data from Both Views
Write two SQL queries:
1. Retrieve all high-scoring students:
SELECT * FROM HighScorers;
2. Retrieve all students with course names and grades:
SELECT * FROM StudentCourses;
Step 4: Create a View to Show Students Without Courses: Create a view named StudentsWithoutCourses
that includes students who are not enrolled in any course.
1. Create the view:
CREATE VIEW StudentsWithoutCourses AS
SELECT s.StudentID, s.Name, s.Grade
FROM Students s
LEFT JOIN Courses c ON s.StudentID = c.StudentID
WHERE c.CourseID IS NULL;
2. Test the view by running:
SELECT * FROM StudentsWithoutCourses;
Step 5: Drop the HighScorers View: Drop the HighScorers view after verifying it works.
1. Check if the view exists:
SELECT * FROM HighScorers;
2. Drop the view:
DROP VIEW HighScorers;
IF EXISTS: You can wrap the above DROP VIEW statement in an IF EXISTS condition to avoid errors if the
view does not exist.
IF EXISTS (SELECT * FROM sys.views WHERE name = 'HighScorers')
BEGIN
DROP VIEW HighScorers;
END
