w3resource

SQL VIEW Exercises: Create a view for all the salesmen with columns salesman_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:

CREATE VIEW salesown
 AS SELECT salesman_id, name, city
 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)

Inventory database model:

Inventory database model

Contribute your code and comments through Disqus.

Previous: From the following table, create a view for those salespersons belong to the city 'New York'.
Next: From the following table, create a view to find the salespersons of the city 'New York'.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

MySQL export schema without data

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

Ref: https://bit.ly/3xzB9dS