Databases Query Assignment

workshop instructions Write the SQL command to create the customer and the invoice tables using the InnoDB database engine. Define the PK for each table and a FK constraint on CUS_CODE of the invoice table. Run the following query: select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE and show the output. Write the SQL command to insert a record in the invoice table with a CUS_CODE that does not exist in the customer table. Show the output message returned by mysql. Drop the FK constraint on CUS_CODE of the invoice table and redefine it with “on HYPERLINK "[login to view URL]"update HYPERLINK "[login to view URL]" HYPERLINK "[login to view URL]"cascade” and “on HYPERLINK "[login to view URL]"delete HYPERLINK "[login to view URL]" HYPERLINK "[login to view URL]"cascade” clauses. Write sql commands to insert one record in the customer table and one record in the invoice table. Check how Foreign keys help guarantee referential integrity within the database. Check the data in both tables and show the output. Write a sql command to change the CUS_CODE in the customer table to any new value. Show what message do you get from the system. Check the data in both tables and show the output. Write a sql command to delete all rows from the customer table. Show what message do you get from the system. Check the data in both tables and show the output. Drop the FK constraint on CUS_CODE of the invoice table and redefine it with “on HYPERLINK "[login to view URL]"update HYPERLINK "[login to view URL]" HYPERLINK "[login to view URL]"restrict” and “on HYPERLINK "[login to view URL]"delete HYPERLINK "[login to view URL]" HYPERLINK "[login to view URL]"restrict” clauses. Check how Foreign keys help guarantee referential integrity within the database. Write a sql command to update the CUS_CODE of any record in the customer table. What message do you get? Write a sql command to delete the records of the customer table. What message do you get? Load the sample data of your assignment for the customer and the invoice tables. Write the SQL query to get the product of the two tables. To get the product of two tables T1 and T2, run the following query: select * from T1, T2. Show the output. Write the SQL query to get the list of all customers and their corresponding invoices when available. Use left Join. As you do not have the super privilege on your mysql account under zenit to create triggers, Use [login to view URL] or your own installation of mysql. Using [login to view URL], provide a username and password and database name as well. You will need to activate your account by clicking on a link sent to your email while the creation of the account. Once you login, create the customer and the invoice table and create the following triggers. Create a trigger before insert on the customer table that checks if the balance is different than zero and if so it sets its value to zero. Write the sql command to insert a customer record with a balance different than zero, then check the data in the table. Create a trigger before insert on the invoice table that sets the value of the invoice total as the sum of invoice subtotal and invoice tax. Write the sql command to insert an invoice record without providing a value for the invoice total, then check the data in the table. more info in attached file.