Review

The following questions are designed to reinforce key information presented in the chapter. If you are unable to answer a question, review the appropriate lesson and then try the question again.

The Duluth Mutual Life health care organization has a database that tracks information about doctors and their patients. The database includes the following tables:

Doctors table

Column Data Type and Constraints
doc_id Char(9), PRIMARY KEY
fname Char(20)
lname Char(25)
specialty Char(25)
phone Char(10)

Patients table

Column Data Type and Constraints
pat_id Char(9), PRIMARY KEY
fname Char(20)
lname Char(25)
insurance_company Char(25)
phone Char(10)

Cases table

Column Data Type and Constraints
admission_date Datetime, PRIMARY KEY (composite)
pat_id Char(9), PRIMARY KEY (composite), FOREIGN KEY to patient.pat_id
doc_id Char(9), FOREIGN KEY to doctor.doc_id
diagnosis Varchar(150)

Based on this table structure, answer the following questions:

  1. How, with a single query, can you produce a list of all cases that were admitted on the first chronological date in the database?
  2. Use a single-value subquery with the MIN function to determine the oldest date of admission. Compare the result of the subquery to the admission date for each case in the WHERE clause. For example:

    SELECT * FROM Cases WHERE admission_date = (SELECT MIN(admission_date) FROM Cases)

  3. You want to know the total number of hospital admissions, listed by patient name. How can you determine this total? What are the advantages or disadvantages of your method?
  4. You could write a SELECT statement with a correlated subquery that calculates the total admissions for each patient by using the COUNT function.

    SELECT pat_id, pat_name, (SELECT count * FROM Cases  c WHERE c.pat_id = p.pat_id) FROM Patients  p

    You could also do this by using a join with the GROUP BY clause and the COUNT function. The subquery method is probably less efficient than the GROUP BY method.

    You have numerous, user-defined databases that you administer. On a periodic basis, you want to be able to determine the amount of space that each table uses in a specified database. You plan to execute the sp_spaceused stored procedure against every table in the specified database.

  5. Could you use a Transact-SQL cursor for the task?
  6. Yes. You could query the sysobjects table to build a result set that contains all the tables in the specified database. The cursor could navigate through this result set and execute the sp_spaceused stored procedure against each table.

  7. Could you do this task without using a Transact-SQL cursor?
  8. Yes. You could use SQL-DMO, or you could use a SELECT statement to create a loop and execute the sp_spaceused stored procedure against each table.

  9. Which solution is most appropriate?
  10. The solution to this type of problem requires processing one row at a time, regardless of whether a cursor is used or not. The performance characteristics of each solution will be substantially identical. The main reason to consider using a method that does not use a cursor is that more resources (such as space in tempdb) are used on the server when a cursor is used.

  11. Does your answer depend on the number of tables or size of the database? For example, if you increase the number of tables or the size of the database, will your answer change?
  12. No. Because updates and exclusive locks are not involved, neither the number of tables nor the size of the database will have a significant effect on performance. The number of concurrent users of the query does affect the decision to use cursors. You should avoid having a large number of concurrent users using cursors.



Microsoft Corporation - Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit
Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit
ISBN: 0735606706
EAN: 2147483647
Year: 1999
Pages: 114

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net