Part II of the Assignment
Write a query that performs a Union on your main table 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
Write a query that performs an intersection on your main table 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
Write two separate queries that perform a Difference Operation on your main table and another table
This will produce a result set containing the instances from the main table 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.
.
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.
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 an Alter Table command 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.
Write twoqueries 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 process to update one or more existing records in your chosen table. 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.
Write two queries that will delete two different categories of rows in your tables
To demonstrate that you understand delete queries, create a delete process to delete one or more existing records in your chosen table. The practice videosand homework assignments will provide a framework for doing this.
Write two queries that perform aggregate functions on at least your Primary Table in your set of individual tables.
The practice videosand homework assignments will provide a framework for doing this.
Write two queries that use a HAVING clause on different categories of rows in your set of tables chosen.
The practice videos and homework assignments will provide a framework for doing this.
Write two queries that use a GROUP BY and HAVING clause on different categories of rows in your entity table or a combination of the set of tables chosen
The practice videosand homework assignments will provide a framework for doing this.
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.
Write two queries that create two SQL VIEWS. These view statements should also include the SELECT Statements to execute them.
The practice videos and homework assignments will provide a framework for doing this.
Write two Stored Procedures
Create a Stored Procedure that describes 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
Create a Stored Procedure that receives a passed parameter that it will use in a query with the stored procedure. The script must include the call statements to invoke the stored procedure
The practice videosand homework assignments will provide a framework for doing this.
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 Stored Trigger that inserts the older version of the tuple into a backup table before it was updated. One Stored Trigger that will insert the tuple that was deleted into the same backup table that the update trigger uses. Both stored triggers should be linked to the one table that you took individual responsibility for in the project. You must include code that invokes the triggers and a SELECT Query that displays evidence that the triggers were invoked successfully.
The practice videos and homework assignments will provide a framework for doing this.
Create New Users
Write code that will create you and another user in the database you are participating in. Include code that will grant you access to primary tables that you have taken possession of. Also, include code that will revoke privileges to the tables that you have taken possession of in the project. Include 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 practice videos and homework assignments will provide a framework for doing this.