Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Creating Views

Explore how to create SQL views to simplify complex data queries and improve data access. Learn the use of CREATE VIEW, JOINs, aliases, and subqueries to display combined data from multiple tables effectively.

We'll cover the following...

Solution

The solution is given below:

MySQL
/*Query to create view named SalesTransactionsByCategory
to show each sales transaction with the 
corresponding category by each employee every month*/
CREATE VIEW SalesTransactionsByCategory AS
SELECT      S.SalesID AS 'Sales ID', 
            E.EName AS 'Employee Name', 
            PC.CategoryName AS Category, 
            S.SalesAmount AS 'Sales Amount', 
            S.Month AS Month
FROM        Sales S
JOIN        ProductCategories PC ON S.CategoryID = PC.CategoryID
JOIN        Employees E ON S.EID = E.EID;
SELECT * FROM SalesTransactionsByCategory ORDER BY Category ASC;

Code explanation

The explanation of the solution code is given below:

  • Line 4: The CREATE VIEW statement creates a view named SalesTransactionsByCategory.

  • Lines 5–9: The columns of the view are Sales ID, Employee Name, Category, Sales Amount, and Month.

  • Line 10: The data is retrieved from the Sales table.

  • ...