Create a view named vwPenguin that includes the following fields: book ID, title, type (name) and price for every book published by Penguin USA. Sort the list by type and then price. Using vwPenguin, display the Penguin USA books with a price less than $10. Create a view named vwHardCover that includes the following fields: bookd ID, title, publisher name and price. Include only hard cover books (not paperback). Sort the list by publisher name and then price descending. Create a view named vwBranchInventory the includes the following fields: branch location and total number of books in inventory at that branch. Sort the results by location. Provide the command to create an index of your choice on a table in the Henry Books database. Why do you feel this index would be valuable? Extra Credit (+4 Points Max) Provide the commands (one command each when possible) to provide the following database security. You will not be able to test queries using the PortableApps version of XAMPP and MySQL Workbench. User Ashton must be able to retrieve data from the Parts table. Users Kelly and Morgan must be able to add new orders and order lines. User James must be able to change the price for all parts. User Danielson must be able to delete customers; All users must be able to retrieve each customer’s ID, name, street, city, state, and zip code. User Perez must be able to create an index on the Orders table. User Washington must be able to change the structure of the Parts table. User Grinstead must have all privileges on the Orders table.
Create a view named vwPenguin that includes the following fields: book ID, title, type (name) and price for every book published by Penguin USA. Sort the list by type and then price. Using vwPenguin, display the Penguin USA books with a price less than $10. Create a view named vwHardCover that includes the following fields: bookd ID, title, publisher name and price. Include only hard cover books (not paperback). Sort the list by publisher name and then price descending. Create a view named vwBranchInventory the includes the following fields: branch location and total number of books in inventory at that branch. Sort the results by location. Provide the command to create an index of your choice on a table in the Henry Books database. Why do you feel this index would be valuable? Extra Credit (+4 Points Max) Provide the commands (one command each when possible) to provide the following database security. You will not be able to test queries using the PortableApps version of XAMPP and MySQL Workbench. User Ashton must be able to retrieve data from the Parts table. Users Kelly and Morgan must be able to add new orders and order lines. User James must be able to change the price for all parts. User Danielson must be able to delete customers; All users must be able to retrieve each customer’s ID, name, street, city, state, and zip code. User Perez must be able to create an index on the Orders table. User Washington must be able to change the structure of the Parts table. User Grinstead must have all privileges on the Orders table.
SQL%205%20-%20Views,%20Indexes%20&%20Security.docx