SQL_Views Assignment
Note: This assignment has to be completely performed in MYSQL Workbench. Select your OS
type and download workbench from the given link.
https://dev.mysql.com/downloads/workbench/
The version of MYSQL Workbench used for this assignment is 8.0.27
You can use any other stable version.
Follow: https://youtu.be/YSOY_NyOg40
● Views in SQL are kind of virtual tables. A View can either have all
the rows of a table or specific rows based on certain condition.
SCHEMA
EVENTS TABLE PARTICIPANT TABLE
Before beginning, lets first create a table and insert data.
Create table ‘events’ having columns
event_id,event_name,fees,venue,no_of_participants
This is how our events table should look like
.
EVENTS TABLE
CREATING VIEWS
We can create 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;
view_name: Name for the View
table_name: Name of the table
condition: Condition to select rows
Creating View from a single table:
CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM StudentDetails
WHERE S_ID < 5;
Q1. Create a view having event name and venue for events having more than 10
participants and venue as center stage.
YOUR QUERY HERE:
Output:
Q2. Create a view having event name and venue for events having venue as
auditorium.
YOUR QUERY HERE:
Output:
Creating View from multiple tables:
CREATE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;
For creating views from multiple tables, lets create a second table and insert
data.
Create table ‘participant’ having columns,
participant_name,event_name, ,fees_paid_or_not
This is how our schema should look like.
PARTICIPANT TABLE
Q3. Create a view from events and participant tables having matching event
names.
YOUR QUERY HERE:
Output:
Q4. Create a view of event name and participant name participating in solo
singing event.
YOUR QUERY HERE:
Output:
DELETING VIEWS
Syntax:
DROP VIEW view_name;
Q5. Delete the view3.
YOUR QUERY HERE:
Output:
UPDATING VIEWS
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.
5. The view should be created from a single table.
We can use the CREATE OR REPLACE VIEW statement to add or remove fields
from a view.
Syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column1,coulmn2,..
FROM table_name
WHERE condition;
Q6. Replace the view2 with participant names who have not paid the fees.
YOUR QUERY HERE:
Output:
Inserting a row in a view:
Syntax:
INSERT INTO view_name(column1, column2 , column3,..) VALUES(value1, value2,
value3..);
Q7. Insert a row of values in the view2.
YOUR QUERY HERE:
Output:
Congratulations, you have just completed an assignment!!
FEEDBACK FORM:
https://docs.google.com/forms/d/e/1FAIpQLScnAaAKvph9R3vINyCtOOMiav-
tSMsRGlJReMaHyPjsXDlTIA/viewform
