SQL Exercises: View to show salesmen with columns id, name and city

SQL VIEW: Exercise-2 with Solution

2. From the following table, create a view for all salespersons. Return salesperson ID, name, and city.

Sample table: salesman

Sample Solution:

-- Creating a VIEW named 'salesown'
CREATE VIEW salesown

-- Selecting specific columns (salesman_id, name, city) from the 'salesman' table
AS SELECT salesman_id, name, city

-- Retrieving data from the 'salesman' table and storing it in the VIEW
FROM salesman;

Original salesown

Sample Output:

sqlpractice=# SELECT *
sqlpractice-# FROM salesown;
 salesman_id |     name     |   city
-------------+--------------+----------
        5002 | Nail Knite   | Paris
        5005 | Pit Alex     | London
        5006 | Mc Lyon      | Paris
        5003 | Lauson Hense |
        5007 | Paul Adam    | Rome
        5001 | James Hoog   | New York
(6 rows)

Now UPDATE the city name which salesman_id is '5007'.

Code

UPDATE salesown
SET city = 'London'
WHERE salesman_id = 5007;

output

sqlpractice=# SELECT *
sqlpractice-# FROM salesown;
 salesman_id |     name     |   city
-------------+--------------+----------
        5002 | Nail Knite   | Paris
        5005 | Pit Alex     | London
        5006 | Mc Lyon      | Paris
        5003 | Lauson Hense |
        5001 | James Hoog   | New York
        5007 | Paul Adam    | London
(6 rows)

Code Explanation:

The above statement in SQL creates a view called 'salesown' that includes only the "salesman_id", "name", and "city" columns from the 'salesman' table.
This view can be used to quickly retrieve information about salesmen such as their name and the city they work in.

Inventory database model:

Contribute your code and comments through Disqus.

Previous SQL Exercise: View for the salesmen who belong to the city New York.
Next SQL Exercise: View to find the salesmen of the city New York.