SQL Course 1 70-431

Database Management

Information Technology

ISO/IEC 9075

ANSI SQL

Reviewed by Editorial Team

The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.

Learn about Our Editorial Process

| By Sabio

Sabio

Community Contributor

Quizzes Created: 1 | Total Attempts: 150

| Attempts: 150 | Questions: 19

Share on Facebook Share on Twitter Share on Whatsapp Share on Pinterest Share on Email Copy to Clipboard Embed on your website

Question 1 / 19

0 %

0/100

Score 0/100

1. You are responsible for importing data into SQL Server 2005 databases. Your department is starting to receive text files that contain sales transactions from stores across the country. Columns in the data are separated by semicolons. You need to import the files into the sales database. What should you do?

Create a custom format file, specifying a semicolon as the row terminator.

Use the bcp command, specifying a semicolon as the field terminator.

Use the bcp command with the default arguments.

Use the BULK INSERT statement with the default arguments.

To import the text files containing sales transactions into the sales database, the most appropriate option is to use the bcp command with a semicolon specified as the field terminator. The bcp command is a utility that allows for bulk data import and export in SQL Server. By specifying a semicolon as the field terminator, the command will correctly parse and separate the columns in the data. This ensures that the data is imported accurately into the sales database.

Explanation

Submit

About This Quiz

This quiz assesses advanced skills in managing and querying databases using SQL Server 2005. It covers topics such as linked servers, protocol configurations, and optimization techniques, essential for database administrators and IT professionals.

What's your name? Personalize your quiz and earn a certificate with your name on it!

2. You configure a new SQL Server 2005 computer to use TCP/IP with all default settings. Your corporate policy requires that each server use a firewall. You find that you can connect to the SQL Server instance from the local computer. However, client computers cannot connect to the SQL Server instance. You need to identify the most likely cause of the connection issues. What should you do first?

Ensure that port 1433 is open in your firewall.

Ensure that port 443 is open in your firewall.

Ensure that client computers connect by using Shared Memory protocol.

Ensure that the server is not paused.

The most likely cause of the connection issues is that port 1433, which is the default port for SQL Server, is not open in the firewall. By ensuring that port 1433 is open in the firewall, client computers will be able to establish a connection with the SQL Server instance.

Explanation

Submit

3. A power failure occurs on the storage area network (SAN) where your SQL Server 2005 database server is located. You need to check the allocation as well as the structural and logical integrity of all databases, including their system catalogs. What should you do?

Execute DBCC CHECKFILEGROUP for each filegroup.

Execute DBCC CHECKCATALOG.

Execute DBCC CHECKDB.

Execute DBCC CHECKTABLE for each table.

In this scenario, where a power failure has occurred on the storage area network (SAN) where the SQL Server 2005 database server is located, the best course of action is to execute DBCC CHECKDB. This command checks the allocation, structural integrity, and logical integrity of all databases, including their system catalogs. It is a comprehensive command that covers all aspects of checking the databases, making it the most suitable option in this situation.

Explanation

Submit

4. Your application must access data that is located on two SQL Server 2005 computers. One of
these servers is named SQL1 and the other is SQL2. You have permissions to create a stored
procedure on SQL1 to support your application. However, on SQL2 you only have permissions to select data. You write the stored procedure on SQL1. The stored procedure accesses SQL2 byusing the OPENQUERY Transact-SQL statement. However, the query fails when executed. You need to troubleshoot the cause of the error. What should you do?

Join the two servers by using the four-part syntax of server.database.schema.table.

Reference SQL2 by using an alias.

Add SQL2 as a remote server to SQL1.

Add SQL2 as a linked server to SQL1.

By adding SQL2 as a linked server to SQL1, you will be able to access SQL2 from SQL1 and execute queries on it. This will allow you to overcome the permissions limitation on SQL2 and successfully execute the stored procedure. Using the OPENQUERY Transact-SQL statement alone is not enough to access SQL2, as it only allows for executing a pass-through query on a linked server.

Explanation

Submit

5. You are responsible for implementing maintenance jobs on a SQL Server 2005 database server. Certain jobs run every Sunday and other jobs run at the beginning of every month. You need to schedule the jobs in the way that uses the least amount of administrative effort. What should you do?

Create a job schedule that runs every Sunday. Assign weekly tasks to this schedule. Create a schedule that runs on... Create a job schedule that runs every Sunday. Assign weekly tasks to this schedule. Create a schedule that runs on the first day of every month. Assign monthly tasks to this schedule. second

Create a job for each task that runs once a day. Use a Transact-SQL statement to check the date and... Create a job for each task that runs once a day. Use a Transact-SQL statement to check the date and day of the week. If the day is either a Sunday or the first day of the month, execute the code.

Create a job schedule that runs once a day. Assign ... Create a job schedule that runs once a day. Assign jobs to this job schedule. If the day is either a Sunday or the first day of the month, execute the jobs.

Create a job for each task that runs once a week on Sunday. Add a second job schedule that runs... Create a job for each task that runs once a week on Sunday. Add a second job schedule that runs the job on the first of the month.

To minimize administrative effort, the best approach is to create a job schedule that runs every Sunday and assign weekly tasks to it. Additionally, create a separate schedule that runs on the first day of every month and assign monthly tasks to it. By doing this, the maintenance jobs will be automatically executed on the designated days without the need for manual intervention or checking the date and day of the week using Transact-SQL statements. This ensures efficiency and reduces the workload for the administrator.

Explanation

Submit

6. You work at the regional sales office. You are responsible for importing and exporting data in SQL Server 2005 databases. The main office asks you to send them a text file that contains updated contact information for the customers in your region. The database administrator in the main office asks that the data be sorted by the StateProvince, Surname, and FirstName columns. You need to satisfy these requirements by using the least amount of effort. What should you do?

You work at the regional sales office. You are responsible ... You work at the regional sales office. You are responsible for importing and exporting data in SQL Server 2005 databases. The main office asks you to send them a text file that contains updated contact information for the customers in your region. The database administrator in the main office asks that the data be sorted by the StateProvince, Surname, and FirstName columns. You need to satisfy these requirements by using the least amount of effort. What should you do?

Create a format file for the export operation.

Specify StateProvince, Surname, and FirstName in the ORDER BY clause in the bcp queryout command.

Copy the data into a new table that has a clustered index on StateProvince, Surname, and FirstName. Export the data.

To satisfy the requirements of sorting the data by StateProvince, Surname, and FirstName in the text file, the least effort option would be to specify these columns in the ORDER BY clause of the bcp queryout command. This command is used for exporting data in SQL Server databases. By including the desired columns in the ORDER BY clause, the data will be sorted accordingly before being exported to the text file. This eliminates the need to create a format file or copy the data into a new table with a clustered index.

Explanation

Submit

7. You work in Dublin at the main office of Companyxyz.com. You are responsible for managing a
SQL Server 2005 database. The sales department wants a report that compares customer activity in the previous quarter between the main office in Dublin and the branch office in Buenos Aires. They want the data sorted by surname and first name. You restore a recent backup of the Buenos Aires database onto your server. You write queries to build the report, ordering the data by the Surname and FirstName columns. You review the data and notice that the customer list from the Buenos Aires database is sorted differently. The sales department needs the revised data within 15 minutes for a presentation. You need to implement the fastest possible solution that ensures that the data from both databases is sorted identically. What should you do?

Use the Copy Database Wizard to copy the data in ... Use the Copy Database Wizard to copy the data in the Buenos Aires database to a new database with the same collation as the Dublin database.

Use the SQL Server Import and Export Wizard to copy ... Use the SQL Server Import and Export Wizard to copy the data from the Buenos Aires database into new tables, specifying the same collation as the Dublin database.

Modify the format file to specify the same collation as the Dublin database. Import the table again.

Modify the query on the Buenos Aires database to use the COLLATE setting in the ORDER BY clause. In the... Modify the query on the Buenos Aires database to use the COLLATE setting in the ORDER BY clause. In the query, specify the same collation as the Dublin database.

To ensure that the data from both databases is sorted identically, the best solution is to modify the query on the Buenos Aires database. By using the COLLATE setting in the ORDER BY clause and specifying the same collation as the Dublin database, the data will be sorted in the desired order. This solution is the fastest and does not require any additional steps such as copying or importing the data.

Explanation

Submit

8. A support engineer reports that inserting new sales transactions in a SQL Server 2005 database results in an error. You investigate the error. You discover that in one of the databases, a developer has accidentally deleted some data in a table that is critical for transaction processing. The database uses the full recovery model. You need to restore the table. You need to achieve this goal without affecting the availability of other data in the database. What should you do?

Back up the current transaction log. Restore the database with a different name and stop at the point just before... Back up the current transaction log. Restore the database with a different name and stop at the point just before the data loss. Copy the table back into the original database.

Back up the current transaction log. Restore the database to the point just before the data loss.

Restore the database from the existing backup files to a time just before the data loss.

Restore the database to the point of the last full backup.

To restore the table without affecting the availability of other data in the database, the support engineer should first back up the current transaction log. Then, they should restore the database with a different name and stop at the point just before the data loss. Finally, they can copy the table back into the original database. This process ensures that the table is restored while preserving the other data in the database and minimizing any potential downtime.

Explanation

Submit

9. You are creating a Web-based application to manage data aggregation for reports. The
application connects to a SQL Server 2005 database named DataManager. One page in the application has controls that execute stored procedures in a database named Reporting Database. There is an existing Service Broker connection between the Data Manager database and ReportingDatabase. You want to add two new message types to the existing service. In each database, you create message types named ProcessReport and SendResult. You need to add the two new message types to the existing service. What should you do first?

Create a queue on each ... Create a queue on each database with the ACTIVATION argument set to DataManager.dbo.ProcessReport.

Create a conversation between the databases by using the following ... Create a conversation between the databases by using the following statement.BEGIN DIALOG FROM SERVICE 'ProcessReport' TO SERVICE 'SendResult'

Create a contract between the services by ... Create a contract between the services by using the following statement.CREATE CONTRACT ProcessData (ProcessReport SENT BY INITIATOR, SendResult SENT BY TARGET)

Create services for each database by using the following statement.CREATE ... Create services for each database by using the following statement.CREATE SERVICE DataManager ON QUEUE ProcessReport

To add the two new message types to the existing service, the first step is to create a contract between the services. This can be done by using the following statement: CREATE CONTRACT ProcessData (ProcessReport SENT BY INITIATOR, SendResult SENT BY TARGET). This contract defines the message types and specifies which party (INITIATOR or TARGET) will send each type of message. Once the contract is created, the message types can be added to the existing service.

Explanation

Submit

10. You are preparing for a new installation of SQL Server 2005. You need to select the protocols
that client computers might use to connect to the server. Which two protocols can you use to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

Named Pipes

TCP/IP

Shared Memory

Virtual Interface Adapter (VIA)

Multiprotocol

Named Pipes and TCP/IP are two protocols that can be used for client computers to connect to the server in a new installation of SQL Server 2005. Named Pipes is a protocol that allows communication between processes on the same computer or between computers across a network. TCP/IP is a protocol that enables communication over the internet or a local network. These two protocols provide different options for client computers to establish a connection with the server.

Explanation

Submit

11. Companyxyz.com has multiple servers in a distributed environment. You work with two SQL Server 2005 computers named SQL1 and SQL2. Each server uses SQL Server Authentication and they use different logins. You need to write a distributed query that joins the data on SQL1 with the data on SQL2. What should you do?

Ensure that both SQL1 and SQL2 use the same login name as the security context for each server.

Configure SQL2 as a remote server. Write the query on SQL1.

Configure SQL2 as a linked server to impersonate the remote login.

Configure SQL2 as a distributed server. Use pass-through authentication.

not-available-via-ai

Explanation

not-available-via-ai

Submit

12. You discover that the msdb database on a SQL Server 2005 computer is corrupt and must be
restored. Databases are backed up daily. The database backup files are written to a network share, but the file names do not clearly indicate which databases are in each file. You need to locate the correct backup file as quickly as possible. The first file in the list is named DB_Backup.bak. Which Transact-SQL statement should you use?

RESTORE LABELONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak

RESTORE HEADERONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak

RESTORE VERIFYONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak

RESTORE DATABASE MSDBFROM DISK = N\\Server1\Backup\DB_Backup.bak

The correct answer is "RESTORE HEADERONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak". This statement is used to retrieve the backup header information from the specified backup file. By using this statement, you can quickly determine if the backup file contains the database you are looking for without actually restoring the database.

Explanation

Submit

13. You discover that the msdb database on a SQL Server 2005 computer is corrupt and must be
restored. Databases are backed up daily. The database backup files are written to a network share, but the file names do not clearly indicate which databases are in each file. You need to locate the correct backup file as quickly as possible. The first file in the list is named DB_Backup.bak. Which Transact-SQL statement should you use?

RESTORE LABELONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak

RESTORE HEADERONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak

RESTORE VERIFYONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak

RESTORE DATABASE MSDBFROM DISK = N\\Server1\Backup\DB_Backup.bak

The correct answer is RESTORE HEADERONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak. This statement will retrieve the header information from the backup file, allowing you to determine which database the backup file belongs to. This will help you quickly locate the correct backup file for the msdb database.

Explanation

Submit

14. You are creating an HTTP endpoint that will be used to provide customer data to external applications. Your SQL Server 2005 computer is named SQL1. You create a stored procedure named dbo.usp_GetPersonData to retrieve the data in the AdventureWorks database. You create the endpoint by using the following code.
CREATE ENDPOINT SQLEP_AWPersons AS HTTP (PATH = '/AWpersons', AUTHENTICATION
=
(INTEGRATED), PORTS = (CLEAR), SITE = 'SQL1') FOR SOAP (WEBMETHOD 'PersonData'
(NAME='AdventureWorks.dbo.usp_GetPersonData'), BATCHES = DISABLED, WSDL =
DEFAULT,
DATABASE = 'AdventureWorks', NAMESPACE = 'https://Adventure-Works/Persons') The first users to connect to the endpoint tell you that they do not get any data. You connect to the endpoint and discover that it is not responding. You need to modify the endpoint so that data is returned as expected. What should you do?

Change the AUTHENTICATION property to KERBEROS.

Specify BATCHES = ENABLED.

Specify STATE = Started.

Specify WSDL = 'pr_GetPersonData'.

not-available-via-ai

Explanation

not-available-via-ai

Submit

15. Companyxyz.com has two SQL Server 2005 computers named SQL1 and SQL2. Both servers
take part in replication. SQL1 is both the Publisher and its own Distributor of a publication named Pub1. Pub1 is the only publication on SQL1, and SQL2 is the only Subscriber. Your supervisor requests a status report about the replication latencies. Using Replication Monitor on SQL1, you need to find out the current latencies between the Publisher and Distributor as well as between the Distributor and Subscriber. What should you do?

Select the Subscription Watch List tab for SQL1. View the ... Select the Subscription Watch List tab for SQL1. View the Latency column for the SQL2 subscription.

Select the All Subscriptions tab for the Pub1 publication. View ... Select the All Subscriptions tab for the Pub1 publication. View the Latency column for the SQL2 subscription.

Select the Tracer Tokens tab for the Pub1 publication. Select the Insert Tracer option and wait for the requested latency... Select the Tracer Tokens tab for the Pub1 publication. Select the Insert Tracer option and wait for the requested latency values for the SQL2 subscription to appear.

Select the Subscription Watch List tab for SQL1. Double-click the SQL2 subscription. View the duration details on ... Select the Subscription Watch List tab for SQL1. Double-click the SQL2 subscription. View the duration details on the Publisher to Distributor History tab as well as on the Distributor to Subscriber History tab.

To find out the current latencies between the Publisher and Distributor as well as between the Distributor and Subscriber, you should select the Tracer Tokens tab for the Pub1 publication. Then, select the Insert Tracer option and wait for the requested latency values for the SQL2 subscription to appear. This will provide you with the necessary information about the replication latencies.

Explanation

Submit

16. Companyxyz.com uses SQL Server 2005. Users report that report execution is slow. You investigate and discover that some queries do not use optimal execution plans. You also notice that some optimizer statistics are missing and others are out of date. You need to correct the problem so that reports execute more quickly. Which two Transact-SQL statements should you use? (Each correct answer presents part of the solution. Choose two.)

DBCC CHECKTABLE

ALTER INDEX REORGANIZE

UPDATE STATISTICS

CREATE STATISTICS

DBCC SHOW_STATISTICS

In order to optimize the execution plans and improve the performance of report execution, you should use the UPDATE STATISTICS statement to update the optimizer statistics. This will provide the query optimizer with up-to-date information about the distribution of data in the tables, allowing it to generate more efficient execution plans. Additionally, you should use the CREATE STATISTICS statement to create additional statistics if necessary. This can help the optimizer make better decisions when generating execution plans.

Explanation

Submit

17. Companyxyz.com has two SQL Server 2005 computers named SQL1 and SQL2. Transaction log shipping occurs from SQL1 to SQL2 by using default SQL Server Agent schedule settings. You need to reconfigure transaction log shipping to provide minimum latency on SQL2. What should you do?

On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, maintain default schedule settings... On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, maintain default schedule settings for both the log shipping copy and the restore jobs.

On SQL1, change the schedule type for the transaction log backup to Start automatically when SQL Server Agent starts. On... On SQL1, change the schedule type for the transaction log backup to Start automatically when SQL Server Agent starts. On SQL2, change the schedule types for both the log shipping copy and the restore jobs to Start automatically when SQL Server Agent starts.

On SQL1, maintain default schedule settings for the transaction log ... On SQL1, maintain default schedule settings for the transaction log backup job. On SQL2, change the schedule types for both the log shipping copy and the restore jobs to Start automatically when SQL Server Agent starts.

On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, reschedule both the log... On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, reschedule both the log shipping copy and the restore jobs so that they occur every minute.

To minimize latency on SQL2 in transaction log shipping, the transaction log backup job on SQL1 should be rescheduled to occur every minute. Additionally, both the log shipping copy and the restore jobs on SQL2 should be rescheduled to occur every minute. This will ensure that the transaction log backups, copies, and restores are performed frequently and in a timely manner, reducing the latency between the two servers.

Explanation

Submit

18. Companyxyz.com has two SQL Server 2005 computers named SQL1 and SQL2. A database named DB1 is located on SQL1. DB1 contains a table named Companyxyz4. Companyxyz4 is replicated to a database named DB1Repl, which is located on SQL2. Full-Text Search is not being used. Users report that the queries they run against Companyxyz4 in DB1Repl are very slow. You investigate and discover that only the clustered index of Companyxyz4 is replicated. All other indexes in DB1Repl are missing. You examine the Companyxyz4 article properties. The current Companyxyz4 article properties are shown in the exhibit. You need to change the article properties so that all indexes of Companyxyz4 in DB1 are replicated when the subscription is
reinitialized. Which two article properties should you change? (Each correct answer presents part of the solution. Choose two.

Copy clustered index

Copy XML indexes

Copy nonclustered indexes

Copy unique key constraints

Copy index partitioning schemes

The correct answer is to change the article properties to "Copy XML indexes" and "Copy nonclustered indexes". This is because the users are reporting slow queries against the replicated database, and it is discovered that only the clustered index is being replicated while all other indexes are missing. By changing the article properties to include copying XML indexes and nonclustered indexes, all indexes of Companyxyz4 in DB1 will be replicated when the subscription is reinitialized, which should improve the performance of the queries.

Explanation

Submit

19. You are implementing transaction log shipping for a database named DB1 from a server named
SQL1 to a server named SQL2. Because DB1 is 100 GB in size, it is too big to transfer over the network in a reasonable amount of time. You need to minimize the impact on the network while you initialize the secondary database. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

Specify the simple recovery model for DB1.

Specify either the full or the bulk-logged recovery model for DB1.

Perform a complete backup of DB1 to portable media. Restore the secondary database from that backup; specify the RECOVERY option.

Perform a complete backup of DB1 to portable media. Restore the secondary database from that backup; specify the STANDBY option.

Before you activate transaction log shipping to the secondary database, execute the following statement on the primary server.BACKUP LOG DB1... Before you activate transaction log shipping to the secondary database, execute the following statement on the primary server.BACKUP LOG DB1 WITH TRUNCATE_ONLY

To minimize the impact on the network while initializing the secondary database, the first action is to specify either the full or the bulk-logged recovery model for DB1. This ensures that only the necessary transaction log information is transferred over the network. The second action is to perform a complete backup of DB1 to portable media and then restore the secondary database from that backup, specifying the STANDBY option. This allows the secondary database to be in a read-only state, reducing the need for continuous data transfer over the network.

Explanation

Submit

View My Results

Quiz Review Timeline (Updated): Aug 23, 2024 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.