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:
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) |
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.
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.
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.
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.
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.