Creating and Using SQL View: Data Security and Ease with Virtual Tables

💡 What Will You Learn in This Guide?

In this guide, you will get to know the View structure, which is a powerful feature of SQL.
Views simplify complex queries, secure data access, and simplify database management.
You will learn step by step to create Views that combine multiple tables, contain calculated columns, and grant access only to certain columns.

🧠 Basic Concept: What is SQL View?

View is a virtual table that does not hold physical data.
Dynamically generates results from the query SELECT where it is defined.

In this way:

• User access may be restricted.
• Complex JOIN queries can be simplified.
• Data duplication is avoided.
• Reporting processes are accelerated.

---

⚙️ 1️⃣ Sample Database Setup

Our scenario: You run a dog grooming service and keep employees' and dogs' information in separate tables.
We will use VIEW to represent these two tables as a single virtual table.

Connect to MySQL

mysql -u genixnode_dev -p

This command connects to MySQL with the user genixnode_dev.

Create the Database

CREATE DATABASE kopek_bakim_db;
USE kopek_bakim_db;

Creates and activates a new dog_maintenance_db database.

Employees Table

CREATE TABLE calisanlar (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(20)
);

It stores the identity and name information of employees.

Dogs Chart

CREATE TABLE kopekler (
  dog_id INT PRIMARY KEY,
  dog_name VARCHAR(20),
  walker INT,
  walk_distance DECIMAL(3,2),
  meals_perday INT,
  cups_permeal DECIMAL(3,2),
  FOREIGN KEY (walker) REFERENCES calisanlar(emp_id)
);

Keeps each dog's name, handler and feeding information.

Sample Data:

INSERT INTO calisanlar VALUES
(1, 'Ali'),
(2, 'Ayşe'),
(3, 'Can');

INSERT INTO kopekler VALUES
(1, 'Fıstık', 1, 5.00, 3, 1.00),
(2, 'Karabaş', 3, 6.50, 3, 1.25),
(3, 'Pıtırcık', 3, 1.25, 2, 0.25),
(4, 'Boncuk', 2, 2.75, 2, 0.75),
(5, 'Şanslı', 1, 4.50, 3, 2.00),
(6, 'Cesur', 1, 4.50, 3, 2.00),
(7, 'Maya', 3, 3.00, 2, 1.50);

---

🧩 2️⃣ Creating and Querying Views

Creating a View

Let's create a view (walk chart) that combines the information of the employee and the dogs connected to him.

CREATE VIEW yuruyus_cizelgesi AS
SELECT emp_name, dog_name, walk_distance, meals_perday, cups_permeal
FROM calisanlar
JOIN kopekler ON emp_id = walker;

This view joins two tables with a JOIN.

View Query

SELECT * FROM yuruyus_cizelgesi;

Queries the view just like a table.

Sample Output:

emp_name	dog_name	walk_distance	meals_perday	cups_permeal
Ali	Peanut	5.00	3	1.00
Ali	Lucky	4.50	3	2.00
Ayşe	Bead	2.75	2	0.75
Jan	Karabaş	6.50	3	1.25
Jan	Pıtırcık	1.25	2	0.25

---

📊 3️⃣ Calculation and Analysis on View

Views are ideal for analysis with aggregate functions.

Longest Walking Distance

SELECT emp_name, MAX(walk_distance) AS en_uzun_yuruyus
FROM yuruyus_cizelgesi
GROUP BY emp_name;

Lists the longest distance each employee has walked.

Output:

emp_name	longest_walk
Ali	5.00
Ayşe	2.75
Jan	6.50

---

🔐 4️⃣ Security: Restricting Access with View

Views can be used to grant the user access to only certain data.

Creating a New User

CREATE USER 'ofis_yonetici'@'localhost' IDENTIFIED BY 'sifre123';

###Grant View Only

GRANT SELECT ON kopek_bakim_db.yuruyus_cizelgesi TO 'ofis_yonetici'@'localhost';

This user can only read the walk_chart view.

🔄 5️⃣ View Update and Deletion

If the Base Table is Updated, the View is Updated as well

INSERT INTO kopekler VALUES (8, 'Şimşek', 2, 3.50, 3, 1.00);
SELECT * FROM yuruyus_cizelgesi;

The view is automatically updated when new data is added.

Update View Structure

CREATE OR REPLACE VIEW yuruyus_cizelgesi AS
SELECT emp_name, dog_name, walk_distance, meals_perday,
(cups_permeal * meals_perday) AS toplam_mama
FROM calisanlar JOIN kopekler ON emp_id = walker;

Updates the view to calculate the total amount of food for the day.

Deleting a View

DROP VIEW yuruyus_cizelgesi;

Permanently deletes the view.

---

❓ Frequently Asked Questions (FAQ)

1. Can I add or delete data to Views?

Usually no. However, INSERT or UPDATE may be possible in simple views based on a single table.

2. Does View increase performance?

Views do not directly increase performance, but they increase developer productivity by reducing code duplication.

3. What is the difference between View and table?

The table keeps data on disk; view only shows the result of a query.

4. Are views included in backup files?

Yes, but only the definition (CREATE VIEW) part is included; not the data itself.

5. In what situations should I use Views?

To store frequently repeated queries, limit access and speed up reporting.

---

🚀 Result

Using the SQL View structure in this guide:

Simplifying complex queries,

Restricting user access,

You learned how to manage views dynamically.

Now you can make your database both more secure and more manageable.