SQL Exercises: View to find the salesmen of the city New York

SQL VIEW : Exercise-3 with Solution

3. From the following table, create a view to locate the salespeople in the city 'New York'.

Sample table: salesman

Sample Solution:

Code to create the view

-- Creating a VIEW named 'newyorkstaff'
CREATE VIEW newyorkstaff

-- Selecting all columns (*) from the 'salesman' table
-- Filtering rows in the 'salesman' table where the 'city' column is 'New York'
AS SELECT *
FROM salesman
WHERE city = 'New York';..

Code to see the records form the view

-- Selecting all columns (*) from the 'newyorkstaff' VIEW
-- Filtering rows in the VIEW where the 'commission' column is greater than 0.13
SELECT *
FROM newyorkstaff
WHERE commission > 0.13;

output:

sqlpractice=# SELECT *
sqlpractice-# FROM newyorkstaff
sqlpractice-# WHERE commission > .13;
 salesman_id | name       |   city   | commission
-------------+------------+----------+------------
        5001 | James Hoog | New York |       0.15
(1 row)

Code Explanation:

The query in SQL given above creates a view called 'newyorkstaff' that includes all columns and rows from the 'salesman' table where the "city" column equals "New York".
Using this view, it is simple to get information about salesmen who work in New York and not have to write the same SELECT statement over and over again.

Inventory database model:

Contribute your code and comments through Disqus.

Previous SQL Exercise: View to show salesmen with columns id, name and city.
Next SQL Exercise: View to count many customers at each lavel of grade.