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.

  • Line 11: The JOIN is performed with Sales and ProductCategories on the columns CategoryID in both the tables.

  • Line 12: The result of the previous JOIN is joined with Employees table using EID columns.

  • Line 14: The SELECT statement displays the content of the created view SalesTransactionsByCategory.

Recalling relevant concepts

We have covered the following concepts in this question:

  • Creating a view

  • Using JOIN

  • Selective columns

  • Aliases

Let’s discuss the concepts used in the solution:

  • We use CREATE VIEW to create a view. Unlike traditional tables, it doesn’t store data physically but displays data from one or more tables in a specific format. Views simplify complex queries, limit data access for security purposes, and improve efficiency. Following are the examples of creating views:

/* Create a view */
CREATE VIEW ExampleView AS
SELECT      ColumnName
FROM        TableName
WHERE       condition;
/* Retrieve data through view */ 
SELECT * FROM ExampleView;
/* Create a view using WITH*/
CREATE VIEW ExampleAnotherView AS
WITH SomeName AS (
      SELECT      ColumnName
      FROM        TableName
      WHERE       condition
)
SELECT * FROM SomeName;
/* Retrieve data through view */ 
SELECT * FROM ExampleAnotherView;
Syntax for creating and using view in SQL

  • JOIN (short for INNER JOIN) combines rows from two or more tables based on their related columns. JOIN or INNER JOIN returns records that have matching values in both tables.

SELECT a.ColumnName
FROM   TableName1 AS a 
JOIN   TableName2 AS b 
ON     a.CommonColumn = b.CommonColumn;

  • JOIN can also be applied by specifying columns in the USING clause, as shown below:

SELECT a.ColumnName
FROM   TableName1 AS a 
JOIN   TableName2 AS b
USING  (CommonColumn);

  • We specify the columns to retrieve in the SELECT query; it retrieves the records based on specified conditions. The FROM clause is used to specify the table from which we want to retrieve the data. 

SELECT Column1, Column2, Columnn
FROM   TableName;

  • Aliases are used to give a temporary name to tables or columns. An alias only exists for the duration of the query. Usually, the AS keyword is used for specifying an alias. However, it can also be omitted. Examples are as follows:

/* Column alias */
SELECT ColumnName AS ColAlias
FROM   TableName;
/* Column alias with space */
SELECT ColumnName AS "Col Alias"
FROM   TableName;
/* Table alias */
SELECT t.ColumnName
FROM   TableName AS t;
/* Table alias without using as */
SELECT t.ColumnName
FROM   TableName t;
Syntax for column and table aliases in SQL

Alternate solutions

Let’s discuss the alternate solutions for the same problem in this section:

Using Common Table Expression (CTE)

We can use the common table expression (CTE) to find the desired output. The CTE defined within a view will get the records based on the requirements. Within the view definition, after defining a CTE, the records are retrieved using the SELECT query. The data of view can be viewed using the SELECT query. Let’s take a look at the following query:

MySQL
CREATE VIEW SalesTransactionsByCategory AS
WITH        SalesDetails AS (
            SELECT S.SalesID, E.EName AS EmployeeName,
                   PC.CategoryName AS Category,
                   S.SalesAmount, S.Month
            FROM   Sales S
            JOIN   Employees E ON S.EID = E.EID
            JOIN   ProductCategories PC ON S.CategoryID = PC.CategoryID
)
SELECT SalesID AS 'Sales ID', EmployeeName AS 'Employee Name', Category,
       SalesAmount  AS 'Sales Amount', Month
FROM   SalesDetails;
SELECT * FROM SalesTransactionsByCategory ORDER BY Category ASC;

Using subqueries

We can use the subqueries to find the desired output without creating a view. The subquery will retrieve the data from the columns in the SELECT statement. Initially, these columns are not part of the same table and we need to fetch the data from different tables. Let’s take a look at the following query:

MySQL
SELECT   SalesID AS 'Sales ID',
         (SELECT EName FROM Employees E WHERE E.EID = S.EID)
         AS 'Employee Name', 
         (SELECT CategoryName FROM ProductCategories PC WHERE PC.CategoryID = S.CategoryID)
         AS Category, 
         SalesAmount AS 'Sales Amount',
         Month
FROM     Sales S
ORDER BY Category ASC;

Similar interview questions

Let’s discuss the variations of the questions in this section:

View for employee responsibility by product

Create a view, EmployeeProductResponsibility, that lists each employee being responsible for each product. The view should show Employee Name, Category, and Product Name.

MySQL
/* Write your query here */

If you’re unsure how to do this, click the “Show Solution” button.

View of products and their categories

Attempt the following quiz to test your understanding of views.

Technical Quiz
1.

(Select all that apply.) Select the correct options to create a view showing products and their categories. Multi-select

A.
CREATE VIEW ProductCategoryList AS
SELECT      P.PName AS 'Product Name', PC.CategoryName AS Category
FROM        Products P
JOIN        ProductCategories PC ON P.CategoryID = PC.CategoryID;
B.
CREATE VIEW ProductCategoryList AS
SELECT      PName AS 'Product Name', CategoryID AS Category
FROM        Products;

SELECT * FROM ProductCategoryList;
C.
CREATE VIEW ProductCategoryList AS
SELECT      P.PName AS 'Product Name', 
            (SELECT CategoryName 
             FROM ProductCategories 
             WHERE CategoryID = P.CategoryID) AS Category
FROM        Products P;
D.
CREATE VIEW ProductCategoryList AS
WITH        ProductWithCategory AS (
            SELECT P.PName AS 'Product Name', PC.CategoryName AS Category
            FROM Products P
            JOIN ProductCategories PC ON P.CategoryID = PC.CategoryID
)
SELECT * FROM ProductWithCategory;
1 / 1

Brain teaser

Solve the following puzzle to create a view to show each Employee along with the product category they manage.

If you’re unsure how to do this, click the “Show Solution” button.

Key terms

We have covered the following key terms in the lesson:

SELECT

FROM

WHERE

AS

CREATE VIEW

USING

ON

JOIN

WITH