1.

Statement A: The transaction log tracks all transactions of a database.

Statement B: The transaction log displays one copy of each row affected by a SQL statement.

Which of the following options is correct about the above statements?

Tra loi:

Statement A is true and Statement B is false.

2.

You need to create a table to store the details of customers. You should be able to store the customer id, the name, the address, and the password of the customer. The id of the customer should be unique. While storing the details of the customers, the customer id, the name, and the password must be stored without fail. However, if the address is not known, it need not be stored. Which of the following statements should you use to create the Customer table?

Tra loi:

CREATE TABLE Customer ( cCustomerId char(6) constraint pkCustomerId primary key, cName char(40) not null, cAddress char(30) null, cPassword char(10) not null )

3.

Which of the following database objects can be used to print a report in the following format: Publisher Id: XXXX Publisher Name: XYZ State:XXXXX City:XXXXXX

Tra loi

Cursor

4.

Predict the output of the following SQL statement if the date of sale for the given product is July 23, 2001 and the order date is July 1, 2001.

Select datediff(dd, sale_dt, order_dt) from transaction where prod_id = '10202'

Tra loi:

-22

5.

Consider the following statements:

Statement 1: Constraints are created only at the table level.

Statement 2: Rules enable you to validate values in the column, and they can be bound to columns from multiple tables.

Which of the following is correct about the above statements?

Tra loi:

Both statements are true.

6.

Identify the replication model in which one server is defined as the publisher that replicates data to multiple servers known as the subscribers. The publisher and the distributor are on the same server and there is only one source of data that is published on the destination servers.

Tra loi:

The central publisher model

7.

Identify the type of user defined function. CREATE FUNCTION product_code (@city varchar(30)) RETURNS varchar (30) AS BEGIN DECLARE @product_code varchar (30) SELECT @product_code=case @pr_desc When 'doll' then 'P0001' When 'cycle' then 'P0002' When 'swing' then 'P0003' When 'puzzle' then 'P0004' Else 'unknown' End Return @product_code END

Tra loi:

Scalar functions

8.

Employee details are maintained in the Employee table. The company has a scheme which allows employees to own a car. When any employee avails this scheme, the details related to the scheme are recorded in the CarScheme table. However, the scheme can be availed by only those employees who have completed one year of service in the company. The date of joining is stored in the Employee table in the attribute called dDateOfJoining and the date on which they avail the scheme is maintained in the dDateOfScheme attribute of the CarScheme table. How will you ensure that the car scheme is being availed by only those employees who have completed one year of service in the company?

Tra loi:

By creating an insert trigger on the CarScheme table

9.

Identify the INCORRECT statement about DTS package?

Tra loi:

DTS Tasks are functions that are executed in multiple steps in a DTS package.

10.

In which level of the database architecture is the physical storage of data described?

Tra loi:

Internal level

11.

Identify the command that you will execute to assign sysadmin fixed server role to Jim.

Tra loi

EXEC sp_addsrvrolemember 'Jim', 'sysadmin'

12.

Which data integrity constraint requires that a column contain a non-null value?

Tra loi:

Required data

13.

Statement A: A non-validating parser checks whether an XML document is well-formed. Statement B: A validating parser checks whether an XML document conforms to the structure defined in the schema. However, it does not check whether an XML document is well-formed.

Tra loi

Statement A is true and statement B is false.

14.

The orders placed by various customers at an online gift store are stored in the following Orders table: CREATE TABLE Orders ( iOrderCode int constraint pkOrders primary key, iGiftCode int not null, iCustomerCode int not null, iQuantityOrdered int not null, mTotalAmount money null, mDiscount money null ) The discount is calculated, for each order, based on the total amount of the order. If the total amount of the order is less than $100, the discount is 1% of the total amount. If the total amount of the order is $100 or more, the discount is 5% of the total amount. Which of the following statement can you use to calculate the discount for each order?

Tra loi:

UPDATE Orders SET mDiscount= CASE WHEN mTotalAmountÄê THEN 0.01*mTotalAmount WHEN mTotalAmount >=100 THEN 0.05*mTotalAmount END

15.

Which of the following character should you use to substitute spaces in an URL query?

Tra loi +

16.

The details of students studying in a university are stored in the following Student table. CREATE TABLE Student ( cStudentCode char(10) not null, cStudentName char(30) not null, cStudentAddress char(50) not null, cStudentState char(50) not null, cStudentPhone char(15) not null, dStudentBirthDate datetime not null ) A procedure to display all the details of students belonging to a particular state was created as follows: CREATE PROCEDURE prcDisplayStudent @State char(50) AS BEGIN SELECT * FROM Student WHERE cStudentState = @State END The indexes on the Student table are modified very often. You need to modify the above procedure so that its performance can improve. Which of the following command should you use to modify the above procedure?

Tra loi:

Use the ALTER PROCEDURE along with the WITH RECOMPILE option

17.

To store information about various flights in an online air-reservation system, the following Flight table is being used: CREATE TABLE Flight ( cFlightNumber char(6) not null, cAircraftDescription char(30) not null, cFlightSource char(30) not null, cFlightDestination char(30) not null, dFlightTime datetime not null, mFlightBusinessRate money not null, mFlightFirstclassRate money not null ) The table is used to store information about various flights. The table is rarely updated. Around 2 new flights are added to the Flight table every year. The table is used heavily for queries. In addition, no two flights can have the same flight number. What type of index would you create to improve the performance of the queries?

Tra loi:

Create a unique clustered index on the cFlightNumber attribute of the Flight table.

18.

A music store maintains the details of various audio-CDs being sold at the store in the following MusicSales table CREATE TABLE MusicSales ( cMusicCode char(6) not null, cMusicTitle char(30) not null, cArtistName char(30) not null, iMusicQuantityOnHand int not null, mMusicPrice money ) Currently the average price of the music CDs is $70. You have to decrease the price of all the CDs being sold by $1 at the music store till the average price reaches a price less than $50. Which of the following batches will you use to decrease the price of all the CDs?

Tra loi:

WHILE (SELECT AVG(mMusicPrice) FROM MusicSales )j BEGIN UPDATE MusicSales SET mMusicPrice=mMusicPrice - 1 END

19.

Consider the following trigger: Create trigger st_update on student FOR UPDATE AS IF UPDATE (st_Id) BEGIN PRINT 'student ID cannot be modified' ROLLBACK TRAN END Predict the output if the following command is given. UPDATE students SET st_id = 's0001' WHERE st_id ='0001'

Tra loi:

None of the rows will be updated.

20.

What do you call a component that is a collection of information about the data or database objects that have to be replicated?

Tra loi:

Article

21.

A transportat company has a table Customer to keep information about their customers. The table to maintain customer information was created with the following statement: CREATE TABLE Customer ( cCustomerCode char(5) not null, cCustomerName char(20) not null, cCustomerAddress char(50) null, cPhone char(7) null, cEmail char(25) null ) Now, you need to generate a report to list all those customers who prefer to be contacted through email and hence have not given their phone numbers as a means to contact them. Which of the following statements is the correct solution for the required report?

Tra loi:

SELECT cCustomerName FROM Customer WHERE cPhone IS NULL AND cEmail IS NOT NULL

22.

Statement A: A subquery is given in parentheses.

Statement B: You can nest subqueries to any depth.

Which of the following options is true about the above statements?

Tra loi:

Both, Statement A and Statement B, are true.

23.

Identify the function that calculates the number of date parts between two dates.

Tra loi:

DATEDIFF()

24.

A university offers many disciplines. Each discipline has many courses assigned to it. An instructor can take courses belonging to only one discipline. However, many instructors can take more than one course for that discipline. Click on the Exhibit button to view the ER diagram depicting part of the logical model of the database. Which of the following statements is true for the ER diagram?

Tra loi:

The relationship between the entities is one-to-many.

25.

New Book Store is a leading bookseller of New York. The store maintains a central database for managing the transactions. There are two operators, A and B, to record all transactions in the database. A new bestseller has just arrived in the market and New Book Store has a stock of 100 units of the book. Operator A receives an order of 50 units of the book from a public library and updates the database. Which of the following actions by DBMS will enable operator A to ensure that whenever the next transaction is made with the database, it does not cause an error?

Tra loi:

COMMIT the transaction.

26.

Identify the INCORRECT statement about indexes.

Tra loi:

Indexes are not updated each time the data is modified.

27.

Mary wants to create a table, orders. The attributes to be stored in this table are part number, supplier number, order description, and quantity. Part number and supplier number form the composite primary key. Which of the following statements should Mary use to create the table?

Tra loi:

CREATE TABLE orders (part-no CHAR (5) NOT NULL, supplier-no CHAR(5) NOT NULL, order-desc CHAR (10), qty DECIMAL (8,2), PRIMARY KEY (part-no, supplier-no))

*

Which type of default index is created when a primary key is defined?

Tra loi:

Unique clustered

28.

Which type of join should be created to display the names of books that have the same price?

Tra loi:

Self Join

29.

In a university, students take at least 76 tests for different courses that they do during their graduation in a discipline. The details of students and their test scores are stored in two different tables. The details of the students are stored in the Student table, and their test scores are stored in the StudentTest table. The two tables are created are created as follows: CREATE TABLE Student ( cStudentId char(6) not null, cStudentName char(20) not null, vAddress varchar(40) not null, cCity char(10) not null, cState char(20) not null, cDisciplineCode char(6) not null ) CREATE TABLE StudentTest ( cStudentId char(6) not null, cCourseCode char(6) not null iTestNo int not null, iTestScore int not null, dTestDate datetime not null ) To calculate the aggregate percentage for all tests taken by a student at any point in time, the StudentTest table is queried upon. However, this is taking longer time than expected. To improve the performance of the query, which of the following actions should you take

Tra loi:

Add a new column in the Student table, which will store the aggregate percentage value of all tests taken by a student. Then query this table each time the aggregate percentage value is required.

30.

What type of integrity is applied by defining a primary key in a given table?

Tra loi:

Entity

31.

To store the details of various projects being under taken in an organization, the following Project table has been created. CREATE TABLE Project ( cProjectCode char(3) not null, cProjectName char(30) not null, cProjectDescription char(30) not null, dProjectStartDate datetime not null, dProjectEndDate datetime not null, mProjectCost money not null ) You have created a rule rulMaxCost to limit the maximum cost of any project to $50,000 as follows: CREATE RULE rulMaxCost as @Cost? When you insert rows into the Project table, you are able to insert values more than $50,000 in the mProjectCost attribute. What could be the possible reason due to which you are able to insert values more than $50,000?

Tra loi:

The rule has not been bound to the mProjectCost attribute using the sp_bindrule command.

32.

Consider the following statements:

Statement 1: A lost update problem occurs when two or more transactions try to modify the same row that is based on the originally selected value.

Statement 2: An inconsistent analysis problem occurs when a user views a document before and while the document is being updated.

Which of the following is correct about the above statements?

Tra loi:

Both statements are true.

33.

Identify the SQL Server lock mode that is used to establish a lock hierarchy.

Tra loi: Intent (I)

34.

You have collected data about various students in the colleges of South America who will be completing their graduation in the next 2 months and would like to start working full time. You have collected data about students of each college in separate files. You want to now, collate these files into a database called employment. You want to store the details in a table named college. Identify the SQL statement that you will use to copy details of the file college1 to the database.

Tra loi:

BCP employment..College IN College1.txt -SHRServer -Usa -Ppassword

*

True Travel Services is an online travel agency. The agency uses a SQL 2000 database to store data. The travel agency has appointed an analyst to review the performance and guide business growth path. The analyst wants to use various applications that take input in form of plain text file. However, during BCP you want to allow maximum 15 errors. Which option will you use in the given scenario with the BCP statement?

Tra loi:

-m m

*

Identify the correct order of steps to be performed while using a cursor in SQL Server. Step 1: Closing the cursor. Step 2: Opening the cursor. Step 3: Fetching the row. Step 4: Releasing the cursor. Step 5: Declaring the cursor

Tra loi:

Step 5, Step 2, Step 3, Step 1, and Step 4

*

How many unique clustered indexes can be created on a single table?

Tra loi:

1

35.

36.

You are the system administrator of the ABC Books. You have created a SQL 2000 database that will store the details of books published by ABC Books. You want to grant permissions to the users of the database to work with the data stored in the database. Identify the object permission that you will grant to allows users to execute the stored procedure on which the permission is defined.

Tra loi:

EXECUTE

37.

Consider the following statements:

Statement 1: A relationship is an association among entities.

Statement 2: A relation is depicted as a rectangle in the entity-relationship diagram. Which of the following is correct about the above statements?

Tra loi:

Statement 1 is true and statement 2 is false

38.

A Soap company sells various soaps. Each soap is treated as a product and is given a unique product code. No two soaps have the same name. However, the price and the weight of some products is the same. A table called Product is proposed to store the details of various soaps. The proposed structure of the Product table is as follows: CREATE TABLE Product ( cProductCode char(6) not null, cProductName char(20) not null, mPrice money not null, iWeight int not null ) Which of the following statements is correct?

Tra loi:

The cProductCode attribute is a candidate key.

*

A Soap company sells various soaps. Each soap is treated as a product and is given a unique product code. No two soaps have the same name. However, the price and weight of some products is the same. A table called Product is proposed to store the details of various soaps. The proposed structure of the Product table is as follows: CREATE TABLE Product ( cProductCode char(6) not null, cProductName char(20) not null, mPrice money not null, iWeight int not null ) Which of the following attributes of the Product table are candidate keys?

Tra loi:

The cProductName and cProductCode attributes.

39.

Identify the query that will display the titles of all those books for which the advance amount is more than the average advance paid for the business-related books

Tra loi:

Select title from titles where advance > (Select avg(advance) from titles where type = 'business')

40.

Resource Solutions is HR consultancy firm. Jim and Nancy work with Resource Solutions. Resource Solutions conducted a test for short-listing candidates for the post of Finance manager. Jim is updating the database with the marks scored by a student with the test score and the test date after a candidate with cCandidateCode '000002' has taken the test. He executes the following transaction. BEGIN TRANSACTION UPDATE ExternalCandidate SET siTestScore = 90 WHERE cCandidateCode='000002' UPDATE ExternalCandidate SET dTestDate = getdate() WHERE cCandidateCode = '000002' While the above transaction is being executed, Nancy wants to schedule an interview for candidates, but is unable to view the details of candidates whoø$

¦

O

W

E

I

n ˆ ²Î/@ô) i µ *Ž¹%9g tv

ÎÐÔæ#-"Z"*%Š%Œ% & 'B'Ï'×'8*‡*=+j+É+Ô+l- -ç/ÿ/<0t0›162‡2˜23

3

3

7Ñ7,838;Y;ß<ý<K=_=ïÚïÚïÚïÚïÚïÚïÚïÚïÚïÚïÚïÚïÚïÚÉÚï»ÚïÚïÚïÚÉÚïÚïÚïÚïÚïÚïÚïÚïÚïÚïÚïÚ»ïÚïÚïÚïÚïÚhC!CJOJQJ^JaJ!h¡q¾hC!B*CJ^JaJphÿ)h¡q¾hC!B*CJOJQJ^JaJphÿ h4!ƒhC!CJOJQJ^JaJJL©ïø& ) ÷

¦

©

G

O

W

Z

ì

<

E

I

L

p

¯

' e n ˆ ‹ ©úúúúññúúññúúññúúúññúúúúúññúú„Ð^„ÐgdC!gdC!zeý©²ÎÑ&/@Cëô) , ' i · » *.†Ž¹½ %)1ööññööññööññööññööññööññööññgdC!„Ð^„ÐgdC!19gkþ

ÀÔÜÝæ#-'-ý!"Z"^"%*%& &' 'B'F'Æ'Ï'ööññññññöññööññööññööññööññögdC!„Ð^„ÐgdC!Ï'×'Û'/*8*‡*‹*¼*ñ*4+=+j+n+À+É+Ô+Ø+c-l-¢-¦-Þ/ç/ÿ/030<0t0x0öññööññññööññööññööññööññööñgdC!„Ð^„ÐgdC!x0'1›16282~2‡2˜2œ2ù23

337

7Ñ7Õ7#8,83878÷:;Y;];;<˜<Ö<úññúúññúúññúúññúúññúúññúúúúú„Ð^„ÐgdC!gdC!Ö<ß<ù<ý<K=_=c=H?Q?"?-?:ACAHAJA/B8BcBeB©B²B'B¸B¼BADJDRDVDzDööññöññööññööññööññööñññööññgdC!„Ð^„ÐgdC!_=Q?"?CAHA8BcB²B'BJDRDWE„E"GÄGèI JÈJ+KNdQeyezeïÚïÚïÚïÚïÚïÚïÚïÚïÚïØïÚÔhC!U)h¡q¾hC!B*CJOJQJ^JaJphÿ h4!ƒhC!CJOJQJ^JaJzD¹DNEWE„EˆEŠG"GÂGÄGßIèIJJ¿JÈJ+K/KHeQeyezeúúññúúññúúññúúññúúññï„Ð^„ÐgdC!gdC! have scored more than 80 marks. Nancy uses the following statements to view the details and schedule an interview: BEGIN TRANSACTION SELECT * from ExternalCandidate WHERE siTestScore > 80 UPDATE ExternalCandidate SET dInterviewDate = getdate()+ 2 WHERE siTestScore > 80 Identify why Nancy is unable to execute the transaction.

Tra loi:

SQL Server uses the concept of locking.