Reference no: EM132286278

Data and Information Management Assignment -

1. Create the database for Continental Palms Hardware Company (CPHC) in Oracle. Enforce as many integrity constraints as you can.

2. Enter the following sample data into the database.

3. Answer the following queries in Oracle SQL. Include all SQL statements in script file. The statements must be tested. Sample results are available on the G: drive.

• Get the ID, first name, last name, and birthdate of every employee of CPHC.
• Get the ID, name (first name plus last name), and age of every female employee of CPHC.
• Get the ID, name, and age of every female salesperson.
• Get the ID, name, and age of every female salesperson who sells some products manufactured by Stanley.
• Get the following about every female salesperson who sells some products manufactured by Stanley: ID, name, age, and products (UPC and manufacturer) authorized to sell. Include products not manufactured by Stanley too.
• Get the following about every female salesperson who sells some products manufactured by Stanley: ID, name, age, number of products authorized to sell, and number of manufacturers of these products.
• Get the following about every female salesperson who sells products from multiple manufactures or more than five products: ID, name, age, number of products authorized to sell, and number of manufacturers of these products.
• Get the following about every female salesperson who sells products from multiple manufactures or more than five products, but does not sell any product by Stanley: ID, name, age, number of products authorized to sell, and number of manufacturers of these products.
• Get the ID, name, age, and manager name (if any) of every female salesperson.
• Get the ID, name and age of every female salesperson who sells all of the products manufactured by Stanley. (Hint: This query involves the Division operation in relational algebra, but is notoriously difficult to write in SQL. Consider using sub-queries. Restate the query as "Find every female salesperson, such that there does NOT exist any product manufactured by Staley that the salesperson does NOT sell").

Note - Just need the answers of question number 3, in which only needed 10 SQL queries. No data loading and database is required.

Attachment:- Assignment File.rar