Answer the questions below for the table

-- Create the petSupplies table

drop table if exists petSupplies;

CREATE Table IF NOT EXISTS petSupplies

(

prodID VARCHAR(50),

suppDescription VARCHAR(2000),

CONSTRAINT petSupplies_PK PRIMARY KEY(prodID),

CONSTRAINT petSupplies_FK FOREIGN KEY(prodID)

REFERENCES product(prodID)

) ENGINE =InnoDB;

1. Write a query that performs a Union on one of your main tables and another table

Write an SQL statement that will combine the instances in both tables as a relational algebra union operation using an SQL SELECT Statement. This is basically each student demonstrating their individual use of the UNION Clause in SQL

2. Write a query that performs an intersection on one of your main tables and another table.

This will produce a result set containing the instances that match in both the table that you took ownership of. If you have difficulties with this concept, I have published a video series to provide students with a framework to accomplish this. The Query linked to this task must produce a result set of 1 or more rows in order to receive points for this task.

3. Write two separate queries that perform a Difference Operation on your main tables and another table.

This will produce a result set containing the instances from one of the main tables that you took ownership of that do not match instances in the other table

It will also produce a result set that contains the instances from the other table that do not match instances in the entity table you took ownership of. The video described earlier will provide a framework for doing this. The Queries linked to this task must produce a result set of 1 or more rows in order to receive points for this task.

.

4. You are to write a JOIN Query using two or more of your tables. A table and two or more tables that it has a relationship with:

Remember a basic relationship is established when two tables share a common attribute. One is the primary key table the other is the foreign key table. For the multi-table select query, use the entity table as the one side of a 1-M relationship. Use one of the relationship tables created in the group segment as the many sides of a 1-M relationship. The SELECT Query may include 2 or more tables. The practice videos will provide a framework for doing this. The Query linked to this task must produce a result set of 1 or more rows in order to receive points for this task.

5. Create two queries that will alter the structure of your entity table:

Normally when integrating systems, there are a few datatype compatibility issues and data alignment issues that need resolution. To prepare for handling such problems, create a set of Alter Table commands to demonstrate that you can handle these types of situations should they arise. The practice videos and homework assignments will provide a framework for doing this. Each ALTER TABLE Statement must be preceded by a statement that describes the structure of the table before it is altered and a statement that will display the structure of the table after it is altered.

6. Write two queries that will update two different categories of rows in the tables of the set of tables that you have chosen.

To demonstrate that you understand update queries, create at least two UPDATE Statements to process and update one or more existing records in your chosen tables. This could be accomplished by populating the new attributes added to the table. The practice videos and homework assignments will provide a framework for doing this. The Queries linked to this task must produce a result set of 1 or more rows that are updated in order to receive points for this task.

7. Write two queries that will delete two different categories of rows in your tables.

To demonstrate that you understand delete queries, create a set of DELETE Statements to process and delete one or more existing records in your chosen tables. The practice videos and homework assignments will provide a framework for doing this. The Queries linked to this task must produce a result set of 1 or more rows that are deleted in order to receive points for this task.

8. Write two queries that perform aggregate functions on at least your Primary Tables in your set of individual tables.

The practice videos and homework assignments will provide a framework for doing this. The Queries linked to this task must produce a result set.

9. Write two queries that use a HAVING clause on different categories of rows in your set of tables chosen. The SELECT Queries should only include the HAVING Clause to separate them from the Queries that include both a HAVING Clause and a GROUP BY Clause. The practice videos and homework assignments will provide a framework for doing this. The Queries linked to this task must produce a result set of 1 or more rows that are updated in order to receive points for this task.
10. Write two queries that use both a GROUP BY and HAVING clause on different categories of rows in your entity tables or a combination of the set of tables chosen The practice videos and homework assignments will provide a framework for doing this. The Queries linked to this task must produce a result set of 1 or more rows that are updated in order to receive points for this task. There should be a collection of Two Queries that are using the GROUP BY Clause and Two Queries that are using both the GROUP BY Clause and the HAVING Clause

11. Write two queries that sort the results in Ascending and Descending order of the set of tables chosen. The practice videos and homework assignments will provide a framework for doing this. The Queries linked to this task must produce a result set of 2 or more rows in order to receive points for this task.
12. Write statements that create two SQL VIEWS. The set of view statements should also include the SELECT Statements to execute them.

The practice videos and homework assignments will provide a framework for doing this. The Queries linked to this task must produce a result set of 1 or more rows in order to receive points for this task.

13. Write two Stored Procedures

Create a set of CREATE Stored Procedure Statements. One of the CREATE PROCEDURE Statements must include statements that describe the structure of the tables that you took responsibility for and lists all the tuples in the tables. The script must include the call statements to invoke the stored procedure.

The second Create a Stored Procedure Statement must receive at least one passed parameter that it will use in a query within the body of the Stored Procedure. The script must include the call statements to invoke the stored procedure.

The practice videos and homework assignments will provide a framework for doing this. The Queries linked to this task must produce a result set of 1 or more rows that are updated in order to receive points for this task.

14. Write Stored Triggers

Create a set of Stored Triggers that will automatically backup a tuple when it is deleted or updated. This will involve writing two triggers.

One of the Stored Trigger inserts the older version of the tuple into a backup table before it was updated.

The second Stored Trigger will insert the tuple that was deleted into the same backup table that the update trigger uses.

Both stored triggers should be linked to one of the one tables that you took individual responsibility for in the project. You must include code that invokes the triggers. This means that you must include the DELETE Statements that delete information from the table. You must also include the UPDATE Statement that updates information in the table. Finally, you must include SELECT Queries that display evidence that the triggers were invoked successfully. This is done by displaying the content of the backup audit table after the UPDATE and DELETE operations are performed.

The practice videos and homework assignments will provide a framework for doing this.

15. Create New Users

Write code that will create you and another user in the database you are participating in. This means that two new users must be created

Include code that will do the following:

• Code/Script that will grant you and the other user privileges to the primary tables that you have taken possession of in the Final Project Assignment.
• Code/Script that will revoke user privileges to the tables that you have taken possession of in the Final Project Assignment.
• Code/Script that includes statements that will demonstrate that the privileges that you granted and revoked are working. This is an SQL statement that will display the current privileges of users to tables. The sequence of code should display the privileges prior to granting, after granting privileges, and after revoking privileges.

The practice videos and homework assignments will provide a framework for doing this.

The GRANT Statements, REVOKE Statements and CREATE USER Statements will not interfere with the continuous execution of the SQL Script. The only statements that will interfere are the EXIT Statement and the MySQL Login sequences. The EXIT Statement and the MySQL Login sequences.should be commented using SQL comment indicators before submitting the script if using them in your testing