IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New need a SQL refresher
I'm going to have to take an online SQL skills test and I'm a bit rusty. I lost most of my books in the fire/looting/emergency move.
Can anyone recommend a particular SQL education web site for me to go through?
New This seems to be a decent list.
http://steven.vorefamily.net/2012/03/14/introrefresher-resources-for-sql/comment-page-1/

Some of the links are dead, though. :-(

http://extremesoftwaretesting.com/InterviewQuestions/SQLTestOnline.html is 20 multiple choice questions that seem pretty elementary.

http://stackoverflow.com/questions/18783/favourite-performance-tuning-tricks is a bunch of comments on performance tuning tricks.

HTH a little. Good luck!

Cheers,
Scott.
New Questions 1 - 10 from the one I had to take last week
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.




Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous.
- - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
New Questions 11 - 20 from the one I had to take last week
11. Given the following table, which will result in an error?

My_Table
Name
Occupation
Age

A. SELECT NAME,OCCUPATION FROM MY_TABLE ORDER BY AGE
B. SELECT NAME,AGE ,OCCUPATION FROM MY_TABLE ORDER BY AGE
C. SELECT AGE,* FROM MY_TABLE ORDER BY AGE
D. SELECT * FROM MY_TABLE ORDER BY AGE, GROUP BY OCCUPATION

12. Consider the following query:

Payroll
Name
Salary
Date_Hired

SELECT NAME,
(CASE WHEN SALARY - 0
THEN 'INTERN'
ELSE 'EMPLOYEE'
END) AS TYPE,
DATE_HIRED
FROM PAYROLL

Which of the following statements is FALSE in regards to the query?

A. This query will return 3 columns.
B. This query will return a SALARY of 0 for each row that the TYPE = "INTERN".
C. This query will return a column that is not found in the PAYROLL table.
D. An alias is treated as a column in the query results.

13. Consider the following TSQL statement:

UPDATE EMPLOYEES
SET EMPLOYEES.SALARY = PAYROLL.SALARY
FROM PAYROLL, EMPLOYEES
WHERE EMPLOYEES.ID = PAYROLL.EMP_ID

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

A. This statement will result in an error.
B. This statement will update both the EMPLOYEES and PAYROLL tables.
C. This statement will never update any rows.
D. This statement will update the SQLARY column in the EMPLOYEES table.

14. Which of the following statements will create a variable in TSQL?

A. DECLARE @COUNT INTEGER
B. INT $COUNT
C. @@COUNT INTEGER
D. DEFINE @COUNT INT

15. Consider the following procedure:

CREATE PROCEDURE dbo.P_RETRIEVE_DEMO (@EMP_ID decimal(8,0))
AS
DECLARE @EMP_EXISTS integer
BEGIN
SELECT @EMP_EXISTS = (SELECT 1 FROM EMPLOYEES WHERE EMP_ID = @EMP_ID)

IF @EMP_EXISTS = 1 BEGIN
SELECT 'This employee is in the system.'
END
ELSE
BEGIN
SELECT 'This employee is not in the system.'
END
END
GO

Which one of the following statements is TRUE in regards to the procedure?

A. @EMP_ID is determined in a sub-query.
B. @EMP_EXISTS is a parameter passed into the procedure.
C. @EMP_EXISTS will contain the same number as @EMP_ID if the employee is in the system.
D. @EMP_ID is a parameter passed into the procedure.

16. Which of the following statements is FALSE when dropping a table using TSQL?

A. All indexes for the dropped table are marked invalid.
B. All procedures referencing the tables are dropped.
C. All synonyms for the dropped table are deleted.
D. The dropped table's table definition is dropped from the data dictionary.

17. Consider the following TSQL statement:

SELECT COUNT(*), EMPLOYEE_TYPE FROM EMPLOYEE GROUP BY EMPLOYEE_TYPE

This statement will return which of the following?

A. The total number of EMPLOYEE_TYPE values in the EMPLOYEE table.
B. The number of rows in the EMPLOYEE table for each EMPLOYEE_TYPE.
C. The number of rows in the EMPLOYEE table.
D. The total number of distinct values of EMPLOYEE_TYPE in the EMPLOYEE table.

18. Consider the following TSQL statement:

SELECT EMP_ID,SOC_NUM
INTO #MATCH_EMPS
FROM EMPLOYEES
WHERE TYPE = 'INTERN'

Which of the following statements is TRUE about #MATCH_EMPS?

A. It is a variable.
B. It is a parameter.
C. It is a temporary table.
D. It is a materialized view.

19. which TSQL command is used to iterate through each row in a cursor?

A. SELECT INTO
B. FETCH
C. FOREACH
D. UPDATE CURSOR ROW

20. Which of the following TSQL statements show the correct way to use concatenation in a SELECT clause?

A. SELECT F_NAME + ' ' + L_NAME FROM EMPLOYEES
B. SELECT F_NAME. ' ' . L_NAME FROM EMPLOYEES
C. SELECT F_NAME && ' ' && L_NAME FROM EMPLOYEES
D. SELECT F_NAME || ' ' || L_NAME FROM EMPLOYEES




Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous.
- - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
New Questions 21 - 29 from the one I had to take last week
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




Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous.
- - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
New Questions 30 - 36 from the one I had to take last week
30. Consider the following TSQL statement:

SELECT E.ID,
E.F_NAME,
E.L_NAME,
E.DATE_HIRED,
P.JOB,
P.EMP_ID
FROM EMPLOYEE E,
POSITION P
WHERE E.SALARY > 50000
AND E,ID = P.EMP_ID
AND P.JOB - "PROGRAMMER"

Which of the following indexes would provide the LEAST benefit in this query?

A. CREATE INDEX idx1 on EMPLOYEE(DATE_HIRED)
B. CREATE INDEX idx2 on EMPLOYEE(SALARY)
C. CREATE INDEX idx3 on POSITION(EMP_ID)
D. CREATE INDEX idx4 on POSITION(JOB)

31. When altering a column of a table in TSQL, which of the following statements is TRUE?

A. You cannot change a NOT NULL column to NULL if any rows contain null values in the column.
B. You cannot change a NULL column to NOT NULL if any rows contain null values in the column.
C. The altered column must retain the same data type.
D. Altered columns must be added to the end of the table.

32. Consider the following TSQL statement:

SELECT NAME, SALARY
INTO #EMP_SALARY_LIST
FROM EMPLOYEE
WHERE ID = 27

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

A. The tables must have the same columns in order for this statement to execute without error.
B. Both tables must exist prior to running the statement.
C. One of these tables will be dropped when the user logs out.
D. As part of the copy process to the second table, all data is deleted from the first table.

33. The relational operator 'less then sign-greater than sign' (replace the words with the actual symbols) means which of the following?

A. less than or greater than
B. equal to
C. not equal to
D. less than or equal to

34. Consider the following TSQL statements:

CREATE TABLE TEsTER (
ID INT NOT NULL,
F_NAME varchar(30) NOT NULL,
L_NAME varchar(30) NOT NULL
)

CREATE TABLE TEsT (
TEST_ID INT NOT NULL,
TESTER_ID INT NOT NULL,
TEST_NAME VARCHAR(30) NOT NULL,
TEST_DATE DATETIME NULL
)

Which of the following queries will return all testers, even if they have not taken a test yet, and their corresponding test name?

A. SELECT l_name,f_name,test_name LEFT JOIN test ON TESTER.tester_id = TEST.tester_id
B. SELECT l_name,f_name,test_name FROM tester, test on tester.id = test.tester_id
C. SELECT l_name,f_name,test_name FROM tester, test WHERE test.tester_id =* tester.id
D. SELECT l_name,f_name,test_name FROM tester RIGHT JOIN test WHERE tester.id = test.tester_id

35. Consider the following TSQL syntax:

SELECT title_id, NULLIF(royalty,10) AS Amount FROM titles

What must royalty be to have null returned?

A. 5
B. 10
C. 25
D. NULL

36. An advantage of using an index is it ...

A. ...enables more efficient table reads and can improve query execution time.
B. ...increase the page length enabling more data returned in the same amount of time.
C. ...improves entropy of arrangement and sorting to allow for improved sorting algorithms.
D. ...assists in data aggregation which combines possibilities into queriable units.




Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous.
- - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
New Here's another one
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.







Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous.
- - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
New thanks all
I was really over thinking it.
Allocate 100 minutes for test.
3 problems.
1st: given standard emp/dept, total sum of salary by department. 1 minute.
2nd: emp/dept again, gimme count of people by department. 1 minute.
3rd: given series of measurements, show the most recent measurement for each sensor. Problem wanted a correlated subquery, but the time stamps were unique, so I didn't need to match on multiple fields, and could use an simple min(time) feeding an "in" clause. 3 minutes.
Next interview (group attack) being set up.
New Woot! Excellent. Best of luck with the next hurdles.
New face to face setup
In NYC, 3 minute walk from my last job. But they prefer I work from home and simply visit the office occasionally.
New Re: they prefer I work from home
"Please don't throw me in that briar patch!" :)
Alex

"There is a cult of ignorance in the United States, and there has always been. The strain of anti-intellectualism has been a constant thread winding its way through our political and cultural life, nurtured by the false notion that democracy means that "my ignorance is just as good as your knowledge."

-- Isaac Asimov
New actually, I prefer going in
As long I can control my light and noise. There is way too much opportunity for missed communications and people don't realize how much they lose by not having face to face meetings.
New That depends what you're doing at the time.
When you have a well known objective and a lot of code to produce solitude can be a boon.

But yeh, when you the objective is a bit fuzzy and needs to be clarified with others then you need to be there. Same when you need feedback on what you've done so far.
Alex

"There is a cult of ignorance in the United States, and there has always been. The strain of anti-intellectualism has been a constant thread winding its way through our political and cultural life, nurtured by the false notion that democracy means that "my ignorance is just as good as your knowledge."

-- Isaac Asimov
New Did *they* think it would take that long?
--

Drew
New don't think so
These were essentially the starting queries I'd use to winnow job applicants who claimed to know SQL, and I've seen some horrendous attempts that would take 15 minutes to code that would never run.
New Yeah, they're good screeners
--

Drew
     need a SQL refresher - (crazy) - (15)
         This seems to be a decent list. - (Another Scott)
         Questions 1 - 10 from the one I had to take last week - (lincoln)
         Questions 11 - 20 from the one I had to take last week - (lincoln)
         Questions 21 - 29 from the one I had to take last week - (lincoln)
         Questions 30 - 36 from the one I had to take last week - (lincoln)
         Here's another one - (lincoln)
         thanks all - (crazy) - (8)
             Woot! Excellent. Best of luck with the next hurdles. -NT - (Another Scott) - (4)
                 face to face setup - (crazy) - (3)
                     Re: they prefer I work from home - (a6l6e6x) - (2)
                         actually, I prefer going in - (crazy) - (1)
                             That depends what you're doing at the time. - (a6l6e6x)
             Did *they* think it would take that long? -NT - (drook) - (2)
                 don't think so - (crazy) - (1)
                     Yeah, they're good screeners -NT - (drook)

We'll be back after a word from our sponsor.
86 ms