🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.
▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube
▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube
Introduction
In this chapter, we will focus on the CREATE VIEW statement in SQL. A view is a virtual table based on the result set of an SQL query. Views can simplify complex queries, enhance security by limiting data access, and provide a layer of abstraction over the underlying tables. This chapter will cover the definition, syntax, and provide examples to help you understand how to use the CREATE VIEW statement effectively.
What is a View?
A view is a virtual table that provides a way to present data in a specific format or structure. Unlike a regular table, a view does not store data physically; instead, it stores a query that dynamically retrieves data from one or more tables whenever the view is accessed.
Syntax for CREATE VIEW
Basic Syntax
CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE conditions;
view_name: The name of the view to be created.columns: The columns to be included in the view.table: The table from which the view will retrieve data.conditions: The conditions for selecting data.
Step-by-Step Example
Sample Tables
First, let's create two sample tables named employees and departments.
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
department_id INT,
salary DECIMAL(10, 2),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
Insert Sample Data
INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');
INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ravi', 'Verma', 'ravi.verma@example.com', NULL, 50000);
Creating a Simple View
To create a view that displays employee names and their corresponding department names:
CREATE VIEW employee_department_view AS
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
Querying the View
To retrieve data from the employee_department_view:
SELECT * FROM employee_department_view;
Output
| first_name | last_name | department_name |
|---|---|---|
| Ramesh | Kumar | HR |
| Sita | Patel | Finance |
| Arjun | Singh | HR |
| Priya | Sharma | Finance |
| Ravi | Verma | NULL |
In this example, the view employee_department_view provides a simplified representation of the data, combining information from both the employees and departments tables.
Creating a View with Conditions
To create a view that only includes employees with a salary greater than 55000:
CREATE VIEW high_salary_employees AS
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 55000;
Querying the View with Conditions
To retrieve data from the high_salary_employees view:
SELECT * FROM high_salary_employees;
Output
| first_name | last_name | salary |
|---|---|---|
| Sita | Patel | 60000 |
| Priya | Sharma | 62000 |
In this example, the view high_salary_employees filters the data to include only employees with a salary greater than 55000.
Updating Data Through a View
Views can sometimes be used to update data in the underlying tables, depending on the complexity of the view and the database system. Here is an example of updating data through a simple view:
CREATE VIEW employee_salary_view AS
SELECT id, first_name, last_name, salary
FROM employees;
-- Update salary through the view
UPDATE employee_salary_view
SET salary = 65000
WHERE id = 1;
Deleting a View
To delete a view when it is no longer needed:
DROP VIEW employee_department_view;
Conclusion
The CREATE VIEW statement is used for simplifying complex queries, enhancing data security, and providing a layer of abstraction over the underlying tables. This chapter covered the basic syntax for CREATE VIEW, provided examples to illustrate its use, and demonstrated how to create, query, update, and delete views. Understanding how to use views effectively will greatly enhance your ability to manage and analyze data in a relational database. In the next chapter, we will explore more about UPDATE View.
My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:
Build REST APIs with Spring Boot 4, Spring Security 7, and JWT
![[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25](https://img-c.udemycdn.com/course/750x422/6852721_b512_2.jpg "[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25")
[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25
ChatGPT + Generative AI + Prompt Engineering for Beginners
Spring 7 and Spring Boot 4 for Beginners (Includes 8 Projects)
Available in Udemy for Business
Building Real-Time REST APIs with Spring Boot - Blog App
Available in Udemy for Business
Building Microservices with Spring Boot and Spring Cloud
Available in Udemy for Business
Java Full-Stack Developer Course with Spring Boot and React JS
Available in Udemy for Business
Build 5 Spring Boot Projects with Java: Line-by-Line Coding
Testing Spring Boot Application with JUnit and Mockito
Available in Udemy for Business
Spring Boot Thymeleaf Real-Time Web Application - Blog App
Available in Udemy for Business
Master Spring Data JPA with Hibernate
Available in Udemy for Business
Spring Boot + Apache Kafka Course - The Practical Guide
Available in Udemy for Business
Comments
Post a Comment
Leave Comment