Pretty Prints Database Queries with MySQL Workbench
In this assignment , a script file should be created to contain a set of queries that the Pretty Prints Company executes each month to better manage their business. Use the database created for Print Prints that was created in Assignment 2. The script file can be created within the MySQL Workbench. Include the SQL commands necessary to satisfy the follow queries in the script.
The following queries should be included in the script:
1. CreateaviewnamedUnder_100.Itconsistsoftheitem_id,title,artist,unit_priceandorder_qtyforeveryprintwithaunit_price under 100 dollars.
2. CreateaviewnamedAllen.Itconsistsofthecustomer_id,customer_name,customer_phone,title,andartistofeachprint ordered.
3. Createaviewnamedorders.Itconsistsoftheitem_id,title,artist,unit_priceandorder_qtyforeveryprintorderedintherangeof 2014-01-01 and 2014-02-28.
4. Createaviewnamedzip_27.Itconsistsofthecustomer_name,customer_phone,title,artistanddate_shippedofeachprint ordered by a customer whose zip code begins with 27.
5. Createthefollowingindexes.Usetheindicatedindexname.
a. Createanindexnamedcustomer_idonthecustomer_idfieldinthecustomerstable.
b. Createanindexnamednameonthecustomer_namefieldinthecustomerstable.
c. Create an index named shipped on the customer_id and ship_date in the orders table.
6. Dropthenameindex.
7. Specifytheintegrityconstraintthattheunit_priceofanyprintmustbemorethan$35.
8. Ensurethatthefollowingareforeignkeys(thatis,specifyreferentialintegrity)withintheprintsdatabase. a. customer_idisaforeignkeyintheorderstable.
b. Item_idisaforeignkeyintheorderlinetable.
9. Addtotheitemstableanewcharacterfieldnamedtypethatisonecharacterinlength.
10.Change the type field in the items table to M for the print titled Skies Above.
11.Change the length of the artist field in the items table to 30.
12. What command would you use to delete the orders table from the prints database? (Do not delete the orders table.)
Is this the question you were looking for? If so, place your order here to get started!
