SQL Views
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.
A view is created with the CREATE VIEW statement.
CREATE VIEW SYNTAX
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Creating View from a single table:
In this example we will create a View named DetailsView from the table Stu-
dentDetails.
Query:
mysql> CREATE VIEW bookV (ID,TITLE,PUBLISHER,YEAR, PRICE)
AS SELECT ID, TITLE, PUBLISHER, YEAR, PRICE
FROM BOOK
WHERE YEAR > 1995;
To see the data in the View :
SELECT * FROM DetailsView;
Creating View from multiple tables:
To create a View from multiple tables we can simply include multiple tables in
the SELECT statement. Query:
mysql> CREATE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS, Student-
Marks.MARKS
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;
To display data of View Marks View:
SELECT * FROM MarksView;
DATA QUERY AND MANIPULATION OPERATIONS
WITH VIEWS
mysql> CREATE VIEW bookV (ID,TITLE,PUBLISHER,YEAR, PRICE)
AS SELECT ID, TITLE, PUBLISHER, YEAR, PRICE
FROM BOOK
WHERE YEAR > 1935;
+-----------+-------------+-------------+-----------+-----------+
| ID | TITLE | PUBLISHER | YEAR | PRICE |
+-----------+-------------+-------------+-----------+-----------+
| 8984 | python | McGraw-hill | 1994 | 5000 |
| 5678 | c with DSA | Kim | 1999 | 9555 |
| 4948 | Java | McGraw-hill | 1745 | 8500 |
| 4563 | Networking | McGraw-hill | 1945 | 7000 |
| 4536 | c++ | Denis | 2000 | 3500 |
+-----------+-------------+-------------+-----------+-----------+
1. Issuing the following SQL statements
Query:
mysql> SELECT *
fROM bookV
WHERE PUBLISHER = “McGraw-hill”;
Output:
+-----------+-------------+-------------+-----------+-----------+
| ID | TITLE | PUBLISHER | YEAR | PRICE |
+-----------+-------------+-------------+-----------+-----------+
| 8984 | python | McGraw-hill | 1994 | 5000 |
| 4948 | Java | McGraw-hill | 1745 | 8500 |
| 4563 | Networking | McGraw-hill | 1945 | 7000 |
+-----------+-------------+-------------+-----------+-----------+
2. Update Operation
It is use to update the view
Query:
mysql> UPDATE bookV
SET PRICE = PRICE * 2
WHERE PUBLISHER = “McGraw-hill”;
Output:
+-----------+-------------+-------------+-----------+------------+
| ID | TITLE | PUBLISHER | YEAR | PRICE |
+-----------+-------------+-------------+-----------+------------+
| 8984 | python | McGraw-hill | 1994 | 10000 |
| 4948 | Java | McGraw-hill | 1745 | 17000 |
| 4563 | Networking | McGraw-hill | 1945 | 14000 |
+-----------+-------------+-------------+-----------+------------+
3. Insert Operation
It use to insert the data in the view
Query:
mysql> INSERT INTO bookV(ID,TITLE,PUBLISHER,YEAR,PRICE)
VALUES ("4949", "abduction", "Kim", 1987, 5000);
Output:
+-----------+-------------+-------------+-----------+-----------+
| ID | TITLE | PUBLISHER | YEAR | PRICE |
+-----------+-------------+-------------+-----------+-----------+
| 8984 | python | McGraw-hill | 1984 | 5000 |
| 5678 | c with DSA | Kim | 1999 | 9555 |
| 4948 | Java | McGraw-hill | 1745 | 8500 |
| 4563 | Networking | McGraw-hill | 1945 | 7000 |
| 4536 | c++ | Denis | 2000 | 3500 |
| 4949 | Abduction | Kim | 1987 | 5000 |
+-----------+-------------+-------------+-----------+-----------+
4. Delete statement:
Query:
mysql> DELETE FROM bookV
WHERE PUBLISHER = “McGraw-Hill”;
Output:
+-----------+-------------+-------------+-----------+-----------+
| ID | TITLE | PUBLISHER | YEAR | PRICE |
+-----------+-------------+-------------+-----------+-----------+
| 5678 | c with DSA | Kim | 1999 | 9555 |
| 4536 | c++ | Denis | 2000 | 3500 |
| 4949 | Abduction | Kim | 1987 | 5000 |
+-----------+-------------+-------------+-----------+-----------+
5. The Check Option
Consider the following UPDATE operation on bookV :
Query:
mysql> UPDATE bookV
SET YEAR = 1984
WHERE TITLE = “Abduction”;
Output:
+-----------+-------------+-------------+-----------+-----------+
| ID | TITLE | PUBLISHER | YEAR | PRICE |
+-----------+-------------+-------------+-----------+-----------+
| 8984 | python | McGraw-hill | 1984 | 5000 |
| 5678 | c with DSA | Kim | 1999 | 9555 |
| 4948 | Java | McGraw-hill | 1745 | 8500 |
| 4563 | Networking | McGraw-hill | 1945 | 7000 |
| 4536 | c++ | Denis | 2000 | 3500 |
| 4949 | Abduction | Kim | 1984 | 5000 |
+-----------+-------------+-------------+-----------+-----------+
