# SQL Exercises with Solution - VIEW

## SQL [16 exercises with solution]

1. From the following table, create a view for those salespersons belong to the city 'New York'.

Sample table: salesman

Sample Output:

```sqlpractice=# select * from newyorkstaff;
salesman_id |    name    |   city   | commission
-------------+------------+----------+------------
5001 | James Hoog | New York |       0.15
(1 row)
```

Click me to see the solution

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

Sample table: salesman

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)
```

Click me to see the solution

3. From the following table, create a view to find the salespersons of the city 'New York'.

Sample table: salesman

output:

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

Click me to see the solution

4. From the following table, create a view to count the number of customers in each grade.

Sample table: customer

output:

```sqlpractice=# SELECT *
sqlpractice-# WHERE number = 2;
-------+--------
|      2
200 |      2
300 |      2
(3 rows)
```

Click me to see the solution

5. From the following table, create a view to count the number of unique customer, compute average and total purchase amount of customer orders by each date.

Sample table : orders

output:

```sqlpractice=# SELECT *
sqlpractice-# FROM totalforday;
ord_date  | count |          avg          |   sum
------------+-------+-----------------------+---------
2012-04-25 |     1 | 3045.6000000000000000 | 3045.60
2012-06-27 |     1 |  250.4500000000000000 |  250.45
2012-07-27 |     1 | 2400.6000000000000000 | 2400.60
2012-08-17 |     3 |   95.2633333333333333 |  285.79
2012-09-10 |     3 | 2326.3833333333333333 | 6979.15
2012-09-22 |     1 |  322.0000000000000000 |  322.00
2012-10-05 |     2 |  132.6300000000000000 |  265.26
2012-10-10 |     2 | 2231.9150000000000000 | 4463.83
(8 rows)
```

Click me to see the solution

6. From the following tables, create a view to get the salesperson and customer by name. Return order name, purchase amount, salesperson ID, name, customer name.

Sample table: salesman

Sample table: customer

Sample table: orders

output:

```sqlpractice=# SELECT *
sqlpractice-# FROM nameorders
sqlpractice-# WHERE name = 'Mc Lyon';
ord_no | purch_amt | salesman_id |  name   |   cust_name
--------+-----------+-------------+---------+----------------
70010 |   1983.43 |        5006 | Mc Lyon | Fabian Johnson
70015 |    322.00 |        5006 | Mc Lyon | Varun
(2 rows)
```

Click me to see the solution

7. From the following tables, create a view to find the salesperson who handles a customer who makes the highest order of a day. Return order date, salesperson ID, name.

Sample table: salesman

Sample table: orders

output:

```sqlpractice=# SELECT *
sqlpractice-# FROM elitsalesman;
ord_date  | salesman_id |     name
------------+-------------+--------------
2012-08-17 |        5003 | Lauson Hense
2012-07-27 |        5001 | James Hoog
2012-09-10 |        5001 | James Hoog
2012-10-10 |        5003 | Lauson Hense
2012-06-27 |        5002 | Nail Knite
2012-04-25 |        5001 | James Hoog
2012-10-05 |        5002 | Nail Knite
2012-09-22 |        5006 | Mc Lyon
(8 rows)
```

Click me to see the solution

8. From the following tables, create a view to find the salesperson who handles the customer with the highest order, at least 3 times on a day. Return salesperson ID and name.

Sample table: customer

Sample table: elitsalesman

output:

```sqlpractice=# SELECT *
sqlpractice-# FROM incentive;
salesman_id | name
-------------+------------
5001 | James Hoog
(1 row)
```

Click me to see the solution

9. From the following table, create a view to find all the customers who have the highest grade. Return all the fields of customer.

Sample table: customer

output:

```sqlex=# select * from highgrade;
customer_id |   cust_name    |  city  | grade | salesman_id
-------------+----------------+--------+-------+-------------
3008 | Julian Green   | London |   300 |        5002
3004 | Fabian Johnson | Paris  |   300 |        5006
(2 rows)
```

Click me to see the solution

10. From the following table, create a view to count number of the salesperson in each city. Return city, number of salespersons.

Sample table: salesman

output:

```sqlpractice-# FROM citynum;
city   | count
----------+-------
London   |     1
New York |     1
Paris    |     2
Rome     |     1
|     1
(5 rows)

```

Click me to see the solution

11. From the following table, create a view to compute average purchase amount and total purchase amount for each salesperson. Return name, average purchase and total purchase amount. (Assume all names are unique).

Sample table: salesman

Sample table: orders

output:

```sqlpractice=# SELECT *
sqlpractice-# FROM norders;
name     |          avg          |   sum
--------------+-----------------------+----------
Mc Lyon      | 1152.7150000000000000 |  2305.43
James Hoog   | 2817.8650000000000000 | 11271.46
Pit Alex     |  270.6500000000000000 |   270.65
Lauson Hense | 1295.4500000000000000 |  2590.90
Paul Adam    |   87.6450000000000000 |   175.29
Nail Knite   |  466.3166666666666667 |  1398.95
(6 rows)
```

Click me to see the solution

12. From the following tables, create a view to find those salespeople who handle more than one customer. Return all the fields of salesperson.

Sample table: salesman

Sample table: customer

output:

```sqlpractice=# SELECT *
sqlpractice-# FROM mcustomer;
salesman_id |     name     |   city   | commission
-------------+--------------+----------+------------
5002 | Nail Knite   | Paris    |       0.13
5001 | James Hoog   | New York |       0.15
(2 rows)
```

Click me to see the solution

13. From the following tables, create a view that shows all matches of customers with salesperson such that at least one customer in the city of customer served by a salesperson in the city of the salesperson.

Sample table: salesman

Sample table: customer

output:

```sqlpractice=# SELECT *
sqlpractice-# FROM citymatch;
custcity  | salescity
------------+-----------
Seattle    | Paris
Moscow     | Rome
New York   | New York
NC         |
Paris      | Paris
....
```

Click me to see the solution

14. From the following table, create a view to get number of orders in each day. Return order date and number of orders.

Sample table: orders

output:

```sqlpractice=# SELECT *
sqlpractice-# FROM dateord;
ord_date  | odcount
------------+---------
2012-10-05 |       2
2012-08-17 |       3
2012-07-27 |       1
2012-09-22 |       1
....
```

Click me to see the solution

15. From the following tables, create a view to find the salespersons who issued orders on October 10th, 2012. Return all the fields of salesperson.

Sample table: salesman

Sample table: orders

output:

```sqlpractice=# SELECT *
sqlpractice-# FROM salesmanonoct;
salesman_id |     name     | city  | commission
-------------+--------------+-------+------------
5006 | Mc Lyon      | Paris |       0.14
5003 | Lauson Hense |       |       0.12
(2 rows)
```

Click me to see the solution

16. From the following table, create a view to find the salespersons who issued orders on either August 17th, 2012 or October 10th, 2012. Return salesperson ID, order number and customer ID.

Sample table: orders

output:

```sqlpractice=# SELECT *
sqlpractice-# FROM sorder;
salesman_id | ord_no | customer_id
-------------+--------+-------------
5003 |  70004 |        3009
5006 |  70010 |        3004
5003 |  70003 |        3009
5007 |  70011 |        3003
5007 |  70014 |        3005
(5 rows)
```

Click me to see the solution

More to Come!

Query visualizations are generated using Postgres Explain Visualizer (pev)

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.

﻿

## SQL: Tips of the Day

How to restore a dump file from mysqldump?

It should be as simple as running this:

```mysql -u <user> -p < db_backup.dump
```

If the dump is of a single database you may have to add a line at the top of the file:

```USE <database-name-here>;
```

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