SQL Views:
Views in SQL are a kind of virtual table. A view also has rows and columns
like tables, but a view doesn’t store data on the disk like a table. View defines a
customized query that retrieves data from one or more tables, and represents the
data as if it was coming from a single source.
We can create a view by selecting fields from one or more tables present in the
database. A View can either have all the rows of a table or specific rows based
on certain conditions.
Uses of a View
A good database should contain views for the given reasons:
1. Restricting data access – Views provide an additional level of table
security by restricting access to a predetermined set of rows and
columns of a table.
2. Hiding data complexity – A view can hide the complexity that exists
in multiple joined tables.
3. Simplify commands for the user – Views allow the user to select
information from multiple tables without requiring the users to actually
know how to perform a join.
4. Store complex queries – Views can be used to store complex queries.
5. Rename Columns – Views can also be used to rename the columns
without affecting the base tables provided the number of columns in
view must match the number of columns specified in a select
statement. Thus, renaming helps to hide the names of the columns of
the base tables.
6. Multiple view facility – Different views can be created on the same
table for different users.
CREATE VIEWS in SQL
We can create a view using CREATE VIEW statement. A View can be created
from a single table or multiple tables.
Syntax
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;
Parameters:
view_name: Name for the View
table_name: Name of the table
condition: Condition to select rows
DELETE VIEWS in SQL
SQL allows us to delete an existing View. We can delete or drop View using
the DROP statement.
Syntax
DROP VIEW view_name;
Example
If you want to delete a View named MarksView.
DROP VIEW MarksView;
UPDATE VIEW in SQL
If you want to update the existing data within the view, use the update Syntax
UPDATE view_name
SET column1 = value1, column2 = value2...., columnN =
valueN
WHERE [condition];
Rules to Update Views in SQL:
1. The SELECT statement which is used to create the view should not include
GROUP BY clause or ORDER BY clause.
2. The SELECT statement should not have the DISTINCT keyword.
3. The View should have all NOT NULL values.
4. The view should not be created using nested queries or complex queries.
5. The view should be created from a single table. If the view is created using
multiple tables then we will not be allowed to update the view.
Example 1: Update View to Add or Replace a View Field
We can use the CREATE OR REPLACE VIEW statement to add or replace
fields from a view.
If we want to update the view MarksView and add the field AGE to this
View from StudentMarks Table, we can do this by:
CREATE OR REPLACE VIEW MarksView AS
SELECT [Link], [Link], [Link], [Link]
FROM StudentDetails, StudentMarks
WHERE [Link] = [Link];
If we fetch all the data from MarksView now as:
SELECT * FROM MarksView;
Example 2: Update View to Insert a row in a view
We can insert a row in a View in the same way as we do in a table. We can use
the insert into statement of SQL to insert a row in a View.
In the below example, we will insert a new row in the View DetailsView:
INSERT INTO DetailsView(NAME, ADDRESS)
VALUES("Suresh","Gurgaon");
If we fetch all the data from DetailsView now as,
SELECT * FROM DetailsView;
Deleting a row from a View
Deleting rows from a view is also as simple as deleting rows from a table. We
can use the DELETE statement of SQL to delete rows from a view. Also deleting
a row from a view first deletes the row from the actual table and the change is
then reflected in the view.
Delete view with where clause:
DELETE FROM DetailsView WHERE NAME="Suresh";
DBMS ASSIGNMENT NO. 8
1. Create 4 tables Student, Course, Marks, and Faculty in
SQL.
Student table
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
Gender CHAR(1),
Email VARCHAR(100),
Phone VARCHAR(15)
);
Course table
CREATE TABLE Course (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
Description TEXT,
Credits INT,
FacultyID INT,
FOREIGN KEY (FacultyID) REFERENCES Faculty(FacultyID)
);
Marks table
CREATE TABLE Marks (
MarksID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
MarksObtained DECIMAL(5, 2),
Grade CHAR(2),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);
Faculty table
CREATE TABLE Faculty (
FacultyID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100),
Phone VARCHAR(15),
Department VARCHAR(100)
);
2. Insert 5 rows in each table.
Student table
INSERT INTO Student (StudentID, FirstName, LastName, DateOfBirth,
Gender, Email, Phone) VALUES
(1001, 'Alice', 'Smith', '2000-01-15', 'F', '[Link]@[Link]',
'1234567890'),
(1002, 'Bob', 'Johnson', '1999-05-22', 'M', '[Link]@[Link]',
'2345678901'),
(1003, 'Catherine', 'Brown', '2001-03-10', 'F', '[Link]@[Link]',
'3456789012'),
(1004, 'David', 'Wilson', '1998-12-30', 'M', '[Link]@[Link]',
'4567890123'),
(1005, 'Eva', 'Miller', '2002-07-20', 'F', '[Link]@[Link]',
'5678901234');
Faculty table
INSERT INTO Faculty (FacultyID, FirstName, LastName, Email, Phone,
Department) VALUES
(111, 'John', 'Doe', '[Link]@[Link]', '9876543210', 'Computer
Science'),
(112, 'Linda', 'Taylor', '[Link]@[Link]', '8765432109',
'Mathematics'),
(113, 'Michael', 'Anderson', '[Link]@[Link]', '7654321098',
'Physics'),
(114, 'Patricia', 'Thomas', '[Link]@[Link]', '6543210987',
'Chemistry'),
(115, 'James', 'Jackson', '[Link]@[Link]', '5432109876',
'Biology');
Course table
INSERT INTO Course (CourseID, CourseName, Description, Credits,
FacultyID) VALUES
(201, 'Database Systems', 'Introduction to database management systems', 4,
111),
(202, 'Calculus', 'Advanced calculus and mathematical concepts', 3, 112),
(203, 'Physics 101', 'Basic principles of physics', 3, 113),
(204, 'Organic Chemistry', 'Study of organic compounds and reactions', 4, 114),
(205, 'Biology Basics', 'Fundamentals of biological sciences', 3, 115);
Marks table
INSERT INTO Marks (MarksID, StudentID, CourseID, MarksObtained, Grade)
VALUES
(131, 1001, 201, 85.5, 'A'),
(132, 1002, 202, 78.0, 'B'),
(133, 1003, 203, 65.0, 'C'),
(134, 1004, 204, 55.5, 'D'),
(135, 1005, 205, 90.0, 'A');
3. Create a view to show student details along with
their enrolled courses.
CREATE VIEW StudentCourses AS
SELECT [Link], [Link], [Link], [Link], [Link]
FROM Student s JOIN Marks m ON [Link] = [Link]
JOIN Course c ON [Link] = [Link];
[Link] a view to display students' marks and
grades for each course.
CREATE VIEW StudentMarks AS
SELECT [Link], [Link], [Link], [Link],
[Link], [Link] FROM Student s
JOIN Marks m ON [Link] = [Link]
JOIN Course c ON [Link] = [Link];
5. Create a view to display the average marks obtained by
students in each course.
CREATE VIEW CourseAverageMarks AS
SELECT
[Link], [Link],
AVG([Link]) AS AverageMarks
FROM
Course c
JOIN
Marks m ON [Link] = [Link]
GROUP BY
[Link], [Link];
6. Create a view to show all courses offered by a specific
faculty member.
CREATE VIEW FacultyCourses AS
SELECT
[Link], [Link], [Link],
[Link], [Link]
FROM
Faculty f
JOIN
Course c ON [Link] = [Link];
7. Create a view to display the top 3 students with the
highest marks in each course.
CREATE VIEW Top3StudentsPerCourse AS
SELECT
[Link], [Link],
[Link], [Link], [Link],
[Link]
FROM
Marks m
JOIN
Student s ON [Link] = [Link]
JOIN
Course c ON [Link] = [Link]
ORDER BY
[Link], [Link] DESC
LIMIT 3;
8. Create a view to show the total number of students enrolled in each
course.
CREATE VIEW StudentCountPerCourse AS
SELECT
[Link], [Link],
COUNT([Link]) AS StudentCount
FROM
Course c
JOIN
Marks m ON [Link] = [Link]
GROUP BY
[Link], [Link];
9. Create a view to display students who have
failed any course (assuming pass marks is 40).
CREATE VIEW FailedStudents AS
SELECT
[Link], [Link], [Link],
[Link], [Link],
[Link]
FROM
Marks m
JOIN
Student s ON [Link] = [Link]
JOIN
Course c ON [Link] = [Link]
WHERE
[Link] < 40;
10 View to show all students along with their faculty
advisor for each course.
CREATE VIEW StudentFacultyAdvisor AS
SELECT
[Link], [Link] AS StudentFirstName, [Link] AS
StudentLastName,
[Link], [Link],
[Link], [Link] AS FacultyFirstName, [Link] AS
FacultyLastName
FROM
Student s
JOIN
Marks m ON [Link] = [Link]
JOIN
Course c ON [Link] = [Link]
JOIN
Faculty f ON [Link] = [Link];
11. Create a view to show Student Full Contact Info.
CREATE VIEW StudentContactInfo AS
SELECT StudentID, FirstName, LastName, Email, Phone, Gender, DateOfBirth FROM Student;
12. Create a View to show the list of courses offered under
each faculty department.
CREATE VIEW FacultyDepartmentCourses AS
SELECT [Link], [Link], [Link] AS FacultyFirstName, [Link] AS FacultyLastName,
[Link], [Link], [Link] FROM Faculty f JOIN Course c ON [Link] = [Link];
13. Create a View to Displays the average marks of students
for each faculty’s courses.
CREATE VIEW AverageMarksByFaculty AS
SELECT [Link], [Link] AS FacultyFirstName, [Link] AS FacultyLastName,
AVG([Link]) AS AvgMarks FROM Faculty f JOIN Course c ON [Link] = [Link] JOIN
Marks m ON [Link] = [Link] GROUP BY [Link], [Link], [Link];
14. Create a View to show how many courses each student is
enrolled in.
CREATE VIEW StudentCourseCount AS
SELECT [Link], [Link], [Link], COUNT([Link]) AS TotalCourses FROM Student s
JOIN Marks m ON [Link] = [Link] GROUP BY [Link], [Link], [Link];
15. Create a view to Summarizes performance per course (min,
max, and average marks).
CREATE VIEW CoursePerformanceSummary AS
SELECT [Link], [Link], MIN([Link]) AS MinMarks, MAX([Link]) AS
MaxMarks, AVG([Link]) AS AvgMarks FROM Course c JOIN Marks m ON [Link] =
[Link] GROUP BY [Link], [Link];
16. Create a View to Displays student grades along with the
faculty who taught that course.
CREATE VIEW StudentWithGradesAndFaculty AS
SELECT [Link], [Link] AS StudentFirstName, [Link] AS StudentLastName, [Link],
[Link], [Link] AS FacultyFirstName, [Link] AS FacultyLastName FROM Student s JOIN Marks
m ON [Link] = [Link] JOIN Course c ON [Link] = [Link] JOIN Faculty f ON
[Link] = [Link];
17. Create a View to Calculates the average marks of each
student across all their courses.
CREATE VIEW StudentAverageMarks AS
SELECT [Link], [Link], [Link], AVG([Link]) AS AverageMarks FROM Student
s JOIN Marks m ON [Link] = [Link] GROUP BY [Link], [Link], [Link];
18. Create a View to show how many courses each faculty
member is teaching and total credits.
CREATE VIEW FacultyTeachingLoad AS
SELECT [Link], [Link], [Link], COUNT([Link]) AS TotalCourses, SUM([Link]) AS
TotalCredits FROM Faculty f LEFT JOIN Course c ON [Link] = [Link] GROUP BY [Link],
[Link], [Link];
19. Create View to Displays the top 3 students overall
(across all courses) by total marks.
CREATE VIEW TopScoringStudents AS
SELECT TOP 3 [Link], [Link], [Link], SUM([Link]) AS TotalMarks FROM
Student s JOIN Marks m ON [Link] = [Link] GROUP BY [Link], [Link], [Link]
ORDER BY TotalMarks DESC;
20. Create a View to Shows how many students achieved each
grade in every course.
CREATE VIEW GradeDistributionPerCourse AS
SELECT [Link], [Link], [Link], COUNT([Link]) AS StudentCount FROM Course cJOIN
Marks m ON [Link] = [Link] GROUP BY [Link], [Link], [Link];