w3resource

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:

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/sql-exercises/view/sql-view-exercise-3.php