Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E
Roll no: 01
Assignment No:4
Title: Views and Nested Queries.
Problem Statement: Create a view called HighSalaryEmployees and write a nested query to
find employees earning more than the average salary.
Course Objective: To acquire skills in SQL and PL/SQL for database operations.
Course Outcome: Implement database operations using SQL and PL/SQL constructs such as
procedures, functions, and triggers.
Tools Required: MySql.
Theory:
1. Views
A view is a virtual table. A view contains rows and columns, just like a real table. The fields in a
view are fields from one or more real tables in the database.
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields
from one or more real tables in the database.
You can add SQL statements and functions to a view and present the data as if the data
were coming from one single table.
A view is created with the CREATE VIEW statement.
Syntax
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';
1.1 SQL Updating a View
A view can be updated with the CREATE OR REPLACE VIEW statement.
Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example
CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 1
Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E
Roll no: 01
FROM Customers
WHERE Country = 'Brazil';
1.2 SQL Dropping a View
A view is deleted with the DROP VIEW statement.
Syntax
DROP VIEW view_name;
Example
DROP VIEW [Brazil Customers];
2. Nested Query
A nested query (also called a subquery) is a query written inside another SQL query.
It is used when the result of one query is needed to be used by another query.
The inner query (subquery) executes first.
The outer query (main query) uses the result of the inner query.
Subqueries are mostly used with WHERE, HAVING, FROM, and SELECT clauses.
They help in writing complex queries in a simple and understandable way.
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE column_name operator (SELECT column_name FROM table_name WHERE
condition);
Example
SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Conclusion: Hence, we have successfully implemented Views and Nested Queries.
Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 2
Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E
Roll no: 01
Query and Output:
Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 3
