Examination: SQL Implementation Exam A

.

1. The relational data model includes several types of data integrity. Which type of data integrity is ensured by the use of a primary key?

a. entity integrity

b. domain integrity

c. referential integrity

d. relational integrity

Answer: a

- Your response: [None] (Incorrect)

Section: 1. Data Modeling

Choice a is correct. A join operation usually links a primary key with a foreign key. The relationship between a primary key and a foreign key is used to enforce referential integrity. Enforcement of referential integrity requires that records in the "child" or related table always have an associated record in the "parent" or primary table. DDC p. 10, Data Integrity.

2. A patient in a hospital has three different doctors. Each of these doctors has several patients. Which type of relationship in the relational model would effectively represent this?

a. three one-to-many relationships

b. two many-to-one relationships

c. a single many-to-many relationship

d. at least two many-to-many relationships

Answer: c

- Your response: [None] (Incorrect)

Section: 1. Data Modeling

Choice c is correct. The relational model specifies that a single relationship exists between any two tables. That relationship can be one or zero-to-many, one-to-one, or one-to-many. When each entity from one table can have more than one associated row on a second table it is called a one-to-many relationship. When a one-to-many relationship also exists from the related table to the primary table, then a many-to-many relationship exists between the two. DDC pp. 10-11, Referential Integrity.

3. Which of the following properties are required of a column or set of columns in order for the column or set of columns to function as a primary key?

a. no nulls

b. a clustered index

c. a nonclustered index

d. there must be at least one foreign key on the same table

Answer: a

- Your response: [None] (Incorrect)

Section: 1. Data Modeling

Only choice a is correct. There cannot be any nullable columns in a primary key since this would make it possible for two rows to contain the same key. Although SQL Server will create a unique clustered or nonclustered index on a key designated as primary, neither of these is required to qualify the column set as a primary key. DDC pp. 18, PRIMARY KEY Constraints.

4. The sales database contains a customer table and an order table. For each order there is one and only one customer, and for each customer there can be zero or many orders. How should primary and foreign key fields be placed into the design of this database?

a. A primary key should be created for the customer_id field in the customer table and also for the customer_id field in the order table.

b. A primary key should be created for the order_id field in the customer table and also for the customer_id field in the order table.

c. A primary key should be created for the customer_id field in the customer table and a foreign key should be created for the customer_id field in the order table.

d. A primary key should be created for the customer_id field in the customer table and a foreign key should be created for the order_id field in the order table.

Answer: c

- Your response: [None] (Incorrect)

Section: 1. Data Modeling

Choice c is correct. Relationships between primary and foreign keys are essential components in the relational database model. A primary key column (or set of columns in a composite key) uniquely identifies each row in the primary or "parent" table. Foreign keys in related or "child" tables "inherit" the primary key of the "parent" or primary table. These relationships can then be used to enforce data integrity or create join conditions that simplify data retrieval. DDC pp. 10-11, Referential Integrity.

5. A physician may or may not have a pager number. How could you provide a column in the physician table to reflect the pager number for physicians who have one without requiring an entry for physicians that do not?

a. make the pager number column NOT NULL

b. place a UNIQUE index on the pager number column

c. place a CLUSTERED INDEX on the pager number column

d. assign a default to the pager number column

Answer: d

- Your response: [None] (Incorrect)

Section: 1. Data Modeling

Only choice d is correct. When a column is defined as NOT NULL, it is required to have a value assigned to it at all times. UNIQUE indexes do not allow nulls, nor do CLUSTERED INDEX columns. When a column is assigned a default, it is never required to have a value assigned to it since the default will always be inserted when no value is specified. DDC pp. 16-17, DEFAULT Constraints.

6. Which of the following types of table columns will prevent the entry of duplicate rows?

a. NULL

b. UNIQUE

c. PRIMARY KEY

d. FOREIGN KEY

Answer: bc

- Your response: [None] (Incorrect)

Section: 1. Data Modeling

Only choices b and c are correct. When nulls are allowed in a column it is possible to have duplicate rows, since there is no way to distinguish between two nulls. A UNIQUE index constraint will prevent duplicate values from being entered into the column as will a PRIMARY KEY constraint. A FOREIGN KEY constraint will only enforce domain integrity by insuring that only values available in the referenced table are inserted into the FOREIGN KEY column. DDC pp. 9-20, Data Integrity.

7. Which database contains the sysdevices table?

a. tempdb

b. msdb

c. master

d. all databases

Answer: c

- Your response: [None] (Incorrect)

Section: 2. System Databases and Tables

Choice c is correct. The sysdevices table resides only in the master database. It keeps track of the storage devices used by SQL Server. These storage devices are defined and configured using the DISK INIT and DISK RESIZE statements. Other tables that reside only in the master database are syslogins, sysmessages, and sysdatabases. TSQL p. 600, System Tables-System Catalog.

8. With master set as the current database you execute the statement CREATE TABLE ##products. Where is this table created?

a. in the pubs database

b. in the tempdb database

c. in the master database

d. in the system database

Answer: b

- Your response: [None] (Incorrect)

Section: 2. System Databases and Tables

Choice b is correct. Temporary tables are always created in the tempdb database regardless of which database is current when they are created. There are two types of temporary tables; local temporary tables are prefaced with a single pound sign (#), while global temporary tables are prefaced with a double pound sign (##). Local temporary tables are available only to the session that created them, while global temporary tables are available to all sessions. TSQL p. 73, CREATE TABLE statement.

9. Which of the following will return a list of the tables in the current database?

a. SELECT name FROM systables

b. SELECT name FROM sysobjects WHERE type = 'T'

c. sp_tablenames

d. none of the above

Answer: d

- Your response: [None] (Incorrect)

Section: 2. System Databases and Tables

Choice d is correct. There is no systables table or sp_tablenames stored procedure. To return a list of tables in the current database you can query the sysobjects table for object names where the type is 'U' not 'T'. This is a standard technique used by SQL Server programmers to quickly determine the tables in a database. TSQL p. 588, sysobjects table.

10. If you attempt to create a stored procedure using the name of an existing stored procedure in the database you will get an error. Therefore, when writing a script to create a stored procedure it is important to check for an existing stored procedure with the same name and drop it if it exists. Assuming that you are the database owner, which of the following SQL batches will drop a stored procedure named sp_myprocedure from the current database?

a. select * from sysprocedures where name = 'dbo.sp_myprocedure'

   if @@ROWCOUNT >= 1 then drop procedure dbo.sp_myprocedure

b. select * from sysobjects where name = object_name()

   if @@ROWCOUNT = 1 then drop procedure dbo.sp_myprocedure

c. if exists (select * from sysprocedures where id = object_id('dbo.sp_myprocedure'))

   delete procedure dbo.sp_myprocedure from sysobjects

d. if exists (select * from sysobjects where id = object_id('dbo.sp_myprocedure'))

   drop procedure dbo.sp_myprocedure

Answer: d

- Your response: [None] (Incorrect)

Section: 2. System Databases and Tables

Only choice d is correct. The object name system function always takes an object id number for its argument. The sysprocedures and sysobjects tables both contain the id number for stored procedures but only the sysobjects table contains the name. You can see many examples of how to use system tables by looking at the source code for system stored procedures such as sp_who or sp_spaceused. TSQL p. 574, System Tables-Database Catalog.

11. How many pages and extents are in a SQL Server allocation unit?

a. 2000 pages or 256 extents

b. 256 pages or 256 extents

c. 256 pages or 32 extents

d. 32 pages or 32 extents

Answer: c

- Your response: [None] (Incorrect)

Section: 2. System Databases and Tables

Choice c is correct. The basic unit of measure is a page that is 2KB. These pages are always allocated in groups of eight that are called extents. Thirty-two extents or two hundred and fifty-six pages make up an allocation unit. AC pp. 120-121, Before Creating Databases.

12. Which of the following CANNOT be issued when the database option 'trunc. log on chkpt.' is set to true?

a. DUMP TRANSACTION

b. DUMP DATABASE

c. DROP TABLE

d. DROP VIEW

Answer: a

- Your response: [None] (Incorrect)

Section: 3. Data Definition

Choice a is correct. The 'trunc. log on chkpt.' database option prevents the log from filling up by purging it at regular time intervals (60 seconds by default). This is useful when doing development work since you do not have to worry about space being used for data recovery. However, you would never use DUMP TRANSACTION with this option set since the transaction log would not provide a complete record of changes made to the data since the last DUMP DATABASE was issued. If you issue DUMP TRANSACTION with 'trunc. log on chkpt.' set, you will get an error message telling you that you must DUMP DATABASE first. TSQL p. 495, sp_dboption System Stored Procedure.

13. Which of the following are used together to allocate disk space exclusively for use by a transaction log?

a. CREATE DATABASE with the LOG ON clause

b. CREATE DATABASE with the TRAN DEVICE clause

c. ALTER DATABASE with the LOG ON clause

d. ALTER DATABASE and sp_logdevice

Answer: ad

- Your response: [None] (Incorrect)

Section: 3. Data Definition

Only choices a and d are correct. You can create space exclusively for use by a transaction log with the CREATE DATABASE statement using the LOG ON clause. You can increase the size of the transaction log device with the ALTER DATABASE statement. The transaction log must reside on a device separate from the database for it to be expanded. After issuing the ALTER DATABASE statement to expand a transaction log device you still must use the sp_logdevice statement so that the new space will be used by the transaction log. TSQL pp. 1-3, ALTER DATABASE.

14. Which of the following statements will display information about a constraint called 'salary1' that is attached to the table 'employee'?

a. sp_helpconstraint salary1

b. sp_helpconstraint employee

c. sp_help salary1

d. sp_helptext employee

Answer: b

- Your response: [None] (Incorrect)

Section: 3. Data Definition

Choice b is correct. The sp_helpconstraint procedure takes a table name as its argument. Sp_help only returns information on objects within a database (constraints are not considered high-level objects within a database). Sp_helptext returns the script that was used to create a rule, a default, or an unencrypted stored procedure, trigger, or view. It does not return the script that would be used to define a table. TSQL pp. 517-518, sp_help and sp_helpconstraint System Stored Procedures; p. 535, sp_helptext System Stored Procedure.

15. Which of the following restrictions apply to the ALTER TABLE statement?

a. You cannot use ALTER TABLE to remove a column from a table.

b. You cannot use ALTER TABLE to add a PRIMARY KEY constraint to a table.

c. You cannot use ALTER TABLE to add a NULL column to a table with existing data.

d. When adding a constraint to an existing table with the ALTER TABLE statement, existing data will not be checked against the new constraint.

Answer: a

- Your response: [None] (Incorrect)

Section: 3. Data Definition

Only choice a is correct. All types of constraints can be added to a table with the ALTER TABLE statement. You cannot use ALTER TABLE to add a NOT NULL column to a table with existing data, since values would then have to be determined for each existing row. Existing data is normally checked against constraints added with the ALTER TABLE statement. However, you can force existing data to be ignored by using the WITH NOCHECK clause. TSQL p. 10, ALTER TABLE Statement.

16. Which of the following arguments can be used when defining an IDENTITY constraint?

a. default

b. seed

c. distribution

d. increment

Answer: bd

- Your response: [None] (Incorrect)

Section: 3. Data Definition

Only choices b and d are correct. When you define an IDENTITY column with the CREATE TABLE or ALTER TABLE statements you can define a seed and increment value. The seed value specifies the number that will be assigned to the first row in the table (note that you can create an identity column on an existing table). The increment value specifies the value that will be added to the previous row for each subsequent row. These arguments are optional and are assigned a value of one by default. TSQL p. 5, ALTER TABLE Statement.

17. Which statements would you use to drop a CHECK constraint named 'stucheck' from a table called 'student'?

a. DROP CONSTRAINT stucheck

b. DROP CONSTRAINT ' stucheck ' FROM student

c. ALTER TABLE student DROP stucheck

d. DROP TABLE student

Answer: c

- Your response: [None] (Incorrect)

Section: 3. Data Definition

Choice c is correct. The DROP statement is used to remove objects from a database (constraints are not considered high-level objects within a database). You must add and remove constraints from the database (and tables) using the CREATE TABLE or ALTER TABLE statements. DDC p. 19, Dropping Constraints.

18. Which of the following is only used with a FOREIGN KEY constraint?

a. a PRIMARY KEY constraint

b. the REFERENCES clause

c. a CHECK constraint

d. sp_foreignkey

Answer: b

- Your response: [None] (Incorrect)

Section: 3. Data Definition

Choice b is correct. Although a set of columns referenced by a FOREIGN KEY constraint should have a PRIMARY KEY or UNIQUE constraint defined for it, neither is required. A CHECK constraint acts like a rule by limiting the values that can be entered into a column based on a list or LIKE expression. Sp_foreignkey was used in previous versions of SQL Server for documentation purposes but serves no real purpose in SQL Server 6.5. TSQL p. 7, ALTER TABLE Statement.

19. In your results you want to display the character string 'The name of this product is' immediately before the product name. Which of the following SQL SELECT statements could you use?

a. SELECT 'The name of this product is', prodname FROM products

b. SELECT 'The name of this product is ' & prodname FROM products

c. SELECT 'The name of this product is ' + prodname FROM products

d. SELECT (The name of this product is), prodname FROM products

Answer: ac

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Only choices a and c are correct. The SELECT statement can be used to display a result set containing any combination of SQL Server expressions. This includes not only columns from tables but constants and character strings as well. To return character strings you can either define them as a column to be returned or concatenate them with columns in the result set. DDC pp. 63-64, SELECT Syntax.

20. Which of the following TSQL functions will return a string of twenty-four asterisk characters?

a. SELECT '*' * 24

b. SELECT REPEAT('*') * 24

c. SELECT REPEAT('*', 24)

d. SELECT REPLICATE('*', 24)

Answer: d

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Choice d is correct. You can perform a variety of useful operations with SQL Server's built-in functions. The REPLICATE function takes two arguments and repeats a character expression the specified number of times. Other useful string functions include SUBSTRING, RTRIM, RIGHT, SPACE, and STRING. TSQL pp. 204-207, Functions.

21. The ASCII code value of the uppercase letter 'A' is 65. Which of the following will take as an argument the number 65 and return the letter 'A'?

a. SELECT ASCII(65)

b. SELECT ASCII 65

c. SELECT CHAR(65)

d. SELECT CHARINDEX(65)

Answer: c

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Choice c is correct. SQL Server provides an assortment of string, date, and mathematical functions for manipulating data and queries. The ASCII function takes a character argument surrounded by quotes and returns the corresponding ASCII value for that character. The CHAR function performs the reverse operation of accepting an ASCII number and returning the corresponding character. TSQL p. 204, Functions.

22. You want to display descriptive information about the price ranges of the products in your company's database. Which of the following SQL SELECT statements could you use?

a. SELECT title, 'Price Range' =

   IF price < 20 THEN 'Low Priced Item'

   IF price >= 20 THEN 'Higher Priced Item'

   FROM titles

b. SELECT title, 'Price Range' AS

   IF price < 20 THEN 'Low Priced Item'

   IF price >= 20 THEN 'Higher Priced Item'

   FROM titles

c. SELECT title, 'Price Range' =

   CASE

      WHEN price < 20 THEN 'Low Priced Item'

      WHEN price >= 20 THEN 'Higher Priced Item'

   END

   FROM titles

d. SELECT title, 'Price Range' AS

   CASE

      WHEN price < 20 THEN 'Low Priced Item'

      WHEN price >= 20 THEN 'Higher Priced Item'

   END

   FROM titles

Answer: c

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Choice c is correct. You can use the CASE statement to format query results and create derived columns based on values in other columns. You can assign any name you want to the derived column as long as that name is enclosed in single quotes. You can also use the CASE statement to replace result values with any other character string (e.g., replacing the product names with more descriptive information). DDC pp. 60-62, SELECT Syntax.

23. Consider the following SQL SELECT statement:

   SELECT prodname FROM products WHERE prodname LIKE '_s'

Which of the following product names will match the search criteria and be returned in the result set?

a. apples

b. oranges

c. _s

d. is

Answer: cd

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Only choices c and d are correct. The underscore wildcard character (_) indicates that only a single character in the indicated place combined with the other characters in the string will match. To match on any number of characters you would use the percentage sign (%). Individual characters and ranges of characters can be indicated for inclusion in the search by placing them in brackets ([ and ]). TSQL p. 687, Wildcard Characters.

24. Which of the following system functions will return the login name by which you are known to SQL Server across all of its databases?

a. DB_USER()

b. DB_ID()

c. USER_ID()

d. SUSER_NAME()

Answer: d

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Choice d is correct. SQL Server provides a variety of built-in functions to simplify various operations. In addition to string, date, and mathematical functions there are system functions that return information related to the various system tables. The USER_ID, USER_NAME, and SUSER_ID, SUSER_NAME system function pairs return either the user's database name and id or the SQL Server login name and id respectively. TSQL p. 210, Functions.

25. You are currently logged into the Pubs database. Which of the following SQL SELECT statements will return the identification number for the pubs database?

a. SELECT DB_NAME()

b. SELECT DB_ID(@@DBNAME='pubs')

c. SELECT DB_NAME('pubs', id)

d. SELECT DB_ID('pubs')

Answer: d

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Choice d is correct. The system functions built into SQL Server typically come in pairs. Examples are DB_ID, DB_NAME; OBJECT_ID, OBJECT_NAME; and USER_ID, USER_NAME. For these system functions, providing an identification number will return a name while providing a name will return a system identification number. If no parameter is provided you will receive the id or name of the current database or user. DDC pp. 154-155, Built-in Functions.

26. Which of the following SQL SELECT statements will return an error?

a. SELECT 'Sum' = SUM(totalsale) FROM productsales

b. SELECT "Sum" = SUM(totalsale) FROM productsales

c. SELECT 'This Company''s Sales' = SUM(totalsale) FROM productsales

d. SELECT 'This Company's Sales' = SUM(totalsale) FROM productsales

Answer: d

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Choice d is correct. You can place almost any character string in a column heading as long as it is enclosed with single or double quotes. However you must be careful about mixing single and double quotes. Quotes in column heading strings are always matched one following the other, and an unmatched quote in the wrong place may produce unwanted results. DDC pp. 62-63, Quoted Strings in Column Headings, and Character Strings and Quotation Marks.

27. What order is used to display columns when the SELECT * statement is used?

a. alphabetical order

b. binary order

c. the columns are displayed in the order they were created or added to the table

d. none of the above

Answer: c

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Choice c is correct. Using the SELECT * syntax removes control over the order in which the columns are displayed since they will only be displayed in the order the were created. It is usually better in applications to define the columns to be returned since this insures a predictable ordering of the columns. SELECT * is most useful for designing and debugging. DDC p. 57 Choosing Columns: The Select List.

28. Which of the following SELECT statements is used to return the number of rows in a table?

a. SELECT ALL COUNT from tablename

b. SELECT ROWCOUNT from tablename

c. SELECT COUNT(*) from tablename

d. SELECT NUMROWS from tablename

Answer: c

- Your response: [None] (Incorrect)

Section: 5. Retrieving Data (Advanced Topics)

Choice c is correct. SQL Server aggregate functions include COUNT, AVG, MIN, MAX, and SUM. These functions, when used in SELECT statements, will return summary values about the table or other column set. COUNT is the only aggregate function used with the asterisk character to obtain a summary value. This is because it is the only aggregate function that does not use a specific column set. DDC p. 137, COUNT(*).

29. You want to show the salary of each employee arranged by department. At the end of each department grouping you want to display the total salaries for that department. Which of the following SQL SELECT statements could you use?

a. SELECT employeename, SUM(salary)

   FROM employee

   GROUP BY department

   COMPUTE SUM(salary) BY department

b. SELECT employeename, department, SUM(salary)

   FROM employee

   GROUP BY department

   ORDER BY department

c. SELECT employeename, department, salary

   FROM employee

   ORDER BY department

   COMPUTE SUM(salary) BY department

d. SELECT employeename, department, salary

   FROM employee

   ORDER BY department

   COMPUTE SUM(salary)

Answer: c

- Your response: [None] (Incorrect)

Section: 5. Retrieving Data (Advanced Topics)

Choice c is correct. The COMPUTE clauses (COMPUTE and COMPUTE BY) are used to provide aggregate summaries (SUM, AVG, MIN, MAX, and COUNT) on groups without using the GROUP BY clause. The COMPUTE clauses are always used with ORDER BY so that summary values will always be listed with the groups they represent. COMPUTE provides a single column set aggregate for the entire result set while COMPUTE BY provides aggregate values for each grouping. DDC pp. 146-153, The COMPUTE Clause.

30. You want to find the total amount of widgets sold by each region so far this year. Which of the following SQL SELECT statements could you use?

a. SELECT region, SUM(units)

   FROM productsales

   GROUP BY region

b. SELECT region, units

   FROM productsales

   GROUP BY SUM(units)

c. SELECT region

   FROM productsales

   GROUP BY SUM(units)

d. SELECT region, units

   FROM productsales

   GROUP BY region HAVING SUM(units)

Answer: a

- Your response: [None] (Incorrect)

Section: 5. Retrieving Data (Advanced Topics)

Choice a is correct. The GROUP BY clause is used most often with aggregate functions. It divides a result set based on a particular set of columns. When used with aggregate functions the GROUP BY clause returns a single value for each group. All columns in the select list must either be contained in an aggregate function or be listed in the GROUP BY clause. DDC pp. 139-144, The GROUP BY Clause.

31. Which of the following SQL SELECT statements will return all departments with more than 100 employees?

a. SELECT department

   FROM employees

   WHERE COUNT(*) > 100

b. SELECT department

   FROM employees

   HAVING COUNT(*) > 100

c. SELECT department

   FROM employees

   GROUP BY department

   HAVING COUNT(*) > 100

d. SELECT DISTINCT department

   FROM employees

   GROUP BY department

   WHERE COUNT(employees) > 100

Answer: c

- Your response: [None] (Incorrect)

Section: 5. Retrieving Data (Advanced Topics)

Choice c is correct. The HAVING clause is only appropriate when used with a GROUP BY clause. The HAVING clause restricts the results that are returned based on a condition related to the result set groupings. It works in much the same way that the WHERE clause restricts results returned based on a condition related to the columns in the select list. DDC pp. 144-146, The HAVING Clause.

32. You want to display all of the employees in your company. You want to provide a special indication for those who live in a city where a supplier is located. Which of the following outer-join queries could you use?

a. SELECT employeename, suppliername

   FROM employees, suppliers

   WHERE employees.city =* suppliers.city

b. SELECT employeename, suppliername

   FROM employees, suppliers

   WHERE employees.city *= suppliers.city

c. SELECT employeename, suppliername

   FROM employees, suppliers

   WHERE employees.city IN suppliers.city

d. SELECT e.employeename, s.suppliername

   FROM employees e, suppliers s

   WHERE s.city = e.city

Answer: b

- Your response: [None] (Incorrect)

Section: 5. Retrieving Data (Advanced Topics)

Choice b is correct. An outer join is used to return columns from one table even if they do not meet the join condition. The asterisk (*) is used with the equal sign to indicate which table will return columns that do not match. In this case the suppliername column will display a null value for non-matching rows and will display the supplier name for rows that match. DDC pp. 105-109, Using Joins to Create Multiple Queries.

33. In the pubs database the titleauthor table is used to define the many-to-many relationships between authors and books. Which of the following SQL SELECT statements will show which books (titles) have more than one author?

a. SELECT DISTINCT au_id, title_id

   FROM titleauthor

   WHERE title_id = title_id

   AND au_id <> au_id

b. SELECT DISTINCT title_id

   FROM titleauthor

   WHERE title_id(1) = title_id(2)

   AND au_id(1) <> au_id(2)

c. SELECT DISTINCT au_id, title_id

   FROM titleauthor t1, titleauthor t2

   WHERE t1.title_id = t2.title_id

   AND t1.au_id <> t2.au_id

d. SELECT DISTINCT t1.title_id, t2.title_id

   FROM titleauthor t1, titleauthor t2

   WHERE t1.title_id = t2.title_id

   AND t1.au_id <> t2.au_id

Answer: d

- Your response: [None] (Incorrect)

Section: 5. Retrieving Data (Advanced Topics)

Choice d is correct. The not-equal sign is usually only used with self-joins. In a self-join, a table is given at least two separate identities. Aliases are used to distinguish one version of the table from another. Once the table is defined as more than one virtual table it can be treated as such in SELECT statements. DDC p. 103, Using Joins to Create Multiple Queries.

34. You want to know which publishers sell books that cost more than $40.00. Which of the following SELECT statements could you use?

a. SELECT pub_name

   FROM publishers, titles

   WHERE pub_id IN

   (SELECT pub_id

   FROM titles

   WHERE price > 40)

b. SELECT pub_name

   FROM publishers, titles

   WHERE pub_id EXISTS

   (SELECT pub_id

   FROM titles

   WHERE price > 40)

c. SELECT pub_name

   FROM publishers

   WHERE pub_id IN

   (SELECT pub_id

   FROM titles

   WHERE price > 40)

d. SELECT pub_name

   FROM publishers

   WHERE pub_id =

   (SELECT pub_id

   FROM titles

   WHERE price > 40)

Answer: c

- Your response: [None] (Incorrect)

Section: 5. Retrieving Data (Advanced Topics)

Choice c is correct. You would not want to include the titles table in the FROM clause of this query without qualifying the pub_id columns in the subquery (SQL Server finds this statement to be ambiguous). The EXISTS keyword returns true or false and is not used for comparison operations. The equal sign operator cannot be used with a subquery that returns multiple row results. DDC pp. 111-133, Using Subqueries.

35. You have been using the identity column to create a product_id value for each of your company's products. It is determined that one of the product rows was accidentally deleted many weeks ago and that many products have been added to the table since then. How can you INSERT a row for the missing product and give it a product_id of 48?

a. You will have to move all rows with product ids greater than 47 into a temporary table, insert the new product id 48, and then re-insert all of the products in the temporary table.

b. You can insert product id number 48 directly into the table with a simple INSERT statement since there will not be a product with an id of 48 in the table.

c. You will have to use the SET statement with the IDENTITY INSERT option activated to re-insert product number 48.

d. You will have to recreate the products table and do a SELECT INTO operation in order to reclaim product id number 48.

Answer: c

- Your response: [None] (Incorrect)

Section: 6. Modifying Data

Choice c is correct. A value cannot be inserted directly into an identity column by default. If you accidentally delete a row, or if you want to fill a gap that exists for some other reason, you should use the SET statement with the IDENTITY INSERT option turned on temporarily. This is an important consideration when deciding whether or not to use an identity column in your table. There are some cases where the use of an identity column may not be ideal. TSQL p. 75, CREATE TABLE Statement.

36. The type column in the titles table of the pubs database has a default value of 'UNDECIDED' assigned to it. Which of the following INSERT statements will cause the default value to be entered into the type column?

a. INSERT titles (title_id, title, DEFAULT, pubdate)

   VALUES ('RH6320', 'SQL Server Basics', 'UNDECIDED', 'June 12 1995')

b. INSERT titles (title_id, title, type, pubdate)

   VALUES ('RH6320', 'SQL Server Basics', DEFAULT, 'June 12 1995')

c. INSERT titles (title_id, title, type, pubdate)

   VALUES ('RH6320', 'SQL Server Basics', 'June 12 1995')

d. INSERT titles (title_id, title, pubdate)

   VALUES ('RH6320', 'SQL Server Basics', 'June 12 1995')

Answer: bd

- Your response: [None] (Incorrect)

Section: 6. Modifying Data

Only choices b and d are correct. Defaults are used to insure that there will always be some value other than NULL inserted into a column when a value is not specified. There must always be the same number of columns in the column list as there are in the values list. Defaults are defined for a column when the table is created or modified with the ALTER statement. TSQL pp. 224-225, INSERT Statement.

37. The product table in your database contains a price column that allows NULL values. Which of the following UPDATE statements will set the value of the price column to NULL for all of the products whose product id is between 480 and 500?

a. UPDATE products SET price = 0

   WHERE product_id >= 480 AND product_id <= 500

b. UPDATE products SET price AS NULL

   WHERE product_id > 480 AND product_id < 500

c. UPDATE products SET price = NULL

   WHERE product_id >= 480 AND product_id <= 500.

d. You cannot use an UPDATE statement to set columns to NULL.

Answer: c

- Your response: [None] (Incorrect)

Section: 6. Modifying Data

Choice c is correct. Zero and null are not the same in SQL Server and do not equate to each other. You can set columns to NULL during an UPDATE operation with the statement SET columnname = NULL. The WHERE statement in an UPDATE can include any search condition allowed for a SELECT statement. TSQL p. 656, UPDATE Statement.

38. Which of the following restrictions apply to SQL Transfer Manager?

a. A source server can be a Windows NT, OS/2, or non-Microsoft SQL Server, but the destination server can only be Windows NT SQL Server.

b. A source server can only be Windows NT SQL Server, but the destination server can be a Windows NT, OS/2, or non-Microsoft SQL server.

c. Both the destination and source server can be a Windows NT, OS/2, or non-Microsoft SQL server.

d. Both the destination and source server can only be Windows NT SQL Server

Answer: a

- Your response: [None] (Incorrect)

Section: 6. Modifying Data

Choice a is correct. Also note that if you transfer data from SQL Server 4.21 or 6.5, you cannot include SQL Server 6.5-specific datatypes. Also, SQL Server 6.5 features such as identity columns and declarative referential integrity will not be transferred. AC pp. 297-299, Using SQL Transfer Manager.

39. The Orders table contains a field for the employee id of the person who entered the order into the system. This field shares a many-to-one relationship with the employees table which contains a first and last name for each employee who has an employee id. Which of the following DELETE statements will remove all rows from the orders table that have been placed by John Adams?

a. DELETE * FROM orders

   WHERE orders.employee_id = employee.employee_id

   AND employee.firstname = 'John'

   AND employee.lastname = 'Adams'

b. DELETE orders, employee

   WHERE orders.employee_id = employee.employee_id

   AND employee.firstname = 'John'

   AND employee.lastname = 'Adams'

c. DELETE FROM orders

   WHERE orders.employee_id = employee.employee_id

   AND employee.firstname = 'John'

   AND employee.lastname = 'Adams'

d. DELETE FROM orders

   FROM orders, employee

   WHERE employee.firstname = 'John'

   AND employee.lastname = 'Adams'

   AND orders.employee_id = employee.employee_id

Answer: d

- Your response: [None] (Incorrect)

Section: 6. Modifying Data

Choice d is correct. When you want to delete rows in a table based on values in related tables you must include two sets of table names. The first set lists the table from which rows will be deleted and the second set lists the tables in the WHERE clause. All tables referenced in the WHERE clause must be included in the second table list. TSQL pp. 141-142, DELETE Statement.

40. Which of the following will NOT delete all rows from the orders table?

a. DELETE * FROM orders

b. DELETE orders

c. TRUNCATE TABLE orders

d. DELETE ALL FROM orders

Answer: ad

- Your response: [None] (Incorrect)

Section: 6. Modifying Data

Only choices a and d are correct. The FROM keyword is optional for the DELETE statement. The DELETE statement never contains a column list or ALL keyword since all columns are deleted. You cannot delete values in individual columns using the DELETE statement. TSQL pp. 140-142.

41. Consider the following batch:

   CREATE TABLE mytable (myid IDENTITY(1,1), myname char(10))

   GO

   INSERT mytable VALUES('Ron')

   INSERT mytable VALUES('Rob')

An input file called table_in will be used for bcp which looks like this:

<tab>3<tab>James

<tab>4<tab>Joe

<tab>5<tab>Jim

Which of the following bulk copy commands will put values into the table correctly?

a. bcp mytable in table_in /i /Usa /P /e error_out

b. bcp mytable in table_in /i[3,1] /Usa /P /e error_out error_out

c. bcp mytable in table_in /e /Usa /P /e

d. bcp mytable in table_in /E /Usa /P /e error_out

Answer: d

- Your response: [None] (Incorrect)

Section: 6. Modifying Data

Only choice d is correct. Use the /E flag to indicate that identity columns exist in the data to be imported. If you want SQL Server to generate identity column values, you should leave non-identity columns out of the file. AC p. 307, Using the Bulk Copy Program.

42. The IGNORE_DUP_ROW and ALLOW_DUP_ROW options are used with the CREATE INDEX statement to specify what will happen when you try to create a non-unique clustered index. What will happen if you attempt to create a clustered index on a table with duplicate rows without specifying either the IGNORE_DUP_ROW or ALLOW_DUP_ROW option?

a. a clustered index will be created automatically and a message will be returned

b. a clustered index will be created and no message will be returned

c. a clustered index will be created with duplicate rows omitted

d. the CREATE INDEX statement will fail

Answer: d

- Your response: [None] (Incorrect)

Section: 7. Indexes

Choice d is correct. You cannot create a non-unique clustered index without using the ALLOW_DUP_ROW option in the CREATE INDEX statement. If you use the IGNORE_DUP_ROW option the index will be created but duplicate rows will be deleted and an error message will be returned. If there are duplicated values in the indexed columns and neither of these options are specified the CREATE INDEX statement will fail. TSQL pp. 48-49, CREATE INDEX Statement.

43. The SORTED_DATA option is used with the CREATE INDEX statement to prevent a sort from being implemented automatically when creating an index. With which type of index is the SORTED_DATA option used?

a. a composite index

b. a primary key

c. a clustered index

d. a nonclustered index

Answer: c

- Your response: [None] (Incorrect)

Section: 7. Indexes

Choice c is correct. The SORTED_DATA option is used only when creating clustered indexes. If the creation of a clustered index requires that data is re-copied to put it in order, nonclustered indexes will have to be rebuilt. For this reason it may be desirable to check whether the data is properly sorted before creating a clustered index. TSQL p. 48, CREATE INDEX Statement.

44. Which of the following statements will successfully create a composite index on the products table?

a. CREATE INDEX compind ON products AS prodid, prodname

b. CREATE INDEX compind ON products(prodid, prodname)

c. CREATE INDEX compind AS products.prodid, products.prodname

d. CREATE INDEX compind ON products.prodid, products.prodname

Answer: b

- Your response: [None] (Incorrect)

Section: 7. Indexes

Choice b is correct. Composite indexes are created in the same way that single-column indexes are. There can be as many as 16 columns in a composite index but all columns in the composite index must be from the same table. The sum total of all characters and values used in an index cannot exceed 256 bytes. TSQL pp. 44-46, CREATE INDEX Statement.

45. Which of the following types of columns or queries would most likely benefit from the use of a clustered index?

a. columns with a limited number of unique values

b. columns with a large number of unique values

c. queries that return small results sets

d. queries that use an ORDER BY clause

Answer: a

- Your response: [None] (Incorrect)

Section: 7. Indexes

Choice a is correct. A clustered index causes data to be stored in the exact order specified in the CREATE INDEX statement. Clustered indexes are most likely to be used by the optimizer for columns that contain a limited (but not tiny) number of distinct values. Clustered indexes are usually beneficial for queries that return a range of values using comparison operators and for queries that return large results sets. DDC p. 176, Clustered Indexes.

46. Which of the following types of columns or queries would most likely benefit from the use of a nonclustered index?

a. columns that contain a limited number of unique values

b. queries that do not return ordered results sets

c. queries that return small results sets

d. queries that return large results sets

Answer: c

- Your response: [None] (Incorrect)

Section: 7. Indexes

Only choice c is correct. A nonclustered index causes data to be stored randomly but with corresponding index pages that provide pointers to the location of each row on the disk. Nonclustered indexes are most beneficial for columns that contain a large number of distinct values. They are also beneficial when used with queries that return small results sets or that use an ORDER BY clause. DDC p. 177, Nonclustered Indexes

47. A ten-thousand row table in your database is never updated, but is used heavily for queries. All search conditions focus on two particular columns in the table that are usually searched on together as a group. Neither of these columns has a significant number of unique values. Which of the following indexing schemes would probably provide the best query performance?

a. a nonclustered composite index

b. a clustered composite index

c. a nonclustered index on the first column and a clustered index on the second column

d. a clustered index on the first column and a nonclustered index on the second column

Answer: b

- Your response: [None] (Incorrect)

Section: 7. Indexes

Choice b is correct. Composite indexes are most useful when the columns in the index are searched on as a group. An index is more likely to be used by SQL Server if its columns contain a greater percentage of distinctive values. Although a clustered index is good for a column with a medium percentage of unique values, it will not be used on a large table if the column only contains a few unique values. A nonclustered index will usually perform better than a clustered index on a column with a high percentage of unique values. DDC pp. 173-179, Optimizing Indexes.

48. The authors table has a single clustered index named authorindex defined for it. Which of the following statements will force authorindex to be used by the database query optimizer?

a. SELECT date, custid, phone FROM authors (INDEX = 1)

b. SELECT date, custid, phone FROM authors INDEX 1

c. SELECT date, custid, phone FROM authors INDEX = 0

d. SELECT date, custid, phone FROM authors INDEX ON 0

Answer: a

- Your response: [None] (Incorrect)

Section: 7. Indexes

Choice a is correct. Optimizer hints are placed in parentheses after the FROM clause and can indicate a specific locking method as well as a specific index to be used by the query engine. You can specify that an available clustered index is to be used by placing index = 1 in the parentheses, and you can specify that no index is to be used (a table scan is to be used) by placing index = 0 in the parentheses. TSQL p. 269, SELECT Statement.

49. Which of the following is used with the CREATE VIEW statement to ensure that modifications committed through the view will remain visible?

a. WITH CHECK OPTION

b. CHECKOPTION ONLY

c. WITH FUTUREONLY

d. you cannot make data modifications to a table through a view

Answer: a

- Your response: [None] (Incorrect)

Section: 8. Using Views, Defaults and Rules

Choice a is correct. Normally it is possible to INSERT or UPDATE a row into a table through a view that does not meet the criteria used to define the view. For example if your view only displays product rows where the price is greater than $20, users can still insert rows to that table through your view for products that cost less than $20. When the WITH CHECK OPTION clause is used to create a view it insures that no inserts or updates can be made to the table that do not conform to the criteria used to create the view. TSQL p. 89, CREATE VIEW Statement.

50. Which of the following can be attached directly to a view using the CREATE VIEW statement?

a. indexes

b. defaults

c. triggers

d. none of the above

Answer: d

- Your response: [None] (Incorrect)

Section: 8. Using Views, Defaults and Rules

Choice d is correct. You cannot associate a rule, default, trigger or index with a view when creating it. You also cannot create temporary views or create views with temporary tables. Although it is not required that you include column names when you create a view, you must include column names when any of the view columns are derived or when two tables in the view have a column with the same name. DDC pp. 30-31, Creating Views.

51. Which of the following can be defined with a view?

a. a subset of the columns in a table

b. a combination of data from more than one table

c. a combination of data from more than one view

d. a combination of data from more than one view and more than one table

Answer: abcd

- Your response: [None] (Incorrect)

Section: 8. Using Views, Defaults and Rules

Choices a, b, c and d are correct. Views are created with SQL Server SELECT statements and virtually any data set that can be defined with a SELECT statement can define a view. There are restrictions on data modification through views, but views provide good security mechanisms since permission can be granted to a view without granting permission to data in tables that define the view. DDC pp. 29-30, Planning Views.

52. Which of the following inserts and updates are NOT allowed through views?

a. updating view columns derived from computed table columns

b. inserting rows to views derived from computed table columns

c. updating tables with NOT NULL columns for which there are no defaults

d. inserting rows to tables with NOT NULL columns for which there are no defaults

Answer: ab

- Your response: [None] (Incorrect)

Section: 8. Using Views, Defaults and Rules

Only choices a and b are correct. Inserts and updates to computed columns or columns derived from built-in functions are not allowed. This is because there could be no way for SQL Server to determine the values to be placed in the columns from which the computed column was derived. Another restriction arises if null values are not allowed in a column from an underlying view table. If the column is not defined in the view, and if the NOT NULL column does not have a default defined, then inserts into it are not allowed. This is not the case if the NOT NULL column is defined in the view and given a value through the insert statement. DDC pp. 31-34, Restrictions for Modifying Data Through Views.

53. Which of the following are true of SQL Server default objects?

a. You must unbind a default from a column before dropping the default.

b. You must unbind a default from a user-defined datatype before dropping the default.

c. You must unbind a default from a user-defined datatype before dropping the user-defined datatype.

d. You must unbind a default from a column before dropping the column's table.

Answer: ab

- Your response: [None] (Incorrect)

Section: 8. Using Views, Defaults and Rules

Only choices a and b are correct. You must unbind a default from a column or user-defined datatype before you can drop it. Defaults are unbound from objects with the sp_unbindefault system stored procedure. Although a user-defined datatype cannot be dropped if existing tables reference it, you can drop a user-defined datatype that has a default bound to it. You do not have to unbind a default from a column before dropping the table that contains the column. TSQL p. 155, DROP DEFAULT Statement.

54. You create a rule with the following SQL statement:

   CREATE RULE lnamerule AS @name < 'B'

Which of the following is true?

a. This rule cannot be bound to any column since it is not stated correctly.

b. This rule cannot be bound to a column with an integer datatype.

c. The rule can be bound to an integer column but will be ignored when inserting a row.

d. The rule can be bound to an integer column but will cause an error when inserting a row.

Answer: d

- Your response: [None] (Incorrect)

Section: 8. Using Views, Defaults and Rules

Choice d is correct. Rules must be compatible with the datatypes of columns to which they are bound. You can bind a rule to a column even if the datatypes are incompatible. An error will be returned if you try to insert or update the value of the column with the erroneous rule assigned to it. TSQL p. 63, CREATE RULE Statement.

55. Which of the following will cause a SQL Server stored procedure to stop executing and return control back to the calling procedure?

a. RETURN

b. BREAK

c. HALT

d. STOP

Answer: a

- Your response: [None] (Incorrect)

Section: 9. Programmability

Choice a is correct. The RETURN statement is used to cause a stored procedure to stop executing and return control to the calling procedure. You can also use RETURN to send status codes to the calling procedure. When using these return codes you must have called the procedure using the form EXECUTE @returnstatus=procedurename. TSQL pp. 29-30, Control-of-Flow Language.

56. Which of the following SQL statements will define a cursor that holds all of the rows from the customer table?

a. DECLARE CURSOR curcustomer AS select * from customer

b. DECLARE curcustomer CURSOR FOR select * from customer

c. OPEN CURSOR curcustomer AS select * from customer

d. OPEN CURSOR curcustomer FOR select * from customer

Answer: b

- Your response: [None] (Incorrect)

Section: 9. Programmability

Choice b is correct. Before opening and using a cursor you must define it with the DECLARE statement. As with most object, table, and column names, cursors must conform to the rules for identifiers. You can use the SCROLL keyword when declaring a cursor to make the cursor scrollable (dynamic) and you can use the READ ONLY and UPDATE keywords to restrict which columns are updatable. TSQL pp. 93-94, Cursors.

57. Consider this first line of a batch that creates a stored procedure:

   CREATE PROCEDURE procproductsales

   @prodid varchar(10) = '%' , @totalsales money OUTPUT

Which of the following statements are true?

a. '%' is the default value for the @prodid parameter

b. '%' will be returned to the calling procedure as @prodid

c. a value for the @totalsales parameter must be supplied by a calling procedure

d. any value assigned to @totalsales by the procproductsales will be available to the calling procedure

Answer: ad

- Your response: [None] (Incorrect)

Section: 9. Programmability

Only choices a and d are correct. There are two special types of stored procedure parameters - defaults defined with an equal sign and output parameters defined with the OUTPUT keyword. You can use wildcards with parameter defaults if you intend to use the LIKE keyword in the procedure. Although an OUTPUT parameter must be dimensioned and included in a stored procedure call, a value does not have to be supplied for it (since it is meant to be an output variable anyway). TSQL p. 53, CREATE PROCEDURE Statement.

58. Your network has several SQL Servers. Which of the following SQL statements could be used from the Accounting Server to call the sp_who system stored procedure on the Sales server?

a. EXECUTE Sales.master.dbo.sp_who

b. REMOTE Sales.master.dbo.sp_who

c. EXECUTE REMOTE Sales.master.dbo.sp_who

d. REMOTE EXECUTE Sales.master.dbo.sp_who

Answer: a

- Your response: [None] (Incorrect)

Section: 9. Programmability

Choice a is correct. SQL Server supports remote servers on a network whose stored procedures can be accessed from other servers without requiring explicit logins. Remote servers work in pairs and are configured with the sp_addserver system procedure. Once remote servers are set up, the user of a local server can call procedures on the remote server with the EXECUTE statement by adding the server qualifier to the beginning of the procedure identifier. AC pp. 277-284, Remote Servers and Users; TSQL pp. 174-179, EXECUTE Statement.

59. Which of the following can be used to configure SQL Server so that it will login to the Windows NT Mail client when SQL Server boots?

a. SQL Service Manager

b. SQL Server Setup

c. SQL Mail Manager

d. SQL MAPI

Answer: b

- Your response: [None] (Incorrect)

Section: 9. Programmability

Choice b is correct. SQL Server Setup is used to make SQL Server log into the Windows NT Mail client during SQL Server boot up. The SQL Service Manager is only used to start and stop SQL Server. MAPI is the Microsoft Windows Messaging Application Programming Interface that allows SQL Server to send mail messages. AC pp. 607-612, Setting Up a SQLMail Client.

60. You are currently logged into the master database. Which of the following EXECUTE statements will change the database context to pubs?

a. EXEC USE 'pubs'

b. EXEC (USE 'pubs')

c. EXECUTE 'USE pubs'

d. EXECUTE ('USE pubs')

Answer: d

- Your response: [None] (Incorrect)

Section: 9. Programmability

Choice d is correct. Transact-SQL statements can be constructed and executed at run time with the EXECUTE statement. Only a string value is allowed although it can be a concatenated string. No other words or characters are allowed within the parentheses except for those that will be used to build the string, and string functions are not allowed. TSQL p. 177, Using EXECUTE with a Character String.

61. Which statement will remove a trigger named 'mytrigger' from the database?

a. DELETE FROM sysprocedures WHERE object = mytrigger

b. DELETE FROM sysprocedures WHERE object = 'mytrigger'

c. DROP TRIGGER mytrigger

d. DROP OBJECT 'mytrigger'

Answer: c

- Your response: [None] (Incorrect)

Section: 10. Triggers

Choice c is correct. Any database object that is created with the CREATE statement is normally removed from the database with the DROP statement. This includes databases, defaults, indexes, stored procedures, rules, tables, triggers, and views. TSQL p. 161, DROP TRIGGER Statement.

62. Which of the following statements is not allowed in a trigger?

a. UPDATE

b. SELECT INTO

c. WHILE

d. BEGIN

Answer: b

- Your response: [None] (Incorrect)

Section: 10. Triggers

Choice b is correct. Generally any statement that creates a database object or drops one is not allowed in a trigger. SELECT INTO is not allowed since it would create a table. Statements like ALTER, TRUNCATE, GRANT and REVOKE are also not allowed. Control-of-flow statements like WHILE, IF, and BEGIN are necessary to implement the logic necessary for complex trigger routines. TSQL p. 82, CREATE TRIGGER Statement.

63. Your company uses a table created with the following statement:

   CREATE TABLE widgets (mfgname char(30), mfgvalue smallint, mfgdate date)

When writing a trigger, which of the following will contain the value that has just been overwritten in the mfgvalue column by an UPDATE statement?

a. updated.mfgvalue

b. inserted.mfgvalue

c. deleted.mfgvalue

d. none of the above

Answer: c

- Your response: [None] (Incorrect)

Section: 10. Triggers

Only choice c is correct. The virtual tables inserted and deleted are used inside triggers to interrogate values that have been replaced or inserted. The data modification can be rolled back if an error is detected, or if a business rule is violated. For an UPDATE to a table the replaced value is put into the deleted table and the new value is put into the inserted table. TSQL pp. 83-84, Inserted and Deleted Tables.

64. Normally triggers are ignored during bulk copy operations. How can you enforce data integrity when using bulk copy operations?

a. set the select into/bulkcopy database option to true

b. set the select into/bulkcopy database option to false

c. run queries or stored procedures against the table after the bulk copy has completed

d. use the IF UPDATED clause at the beginning of the CREATE TRIGGER statement

Answer: c

- Your response: [None] (Incorrect)

Section: 10. Triggers

Choice c is correct. Changing the select into/bulkcopy database option will not change the fact that SQL Server does not observe triggers during bulk copy operations. The only way to enforce business rules built into triggers is to run queries or stored procedures against the trigger tables after the bulk copy operation is completed. For example you could run the query UPDATE pubs..titles SET title = title. Although this query would not change any of the data in the table, it would cause an update trigger to fire and check every row in the titles table. AC pp. 318-319, Copying Tables with Defaults, Rules, and/or Triggers.

65. Triggers use two tables called inserted and deleted. How are these tables applied in triggers?

a. The inserted table is used during UPDATE and DELETE operations.

b. The deleted table is used during UPDATE and DELETE operations.

c. The inserted table is used during INSERT and DELETE operations.

d. The deleted table is used during INSERT and DELETE operations.

Answer: b

- Your response: [None] (Incorrect)

Section: 10. Triggers

Only choice b is correct. The virtual tables inserted and deleted are used in triggers to enable programmatic validation of UPDATE, INSERT, and DELETE operations. Modified rows are placed into the inserted table during INSERT and UPDATE operations, and modified rows are placed into the deleted table during DELETE and UPDATE operations. These tables can be examined in trigger code and the data modification can be rolled-back on a row-by-row basis or modified on a column-by-column basis. TSQL pp. 83-84, Inserted and Deleted Tables.

66. Which global variable can be used inside of a trigger to find out how many rows have been modified?

a. @COUNT

b. @ROWNUMBER

c. @@ROWS

d. @@ROWCOUNT

Answer: d

- Your response: [None] (Incorrect)

Section: 10. Triggers

Choice d is correct. There may be times when you need to know how many rows were modified during a data modification operation. You can use the @@ROWCOUNT global variable inside a trigger to return the number of rows that have been modified. You should check this value at the beginning of the trigger since the execution of some SQL statements inside a trigger will reset the value. TSQL p. 84, Inserted and Deleted Tables.

67. Which of the following replication tables always resides on the publication server?

a. syspublications

b. MSlast_job_info

c. MSjob_commands

d. MSjobs

Answer: a

- Your response: [None] (Incorrect)

Section: 11. Replication

Choice a is correct. All publication servers use the syspublications and syssubscriptions tables. The MSlast_job_info table resides on the subscription server and tracks the last successful transaction replicated. MSjob_commands and MSjobs reside on the distribution database (which can be but is not always located on the publication server). TSQL pp. 616-617, System Tables-Replication Tables.

68. The msjob_commands table contains a record for each replication transaction in SQL Server. Which database contains the MSjob_commands table?

a. the publication database

b. the subscription database

c. the distribution database

d. the master database

Answer: c

- Your response: [None] (Incorrect)

Section: 11. Replication

Choice c is correct. Transactions move from the publication databases to the MSjobs table on the distribution database before being sent to the subscription databases. MSjob_commands maintains a record for each command associated with a transaction in the MSjobs table. MSjob_commands is used only on a distribution server (which can also be the publication server). Most replication tables reside in the msdb database. TSQL pp. 616-617, System Tables-Replication Tables.

69. Your company has a SQL server at corporate headquarters in Los Angeles where all data from regional sales offices is rolled up using replication. Which type of replication scenario best illustrates your enterprise?

a. Central Publisher

b. Central Subscriber

c. Publishing Subscriber

d. Distribution Subscriber

Answer: b

- Your response: [None] (Incorrect)

Section: 11. Replication

Choice b is correct. In a Central Publisher scenario (the most common scenario) data would be replicated from a single server to more than one subscription server. Transactions move from the publication database to the msjob_commands table in the distribution database where they are queued for further distribution. AC p. 425, Replication Scenarios.

70. Which of the following applies to the Central Publisher replication scenario?

a. destination tables are horizontally partitioned and contain subscriber determinant columns in their primary key

b. subscription servers will normally treat replicated data as read-only

c. more than one subscription server replicates information into a common destination table

d. subscription servers replicate rows for each of their locations

Answer: b

- Your response: [None] (Incorrect)

Section: 11. Replication

Choice b is correct. In the Central Publisher scenario, destination tables are not necessarily horizontally partitioned, and there is no need for them to contain subscriber determinant columns in their keys. Subscription servers typically treat replicated data as read-only, since any updates would not normally be mirrored on the publication server. Subscription servers do not replicate data, publication servers do. AC p. 422, Replication Scenarios.

71. Your company has a subscription server in Los Angeles that contains two databases. The Sales database in Los Angeles subscribes to an orders table on a database located on the Phoenix publication server. The Los Angeles Accounting database (located on the same server as the Sales database) needs data from many of the same rows in the same Phoenix orders table. How can this be accomplished?

a. Both databases can subscribe to the same article.

b. Both databases can subscribe to the same publication.

c. You can create a trigger on the Sales orders table that updates the Accounting orders table.

d. The desired result cannot be accomplished.

Answer: c

- Your response: [None] (Incorrect)

Section: 11. Replication

Choice c is correct. Two databases on the same server cannot subscribe to the same article, or to the same publication. However you can process data in whatever manner you wish once it is replicated on the subscription server. Therefore, a trigger would accomplish the desired result. AC pp. 427-429, Frequently Asked Questions.

72. You manage a SQL Server enterprise with a Central Publisher scenario. Although most of your subscription servers are receiving replicated data without any problems, one particular subscription server is not receiving ANY updates. Which of the following components should you attempt to troubleshoot first?

a. the publication database

b. the publication server

c. the distribution task

d. the log reader task

Answer: c

- Your response: [None] (Incorrect)

Section: 11. Replication

Choice c is correct. Transactions marked for replication are moved from the publication database to the MSjobs table of the distribution database using the log reader task. If at least one subscription service is operating properly in a Central Publisher enterprise you can assume that the log reader task is operating correctly. It is the job of the distribution task to move jobs from the distribution database to individual subscription servers. AC pp. 508-517, Troubleshooting.

73. Which of the following is NOT a component of the SQL Server Distributed Management Framework (SQL-DMF)?

a. alerting and notification features

b. Transact-SQL commands

c. Messaging Application Programming Interface (MAPI)

d. SQL Distributed Management Objects.

Answer: c

- Your response: [None] (Incorrect)

Section: 12. Application Development and ODS

Choice c is correct. The SQL Server Distributed Management Framework encompasses all of the functionality provided by the SQL Distributed Management Objects. This includes alerting and notification as well as all other features accessible through Transact-SQL or the SQL Server Enterprise Manager application. AC p. 23, Introducing the SQL Distributed Management Framework.

74. The DBPROCESS structure contains information needed by SQL Server to handle DB-Library function calls. Which of the following must be provided to the connection call in order to obtain a DBPROCESS structure?

a. a value returned by the dblogin() function

b. a value returned by the DBSETLUSER() function

c. a value returned by the DBSETLPWD() function

d. a value returned by the DBSETLAPP() function

Answer: a

- Your response: [None] (Incorrect)

Section: 12. Application Development and ODS

Choice a is correct. Whether using DB-Library with either C or Visual Basic the initiating call sequence is the same. First a login variable or structure is defined in memory. The user name, password, and application name are all written to this structure. Finally, the login information is passed to the SqlOpen function (Visual Basic) or dbopen function (C) to establish the connection (and the DBPROCESS structure in C). C p. 12, Chapter 2, Programming with DB-Library for C.

75. The Open Database Connectivity (ODBC) API is a client-side interface that can be used with SQL Server. Which of the following can be used to assign an ODBC data source name to a SQL Server database?

a. Enterprise Manager

b. SQL Server Service Manager

c. ODBC DB-Library

d. ODBC Administrator

Answer: d

- Your response: [None] (Incorrect)

Section: 12. Application Development and ODS

Choice d is correct. The use of SQL Server as an ODBC data source requires that it be assigned a data source name through the ODBC Administrator applet that resides in the Windows Control Panel Application. You can use the ODBC Administrator to assign data source names to any DBMS for which there is an ODBC driver available. ODBC SQL Server Driver Reference (available in SQL Server Online Books), "ODBC SQL Server Setup".

76. Which of the following statements is true of SQLOLE?

a. SQLOLE allows multiple SQL Servers to be managed from a single user application.

b. SQLOLE allows gateway applications to be developed for non-relational DBMSs.

c. SQLOLE allows the creation of dynasets and snapshots.

d. SQLOLE provides a single client-side API that can be used with different vendors' database systems.

Answer: a

- Your response: [None] (Incorrect)

Section: 12. Application Development and ODS

Choice a is correct. SQLOLE is the name of the object library implemented by SQL Distributed Management Objects (SQL-DMO). It is a collection of objects, properties, and methods conforming to the Microsoft Component Object Model specification that allows SQL Server to act as an OLE Automation Server. This object collection greatly simplifies the process of developing an application that will allow an administrator to manage multiple SQL Servers simultaneously over a network. DMO Chapter 1, Getting Started.

77. What is the correct order of operations when using SQLOLE properties and methods with Visual C++ or Visual Basic as the Automation controller?

a. connect to the object, create the object, release the object

b. create the object, connect to the object, release the object

c. release the object, connect to the object, create the object

d. create the object, release the object, connect to the object

Answer: b

- Your response: [None] (Incorrect)

Section: 12. Application Development and ODS

Choice b is correct. SQLOLE provides the ability for OLE Automation Controllers such as Visual Basic and Visual C++ to manage SQL Server programmatically. In a typical code module for an OLE Automation controller a variable is dimensioned for the OLE object (SQL Server in this case). Then a connection is made with the SQLServer.Connect method. After operatSQL-Cert v. 6.5 Copyright (c) 1996-1997 Transcender Corporation.

Examination: SQL Implementation Exam A

1. The relational data model includes several types of data integrity. Which type of data integrity is ensured by the use of a primary key?

a. entity integrity

b. domain integrity

c. referential integrity

d. relational integrity

Answer: a

- Your response: [None] (Incorrect)

Section: 1. Data Modeling

Choice a is correct. A join operation usually links a primary key with a foreign key. The relationship between a primary key and a foreign key is used to enforce referential integrity. Enforcement of referential integrity requires that records in the "child" or related table always have an associated record in the "parent" or primary table. DDC p. 10, Data Integrity.

2. A patient in a hospital has three different doctors. Each of these doctors has several patients. Which type of relationship in the relational model would effectively represent this?

a. three one-to-many relationships

b. two many-to-one relationships

c. a single many-to-many relationship

d. at least two many-to-many relationships

Answer: c

- Your response: [None] (Incorrect)

Section: 1. Data Modeling

Choice c is correct. The relational model specifies that a single relationship exists between any two tables. That relationship can be one or zero-to-many, one-to-one, or one-to-many. When each entity from one table can have more than one associated row on a second table it is called a one-to-many relationship. When a one-to-many relationship also exists from the related table to the primary table, then a many-to-many relationship exists between the two. DDC pp. 10-11, Referential Integrity.

3. Which of the following properties are required of a column or set of columns in order for the column or set of columns to function as a primary key?

a. no nulls

b. a clustered index

c. a nonclustered index

d. there must be at least one foreign key on the same table

Answer: a

- Your response: [None] (Incorrect)

Section: 1. Data Modeling

Only choice a is correct. There cannot be any nullable columns in a primary key since this would make it possible for two rows to contain the same key. Although SQL Server will create a unique clustered or nonclustered index on a key designated as primary, neither of these is required to qualify the column set as a primary key. DDC pp. 18, PRIMARY KEY Constraints.

4. The sales database contains a customer table and an order table. For each order there is one and only one customer, and for each customer there can be zero or many orders. How should primary and foreign key fields be placed into the design of this database?

a. A primary key should be created for the customer_id field in the customer table and also for the customer_id field in the order table.

b. A primary key should be created for the order_id field in the customer table and also for the customer_id field in the order table.

c. A primary key should be created for the customer_id field in the customer table and a foreign key should be created for the customer_id field in the order table.

d. A primary key should be created for the customer_id field in the customer table and a foreign key should be created for the order_id field in the order table.

Answer: c

- Your response: [None] (Incorrect)

Section: 1. Data Modeling

Choice c is correct. Relationships between primary and foreign keys are essential components in the relational database model. A primary key column (or set of columns in a composite key) uniquely identifies each row in the primary or "parent" table. Foreign keys in related or "child" tables "inherit" the primary key of the "parent" or primary table. These relationships can then be used to enforce data integrity or create join conditions that simplify data retrieval. DDC pp. 10-11, Referential Integrity.

5. A physician may or may not have a pager number. How could you provide a column in the physician table to reflect the pager number for physicians who have one without requiring an entry for physicians that do not?

a. make the pager number column NOT NULL

b. place a UNIQUE index on the pager number column

c. place a CLUSTERED INDEX on the pager number column

d. assign a default to the pager number column

Answer: d

- Your response: [None] (Incorrect)

Section: 1. Data Modeling

Only choice d is correct. When a column is defined as NOT NULL, it is required to have a value assigned to it at all times. UNIQUE indexes do not allow nulls, nor do CLUSTERED INDEX columns. When a column is assigned a default, it is never required to have a value assigned to it since the default will always be inserted when no value is specified. DDC pp. 16-17, DEFAULT Constraints.

6. Which of the following types of table columns will prevent the entry of duplicate rows?

a. NULL

b. UNIQUE

c. PRIMARY KEY

d. FOREIGN KEY

Answer: bc

- Your response: [None] (Incorrect)

Section: 1. Data Modeling

Only choices b and c are correct. When nulls are allowed in a column it is possible to have duplicate rows, since there is no way to distinguish between two nulls. A UNIQUE index constraint will prevent duplicate values from being entered into the column as will a PRIMARY KEY constraint. A FOREIGN KEY constraint will only enforce domain integrity by insuring that only values available in the referenced table are inserted into the FOREIGN KEY column. DDC pp. 9-20, Data Integrity.

7. Which database contains the sysdevices table?

a. tempdb

b. msdb

c. master

d. all databases

Answer: c

- Your response: [None] (Incorrect)

Section: 2. System Databases and Tables

Choice c is correct. The sysdevices table resides only in the master database. It keeps track of the storage devices used by SQL Server. These storage devices are defined and configured using the DISK INIT and DISK RESIZE statements. Other tables that reside only in the master database are syslogins, sysmessages, and sysdatabases. TSQL p. 600, System Tables-System Catalog.

8. With master set as the current database you execute the statement CREATE TABLE ##products. Where is this table created?

a. in the pubs database

b. in the tempdb database

c. in the master database

d. in the system database

Answer: b

- Your response: [None] (Incorrect)

Section: 2. System Databases and Tables

Choice b is correct. Temporary tables are always created in the tempdb database regardless of which database is current when they are created. There are two types of temporary tables; local temporary tables are prefaced with a single pound sign (#), while global temporary tables are prefaced with a double pound sign (##). Local temporary tables are available only to the session that created them, while global temporary tables are available to all sessions. TSQL p. 73, CREATE TABLE statement.

9. Which of the following will return a list of the tables in the current database?

a. SELECT name FROM systables

b. SELECT name FROM sysobjects WHERE type = 'T'

c. sp_tablenames

d. none of the above

Answer: d

- Your response: [None] (Incorrect)

Section: 2. System Databases and Tables

Choice d is correct. There is no systables table or sp_tablenames stored procedure. To return a list of tables in the current database you can query the sysobjects table for object names where the type is 'U' not 'T'. This is a standard technique used by SQL Server programmers to quickly determine the tables in a database. TSQL p. 588, sysobjects table.

10. If you attempt to create a stored procedure using the name of an existing stored procedure in the database you will get an error. Therefore, when writing a script to create a stored procedure it is important to check for an existing stored procedure with the same name and drop it if it exists. Assuming that you are the database owner, which of the following SQL batches will drop a stored procedure named sp_myprocedure from the current database?

a. select * from sysprocedures where name = 'dbo.sp_myprocedure'

   if @@ROWCOUNT >= 1 then drop procedure dbo.sp_myprocedure

b. select * from sysobjects where name = object_name()

   if @@ROWCOUNT = 1 then drop procedure dbo.sp_myprocedure

c. if exists (select * from sysprocedures where id = object_id('dbo.sp_myprocedure'))

   delete procedure dbo.sp_myprocedure from sysobjects

d. if exists (select * from sysobjects where id = object_id('dbo.sp_myprocedure'))

   drop procedure dbo.sp_myprocedure

Answer: d

- Your response: [None] (Incorrect)

Section: 2. System Databases and Tables

Only choice d is correct. The object name system function always takes an object id number for its argument. The sysprocedures and sysobjects tables both contain the id number for stored procedures but only the sysobjects table contains the name. You can see many examples of how to use system tables by looking at the source code for system stored procedures such as sp_who or sp_spaceused. TSQL p. 574, System Tables-Database Catalog.

11. How many pages and extents are in a SQL Server allocation unit?

a. 2000 pages or 256 extents

b. 256 pages or 256 extents

c. 256 pages or 32 extents

d. 32 pages or 32 extents

Answer: c

- Your response: [None] (Incorrect)

Section: 2. System Databases and Tables

Choice c is correct. The basic unit of measure is a page that is 2KB. These pages are always allocated in groups of eight that are called extents. Thirty-two extents or two hundred and fifty-six pages make up an allocation unit. AC pp. 120-121, Before Creating Databases.

12. Which of the following CANNOT be issued when the database option 'trunc. log on chkpt.' is set to true?

a. DUMP TRANSACTION

b. DUMP DATABASE

c. DROP TABLE

d. DROP VIEW

Answer: a

- Your response: [None] (Incorrect)

Section: 3. Data Definition

Choice a is correct. The 'trunc. log on chkpt.' database option prevents the log from filling up by purging it at regular time intervals (60 seconds by default). This is useful when doing development work since you do not have to worry about space being used for data recovery. However, you would never use DUMP TRANSACTION with this option set since the transaction log would not provide a complete record of changes made to the data since the last DUMP DATABASE was issued. If you issue DUMP TRANSACTION with 'trunc. log on chkpt.' set, you will get an error message telling you that you must DUMP DATABASE first. TSQL p. 495, sp_dboption System Stored Procedure.

13. Which of the following are used together to allocate disk space exclusively for use by a transaction log?

a. CREATE DATABASE with the LOG ON clause

b. CREATE DATABASE with the TRAN DEVICE clause

c. ALTER DATABASE with the LOG ON clause

d. ALTER DATABASE and sp_logdevice

Answer: ad

- Your response: [None] (Incorrect)

Section: 3. Data Definition

Only choices a and d are correct. You can create space exclusively for use by a transaction log with the CREATE DATABASE statement using the LOG ON clause. You can increase the size of the transaction log device with the ALTER DATABASE statement. The transaction log must reside on a device separate from the database for it to be expanded. After issuing the ALTER DATABASE statement to expand a transaction log device you still must use the sp_logdevice statement so that the new space will be used by the transaction log. TSQL pp. 1-3, ALTER DATABASE.

14. Which of the following statements will display information about a constraint called 'salary1' that is attached to the table 'employee'?

a. sp_helpconstraint salary1

b. sp_helpconstraint employee

c. sp_help salary1

d. sp_helptext employee

Answer: b

- Your response: [None] (Incorrect)

Section: 3. Data Definition

Choice b is correct. The sp_helpconstraint procedure takes a table name as its argument. Sp_help only returns information on objects within a database (constraints are not considered high-level objects within a database). Sp_helptext returns the script that was used to create a rule, a default, or an unencrypted stored procedure, trigger, or view. It does not return the script that would be used to define a table. TSQL pp. 517-518, sp_help and sp_helpconstraint System Stored Procedures; p. 535, sp_helptext System Stored Procedure.

15. Which of the following restrictions apply to the ALTER TABLE statement?

a. You cannot use ALTER TABLE to remove a column from a table.

b. You cannot use ALTER TABLE to add a PRIMARY KEY constraint to a table.

c. You cannot use ALTER TABLE to add a NULL column to a table with existing data.

d. When adding a constraint to an existing table with the ALTER TABLE statement, existing data will not be checked against the new constraint.

Answer: a

- Your response: [None] (Incorrect)

Section: 3. Data Definition

Only choice a is correct. All types of constraints can be added to a table with the ALTER TABLE statement. You cannot use ALTER TABLE to add a NOT NULL column to a table with existing data, since values would then have to be determined for each existing row. Existing data is normally checked against constraints added with the ALTER TABLE statement. However, you can force existing data to be ignored by using the WITH NOCHECK clause. TSQL p. 10, ALTER TABLE Statement.

16. Which of the following arguments can be used when defining an IDENTITY constraint?

a. default

b. seed

c. distribution

d. increment

Answer: bd

- Your response: [None] (Incorrect)

Section: 3. Data Definition

Only choices b and d are correct. When you define an IDENTITY column with the CREATE TABLE or ALTER TABLE statements you can define a seed and increment value. The seed value specifies the number that will be assigned to the first row in the table (note that you can create an identity column on an existing table). The increment value specifies the value that will be added to the previous row for each subsequent row. These arguments are optional and are assigned a value of one by default. TSQL p. 5, ALTER TABLE Statement.

17. Which statements would you use to drop a CHECK constraint named 'stucheck' from a table called 'student'?

a. DROP CONSTRAINT stucheck

b. DROP CONSTRAINT ' stucheck ' FROM student

c. ALTER TABLE student DROP stucheck

d. DROP TABLE student

Answer: c

- Your response: [None] (Incorrect)

Section: 3. Data Definition

Choice c is correct. The DROP statement is used to remove objects from a database (constraints are not considered high-level objects within a database). You must add and remove constraints from the database (and tables) using the CREATE TABLE or ALTER TABLE statements. DDC p. 19, Dropping Constraints.

18. Which of the following is only used with a FOREIGN KEY constraint?

a. a PRIMARY KEY constraint

b. the REFERENCES clause

c. a CHECK constraint

d. sp_foreignkey

Answer: b

- Your response: [None] (Incorrect)

Section: 3. Data Definition

Choice b is correct. Although a set of columns referenced by a FOREIGN KEY constraint should have a PRIMARY KEY or UNIQUE constraint defined for it, neither is required. A CHECK constraint acts like a rule by limiting the values that can be entered into a column based on a list or LIKE expression. Sp_foreignkey was used in previous versions of SQL Server for documentation purposes but serves no real purpose in SQL Server 6.5. TSQL p. 7, ALTER TABLE Statement.

19. In your results you want to display the character string 'The name of this product is' immediately before the product name. Which of the following SQL SELECT statements could you use?

a. SELECT 'The name of this product is', prodname FROM products

b. SELECT 'The name of this product is ' & prodname FROM products

c. SELECT 'The name of this product is ' + prodname FROM products

d. SELECT (The name of this product is), prodname FROM products

Answer: ac

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Only choices a and c are correct. The SELECT statement can be used to display a result set containing any combination of SQL Server expressions. This includes not only columns from tables but constants and character strings as well. To return character strings you can either define them as a column to be returned or concatenate them with columns in the result set. DDC pp. 63-64, SELECT Syntax.

20. Which of the following TSQL functions will return a string of twenty-four asterisk characters?

a. SELECT '*' * 24

b. SELECT REPEAT('*') * 24

c. SELECT REPEAT('*', 24)

d. SELECT REPLICATE('*', 24)

Answer: d

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Choice d is correct. You can perform a variety of useful operations with SQL Server's built-in functions. The REPLICATE function takes two arguments and repeats a character expression the specified number of times. Other useful string functions include SUBSTRING, RTRIM, RIGHT, SPACE, and STRING. TSQL pp. 204-207, Functions.

21. The ASCII code value of the uppercase letter 'A' is 65. Which of the following will take as an argument the number 65 and return the letter 'A'?

a. SELECT ASCII(65)

b. SELECT ASCII 65

c. SELECT CHAR(65)

d. SELECT CHARINDEX(65)

Answer: c

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Choice c is correct. SQL Server provides an assortment of string, date, and mathematical functions for manipulating data and queries. The ASCII function takes a character argument surrounded by quotes and returns the corresponding ASCII value for that character. The CHAR function performs the reverse operation of accepting an ASCII number and returning the corresponding character. TSQL p. 204, Functions.

22. You want to display descriptive information about the price ranges of the products in your company's database. Which of the following SQL SELECT statements could you use?

a. SELECT title, 'Price Range' =

   IF price < 20 THEN 'Low Priced Item'

   IF price >= 20 THEN 'Higher Priced Item'

   FROM titles

b. SELECT title, 'Price Range' AS

   IF price < 20 THEN 'Low Priced Item'

   IF price >= 20 THEN 'Higher Priced Item'

   FROM titles

c. SELECT title, 'Price Range' =

   CASE

      WHEN price < 20 THEN 'Low Priced Item'

      WHEN price >= 20 THEN 'Higher Priced Item'

   END

   FROM titles

d. SELECT title, 'Price Range' AS

   CASE

      WHEN price < 20 THEN 'Low Priced Item'

      WHEN price >= 20 THEN 'Higher Priced Item'

   END

   FROM titles

Answer: c

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Choice c is correct. You can use the CASE statement to format query results and create derived columns based on values in other columns. You can assign any name you want to the derived column as long as that name is enclosed in single quotes. You can also use the CASE statement to replace result values with any other character string (e.g., replacing the product names with more descriptive information). DDC pp. 60-62, SELECT Syntax.

23. Consider the following SQL SELECT statement:

   SELECT prodname FROM products WHERE prodname LIKE '_s'

Which of the following product names will match the search criteria and be returned in the result set?

a. apples

b. oranges

c. _s

d. is

Answer: cd

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Only choices c and d are correct. The underscore wildcard character (_) indicates that only a single character in the indicated place combined with the other characters in the string will match. To match on any number of characters you would use the percentage sign (%). Individual characters and ranges of characters can be indicated for inclusion in the search by placing them in brackets ([ and ]). TSQL p. 687, Wildcard Characters.

24. Which of the following system functions will return the login name by which you are known to SQL Server across all of its databases?

a. DB_USER()

b. DB_ID()

c. USER_ID()

d. SUSER_NAME()

Answer: d

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Choice d is correct. SQL Server provides a variety of built-in functions to simplify various operations. In addition to string, date, and mathematical functions there are system functions that return information related to the various system tables. The USER_ID, USER_NAME, and SUSER_ID, SUSER_NAME system function pairs return either the user's database name and id or the SQL Server login name and id respectively. TSQL p. 210, Functions.

25. You are currently logged into the Pubs database. Which of the following SQL SELECT statements will return the identification number for the pubs database?

a. SELECT DB_NAME()

b. SELECT DB_ID(@@DBNAME='pubs')

c. SELECT DB_NAME('pubs', id)

d. SELECT DB_ID('pubs')

Answer: d

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Choice d is correct. The system functions built into SQL Server typically come in pairs. Examples are DB_ID, DB_NAME; OBJECT_ID, OBJECT_NAME; and USER_ID, USER_NAME. For these system functions, providing an identification number will return a name while providing a name will return a system identification number. If no parameter is provided you will receive the id or name of the current database or user. DDC pp. 154-155, Built-in Functions.

26. Which of the following SQL SELECT statements will return an error?

a. SELECT 'Sum' = SUM(totalsale) FROM productsales

b. SELECT "Sum" = SUM(totalsale) FROM productsales

c. SELECT 'This Company''s Sales' = SUM(totalsale) FROM productsales

d. SELECT 'This Company's Sales' = SUM(totalsale) FROM productsales

Answer: d

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Choice d is correct. You can place almost any character string in a column heading as long as it is enclosed with single or double quotes. However you must be careful about mixing single and double quotes. Quotes in column heading strings are always matched one following the other, and an unmatched quote in the wrong place may produce unwanted results. DDC pp. 62-63, Quoted Strings in Column Headings, and Character Strings and Quotation Marks.

27. What order is used to display columns when the SELECT * statement is used?

a. alphabetical order

b. binary order

c. the columns are displayed in the order they were created or added to the table

d. none of the above

Answer: c

- Your response: [None] (Incorrect)

Section: 4. Retrieving Data

Choice c is correct. Using the SELECT * syntax removes control over the order in which the columns are displayed since they will only be displayed in the order the were created. It is usually better in applications to define the columns to be returned since this insures a predictable ordering of the columns. SELECT * is most useful for designing and debugging. DDC p. 57 Choosing Columns: The Select List.

28. Which of the following SELECT statements is used to return the number of rows in a table?

a. SELECT ALL COUNT from tablename

b. SELECT ROWCOUNT from tablename

c. SELECT COUNT(*) from tablename

d. SELECT NUMROWS from tablename

Answer: c

- Your response: [None] (Incorrect)

Section: 5. Retrieving Data (Advanced Topics)

Choice c is correct. SQL Server aggregate functions include COUNT, AVG, MIN, MAX, and SUM. These functions, when used in SELECT statements, will return summary values about the table or other column set. COUNT is the only aggregate function used with the asterisk character to obtain a summary value. This is because it is the only aggregate function that does not use a specific column set. DDC p. 137, COUNT(*).

29. You want to show the salary of each employee arranged by department. At the end of each department grouping you want to display the total salaries for that department. Which of the following SQL SELECT statements could you use?

a. SELECT employeename, SUM(salary)

   FROM employee

   GROUP BY department

   COMPUTE SUM(salary) BY department

b. SELECT employeename, department, SUM(salary)

   FROM employee

   GROUP BY department

   ORDER BY department

c. SELECT employeename, department, salary

   FROM employee

   ORDER BY department

   COMPUTE SUM(salary) BY department

d. SELECT employeename, department, salary

   FROM employee

   ORDER BY department

   COMPUTE SUM(salary)

Answer: c

- Your response: [None] (Incorrect)

Section: 5. Retrieving Data (Advanced Topics)

Choice c is correct. The COMPUTE clauses (COMPUTE and COMPUTE BY) are used to provide aggregate summaries (SUM, AVG, MIN, MAX, and COUNT) on groups without using the GROUP BY clause. The COMPUTE clauses are always used with ORDER BY so that summary values will always be listed with the groups they represent. COMPUTE provides a single column set aggregate for the entire result set while COMPUTE BY provides aggregate values for each grouping. DDC pp. 146-153, The COMPUTE Clause.

30. You want to find the total amount of widgets sold by each region so far this year. Which of the following SQL SELECT statements could you use?

a. SELECT region, SUM(units)

   FROM productsales

   GROUP BY region

b. SELECT region, units

   FROM productsales

   GROUP BY SUM(units)

c. SELECT region

   FROM productsales

   GROUP BY SUM(units)

d. SELECT region, units

   FROM productsales

   GROUP BY region HAVING SUM(units)

Answer: a

- Your response: [None] (Incorrect)

Section: 5. Retrieving Data (Advanced Topics)

Choice a is correct. The GROUP BY clause is used most often with aggregate functions. It divides a result set based on a particular set of columns. When used with aggregate functions the GROUP BY clause returns a single value for each group. All columns in the select list must either be contained in an aggregate function or be listed in the GROUP BY clause. DDC pp. 139-144, The GROUP BY Clause.

31. Which of the following SQL SELECT statements will return all departments with more than 100 employees?

a. SELECT department

   FROM employees

   WHERE COUNT(*) > 100

b. SELECT department

   FROM employees

   HAVING COUNT(*) > 100

c. SELECT department

   FROM employees

   GROUP BY department

   HAVING COUNT(*) > 100

d. SELECT DISTINCT department

   FROM employees

   GROUP BY department

   WHERE COUNT(employees) > 100

Answer: c

- Your response: [None] (Incorrect)

Section: 5. Retrieving Data (Advanced Topics)

Choice c is correct. The HAVING clause is only appropriate when used with a GROUP BY clause. The HAVING clause restricts the results that are returned based on a condition related to the result set groupings. It works in much the same way that the WHERE clause restricts results returned based on a condition related to the columns in the select list. DDC pp. 144-146, The HAVING Clause.

32. You want to display all of the employees in your company. You want to provide a special indication for those who live in a city where a supplier is located. Which of the following outer-join queries could you use?

a. SELECT employeename, suppliername

   FROM employees, suppliers

   WHERE employees.city =* suppliers.city

b. SELECT employeename, suppliername

   FROM employees, suppliers

   WHERE employees.city *= suppliers.city

c. SELECT employeename, suppliername

   FROM employees, suppliers

   WHERE employees.city IN suppliers.city

d. SELECT e.employeename, s.suppliername

   FROM employees e, suppliers s

   WHERE s.city = e.city

Answer: b

- Your response: [None] (Incorrect)

Section: 5. Retrieving Data (Advanced Topics)

Choice b is correct. An outer join is used to return columns from one table even if they do not meet the join condition. The asterisk (*) is used with the equal sign to indicate which table will return columns that do not match. In this case the suppliername column will display a null value for non-matching rows and will display the supplier name for rows that match. DDC pp. 105-109, Using Joins to Create Multiple Queries.

33. In the pubs database the titleauthor table is used to define the many-to-many relationships between authors and books. Which of the following SQL SELECT statements will show which books (titles) have more than one author?

a. SELECT DISTINCT au_id, title_id

   FROM titleauthor

   WHERE title_id = title_id

   AND au_id <> au_id

b. SELECT DISTINCT title_id

   FROM titleauthor

   WHERE title_id(1) = title_id(2)

   AND au_id(1) <> au_id(2)

c. SELECT DISTINCT au_id, title_id

   FROM titleauthor t1, titleauthor t2

   WHERE t1.title_id = t2.title_id

   AND t1.au_id <> t2.au_id

d. SELECT DISTINCT t1.title_id, t2.title_id

   FROM titleauthor t1, titleauthor t2

   WHERE t1.title_id = t2.title_id

   AND t1.au_id <> t2.au_id

Answer: d

- Your response: [None] (Incorrect)

Section: 5. Retrieving Data (Advanced Topics)

Choice d is correct. The not-equal sign is usually only used with self-joins. In a self-join, a table is given at least two separate identities. Aliases are used to distinguish one version of the table from another. Once the table is defined as more than one virtual table it can be treated as such in SELECT statements. DDC p. 103, Using Joins to Create Multiple Queries.

34. You want to know which publishers sell books that cost more than $40.00. Which of the following SELECT statements could you use?

a. SELECT pub_name

   FROM publishers, titles

   WHERE pub_id IN

   (SELECT pub_id

   FROM titles

   WHERE price > 40)

b. SELECT pub_name

   FROM publishers, titles

   WHERE pub_id EXISTS

   (SELECT pub_id

   FROM titles

   WHERE price > 40)

c. SELECT pub_name

   FROM publishers

   WHERE pub_id IN

   (SELECT pub_id

   FROM titles

   WHERE price > 40)

d. SELECT pub_name

   FROM publishers

   WHERE pub_id =

   (SELECT pub_id

   FROM titles

   WHERE price > 40)

Answer: c

- Your response: [None] (Incorrect)

Section: 5. Retrieving Data (Advanced Topics)

Choice c is correct. You would not want to include the titles table in the FROM clause of this query without qualifying the pub_id columns in the subquery (SQL Server finds this statement to be ambiguous). The EXISTS keyword returns true or false and is not used for comparison operations. The equal sign operator cannot be used with a subquery that returns multiple row results. DDC pp. 111-133, Using Subqueries.

35. You have been using the identity column to create a product_id value for each of your company's products. It is determined that one of the product rows was accidentally deleted many weeks ago and that many products have been added to the table since then. How can you INSERT a row for the missing product and give it a product_id of 48?

a. You will have to move all rows with product ids greater than 47 into a temporary table, insert the new product id 48, and then re-insert all of the products in the temporary table.

b. You can insert product id number 48 directly into the table with a simple INSERT statement since there will not be a product with an id of 48 in the table.

c. You will have to use the SET statement with the IDENTITY INSERT option activated to re-insert product number 48.

d. You will have to recreate the products table and do a SELECT INTO operation in order to reclaim product id number 48.

Answer: c

- Your response: [None] (Incorrect)

Section: 6. Modifying Data

Choice c is correct. A value cannot be inserted directly into an identity column by default. If you accidentally delete a row, or if you want to fill a gap that exists for some other reason, you should use the SET statement with the IDENTITY INSERT option turned on temporarily. This is an important consideration when deciding whether or not to use an identity column in your table. There are some cases where the use of an identity column may not be ideal. TSQL p. 75, CREATE TABLE Statement.

36. The type column in the titles table of the pubs database has a default value of 'UNDECIDED' assigned to it. Which of the following INSERT statements will cause the default value to be entered into the type column?

a. INSERT titles (title_id, title, DEFAULT, pubdate)

   VALUES ('RH6320', 'SQL Server Basics', 'UNDECIDED', 'June 12 1995')

b. INSERT titles (title_id, title, type, pubdate)

   VALUES ('RH6320', 'SQL Server Basics', DEFAULT, 'June 12 1995')

c. INSERT titles (title_id, title, type, pubdate)

   VALUES ('RH6320', 'SQL Server Basics', 'June 12 1995')

d. INSERT titles (title_id, title, pubdate)

   VALUES ('RH6320', 'SQL Server Basics', 'June 12 1995')

Answer: bd

- Your response: [None] (Incorrect)

Section: 6. Modifying Data

Only choices b and d are correct. Defaults are used to insure that there will always be some value other than NULL inserted into a column when a value is not specified. There must always be the same number of columns in the column list as there are in the values list. Defaults are defined for a column when the table is created or modified with the ALTER statement. TSQL pp. 224-225, INSERT Statement.

37. The product table in your database contains a price column that allows NULL values. Which of the following UPDATE statements will set the value of the price column to NULL for all of the products whose product id is between 480 and 500?

a. UPDATE products SET price = 0

   WHERE product_id >= 480 AND product_id <= 500

b. UPDATE products SET price AS NULL

   WHERE product_id > 480 AND product_id < 500

c. UPDATE products SET price = NULL

   WHERE product_id >= 480 AND product_id <= 500.

d. You cannot use an UPDATE statement to set columns to NULL.

Answer: c

- Your response: [None] (Incorrect)

Section: 6. Modifying Data

Choice c is correct. Zero and null are not the same in SQL Server and do not equate to each other. You can set columns to NULL during an UPDATE operation with the statement SET columnname = NULL. The WHERE statement in an UPDATE can include any search condition allowed for a SELECT statement. TSQL p. 656, UPDATE Statement.

38. Which of the following restrictions apply to SQL Transfer Manager?

a. A source server can be a Windows NT, OS/2, or non-Microsoft SQL Server, but the destination server can only be Windows NT SQL Server.

b. A source server can only be Windows NT SQL Server, but the destination server can be a Windows NT, OS/2, or non-Microsoft SQL server.

c. Both the destination and source server can be a Windows NT, OS/2, or non-Microsoft SQL server.

d. Both the destination and source server can only be Windows NT SQL Server

Answer: a

- Your response: [None] (Incorrect)

Section: 6. Modifying Data

Choice a is correct. Also note that if you transfer data from SQL Server 4.21 or 6.5, you cannot include SQL Server 6.5-specific datatypes. Also, SQL Server 6.5 features such as identity columns and declarative referential integrity will not be transferred. AC pp. 297-299, Using SQL Transfer Manager.

39. The Orders table contains a field for the employee id of the person who entered the order into the system. This field shares a many-to-one relationship with the employees table which contains a first and last name for each employee who has an employee id. Which of the following DELETE statements will remove all rows from the orders table that have been placed by John Adams?

a. DELETE * FROM orders

   WHERE orders.employee_id = employee.employee_id

   AND employee.firstname = 'John'

   AND employee.lastname = 'Adams'

b. DELETE orders, employee

   WHERE orders.employee_id = employee.employee_id

   AND employee.firstname = 'John'

   AND employee.lastname = 'Adams'

c. DELETE FROM orders

   WHERE orders.employee_id = employee.employee_id

   AND employee.firstname = 'John'

   AND employee.lastname = 'Adams'

d. DELETE FROM orders

   FROM orders, employee

   WHERE employee.firstname = 'John'

   AND employee.lastname = 'Adams'

   AND orders.employee_id = employee.employee_id

Answer: d

- Your response: [None] (Incorrect)

Section: 6. Modifying Data

Choice d is correct. When you want to delete rows in a table based on values in related tables you must include two sets of table names. The first set lists the table from which rows will be deleted and the second set lists the tables in the WHERE clause. All tables referenced in the WHERE clause must be included in the second table list. TSQL pp. 141-142, DELETE Statement.

40. Which of the following will NOT delete all rows from the orders table?

a. DELETE * FROM orders

b. DELETE orders

c. TRUNCATE TABLE orders

d. DELETE ALL FROM orders

Answer: ad

- Your response: [None] (Incorrect)

Section: 6. Modifying Data

Only choices a and d are correct. The FROM keyword is optional for the DELETE statement. The DELETE statement never contains a column list or ALL keyword since all columns are deleted. You cannot delete values in individual columns using the DELETE statement. TSQL pp. 140-142.

41. Consider the following batch:

   CREATE TABLE mytable (myid IDENTITY(1,1), myname char(10))

   GO

   INSERT mytable VALUES('Ron')

   INSERT mytable VALUES('Rob')

An input file called table_in will be used for bcp which looks like this:

<tab>3<tab>James

<tab>4<tab>Joe

<tab>5<tab>Jim

Which of the following bulk copy commands will put values into the table correctly?

a. bcp mytable in table_in /i /Usa /P /e error_out

b. bcp mytable in table_in /i[3,1] /Usa /P /e error_out error_out

c. bcp mytable in table_in /e /Usa /P /e

d. bcp mytable in table_in /E /Usa /P /e error_out

Answer: d

- Your response: [None] (Incorrect)

Section: 6. Modifying Data

Only choice d is correct. Use the /E flag to indicate that identity columns exist in the data to be imported. If you want SQL Server to generate identity column values, you should leave non-identity columns out of the file. AC p. 307, Using the Bulk Copy Program.

42. The IGNORE_DUP_ROW and ALLOW_DUP_ROW options are used with the CREATE INDEX statement to specify what will happen when you try to create a non-unique clustered index. What will happen if you attempt to create a clustered index on a table with duplicate rows without specifying either the IGNORE_DUP_ROW or ALLOW_DUP_ROW option?

a. a clustered index will be created automatically and a message will be returned

b. a clustered index will be created and no message will be returned

c. a clustered index will be created with duplicate rows omitted

d. the CREATE INDEX statement will fail

Answer: d

- Your response: [None] (Incorrect)

Section: 7. Indexes

Choice d is correct. You cannot create a non-unique clustered index without using the ALLOW_DUP_ROW option in the CREATE INDEX statement. If you use the IGNORE_DUP_ROW option the index will be created but duplicate rows will be deleted and an error message will be returned. If there are duplicated values in the indexed columns and neither of these options are specified the CREATE INDEX statement will fail. TSQL pp. 48-49, CREATE INDEX Statement.

43. The SORTED_DATA option is used with the CREATE INDEX statement to prevent a sort from being implemented automatically when creating an index. With which type of index is the SORTED_DATA option used?

a. a composite index

b. a primary key

c. a clustered index

d. a nonclustered index

Answer: c

- Your response: [None] (Incorrect)

Section: 7. Indexes

Choice c is correct. The SORTED_DATA option is used only when creating clustered indexes. If the creation of a clustered index requires that data is re-copied to put it in order, nonclustered indexes will have to be rebuilt. For this reason it may be desirable to check whether the data is properly sorted before creating a clustered index. TSQL p. 48, CREATE INDEX Statement.

44. Which of the following statements will successfully create a composite index on the products table?

a. CREATE INDEX compind ON products AS prodid, prodname

b. CREATE INDEX compind ON products(prodid, prodname)

c. CREATE INDEX compind AS products.prodid, products.prodname

d. CREATE INDEX compind ON products.prodid, products.prodname

Answer: b

- Your response: [None] (Incorrect)

Section: 7. Indexes

Choice b is correct. Composite indexes are created in the same way that single-column indexes are. There can be as many as 16 columns in a composite index but all columns in the composite index must be from the same table. The sum total of all characters and values used in an index cannot exceed 256 bytes. TSQL pp. 44-46, CREATE INDEX Statement.

45. Which of the following types of columns or queries would most likely benefit from the use of a clustered index?

a. columns with a limited number of unique values

b. columns with a large number of unique values

c. queries that return small results sets

d. queries that use an ORDER BY clause

Answer: a

- Your response: [None] (Incorrect)

Section: 7. Indexes

Choice a is correct. A clustered index causes data to be stored in the exact order specified in the CREATE INDEX statement. Clustered indexes are most likely to be used by the optimizer for columns that contain a limited (but not tiny) number of distinct values. Clustered indexes are usually beneficial for queries that return a range of values using comparison operators and for queries that return large results sets. DDC p. 176, Clustered Indexes.

46. Which of the following types of columns or queries would most likely benefit from the use of a nonclustered index?

a. columns that contain a limited number of unique values

b. queries that do not return ordered results sets

c. queries that return small results sets

d. queries that return large results sets

Answer: c

- Your response: [None] (Incorrect)

Section: 7. Indexes

Only choice c is correct. A nonclustered index causes data to be stored randomly but with corresponding index pages that provide pointers to the location of each row on the disk. Nonclustered indexes are most beneficial for columns that contain a large number of distinct values. They are also beneficial when used with queries that return small results sets or that use an ORDER BY clause. DDC p. 177, Nonclustered Indexes

47. A ten-thousand row table in your database is never updated, but is used heavily for queries. All search conditions focus on two particular columns in the table that are usually searched on together as a group. Neither of these columns has a significant number of unique values. Which of the following indexing schemes would probably provide the best query performance?

a. a nonclustered composite index

b. a clustered composite index

c. a nonclustered index on the first column and a clustered index on the second column

d. a clustered index on the first column and a nonclustered index on the second column

Answer: b

- Your response: [None] (Incorrect)

Section: 7. Indexes

Choice b is correct. Composite indexes are most useful when the columns in the index are searched on as a group. An index is more likely to be used by SQL Server if its columns contain a greater percentage of distinctive values. Although a clustered index is good for a column with a medium percentage of unique values, it will not be used on a large table if the column only contains a few unique values. A nonclustered index will usually perform better than a clustered index on a column with a high percentage of unique values. DDC pp. 173-179, Optimizing Indexes.

48. The authors table has a single clustered index named authorindex defined for it. Which of the following statements will force authorindex to be used by the database query optimizer?

a. SELECT date, custid, phone FROM authors (INDEX = 1)

b. SELECT date, custid, phone FROM authors INDEX 1

c. SELECT date, custid, phone FROM authors INDEX = 0

d. SELECT date, custid, phone FROM authors INDEX ON 0

Answer: a

- Your response: [None] (Incorrect)

Section: 7. Indexes

Choice a is correct. Optimizer hints are placed in parentheses after the FROM clause and can indicate a specific locking method as well as a specific index to be used by the query engine. You can specify that an available clustered index is to be used by placing index = 1 in the parentheses, and you can specify that no index is to be used (a table scan is to be used) by placing index = 0 in the parentheses. TSQL p. 269, SELECT Statement.

49. Which of the following is used with the CREATE VIEW statement to ensure that modifications committed through the view will remain visible?

a. WITH CHECK OPTION

b. CHECKOPTION ONLY

c. WITH FUTUREONLY

d. you cannot make data modifications to a table through a view

Answer: a

- Your response: [None] (Incorrect)

Section: 8. Using Views, Defaults and Rules

Choice a is correct. Normally it is possible to INSERT or UPDATE a row into a table through a view that does not meet the criteria used to define the view. For example if your view only displays product rows where the price is greater than $20, users can still insert rows to that table through your view for products that cost less than $20. When the WITH CHECK OPTION clause is used to create a view it insures that no inserts or updates can be made to the table that do not conform to the criteria used to create the view. TSQL p. 89, CREATE VIEW Statement.

50. Which of the following can be attached directly to a view using the CREATE VIEW statement?

a. indexes

b. defaults

c. triggers

d. none of the above

Answer: d

- Your response: [None] (Incorrect)

Section: 8. Using Views, Defaults and Rules

Choice d is correct. You cannot associate a rule, default, trigger or index with a view when creating it. You also cannot create temporary views or create views with temporary tables. Although it is not required that you include column names when you create a view, you must include column names when any of the view columns are derived or when two tables in the view have a column with the same name. DDC pp. 30-31, Creating Views.

51. Which of the following can be defined with a view?

a. a subset of the columns in a table

b. a combination of data from more than one table

c. a combination of data from more than one view

d. a combination of data from more than one view and more than one table

Answer: abcd

- Your response: [None] (Incorrect)

Section: 8. Using Views, Defaults and Rules

Choices a, b, c and d are correct. Views are created with SQL Server SELECT statements and virtually any data set that can be defined with a SELECT statement can define a view. There are restrictions on data modification through views, but views provide good security mechanisms since permission can be granted to a view without granting permission to data in tables that define the view. DDC pp. 29-30, Planning Views.

52. Which of the following inserts and updates are NOT allowed through views?

a. updating view columns derived from computed table columns

b. inserting rows to views derived from computed table columns

c. updating tables with NOT NULL columns for which there are no defaults

d. inserting rows to tables with NOT NULL columns for which there are no defaults

Answer: ab

- Your response: [None] (Incorrect)

Section: 8. Using Views, Defaults and Rules

Only choices a and b are correct. Inserts and updates to computed columns or columns derived from built-in functions are not allowed. This is because there could be no way for SQL Server to determine the values to be placed in the columns from which the computed column was derived. Another restriction arises if null values are not allowed in a column from an underlying view table. If the column is not defined in the view, and if the NOT NULL column does not have a default defined, then inserts into it are not allowed. This is not the case if the NOT NULL column is defined in the view and given a value through the insert statement. DDC pp. 31-34, Restrictions for Modifying Data Through Views.

53. Which of the following are true of SQL Server default objects?

a. You must unbind a default from a column before dropping the default.

b. You must unbind a default from a user-defined datatype before dropping the default.

c. You must unbind a default from a user-defined datatype before dropping the user-defined datatype.

d. You must unbind a default from a column before dropping the column's table.

Answer: ab

- Your response: [None] (Incorrect)

Section: 8. Using Views, Defaults and Rules

Only choices a and b are correct. You must unbind a default from a column or user-defined datatype before you can drop it. Defaults are unbound from objects with the sp_unbindefault system stored procedure. Although a user-defined datatype cannot be dropped if existing tables reference it, you can drop a user-defined datatype that has a default bound to it. You do not have to unbind a default from a column before dropping the table that contains the column. TSQL p. 155, DROP DEFAULT Statement.

54. You create a rule with the following SQL statement:

   CREATE RULE lnamerule AS @name < 'B'

Which of the following is true?

a. This rule cannot be bound to any column since it is not stated correctly.

b. This rule cannot be bound to a column with an integer datatype.

c. The rule can be bound to an integer column but will be ignored when inserting a row.

d. The rule can be bound to an integer column but will cause an error when inserting a row.

Answer: d

- Your response: [None] (Incorrect)

Section: 8. Using Views, Defaults and Rules

Choice d is correct. Rules must be compatible with the datatypes of columns to which they are bound. You can bind a rule to a column even if the datatypes are incompatible. An error will be returned if you try to insert or update the value of the column with the erroneous rule assigned to it. TSQL p. 63, CREATE RULE Statement.

55. Which of the following will cause a SQL Server stored procedure to stop executing and return control back to the calling procedure?

a. RETURN

b. BREAK

c. HALT

d. STOP

Answer: a

- Your response: [None] (Incorrect)

Section: 9. Programmability

Choice a is correct. The RETURN statement is used to cause a stored procedure to stop executing and return control to the calling procedure. You can also use RETURN to send status codes to the calling procedure. When using these return codes you must have called the procedure using the form EXECUTE @returnstatus=procedurename. TSQL pp. 29-30, Control-of-Flow Language.

56. Which of the following SQL statements will define a cursor that holds all of the rows from the customer table?

a. DECLARE CURSOR curcustomer AS select * from customer

b. DECLARE curcustomer CURSOR FOR select * from customer

c. OPEN CURSOR curcustomer AS select * from customer

d. OPEN CURSOR curcustomer FOR select * from customer

Answer: b

- Your response: [None] (Incorrect)

Section: 9. Programmability

Choice b is correct. Before opening and using a cursor you must define it with the DECLARE statement. As with most object, table, and column names, cursors must conform to the rules for identifiers. You can use the SCROLL keyword when declaring a cursor to make the cursor scrollable (dynamic) and you can use the READ ONLY and UPDATE keywords to restrict which columns are updatable. TSQL pp. 93-94, Cursors.

57. Consider this first line of a batch that creates a stored procedure:

   CREATE PROCEDURE procproductsales

   @prodid varchar(10) = '%' , @totalsales money OUTPUT

Which of the following statements are true?

a. '%' is the default value for the @prodid parameter

b. '%' will be returned to the calling procedure as @prodid

c. a value for the @totalsales parameter must be supplied by a calling procedure

d. any value assigned to @totalsales by the procproductsales will be available to the calling procedure

Answer: ad

- Your response: [None] (Incorrect)

Section: 9. Programmability

Only choices a and d are correct. There are two special types of stored procedure parameters - defaults defined with an equal sign and output parameters defined with the OUTPUT keyword. You can use wildcards with parameter defaults if you intend to use the LIKE keyword in the procedure. Although an OUTPUT parameter must be dimensioned and included in a stored procedure call, a value does not have to be supplied for it (since it is meant to be an output variable anyway). TSQL p. 53, CREATE PROCEDURE Statement.

58. Your network has several SQL Servers. Which of the following SQL statements could be used from the Accounting Server to call the sp_who system stored procedure on the Sales server?

a. EXECUTE Sales.master.dbo.sp_who

b. REMOTE Sales.master.dbo.sp_who

c. EXECUTE REMOTE Sales.master.dbo.sp_who

d. REMOTE EXECUTE Sales.master.dbo.sp_who

Answer: a

- Your response: [None] (Incorrect)

Section: 9. Programmability

Choice a is correct. SQL Server supports remote servers on a network whose stored procedures can be accessed from other servers without requiring explicit logins. Remote servers work in pairs and are configured with the sp_addserver system procedure. Once remote servers are set up, the user of a local server can call procedures on the remote server with the EXECUTE statement by adding the server qualifier to the beginning of the procedure identifier. AC pp. 277-284, Remote Servers and Users; TSQL pp. 174-179, EXECUTE Statement.

59. Which of the following can be used to configure SQL Server so that it will login to the Windows NT Mail client when SQL Server boots?

a. SQL Service Manager

b. SQL Server Setup

c. SQL Mail Manager

d. SQL MAPI

Answer: b

- Your response: [None] (Incorrect)

Section: 9. Programmability

Choice b is correct. SQL Server Setup is used to make SQL Server log into the Windows NT Mail client during SQL Server boot up. The SQL Service Manager is only used to start and stop SQL Server. MAPI is the Microsoft Windows Messaging Application Programming Interface that allows SQL Server to send mail messages. AC pp. 607-612, Setting Up a SQLMail Client.

60. You are currently logged into the master database. Which of the following EXECUTE statements will change the database context to pubs?

a. EXEC USE 'pubs'

b. EXEC (USE 'pubs')

c. EXECUTE 'USE pubs'

d. EXECUTE ('USE pubs')

Answer: d

- Your response: [None] (Incorrect)

Section: 9. Programmability

Choice d is correct. Transact-SQL statements can be constructed and executed at run time with the EXECUTE statement. Only a string value is allowed although it can be a concatenated string. No other words or characters are allowed within the parentheses except for those that will be used to build the string, and string functions are not allowed. TSQL p. 177, Using EXECUTE with a Character String.

61. Which statement will remove a trigger named 'mytrigger' from the database?

a. DELETE FROM sysprocedures WHERE object = mytrigger

b. DELETE FROM sysprocedures WHERE object = 'mytrigger'

c. DROP TRIGGER mytrigger

d. DROP OBJECT 'mytrigger'

Answer: c

- Your response: [None] (Incorrect)

Section: 10. Triggers

Choice c is correct. Any database object that is created with the CREATE statement is normally removed from the database with the DROP statement. This includes databases, defaults, indexes, stored procedures, rules, tables, triggers, and views. TSQL p. 161, DROP TRIGGER Statement.

62. Which of the following statements is not allowed in a trigger?

a. UPDATE

b. SELECT INTO

c. WHILE

d. BEGIN

Answer: b

- Your response: [None] (Incorrect)

Section: 10. Triggers

Choice b is correct. Generally any statement that creates a database object or drops one is not allowed in a trigger. SELECT INTO is not allowed since it would create a table. Statements like ALTER, TRUNCATE, GRANT and REVOKE are also not allowed. Control-of-flow statements like WHILE, IF, and BEGIN are necessary to implement the logic necessary for complex trigger routines. TSQL p. 82, CREATE TRIGGER Statement.

63. Your company uses a table created with the following statement:

   CREATE TABLE widgets (mfgname char(30), mfgvalue smallint, mfgdate date)

When writing a trigger, which of the following will contain the value that has just been overwritten in the mfgvalue column by an UPDATE statement?

a. updated.mfgvalue

b. inserted.mfgvalue

c. deleted.mfgvalue

d. none of the above

Answer: c

- Your response: [None] (Incorrect)

Section: 10. Triggers

Only choice c is correct. The virtual tables inserted and deleted are used inside triggers to interrogate values that have been replaced or inserted. The data modification can be rolled back if an error is detected, or if a business rule is violated. For an UPDATE to a table the replaced value is put into the deleted table and the new value is put into the inserted table. TSQL pp. 83-84, Inserted and Deleted Tables.

64. Normally triggers are ignored during bulk copy operations. How can you enforce data integrity when using bulk copy operations?

a. set the select into/bulkcopy database option to true

b. set the select into/bulkcopy database option to false

c. run queries or stored procedures against the table after the bulk copy has completed

d. use the IF UPDATED clause at the beginning of the CREATE TRIGGER statement

Answer: c

- Your response: [None] (Incorrect)

Section: 10. Triggers

Choice c is correct. Changing the select into/bulkcopy database option will not change the fact that SQL Server does not observe triggers during bulk copy operations. The only way to enforce business rules built into triggers is to run queries or stored procedures against the trigger tables after the bulk copy operation is completed. For example you could run the query UPDATE pubs..titles SET title = title. Although this query would not change any of the data in the table, it would cause an update trigger to fire and check every row in the titles table. AC pp. 318-319, Copying Tables with Defaults, Rules, and/or Triggers.

65. Triggers use two tables called inserted and deleted. How are these tables applied in triggers?

a. The inserted table is used during UPDATE and DELETE operations.

b. The deleted table is used during UPDATE and DELETE operations.

c. The inserted table is used during INSERT and DELETE operations.

d. The deleted table is used during INSERT and DELETE operations.

Answer: b

- Your response: [None] (Incorrect)

Section: 10. Triggers

Only choice b is correct. The virtual tables inserted and deleted are used in triggers to enable programmatic validation of UPDATE, INSERT, and DELETE operations. Modified rows are placed into the inserted table during INSERT and UPDATE operations, and modified rows are placed into the deleted table during DELETE and UPDATE operations. These tables can be examined in trigger code and the data modification can be rolled-back on a row-by-row basis or modified on a column-by-column basis. TSQL pp. 83-84, Inserted and Deleted Tables.

66. Which global variable can be used inside of a trigger to find out how many rows have been modified?

a. @COUNT

b. @ROWNUMBER

c. @@ROWS

d. @@ROWCOUNT

Answer: d

- Your response: [None] (Incorrect)

Section: 10. Triggers

Choice d is correct. There may be times when you need to know how many rows were modified during a data modification operation. You can use the @@ROWCOUNT global variable inside a trigger to return the number of rows that have been modified. You should check this value at the beginning of the trigger since the execution of some SQL statements inside a trigger will reset the value. TSQL p. 84, Inserted and Deleted Tables.

67. Which of the following replication tables always resides on the publication server?

a. syspublications

b. MSlast_job_info

c. MSjob_commands

d. MSjobs

Answer: a

- Your response: [None] (Incorrect)

Section: 11. Replication

Choice a is correct. All publication servers use the syspublications and syssubscriptions tables. The MSlast_job_info table resides on the subscription server and tracks the last successful transaction replicated. MSjob_commands and MSjobs reside on the distribution database (which can be but is not always located on the publication server). TSQL pp. 616-617, System Tables-Replication Tables.

68. The msjob_commands table contains a record for each replication transaction in SQL Server. Which database contains the MSjob_commands table?

a. the publication database

b. the subscription database

c. the distribution database

d. the master database

Answer: c

- Your response: [None] (Incorrect)

Section: 11. Replication

Choice c is correct. Transactions move from the publication databases to the MSjobs table on the distribution database before being sent to the subscription databases. MSjob_commands maintains a record for each command associated with a transaction in the MSjobs table. MSjob_commands is used only on a distribution server (which can also be the publication server). Most replication tables reside in the msdb database. TSQL pp. 616-617, System Tables-Replication Tables.

69. Your company has a SQL server at corporate headquarters in Los Angeles where all data from regional sales offices is rolled up using replication. Which type of replication scenario best illustrates your enterprise?

a. Central Publisher

b. Central Subscriber

c. Publishing Subscriber

d. Distribution Subscriber

Answer: b

- Your response: [None] (Incorrect)

Section: 11. Replication

Choice b is correct. In a Central Publisher scenario (the most common scenario) data would be replicated from a single server to more than one subscription server. Transactions move from the publication database to the msjob_commands table in the distribution database where they are queued for further distribution. AC p. 425, Replication Scenarios.

70. Which of the following applies to the Central Publisher replication scenario?

a. destination tables are horizontally partitioned and contain subscriber determinant columns in their primary key

b. subscription servers will normally treat replicated data as read-only

c. more than one subscription server replicates information into a common destination table

d. subscription servers replicate rows for each of their locations

Answer: b

- Your response: [None] (Incorrect)

Section: 11. Replication

Choice b is correct. In the Central Publisher scenario, destination tables are not necessarily horizontally partitioned, and there is no need for them to contain subscriber determinant columns in their keys. Subscription servers typically treat replicated data as read-only, since any updates would not normally be mirrored on the publication server. Subscription servers do not replicate data, publication servers do. AC p. 422, Replication Scenarios.

71. Your company has a subscription server in Los Angeles that contains two databases. The Sales database in Los Angeles subscribes to an orders table on a database located on the Phoenix publication server. The Los Angeles Accounting database (located on the same server as the Sales database) needs data from many of the same rows in the same Phoenix orders table. How can this be accomplished?

a. Both databases can subscribe to the same article.

b. Both databases can subscribe to the same publication.

c. You can create a trigger on the Sales orders table that updates the Accounting orders table.

d. The desired result cannot be accomplished.

Answer: c

- Your response: [None] (Incorrect)

Section: 11. Replication

Choice c is correct. Two databases on the same server cannot subscribe to the same article, or to the same publication. However you can process data in whatever manner you wish once it is replicated on the subscription server. Therefore, a trigger would accomplish the desired result. AC pp. 427-429, Frequently Asked Questions.

72. You manage a SQL Server enterprise with a Central Publisher scenario. Although most of your subscription servers are receiving replicated data without any problems, one particular subscription server is not receiving ANY updates. Which of the following components should you attempt to troubleshoot first?

a. the publication database

b. the publication server

c. the distribution task

d. the log reader task

Answer: c

- Your response: [None] (Incorrect)

Section: 11. Replication

Choice c is correct. Transactions marked for replication are moved from the publication database to the MSjobs table of the distribution database using the log reader task. If at least one subscription service is operating properly in a Central Publisher enterprise you can assume that the log reader task is operating correctly. It is the job of the distribution task to move jobs from the distribution database to individual subscription servers. AC pp. 508-517, Troubleshooting.

73. Which of the following is NOT a component of the SQL Server Distributed Management Framework (SQL-DMF)?

a. alerting and notification features

b. Transact-SQL commands

c. Messaging Application Programming Interface (MAPI)

d. SQL Distributed Management Objects.

Answer: c

- Your response: [None] (Incorrect)

Section: 12. Application Development and ODS

Choice c is correct. The SQL Server Distributed Management Framework encompasses all of the functionality provided by the SQL Distributed Management Objects. This includes alerting and notification as well as all other features accessible through Transact-SQL or the SQL Server Enterprise Manager application. AC p. 23, Introducing the SQL Distributed Management Framework.

74. The DBPROCESS structure contains information needed by SQL Server to handle DB-Library function calls. Which of the following must be provided to the connection call in order to obtain a DBPROCESS structure?

a. a value returned by the dblogin() function

b. a value returned by the DBSETLUSER() function

c. a value returned by the DBSETLPWD() function

d. a value returned by the DBSETLAPP() function

Answer: a

- Your response: [None] (Incorrect)

Section: 12. Application Development and ODS

Choice a is correct. Whether using DB-Library with either C or Visual Basic the initiating call sequence is the same. First a login variable or structure is defined in memory. The user name, password, and application name are all written to this structure. Finally, the login information is passed to the SqlOpen function (Visual Basic) or dbopen function (C) to establish the connection (and the DBPROCESS structure in C). C p. 12, Chapter 2, Programming with DB-Library for C.

75. The Open Database Connectivity (ODBC) API is a client-side interface that can be used with SQL Server. Which of the following can be used to assign an ODBC data source name to a SQL Server database?

a. Enterprise Manager

b. SQL Server Service Manager

c. ODBC DB-Library

d. ODBC Administrator

Answer: d

- Your response: [None] (Incorrect)

Section: 12. Application Development and ODS

Choice d is correct. The use of SQL Server as an ODBC data source requires that it be assigned a data source name through the ODBC Administrator applet that resides in the Windows Control Panel Application. You can use the ODBC Administrator to assign data source names to any DBMS for which there is an ODBC driver available. ODBC SQL Server Driver Reference (available in SQL Server Online Books), "ODBC SQL Server Setup".

76. Which of the following statements is true of SQLOLE?

a. SQLOLE allows multiple SQL Servers to be managed from a single user application.

b. SQLOLE allows gateway applications to be developed for non-relational DBMSs.

c. SQLOLE allows the creation of dynasets and snapshots.

d. SQLOLE provides a single client-side API that can be used with different vendors' database systems.

Answer: a

- Your response: [None] (Incorrect)

Section: 12. Application Development and ODS

Choice a is correct. SQLOLE is the name of the object library implemented by SQL Distributed Management Objects (SQL-DMO). It is a collection of objects, properties, and methods conforming to the Microsoft Component Object Model specification that allows SQL Server to act as an OLE Automation Server. This object collection greatly simplifies the process of developing an application that will allow an administrator to manage multiple SQL Servers simultaneously over a network. DMO Chapter 1, Getting Started.

77. What is the correct order of operations when using SQLOLE properties and methods with Visual C++ or Visual Basic as the Automation controller?

a. connect to the object, create the object, release the object

b. create the object, connect to the object, release the object

c. release the object, connect to the object, create the object

d. create the object, release the object, connect to the object

Answer: b

- Your response: [None] (Incorrect)

Section: 12. Application Development and ODS

Choice b is correct. SQLOLE provides the ability for OLE Automation Controllers such as Visual Basic and Visual C++ to manage SQL Server programmatically. In a typical code module for an OLE Automation controller a variable is dimensioned for the OLE object (SQL Server in this case). Then a connection is made with the SQLServer.Connect method. After operations on the object are completed its resources are released. DMO, Chapter 2, Using SQL-DMO Objects.

78. Which of the following applications could be developed using Open Data Services (ODS)?

a. a Windows NT Service

b. a remote stored procedure application

c. a gateway application

d. all of the above

Answer: d

- Your response: [None] (Incorrect)

Section: 12. Application Development and ODS

Choice d is correct. Once an ODS application is installed, it can be configured as a Windows NT Service that will respond to commands such as net start and net stop. Extended stored procedures and gateway applications are the most important uses of ODS. Gateway applications allow another relational or non-relational DBMS to appear as a SQL Server DBMS to client applications. This typically involves the development of remote stored procedures that are called using client APIs such as DB-Library and ODBC. ODS, Chapter 1, Types of Open Data Services Applications.

 ions on the object are completed its resources are released. DMO, Chapter 2, Using SQL-DMO Objects.

78. Which of the following applications could be developed using Open Data Services (ODS)?

a. a Windows NT Service

b. a remote stored procedure application

c. a gateway application

d. all of the above

Answer: d

- Your response: [None] (Incorrect)

Section: 12. Application Development and ODS

Choice d is correct. Once an ODS application is installed, it can be configured as a Windows NT Service that will respond to commands such as net start and net stop. Extended stored procedures and gateway applications are the most important uses of ODS. Gateway applications allow another relational or non-relational DBMS to appear as a SQL Server DBMS to client applications. This typically involves the development of remote stored procedures that are called using client APIs such as DB-Library and ODBC. ODS, Chapter 1, Types of Open Data Services Applications.