1. When does a trigger fire?
*a. Automatically after the table modification.
b. Before the table modification.
2. What is true about defaults?
*a. Gets deleted automatically when a table is dropped.
(If default is a constraint)
*b. Doesn't get deleted when a table is dropped
3. How to distinguish table in a self join?
*a. Alias
b. defaults
c. columns
d. table
4. To get report of all the publishers names who's published titles average price is greater than $10.
SELECT name, Avg(price)
From pubs
Where avg(price) > = $10
Group by name
a. Outstanding solution
b. Adequate solution
c. Will work but not a solution
d. Will not work but appears to work
*e. Will not provide a solution and will not work
5. What is true about CREATE RULE statement ?
a. The variables used needs to be declared using DECLARE statement
*b. rules bound to a column cannot be dropped.
*c. rules bound to a column overrides the rule bound to the datatype that the column uses.
6. You are analysing the tables that store data on customers. Some rows of customer require 3 columns of address and some rows require two columns . The columns of address data are all varchar(50) dataype . How to use T-SQL to create a table that permits more characteristics.
*a. Create a column as Null
b. Create a column that has a minimum length of zero.
c. Create a column that has a default value of zero.
d. allow variable no. of columns for different rows.
7. Triggers are created for data integrity and operate at
*a. table level
b.column level.
c. row level
8. What happens to the data in a table when a clustered index is created?
*a. The data is sorted and becomes a part of the index at the leaf page.
b. The data is sorted and put on a device separate from the index.
c. The data is scanned and pointed to each data page that are created at the leaf page.
9. How can u access the data being modified during the execution of a trigger ?
*a. Access the rows already modified in the deleted table to see the version of the rows before modification and the inserted table to see the version of the rows after modification.
10. How do you maintain data integrity on the subscription server in a replication scenario that has multiple publisher and a Single publisher?
a. Use subscription option that allows you to update anyone.
*b. Use a location specific composite primary key.
11. You are working with an application that contains customer table and sales items table, most of the traffic in sales items involves searching customer records and there are 100 to 10000 rows per record . Although a high number of rows are often in
a. Clustered index with high fillfactor.
b. Clustered index with low fillfactor.
c. Non clustered index with high fill factor.
*d. Non clustered index with low fill factor.
12. Which statements is true about binding and unbinding defaults.
*a. Default can be bound to future users to a user defined data type and current users of data type can be ignored.
b. A default cannot be bound to a column if another default exists on the column .
13. A composite index of 200 bytes width is created. The first logical column in the index has very few unique data, whereas the second column has many. How can u change the index so that the performances increases ?
*a. Make the second column as the first logical column and decrease the width of the index.
14. A user createa temporary table #tmpl. If he wants the table to be accessed by everyone (Global) he should
a. Grant permission to all users.
b. Not possible at all.
*c. drop the table and create a ##tmpl
15. How do u ensure business rules take corrective action?
*a. Trigger
16. A) Constraint will be executed before data is modified
B) Triggers get executed after data is modified.
*a. Both are true
17. Where u will find entries for a table in user defined database?
*a. In sysobjects of user db.
18. Where will be temporary info is stored in server when we issue an sql command using distinct clause
a. is stored in database itself.
*b. is stored in tempdb.
19. How would you see all the records 30 days before today's date.
*a. dateadd(dd, -30, getdate())
b. datediff(dd,30,getdate())
c. dateadd(dd,getdate(), -30)
d. Not possible
20. Triggers are best used for
a. Modification of row (Likely to be the answer)
*b. Modification of table
c. Modification of column
(Note : For modification in every record , the trigger fires/ during BCP triggers are ignored)
21. The titles has l_name and f_name . Write the select statement to display the two in column.
*a. select l_name + f_name
22. To make better use of Alert and task objects
*a. Use properties of SQL executive manager.
23. Using FETCH NEXT statement you are retrieving a row .Now delete the row returned by fetch statement.
*a. DELETE table_name
WHERE current of cursor_name
24. Where are systems procedures stored ?
*a. Master Database.
b. Model Database
c. Temp Database
d. MSDB
25. There is organisation, which deals with bank transaction. All account heads are stored in ACCT-HEAD, while all transactions are stored in DAILY-TRAN. There is heavy traffic involved in sales. Since it involves an online updation the ACCT_HEAD is r
*a. unique clustered index on ACCT-HEAD.
26. Date and leaf pages are one and same in case of
*a. Clustered
27. There is file Reservation which involved in on-line reservation, cancellation and booking . There is already an non-clustered index existing. Due to heavy transaction it is taking a very long time to insert the data. What is the best way to solve
*a. Drop the index , create it again with low fill factor
28. The 2 ways to force an index reference to the option is
*a. (index = ind_number) in from clause
b. (index = ind_name) in from clause
29. The optimizer hint can be specified in
a. Select
b. Where
*c. From
d. Order by
30. A table has two indexes . The no. of pages utilized is
*a. 48k (24 pages)
31. In order to find count of all unique cities from the author table the sql statement which is given is
*a. SELECT COUNT(DISTINCT city)
32. ------------ page is unaffected by fillfactor.
*a. Data
33. The user wants to find out all those, which had some sales. The sql command given is:
select title_id from titles
where title_id= (select title_id from sales)
The above sql is
a. Outstanding
b. Adequate
c. Seems to be ok will not work.
*d. It is wrong and will not work.
34. System error messages are stored in
*a. Master db
35. Every database
*a. is a copy of model
b. has user defined data types
*c. default size of 1 mb
d. user information
36. The default size of tempdb is
*a. 2 mb
37. The sql executive service schedule is supported by
a. Master
b. Model
c. Tempdb
*d. MSDB
38. Syshistory is used to
a) Find out the history of sql server
*b) Find about the alerts and tasks executed
39. Devices are referred to in sql statemets using the
a. Physical name
*b. Logical name
c. DEV_ID
40. When we require to create a device the command to use is
*a) Default device
41. If the user does not specify the name of the device while creating database
a.The master device is used.
*b)The device from default device pool is used
42. Removing master device as default is
*a) Outstanding solution
b)Adequate solution
43. While dropping a device I want to remove a physical file from the disk, the command
sp_dropdevice DELFILE is an
*a)Outstanding soln..
b)adequate soln..
44. Modifying database is done using
*a. Alter database
45. To reduce the size of a database you need to be
*a) System admin.
*b)give SHRINKDB command
c)resize if it less than model
*d) give the final size
46. What shall be included to take advantage of OLE
*a) Properties and methods of SQL DMO
b) Extended strored proc calls
c) n/w libraries
d) call level API
47. How shall a column be referenced in INSERT
*a) col that allows NULL or has default needn't be referenced
*b)default values clause in insert , inserts the values of a default bound to a column or the statement inserts NULL if the columns allows null and there is no default.
48. How are user defined stored proc created and stored as databas objects .
*a) Written as rows to system tables in the user defined db
*b) allocated space in extents
49. Where are the modified data stored when a update trigger is executed
*a) deleted, inserted
b) delete, insert
c) table, insert
d) delete, table
50. Where all Defaults can be used?
A)Insert table Default values
b)Update Table
set column_name = DEFAULT
51. Sales db uses a device DEV1 and the transaction log is using LOGDEV2. The Log device is full. Disk resize has already been given on the device LOGDEV2 and to increase the size of the tran log, the command given is
ALTER DATABASE Sales
on LOGDEV2 = 25
*a) Oustanding solution
52. Sales db uses a device DEV1 and the transaction log is using LOGDEV2. The Log device is full. To increase the size of the tran log , the command given is DISK INIT and the ALTER DATABASE .
*a)Will not work
53. DMO object is used for?
a) 32 bit OLE Automation programs registered in the Windows NT registry.
*b) Data Administering of sql Oblects MS_Access, Excel and VB
54. Which one is faster, TRUNCATE or DELETE
*a) Truncate ( as the Transaction is not logged )
Truncate is done Pagewise not Rowwise
55. Exec can be used for executing procedures and also for strings stored in a variable
56. Mail feature of SQL Server is implemented by using
*a) Extended stored procedure
b) NT Mail
57. What is ODBC?
*a) Client side API used by windows application.
58. How does the publisher maintain the data integrity.
*a) By maintaining the data as Read only on all the subscribers.
*b) By sending articles
59.How will you create your own error messages with a column name?
a) Create your own stored procedure
*b) RAISEERROR should be used
*c) sp_addmessage
60.There is a table called cust_order existing. At the end of evry month the sum of order qty will be added in to a table called cust_summary. So what is the relationship between cust_order and cust_summary.
a) one-to-many
b) one-to-one
c) many-to-many
*d)many-to-one
61. What is HoldLock?
*a. Instructs the SQL server to hold the lock until completion of a transaction
62. How does Foreign key used to maintain referential integrity
*a) It can be related to primary key of another table
63.The device info is stored in
a)User defined database
b)Model
*c)Master
d)tempdb
64. Select * into #tl from cust. Where will #tl be stored
*a) Tempdb
65. To increase the size of transaction log, the following statement is used
ALTER SALES ON LOG_DEV =25
*a) Outstanding soln.. it will work
66. A table has two indexes, how much memory is required
*a) 48 k
67. CREATE TABLE is used
*a) For creation of tables and constraints
68. How will you delete a column?
*a) Column cannot be deleted thro' delete command - only rows
69. How do you prevent a log from filling up?
*a) Set checkpoint = TRUE
*b)dump tran with no_log
*c)dump tran with truncate_only
70. If a table is dropped, which is getting dropped automatically?
*a) Index
*b) Constarints
c) Rule
*d) Triggers
NOTE: "Permissions" also gets dropped
71. Display the name, price of the item, if price of item is null, then 0.00 has to be displayed
*a) Select name, ISNULL(price, $0.00)
b) select name, IIF(price, $0.00)
*c) select name, coalase(price, $0.00)
72. Display the count of unique titles
*a) select count(distinct title_id) from titles
73. Display the employee name and manager name from the table emp(emp_id, name, mgr_id)
*a) select a.name, b.name
from empa, emp b where b.emp_id= a.mgr_id
74. DMO objects to enable affecting tasks & events
*a) Use alert & task objects of SQL executive
75. How can OLE automation objects be used?
*a) thro' DMO & thro' VBA
76. To increase the size of the device I give the following instructions.
DISK INIT
name=.....
size= 9000
*a) Will not work
77. Log is full; you have created a device called mydevice. DISKINIT has already been used . ALTER database on mydevice = 5 is given to increase the log size
*a) is an outstanding soln..
78. How to ensure that pubid which is not in publishers is not inserted in titles.
*a) reference constarint
b) triggers
*c) Foreign Key
d) defaults
79. Two tables awith common attributes , they are in
*a) Relationship
80. The advantage of composite index is
*a) When the first key is used with the where clause the index is used
81. I have a table travel, custno is primary key and I have a column called t_id which I want to be unique what will I do?
*a) t_id should be made unique
82. How can I achieve maximum security when I have a stored procedure?
*a) Remove all permission on related object and give exec permission to the procedure
83. The logical attribute of physical entity is called
*a) column
84. A rule is bound to a datatype, so
*a) All existing table that have the data type will be effected.
85. Collection point for all public data is
*a) Distribution server
b) Publishing server
c) Subscription server
d) SQL server
86. I have created a view on a table, dropping the table will result in
*a) View will not be dropped but will result in an error when used
87. Creation of table will create an entry in
*a) sysobjects of user db
88. Creation of view will result in a following table being updated.
*a) sysobjects
*b)syscomments
c) sysviews
d) syscolumns
89. Triggers get executed in response to a ---------- getting modified
*a) table
90. How does SQL server allow u to take a corrective action
*a) Triggers
91. Whenever corrective action needs to be taken and business rules is to be used
*a) Triggers needs to be used.
92.Multiple servers are managed by SQL server using
*a) SQL Enterprise manager
93. You have 2 hours in hand to update the transaction . What will you do?
*a. Replication with loose consistency
94.A null value is
*a) UNKNOWN
95. The features of primary key are
*a) A column or group of column
*b) is not null
96.Ensuring that primary key is unique and does not allow null value is called
*a) Entity integrity
97.The various database objects are
*a) Table
*b) rules
98. Stored proc is a collection of
*a) Precompiled sql statement
*b) Is a database object
99. You access the SQl server thro' an application using DMF. Which one of the following are you allowed to access?
*a. The alert manager and the task manager through the SQL -Executive.
b. The Mail API thro' Microsoft Mail.
c. Extended stored procedure using Windows NT.
100. How does subscription server maintain data integrity ?
*a. By receiving articles as one unit
101.There are 3 tables title, author and titleauthor . The rule posed on the 3 tables is that, every author should have written 0 - many books and every book should have atleast one author. The titleauthor table connects titles and the author table. During the creation of the table how will you ensure these rules?
*a. Create REFERENCES constraint on titleauthor(title_id) to the title table, and
Create REFERENCES constraint on titleauthor(author_id) to the author table.
102. What type of lock is created when an Index is created?
*a. An exclusive lock is created when a clustered index is created.
*b. A shared lock is created when a non-clustered index is created.
103. Which 3 of the following will ensure for a fast BCP?
a. Delete all triggers
*b. Set SELECT INTO/BULKCOPY ON to true
*c. Transactions not logged
*d. Drop all indexes
104.There are 2 tables cust_summary and cust_info. The cust_info table contains columns like cust_id, amount, name etc,. The group total of amount for each customer is calculated and updated in the total_amount of the cust_summary table. It should be seen that the cust_summary table is not updated when these updation are taking place.
BEGIN TRAN select cust_id, total = SUM(amount)
into #cust1
from cust_info
group by cust_id HOLD LOCK
update cust_summary
set a.total_amount = b.total
from cust_summarya, #cust1 b
where a.cust_id = b.cust_id
COMMIT TRAN
END TRAN
a. Will meet the requirements and is an outstanding solution
*b. Will meet the requirements and is an adequate solution. (Likely to be the answer . Better go for UPDATE LOCK )
c. Will meet the requirements but is not a desirable solution.
d. Will not meet the requirements and will not work.
105. There is a publisher table and a title table. All the publishers who have published books whose average price is > $10 is to be listed.
a. select a.pub_id, a.pub_name, avg(b.price)
from publishers a , titles b
having avg(price) > 10
b. select a.pub_id, a.pub_name, avg(b.price)
from publishers a , titles b
where a.pub_id = b.pub_id and avg(price) > 10
*c. select a.pub_id, a.pub_name, avg(b.price)
from publishers a , titles b
group by a.pub_id, a.pub_name
having avg(price) > 10
106. To ensure an Update-in-place
a. The table should be marked for replication
b. The table should participate in a clustered index
*c. The table should have fixed column width
d. The timestamp datatype should be used
107. How will you view the name of the current user and database?
a. select @@USERNAME, @@DBNAME
*b. select user_name(), db_name()
c. select USER_ID, DB_ID
108. How will you view all the books whose type is cooking .
*a. select * from titles
where type = 'cooking'
109.To list all the books starting with 'computers'
select * from titles
where name = %computer%
a. Will meet the requirements and is an outstanding solution .
b. Will meet the requirements and is an adequate solution .
*c.Will meet the requirements and is not a desirable solution .
d.Will not meet the requirements and will not work .
110. What is DMO (Distributed Management Object) ?
a. It is an API which help in developing Applications for other operating systems.
b. It is Server side API used to develop applications using c++ and MS VisualBasic
*c. Functions, objects used to develop applications for administrative functions.
d. Used to develop applications like MS Excel and MS Access to access data from sql-server.
111.You want to wait for 10 minutes what will you do ?
*a. wait for delay "00:10:00"
112. What is the reason for creating a Index?
*a. column is used for sorting results.
*b. quick search.
*c. for data specified in the WHERE clause.
113. How to insert in values in the row by the user defined order
*a. insert employee (e_name, e_id) values ("xxxxx", "x101" )
114. How can you hide the view definitions ?
*a. Use the WITH ENCRYPTION option
115.You want to take advantage of DMO objects what will you do?
*a. Use OLE Automation with VB and Excel
116. You are working on a environment that has heavy online transaction processing. How should you manage the distribution process to ensure the workload is reduced ?
*a. Set aside a separate server for the distribution and another for the subscription.
117.How can you use triggers to maintain data integrity?
*a. With the help of the audit tables.
118. You are copying data from a mainframe thro' bcp. It has to be sent to 20 sql servers on the network. What has to be done ?
*a. Replication thro' Horizontal Partitioning.
119. When does synchronization happen ?
*a. Only the first time when you subscribe to a publication.
120. You want to dump the transaction log, but the transaction has some free space which one will you choose ?
*a. Dump Transaction with truncate_only.
b. Dump Transaction with no_log .