Learning Goal: I’m working on a databases exercise and need an explanation and answer to help me learn.
6.6m Create new tables from full Redcat DB (MySQL)
As you learned in Chapter 2 problems, in order to make modifications to database tables and records, you need to have your own database schema.
In this and the following problems, you will use your student credentials to first create tables with and extract data from Redcat Shoes and then modify the data in those tables.
Because you are using your own database schema, you are not automatically connected to Redcat shoes. To retrieve data from the Redcat Shoes database the required syntax is to prefix table names with the database name. For example to access data in the Purchase table, you will specify it as “redcat.purchase”.
Also remember that as you build tables and populate them with data in your own database schema, the tables and data remain unless you remove it. If you make a mistake or want to rerun your SQL you will need to remove tables. To do so use the Drop Table statement.
Edit
1. Enter your entire script below. Do not include statements to drop objects as your script will be executed against a clean schema. You may include select statements in sub queries in your insert statements, but do not include select queries that return records for display.
Create a new table (name it Purchase61) and insert into it the Purchase records of employee 61 that were made after Christmas in 2014 (i.e. between December 26, 2014 and December 31, 2014. (Use between. Use date format of ‘yyyy-mm-dd’)
Note: You will be working in your own workspace. To get access to tables in the Redcat database workspace, you will need to add a prefix, i.e. Redcat.Purchase.
2. Enter your entire script below. Do not include statements to drop objects as your script will be executed against a clean schema. You may include select statements in sub queries in your insert statements, but do not include select queries that return records for display.
Create a new table (name it PurchaseItem61) and insert into it the PurchaseItem records of employee 61 that were made after Christmas in 2014 (i.e. between December 26, 2014 and December 31, 2014. (Use between. Use date format of ‘yyyy-mm-dd’)
Note: You will be working in your own workspace. To get access to tables in the Redcat database workspace, you will need to add a prefix, i.e. Redcat.PurchaseItem.
3. Enter your entire script below. Do not include statements to drop objects as your script will be executed against a clean schema. You may include select statements in sub queries in your insert statements, but do not include select queries that return records for display.
In the previous problems you created two new tables, Purchase61 and PurchaseItem61. In this problem you are asked to delete records.
Delete from PurchaseItem61 the records for the manufacturer whose ID is 630.
4. Enter your entire script below. Do not include statements to drop objects as your script will be executed against a clean schema. You may include select statements in sub queries in your insert statements, but do not include select queries that return records for display.
Delete from Purchase61 the records for the manufacturer whose ID is 630.
5. Enter your entire script below. Do not include statements to drop objects as your script will be executed against a clean schema. You may include select statements in sub queries in your insert statements, but do not include select queries that return records for display.
6. Enter your entire script below. Do not include statements to drop objects as your script will be executed against a clean schema. You may include select statements in sub queries in your insert statements, but do not include select queries that return records for display.
In Chapter 5 you learned about the Alter statement to change the structure of tables in the database. In this problem use the Purchase61 and PurchaseItem61 tables that you created and modified in previous problems. In a previous problem you deleted records from the Purchase61 table and the PurchaseItem61 tables. By adding a foreign key constraint, you can simplify that process so that when purchase records are deleted, then the corresponding purchaseItem records are automatically deleted by the DBMS.
Alter the Purchase61 table and add PurchaseID as a primary key. Also alter the PurchaseItem61 table so that the PurchaseID is a foreign key constraint from Purchase61 table, including that when records are deleted in Purchase61 the corresponding records in PurchaseItem61 are also deleted.
Note: Do not delete any records, just configure the foreign key so that delete happens automatically.
Continue to use the Purchase61 and PurchaseItem61 tables that you created and modified in the previous problems.
Increase the shippping cost of every purchase from Manufacturers in Massachusetts (‘MA’) by 10%. Round your calculations to two decimal points.
Hint: Use the IN clause. Do not use a Join.V
