In your job at WebbedMD, you should have all three tables created and imported into your database.  Your task continues with a request from the business office to have a set of regular reports certain people in their office can run.  Some are summary data and some are monitoring reports on competitors.  You must write the SQL to provide views for them.  Their business requests are described below.

The GROUP BY clause can be used to return combination-subsets of data within a set for aggregated analysis.  When used with the SELECT clause and SQL Functions (SUM, COUNT, etc.), summary data can be determined.  (Don't forget about how to consider NULLs.)  In combination with the HAVING clause, sigma can be performed to restrict the tuples included in the analysis.

Using GROUP BY and SELECT, write and submit a query that reports the company name, the number of unique products made by each company (1).

Now insert this query into an outer query that tells the average and standard deviation for  unique products made by these companies (2)

Add HAVING to your first statement, and submit a query that identifies the number of companies with more than 8 products that come as tablets (3).

Using the GROUP BY clause, write and submit a SQL Statement that will provide sets identifying the primary key as you did above (4).

When you identified primary keys in your last step, you focused on the unique value(s) of the attribute(s) for the entire set of tuples.  By identifying primary keys, you can create a new access path to your data.  SQL, in combination with the relational model, allows your DBMS package to arrange data on disk in a manner that makes accessibility faster.  It also improves data integrity by making sure that there are no two tuples describing the same entity in the entity set.  Make sure you applied the keys to each of your tables, then repeat your SELECT Queries from Lab 2.  Also apply indexes to any columns, or group of columns, you deem appropriate for your query.  The information is repeated in this box for your convenience, but refer to Lab 2 for the red-letter questions.  Submit the same results as you did in Lab 2, and add the execution plans after your primary keys have been created. Note whether primary keys shortened response time for each query (4).

FROM LAB 2: Modify the following to make this work for one of the values stored in the "Ingredients" column.  Change the value several times to what effect it has on your results.  You should be retrieving a single record each time. SELECT * FROM .... WHERE .... = '[the complete string stored in that field]' Notice that the FDA application allows any or all parts of a string.  You can do this as well by changing = '[the complete string stored in that field]' to LIKE '%[any string]% Run your query several times, changing the string length to see the impact on number of rows returned and the time required.  Continue to increase your string until only one row is returned.  Now click on Query in the Menu Bar and select both 'Include Actual Execution Plan' and 'Include Client Statistics.'  Run your two single-row-result queries again and record the Total Execution Time for each.  What is the unit of measure for this time?  (Don't forget to use SQL Server Books Online as a resource). Were different steps illustrated in the execution plans for each of these?  Record the name of each in the order the query was run. Which of the two queries above would be expected to return a single row faster?  Did you see a difference?

Now write and submit SQL views to answer the following questions for your data analysis staff.  Utilize joins (left, right, inner and outer) and nested queries where appropriate.  Also submit each of these as relational algebra statements, using the syntax from the book.

Who makes Cogentin?

Which company has the longest time interval between the exclusivity date and the patent expiration date?  What is the average time interval between these two dates for all products made by that company?

Which companies make products that have the same ingredients, strengths, dosage form and route as Percodan?

Which company has the most products containing some type of aspirin?  How many products is that?

Which company has a product with the most ingredients in addition to aspirin?  How many ingredients is that?

Which company has the highest number of exclusive products? for which the exclusivity is still active?

Which day has the most applications filed and which companies filed on that day and what are the trade names and ingredients for those products?

Are there any companies that make Demerol in 50 mg/5ml but whose product doesn't come in tablets?

I need to pass in a company name, trade name, ingredients, strengths, route and dosage form from my code and accept back that company's product, plus all other products that are considered therapeutically equivalent to those products and that have not been discontinued.  My client considers any product that is a reference listed drug (RLD) to be the standard and any product with a TE Code starting with "A" to be as good as the standard, while all of the rest are inferior.