1. What will the following SQL script return?

/*
SELECT *
FROM
EMPLOYEES
WHERE TYPE = 'CASHIER'
*/

A. It will return all rows and columns from the EMPLOYEES table.
B. It will return the CASHIER column for all rows in the EMPLOYEES TABLE.
C. It will return all rows and columns from the EMPLOYEES table where the TYPE column = 'CASHIER'
D. It will return nothing.

2. Which of the following commands will cause a name column query set to be listed in reverse alphabetical order?

A. SORT BY NAME
B. ORDER BY PIN Z-A
C. ORDER BY NAME REVERSE
D. ORDER BY NAME DESC

3. If you want to select all rows from the STUDENTS table where the ID number is any number from 100-500, which of the following WHERE conditions would you use?

A. WHERE ID FROM 100 TO 500
B. WHERE ID IN (100,500)
C. WHERE ID BETWEEN 100 AND 500
D. WHERE ID RANGE 100-500

4. Which of the following relational operators is used to test for inequality?

A. *=
B. !=
C. less than-greater than signs (can't put them here; the text following them disappeared)
D. -=

5. The symbol * can be used in the SELECT CLAUSE of a SELECT statement in TSQL to return ...

A. ...all rows in a table.
B. ...all columns in a table.
C. ...only the rows indicated in the WHERE CLAUSE.
D. ...only the columns indicated in the FROM CLAUSE.

6. The statement below can be best described as what type of TSQL statement?

SELECT my_value INTO @ my_new_table from my_old_table

A. DDL
B. DML
C. DXL
D. DDL

7. Which of the following TSQL statements will restrict the returned result set to 100 rows?

A. LIMIT 100
B. RETURN 100
C. SET ROWCOUNT 100
D. SET LIMIT 100

8. Consider the following table:

MY_TABLE:
ID COLUMN1 COLUMN2
---- ------- -------
101 AAAA 1111
101 AAAA 1111
103 CCCC 2222
104 DDDD 1212

Which of the following statements will return a different result than the others?

A. SELECT * FROM MY_TABLE
B. select * from my_table
C. Select ID,COLUMN1,COLUMN2 from My_Table
D. SELECT Distinct * FROM My_Table

9. Which of the following is a correct implementation of the GROUP BY CLAUSE?

A. SELECT COUNT(*), AGE, OCCUPATION FROM MY_TABLE GROUP BY AGE
B. SELECT COUNT(*), AGE FROM MY_TABLE GROUP BY AGE
C. SELECT AGE, OCCUPATION FROM MY_TABLE GROUP BY COUNT(*)
D. SELECT AGE, FROM MY_TABLE GROUP BY OCCUPATION

10. The WHERE CLAUSE in a TSQL statement determines ...

A. ...what columns will be returned.
B. ...how the rows will be sorted.
C. ...what rows will be returned.
D. ...how the rows will be grouped together.

21. Consider the following TSQL statement:

Promotions
ID int not null,
Emp_ID int not null,
Promotion_Date datetime not null

Employees
ID int not null,
F_Name varchar(50) not null,
L_Name varchar(50) not null

Select E.ID as EMP_ID,E.F_Name,E.L_Name,P.Promotion_Date
FROM Employees E left join Promotions P
on E.ID = P.Emp_ID
WHERE P.Promotion_Date is null
GROUP BY E.ID,E.F_Name,E.L_Name,P.Promotion_Date

Which of the following statements is TRUE in regards to the statement?

A. This query will return all employees that were promoted.
B. This query will return employees that are not in the promotions table.
C. This query will return all the employees that have a promotion date.
D. The query will return an error.

22. Consider the following TSQL statement:

Employees
ID int not null,
F_Name varchar(50) not null,
L_Name varchar(100) not null

DECLARE @ColumnName varchar(255)
SET @ColumnName = ''

SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME)
FROM Information_Schema.Columns
WHERE Table_Name = 'Employees'
AND Date_Type IN ('char','varchar','nchar','nvarchar')
)

What will @ColumnName return?

A. It will return ID.
B. It will return F_Name.
C. It will return L_Name.
D. This query will return an error.

23. Consider the following TSQL statement:

SELECT ID, AVG(SALARY) AS 'AVG',sum(SALARY) AS 'SUM', F_NAME AS 'FIRSTNAME',L_NAME
FROM EMPLOYEES
GROUP BY ID
HAVING L_NAME IS NOT NULL
ORDER BY F_NAME,L_NAME

This query results in an error. Which of the following statements correctly identifies the reason why?

A. The ORDER By clause must use the aliases identified in the SELECT clause.
B. The HAVING clause must contain all aggregate columns.
C. The SELECT clause has incorrect use of aliases.
D. The GROUP BY clause must contain all non-aggregated columns.

24. Consider the following TSQL statement:

CREATE TABLE EMP (
ID int NOT NULL,
F_NAME varchar(30) NOT NULL,
L_NAME varchar(30) NOT NULL,
AGE int NULL,
GENDER varchar(1) NULL
)

Which of the following statements will correctly insert data into this table?

A. INSERT INTO EMP VALUES (100, 'chris','Brown',26,'MALE')
B. INSERT INTO EMP (AGE,GENDER,ID,F_NAME,L_NAME,) VALUES (26,'M',100,'Chris','Brown')
C. INSERT INTO EMP ('100','Chris','Brown', 26,'MALE')
D. INSERT INTO EMP (AGE,GENDER,ID,F_NAME,L_NAME,) VALUES (26,100,'Chris','Brown')

25. A TSQL Trigger is a...

A. ...field in a table that uniquely defines a row in the table.
B. ...rule set up on a column to restrict values being entered.
C. ...specialized stored procedure, bound to a table or view, which executes upon certain events.
D. ...data structure used to provide quick access to data in a database table or view.

26. Consider the following TSQL statement:

SELECT E.ID,
E.F_NAME,
E.L_NAME,
(SELECT TITLE
FROM POSITION P WHERE P.ID = E.ID) 'TITLE'
FROM EMPLOYEE E

This example shows which of the following structures?

A. a Nested Sub Query
B. a Correlated Sub Query
C. a Sub Query Aggregate
D. a Scalar Function

27. Consider the following tables:

Employee:
EMP_ID L_NAME JOB
1 Smith Programmer
2 Jones Manager
3 Mason Data Entry
4 Moen Analyst

Projects:
EMP_ID PROJ_NO
1 p_101
1 p_102
2 p_102
1 p_103
2 p_103
3 p_103

Then consider the following query:

SELECT L_NAME FROM EMPLOYEE
WHERE EMP_ID = (SELECT EMP_ID FROM PROJECTS WHERE PROJ_NO - 'p_102')

Which of the following values is returned by this query?

A. Smith
B. 1, 2
C. Smith, Jones
D. This query returns an error.

28. Consider the following TSQL statement:

WHILE (SELECT SUM(SALARY) FROM PROJECTS) 50000 BEGIN
BREAK
END
END

Which of the following statements is TRUE in regards to the statement?

A. If a salary is increased to more than 50000, that salary is no longer increased, but all others will be until the sum of all or equal to 100000.salaries is greater than
B. If a salary is increased to more than 50000, no further salaries will be increased.
C. Salaries will continue to increase until the highest salary is greater than or equal to 100000.
D. Salaries will continue to increase until the sum of all salaries is greater than or equal to 50000.

29. Consider the following table:

Employee:
EMP_ID L_NAME JOB SALARY
1101 Smith Programmer 55000
2913 Jones Manager 76000
2450 Mason Data Entry 39000
4412 Moen Analyst 67000

What is the result of executing the following TSQL code:

CREATE PROCEDURE sp_GeT_Salary
@Emp_ID int,
@Salary money OUTPUT
AS
BEGIN
SELECT @Salary = Salary FROM Employee
WHERE Emp_ID = @Emp_ID

IF @@RowCount = 1 BEGIN
Return
END

Set @Salary = 0
Return 1
END
GO

DECLARE @Value1 money
DECLARE @Value2 money
EXECUTE @Value2 = sp_Get_Salary 2913, @Value1 OUTPUT
SELECT @Value2

A. 1
B. 0
C. 76000
D. 2913

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.