How to create views in SQL? Explain with an example.

How to create views in SQL? Explain with an example.

Answer:-

In SQL, the command to create a view is CREATE VIEW. Views are given a virtual table name, a list of attribute names, and a query to specify their contents. If the view attributes don’t involve functions or arithmetic operations, new attribute names aren’t necessary as they would match the names of the attributes from the defining tables by default.

Example 1:

CREATE VIEW WORKS_ON1 AS
SELECT Fname, Lname, Pname, Hours
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn=Essn AND Pno=Pnumber;

Example 2:

CREATE VIEW DEPT_INFO(Dept_name, No_of_emps, Total_sal) AS
SELECT Dname, COUNT(*), SUM(Salary)
FROM DEPARTMENT, EMPLOYEE
WHERE Dnumber=Dno
GROUP BY Dname;

In Example 1, new attribute names are not specified for the view WORKS_ON1, so it inherits attribute names from the defining tables. In Example 2, new attribute names are explicitly specified for the view DEPT_INFO, using a one-to-one correspondence between the attributes in the CREATE VIEW clause and those in the SELECT clause of the defining query.