1. What is the best way to map a One to Many relationship between two entities?

A. Create one table that stores data for both entities.
B. Create one table that has a foreign key to the other table.
C. Create two tables that have foreign keys to each other.
D. Create three tables: one to store the data from entity A, one to store the data from entity B, and one to link the two.


2. Which of the following is NOT an advantage of a stored procedure?

A. Stored Procedures are pre-compiled, so they run faster than a query.
B. Stored Procedures can put an entire sequence in one place, so they can be easily edited.
C. Stored Procedures were invented by Microsoft, so they run better on SQL Server than other database systems.
D. Stored Procedures are reusable.


3. In SQL, data should be normalized. One of the accepted rules for normalization is that the data must be at least …

A. …3-NF.
B. …1-NF
C. …N-NF
D. …Boyce-Codd


4. What is ADO?

A. Active Directory Objects are the fundamental building blocks of SQL Server.
B. Archived Documents Online are the help files for SQL Server.
C. ActiveX Data Objects are one set of several tools used to connect to SQL Server.
D. Anti-Differing Organization is a programming philosophy that optimizes ASP, ASP.NET, and other programming languages that can connect to SQL Server.


5. Which of the following adds a row to a table?

A. ADD ROW (city_name VALUE ‘San Francisco’, state_code VALUE ‘CA’) to city_table
B. CREATE_ROW in city_table SET city_name = ‘San Francisco’AND state_code = ‘CA’
C. INSERT INTO city_table city_name = ‘San Francisco’, state_code = ‘CA’)
D. INSERT INTO city_table (city_name, state_code) VALUES (‘San Francisco’, ‘CA’)


6. Which of the following is the correct syntax for a DELETE statement?

A. DELETE(city_table, state_code = ‘CA’)
B. DELETE FROM city_table WHERE state_code = ‘CA’
C. DELETE WHERE state_code = ‘CA’ FROM city_table
D. All of the above are acceptable statements.


7. Which of the following statements is TRUE?

A. SQL Server is required to be installed on the same computer as the program that is connecting to it.
B. You cannot run SQL Server on a server that has IIS or another web server running on it.
C. ASP web pages can only connect to Access databases. In order to connect to SQL Server, you need ASP.NET.
D. None of the above are true.


8. What is the difference between VARCHAR and nVARCHAR?

A. Both types are for storing variable length character fields. VARCHAR stores each character in one byte and nVARCHAR stores each character in 2 bytes.
B. VARCHAR is for storing variable length characters, nVARCHAR is for storing variable length strings.
C. Both types are for storing pointers to character variables, but VARCHAR is old. nVARCHAR is version two of the datatype.
D. There is no difference, both are of the same type.


9. What is an Index used for in Relational Databases?

A. An Index is used to reference data in another table, thus making joins and lookups faster when multiple tables are involved.
B. An Index identifies one or more restricted values for a field and limits what values a user can enter.
C. An Index is a list of values that correspond to fields, helping the database sort and retrieve records faster.
D. . An Index is a tool for resizing and storing tables that SQL Server utilizes when the physical size of the data stored in a table reaches beyond the theoretical capacity.


10. In general terms, what is the definition of a primary key?

A. Any field that is deemed most important by the designer.
B. The first row in a table that holds the names of the other fields.
C. A unique identifier that, if searched for, returns one and only one row.
D. All of the above are definitions of a primary key.


11. In the following statement, what is returned?

SELECT city_name, count(*) FROM city_table
LEFT OUTER JOIN store_table
ON city_table.city_id = store_table.city_id GROUP BY city_name

A. A list of cities, with a count of the number of stores in that city, but only the cities where there are stores in the database.
B. A list of stores, with a count of the number of cities that have stores of that name, but only stores where there is more than one store with the same name.
C. A list of cities, with a count of the number of stores in each city, but some of the cities may have zero stores.
D. A count of cities and stores, matching the stores with the city that they are in.


12. What is normalization?

A. The process of organizing tables to minimize redundancy.
B. The process of removing unusual data from one or more tables.
C. The process of adjusting the physical space of one or more tables to match expected parameters.
D. None of the above describe normalization.


13. Which of the table or tables below is in 3rd Normal Form?

In this situation, a project has a name and up to three employees who work on that project. Each employee is in a rate category and has an hourly rate. So, a project has up to three employees and each one can have a different rate category and a different hourly rate.

A. Create TABLE project_employee(
Project_number INT,
Project_name VARCHAR(200),
Emp_id_1 INT,
Emp_name_1 VARCHAR(50),
Emp_id_2 INT,
Emp_name_2 VARCHAR(50),
Emp_id_3 INT,
Emp_name_3 VARCHAR(50),
Emp_rate_category_1 VARCHAR(10),
Emp_rate_category_2 VARCHAR(10),
Emp_rate_category_3 VARCHAR(10),
Emp_hourly_rate_1 MONEY,
Emp_hourly_rate_2 MONEY,
Emp_hourly_rate_3 MONEY)
B. Create TABLE project_employee(
Project_number INT NOT NULL PRIMARY KEY,
Project_name VARCHAR(200),
Emp_id INT NOT NULL PRIMARY KEY,
Emp_name VARCHAR(50),
Emp_rate_category VARCHAR(10),
Emp_hourly_rate MONEY)
C. Create TABLE project(
Project_number INT NOT NULL PRIMARY KEY,
Project_name VARCHAR(200)
)
Create TABLE employee (
Emp_id INT NOT NULL PRIMARY KEY,
Emp_name VARCHAR(50),
Emp_rate_category VARCHAR(10),
Emp_hourly_rate MONEY
)
Create TABLE employee_project (
Project_number INT NOT NULL PRIMARY KEY,
Emp_id INT NOT NULL PRIMARY KEY
)
D. Create TABLE project (
Project_number INT NOT NULL PRIMARY KEY,
Project_name VARCHAR(200)
)
Create TABLE employee (
Emp_id INT NOT NULL PRIMARY KEY,
Emp_name VARCHAR(50),
Emp_rate_category VARCHAR(10)
)
Create TABLE employee_project (
Project_number INT NOT NULL PRIMARY KEY,
Emp_id INT NOT NULL PRIMARY KEY
)
Create TABLE rate (
Emp_rate_category VARCHAR(10) NOT NULL PRIMARY KEY,
Emp_hourly_rate MONEY
)


14. What does XSP mean?

A. A stored procedure optimized for SQL Server XP.
B. An ActiveX object that is part of the SQL Server core system.
C. A DLL or other external object referenced by SQL Server as an extended stored procedure.
D. None of the above describe XSP.


15. Using ANSI SQL, which of the following will give you the total number of rows in the table?

A. SELECT count_rows(*) FROM city_table
B. SELECT count(*) FROM city_table
C. SELECT sum(*) FROM city_table
D. All of the above will return the number of rows in the table.


16. The business rules for an application require that the STATE field only contain “NY”,”CA”,”IL”,”FL”, and “TX”. Which of the following is the correct syntax for achieving this rule?

A. ALTER TABLE address SET state IN (“NY”,”CA”,”IL”,”FL”,“TX”)
B. ALTER TABLE address ADD REQUIREMENT state IN (“NY”,”CA”,”IL”,”FL”,“TX”)
C. ALTER TABLE address ADD CONSTRAINT state_ck REQUIRE (state = (“NY” OR ”CA” OR ”IL” OR ”FL” OR “TX”))
D. ALTER TABLE address ADD CONSTRAINT state_ck CHECK (state IN(“NY”,”CA”,”IL”,”FL”,“TX”))


17. What is the best way to map a Many to Many relationship between two entities?

A. Create one table that stores the data for both entities.
B. Create one table that has a foreign key to the other table.
C. Create two tables that have foreign keys to each other.
D. Create three tables: one to store the data for entity A, one to store the data for entity B, and one to link the two.


18. Which of the following can NOT be used in a stored procedure?

A. Cursor
B. Table
C. Integer
D. All of the above are usable.


19. In a customer’s office, they have two types of users: office staff and managers. Office staff need to enter data and run reports, but cannot see information about employee salaries. Managers can do everything office staff can do, but also can create employees and see their salaries. All users have database access software and a web browser. Which of the following choices is the best one for implementing this requirement?

A. Create two database roles: OFFICE_STAFF and MANAGERS. Grant access to all tables to MANAGERS and grant access to every table but EMPLOYEE to OFFICE_STAFF.
B. Create one role, USER, grant access for USER to all tables, and build an OFFICE_STAFF website separate from the MANAGER website.
C. Use the default SA for all database access and don’t teach the OFFICE_STAFF to use the MANAGER data.
D. Create two databases, OFFICE_STAFF and MANAGER, and link the shared tables between the two databases.


20. What does the @@ symbol signify?

A. @@ is what precedes any variable in T-SQL.
B. @@ is the comment character in T-SQL. None of the rest of the line is compiled.
C. @@ signifies a T-SQL reserved variable or constant.
D. @@ does not mean anything in T-SQL.


21. What is it referred to when a primary key is made up of more than one field?

A. Multi-field Primary Key.
B. Compound Primary Key.
C. Variable Primary Key.
D. Dual Primary Key.


22. In ANSI SQL, what does the CASE statement do?

A. Allows for optional columns or values to be selected, as SELECT CASE WHEN state_code = ‘CA’ THEN ‘CALIFORNIA’ FROM State_table.
B. Allows for optional filters in the WHERE clause, as in: SELECT state_name FROM State_table WHERE CASE (state_region = ‘North East’), CASE ((state_region = ‘South West’).
C. Allows for optional tables to be used in the FROM clause, as in: SELECT state_name FROM CASE(WHERE customer_id = 11, State_table A), CASE(WHERE customer_id = 24, State_table B)
D. Nothing, the CASE statement isn’t ANSI SQL compliant.


23. What is the difference between IN and EXISTS?

A. There is no difference between IN and EXISTS.
B. IN is the correct syntax for a sub-query. EXISTS is not ANSI SQL syntax.
C. With the returned results of a sub-query, IN checks if any results were returned at all, where EXISTS verifies that the searched value exists in the result set.
D. With the returned results of a sub-query, IN verifies that the searched value is in the result set, where EXISTS checks if any results were returned at all.


24. What is DDL?

A. Data Documentation Language – the language for writing database documentation, invented by MIT researchers.
B. Data Definition Language - the language used to create tables & views, and define the structure of a database.
C. Database Directory Lookup – the syntax used to seek people or objects from a Microsoft Active Directory installation.
D. Dynamic Data Library – a set of code used for a specific purpose. DDL’s were the precursor to Active X Objects.


25. Which of the following database options is the best use of a cursor if it isn’t updating any records?

A. NO UPDATE
B. READ ONLY
C. UPDATE FALSE
D. IMMUTABLE


26. Which symbol precedes every user-defined variable in T-SQL?

A. ?, as in SET ?foo = 7
B. %, as in SET %foo = 7
C. &, as in SET &foo = 7
D. @, as in SET @foo = 7


27. Which of the following words or phrases will cause a stored procedure to cease further execution?

A. CEASE
B. EXIT
C. END
D. RETURN


28. Of the following choices, which one is the best choice for a password for the SA account?

A. “a86M13oG”, because it has both letters and numbers, and isn’t a word in the dictionary.
B. “pass_word”, because it is easy to remember and it has a non-character in the middle.
C. “xyzcompany”, because XYZ is your company name and it is something that the other administrators will remember.
D. Nothing (blank), because it is the default, it doesn’t require any additional typing, and because no one would think to have a blank password.


29. In general terms, what is a foreign key?

A. A field in a table that refers to the primary key in another table.
B. An index that is in a language other than English.
C. Any word or phrase that is fundamental to understanding another database.
D. All of the above can be Foreign Keys.


30. Which of the following is the correct syntax for using a CURSOR?

A. DECLARE my_cur CURSOR
SET my_cur = “SELECT emp_id, emp_name FROM employee WHERE is_active = 1”
EXECUTE my_cur
B. DECLARE my_cur CURSOR FOR
SELECT emp_id, emp_name FROM employee WHERE is_active = 1
OPEN my_cur
C. DECLARE CURSOR USING
SELECT emp_id, emp_name FROM employee WHERE is_active = 1
USE my_cur
D. DECLARE my_cur CURSOR
SELECT emp_id, emp_name INTO my_cur FROM employee WHERE is_active


31. Which of the following is the ANSI-established correct way to store a person’s name?

A. One field, called NAME or PERSON_NAME or something like that, because a person only has one name.
B. Two fields, FIRST_NAME and LAST_NAME, so you can search on one or the other. Additional parts of the name, like MS. Or JR. can be added in the fields efficiently.
C. Five fields: SALUTATION, FIRST_NAME, LAST_NAME, LEGAL_SUFFIX, PROFESSIONAL_SUFFIX, so that you can store Dr. J. Michael Adams, III, PhD effectively.
D. There are many ways to store a person’s name, and you have to determine the right way that matches your system and your design.


32. How many stored procedures can you create for one database?

A. Theoretically, it is unlimited, but it will become quickly unmanageable if you try to make too many.
B. 1024, because that is the MAX_EXTENTS of the SYS_PROCEDURES table.
C. None per database. All stored procedures are common to every database.
D. It is unlimited, but once you go beyond 1024, SQL Server has a Stored Procedure organizing tool.


33. @client_name varchar(127), @ytd_sales int

Which of the following key words or phrases, added after @ytd_sales will indicate that the Year to Date sales figure needs to be passed back to the calling program?


A. RETURN
B. BY REF
C. RESULT
D. OUTPUT


34. When trapping an error in a stored procedure and reporting it to the caller, what function should be used?

A. ERROR()
B. RAISERROR()
C. EXCEPTION()
D. SP_ERR()


35. Which of the following has the correct syntax for a SELECT statement?

A. SELECT foo WHERE bar > 7 FROM my_table
B. SELECT FROM my_table (FOO)
C. SELECT foo FROM my_table WHERE bar > 7
D. All of the above are acceptable statements.


36. To get linked data from two different tables to appear in the same query, you should use …

A. … the LINK command.
B. … a JOIN statement.
C. … a UNITE function.
D. … a COMBINE control.


37. What word or phrase is used to make a Stored Procedure?

A. CREATE PROCEDURE
B. PROCEDURE BEGIN
C. PROCEDURE:
D. START PROCEDURE

38. How do you change existing data in a table?

A. with an UPDATE statement.
B. with a CHANGE DATA statement.
C. with an ALTER TABLE function.
D. with a MODIFY command.