微软认证最新考题70-029 SQL7.0实现

70-029 SQL7.0实现
1). Users report slow response times when they are modify data in  your application. Response times are excellent when users are merely retrieving data. The search criteria used for modifying data are the same as the search criteria for retrieving data.

All transactions are short and follow standard guidelines for coding transactions. You monitor blocking locks, it’s ok.

What is the most likely cause?
A. The transaction log is placed on an otherwise busy disk drive.
B. The transaction log is nearly full
C. The checkpoint process is set too short
D. The tempdb database is too small
E. The tempdb database is on the same physical disk drive as the
    database.
Answer: A

2). Database includes a table named experiments that is defined as follows:
CREATE TABLE experiments { experimentid char(32),
description text,
status integer,
results text}

You write the following:
SELECT* from experiments where contains(description,  'angina')
you are certain that there are matching rows, but you receive an empty result set when you run the query.

what should you do?(choose two)
A. ensure that there is a nonunique index on the description column of the experiments table.
B. ensure that there is a clustered index on the results column
    experiments table.
C. create a full-text catalog that includes the experiments table.
D. create a scheduled job to populate the full-text catalog.
Answer: C,D

3). Database includes  a  job_cost table that typically holds 100000 rows but can grow or shrink by as much as 75000 rows at a time. the job_cost table is maintained by a batch job that runs at night.
during the day, the job_cost table is frequently joined to other tables by many different queries. Users report that their initial queries are very slow, but then response time improves for subsequent queries.

How should you improve the response time of the initial queries?
A. run the sp_updatestats stored procedure as part of the nightly batch
   job.
B. run the sp_createstats stored procedure as part of the nightly batch 
   job.
C. set the auto create statistics database option to true.
D. set the auto update statistics database option to true.
Answer: A

4).you are the DBA. You receive reports that your sales application has very poor response times.
The database includes a table that is defined as follow:
    create table dbo.orders {
      OrderID         int identity(1,1)  NOT NULL,
      SalespersonID  int                   NOT NULL,
      RegionID        int                   NOT NULL,
      Orderdate       datetime             NOT NULL,
      Orderamount     int                   NOT NULL,
      CustomerID      int                   NULL}
the orderid column is the primary key of the table. there are also indexes on the regionid and orderamount columns.
you decide to run a showplan on all queries in the application. The following query, which accesses this table is used to list total average sales by region:
    select t1.regionid,avg(t1.salestotal) as regionaverage
    from (select regionid,salespersonid,sum(orderamount) as
    salestotal
    from orders
    group by regionid,salespersonid as t1
    group by t1.regionid
you set the SHOWPLAN_TEXT option to ON execute the query.the
showplan output is as follows:
  ...
you suspect that this query is part of the problem because the showplan indicates that the query is performing a table scan operation.

what is the most likely reason that this query is performing a table scan?
A. there is no composite index on OrderID,RegionID,and Orderamount.
B. there is no where clause in the query.
C. the query contains a subquery.
D. the query is performing aggregate operations.
ANSWER: B

5). Users can create and submit their own ad hoc queries against any of The tables in DSS database. You users report that the responses time for some queries are too long. Response times for other queries are acceptable.

What should you use to identify long-running queries?
A. SQL Server Enterprise Manager
B.  SQL Server Profiler
C   SQL Server Analyzer
D. Microsoft Windows NT Performance Monitor.
ANSWER: B

6). You have an application that makes four connections to the SQL Server at the same time, The connections are used to Execute SELECT, INSERT, UPDATE, and DELETE statements.
The application occasionally stops responding when a user is trying to update or Delete rows, and then the user must close the application. The problem occurs When a user attempts to execute an UPDATE or DELETE statement after Submitting a SELECT statement that retrieves a result of more than 10,000 Rows
What can you do?
A. On the connection for the SELECT statement, set the deadlock priority 
   to low.
B. On the connections for the UPDATE and DELETE statement, set the
   deadlock priority to low.
C. On the connection for the SELECT, set the transaction isolation
   level to READ UNCOMMITTED.
D. Set the query wait configuration option of the SQL Server 5,000.
Answer: C

7). The policy table will be accessed and updated by several additional applications, in the policy table, you need to ensure that the value entered into the beginning_effective_date column is less than or equal to the value entered into The ending_effective_date column.

What should you do?
A. Program each application to compare the values before updating the
    policy table.
B. Create a CHECK constraint on the policy table that compares the values.
C. Create a rule and bind the rule to the Beginning_effective_date
    column.
D. Create INSERT and UPDATE triggers on the policy table that compare
    the values.
ANSWER:B

8). The demographic table in the application contains more than 1,000 columns

Most of the reports relate to long response times when users are updating or retrieving data from the demographic table. Nearly 90 percent of the users search or update 20 of the columns .The remaining columns are seldom used, but there are important.

What should you do to?
A. Create a clustered index on the demographic table over the most
    accessed columns.
B. Create a view based on the demographic table, which selects the 
    20 most accessed columns.
C. Divide the data in the demographic table into two new tables ,with 
one table   Containing 20 most accessed columns and the other 
containing the remaining columns.
D. Create a series of stored procedures that select or update the
    demographic table according to user needs.
Answer: C

9). You are implementing a transaction-based application for a Credit card company. More than 10 million vendors accept the Company’s credit card, and more than 100 million people Regularly use the credit card.

Vendors around the world must be able to authorize purchases in less than 30 Seconds, 24 hours a day, seven days a week, Additionally, the application must be able to accommodate more vendors in more locations in the future.

What should you do?
A. .. .
B. .. .
C. .. .
D. Implement an n-tier architecture in which vendor make calls to the geographically dispersed Microsoft Transaction Servers(MTS),which would then obtain an authorization code from geographically dispersed SQL Servers.
Answer: D

10). Two SQL Servers supporting two separate applications on your network. Each application uses stored procedures for all data manipulation. You need to integrate parts of the two applications. The changes are limited to a few stored procedures that need to make calls to remote stored procedures.

What should you do to implement calls to remote stored procedures?
A. Add the remote server as a linked server, Fully qualify the remote
   procedures  names.
B. Program each application to connect to both servers. Use the  
sp_bindsession   stored procedures to bind both connections before 
running any stored procedures that start a transaction.
C. Program each application to issue the BEGIN DISTRIBUTED TRANSACTION
  Statement before each transaction .
D. Configure the SQL Server by setting the remote proc trans option 
    to 1.
Answer: A

11). You need to produce a list of the five Highest revenue transactions from the Orders table in the Sales database.
The Orders table is defined as follows:
  CREATE TABLE Orders{
  Order ID      int  IDENTITY(1,1)         NOT  NULL,
  SalePersonID     int         NOT  NULL,
  RegionID                     int         NOT  NULL, 
  Orderdate     datetime          NOT  NULL, 
  OrderAmount     int         NOT  NULL 

Which statement will produce the report?
A. SELECT TOP 5 OrderAmount, SalePersonID  FROM  Orders ORDER BY
    OrderAmount DESC.
B. SELECT TOP 5 OrderAmount, SalePersonID  FROM  Orders.
C. SELECT TOP 5 with TIES OrderAmount, SalePersonID  FROM  Orders 
    ORDER BY OrderAmount.
D. SELECT TOP 5 with TIES OrderAmount, SalePersonID  FROM  Orders
    ORDER BY Order ID.
Answer: A

12).Your Sales database is accessed by a Micorosoft Visual Basic Client/Server application. The application is not using the Microsoft Windows NT Authentication security model.

You write the following stored procedure to be called by the Visual Basic application:
     CREATE PROCEDURE InsertReinstatedOrder
   ...
     ...
A User named Andrew assigned to the Sales role. Andrew reports that he is receiving an error message indicating that he is having a permissions problem with the procedure.
What must you do to solve the problem?
A. Grant permission on the stored procedure specifically to Andrew.
B. Add Andrew to the Windows NT Sales group.
C. Add Andrew to the Windows NT Administrator group.
D. Add Andrew to the db_owner role.
Answer: D

13).You are designing a data model to track research projects.

You want to accomplish the following goals:
...
You design the logical model as shown in the exhibit(Click the exhibit
Button)
                           
Institution
InstitutionID(PK)
InstitutionID1

                                               
Job
JobName(PK)

Project
ProjectID(PK)
                                      
Scientist
ScientistID(PK)
InstitutionID
JobName

ProjectDetail
ProjectID(PK)
InstitutionID(PK)

Which result or results does this model produce?(choose all that apply)
A. All the scientists conducting research for any specific project 
    can be reported.
B. A scientist’s job for a specific project in a specific institution
    can  be reported.
C. All the institution participating in any specific project can be
    reported.
D. The institution at which a scientist is a staff member
    can be tracked.
E. An institution can be identified as part of another
   institution.
Answer: C,D,E

14)You work for a licensing agency that buys photographs and then sells
them to other companies for commercial use...

You want to accomplish the following goals:
...
You take the following actions:
...
Which result or results do these actions produce?(choose all that apply)
A. An individual can be identified as a customer, a supplier or an  
   employee, or any combination of the three.
B. There is no data redundancy.
c. No individual can have the same identification number  as an
    organization.
D. An individual can be associated with more than one  organization
E. An individual can be associated with more than one individual.
Answer: B,D,E

15).You are designing an inventory database application for a national
automobile sales registry.
...
You want to be able to track information about each automobile.
You want to normalize your database.
...
Which tables should be included in the database application(choose all that apply)
A. a table containing the list of all dealerships along with the address
 and identification number for each dealership.
B. a table containing the contact information for each automobile manufacturer along with the name of each model manufactured by each
manufacturer.
C. a table containing the name and address of each dealership along
with automobile information
D. a table containing identification number for each automobile,
the owning dealership’s identification number, and other information specific to each automobile
Answer: A,D

16).You are designing the data model to maintain information about
students living in a group home.

You want to accomplish the following goals:
...
You design the logical model as shown in the exhibit(click the  exhibit)


Student
StudentID(PK)


StudentAlias
StudentID(PK)
Alias(PK)
 
StudentEvent
StudentID(PK)
EventName(PK)
  
FamilyRelationship
StudentID1(PK)
StudentID2(PK)
 
Address
AddressID(PK)
StudentID

Which result or results do this model produce?(choose all that apply)
A. Any kind of descriptor can be associated with a student.
A. Multiple addresses can be associated with multiple students, and
   that address usage can be reported.
C. Any family relationship with another student can be reported.
D. All known aliases for a student can be reported.
E. Significant events in a student’s life can be reported.
Answer: C,D,E

17).You are creating a table named recruit to track the status of
potential new employees. The SocialSecurityNo column must not
allow null values. However, a value for a potential  employee’s
Social Security number is not always known at the time of initial
Entry.

You want the database to populate the SocialSecurityNo column with a value of UNKNOWN when a recruiter enters the name of a new potential
Employee without a Social Security number.

How can you accomplish this task?
A. Create a rule on the SocialSecurityNo column.
B. Create a default definition on the SocialSecurityNo column.
C. Create a CHECK contraint on the SocialSecurityNo column.
D. Create a user-defined data type. apply it to the SocialSecurityNo
    column, and bind a rule to the user-defined data type.
Answer: B

18).You are developing a Personnel database for your company.
This database includes an employee table that is defined as
Follows:
          CREATE TABLE employee{
    ID      int IDENTITY     NOT NULL,
    Surname                  varchar(50)     NOT NULL,
    FirstName       varchar(50)    NOT NULL,
       SocialSecurityNo        char(10)     NOT NULL,
    Extention     char(4)        NOT NULL,
    EmailAddress              varchar(255)   NOT NULL}

Each employee must have a unique telephone extension number and a unique e-mail address. In addition, you must prevent duplicate Social Security numbers from  being entered into the database

How can you alter the table to meet all of the requirements?

A. ALTER TABLE employee
  ADD CONSTRAINT u_nodups UNIQUE NONCLUSTERED (SocialSecurityNo,  
  Extention, EmailAddress).

B. ALTER TABLE employee
    ADD CONSTRAINT u_nodups UNIQUE  CLUSTERED (SocialSecurityNo,      
    Extention, EmailAddress).

C. ALTER TABLE employee
    ADD CONSTRAINT u_nodupssn UNIQUE NONCLUSTERED (SocialSecurityNo ).
    ALTER TABLE employee
    ADD CONSTRAINT u_nodupext UNIQUE NONCLUSTERED (Extension)
    ALTER TABLE employee
    ADD CONSTRAINT u_nodupemail UNIQUE NONCLUSTERED (EmailAddress).
D. Alter table employee
    Add constraint u_nodupssn check...
Answer: C

19).Your database is used to store information about each employee
department. An employee can work in only one department.
The database contains two tables, which are named Department and
Employee. The tables are modeled as shown in the exhibit(click the exhibit)

Department
DepartmentNo(PK)
DepartmentName
 
Employee
EmployeeID(PK)
FirstName
LastName
SocialsecurityNo
DepartmentNo
DepartmentName
Phone

You want to ensure that all data stored is dependent on the whole key
Of the table in which data is stored. What should you do ?
A. Add an EmployeeID column to the Department table.
B. Remove the SocialSecurityNo column from the Employee table.
C. Remove the Departmentname column from the Employee table.
D. Remove the Departmentname column from the Departmenttable.
Answer: C

20).You are designing a distributed data model for an international
importing and exporting company.

You take the following actions:
...
Which result or results does these actions produce?(choose all apply)
A. All sales record primary keys are unique throughout the
    distributed database.
B. A sales record created in London or Nairobi includes a
   value-added tax,but that a sales record created in Cairo does not.
C.Local sales amounts can be calculated in United States dollars at
   the time of the sale.
D. Local sales amounts can be calculated in United States dollars at the end of the month.
E. The difference between the values of a sale United States dollars  
at the time of the sale and the value at the end of the month can be calculated.
Answer: A,B

21).You must write a stored procedure to perform cascading deletes
on the HomeLoan database. The client application will pass a parameter containing the CustomerID of the customer to be deleted.

Customer
CustomerID(PK)
LastName
FirstName
AddressID
StatusID
Country
 

Loan
LoanID(PK)
LoanDate
LoanStatusID
AppraisalDate
AppraisalAmount
LoanAmount
CustomerID
 
Inspection
InspectionID(PK)
LoanID
InspectionResultID
InspectionDate
InspectionTypeID
 
Appraisal
AppraisalID(PK)
LoanID
AppraisalDate
AppraisalAmount
AppralsorID

Which stored procedure should you use?
A. CREATE PROCEDURE LoanCascadeDelete
@customerID int
AS
DELETE FROM Appraisal
FROM Appraisal
JOIN Loan ON Appraisal.LoanID=Loan.LoanID
WHERE customerID=@customerID
DELETE FROM Inspection
FROM Inspection
JOIN Loan ON Inspection.LoanID=Loan.LoanID
WHERE customerID=@customerID
DELETE FROM Loan
WHERE customerID=@customerID
DELETE FROM Customer
WHERE customerID=@customerID

B. ...
C. ...
D. ...
Answer: A  (A是级联删除的标准写法)

22).You have a database that is accessed by many different
applications complies with ANSI 92 written with many different development tools. Each application uses a different mechanism for accessing the database.

Transaction processing in not uniform across all your applications.
How Can you  ensure that all applications handle transactions in the some Fashion?

A. Inside the database,create an application role,Program all
    applications to activate the application role.
B. Program all applications to issue the SET ANSI_DEFAULTS ON command
 Immediately after establishing a user connection.
C. Configure the SQL Server by using the sp_configure ‘user options’
  Stored procedure to turn on the following  
  options:implicit_transactions,cussor_close_on_commit,
  ansi_warnings, ansi_padding, ansi_nulls,quoted_identifier,  
  ansi_null_dflt_on.
D. Create a stored procedure that sets all the users seesion properties
    Run the sp_procoption stored procedure to mark your procedure for
    startup.
Answer: B

23).Your database includes tables that are defined as follow:
    create table SalesPerson(
      SalesPersonID         int identity(1,1)   NOT NULL
                          PRIMARY KEY NONCLUSTERED,
      RegionID        int                   NOT NULL,
      Lastname        varchar(30)         NULL,
      Firstname       varchar(30)         NULL,
      Middlename      varchar(30)         NULL,
      AddressId       int                   NULL)
     
     create table Orders(
      OrderID         int identity(1,1)   NOT NULL
                          PRIMARY KEY NONCLUSTERED,
      SalePersonID        int                   NOT NULL,
      RegionID             int                   NOT NULL,
      Orderdate   datetime             NOT NULL,
   OrderAmount   money                 NOT NULL)

You need to produce a list of the highest sale for each salesperson on September 15,1998. The list id to be printed in the following:
LastName         FirstName        OrderDate      OrderAmount 

Which query will accurately produce the list?

A. SELECT s.LastName,s.FirstName,o.OrderDate,OrderAmount
FROM  salesperson AS s
LEFT OUTER JOIN Orders AS o
ON o.SalePersonID=s. SalesPersonID
WHERE o.OrderDate=’09/15/1998’
and OrderAmount in (select MAX(OrderAmount) from Orders)

B. SELECT s.LastName,s.FirstName,o.OrderDate,MAX(OrderAmount)
FROM  salesperson AS s
LEFT OUTER JOIN Orders AS o
ON o.SalePersonID=s.SalesPersonID AND o.OrderDate=’09/15/1998’
GROUP BY s.LastName,s.FirstName,o.OrderDate

C.SELECT s.LastName,s.FirstName,o.OrderDate,MAX(OrderAmount)
FROM  salesperson AS s
  INNER JOIN Orders AS o
ON o.SalePersonID=s.SalesPersonID
WHERE o.OrderDate=’09/15/1998’
GROUP BY s.LastName,s.FirstName,o.OrderDate,o.OrderID

D.SELECT s.LastName,s.FirstName,o.OrderDate,MAX(OrderAmount)
FROM  salesperson AS s
  INNER JOIN Orders AS o
ON o.SalePersonID=s.SalesPersonID
WHERE o.OrderDate=’09/15/1998’
  And OrderAmount in (select MAX(OrderAmount) from Orders)
Answer: B
24)You increase the number of users of your customer
application from 20 to 120 .With the additional users, the
response time when retrieving and updating has slowed substantially

You examine all the queries and indexes and discover that they are all fully optimized. The application seems to run properly as long as Number of users is less than 50

What can you do to resolve the problem?
A. Ensure that table hints are used in key queries to force the use of the correct table index
B. Increase the LOCK_TIMEOUT setting to accommodate the long response
Times now being encountered.
C. Free up dirty pages in memory by configuring the SQL Server with a short recovery interval.
D. Ensure that application is using an optimistic locking strategy
Instead of a pessimistic locking strategy.
Answer: D

25).You have a database that keeps track of membership in an organization.Tables in this database includes the membership table,
the Committee table,the Address table,and the Phone table,When a person
resigns from the organization,you want to be able to delete membership
row and have all related rows be automatically removed.

What can you do to accomplish this task?

A. Create a DELETE trigger on the Membership table that deletes any     
rows in Committee, Address ,Phone table that reference the primary  
key in the Membership table
 Do not place a FORDIGN KEY constraints on the Committee, Address, 
 Phone table.

B. Create a DELETE trigger on the Membership table that deletes any
rows in Committee, Address ,Phone table that reference the primary 
key in the Membership table  place a FOREIGN KEY constraints on
the Committee, Address ,Phone  table.

C. Place a PRIMARY KEY constraints on the Membership table with FOREIGN 
KEY constraints  on the Committee, Address ,Phone   table.

D. Place a PRIMARY KEY constraints on the Membership table Place  
FOREIGN KEY constraints on the Committee, Address, Phone  table 
that reference the primary key in the Membership table. Create
DELETE trigger on the Committee, Address, Phone   table will Fire
when their FOREIGN KEY constraints are violated.
Answer: A


26). Your database includes an Orders tables that are defined as follow:
       create table Orders{
      OrderID         int identity(1,1)     NOT NULL
                          PRIMARY KEY NONCLUSTERED,
      SalePersonID        int                  NOT NULL,
      RegionID             int                  NOT NULL,
      Orderdate   datatime            NOT NULL,
   OrderAmount   money                NOT NULL}
You have written a stored procedure named GetOrders.

You must change the stored procedure to produce a list of orders in order first by  RegionID  ,then by  SalePersonID.Permissions have been granted on the stored procedure,and you do not want to have to
Grant them again.

How must you change the stored procedure?
A. DROP PROCEDURE GetOrders
GO
CREATE PROCEDURE GetOrders
AS
SELECT SalesPersionID,RegionID,OrderID,OrderDate
OrderAmount
From Orders
ORDER BY  RegionID, SalePersonID

B. ALTER PROCEDURE GetOrders
AS
SELECT SalesPersionID,RegionID,OrderID,OrderDate
OrderAmount
From Orders
ORDER BY  RegionID, SalePersonID

C. ALTER PROCEDURE GetOrders
AS
SELECT SalesPersionID,RegionID,OrderID,OrderDate
OrderAmount
From Orders

D.DROP PROCEDURE GetOrders
GO
CREATE PROCEDURE GetOrders
AS
SELECT SalesPersionID,RegionID,OrderID,OrderDate
OrderAmount
From Orders
Answer: B

27). Your database includes a table that is defined as follow:
       create table SalesInformation{
     SalesInformation ID         int identity(1,1)  NOT NULL                                    
     SalePersonID                   int                  NOT NULL,
     RegionID                        int                  NOT NULL,
     ReceiptID                       int                  NOT NULL,
     SalesAmount             money                NOT NULL}
You want to populate the table with data from an existing application
That has a numeric primary key.In order to maintain the referential
Integrity of the database, You want to preserve the value of the original
primary key when you convert the data.

What can you do to populate the table?
A. Set the IDENTIEY_INSERT option to OFF, and then insert the data by using a SELECT statement that has a column list.
B. Set the IDENTIEY_INSERT option to ON, and then insert the data by using a SELECT statement that has a column list.
C. insert the data by using a SELECT statement that has a column list, and then alter the table to add the foreign key.
D. insert the data by using a SELECT statement that has a column list, and then alter the table to add the primay key.
Answer: B


28). You have a database to keep track of sales information. You are working with a nested procedure that will pass a parameter back to the calling procedure containing the total sales as follows:
  CREATE PROCEDURE GetSalesPersonData
   @SalesPersonID   int,
 @RegionID    int,
   @salesAmount   money OUTPUT
     AS
      SELECT @salesAmount=SUM(salesAmount)
      FROM  SalesInformation
      WHERE @SalesPersonID=SalesPersonID

Which statement will accurately execute the procedure and receive the value?

A. EXECUTE  GetSalesPersonData 1,1 NULL
B. EXECUTE  GetSalesPersonData @SalesPersonID=1, @RegionID=1,
     @salesAmount=0
C. EXECUTE  GetSalesPersonData 1,1, @salesAmount OUTPUT
D. EXECUTE  GetSalesPersonData @SalesPersonID=1, @RegionID=1,
     @salesAmount=NULL
Answer: C

29)You have an application that captures real-time stock market information and generates trending reports. In the past, the reports are generated after the stock markets closed. The reports now need to be generated on demand during trading hours.

What can you do so that reports can be generated without affecting the rest application? (choose two)
A. Program the application to issue the following command before generating a report.
Set transaction isolation level read uncommitted
B. Program the application to issue the following command before 
    generating a report.
Set transaction isolation level serializable
C   Require the application to include the NOLOCK table hint when
    generating a report.
D   Require the application to include the TABLOCKX table hint when
    generating a report.
E. On the stock transaction tables, create triggers that update summary tables instead of performing a data analysis each time a report is generated.
F. Declare global scrollable cursors on the stock transaction tables.
Answer: A,C

30).You server named Corporate has a Sales database that stores
sales data for a software distribution company, Two remote
servers named NewYork and Chicago each store sales data in a
salesorder table relative only to their respective sales territories. The salesorder table on the Corporate server is updated once a week with data from the remote servers.

The salesorder table on each server including Corporate is defined as follows:
  CREATE TABLE salesorder{
  Number               char(10)                  NOT  NULL,
  CustomerName        varchar(100)             NOT  NULL,
        TerritoryName       varchar(50)              NOT  NULL,
  EntryDate     datetime                  NOT NULL,
      Amount       money                     NOT NULL}

You need to create a view that shows a current list of all sales from the NewYork
And Chicago sales territories, and the list should have the following format

Territory            Customer   Date  Amount  

Which view can you create to show all sales from the NewYork and Chicago sales territories in the required format?
A. CREATE VIEW SalesSummary_view (Territory,Customer,Date,Amount)
AS
SELECT TerritoryName , CustomerName, EntryDate, Amount
FROM Corporate.Sales.dbo.salesorder
WHERE  TerritoryName=’ NewYork’OR TerritoryName=’ Chicago’

B.  CREATE VIEW SalesSummary_view
AS
SELECT NY.TerritoryName , NY.CustomerName, NY.EntryDate, NY.Amount, CHI.TerritoryName , CHI.CustomerName, CHI.EntryDate, CHI.Amount
FROM NewYork.Sales.dbo.salesorder NY,
Chicago.Sales.dbo.salesorder  CHI

C. CREATE VIEW SalesSummary_view (Territory,Customer,Date,Amount)
 AS
 SELECT NY.TerritoryName , NY.CustomerName, NY.EntryDate, NY.Amount,  CHI.TerritoryName , CHI.CustomerName, CHI.EntryDate, CHI.Amount
FROM NewYork.Sales.dbo.salesorder NY,
Chicago.Sales.dbo.salesorder  CHI
WHERE  NY.Number=CHI.Number

D.CREATE VIEW SalesSummary_view (Territory,Customer,Date,Amount)
AS
SELECT TerritoryName , CustomerName, EntryDate, Amount
FROM NewYork.Sales.dbo.salesorder
UNION ALL
SELECT TerritoryName , CustomerName, EntryDate, Amount
  FROM Chicago.Sales.dbo.salesorder
Answer: D

31). Your database includes a table named product. The procduct
table currently has clustered index on the primary key of the
product_id column. There is also a non clustered index on the
description column.

You are experiencing very poor response times when querying the product table. Most of the queries against the table include search arguments
On the description and product_type columns. Because there are many
Values in the size column for any product, query result sets usually contain between 200 and 800 rows.

You want to improve the response times when querying the product table.
What should you do?
A. Use SQL Server Enterprise Manger to create nonclustered indexes on each column being referenced by each SELECT statement.
B. Use SQL Server Enterprise Manger to generate stored procedure for the product table.
C. Use the index Tuning Wizard to identify and build any missing
Indexes.
D. Use SQL Server Profiler to capture performance statistics of queries against  the product table
Answer: C


32).You are building a decision support system(DSS)database for
your company. The new database is expected to include information
from existing data sources that are based on Microsoft Access, dBaseIII
, Microsoft Excel, and Oracle.
You want to use SQL Server Agent to run a scheduled job to extract
Information from the existing data source into a centralized database
On SQL Server 7.0.You do not want to perform any additional programming
Outside the SQL Server environment

How must you extract information from the existing data sources
A. Use the bulk copy program to import the information from all data
    Source.
B. Use the xp_cmdshell extended stored procedure to execute the
    Microsoft Windows copy command-line command/
C. Create Data Transaction Services package to import data from each
    data source.
D. Create export files for each data source.
Answer: C


33)You automate the backup and recovery process for your database application. After the database is restored, you discover that queries that use the FREETEXT and CONTAINS keywords no longer return the expected rows.

What should you do?
A. Alter the queries to use the LIKE keyword instead of the FREETEXT and CONTAINS keywords.
B. Alter the queries to use the FREETEXTABLE and CONTAINSTABLE keywords instead of the FREETEXT and CONTAINS keyword.
C. Add the database’s full-text catalog to both the backup job and the recovery job.
D. Add a job to the restoration process to re-create and populate the full-text catalog.
ANSWER: D


34) you are working on a data conversion effort for a Sales database. You have successfully extracted all of the existing customer data into a tab_delimited flat file. The new customer table is defined as follows:
     CREATE TABLE customer(
      Id                     int     IDENTITY             NOT NULL,
      LastName              varchar(50)                  NOT NULL,
      FirstName             varchar(50)                  NOT NULL,
      Phone                  varchar(15)                  NOT NULL,
      Email                  varchar(255)                 NULL)
 
You need to populate this new table with the customer information exists in a tab_delinited flat file with the following format:
Name             Phone                  E-mail
Adam Barr       555-555-1098        abarr@adatum.com
Karen Berge     555-555-7868        kberge@woodgrovebank.com

How can you transfer the data to accurately populate the customer table?
A. Import the data by using the bcp utility with the /E option to accommodate the column that has the IDENTITY property.
B. In the flat file, separate the Name column into FirstName and Lastname columns by using a bcp format file, and then import the data by using the bcp untility.
C. Import the data by using Data Transformation Services with the Transform information as it is copied to the destination option button selected.
D. Import the data by using Data Transformation Services with the Enable identity insert check box selected.
Answer: C


35) You are developing an application for a worldwide furniture wholesaler. You need to create an inventory table on each of the databases located in New York,Chicago,Paris,London,San Francisco, and Tokyo. In order to accommodate a distributed data environment, you must ensure that each row entered into the inventory table is unique across all locations.

How can you create the inventory table?
A. CREATE TABLE inventory(
Id                   int  IDENTITY      NOT NULL      
CONSTRAINT pk_inventory_id PRIMARY KEY NONCLUSTERED,
ItemName            varchar(100)      NOT NULL,
ItemDescription    varchar(255)      NULL,
    Quantity            int                 NOT NULL,
EntryDate           datetime           NOT NULL)

B. CREATE TABLE inventory(
Id                   uniqueidentifier   NOT NULL      
DEFAULT NEWID(),
ItemName            varchar(100)      NOT NULL,
ItemDescription    varchar(255)      NULL,
    Quantity            int                 NOT NULL,
    EntryDate           datetime           NOT NULL)

C. CREATE TABLE inventory(
Id                   int                 NOT NULL      
CONSTRAINT u_inventory_id  UNIQUE CLUSTERED(Id),
ItemName            varchar(100)      NOT NULL,
ItemDescription    varchar(255)      NULL,
    Quantity            int                 NOT NULL,
EntryDate           datetime           NOT NULL)

D. CREATE TABLE inventory(
Id                   int                      NOT NULL      
CONSTRAINT pk_inventory_id PRIMARY KEY CLUSTERED,
ItemName            varchar(100)      NOT NULL,
ItemDescription    varchar(255)      NULL,
    Quantity            int                 NOT NULL,
EntryDate           datetime           NOT NULL)
Answer: B

36)You need to create a development database that will hold 20 MB of data and indexes and a 4-MB transaction log. There are no concerns regarding query performance or log placement with this database. The SQL Server was installed on drive E of the server computer, and there is plenty of disk space on drive E.

How should you create the database?

A. disk init name=’development1’,physname=
‘ e:/mssql7/data/development1.dat’,vdevno=45,size=10240
    disk init name=’developmentlog1’,physname=
    ‘ e:/mssql7/data/developmentlog1.dat’,vdevno=46,size=2048
    CREATE DATABASE development on developement1=20 log on
    Developmentlog1=4
B. CREATE DATABASE development  on default=20
C. CREATE DATABASE development on primary(name=development1,
Filename=’ e:/mssql7/data/development1.mdf’,size=20MB) log
On (name=developmentlog1,filename=
‘ e:/mssql7/data/developmentlog1.ldf’,size=4MB)
D. CREATE DATABASE development on (name=developmnet1,filename
=’e:/mssql7/data/development1.mdf’,size=24MB)
Answer: C


37)You must reconcile the checking account for your company. You have a CheckRegister table, an InvalidCheck table, and a ClearedCheck table. The ClearedCheck table lists checks that have cleared the bank.

You must update the ClearedDate column of the CheckRegister table for
any checks that are on the ClearedCheck table. If the check is in the ClearedCheck table but not on the CheckRegister table, you must insert a row into the InvalidCheck table. If the amount shown for a check in the ClearedCheck table is different from the amount shown for the same check in the CheckRegister table, you must insert a row into the InvalidCheck table. Each row must be deleted from the ClearedCheck table after it has been evaluated for accuracy.

Which statement group should you use to accomplish this task in the shortest time?

A. ...
B. ...
C. UPDATE CheckRegister SET CheckRegister.ClearedDate = ClearedCheck.ClearedDate
FROM CheckRegister
JOIN ClearedCheck ON CheckRegister.CheckNumber = clearedCheck.CheckNumber
AND CheckRegister.CheckAmount = ClearedCheck.CheckAmount
DELETE ClearedCheck
FROM ClearedCheck
JOIN CheckRegister ON CheckRegister.CheckNumber = ClearedCheck.CheckNumber
AND CheckRegister.CheakAmount = ClearedCheck.CheckAmount
INSERT InvalidCheck (CheckNumber, CheckAmount, ClearedDate)
SELECT CheckNumber, CheckAmount, ClearedDate
FROM ClearedCheck
DELETE ClearedCheck

D. ...
Answer: C

38)Your company stores its sales information in a SQL Server 7.0 database
To identity the amount of sales per product in each  shipped  to customers
In Montreal in 1998.you create the following query:

SELECT p.ProductID.o.OrderID.p.ProductName.Total=p.UnitPrice*od.Quantity
      FROM Products p JOIN [Order Details] od ON p.ProductID=od.ProductID
   JOIN Order o ON od.OrderID=o. OrderID
   WHERE O.ShipCity=’Montreal’AND DATEPART(yy.o.ShippedDate)=1998
   ORDER BY p.ProductName
Which of the following execution plans is generated for this query? 
A).

B).

C).

D).


ANSWER:A
此题的答案是A,希望能通过这题看懂意思。

在考试时的题目和此题有点不同,考试时你应选B。


39).Your database application includes a complex stored procedure that displays status information as it processes transactions. You obtain unexpected results in the status information when you run the stored procedure with certain input parameters. You want to use SQL server profiler to help find the problem in your stored procedure.
Which four event should you track?(choose four)
A. sqltransaction
B. sp:stmtstarting
C. sp:stmtcompleted
D. sql:stmtstarting
E. sql:stmtcompleted
F. scan:started
G. object:opened
ANSWER: B,C,D,E

40).You are the database developer for a leasing company
Your leasing database includes a lessee table that is defined as
Followings:
CREATE TABLE lessee{
ID      int    IDENTITY       NOTNULL
CONSTRAINT      Pk_lessee_id       Primary key Non inclustered
Surname    Vchar(50)              NOTNULL
First Name       Vchar(50)             NOTNULL
SocialSecurity  Char(9)                 NOTNULL
Credit Rating   Char(10)                 NULL
Credit Limit  money               NULL}
each social security number must be unique. You want the data to be physically stored in order by social security number.
Which constraint should you add to the socialsecurityno column on the lessee table?
A. A unique clustered constraint
B. A unique nonclustered constraint
C. A Primary key Clustered constraint
D. A Primary key NonClustered constraint
ANSWER: A

41).You are implementing a logical data model for an online transaction processing(OLTP) application.One entry from the logical models in third normal form and currently has 10 attributes.
One attribute is the Primary Key.Six of the attributes are Foreign Key references into six other entries.
The last three attributes represent columns that hold numeric values.
How should this entry from the logical at model be implemented?
A. Create a table  by denormalizing the entry. Add the information from the six Foreign Key references as additional columns of the table.
B. Create two tables by denormalizing the entry. Add the Primary Key
And the three numeric values as columns of one table.Add the Primary Key and information from the six Foreign Key references as additional columns of other tables.
C. Create the table as described in the logical data model.
D. create a view that joins the six foreign key references.
ANSWER:C

42).You are design a database that will be used to store information about tasks assigned to various employees.Each tasks is assigned to
only one employee. The database  contains a table named Task that is
modeled as shown in the exhibit.
You want to use a Primary Key constraint to uniquely identity each row in the Task table.
On which columns or columns should you define the Primary Key constraint
(choose all the apply)
table:
Task
Taskno
Employeeno
Title
Description
Status
Datecompleted
A. Task No.
B. Emplyee No.
C. Status.
ANSWER: A,B,C

43).You are building a new database for the human resources department of a company.There are ten departments within the company,and each
department contains multiple employee in addtion.each employee might
work for several departments.
How should you logically model the relationship between the department
entry and the employee entry.
A.Create a mandatory one-to-many relationship between the department
entry and the employee entry.
B. Create an optional one-to-many relationship between the department entity and the employee entity.
C. create a new entity, create a one-to-many relationship from the employee entity to the new entity, and the create a one-to-many relationship from the department entity to the new entity.
D.  Create a new entity, create a one-to-many relationship from the new entity to the employee entity, and then create a one-to-many relationship from the new entity to the department entity.
ANSWER:C

44). You are building a database for the human resources department of your company. You want to eliminate duplicate entry and minimize data
storage  wherever possible. You want to track the following information
for each employee.
Which table or tables should you use?
A.
Employee
EmployeeID
ManageremplyID
FirstName
MiddleName
LastName
Address
Date of hire
Department
Salary
B.
Employee
EmployeeID
FirstName
MiddleName
LastName
Address
Date of hire
Department
Salary

Manager
ManagerID
FirstName
MiddleName
LastName
C.
Employee
EmployeeID
ManagerID
FirstName
MiddleName
LastName
Address
Date of hire
Department
Salary
Manager
ManagerID
FirstName
MiddleName
LastName
D.
Employee
EmployeeID
FirstName
MiddleName
LastName
Address
Date of hire
Department
Salary

Manager
ManagerID
FirstName
MiddleName
LastName

Employee Manager
EmployeeID
ManagerID
ANSWER:A

45). You are designing a data model to track purchases for a fish canning company. You take the following actions
1.Create the data model as shown in the exhibit.
...
Which results does these actions produced (choose all the apply)
A. all purchase amounts are rounded to the nearest whole dollar.
B. Record for tuna purchased must be in multiples of 100, and records for salmon purchased must be in multiples of 50.
C. The daily total quantity of salmon purchased cannot exceed 150.
D. The monthly government report can be produced reporting the kind, quantity, and supplier for each lot of tuna or salmon purchased.
         
ANSWER:A,C,D
Company
CompanyID(PK)
   
Purchase
PurchaseID(PK)
PurchaseID
PurchaseDate

          
Fish
FishName

PurchaseDetail
PurchaseID(PK)
LineNumber(PK)
FishName
FishQuantity
Purchaseamount

46). You are implementing a logical data model for a decision support system(DSS)database. Two of the tables in the model have a parent/child relationship. The parent table is expected to have more than 1 million rows. The child table is expected to have more than 100 million rows.
Most reports present aggragate child information grouped accounting to each parent row reports containing detailed child table information
are occasionly needed.
How should tables be implemented?
A. Create the parent table and the child table as described in the logical data model
B. Create the parent table that include aggregate child information, do not create the child table.
C. create the parent table that include aggregate child information,
 Create the child table as it exists in the logical data model
D. in child table, create a nonclustered index that includes any columns that are aggregated and the foreign key reference to the parent.
ANSWER:C

47).Your are building an invocing system for your company.
there can be multiple parts on one invoice, cost and price information comes from a master list but the history store for each invoice should show the cost and price at time of sale you want to make sure your database is properly normalized.
You want to accomplish the following goals
1. Every table must have a Primary Key.
2. All non-key columns must depend on the whole Primary Key.
3. All columns must contain exactly one value
4. Each column in a table must be independent of any non-key column
in the same table
You create the logical model as show in the exhibit
Which result or results does this model produce(choose all the apply)

Part
PartNo(PK)
PartDescription
Unitweight
UnitCost
UnitPrice

Orders
OrdersNo(PK)
OrdersDate
 

Customer
CustomerNo(PK)
CustomerName
CustomerAddress1
CustomerAddress2
City
Territory
Postal Code
       
OrderDetails
OrdersNo(PK)
ItemNo
CustomerNo
PartNo
PartDescription
QuantitySold
Unitweight
UnitCost
UnitPrice

A. every table has a primary key
B. all non-key columns depend on the whole primary key
C. all columns contain exactly one value
D. each column in a table is independent of any non-key column in the same table.
ANSWER:A,C

48).Your database include a Salesperson table that tracks various data including the sales goals and actual sales for individual salespeople.
The sales manager wants a report containg a list of the five least
Productive salepeople,along with their goals and their actual sales
Production.You will use an ascending sort to order the information
In the report by actual sale production.
What should you do to produce report?
A. Issue a set rowcount 5 statement before issuing a select statement against the salesperson table
B. Include a Top 5 clause in the select list against the salesperson table
C. Issue a set query_governor_cost_limit 5 statement before issue select list against the salesperson table.
D. Count the row returned by using a having count(*)<=5 clause.
ANSWER:B

49).You development team has just developed tested and developed a new accounting application that including many integrated modules.Users frequently encounter deadlocks whenever someone performs a function that integrates data from multiple modules.The
development team never encounted deadlocks when unit testing the application.
What can you do to minmize deadlock?
A. Set the deadlock priority to low
B.issue the begin distributed transaction statement whenever data modifications involve data in different modules
C.ensure that all transactions modify tables in the same order.
D.Ensure that all tables have indexes on primary and foreign keys.
ANSWER: C

50).You issure an UPDATE statement and then run a select  query to verify that the update were accurate .You find out the UPDATE statement
was excecuted properly.However,the next time you log on to the SQL Server computer.It appears that your UPDATE statement was not excecuted
What is the most likely cause the problem?
A.The FMTONLY option is set to on
B.the showplan_all option is set to on
C.the parseonly option is set to on
D.the implicit_transactions option is set to on
ANSWER: D

51)You are investigating reported problems regarding the performance of a query in your database .The WHERE clause of the query includes
search arguments on ColumnA, ColumnB, ColumnC
You analyze the data and discover that the content of ColumnA
is nearly identical in all rows.The content of ColumnB is the same in about 50 percent of the rows.
The content of ColumnC is the same in about 10 percent of the rows
How should you index the table to improve query performance?
A.create a composite clustered index on columnA,columnB,columnC
B.create a composite clustered index on columnC,columnB,columnA
C.create a composite nonclustered index on columnA, columnB, columnC
D.create a composite nonclustered index on columnC, columnB, columnA
E.create a clustered indes on columnA, create separate nonclustered indexes on columB and columnC
F.create a separate nonclustered indexes in each column.
ANSWER:B

52).Your shipping company has a database application that maintains
an invetory of items on each vessel.When each vessel is unloaded at
its destination.The invetory is counted and the arrived-quantity column is updated in the database.
There can be thousands of vessels en route at any one time.
Each shipments id identified by a shipment_id.Each vessel can carry
Thousands of items.Each item in a shipment is identified by an item_number.
You want to make sure the update of the arrived-quantity column is as fast as possible .
What should you do ?
A. create a nonclustered index on the shipment_id column, the item_number column, and the arrived_quantity column.
B. create a clustered index on the shipment_id column, the item_number column, and the arrived_quantity column.
C. Create a clustered index on the shipment_id column and item_number column.
D. Create a nonclustered index on the shipment_id column and item_number column.
ANSWER:C

53).You have a database that contains information abuot publications for sale.You want to write a full-text search query that will search
through all the columns in one table enabled for full-text querying.
The table includes a column named titles. a column named price
and a column named notes.The titles and notes  column are full-text enabled.
you want to find all publications that deal with French gourmet cooking, which contains statement should you use?
A. where contains(*, ’”French gourmet”’)
B. where contains(notes, ’”French gourmet”’)
C. where contains(titles, ’”French gourmet”’)
D. where contains(price, ’”French gourmet”’)
ANSWER: A

54)You need to create two new tables for your Purchaseing database
The new tables will be named Purchaseorderheader and Purchaseorderline
The Purchaseorderheader table will have the PurchaseorderheaderID columns as the Primary Key.A Purchaseorderline row must not exist without a corresponding Purchaseorderheader row.
How can you create the tables?
A.  Create both tables and then use the ALTER TABLE statement to create a CHECK constraint on Purchaseorderline Primary Key column.
B.  Create both tables, and then use the the alter table statemetn to create a check constraint on the purchaseorderheader primary key column
C. Create the purchaseorderheader table and then create the purchaseorderline table that has a foreign key constraint referencing the purchaseorderheader table
D. Create the purchaseorderline table, and the create the purchaseorderheader table that has a foreign key constraint referencing the purchaseorderline table
ANSWER:C

55).You have a database that is used for storing the text of speeches given by certain government officials.
The text of each speech is stored in the speeches table that is defined
as follows:
CREATE TABLE Speeches{
  SpeechID   Char(32),
  SpeechText Text,
  AuthorID   Char(32)}
GO
A full-text indexs exist for all columns in the speeches table .You want to search for a speech that includes the phrase
“ Four score and seven years ago”
which query should you use to perform this search?
A. where speechid like ‘% Fore Score and only %’
B. where speechtext like ‘% Fore Score and only %’
C. where freetext (speechid, ‘ Fore Score and only ‘’)
D. where freetext (speechtext, ‘ Fore Score and only ‘’)
ANSWER:D

56).You need to create a 6-GB online transaction processing(OLTP)database,Your SQL Server computer has 2 disk controllers and each controller has 4 6-GB hard disk drivers.Each hard
disk drive is configured as a separate NTFA partion.Microsoft WindowsNT
.The Microsoft WindowsNT swap file and SQL Server are all installed on drive C .The remaining drivers,which are labled as drive D through J are empty.
How should you create OLTP database?
A.  Create the data portion of the database as a single file on D, create the log on E
B.  Create the data portion of the database as six separate files on D through I, with one file on each drive, create log as a single file on J
C. Create the data portion of the database as 24 separate files on D through I, with four files on each drive, create the log as a single file on J
D. Create the data portion of database as four separate files on D through G, with one file one each drive, create the log as thress separate files on H through J
ANSWER:B

57).Department managers in your company want to use Microsoft Excel pivot tables analyze data from your SQL Server database.You need to
extract data from tables in the database to an Extent spreadsheet so
that managers can copy the spreadsheet and build pivot tables
The data in database changes frequently and you want to automate the
Process of updating the Excel spreadsheet.
you plan to use a sql server agent scheduled job to automation the extraction of the data to the spreadsheet.
What should the scheduled job execute?
A. a bulk copy program script to create a tab_delimited text file
B. a bulk copy program script to populate the spreadsheet
C. a data transformation services export package to create a tab_delimited text file
D. a data transformation services export package to populate the spreadsheet
ASNWER:D

58).Your users report that your database application takes an excessive amount of time to complete an operation.Overtime.with the addition
of new rows and changes to existing rows the situation has worsened.
You suspect that the tables are not optimally indexed
You plan to use the SQL Server Profiler Create Trace wizard to find out the cause of the problems
What should you use the create Trace Wizard to do (choose two)
A. Find the worst performing queries
B. identify scans of large tables
C. identify the cause of a deadlock
D. profile the performance of a store procedure
E. trace T-SQL activity by application
F. trace T-SQL activity by user
ANSWER: A,B

59) You run the DBCC SHOWCONTIG statement, the statement provides the following output.
---Page scanned ------------------------------158
---Extends scanned ---------------------------21
---Extend Switch------------------------------20
---Avg.Pages per Extent ----------------------7.5
---Scan Destiny(Best Count:Actual Count) -----95.24%(20:21)
---Extent scan Fragmentation------------------4.76%
--- Avg.Bytes Free per page ------------------408.4
--- Avg.Page Destiny(full)  -------------------94.95%
What does this output tell you about how the data is stored ?
(choose all the apply)
A. The table is not externally fragmented
B. the table is not internally fragmented
C. the number of extent switches is excessives
D. the rowsize does not efficiently fit on a page
E. the IAM page does not reflect the actual extent usage
ANSWER:A,B

60).You add new fuctionally to an existing database application.
After the upgrade.Users of the application report slower performance .
The new fuctionally executes multiple store procedure and dynamic
SQL statement.You want to be able to identity specific queries
That are encountering excessively long execution times.
What should you do ?
A. Run the sql server undex tuning wizard to retune the indexes for the new functionality
B. create a sql server profiler trace that uses the minimum execution time and application filters
C. use the current activity dialog box of the sql server enterprise manager to list all current user tasks and object blocks.
D. Use the sp_monitor store procedure to monitor the cpu_busy and io_busy column before and after testing the new fuctionally.
ANSWER:B

61).You are troubleshooting a process that makes use of multiple complex stored procedure that operate on a table.
The process is producing an unexpected update to the table
You want to identity the specific stored procedure and statement that
are causing the problems.
What should you do ?
A. Use sql server profiler to create and reply a trace by using single stepping.
B. place a trigger on the table to send an e-mail message when a column is set to a specific value
C. execute the store procedure’s create procedure statement to verify that the syntax is still valid
D. Examine the transaction log locate the statement that make the unexpected update to the table
ANSWER:A

62). You run the DBCC SHOWCONTIG statement on the sales table and receive
The following output:
---Page scanned ------------------------------1657
---Extends scanned ---------------------------210
---Extend Switch------------------------------1528
---Avg.Pages per Extent ----------------------7.9
---Scan Destiny(Best Count:Actual Count) -----13.60%(208:1529)
---Extent scan Fragmentation------------------97.43%
--- Avg.Bytes Free per page ------------------2843。5
--- Avg.Page Destiny(full)  -------------------64.87%
What should you do to improve the response times for querise?
A. Update the statistics on the Sales table
B. Create additional statistics on the Sales table.
C. Run the DBCC DBREINDEX statement on the Sales table.
D   Run the DBCC CHECKTABLE statement on the Sales table.
ANSWER: C

63). You run the DBCC SHOWCONTIG statement on the table and receive
The following output:
---Page scanned ------------------------------354
---Extends scanned ---------------------------49
---Extend Switch------------------------------253
---Avg.Pages per Extent ----------------------7.2
---Scan Destiny(Best Count:Actual Count) -----17.79%(45:94)
---Extent scan Fragmentation------------------82.21%
--- Avg.Bytes Free per page ------------------485.2
--- Avg.Page Destiny (full)  -------------------94.01%
what should you do to improve the query performance ?
A. Update the statistics on the clustered index.
B. Change the row size to fit efficiently on a page.
C. Rebulid the clustered index with a fill factor value set to 100.
D. Rebulid the clustered index with a fill factor value set to 25.
E. Rebulid the clustered index with a fill factor value set to 75.
ANSWER: E

64)You have an accounting application that allows users to enter
information into a table named staging
when data entry is complete a batch job users .
the rows in the staging table to update a table named prodution
Each users rows in the staging table are identified by the user’s
SQL Server Process ID number in a column named spid
the code for the batch job that updates the production table is:
declare @count int
begin tran
select @count=count(*) from production p join staging a on p.account=s.account where s.spid=@@spid
update p set amount=s.amount from production p join staging s on p.account=s.account where s.spid=@@spid
if @@rowcount<>@count
rollback tran
else
commit tran
You find out that there have been locking problems when two users run
the batch job at the same time.What should you do to solve the locking
problems?
A. Program the accounting application to set the transaction isolation
level to SERIALTABLE before running the batch job.
B. Program the accounting application to set the transaction isolation
level to READ UNCOMMITTED before running the batch job.
C. program the accounting application to set the deadlock priority to normal before running the batch job
D. program the accounting application to set the deadlock priority to low before running the batch job
E. inclued the table hit WITH ROWLOCK UPDLOCK when counting the rows
in the production table.
F. inclued the table hit WITH TABLOCKX when counting the rows
in the production table.
ANSWER: E

65).You are implementing a logical data model.All of the tables in your logical data model are normalized to at least third normal form
There are no surrogate Primary keys in any of the tables.Some table
relationships involve up to eight levels of parent,child grand child
and so forth, in the model ,the Primary key of each descendent table
inherts Primary key of all ancestor tables.
you want to accomplish the following goals:
.allow tables at any level in the hierarchy to be joined to any other table in the hierarchy.
.ensure that tables are joined on a single column.
.limit the index length of primary keys to 10 bytes or less.
.ensure that key columns are always compared on a bunary basis regardless of which options were selected during the installation of sql server.
You take the following actions.
1.Implement the data model as is.
2.Create indexs on all foreign keys.
Which result or results do these actions produce(choose all the apply)
A. tables in the data model hierarchy can be joined to any other table in the hierarchy
B. tables are joined on a single column
C. The Index length of all Primary keys is 10 bytes or less.
D. Key columns are always compared on a binary basis regardless of
Which options were selected during the installtion of SQL Server.
ANSWER:A,B,C,D

66).You are implementing a marketing application that was not origianlly designed replication.You need to use replication to distribute data among four separate SQL Server .You also need to allow
users to enter data into a table named sales from any location and have the data distributed to all locations.
When creating tables,you must follow your company programming standards which reqire the use of surrogate Primary key in all tables.
What should you do ?
A. Create a primary key column that uses the identity property with a step of 1 and a separate starting number for each server so that the first server starts at 1, the second server starts at 10000001, and so forth.
Replicate new rows by using store procedure that include the following command: set identity_insert sales on
B. Create a primary key column that uses the identity property with a step of 4 and a separate starting number for each server so that the first server starts at 1, the second server start at 2, and so forth
Replicate new rows by using store procedure that include the following command: set identity_insert sales on
C.Create a Primary key column that uses the timestamp data type.
D.Create a Primary key column that uses the uniqueidentifier data
Type and a default constraint of NEWID()
ANSWER: D

67).You have existing data that must be moved into a new database.The
data is stored in two tables named table A and table B.Each table
contains more than 100 million rows.You know that application are joining table A to table B by using a cluster index.
Fast response time is critical ,in the past separating the tables from
The rest of the database and placing them onto separate physical disks
has improved response time.
In the new database.how should you save the data in table A and       table B in order to reduce response time when joining the two tables.
A. In the database, create two segments, named segmentA and segmentB, that point to separate physical disk drives. Remove the default and system segments from the devices where segmentA and segmentB reside.
Create tableA and any associated index on segmentA.
Create tableB and any associated index on segmentB.
B. create two filegroups, named filegroupA and filegroupB, as part of the database on separate physical disk drives.
Create tableA and any associated index on filegroupA.
Create tableB and any associated index on filegroupB.
C. create three filegroup named filegroupA and filegroupB and filegroupC, as part of the database on separate physical disk drives.
Create tableA and any associated index on filegroupA.
Create tableB and any associated index on filegroupB.
Create all clustered and nonclustered indexes for tableA and tableB on filegroupC.
D. create the data portion of the database on a RAID 5 device, create tableA and tableB and all indexes in the database.
E. Create the data partion of the database on a RAID1 +0 the device
Create table A and table B and all indexes in the database.
ANSWER:B

68).You are the database manager for a manufacturing company.You are
receiving the mixture of online transaction processing(OLTP) and
decision support system(DSS) activities that from the shop floor in real time.The application must report production results trends and
variances in a timely fashion.
What should you do to maxmize the overall performance of the production
Monitoring application?
A. Add additional indexes to support both OLTP and DSS requirements.
B. create an OLTP sql server and a DSS sql server replicate table from the OLTP sql server to a DSS sql server with the distribution database on the DSS server. Index the tables on each sql server to support their respective activities.
C. create an OLTP sql server and a DSS sql server. On the OLTP sql server, index the tables to support the OLTP requirements every 15 minute, backup the log from OLTP sql server and restore it on the DSS sql server. Reindex the table on the DSS sql server to support the DSS activities.
D.Creat an OLTP database and a DSS database on the Same SQL Server
In the OLTP database ,create triggers that automatically update tables
In the DSS database
ANSWER: B

69) You need to produce a sales report listing all salesperson numbers
sales amounts ,and order dates. You want the report stored from most
recent sales to oldest sales. For each day, you want the sales amounts
stored from highest to lowest.

You will be selecting this information from a table that is defined
As follow:

   CREATE TABLE Saleinformation
       (Sales informaitonID int IDENTITY(1,1)   NOT NULL
     PRIMARY KEY NONCLUSTERED.
   SalePersonID               int      NOT NULL
   RegionID                    int      NOT NULL
   ReceiptID      int      NOT NULL
   SalesAmount      money     NOT NULL
   OrderDate      datetime     NOT NULL}

Which query will accurately produce the report?

A) SELECT SalePersonID, SalesAmount ,OrderDate 
   FROM Saleinformation
  ORDER BY OrderDate ,SalesAmount DESC
B). SELECT SalePersonID, SalesAmount ,OrderDate 
   FROM Saleinformation
  ORDER BY SalesAmount, OrderDate DESC
C). SELECT SalePersonID, SalesAmount ,OrderDate 
   FROM Saleinformation
  ORDER BY OrderDate DESC,SalesAmount DESC
D) . SELECT SalePersonID, SalesAmount ,OrderDate 
   FROM Saleinformation
  ORDER BY SalesAmount DESC, OrderDate DESC
ANSWER:C

70). You have an accounting application that captures batches of transactions into a staging table before being proceed. Processing
can be performed on individual batches or on the whole staging table
Processing includes many validations before updating any of the
Production tables.

you take the following actions:
...

Which result or results do these actions produce? (choose all apply)
A. Deadlocks are avoided or handled appropriately.
B. Each batch of transactions is accepted or rejected.
C. Users to access the production tables while accounting
Transactions are being processed.
D. Resource locking is minimized
ANSWER B,C,D

71) You database stores telephone number. Each telephone number
is stored as an integer. You must format the telephone number to
print on a report in the following format:

(999)-999-999

You have selected the phone number into a local variable as follows:
   DECLARE @ PhoneNumber int
Which statement will correctly format the number?

A. Select’PhoneNumber’=’(‘+SUBSTRING(CONVERT(varchar(10),
@PhoneNumber),3,0)+’)’+SUBSTRING(CONVERT(varchar(10), honeNumber),3,3)+’-‘+ SUBSTRING(CONVERT(varchar(10), PhoneNumber),4,6)
B. Select’PhoneNumber’=’(‘+SUBSTRING(CONVERT(varchar(10),
@PhoneNumber),3,1)+’)’+SUBSTRING(CONVERT(varchar(10), honeNumber),3,4)+’-‘+ SUBSTRING(CONVERT(varchar(10), PhoneNumber),4,7)
C. Select’PhoneNumber’=’(‘+SUBSTRING(CONVERT(varchar(10),
@PhoneNumber),0,3)+’)’+SUBSTRING(CONVERT(varchar(10), honeNumber),3,3)+’-‘+ SUBSTRING(CONVERT(varchar(10), PhoneNumber),6,4)
D. Select’PhoneNumber’=’(‘+SUBSTRING(CONVERT(varchar(10),
@PhoneNumber),1,3)+’)’+SUBSTRING(CONVERT(varchar(10), honeNumber),4,3)+’-‘+ SUBSTRING(CONVERT(varchar(10), PhoneNumber),7,4)
ANSWER:D


 72).Your Orders table is defined as follows:
     CREATE TABLE Orders{
  OrderID        int IDENTITY(1,1)        NOTNULL
  SalesPersonID int       NOTNULL
  RegionID       int       NOTNULL
  OrderDate      datetime      NOTNULL
OrderAmount   int       NOTNULL}
The table is becoming too large to manage.You must delete all
Sales that are more than three years old.

Which query will accomplish the desired result?
A. Delete from Orders Where OrderDate < DATEADD(YY,-3,GETDATE())
B. Delete from Orders Where OrderDate < DATEADD(YY,3,GETDATE())
C. Delete from Orders Where OrderDate < GETDATE(), -3
D. Delete from Orders Where OrderDate < GETDATE(), +3
  ANSWER:A

73). You are developing a sales database for a company that has a 100
person sales staff. The company’s policy requires that any sales orders
in excess of $100,000 be approved and entered into the database by
the sales manager

Your database includes a SalesOrder table that is defined as follows:

    CREATE TABLE Saleorder {
     Number      char(10)       NOTNULL
     Saleperson varachar(50)  NOTNULL
     Amount      money           NOTNULL}

You need to create a view on the SalesOrder table that will prevent
The sales staff from entering a sales order in excess of $100,000.
Which view should you write?

A. CREATE VIEW SaleOrderLimit
 AS SELECT Number,Saleperson,Amount
FROM SaleOrder
WHERE Amount<=100000
WITH CHECK OPTION
B. CREATE VIEW SaleOrderLimit
 AS SELECT Number,Saleperson,Amount
FROM SaleOrder
WHERE Amount<=100000
C. CREATE VIEW SaleOrderLimit(Number,Saleperson,Amount)
 AS SELECT Number,Saleperson,SUM(Amount)
FROM SaleOrder
GROUP BY Number. Saleperson
HAVING(SUM(Amount)<=100000)
D. CREATE VIEW SaleOrderLimit
 AS SELECT TOP 100000
 Number,Saleperson,Amount
 FROM SaleOrder
 WHERE Amount<=100000
 ORDER BY Amount

ANSWER :A

74). Your database includes a table named SaleInformation that tracks sales by region. The tables is defined as follows:

CREATE TABLE Saleinformation
       (Sales informaitonID int IDENTITY(1,1)   NOT NULL
     PRIMARY KEY NONCLUSTERED.
   SalePersonID               int      NOT NULL
   RegionID                    int      NOT NULL
   ReceiptID      int      NOT NULL
   SalesAmount      money     NOT NULL}

Your database also includes a table named SalePerson that is defined
As follows:
CREATE TABLE SalePerson
       (SalePersonID int IDENTITY(1,1)          NOT NULL
     PRIMARY KEY NONCLUSTERED.
   RegionID                    int      NOT NULL
   LastName      varchar(30)   NOT NULL
   FirstName      varchar(30)   NULL
   MiddleName      varchar(30)   NULL
   AddressID                    int      NULL}

You want to ensure that each saleperson enters sales only in the
Saleperson’s  own region. Which of the following actions can you perform to accomplish this task?

A. Place a FOREIGN KEY constraint on the SalesInformation table that relates  to the SalePerson table.
B. Place a FOREIGN KEY constraint on the SalePerson table that relates  to the SalesInformation table.
C. Create a trigger on the SalesInformation table that verities
That the region for the sale in the same as the region for the
Saleperson.
D. Create a trigger on the Saleperson table that verities
That the region for the sale in the same as the region for the
Saleperson.
ANSWER:C

75). Your database includes a table that is defined as follows:
     CREATE TABLE Orders{
  OrderID        int IDENTITY(1,1)        NOTNULL
  SalesPersonID int       NOTNULL
  RegionID       int       NOTNULL
  OrderDate      datetime      NOTNULL
  OrderAmount   int       NOTNULL}
The sales manager wants to see a report that shows total sales by
region as well as a grand total of sale.

Which query can you use to create the report?

A. SELECT SalePersonID, RegionID OrderAmount
 FROM Orders
 ORDER BY RegionID
 COMPUTE SUM(OrderAmount)
B. SELECT SalePersonID, RegionID OrderAmount
 FROM Orders
 ORDER BY RegionID
 COMPUTE SUM(OrderAmount) BY RegionID
 COMPUTE SUM(OrderAmount)
C. SELECT SalePersonID, RegionID SUM(OrderAmount)
 FROM Orders
 ORDER BY SalePersonID ,RegionID
D. SELECT SalePersonID, RegionID SUM(OrderAmount)
 FROM Orders
 ORDER BY SalePersonID ,RegionID
HAVING SUM(OrderAmount)=RegionID
ANSWER:B

76). Your company has a headquarters sales office and two remote sales
offices. Each sales office has a database containing an Orders table
that is defined as follows
  CREATE TABLE Orders{
  OrderID        int IDENTITY(1,1)        NOTNULL
  SalesPersonID int       NOTNULL
  OrderDate      datetime      NOTNULL
  OrderAmount   int       NOTNULL}

You want a report in the following format that combines data for
All orders from headquarters and the other two sales offices:

Office   OrderID   SalePersonID     OrderDate  OrderAmount

The server in the headquarters office is named HQ,and the servers in
The reomte sales offices are named RS1 and RS2.

Which query can you use to produce the report?
A.  ...
B. ...
C. SELECT Office=’HQ’,OrderID,SalePersonID,OrderDate,OrderAmount
 FROM HQ.Sales.dbo.Order
 UNION ALL
 Office=’RS2’,OrderID,SalePersonID,OrderDate,OrderAmount
 FROM RS2.Sales.dbo.Order
 UNION ALL
 Office=’RS1’,OrderID,SalePersonID,OrderDate,OrderAmount
 FROM RS1.Sales.dbo.Order
D. ...
ANSWER:C

 

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/42924.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

软考-数据库系统工程师

软考之数据库系统工程师 前言 怎么会突然想起来考这个呢&#xff1f;说实话很惭愧&#xff0c;大学四年&#xff0c;毕业两年多都不知道有这个证书&#xff0c;也是今天看朋友圈看到一个学妹晒出了自己的这个证书&#xff0c;也是软考但是不是数据库系统工程师&#xff0c;她…

数据库(SQL Sever)水平证书复习题

全国信息技术水平考试数据库应用系统设计技术水平证书&#xff08;SQL Server&#xff09;复习题 第一大题&#xff1a;单选题 数据库系统是数据库、硬件、软件和&#xff08; &#xff09;的集合体。 (A) DBMS (B) DBS © DB (D) DBA下列选项中&#xff0c;不属于数据模…

软考中级——数据库系统工程师

第十一章—事务管理 自己关于备考软考中级&#xff08;数据库系统工程师&#xff09;的一些知识点的补充文章目录 第十一章—事务管理1 事务的基本概念1.1事务定义语句1.2 事务的特性&#xff08;ACID&#xff09; 2 数据库的并发控制2.1事务调度2.2并发操作带来的问题2.3两段…

全国OSTA计算机高新技术SQLSever数据库四级证书--考证复习知识点集合(附下载地址)

全国OSTA计算机高新技术SQLSever四级证书 说明&#xff1a;没找到一样的图、但长就是长这样的。分享给即将考证的朋友们&#xff01;适合临场考试复习整理思路理顺&#xff01;有其他点问题欢迎提出&#xff01;谢谢&#xff01;祝逢考必过&#xff01;&#xff08;文末有文档下…

MSSQL SERVER DBA 2门证书考试

微软Microsoft 70-764 - Administering a SQL Database Infrastructure 和 70-765 - Provisioning SQL Databases考试心得 由于公司不涨工资&#xff0c;所以只能硬着头皮考个DBA证书增添个人色彩了。 微软的DBA需要2个考试&#xff0c;70-764和70-765. 由于疫情&#xff0c;所…

aspnet+sqlserver英语等级考试报名系统

考试报名数据处理系统的开发目的是使考试报名数据处理模式从手工记录转变成信息管理&#xff0c;为考试报名数据处理人员提供方便条件。对考试报名的实际情况进行调研之后&#xff0c;进行详细的需求分析&#xff0c;目 录 摘 要 I Abstract II 1 引言 1 1.1 项…

华为鸿蒙HarmonyOS 4定档8月;ChatGPT之父的加密货币正式上线;微软必应聊天将推出重新生成答案功能|极客头条

「极客头条」—— 技术人员的新闻圈&#xff01; CSDN 的读者朋友们早上好哇&#xff0c;「极客头条」来啦&#xff0c;快来看今天都有哪些值得我们技术人关注的重要新闻吧。 整理 | 梦依丹 出品 | CSDN&#xff08;ID&#xff1a;CSDNnews&#xff09; 一分钟速览新闻点&…

注册Gmail邮箱

注册Gmail邮箱 文章目录 注册Gmail邮箱1. 安装谷歌浏览器2. 注册Gmail邮箱2.1 创建新账号2.2 填写信息2.3 设置邮箱地址2.4 不用填号码直接跳过&#xff01;2.5 同意隐私条款2.6 返回登录2.7 访问油管验证成功 1. 安装谷歌浏览器 vivo自带的应用商店 2. 注册Gmail邮箱 2.1 …

Email,电子邮箱免费注册流程

工欲善其事&#xff0c;必先利其器。要做好工作&#xff0c;先要有锋利的工具。日常办公也是一样&#xff0c;申请一个好用的电子邮箱很重要&#xff0c;同事客户之间发送个文件都可以通过电子邮箱进行&#xff0c;并且现在很多平台注册帐号都需要填写email&#xff0c;比如百度…

Petya勒索病毒

Petya勒索病毒 1、原理说明 2017年6月27日晚&#xff0c;印度、俄罗斯、西班牙以及欧洲多国遭受大规模Petya勒索病毒袭击&#xff0c;该病毒远程锁定设备&#xff0c;并索要赎金。其中乌克兰地区受灾害最为严重&#xff0c;政府、银行、电力系统、通讯系统、企业等都受到不同…

OilRig APT 组织或在中东地区发动更多 IT 供应链攻击

聚焦源代码安全&#xff0c;网罗国内外最新资讯&#xff01; 编译&#xff1a;代码卫士 专栏供应链安全 数字化时代&#xff0c;软件无处不在。软件如同社会中的“虚拟人”&#xff0c;已经成为支撑社会正常运转的最基本元素之一&#xff0c;软件的安全性问题也正在成为当今社会…

ChatGPT的副作用逐渐显现:人逐渐“废柴化“

源&#xff5c;APPSO 随着 ChatGPT 风靡全球&#xff0c;越来越多人开始将其融入到工作/学习流当中&#xff0c;久而久之人们开始依赖于 AI 的帮助&#xff0c;丧失了原有的一些技能。美利坚大学语言学名誉教授 Naomi SBaron 就此提出了自己的一些担忧。 原文标题&#xff1a;H…

被疯狂转发的几件事。。。

点击上方“AI遇见机器学习”&#xff0c;选择“星标”公众号 第一时间获取价值内容 今天聊聊这两天互联网上发生的那些事。 一、青语言开源发布 长久以来&#xff0c;中文编程一直是开发者社区中争议不断的热点问题。 一部分人认为&#xff0c;编程语言是符号化的语言&#xff…

一个代码拼写错误引发微软Azure故障,17 个生产级数据库被删

出品 | OSC开源社区&#xff08;ID&#xff1a;oschina2013) 5 月 24 日&#xff0c;微软 Azure DevOps 在巴西南部地区的一处 scale-unit 发生故障&#xff0c;导致宕机约 10.5 个小时。近日&#xff0c;微软首席软件工程经理 Eric Mattingly 出面针对此次故障事件道歉&#x…

AI专属社交平台爆火,全体人类被禁言只能围观

衡宇 发自 凹非寺量子位 | 公众号 QbitAI 玩腻了推特和微博&#xff1f;有个新的社交平台火爆外网&#xff01; 成千上万的用户连夜涌入&#xff0c;每日积极发帖&#xff0c;活跃度堪比ChatGPT。 就连马一龙都来开辟了自己的嘴炮新阵地&#xff1a; OpenAI CEO山姆奥特曼也成为…

chagpt

首发网站 天风的人工智能小站 前言&#xff1a;chatGPT 一、注册 https://chat.openai.com/auth/login 1、点击右边注册sign up 2、可以使用自己的邮箱或者谷歌邮箱注册 验证邮箱输入姓名 3、现在到了最关键的一步 因为openAI不对中国开放所以&#xff0c;必须要购买一个外…

真实揭露:一段激情视频裸聊被骗的经历

故事发生前几天放假的时候&#xff0c;那天我正在海边散步&#xff0c;有个人加我微信&#xff0c;备注了求助信息。当我通过了他的好友验证请求&#xff0c;他给我发来这样一段话。出于对luo聊诈骗套路的好奇&#xff0c;我对他的这段经历进行深入的了解。 本文已获得受害者本…

一文给你讲透 ARP 协议原理!

我把自己以往的文章汇总成为了 Github &#xff0c;欢迎各位大佬 star https://github.com/crisxuan/bestJavaer 公众号连载计算机网络文章如下 ARP&#xff0c;这个隐匿在计网背后的男人 我画了 40 张图就是为了让你搞懂计算机网络层 40 张图带你搞懂 TCP 和 UDP 拿下计网…

万万没想到,“红孩儿”竟然做了程序员,还是CTO!

点击上方“码农突围”&#xff0c;马上关注 这里是码农充电第一站&#xff0c;回复“666”&#xff0c;获取一份专属大礼包 真爱&#xff0c;请设置“星标”或点个“在看”作者 | 年素清来源 | 码农故事汇 01 说起老版《西游记》&#xff0c;大家肯定都不陌生&#xff0c;毕竟这…

马化腾回忆创业:曾假扮女孩子陪聊

http://comment.tech.163.com/tech_bbs/AR0O2L7200094OE0.html 昨天&#xff0c;香港大学举办以创新创业为主题的Dream Catchers论坛。其中腾讯董事局主席马化腾在下午两点四十五分在李兆基会议中心做了专题演讲&#xff0c;分享了自己的创业经历并回答了媒体人张力奋有关产品、…