Review

The following questions are intended to reinforce key information presented in this appendix and Appendixes D, E, and F. If you are unable to answer a question, review the appropriate lesson and then try the question again.

You are the database administrator for a health care plan. The Physicians table was created with the following statement:

 CREATE TABLE dbo.Physicians (  PhysicianNo int IDENTITY (100, 2) NOT NULL ,  FirstName varchar (25) NOT NULL ,  LastName varchar (25) NOT NULL ,  Street varchar (50) NULL ,  City varchar (255) NULL ,  State varchar (255) NULL ,  PostalCode varchar (7) NULL ,  CoPay money NOT NULL CONSTRAINT PhysCoPay DEFAULT (10)  ) 

  1. How would you retrieve information about physicians who have practices in the states of New York (NY), Washington (WA), Virginia (VA), or California (CA)?
  2. Write a SELECT statement with a WHERE clause of the following type:

     WHERE State = 'NY' OR State = 'WA' OR State = 'VA' OR State = 'CA' 

    Or, use a WHERE clause that includes the IN keyword:

     WHERE State in ('NY', 'WA', 'VA', 'CA') 

  3. How can you generate a list of states that does not include any duplicate states in the result set?
  4. Use the DISTINCT keyword as part of the SELECT statement.

  5. How can you generate a column in your result set that lists the CoPay value plus a service charge of $5.00 for each physician, and then alias this column as AmtDue?
  6. Use a computed column in the select list and use an alias for the column name as follows:

     (CoPay + 5) AS AmtDue 

  7. What is the minimum number of column values that you must supply to add a new row to the table?
  8. You must supply data for at least two columns. At a minimum, the INSERT statement contains values for FirstName and LastName. All other columns allow null values or have defaults generated for them.

  9. The participating physicians have increased their costs of services. How can you increase the CoPay value for all doctors by 12 percent?
  10. Use an UPDATE statement as follows:

     UPDATE Physicians SET CoPay = (CoPay * 1.12) 

  11. How can you remove all rows from the Physicians table?
  12. Use a DELETE statement that has no WHERE clause or a TRUNCATE TABLE statement.



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