﻿ SQL - View to show salesmen with columns id, name and city

# 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: