use BikeStores;

$--1)$ Create a view named CUSTOMER$\_$OPEN$\_$ORDERS. It consists of the customer id$,$ last name, first name, zip code, and order id for every customer that has an open order. HINT: use order status from the orders table to accomplish this. $1=$ Pending; $2=$ Processing; $3=$ Rejected; and $4=$ Completed. You'll want to include the rows with $1$ and $2$ values in this view.

$--2)$ What are $2$ ways a view can increase security in a production system? Include your answer in a comment here, making sure to include sufficient explanation to show your understanding.

$--3)$ Create a command using the CUSTOMER$\_$OPEN$\_$ORDERS view to retrieve the customer last name, zip code and balance where the zip code is in New York. Hint the first three digits of New York ZIP Codes are $100-149.$ So you'll need to use a LEFT$()$ function using the first three digits of the ZIP Code to accomplish this.

$--4)$ Create view named PRODUCT$\_$SUMMARY that returns product ID$,$ product$\_$name, category name, and brand name. You will need to join three tables: categories, products, and brands to accomplish this.

$--5)$ Create a query of your new view that lists all the records from the PRODUCT$\_$SUMMARY view where the product ID is $\text{'}40\text{'}.$

$--6)$ Write but do not execute a command to give hypothetical user John permission to retrieve data from the product$\_$summary view.

$--7)$ Create a query that allows hypothetical users Sally and Mary the permission to add new categories to the Categories table.

$--8)$ Users John, Sally and Mary must be able to update the order$\_$status in the Orders table. Create a query that gives them permissions.

$--9)$ Create an Index on the Order Date field of the Orders table.

$--10)$ Going forward, we will only deliver to Texas, California, New York and New Jersey. Make sure that the customers' addresses are in one of these states by adding a check constraint to our view.

$--$ to test your constraint, try to execute the following insert statement $-$ you should get an error!

insert into customers$($first$\_$name, last$\_$name, phone, email, street, city, state, zip$\_$code$)$

values $(\text{'}$Lisa$\text{'},$'Thoendel',$\text{'}(531)622-2605\text{'},$'emthoendel@mccneb.edu',$\text{'}9110$ Giles Road','LaVista',$\text{'}$NE$\text{'},\text{'}68128\text{'})$;