Welcome to Database Laboratory
Welcome to Database Laboratory
Here's a SQL statement to create the table:
Now, let’s insert some sample records into the Employees table:
Now, let's create a view to show employees from the Engineering department with their full names and salaries:
You can then query the view to see the results:
The output of the above query would be:
EmployeeID
FullName
Salary
1
John Doe
70000.00
3
Emily Jones
80000.00
A view is created using the CREATE VIEW statement. You can define the columns and the data that the view will show.
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example:
CREATE VIEW EngineeringEmployees AS
SELECT
EmployeeID,
CONCAT(FirstName, ' ', LastName) AS FullName,
Salary
FROM
Employees
WHERE
Department = 'Engineering';
To retrieve data from a view, you use a SELECT statement just like you would for a table.
Example:
SELECT * FROM EngineeringEmployees;
You can update the underlying table data through a view if the view is updatable (i.e., it meets certain criteria). Use the UPDATE statement with the view name.
Example:
UPDATE EngineeringEmployees
SET Salary = Salary * 1.10
WHERE EmployeeID = 1; -- Give a 10% raise to John Doe
Similar to updates, you can delete rows from the underlying table through the view if it’s updatable.
Example:
DELETE FROM EngineeringEmployees
WHERE EmployeeID = 3; -- Remove Emily Jones
If you no longer need a view, you can remove it using the DROP VIEW statement.
Syntax:
DROP VIEW view_name;
Example:
DROP VIEW EngineeringEmployees;
If you need to change the definition of an existing view, you can use the CREATE OR REPLACE VIEW statement.
Example:
CREATE OR REPLACE VIEW EngineeringEmployees AS
SELECT
EmployeeID,
CONCAT(FirstName, ' ', LastName) AS FullName,
Salary,
HireDate
FROM
Employees
WHERE
Department = 'Engineering';
You can create views that combine data from multiple tables using JOIN.
Example: Assuming there’s another table called Departments:
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50)
);
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'Engineering'),
(2, 'Marketing'),
(3, 'Sales'),
(4, 'HR');
Now create a view that joins these two tables:
CREATE VIEW EmployeeDepartment AS
SELECT
e.EmployeeID,
CONCAT(e.FirstName, ' ', e.LastName) AS FullName,
d.DepartmentName
FROM
Employees e
JOIN
Departments d ON e.Department = d.DepartmentName;
SELECT * FROM EmployeeDepartment;
Some databases support materialized views, which store the result set physically. These can be refreshed periodically.
Example:
CREATE MATERIALIZED VIEW EmployeeSalaries AS
SELECT
Department,
AVG(Salary) AS AverageSalary
FROM
Employees
GROUP BY
Department;
Create a view with CREATE VIEW.
Query a view with SELECT.
Update/Delete through the view if it's updatable.
Drop the view with DROP VIEW.
Alter a view with CREATE OR REPLACE VIEW.
Join multiple tables in a view.
Consider materialized views for performance.