SQL Views and Subqueries
Introduction to Views
Views are virtual tables in SQL that are based on the result-set of an SQL statement. They do not
store data themselves but provide a customized way to look at data from one or more underlying
tables.
Key Benefits of Using Views:
Data Summarization: Views can summarize data from various tables, making it easier to
generate reports.
Data Structuring: They allow data to be structured in a way that users find natural or intuitive.
Data Security: Views can restrict access to data, ensuring users only see and (sometimes)
modify what they need.
Creating a View:
The basic syntax for creating a view is:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
view_name: The name you give to your virtual table.
column1, column2, ...: The columns you want to include in the view.
table_name: The table(s) from which you are selecting data.
WHERE condition: An optional clause to filter the rows included in the view.
Example 1: Creating a Simple View
Objective: Create a view to display only the Employee Name (Ename), Address, and Position from
the Employee table.
USE Company;
CREATE VIEW Employee_View AS
SELECT Ename, Address, Position
FROM Employee;
To see the data in this view, you would run:
SELECT * FROM Employee_View;
Example 2: Creating a View from Multiple Tables
Objective: Create a view to display Employee Number (Eno), Employee Name (Ename),
Department Number (DeptNo), and Department Name (Dname) by joining the Employee and
Department tables.
CREATE VIEW Employee_Dept_View AS
SELECT [Link], [Link], [Link], [Link]
FROM Employee, Department
WHERE [Link] = [Link];
To view the data in this new view:
SELECT * FROM Employee_Dept_View;
Example 3: Creating a View with a Specific Condition
Objective: Create a view to display the Employee Number (Eno), Employee Name (Ename), and
Department Name (Dname) for all employees who are 'Manager's.
CREATE VIEW EMP_Manager AS
SELECT [Link], [Link], [Link]
FROM Employee, Department
WHERE [Link] = [Link] AND Position = 'Manager';
To see the managers' information:
SELECT * FROM EMP_Manager;
WITH CHECK OPTION
The WITH CHECK OPTION clause is an addition to the CREATE VIEW statement. Its purpose is to
ensure that any UPDATE or INSERT operations performed through the view satisfy the conditions
defined in the WHERE clause of the view's definition. If an operation does not meet these
conditions, it will result in an error.
Example with WITH CHECK OPTION:
Objective: Create a view Employee_View that displays Eno, Ename, and Position from the
Employee table, but prevent NULL values from being entered into the Position column through
this view.
USE Company;
CREATE VIEW Employee_View AS
SELECT Eno, Ename, Position
FROM Employee
WHERE Position IS NOT NULL
WITH CHECK OPTION;
If you try to insert a record with a NULL position using this view, it will fail.
Inserting Data Through Views
You can insert data into a view, and if the view is based on a single table and includes all its
columns, the data will be inserted into the underlying table.
Objective: Create a view EMP_Insert that selects all columns from the Employee table and then
insert data into this view.
USE Company;
CREATE VIEW EMP_Insert AS
SELECT *
FROM Employee;
Now, insert data into the view:
Insert into EMP_Insert values ('E12', 'Ishan', 'Galle', 'Cashier', 20000, 'D3');
To verify, check the Employee table:
SELECT * FROM Employee WHERE Eno = 'E12';
The new data will appear in the Employee table.
SQL Subqueries
A subquery, also known as an inner query or a nested query, is a query embedded within another
SQL query. Subqueries are typically used in the WHERE clause to further restrict the data
retrieved by the main query.
Key Characteristics of Subqueries:
Execution Order: The subquery (inner query) executes first, and its results are then passed to
the main query (outer query).
Usage: Subqueries can be used with SELECT, INSERT, UPDATE, and DELETE statements.
Operators: They can be used with comparison operators like =, <, >, <=, >=, IN, BETWEEN, and
multiple-row operators like ANY or ALL.
Basic Syntax:
SELECT column_names
FROM table_list
WHERE column_name OPERATOR
(SELECT column_names
FROM table_list
WHERE condition);
Outer Query (Parent Query): The main query that uses the result of the subquery.
Inner Query (Subquery): The query within parentheses that executes first.
Subqueries with the SELECT Statement
Subqueries are frequently used with SELECT statements.
Example: Finding Employees with a Salary Greater Than a Specific Employee
Objective: Identify all employees who earn more than employee 'E05', without knowing 'E05's
salary beforehand.
Step 1: Find the salary of 'E05'.
USE Company;
SELECT Salary
FROM Employee
WHERE Eno = 'E05';
Let's assume this query returns a salary of 46000.
Step 2: Find employees earning more than that salary.
SELECT [Link], [Link], [Link]
FROM Employee, Department
WHERE [Link] = [Link]
AND [Link] > 46000;
Step 3: Combine these queries into a single query using a subquery.
The subquery (SELECT Salary FROM Employee WHERE Eno = 'E05') will execute first, returning
46000. The outer query will then use this value to find employees with a salary greater than
46000.
SELECT [Link], [Link], [Link]
FROM Employee, Department
WHERE [Link] = [Link]
AND [Link] > (SELECT Salary FROM Employee WHERE Eno = 'E05');
Subqueries with OR Conditions
Subqueries can also be used to handle multiple conditions, especially when those conditions
involve dynamic values.
Objective: Find employees whose salary is greater than that of 'E05' (Amali) or greater than that
of 'E06' (Aravinda).
Individual Queries (for understanding):
1. Find Amali's salary: SELECT Salary FROM Employee WHERE Eno = 'E05'; -- Assume returns
46000
2. Find Aravinda's salary: SELECT Salary FROM Employee WHERE Eno = 'E06'; -- Assume returns
65000
3. Find employees with salary greater than 46000 OR greater than 65000: SELECT * FROM
Employee WHERE Salary > 46000 OR Salary > 65000;
Combined Subquery:
This approach uses two subqueries within the WHERE clause connected by OR.
SELECT *
FROM Employee
WHERE Salary > (SELECT Salary FROM Employee WHERE Eno = 'E05')
OR Salary > (SELECT Salary FROM Employee WHERE Eno = 'E06');